sqlserver性能优化之索引的使用和优化

╰半橙微兮° 2022-06-18 04:12 329阅读 0赞

# sqlserver性能优化之索引的使用和优化 #

在应用系统中,尤其在联机事务处理系统中,对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度也成为广大数据库用户所接受的优化方法。  
  
在良好的数据库设计基础上，能有效地使用索引是SQL Server取得高性能的基础，SQL Server采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查询执行的大部分开销是磁盘I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则查询只需读几次磁盘就可以了。所以如果建立了合理的索引，优化器就能利用索引加速数据的查询过程。但是，索引并不总是提高系统的性能，在增、删、改操作中索引的存在会增加一定的工作量，因此，在适当的地方增加适当的索引并从不合理的地方删除次优的索引，将有助于优化那些性能较差的SQL Server应用。实践表明，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最佳的优化方案。本文就SQL Server索引的性能问题进行了一些分析和实践。

## 一、聚簇索引(clustered indexes)的使用 ##

聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。建立聚簇索引的思想是：  
  
　　1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个高事务的环境中，对最后一页的封锁严重影响系统的吞吐量。  
  
　　2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、<、<=、>、>=)或使用group by或order by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。  
  
　　3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如IDENTITY)上，否则会经常引起封锁冲突。  
  
　　4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。  
  
　　5、选择聚簇索引应基于where子句和连接操作的类型。聚簇索引的侯选列是：  
  
　　　　1、主键列,该列在where子句中使用并且插入是随机的。  
2、按范围存取的列，如pri\_order > 100 and pri\_order < 200。  
　　　　3、在group by或order by中使用的列。  
　　　　4、不经常修改的列。  
　　　　5、在连接操作中使用的列。

## 二、非聚簇索引(nonclustered indexes)的使用 ##

SQL Server缺省情况下建立的索引是非聚簇索引，由于非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺序。在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外，还要考虑这些问题：  
  
　　1、索引需要使用多少空间。  
  
　　2、合适的列是否稳定。  
  
　　3、索引键是如何选择的，扫描效果是否更佳。  
  
　　4、是否有许多重复值。  
  
　　对更新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言，指向该数据的每个非聚簇索引的页级行也必须更新，有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销，另外，删除进程还必须把数据移到页面上部，以保证数据的连续性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况:   
  
　　1、某列常用于集合函数(如Sum,....)。  
  
　　2、某列常用于join,order by,group by。  
  
　　3、查寻出的数据不超过表中数据量的20%。

##    三、覆盖索引(covering indexes)的使用 ##

覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引，这种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页。如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。  
  
　　但是由于覆盖索引的索引项比较多,要占用比较大的空间。而且update操作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。

##     ##

## 四、索引的选择技术 ##

p\_detail是住房公积金管理系统中记录个人明细的表，有890000行，观察在不同索引下的查询运行效果，测试在C/S环境下进行，客户机是IBM PII350(内存64M),服务器是DEC Alpha1000A(内存128M),数据库为SYBASE11.0.3。  
  
查询1、select count(\*) from p\_detail where op\_date>’19990101’ and op\_date<’19991231’ and pri\_surplus1>300   
  
查询2、select count(\*),sum(pri\_surplus1) from p\_detail where op\_date>’19990101’ and pay\_month between‘199908’ and’199912’  
  
不建任何索引  
（查询1） 1分15秒  
（查询2） 1分7秒  
  
在op\_date上建非聚簇索引  
（查询1） 57秒  
（查询2） 57秒  
  
在op\_date上建聚簇索引  
（查询1） <1秒  
（查询2） 52秒  
  
在pay\_month、op\_date、pri\_surplus1上建索引  
（查询1） 34秒  
（查询2） <1秒  
  
在op\_date、pay\_month、pri\_surplus1上建索引  
（查询1） <1秒  
（查询2） <1秒  
  
　　从以上查询效果分析，索引的有无，建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件,这些条件体现于where从句和join表达式中。一般来说建立索引的思路是：  
  
　　(1)、主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。  
  
　　(2)、有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。  
  
　　(3)、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。  
  
　　(4)、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。  
  
　　(5)、在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把fillfactor置为100。  
  
　　(6)、在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。

## 五、索引的维护 ##

上面讲到,某些不合适的索引影响到SQL Server的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将会影响到索引的使用。这时需要用户自己来维护索引。索引的维护包括：  
  
