操纵SQL Server索引提高数据处理的效率[MSSQL防范]

　　在杰出的数据库计划底子上,能有效地利用索引是sql server获得高性能的底子,SQL Server采取基于代价的优化模子,它对每一个提交的有关表的查询,决意能否利用索引或用哪一个索引.因为查询履行的大部份开销是磁盘I/O,利用索引提高性能的一个主要目标是避免全表扫描,因为全表扫描需求从磁盘上读表的每一个数据页,假若有索引指向数据值,则查询只需读几次磁盘便可以了.

　　所以假如成立了公道的索引,优化器就可以操纵索引加快数据的查询历程.但是,索引并不老是提高系统的性能,在增、删、改操作中索引的存在会增添一定的工作量,因此,在得当的地方增添得当的索引并从不公道的地方删除次优的索引,将有助于优化那些性能较差的SQL Server利用.实践表明,公道的索引计划是成立在对各种查询的解析和猜测上的,只有精确地使索引与程序结合起来,才能产生最佳的优化筹划.本文就SQL Server索引的性能问题举行了一些解析和实践.

　　1、聚簇索引(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、在衔接操作中利用的列.

　　2、非聚簇索引(nonclustered indexes)的利用

　　SQL Server缺省情形下成立的索引是非聚簇索引,由于非聚簇索引不重新组织表中的数据,而是对每一行存储索引列值并用一个指针指向数据所在的页面.换句话说非聚簇索引具有在索引构造和数据本身之间的一个额外级.一个表假如没有聚簇索引时,可有250个非聚簇索引.每个非聚簇索引供应拜候数据的差别排序次序.在成立非聚簇索引时,要衡量索引对查询速度的加快与降低改正速度之间的利弊.别的,还要考虑这些问题:

　　1、索引需求利用多少空间.

　　2、符合的列能否安定.

　　3、索引键是若何挑选的,扫描效果能否更佳.

　　4、能否有很多反复值.

　　对更新频繁的表来说,表上的非聚簇索引比聚簇索引和根本没有索引需求更多的额外开销.对移到新页的每一行而言,指向该数据的每个非聚簇索引的页级行也必须更新,有时大概还需求索引页的分理.从一个页面删除数据的进程也会有近似的开销,别的,删除进程还必须把数据移到页面上部,以保证数据的持续性.所以,成立非聚簇索引要非常慎重.非聚簇索引常被用在以下情形:

　　1、某列常用于调集函数(如Sum,....).

　　2、某列常用于join,order by,group by.

　　3、查寻出的数据不超越表中数据量的20%.

　　3、覆盖索引(covering indexes)的利用

　　覆盖索引是指那些索引项中包含查寻所需求的全部信息的非聚簇索引,这种索引之所以对比快也恰是因为索引页中包含了查寻所必须的数据,不需去拜候数据页.假如非聚簇索引中包含后果数据,那么它的查询速度将快于聚簇索引.

　　但是由于覆盖索引的索引项对比多,要占用对比大的空间.并且update操作会惹起索引值改变.所以假如潜在的覆盖查询并不常用或不太关键,则覆盖索引的增添反而会降低性能.

　　4、索引的挑选技术

　　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)在挑选索引键时,设法挑选那些采取小数据范例的列作为键以使每个索引页可以包容尽大概多的索引键和指针,通过这种方法,可以使一个查询必须遍历的索引页面降到最小.此外,尽大概地利用整数为键值,因为它可以供应比任何数据范例都快的拜候速度.

　　5、索引的保护

　　上面讲到,某些不符合的索引影响到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)改正索引列的值.

　　6、完毕语

　　实践表明,不得当的索引不但于事无补,反而会降低系统的履行性能.因为大量的索引在插入、改正和删除操作时比没有索引耗费更多的系统时间.比方下面情形下成立的索引是不得当的:

　　1、在查询中很少或从不引用的列不会受益于索引,因为索引很少或历来没必要搜索基于这些列的行.

　　2、只有两个或三个值的列,如男性和女性(是或否),从不会从索引中得到好处.

　　别的,鉴于索引加快了查询速度,但减慢了数据更新速度的特点.可通过在一个段上建表,而在另一个段上建其非聚簇索引,而这两段辨别在单独的物理设备上来改进操作性能. 　　以上是“操纵SQL Server索引提高数据处理的效率[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：