SQL Server查询性能优化——覆盖索引（二）

2021-08-11 22:17:50

在SQL Server 查询性能优化——覆盖索引（一）中讲了覆盖索引的一些理论。

　　本文将具体讲一下使用不同索引对查询性能的影响。
　　下面通过实例，来查看不同的索引结构，如聚集索引、非聚集索引、组合索引等来查看相同的SQL语句查询的不同性能
 
例一：没有任何索引的查询访问
　　1.表的碎片情况：
 
SQL Server查询性能优化——覆盖索引（二）
　　2.SQL查询语句与查询执行计划成本
 
--要求返回IO统计，也就是数据页访问的数量
SET STATISTICS IO ON
--没有任何索引情况下的数据页访问数量
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

--表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1568 次，物理读取54 次，预读1568 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读 0 次。
SET STATISTICS IO OFF
 
SQL Server查询性能优化——覆盖索引（二）
 
例二：通过聚集索引查询访问
　　1.聚集索引的碎片情况：
 
SQL Server查询性能优化——覆盖索引（二）
　　2.SQL查询语句与查询执行计划成本　　
 
--要求返回IO统计，也就是数据分页访问的数量
SET STATISTICS IO ON
---通过聚集索引查询访问的数据页数量
create clustered index idx_WBK_PDE_LIST_ORG_HISTROY on [WBK_PDE_LIST_ORG_HISTROY](QTY_1)
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
--表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取351 次，物理读取4 次，预读345 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
 
SET STATISTICS IO OFF
---
drop index [WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY
---
 
SQL Server查询性能优化——覆盖索引（二）
 
例三：强制通过非聚集索引查询访问
　　1.非聚集索引的碎片情况：
 
SQL Server查询性能优化——覆盖索引（二）
　　2.SQL查询语句与查询执行计划成本
 
--要求返回IO统计，也就是数据页访问的数目
SET STATISTICS IO ON
--强制通过非聚集索引查询访问的数据页数量，用错索引比不用索引更糟糕
create  index idx_WBK_PDE_LIST_ORG_HISTROY on [WBK_PDE_LIST_ORG_HISTROY](WBOOK_NO)
SELECT [WBOOK_NO]
  ,[COP_G_NO]
  ,[G_NO]
  ,[CODE_T]
  ,[QTY_1]
  ,[UNIT_1]
  ,[TRADE_TOTAL] 
  ,[GROSS_WT]

FROM [WBK_PDE_LIST_ORG_HISTROY] with (index(idx_WBK_PDE_LIST_ORG_HISTROY)) where qty_1 between 50 and 500

--表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取61065 次，物理读取864 次，预读727 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
SET STATISTICS IO OFF
---
drop index [WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY
 
SQL Server查询性能优化——覆盖索引（二）
 
例四：通过字段顺序不适用的覆盖索引查询访问
　　1.非聚集索引的碎片情况：
 
SQL Server查询性能优化——覆盖索引（二）
　　2.SQL查询语句与查询执行计划成本
 
--要求返回IO统计，也就是数据页访问的数量
SET STATISTICS IO ON
--通过字段顺序不适用的覆盖索引查询访问的数据页数量
create  index idx_WBK_PDE_LIST_ORG_HISTROY on [WBK_PDE_LIST_ORG_HISTROY]([WBOOK_NO]
  ,[COP_G_NO]
  ,[G_NO]
  ,[CODE_T]
  ,[QTY_1]
  ,[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

--表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取687 次，物理读取9 次，预读683 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
SET STATISTICS IO OFF
---
drop index [WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY
 
SQL Server查询性能优化——覆盖索引（二）
 
例五：通过覆盖索引查询访问
　　1.非聚集索引的碎片情况：
 
SQL Server查询性能优化——覆盖索引（二）
　　2.SQL查询语句与查询执行计划成本
 
--要求返回IO统计，也就是数据页访问的数量
SET STATISTICS IO ON
--通过覆盖索引查询访问的数据页数量
create  index idx_WBK_PDE_LIST_ORG_HISTROY on [WBK_PDE_LIST_ORG_HISTROY]([QTY_1]
  ,[COP_G_NO]
  ,[G_NO]
  ,[CODE_T]
  ,[WBOOK_NO]
  ,[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

--表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取178 次，物理读取5 次，预读175 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
SET STATISTICS IO OFF
---
drop index [WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY
 
SQL Server查询性能优化——覆盖索引（二）
 
例六：通过字段顺序不适用的覆盖索引查询访问
　　1.聚集索引的碎片情况：
 
SQL Server查询性能优化——覆盖索引（二）
　　2.SQL查询语句与查询执行计划成本
 
--要求返回IO统计，也就数据页访问的数量
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])
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

--表'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
 
SQL Server查询性能优化——覆盖索引（二）
 
例七：通过子叶层覆盖索引查询访问(INCLUDE)
 
　　1.聚集索引的碎片情况：
SQL Server查询性能优化——覆盖索引（二）
　　2.SQL查询语句与查询执行计划成本
 
--要求返回IO统计，也就是数据页访问的数量
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])

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

--表'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
 
SQL Server查询性能优化——覆盖索引（二）
 
访问方式分页
逻辑读
物理读
预读
估计运算符开销
全表扫描
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不必去实际的表执行聚集计算，这样可以提升性能。
 
3) 覆盖索引能提升获取数据的性能，但它们也能降低INSERT、UPDATE和DELETE操作的性能。这是因为维护覆盖索引要求做一些额外的工作。通常这不是问题，除非你的数据库经常进行非常高的INSERT、UPDATE和DELETE操作。你也许不得不在你的产品系统上应用覆盖索引之前，要先进行实验，看看你所建立的覆盖索引是否在提升性能方面上比影响性能方面更有帮助。
4) 应该在那些SELECT查询中常使用到的列上创建覆盖索引，但覆盖索引中包括过多的列也不行，因为覆盖索引列的值是存储在内存中的，这样会消耗过多内存，引发性能下降。
 
关于索引碎片的修复：
 
SQL Server查询性能优化——覆盖索引（二）
关于上图的一些说明：
avg_fragmentation_in_percent:逻辑碎片（索引中的无序页）的百分比。这是索引的叶级页中出错页所占的百分比。对于出错页，分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。 
fragment_count : 索引中的碎片（物理上连续的叶页）数量。 
avg_fragment_size_in_pages :索引中一个碎片的平均页数。 
知道索引碎片程度后，可以使用下表确定修复碎片的最佳方法。
 
avg_fragmentation_in_percent 值
修复语句
> 5% 且 < = 30%
ALTER INDEX REORGANIZE
> 30%
ALTER INDEX REBUILD WITH (ONLINE = ON)
上一篇：Leetcode: UniquePath II


下一篇：Linux swappiness参数设置与内存交换