SQLServer索引效率分析

1、    sqlserver索引效率分析    王桃群摘要：数据库系统性能不够理想的原因之一是存在大量性能低下的sql语句，例如缺乏高效的索引，导致语句执行速度慢以及大量的磁盘读写操作。解决这类问题的主要办法是优化这些没有索引或索引不够合理的sql语句。该文以关系数据库sql server为例，通过案例分析了索引的执行效率。关键词：聚集索引；非聚集索引；覆盖索引；索引扫描；索引查找：tp311 ：a ：1009-3044（2017）26-0011-02优化sql语句的关键在于尽可能减少语句的逻辑读取次数。逻辑读取次数越少，需要的内存和cpu时间也就越少，语

2、句的执行速度也就越快。索引的最大好处就是可以极大地减少sql语句的逻辑读取次数，从而减少语句的执行时间。逻辑读取包含该语句从内存数据缓冲区中访问的页数和从物理磁盘读取的页数，而物理读取表示那些没有驻留在内存缓冲区中需要从磁盘读取的数据页。预读是sql server为了提高性能而多读取一些数据。优化sql语句时重在关注逻辑读取次数，如果物理读取或预读的次数较大，往往意味着在磁盘io上消耗更多的时间。通过sql server中的set statistics io on命令可以查看sql语句执行时的逻辑读取（logical reads）、物理读取（physical reads）和预读（read-ah

3、ead reads）等信息。1 单字段索引、组合索引和覆盖索引单字段索引是指索引中只包含一个字段；组合索引是指索引中包含有多个字段；覆盖索引又称为索引覆盖，是指索引包含查询引用的所有列。（1） 单字段索引对于单字段索引，若查询语句的where子句中不包含索引字段，则系统会对整个表进行扫描，全表扫描的性能通常都很差，尤其是在表中记录很多的情况下。为此，可对在查询中经常用到的字段创建索引。这样，全表扫描就变成了索引查找，通常情况下，索引查找需要的逻辑读取次数比表扫描要少得多，从而提高查询效率。（2） 组合索引如果查询语句的where字句中有多个字段，可以考虑创建组合索引。组合索引中字段的顺序是非常

4、重要的，记录唯一性高的尽量靠前，这样可以降低索引扫描时的io成本。（3） 覆盖索引在sql server中，每一个从非聚集索引取回的行都可以查找聚集索引中剩余行的值，這个操作称之为书签查找（bookmark lookup）。书签指向堆或聚集索引中的行。sql server严格地为非聚集索引中的每一行都存储了书签，这样，在基本表中就可以找到非聚集索引所对应的行。覆盖索引是指索引的叶子节点已包含所有要查询的列，因此不需要访问表数据，只需要访问索引即可得到所有数据。聚集索引的叶子节点就是数据，不存在覆盖的概念，覆盖索引主要是针对非聚集索引。查询语句的查询列表中含有不包含在非聚集索引中的字段，为得到一

5、行数据至少需要两次io，一次访问索引，一次访问基本表。故而查询列表中的选择列应尽可能选取索引覆盖的列，这样可以直接返回结果，避免昂贵的bookmark lookup操作，减少io的次数，从而提高查询的性能。任何非聚集索引列都不需要覆盖创建了聚集索引的列，因为创建聚集索引的列是非聚集索引集合列的一部分，也就是说，只要一个表上的列创建了聚集索引，那么非聚集索引集合列就包含了这个聚集索引。创建覆盖索引时也不能包含过多的列，因为覆盖索引列的值是存储在内存中的，这样会消耗过多的内存，也会导致性能下降。2 表扫描、索引扫描和索引查找sql server数据库主要有3种方法查找数据，分别是表扫描（table

6、 scan）、索引扫描（index scan）和索引查找（index seek）。（1） 索引查找（index seek）：sql server 沿着索引树从根级向下进行索引键值匹配搜索，直到搜索到指定的行，然后使用存储在索引键值中的书签值直接从数据页中检索匹配的数据行。（2） 索引扫描（index scan）：sql server搜索索引树中所有叶级中的行来进行可能匹配的查找。如果发现满足匹配的行，然后利用书签检索数据行。（3） 表扫描：sql server数据库从数据表，可能是堆表或者聚集索引的叶子页从头到尾进行扫描来查找数据。如果是数据表上已建立聚集索引，此时相当于索引扫描。尽管前两者都

7、使用了索引，从i/o代价角度来讲，索引扫描比索引查找的代价要高，但比表扫描要略微要小些。3 案例分析下面通过一个案例来演示索引的执行情况。首先，创建一个测试表test，该表包含三个int类型的字段，对于表中每个元组，这三个字段的取值相同。接着，分别在这三个字段上建立聚集索引、非聚集索引和无索引。然后，通过不同的查询语句来查看语句执行时的逻辑读取次数、物理读取次数、预读次数和执行时间等信息。为获取语句的执行时间，可在语句执行之前先获取系统当前时间，语句执行完毕后再次获取系统当前时间，两次系统当前时间之差即为语句执行所需的时间。测试代码如下。语句执行前设置set statistics io on并

8、开启执行计划。上述四条查询语句的执行结果如图1所示。从图1可见，有索引的语句花费的时间极短，无索引的语句花费的时间相对较长，差距明显。对于逻辑读取次数，无索引语句的逻辑读取次数是有索引语句的很多倍，这正是语句执行效率差异的直接原因。限于sql server时间类型的精度（3毫秒），覆盖索引在执行时间上看不到差异，但无覆盖索引的逻辑读取次数是有索引的2倍，随着表中记录数的增加和查询的复杂，差距会更加明显。上述四条查询语句的执行计划如图2所示。从图2可见，包括无索引查询在内的每个查询都使用了索引。不同之处在于，尽管无索引查询中使用了表中的聚集索引，但是对这个聚集索引使用的是索引扫描，其他几个语句均

9、使用的是索引查找。索引扫描是从开头扫描到结尾，几乎等同于表扫描。索引查找会先定位到索引的局部位置，然后再开始查找，不用把整个索引全部扫描一遍，在效率上比索引扫描快很多。4 结束语合理地使用索引能显著提高语句的执行效率，除了应该回避不适合建立索引的原则以外，索引并不是越多越好。因为索引要占用表空间，还需要额外的维护，这对数据库而言都是巨大的负担。建立索引是典型的以空间换时间的做法，在设计索引时需要在空间与时间两者间进行适当权衡。参考文献：1 对sql server数据库中三种查找数据方案的比较. http：/2 赵爱涛.合理创建索引 实现数据库访问的优化.石家庄职业技术学院学报，2013，25（4）：24-26.endprint电脑知识与技术2017年2