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

据了解绝大多数开发人员对于索引的理解都是一知半解，局限于大多数日常工作没有机会、也什么没有必要去关心、了解索引，实在哪天某个查询太慢了找到查询条件建个索引就ok，哪天又有个查询慢了，再建立个索引就是，或者干脆把整个查询SQL直接发给DBA，让DBA直接帮忙优化了，所以造成的状况就是开发人员对于索引的理解、认识很局限，以下就把我个人对于索引的理解及浅薄认识和大家分享下，希望能解除一些大家的疑惑，一起走出索引的误区 误区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条数据吧，有点小晕，这里解释下逻辑读次数是指读取的页面数，一个面8KB，8个页面构成一个区64KB，对于我们的示例表来说22个页面足以存下所有数据，所以表扫描时只需读取22次就可以了，那查询4.5为啥读取了1724次呢，就算一个页面就一条数据按理说最多800多次也可以读取完毕了，这是因为Sql Server对数据读取的最小单位就是页，哪怕读取一条数据也需要读取整页数据，而非聚集索引的读是随机读哪怕多条记录在同一页上也会导致多次重复读取，外加书签查找导致了这么多的逻辑读，这也是为什么非聚集索引不适合读取大量数据的原因之一。
我们以Northwind数据库表Orders表为示例进行下演示 　1.先将Orders表的索引全部删除
　4.在OrderID上面创建聚集索引，索引列为OrderID　　
<div class="codetitle"><a style="CURSOR: pointer" data="89097" class="copybut" id="copybut89097" onclick="doCopy('code89097')"> 代码如下:<div class="codebody" id="code89097">
create unique clustered index IX_OrderID on Orders(OrderID)

3.在Orders表上创建非聚集索引IX_OrderDate create index IX_OrderDate on Orders(OrderDate)
4.设置查询分析器选中包含实际的执行计划（右键-->包含实际的执行计划），打开IO统计，并依次执行以下查询
<div class="codetitle"><a style="CURSOR: pointer" data="80811" class="copybut" id="copybut80811" onclick="doCopy('code80811')"> 代码如下:<div class="codebody" id="code80811">
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 执行<SPAN style="COLOR: #000000; BACKGROUND-COLOR: #ffff99"> select from Orders 的查询开销及查询计划
　　　　可以看到执行的聚集索引扫描，逻辑读22次，没有使用索引，返回行数830行
　　　　

<IMG alt="" src="https://www.52php.cn/res/2019/01-03/22/65d064d0d8e12da3b0b3f81c4f00d520.png"&gt;<IMG height=150 alt="" src="https://www.52php.cn/res/2019/01-03/22/7e64a47751c32fd098c24e2b440c4a0a.png"&gt;