添加组合索引时,做相等运算字段应该放在最前面
有一个通常的误解,觉得应该把选择性高的字段放在最前面,这通常只是针对一个字段的索引,对于组合索引,常常要把做等式运算的字段放在最前面,看看测试
USE AdventureWorks GO CREATE TABLE demo1 ( id INT identity(1,1) PRIMARY KEY, gender char(1) NOT NULL , age int NOT NULL, DESCRIPTION varchar(1000) default(replicate('a',1000)) ) --填充数字辅助表 --DROP TABLE #num SELECT row_number() over( order BY customerid ) AS number INTO #num FROM adventureworks.sales.Individual --插入测试数据 INSERT demo1 (gender,age) SELECT CASE WHEN number%2=0 THEN 'f' ELSE 'm' END, abs(checksum(newid()))%80 FROM #num
创建如下的索引:
CREATE INDEX ix_age_sex ON demo1(age,gender) include(description) WITH (online=on)
查询如下的语句:
DBCC FREEPROCCACHE CHECKPOINT DBCC DROPCLEANBUFFERS SELECT age,gender,description FROM demo1 WHERE gender='f' AND age BETWEEN 30 AND 40
逻辑读为:
(1236 row(s) affected)
Table 'demo1'. Scan count 1, logical reads 350, physical reads 3, read-ahead reads 346, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
现在删除原来的索引,创建低选择性在前的索引
DROP INDEX ix_age_sex ON demo1 CREATE INDEX ix_age_sex ON demo1(gender,age)INCLUDE (description) WITH (online=on)
在来查询相同的语句:
DBCC FREEPROCCACHE CHECKPOINT DBCC DROPCLEANBUFFERS SELECT age,gender,description FROM demo1 WHERE gender='f' AND age BETWEEN 30 AND 40
逻辑读为:
(1236 row(s) affected)
Table 'demo1'. Scan count 1, logical reads 181, physical reads 3, read-ahead reads 178, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
可以看到低选择性的字段放在前面逻辑读有350减少到了181,由此产生性能提升
总结:1:做等式运算的字段放在最前面,如果有多个等式运算,才要考虑等式运算字段的选择性