误区1.在表上建立了索引在查询时用到了索引的列索引就一定会生效首先明确下这样的观点是错误的SQL Server查询优化器是基于开销进行选择的优化器通过一系列复杂判断来决定是否使用索引、使用什么类型索引、使用那个索引。SQL Server内部维护着索引列上的数据的统计统计信息会随着索引列内容的变化而变化索引的有效期完全取决于索引列上的统计信息随着数据的变化关于索引的检索机制也随之变化。对于查询优化器来说始终保持查询开销最低始终是其的不二选择如果一个非聚集索引的列上有大量的重复值那么这个索引就不会有什么存在的意义这也是为什么不建议在类似性别bit类型上面建立非聚集索引的原因。说到这里可能会有人疑惑我在性别列上建一个索引性别只有两个值男、女当我我们查询条件中有性别这个字段时最起码会过滤掉一半的数据能大幅缩小我们需要检索的数据范围怎么会没用呢(事实上这也是我曾经困惑的地方)对我们理解的没错比如说Users表性别列Gender上建立索引IX_Gender执行select Gender from Users where Gender男 这个查询效率非常高而且也成功使用了索引IX_Gender然而我们这样写SQL的时候少之又少更多的我们会写这样的SQL:select UserID,UserName,Phone,Email from Users where Gender男 这时再去看看查询计划根本没用使用索引IX_Gender而是进行了一个聚集索引扫描或者表扫描查询条件where Gender男 明明在IX_Gender里面定义了为什么没使用呢这一切罪恶的根源就在于书签查找(RID、键查找)好了关于书签查找不是我们要讨论的话题在这里只想告诉大家索引不是万能的索引不是创建了就一定有效。误区2.聚集索引扫描用到了聚集索引索引所以性能很高一般来说我们可以认为聚集索引是效率最高的索引但聚集索引扫描绝不代表高效本质上聚集索引扫描就是表扫描一般出现扫描字样时代表缺少索引或者索引无效所以我们日常应用中应该避免在查询计划中看到扫描字样更多的出现聚集索引查找、索引查找才真正的使用到了索引才是王道。误区3.聚集索引扫描(表扫描)是全表扫描所以只要出现了表扫描就一定代表性能低下在误区2中我们说到应该尽量避免出现聚集索引扫描或者表扫描这是我们必须要坚持的原则但这并不代表这出现表扫描就一定性能低下有些情况下表扫描反而比索引查找有着更高的效率(一般出现在返回数据量较大出现大量书签查找的情况下)误区4.查询计划中看到了键查找或者RID查找时有着很高的性能键查找和RID查找统称为书签查找和错误认识正好相反出现书签查找反而代表着性能低下有些情况下甚至有着比表扫描更低的效率因此我们应该尽量避免书签查找。在返回数据量较小时书签查找对性能影响不大若返回数据量较大书签查找会严重影响查询性能因此我们建立索引时应该尽量覆盖要返回的所有列当然索引列数是有限的而且也不能单纯的为了避免书签查找而在索引中包含大量的列可以使用覆盖索引来解决书签查找问题或者需要大数据量返回时尽量使用聚集索引同时这也是为什么常听说的不要使用select *,而只选择需要的列进行输出因为select *很容易导致书签查找毕竟我们不打可能在所有列上建立索引也不可能所有查询都使用聚集索引(使用聚集索引和表扫描时不存在书签查找)误区5.查询开销统计中的逻辑读次数是读取的记录数天真的我曾经也这么认为查询计划中逻辑读次数就是读取的记录数然而看我们的查询4.1全表扫描返回830行数据为啥逻辑读只有22次而查询4.5同样是返回830行数据逻辑读为啥1724次呢一次读取一条的话逻辑读22次最多返回22行数据逻辑读1724次的话应该返回1724条数据吧有点小晕这里解释下逻辑读次数是指读取的页面数一个面8KB8个页面构成一个区64KB对于我们的示例表来说22个页面足以存下所有数据所以表扫描时只需读取22次就可以了那查询4.5为啥读取了1724次呢就算一个页面就一条数据按理说最多800多次也可以读取完毕了这是因为Sql Server对数据读取的最小单位就是页哪怕读取一条数据也需要读取整页数据而非聚集索引的读是随机读哪怕多条记录在同一页上也会导致多次重复读取外加书签查找导致了这么多的逻辑读这也是为什么非聚集索引不适合读取大量数据的原因之一。我们以Northwind数据库表Orders表为示例进行下演示1.先将Orders表的索引全部删除4.在OrderID上面创建聚集索引索引列为OrderIDcreate unique clustered index IX_OrderID on Orders(OrderID)3.在Orders表上创建非聚集索引IX_OrderDatecreate index IX_OrderDate on Orders(OrderDate)4.设置查询分析器选中包含实际的执行计划右键--包含实际的执行计划打开IO统计并依次执行以下查询set statistics io on select * from Orders select * from Orders where OrderDate1996-7-10 select * from Orders where OrderDate1997-1-1 --强制使用索引IX_OrderDate 查询日期1997-1-1 select * from Orders with(indexIX_OrderDate) where OrderDate1997-1-1 --强制使用索引IX_OrderDate查询日2000-1-1 select * from Orders with(indexIX_OrderDate) where OrderDate2001-1-14.1 执行 select * from Orders 的查询开销及查询计划可以看到执行的聚集索引扫描逻辑读22次没有使用索引返回行数830行4.2 执行 select * from Orders where OrderDate1996-7-10 的查询开销借查询计划可以看到成功使用了在OrderDate上面建立的索引IX_OrderDate逻辑读次数为14返回行数6行4.3 执行 select * from Orders where OrderDate1997-1-1 的查询开销及查询计划可以看到虽然我们在OrderDate上面建立了索引IX_OrderDate但执行计划并没有使用索引IX_OrderDate而是执行了一个聚集索引扫描逻辑读次数22而这个查询与4.2的区别仅仅在于OrderDate的值不一样返回行数154行4.4 执行 select * from Orders with(indexIX_OrderDate) where OrderDate1997-1-1 的查询开销及查询计划可以看到查询条件和4.3完全一致我们强制使用了IX_OrderDate返回记录数和4.3完全一致但逻辑读达到了328次返回行数154行4.5 执行 select * from Orders with(indexIX_OrderDate) where OrderDate2001-1-1 查询开销及查询计划同样我们强制使用了索引IX_OrderDate查询条件进行改变逻辑读达到了1724次返回行数数830行查询统计查询SQL索引返回行数逻辑读次数4.1 select * from Orders聚集索引扫描830224.2 select * from Orders where OrderDate1996-7-10IX_OrderDate6144.3 select * from Orders where OrderDate1997-1-1聚集索引扫描154224.4 select * from Orders with(indexIX_OrderDate) where OrderDate1997-1-1强制使用IX_OrderDate1543284.5 select * from Orders with(indexIX_OrderDate) where OrderDate2001-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.聚集索引扫描、表扫描不代表一定低效(表扫描不存在书签查找使用非聚集索引返回大量行时若存在书签查找反而不如表扫描性能高)