网站开发所需费用,酷播wordpress,凡科建站的怎么取消手机网站,免费 护理ppt模板有一个通常的误解#xff0c;觉得应该把选择性高的字段放在最前面#xff0c;这通常只是针对一个字段的索引#xff0c;对于组合索引#xff0c;常常要把做等式运算的字段放在最前面#xff0c;看看测试 USE AdventureWorks
GOCREATE TABLE demo1
( id INT identity(1,1)… 有一个通常的误解觉得应该把选择性高的字段放在最前面这通常只是针对一个字段的索引对于组合索引常常要把做等式运算的字段放在最前面看看测试 USE AdventureWorks
GOCREATE 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 #numSELECT row_number() over( order BY customerid ) AS number
INTO #num
FROM adventureworks.sales.Individual--插入测试数据
INSERT demo1 (gender,age)
SELECT CASE WHEN number%20 THEN fELSE mEND,abs(checksum(newid()))%80
FROM #num 创建如下的索引 CREATE INDEX ix_age_sex ON demo1(age,gender) include(description) WITH (onlineon)查询如下的语句 DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERSSELECT age,gender,description FROM demo1
WHERE genderf
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 (onlineon)在来查询相同的语句 DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERSSELECT age,gender,description FROM demo1
WHERE genderf
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做等式运算的字段放在最前面如果有多个等式运算才要考虑等式运算字段的选择性 转载于:https://www.cnblogs.com/fly_zj/archive/2012/08/11/2633629.html