　　1、重建索引  
  
　　随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：  
  
　　(1)、数据和使用模式大幅度变化。  
  
　　(2)、排序的顺序发生改变。  
  
　　(3)、要进行大量插入操作或已经完成。  
  
　　(4)、使用大块I/O的查询的磁盘读次数比预料的要多。  
  
　　(5)、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。  
  
　　(6)、dbcc检查出索引有问题。  
  
　　当重建聚簇索引时,这张表的所有非聚簇索引将被重建.  
  
　　2、索引统计信息的更新  
  
　　当在一个包含数据的表上创建索引的时候，SQL Server会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行update statistics命令：  
  
　　(1)、数据行的插入和删除修改了数据的分布。  
  
　　(2)、对用truncate table删除数据的表上增加数据行。  
  
　　(3)、修改索引列的值。

## 六、结束语 ##

实践表明，不恰当的索引不但于事无补，反而会降低系统的执行性能。因为大量的索引在插入、修改和删除操作时比没有索引花费更多的系统时间。例如下面情况下建立的索引是不恰当的：  
  
　　1、在查询中很少或从不引用的列不会受益于索引，因为索引很少或从来不必搜索基于这些列的行。  
  
　　2、只有两个或三个值的列，如男性和女性(是或否)，从不会从索引中得到好处。  
  
　　另外，鉴于索引加快了查询速度，但减慢了数据更新速度的特点。可通过在一个段上建表，而在另一个段上建其非聚簇索引，而这两段分别在单独的物理设备上来改善操作性能。  
  
影响SQL server性能的关键三个方面  
  
一、逻辑数据库和表的设计数据库的逻辑设计、包括表与表之间的关系是优化关系型数据库性能的核心。一个好的逻辑数据库设计可以为优化数据库和应用程序打下良好的基础。  
　　标准化的数据库逻辑设计包括用多的、有相互关系的窄表来代替很多列的长数据表。下面是一些使用标准化表的一些好处。  
　　A:由于表窄，因此可以使排序和建立索引更为迅速  
　　B:由于多表，所以多镞的索引成为可能  
　　C:更窄更紧凑的索引  
　　D:每个表中可以有少一些的索引，因此可以提高insert update delete等的速度，因为这些操作在索引多的情况下会对系统性能产生很大的影响  
  
　　E:更少的空值和更少的多余值，增加了数据库的紧凑性  
  
　　由于标准化，所以会增加了在获取数据时引用表的数目和其间的连接关系的复杂性。太多的表和复杂的连接关系会降低服务器的性能，因此在这两者之间需要综合考虑。定义具有相关关系的主键和外来键时应该注意的事项主要是：用于连接多表的主键和参考的键要有相同的数据类型。

二、索引的设计

A:尽量避免表扫描检查你的查询语句的where子句，因为这是优化器重要关注的地方。包含在where里面的每一列（column)都是可能的侯选索引，为能达到最优的性能，考虑在下面给出的例子：对于在where子句中给出了column1这个列。下面的两个条件可以提高索引的优化查询性能！第一：在表中的column1列上有一个单索引第二：在表中有多索引，但是column1是第一个索引的列避免定义多索引而column1是第二个或后面的索引，这样的索引不能优化服务器性能例如：下面的例子用了pubs数据库。  
SELECT au\_id, au\_lname, au\_fname FROM authors WHERE au\_lname = 'White' 按下面几个列上建立的索引将会是对优化器有用的索引  
　　au\_lname  
　　au\_lname, au\_fname而在下面几个列上建立的索引将不会对优化器起到好的作用  
　　au\_address  
　　au\_fname, au\_lname考虑使用窄的索引在一个或两个列上，窄索引比多索引和复合索引更能有效。用窄的索引，在每一页上将会有更多的行和更少的索引级别（相对与多索引和复合索引而言），这将推进系统性能。对于多列索引，SQL Server维持一个在所有列的索引上的密度统计（用于联合）和在第一个索引上的  
histogram（柱状图）统计。根据统计结果，如果在复合索引上的第一个索引很少被选择使用，那么优化器对很多查询请求将不会使用索引。有用的索引会提高select语句的性能，包括insert,uodate,delete。但是，由于改变一个表的内容，将会影响索引。每一个insert,update,delete语句将会使性能下降一些。实验表明，不要在一个单表上用大量的索引，不要在共享的列上（指在多表中用了参考约束）使用重叠的索引。在某一列上检查唯一的数据的个数，比较它与表中数据的行数做一个比较。这就是数据的选择性，这比较结果将会帮助你决定是否将某一列作为侯选的索引列，如果需要，建哪一种索引。你可以用下面的查询语句返回某一列的不同值的数目。  
　　select count(distinct cloumn\_name) from table\_name假设column\_name是一个10000行的表，则看column\_name返回值来决定是否应该使用，及应该使用什么索引。  
Unique values Index  
5000 Nonclustered index  
20 Clustered index  
3 No index  
　　镞索引和非镞索引的选择  
　　<1:>镞索引是行的物理顺序和索引的顺序是一致的。页级，低层等索引的各个级别上都包含实际的数据页。一个表只能是有一个镞索引。由于update,delete语句要求相对多一些的读操作，因此镞索引常常能加速这样的操作。在至少有一个索引的表中，你应该有一个镞索引。在下面的几个情况下，你可以考虑用镞索引：例如：某列包括的不同值的个数是有限的（但是不是极少的）顾客表的州名列有50个左右的不同州名的缩写值，可以使用镞索引。例如：对返回一定范围内值的列可以使用镞索引，比如用between,>,>=,<,<=等等来对列进行操作的列上。  
select \* from sales where ord\_date between '5/1/93' and '6/1/93'例如：对查询时返回大量结果的列可以使用镞索引。  
  
