Sql Server 查询性能优化之走出索引的误区解析[MSSQL防范]
本文“Sql Server 查询性能优化之走出索引的误区解析[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
据理解绝大大都开辟人员关于索引的理解都是一知半解,范围于大大都平常工作没有机会、也什么没有必要去关心、理解索引,实在哪天某个查询太慢了找到查询条件建个索引就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
复制代码 代码以下:
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 | 堆积索引扫描 | 830 | 22 |
| 4.2 select * from Orders where OrderDate<='1996-7-10' | IX_OrderDate | 6 | 14 |
| 4.3 select * from Orders where OrderDate<='1997-1-1' | 堆积索引扫描 | 154 | 22 |
| 4.4 select * from Orders with(index=IX_OrderDate) where OrderDate<='1997-1-1' | 强迫利用IX_OrderDate | 154 | 328 |
| 4.5 select * from Orders with(index=IX_OrderDate) where OrderDate<='2001-1-1' | 强迫利用IX_OrderDate | 830 | 1724 |
通过比较以上查询我们可以知道固然我们成立了索引,但索引并不老是有效,强迫利用索引只会带来更低的效率,查询优化器会按照索引列的统计信息自动挑选最优的查询筹划举行履行.查询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表
此文章属怠惰的肥兔原创
以上是“Sql Server 查询性能优化之走出索引的误区解析[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“Sql Server 查询性能优化之走出索引的误区解析”更多相关内容
中查找“Sql Server 查询性能优化之走出索引的误区解析”更多相关内容