SQLServer索引调优实践(2) - 索引覆盖

以你之姓@ 2022-08-18 03:06 220阅读 0赞

继续SQLServer索引调优实践。这次探讨一下索引覆盖 - SQL Server主要使用索引去查询你需要的数据，当索引包括所有的你请求查询的字段，SQL Server将不需要去在表中查询。这个概念称做“索引覆盖”。

SQLServer2005的Non-clustered INDEX增加了一个“[包含列(included column)][included column] ”选项。在 SQL Server 2005 中，可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。当查询中的所有列都作为键列 或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。这样可以实现性能提升，因为查询优化器可以在索引中找到所有列值；不访问表或聚集 索引数据，从而减少磁盘 I/O 操作。

但应避免添加不必要的列。添加过多的索引列（键列或非键列）会对性能产生不良影响，应该合理使用。和Clustered INDEX，或者组合索引，结合使用，扩大索引覆盖，但不大可能所有列都有索引覆盖，磁盘开销和数据insert updat时索引的重新计算的时间开销是巨大的。总之，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最 佳的优化方案。

继续实践，先建个实验表 Table1：

[![sql5][] ][sql5_]

建两个索引：

1. 主键ID是Clustered INDEX

2. 非聚簇索引Non-Clustered INDEX建立在Age列上，包含列：Count。

CREATE NONCLUSTERED INDEX \[cnt\] ON \[dbo\].\[table1\]  
(  
    \[Age\] ASC  
)  
INCLUDE ( \[Count\])  
ON \[PRIMARY\]

我们的测试SQL语句是：从10万条记录中取出4条记录，两种写法

1. SELECT **\*** FROM table1 WHERE age < 100;  
2. SELECT **count** FROM table1 WHERE age < 100;

看看运行效率如何：

磁盘IO和时间：

[![sql2][] ][sql2_]

实际执行计划：

[![sql][] ][sql_]

性能居然相差20多倍。为什么？

原来**第二句Select Count** 在索引覆盖范围内，因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。而**第一句Select \*** 选择了所有字段，其中有一个字段Name不在索引覆盖范围内（既不在聚簇索引列，也不在非聚簇索引覆盖列内），SQL Server可以在同一个查询中为一个表使用多个索引，并可以合并多个索引（使用联接算法），以便搜索关键字共同覆盖一个查询。查询分析优化器会自动进行 选择, 上述执行计划就是优化的结果，依然比第二个index seek慢了20倍。

然后我又把sql改了一下，变成从10万条记录中取得大部分数据( 返回99900条)，小于号改成大于号：

1. SELECT **\*** FROM table1 WHERE age > 100;  
2. SELECT **count** FROM table1 WHERE age > 100;

看看结果：

磁盘IO和时间：

[![sql4][] ][sql4_]

实际执行计划：

[![sql3][] ][sql3_]

依然是第二句索引覆盖的快，这是毋庸置疑的。但第一句执行计划有所不同，SQLServer查询分析优化器选择了不同的策略，改为聚簇索引扫描。上 面说过了，SQL Server可以在同一个查询中为一个表使用多个索引，并可以合并多个索引（使用联接算法），以便搜索关键字共同覆盖一个查询。查询分析优化器会自动进行 选择, 上述执行计划就是优化的结果。

为何结果集较小和结果集较大SQLServer选择的索引方案不同？

(From园友 [高强][Link 1] ： 当 WHERE age < 100时，由于结果集记录数较小，SQL SERVER 先INDEX SEEK（得出一个较小的结果集） 然后和clustered index seek 嵌套循环。而当WHERE age > 100时，由于记录数比较多，所以SQL SERVER 认为直接根据聚集索引叶级页面链表扫描页面得出结果更快。不管怎样，最终目的就是在相同结果集情况下，尽可能减少逻辑IO。)

看到这儿，恐怕喜欢用**Select\*** 的同学也要节制一下使用了，有时候SQLServer中**Select\*** 代价是很高的。当然类似这种SQL是没有问题的，（where exists (select \* from ...)），因为SQLServer查询分析优化器会聪明的知道此**Select\*** 非彼**Select\*** 。

数据库是一个很复杂的系统，即使你不是数据库专家，是应用开发人员，知道一点SQLServer内部更多的东西会有好处，而合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来, 才能产生最佳的优化方案。

[included column]: http://msdn.microsoft.com/zh-cn/library/ms190806%28SQL.90%29.aspx
[sql5]: /images/20220731/d8b9614d2bff46cd8aa4d49327380749.png
[sql5_]: http://images.cnblogs.com/cnblogs_com/Mainz/WindowsLiveWriter/SQLServer2_D865/sql5_2.png
[sql2]: /images/20220731/0059634d65cf4ade81092943e93ac320.png
[sql2_]: http://images.cnblogs.com/cnblogs_com/Mainz/WindowsLiveWriter/SQLServer2_D865/sql2_2.png
[sql]: /images/20220731/937e06c7b4da416086121ebd572f5553.png
[sql_]: http://images.cnblogs.com/cnblogs_com/Mainz/WindowsLiveWriter/SQLServer2_D865/sql_2.png
[sql4]: /images/20220731/4b4b4e477834477982a0ff9c40345b49.png
[sql4_]: http://images.cnblogs.com/cnblogs_com/Mainz/WindowsLiveWriter/SQLServer2_D865/sql4_2.png
[sql3]: /images/20220731/cea1828691e24ca59b384d134bfae051.png
[sql3_]: http://images.cnblogs.com/cnblogs_com/Mainz/WindowsLiveWriter/SQLServer2_D865/sql3_2.png
[Link 1]: http://www.cnblogs.com/zhouqiang52154/