　　SELECT \* FROM phonebook WHERE last\_name = 'Smith'  
  
　　当有大量的行正在被插入表中时，要避免在本表一个自然增长（例如，identity列）的列上建立镞索引。如果你建立了镞的索引，那么insert的性能就会大大降低。因为每一个插入的行必须到表的最后，表的最后一个数据页。当一个数据正在被插入（这时这个数据页是被锁定的），所有的其他插入行必须等待直到当前的插入已经结束。一个索引的叶级页中包括实际的数据页，并且在硬盘上的数据页的次序是跟镞索引的逻辑次序一样的。   
  
　　<2:>一个非镞的索引就是行的物理次序与索引的次序是不同的。一个非镞索引的叶级包含了指向行数据页的指针。在一个表中可以有多个非镞索引，你可以在以下几个情况下考虑使用非镞索引。在有很多不同值的列上可以考虑使用非镞索引例如：一个part\_id列在一个part表中select \* from employee where emp\_id = 'pcm9809f'查询语句中用order by子句的列上可以考虑使用镞索引   
　　三、查询语句的设计  
　　SQL Server优化器通过分析查询语句，自动对查询进行优化并决定最有效的执行方案。优化器分析查询语句来决定那个子句可以被优化，并针对可以被优化查询的子句来选择有用的索引。最后优化器比较所有可能的执行方案并选择最有效的一个方案出来。在执行一个查询时，用一个where子句来限制必须处理的行数，除非完全需要，否则应该避免在一个表中无限制地读并处理所有的行。例如下面的例子，select qty from sales where stor\_id=7131是很有效的比下面这个无限制的查询select qty from sales避免给客户的最后数据选择返回大量的结果集。允许SQL Server运行满足它目的的函数限制结果集的大小是更有效的。这能减少网络I/O并能提高多用户的相关并发时的应用程序性能。因为优化器关注的焦点就是where子句的查询，以利用有用的索引。在表中的每一个索引都可能成为包括在where子句中的侯选索引。为了最好的性能可以遵照下面的用于一个给定列column1的索引。第一：在表中的column1列上有一个单索引第二：在表中有多索引，但是column1是第一个索引的列不要在where子句中使用没有column1列索引的查询语句，并避免在where子句用一个多索引的非第一个索引的索引。这时多索引是没有用的。  
For example, given a multicolumn index on the au\_lname, au\_fname columns of the authors table in   
the pubs database,下面这个query语句利用了au\_lname上的索引  
SELECT au\_id, au\_lname, au\_fname FROM authors  
WHERE au\_lname = 'White'  
AND au\_fname = 'Johnson'  
SELECT au\_id, au\_lname, au\_fname FROM authors  
WHERE au\_lname = 'White'下面这个查询没有利用索引，因为他使用了多索引的非第一个索引的索引  
SELECT au\_id, au\_lname, au\_fname FROM authors

WHERE au\_fname = 'Johnson'

\--- end ---