Sql Server 查询性能优化之走出索引的误区分析（2）

我们以Northwind数据库表Orders表为示例进行下演示

　1.先将Orders表的索引全部删除
　4.在OrderID上面创建聚集索引，索引列为OrderID　　 

代码如下:


create unique clustered index IX_OrderID on Orders(OrderID)


3.在Orders表上创建非聚集索引IX_OrderDate

create index IX_OrderDate on Orders(OrderDate)
4.设置查询分析器选中包含实际的执行计划（右键-->包含实际的执行计划），打开IO统计，并依次执行以下查询 

代码如下:


set statistics io on
select * from Orders
select * from Orders where OrderDate<='1996-7-10'
select * from Orders where OrderDate<='1997-1-1'

--强制使用索引IX_OrderDate 查询日期1997-1-1
select * from Orders with(index=IX_OrderDate) where OrderDate<='1997-1-1'

--强制使用索引IX_OrderDate查询日2000-1-1
select * from Orders with(index=IX_OrderDate) where OrderDate<='2001-1-1'


4.1 执行 select * from Orders 的查询开销及查询计划
　　　　可以看到执行的聚集索引扫描，逻辑读22次，没有使用索引，返回行数830行
　　　　

4.2 执行 select * from Orders where OrderDate<='1996-7-10' 的查询开销借查询计划
　　　　可以看到成功使用了在OrderDate上面建立的索引IX_OrderDate，逻辑读次数为14，返回行数6行

　　4.3 执行 select * from Orders where OrderDate<='1997-1-1' 的查询开销及查询计划
　　　　可以看到虽然我们在OrderDate上面建立了索引IX_OrderDate，但执行计划并没有使用索引IX_OrderDate而是执行了一个聚集索引扫描，逻辑读次数22而这个查询与4.2的区别仅仅在于OrderDate的值不一样，返回行数154行
　　
　　4.4 执行 select * from Orders with(index=IX_OrderDate) where OrderDate<='1997-1-1' 的查询开销及查询计划
　　　　可以看到查询条件和4.3完全一致，我们强制使用了IX_OrderDate，返回记录数和4.3完全一致，但逻辑读达到了328次，返回行数154行
　　　　
　　　　

4.5 执行 select * from Orders with(index=IX_OrderDate) where OrderDate<='2001-1-1' 查询开销及查询计划

　　同样我们强制使用了索引IX_OrderDate，查询条件进行改变，逻辑读达到了1724次，返回行数数830行
　　　　

　　

查询SQL索引返回行数逻辑读次数4.1 select * from Orders聚集索引扫描830224.2 select * from Orders where OrderDate<='1996-7-10'IX_OrderDate6144.3 select * from Orders where OrderDate<='1997-1-1'聚集索引扫描154224.4 select * from Orders with(index=IX_OrderDate) where OrderDate<='1997-1-1'强制使用IX_OrderDate1543284.5 select * from Orders with(index=IX_OrderDate) where OrderDate<='2001-1-1'强制使用IX_OrderDate8301724

通过对比以上查询我们可以知道虽然我们建立了索引，但索引并不总是有效，强制使用索引只会带来更低的效率，查询优化器会根据索引列的统计信息自动选择最优的查询计划进行执行。查询4.3和4.4查询条件完全一样，虽然我们建立了索引IX_OrderDate，但查询优化器并没有采用而是选择了开销更低的聚集索引扫描，在我们强制使用了索引后查询开销反而激增从逻辑读22次达到了328次，而我们仅仅查询到了154行数据；在查询4.5中我们继续强制使用索引，改变查询条件的值，在返回830行数据的情况下逻辑读次数达到了1724次，而返回相同数据的查询4.1仅仅执行了22次逻辑读。

困惑：通过查询4.1我们知道Orders表一共才有830条数据，为什么我们在查询4.5中强制使用索引后逻辑读达到了恐怖的1724次呢，即便一条数据读取一次也才不过830次啊。

解惑：查询4.5强制使用索引后，查询优化器首先去到索引IX_OrderDate上面检索，然后在根据索引IX_OrderDate去找聚集索引指针，根据聚集索引指针去聚簇索引叶子节点(实际数据行)查找数据(书签查找)，才导致了更大的查询开销。

结论：
　　　　1.索引不是万能的，查询列上建立了索引不代表就一定会使用索引(参见结论2)
　　　　2.绝大多数情况下查询优化器会根据索引列上的数据统计信息自动选择最优的执行计划，而且查询计划会随着数据量变化而变化，所以如果不是有必要不要使用索引提示来强制使用某索引
　　　　3.聚集索引扫描、表扫描不代表一定低效(表扫描不存在书签查找，使用非聚集索引返回大量行时，若存在书签查找反而不如表扫描性能高)
　　　　4.索引查找不一定高效(非聚集索引查找时容易出现书签查找)
　　　　5.书签查找会降低查询效率，尤其是大范围读取数据时会严重影响效率，所以应该尽量避免书签查找或出现书签查找时尽量返回较少的数据行
　　　　6.需要注意下查询开销统计里的逻辑读是指读取的页面数而不是数据行数

　示例中采用的语句及数据仅作为演示使用，实际开发应用中要比示例的数据复杂的多，同一个查询在不同的环境下可能产生完全相反的结果，如何应用好还主要在于我们个人的认识和理解，希望有幸看到本文的朋友能借此加深一些对索引的理解和认识，走出索引的误区，开发出高性能的应用。

本人不是DBA，只是一名普通的开发人员，以上均为实际工作中的一些经验、体会，鉴于本人水平非常有限，有说的不对或理解不到位的地方还望各位大神给予指正，以免误导他人，不胜感激。

后续会继续写一些关于Sql Server查询性能优化方面的实践经验，主要包含以下几方面
Sql Server查询性能优化之建立合理的索引
Sql Server查询性能优化之避免书签查找
Sql Server查询性能优化之复用查询计划
Sql Server查询性能优化之选择合适的字段类型

附上用的数据表：DemoDB.rar

从Northwind数据库分离出来的，仅用了其中的Orders表

此文章属懒惰的肥兔原创

查询统计