SQL Server查询性能优化――覆盖索引（二）(二)
PDE_LIST_ORG_HISTROY
例六：通过字段顺序不适用的覆盖索引查询访问
1.聚集索引的碎片情况：
2.SQL查询语句与查询执行计划成本
--要求返回IO统计，也就数据页访问的数量
SET STATISTICS IO ON
SET STATISTICS IO ON
---通过字段顺序不适用的覆盖索引查询访问的数据页数量
create index idx_WBK_PDE_LIST_ORG_HISTROY on [WBK_PDE_LIST_ORG_HISTROY]([WBOOK_NO]) include(qty_1
,[COP_G_NO]
,[G_NO]
,[CODE_T]
,[UNIT_1]
,[TRADE_TOTAL]
,[GROSS_WT])
create index idx_WBK_PDE_LIST_ORG_HISTROY on [WBK_PDE_LIST_ORG_HISTROY]([WBOOK_NO]) include(qty_1
,[COP_G_NO]
,[G_NO]
,[CODE_T]
,[UNIT_1]
,[TRADE_TOTAL]
,[GROSS_WT])
SELECT [WBOOK_NO]
,[COP_G_NO]
,[G_NO]
,[CODE_T]
,[QTY_1]
,[UNIT_1]
,[TRADE_TOTAL]
,[GROSS_WT]
FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1 between 50 and 500
,[COP_G_NO]
,[G_NO]
,[CODE_T]
,[QTY_1]
,[UNIT_1]
,[TRADE_TOTAL]
,[GROSS_WT]
FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1 between 50 and 500
--表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取682 次，物理读取1 次，预读492 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
SET STATISTICS IO OFF
---
drop index [WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY
drop index [WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY
例七：通过子叶层覆盖索引查询访问(INCLUDE)
1.聚集索引的碎片情况：
--要求返回IO统计，也就是数据页访问的数量
SET STATISTICS IO ON
SET STATISTICS IO ON
--通过子叶层覆盖索引查询访问的数据页数量
create index idx_WBK_PDE_LIST_ORG_HISTROY on [WBK_PDE_LIST_ORG_HISTROY](qty_1) include([WBOOK_NO]
,[COP_G_NO]
,[G_NO]
,[CODE_T]
,[UNIT_1]
,[TRADE_TOTAL]
,[GROSS_WT])
create index idx_WBK_PDE_LIST_ORG_HISTROY on [WBK_PDE_LIST_ORG_HISTROY](qty_1) include([WBOOK_NO]
,[COP_G_NO]
,[G_NO]
,[CODE_T]
,[UNIT_1]
,[TRADE_TOTAL]
,[GROSS_WT])
SELECT [WBOOK_NO]
,[COP_G_NO]
,[G_NO]
,[CODE_T]
,[QTY_1]
,[UNIT_1]
,[TRADE_TOTAL]
,[GROSS_WT]
FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1 between 50 and 500
,[COP_G_NO]
,[G_NO]
,[CODE_T]
,[QTY_1]
,[UNIT_1]
,[TRADE_TOTAL]
,[GROSS_WT]
FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1 between 50 and 500
--表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取177 次，物理读取4 次，预读173 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
SET STATISTICS IO OFF
drop index [WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY
drop index [WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY
|
访问方式分页
|
逻辑读
|
物理读
|
预读
|
估计运算符开销
|
|
全表扫描
|
1568
|
54
|
1568
|
1.06575
|
|
以QTY_1字段建立聚集索引
|
351
|
4
|
345
|
0.275863
|
|
以WBOOK_NO字段建非立聚集索引
|
61065
|
864
|
727
|
14.10295
|
|
以[WBOOK_NO],[COP_G_NO],[G_NO],[CODE_T],[QTY_1],[UNIT_1],[TRADE_TOTAL] ,[GROSS_WT]八个字段建复合索引
|
687
|
9
|
683
|
0.570198
|
|
以[QTY_1],[COP_G_NO],[G_NO],[CODE_T],[WBOOK_NO],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]八个字段建复合索引
|
178
|
5
|
175
|
0.146974
|
|
以WBOOK_NO建立索引,include以下字段 [QTY_1],[COP_G_NO],[G_NO],[CODE_T],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]
|
682
|
1
|
492
|
0.570198
|
|
以[QTY_1]建立索引,include 以下字段[WBOOK_NO],[COP_G_NO],[G_NO],[CODE_T],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]
|
177
|
4
|
173
|
0.146974
|
|
例一/例二/例三/例四/例五/例六/例七
|
8.8/2/345/3.9/1/3.9/1
|
54/4/864/9/5/1/4
|
8.8/2/4.1/3.9/1/2.8
|
7.2/1.9/96/3.9/1/3.9/1
|
从上表中可以得出一个结论，如果索引使用不当，例如上面的例三——强制使用选择性很低的索引来查找数据（或是索引统计数据错误、优化引擎误判等，造成索引使用不当），反而会导致大量的I/O操作（逻辑读61065次，物理读864次），其成本比进行全表扫描(例一)还高。
例二，通过聚集索引来查找，因为缩小了数据表扫描范围，所以效果较佳。
例五、例七，建立覆盖索引，因为数据结构远小于数据表本身，所以不管组合索引的字段顺序是否正确，都有更好的查询效果。当然 ，依WHERE条件所需要的字段建立索引数据摆放顺序，也就是[QTY_1]放在索引顺序的第一位，再include查询所需要的字段([WBOOK_NO],[COP_G_NO],[G_NO],[CODE_T],[UNIT_1],[TRADE_TOTAL],[GROSS_WT])，其查询性能最佳。
最后要提醒注意：
1） 在建立覆盖查询时要尽量限制索引键值的大小，保持Row-to-key的大小比例差异越大越好。否则扫描覆盖索引与扫描数据表所花的I/O操作差不多，这样就失去了建立覆盖索引的意义。
2) 覆盖索引可以用来提升查询性能，因为索引中包含了所有查询里的列.非聚集索引为表里的每一行用索引键值来存储一行。另外SQL Server能使用索引页级里的这些行来执行聚集计算。这意味着SQLServer不必去实际的表执行聚集计算，这样可以提升性能。