Sql Server 查询性能优化之走出索引的误区分析
来源:jb51 发布时间:2012-05-22 18:56:52
据了解绝大多数开发人员对于索引的理解都是一知半解,局限于大多数日常工作没有机会、也什么没有必要去关心、了解索引,实在哪天某个查询太慢了找到查询条件建个索引就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对数据读取的最小单位就是页,哪怕读取一条数据也需要读取整页数据,而非聚集索引的读是随机读哪怕多条记录在同一页上也会导致多次重复读取,外加书签查找导致了这么多的逻辑读,这也是为什么非聚集索引不适合读取大量数据的原因之一。
猜你喜欢
- 本文实例讲述了Python实现对象转换为xml的方法。分享给大家供大家参考,具体如下:# -*- coding:UTF-8 -*-'
- 下面是我已经证实可用的自动备份的方法. 1、打开企业管理器->管理->sql server代理 2、新建一个作业,作业名称随便取
- mysql安装目录使用MySQL AB's Linux RPM分发进行安装后,将在以下系统目录产生文件目录目录内容/usr/bin客
- 本篇文章通过调用opencv里的函数简单的实现了对图像里特定颜色提取与定位,以此为基础,我们可以实现对特定颜色物体的前景分割与定位,或者特定
- 发送端可以不停的发送新文件,接收端可以不停的接收新文件。例如:发送端输入:e:\visio.rar,接收端会默认保存为 e:\new_vis
- 在开始做mysql分页功能组件前,便设定的是要有一定可复用性。先在项目里Views文件夹下右键新建名为_PaginationComponen
- 1.int,float相互转换例1:int转float使用float(int)float转int使用int(float)# coding:u
- 1.字符串的字符转换1.1.字符转换的概念在前面说的的字符串替换,是将字符串中的一个子串替换成了新的子串,如果我们想对字符串中的某些字符进行
- 使用matplotlib绘图时,在弹出的窗口中默认是有工具栏的,那么这些工具栏是如何定义的呢?工具栏的三种模式matplotlib的基础配置
- tkinter 绘制GUI简单明了,制作一些简单的GUI足够,目前遇到的一个问题是不能同时排列显示多幅图片(目前没找到同时显示解决方法),退
- 批量修改: EXEC sp_MSforeachtable 'exec sp_changeob
- Gevent官网文档地址:http://www.gevent.org/contents.html进程、线程、协程区分我们通常所说的协程Cor
- 本文实例讲述了python使用chardet判断字符串编码的方法。分享给大家供大家参考。具体分析如下:最近利用python抓取一些网上的数据
- 想要实现自定义标签和过滤器需要进行准备工作:准备(必需)工作:1 在某个app下创建一个名为templatetags(必需,且包
- 前两篇讲述了Django的理论,从这篇开始,我们真正进入Django实战部分,今天先从用户认证开始。当大家平时打开一个网站时,第一步一般做什
- 最近在用GAE开发自己的博客程序。虽然GAE的API没有显式的提供操作Cookie的方法,但他现有的架构,使我们有足够的自由来操作Cooki
- Python中默认安装的ftplib模块定义了FTP类,其中函数有限,可用来实现简单的ftp客户端,用于上传或下载文件.FTP的工作流程及基
- 如下所示:import numpy as npimport matplotlib.pyplot as pltx = np.linspace(
- 一.问题描述在二维数组的遍历中,我们经常使用双层for循环。在某些时候,我们并不需要遍历整个二维数组。当条件满足时就应该终止for循环。但是
- 我们要生成二维码都需要借助一些类库来实现了,下面我介绍利用PHP QR Code生成二维码吧,生成方法很简单,下面我来介绍一下.利用php类