为什么b d不走索引啊

[qq_44782455]

在线等。。。。。。。。。

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('EMP') IS NOT NULL
DROP TABLE EMP
GO
CREATE TABLE EMP(
	id INT IDENTITY(1,1) PRIMARY KEY,
	deptno INT,
	job VARCHAR(20)	
)
GO
INSERT INTO EMP(deptno,[job])
SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 1)) AS rid
,left(NEWID(),10) AS job 
FROM [master].dbo.spt_values AS sv 
 CROSS JOIN [master].dbo.spt_values AS sv2 
WHERE sv.type='P' AND sv2.type='P' AND sv.number>0 AND sv2.number>0
--(4190209 行受影响)
CREATE INDEX c_index ON EMP(deptno,job)
--------- 以上为测试表及数据 ----------

--执行，打开执行计划
SELECT * FROM emp WHERE deptno=66 AND JOB = 'sals'
--1. 两者用的是 AND, 条件列与索引正好匹配

SELECT * FROM emp WHERE deptno=66 OR JOB='sals'
--2.1 两者用的是 OR, 条件列与索引不匹配

SELECT * FROM emp WHERE deptno=66
UNION 
SELECT * FROM emp WHERE JOB='sals'
--2.2 改良后的写法，有一个语句是索引查找 

SELECT * FROM emp WHERE deptno=66
--3. 首列匹配，所以是索引查找

SELECT * FROM emp WHERE JOB='sals' 
--4. 首列不匹配

2 不能走索引查找，是因为用到了 非 SARG 的 OR 关键字。 4 不能走索引查找，是因为条件列与索引首列不匹配，索引首列非常重要。

[吉普赛的歌]

[xiaoxiangqing]

优化器会自动决定是否找索引

[吉普赛的歌]

引用 6 楼 Sqlera 的回复:

[quote=引用 5 楼 吉普赛的歌 的回复:] [quote=引用 4 楼 Sqlera 的回复:] 版主一楼二楼回复得很好。只是有两点要注意， 1. 2.2 并非与2.1完全同义 2. 2.2未必比2.1性能好，因为Index Scan始终不能避免

1. 没有说同义， 只是列出解决方案。 2. 2.2 不能算是 100 分， 但至少是 60 分， 比 2.1 好是肯定的，因为至少一个表用到了 索引查找， 而 2.1 是纯索引扫描。 在不允许添加其它索引的情况下， 算是最优的办法了。[/quote] 至少从IO角度，2.1一次scan解决的东西，2.2变成了一个scan，再加一个index seek. 性能统计或者执行计划应该能反映这一点。[/quote] 这些优化经验，是从实际的工作里得到的。 当然， 也可以给你看实际的消耗哪个大：

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT * FROM emp WHERE deptno=66 OR JOB='sals'
--2.1 两者用的是 OR, 条件列与索引不匹配

SELECT * FROM emp WHERE deptno=66
UNION 
SELECT * FROM emp WHERE JOB='sals'
--2.2 改良后的写法，有一个语句是索引查找 

/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)
表 'EMP'。扫描计数 5，逻辑读取 14811 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 640 毫秒，占用时间 = 170 毫秒。

(1 行受影响)
表 'EMP'。扫描计数 6，逻辑读取 14814 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 438 毫秒，占用时间 = 113 毫秒。
*/

[天涯特困生]

版主一楼二楼回复得很好。只是有两点要注意， 1. 2.2 并非与2.1完全同义 2. 2.2未必比2.1性能好，因为Index Scan始终不能避免

[吉普赛的歌]

引用 4 楼 Sqlera 的回复:

版主一楼二楼回复得很好。只是有两点要注意， 1. 2.2 并非与2.1完全同义 2. 2.2未必比2.1性能好，因为Index Scan始终不能避免

1. 没有说同义， 只是列出解决方案。 2. 2.2 不能算是 100 分， 但至少是 60 分， 比 2.1 好是肯定的，因为至少一个表用到了 索引查找， 而 2.1 是纯索引扫描。 在不允许添加其它索引的情况下， 算是最优的办法了。

[天涯特困生]

引用 5 楼 吉普赛的歌 的回复:

[quote=引用 4 楼 Sqlera 的回复:] 版主一楼二楼回复得很好。只是有两点要注意， 1. 2.2 并非与2.1完全同义 2. 2.2未必比2.1性能好，因为Index Scan始终不能避免

1. 没有说同义， 只是列出解决方案。 2. 2.2 不能算是 100 分， 但至少是 60 分， 比 2.1 好是肯定的，因为至少一个表用到了 索引查找， 而 2.1 是纯索引扫描。 在不允许添加其它索引的情况下， 算是最优的办法了。[/quote] 至少从IO角度，2.1一次scan解决的东西，2.2变成了一个scan，再加一个index seek. 性能统计或者执行计划应该能反映这一点。

[天涯特困生]

引用 7 楼 吉普赛的歌 的回复:

[quote=引用 6 楼 Sqlera 的回复:] [quote=引用 5 楼 吉普赛的歌 的回复:] [quote=引用 4 楼 Sqlera 的回复:] 版主一楼二楼回复得很好。只是有两点要注意， 1. 2.2 并非与2.1完全同义 2. 2.2未必比2.1性能好，因为Index Scan始终不能避免

1. 没有说同义， 只是列出解决方案。 2. 2.2 不能算是 100 分， 但至少是 60 分， 比 2.1 好是肯定的，因为至少一个表用到了 索引查找， 而 2.1 是纯索引扫描。 在不允许添加其它索引的情况下， 算是最优的办法了。[/quote] 至少从IO角度，2.1一次scan解决的东西，2.2变成了一个scan，再加一个index seek. 性能统计或者执行计划应该能反映这一点。[/quote] 这些优化经验，是从实际的工作里得到的。 当然， 也可以给你看实际的消耗哪个大：

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT * FROM emp WHERE deptno=66 OR JOB='sals'
--2.1 两者用的是 OR, 条件列与索引不匹配

SELECT * FROM emp WHERE deptno=66
UNION 
SELECT * FROM emp WHERE JOB='sals'
--2.2 改良后的写法，有一个语句是索引查找 

/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)
表 'EMP'。扫描计数 5，逻辑读取 14811 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 640 毫秒，占用时间 = 170 毫秒。

(1 行受影响)
表 'EMP'。扫描计数 6，逻辑读取 14814 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 438 毫秒，占用时间 = 113 毫秒。
*/

[/quote] 抱歉，我的确没有亲自去跑这个查询，感谢版主贴上输出，但这个输出应该说是验证我的论述 2.1 扫描计数 5，逻辑读取 14811 次 2.2 扫描计数 6，逻辑读取 14814 次 显然2.2对IO的压力更大。可能从CPU这块，你能看出2.2更快，但可惜这个结果应该不怎么靠得住，在不同的环境，不同的负载下，你可能得到不一样的结果。 另外，我所说的“ 2.2未必比2.1性能好”，并不意味着我支持“2.1的性能一定比2.2好”的观点。在数据库的世界里很多情况是需要case by case的，比如2.2里UNION还需要的一个操作就是 Sort后DISTINCT去掉重复的，如果数据集小重复率低，则对性能的影响可能小到可以忽略不记，反之，如果数据集巨大重复率又很高，那可能会对性能数据产生颠覆性影响。因此，数据的分布性也可能严重影响最终结果，没法把话说太死。 就这个例子而言，可能真正重要的是理解自己在做什么以及可能的影响。