日期：2012-06-27 04:59:00 来源：本站整理
SQL Server 数据库索引其索引的小本领[MSSQL防范]
本文“SQL Server 数据库索引其索引的小本领[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
1、什么是索引
削减磁盘I/O和逻辑读次数的最佳办法之一就是利用【索引】
索引答应SQL Server在表中查找数据而不需求扫描整个表.
1.1、索引的好处:
当表没有堆积索引时,成为【堆或堆表】
【堆】是一堆未加工的数据,以行标识符作为指向存储位置的指针.表数据没有次序,也不能搜索,除非逐行遍历.这个历程称为【扫描】.当存在堆积索引时,非堆积索引的指针由堆积索引所定义的值构成,所以堆积索引变得非常重要.
因为页面大小固定,所以列越少,所能存储的行就越多.由于非堆积索引普通不包含全部列,所以普通一个页面包含有更多的非堆积索引.所以SQLServer能从一个非堆积索引的页面中读到比包含该列的表也页面更多的值.
非堆积索引的另一个好处:独立于数据表的构造,可以放到差别的文件组,利用差别的I/O.
索引利用B-树作为存储构造,所以查询特定行所需的操作被最小化.
1.2、索引开销:
索引过量会惹起（INSERT/UPDATE/DELETE/CRUD中的CUD部份）耗费更长的时间.
在计划索引时,要从两个角度举行:
对现有的生产系统,需求丈量索引的总体影响,应保证性能带来的好处超越处理资源的额外本钱.可以利用Profiler工具举行整体工作负载优化.
当专注与索引立即带来的好处时,可以利用DMV查看:
Sys.dm_db_index_operational_stats或sys.dm_db_index_usage_stats
Sys.dm_db_index_operational_stats:显示正在利用的一个索引的初级活动,比方I/O和锁.
Sys.dm_db_index_usage_stats:随时发生咋一个索引中的各种操作的统计数字.
固然关于DML,保护索引所需求的开销会增添,但是,SQLServer在更新或删除之前必须首先找到一行,所以索引对利用复杂的where子句的update和delete语句大概有帮忙.
2、索引计划倡议
索引计划倡议以下:
l 查抄where子句和衔接条件列;
l 利用窄索引;
l 查抄列的唯一性;
l 查抄列的数据范例;
l 考虑列次序;
l 考虑索引范例（堆积索引VS 非堆积索引）
2.1、查抄where子句和衔接条件列:
当一个查询提交到SQLServer时,优化器会做以下步骤:
1) 优化器辨认WHERE子句和衔接条件中包含的列.
2) 接着优化器查抄这些列上的索引.
3) 优化器通过从索引上保护的统计肯定子句的挑选性（也就是返回多少行）评价每个索引的有效性.
4) 终究,优化器按照前面几个步骤中的汇集信息,预计读取所限定的行开销最低的办法.
当没有符合的where和衔接列时,优化器会做全表扫描.
倡议:在where子句或衔接条件中频繁利用的列上建索引,以避免表扫描.当一个表的数据总量非常小以至可以放入一个单独的页面（8KB）时,表扫描大概比索引查找工作得更好.
2.2、利用窄索引:
为了最好的性能,尽大概在索引中利用较少的列.还该当避免宽数据范例的列.
窄索引可以在8KB的索引页面中包容比宽索引更多的行,可以到达以下效果:
l 削减I/O数目（读取更少的8KB页面）
l 利用数据库缓存更有效,因为SQLServer可以缓存更少的索引页面,削减内存中索引页面所需的逻辑读操作.
l 削减数据库存储空间.
2.3、查抄列的唯一性:
在一个很小范围的大概值的列（如性别）上成立索引对性能没有好处.因为优化器不能利用索引有效地削减返回的行.因为小范围的值大概惹起【全表扫描】大概【堆积索引扫描】.使where子句中的列具有大量的唯一行（大概高挑选性）以限制拜候的行数始终是首选的筹划.应当在这些列上成立索引帮忙拜候小的后果集.
别的,关于成立在多个列上的索引时,次序是有关系的.在某些情形下,利用最有挑选性的列将是索引更有效.
2.4、查抄列数据范例:
对数值型建索引会很快,因为尺寸小,算术操作很简单.但是字符型尺寸大,且需求字符串匹配操作,普通开销更大.
2.5、考虑列次序:
复合索引中,列次序是索引效率的重要因素:
l 列唯一性;
l 列宽度;
l 列数据范例;
查询操纵了索引的前沿来履行查找操作以检索数据.把最有效的索引放到前沿,能尽快挑选数据.削减数据量.
2.6、考虑索引范例:
堆积索引和非堆积索引都以B-树存储数据.下面将具体介绍
3、堆积索引（聚簇索引）
聚簇索引的叶子页面和表的数据页面相同.因此表行物理上按照聚簇索引列排序,因为从物力上只能有一种物理次序,所以只有一个聚簇索引.
3.1、堆表:
没有聚簇索引的表叫堆表.数据列没有任何次序,衔接到表的相邻页面.与拜候非堆表相比,无组织的构造增大了拜候的开销.
3.2、与非聚簇索引的关系:
非聚簇索引的一个索引行包含指向表的对应数据行的指针.这个指针被称为【行定位器（row locator）】.它的值取决于数据页是保存在堆当中还是被聚合.关于非聚簇索引,行定位器指向堆中数据行的RID的指针.关于聚簇索引,行定位器是聚簇索引的索引键值.当有新数据行进入时,大概招致非聚簇索引重定位、分页等等,影响性能.
3.3、聚簇索引倡议:
1) 首先成立聚簇索引:
因为全部非聚簇索引在其索引行上保存聚簇索引键值,所以成立次序非常重要.为了最好的性能,倡议在成立任何非聚簇索引前成立聚簇索引.
2) 保持窄索引:
应保持聚簇索引总体的长度尽大概小.因为聚簇索引长度太大,那么非聚簇索引也会随着增大.因此,大的聚簇索引键值不但影响本身宽度,并且扩大表上的全部非聚簇索引,增添索引页面数目,增添逻辑读和磁盘I/O.
3) 一步重建聚簇索引:
由于聚簇索引和非聚簇索引关联,所以利用DROP INDEX再CREATE INDEX将招致非聚簇索引成立两次,此时可以利用CREATE INDEX 语句的DROP_EXISTING子句在一个单独的原子步骤中重建聚簇索引,类似地可以在非聚簇索引中利用.
4) 什么时刻利用一个聚簇索引:
a) 检索一定范围的数据:
由于聚簇索引是按物理次序成立,索引公道操纵能削减磁头的移动,削减物理I/O量.
b) 读取预先排序的数据:
关于需求排序的数据,聚簇索引非常有效,能削减数据读取后的排序开销.
关于读取大范围行和/或排序输出的查询,聚簇索引普通是比非聚簇索引更有效的挑选.
5) 什么时刻不利用聚簇索引:
在某些情形下最好不要利用聚簇索引:
a) 频繁更新的列:
假如列更新频繁,将招致非聚簇索引重新定位,增添相关操作查询的开销.还将阻塞这段时间引用相同部份和非聚簇索引的其他查询,从而影响数据并行性.
b) 宽的关键字:前面已经阐明缘由
c) 太多并行的次序插入:
假如想并行插入新行,那么把它们分布在多个页面中会更好,有聚簇索引的话,全部插入城市集合在最后一页,形成宏大的"热门",可以通过成立另一列上的索引（该索引不会将行按照新行相同的次序来排序）来将插入操作随机分布在整个表,这个问题只在大量的同时插入时发生.假如磁盘热门成为性能瓶颈,那么可以通过降低表的填充因子来包容到中间页面.这样热的页面将在内存中,也有利于性能.
4、非聚簇索引
非聚簇索引不影响表页面中数据的次序,关于堆表,行定位器指向数据行的RID的指针.关于非堆表,指向聚簇索引的索引键.
4.1、非聚簇索引保护:
为优化保护开销,SQLServer增添一个指向旧数据页的指针,以在页面分割之后指向新的数据页面,而不是更新全部相关非聚簇索引的行定位器.将聚簇索引作为行定位器降低了非聚簇索引相关的开销.
4.2、定义书签查找:
当查询恳求不是优化器挑选的非聚簇索引一部份时,需求一个查找,这对一个聚簇索引来说是一个关键字查找,对堆表来说是一个RID查找.成为:书签查找.
这种查找按照索引行的行定位器值,从表中读取对应的数据行,除了索引页面上的逻辑读操作以外,还需求一个数据页面的逻辑读.但是假如查询需求列中的索引,那么不需求拜候数据页面,这种叫做【覆盖索引】,这些书签查找是大后果集最好利用聚簇索引的缘由.聚簇索引不需求书签查找,因为叶子页面和数据页面相同.
4.3、非聚簇索引倡议:
1. 什么时刻利用非聚簇索引:
在需求从一个大表中读取少量行时最有效.随着行数增添,书签查找的开销成比例增添.索引列应当有很高的挑选性.
有一些索引需求不合适于聚簇索引:
l 频繁更新的列
l 宽关键字
2. 什么时刻不利用非聚簇索引:
非聚簇索引不合适检索大量行的查询.此时利用聚簇索引更好.因为不需求单独的书签查找来检索数据行.假如需求从表上读取大量的后果集,那么在过滤和衔接条件中的非聚簇索引没有帮忙,除非利用非聚簇索引——覆盖索引.
5、聚簇索引VS 非聚簇索引
挑选聚簇索引或非聚簇索引主要考虑因素:
l 检索的行数目;
l 数据排序需求;
l 索引键宽度;
l 列更新频度;
l 书签开销;
l 任何磁盘热门;
5.1、聚簇索引相对非聚簇索引的好处:
在没有索引的表上挑选索引的范例时,聚簇索引普通是首选.
尽大概利用具有高挑选性的列读取小的后果集是该列上成立非聚簇索引很好的启迪,但在赞成列上的聚簇索引大概一样有利乃至更好.
注意:固然许大都据检索中聚簇索引赛过非聚簇索引,但是一个表只有一个聚簇索引,因此,该当将聚簇索引保存在最有力的情形下.
5.2、非聚簇索引相对聚簇索引的好处:
非聚簇索引在以下情形优先于聚簇索引:
l 索引键尺寸很大.
l 为了避免聚簇索引重建时需求重建全部非聚簇索引的相关开销.
l 是数据库读取程序工作于非聚簇索引页面上,同时写入程序对数据页面中的其他列（不包含非聚簇索引中）举行改正以避免阻塞.
l 当查询全部引用列（来自一个表）可以安全地包容非聚簇索引中时.
在不需求跳转到数据行的情形下,非聚簇索引的性能应当和聚簇索引一样好（乃至更好）.非聚簇索引键包含全部表中需求的列是有大概的.
6、高级索引技术
l 覆盖索引:
l 索引穿插:利用多个非聚簇索引以满意查询的全部列需求（来自一个表）
l 索引衔接:利用索引穿插和覆盖索引技术来避免触及基本表.
l 过滤索引:为了可以索引具有零星数据分布的字段大概稀疏的列,可以在索引上利用过滤,这样它只索引一些数据.
l 索引视图:在磁盘上将视图输出实体化
6.1、覆盖索引:
在全部为满意SQL查询不用到达底子表所需的列上成立非聚簇索引.假如查询碰到一个索引并且完好不需求引用底层数据表,那么该索引可以被认为是覆盖索引.利用INCLUDE操作符使索引编程覆盖索引,浙江存储数据和索引而不需求改正索引构造本身.
覆盖索引本身关于削减逻辑读是一种游泳的技术.在以下情形利用最好:
l 你不但愿增添索引键的大小,但仍旧但愿有一个覆盖索引;
l 你打算索引一种不能被索引的数据范例（除了文本、ntext和图象）;
l 你已经超越了一个索引的关键字列的最大数目（但是最好避免这个问题）.
1、 伪聚簇索引（Pseudoclustered index）:
覆盖索引物理上次序地组织全部索引列.从I/O角度看,没有利用包含列的覆盖索引编程一种聚簇索引,用于全部完好满意于覆盖索引中列的查询.假如查询后果集需求排序,那么覆盖索引可以用于物理地按照后果集所需的次序保护列数据.
2、 倡议:
操纵覆盖索引,要注意SELECT语句中的列清单.应尽大概利用较少的列来保持小的覆盖索引键尺寸.假如索引中全部列的字节数相比表的单个数据行来说较小,并且肯定操纵覆盖索引的查询常常履行,那么覆盖索引是有效的.
在成立很多覆盖索引之前,考虑SQLServer若何有效和自动地利用索引穿插为查询当即成立覆盖索引.
6.2、索引穿插:
假如一个表有很多索引,那么SQLServer可以利用多个索引来履行一个查询.按照每个索引挑选小的数据子集,然后履行两个子集的穿插（即只返回满意全部条件的那些行）
但在实际世界中,改正现有索引时要考虑以下问题:
l 因为各种缘由,大概不答应改正现有索引;
l 现有非聚簇索引键大概已经相当宽;
l 利用现有索引的查询开销将被这个改正所影响.
为了增长一个查询的性能,SQLServer可以在表上利用多个索引,因此,考虑成立多个窄索引替换宽的索引键.
有时刻,大概必须为以下缘由成立一个单独的非聚簇索引:
l 重新布列现有索引中的列不被答应;
l 覆盖索引所需求的一些列不能被包含在现有的非聚簇索引中;
l 两个现有非聚簇索引中的总列数大概多余覆盖索引所需求的列数;
在这些情形下,可以在剩下的列上成立非聚簇索引.
6.3、索引衔接:
索引衔接是索引穿插的变种,将覆盖索引技术利用到索引穿插.假如没有单个覆盖查询的索引而多个索引一齐可以覆盖该查询.SQLServer可以利用索引衔接完好满意查询而不需求转到基本表.
6.4、过滤索引:
是利用过滤器的非聚簇索引,基本上上一个where子句.用俩在大概没有很好挑选性的一个或多个列上成立一个高挑选性的关键字组.关于大量null值时对比实用.
过滤索引在很多方面带往复报:
l 削减索引尺寸从而增长查询效率.
l 成立更小的索引降低存储开销;
l 因为尺寸削减,降低了索引保护的本钱.
过滤索引需求在拜候大概成立时的一组特别ANSI设置:
ON:ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,ARITHABORT,CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER
OFF:NUMERIC_ROUNDABORT
6.5、索引视图:
SQLServer可以在视图上成立唯一的聚簇索引来磁盘上实体化.这样的索引成为索引视图或实体化视图.在成立今后可以成立非聚簇索引.
1、 好处:
l 聚合可以预先计算并被保存在索引视图中,以在查询履行期间最小化高贵的计算;
l 表可以预先衔接,后果集可以实物化;
l 衔接或聚合的构成可以被实物化.
2、 开销:
l 基本表中的任何改正必须履行事件的select语句反映到索引视图中;
l 对索引视图定义的基本表上的任何改正大概发动索引视图的非聚簇索引中的改正,假如聚簇键被更新,聚簇索引也将必须更新;
l 索引视图增添数据库的保护开销;
l 数据库中需求更多的存储;
成立索引视图包含以下限制:
l 视图的第一个索引必须是唯一聚簇索引.
l 索引视图上的非聚簇索引只可以在唯一聚簇索引成立之后成立.
l 视图定义必须是肯定性的——即,它对一个给定的查询只能返回一个大概的后果;
l 索引视图必须只引用相同数据库中的基本表,而不是其他视图;
l 索引视图可以包含浮点列但是这样的列不能包含在聚簇索引键中;
l 索引视图必须是绑定到列所引用表的一个架构,免得表架构的改正;
l 视图定义的语法有很多限制
l 必须肯定的SET选项列表:
ON:ARITHABORT,CONCAT_NULL_YIELDS_NULL,ANSI_NULLS,ANSI_PADDING和ANSI_WARNING
OFF:NUMERIC_ROUNDABORT
3、 利用环境:
OLAP能从索引视图中获益,OLTP就对比难从中获益.
6.6、索引压缩:
从2008引入.压缩索引能造成庞大性能改良,但是也会造成CPU和内存开销.不是合适全部索引的筹划.
默许情形下,索引不会被压缩.必须明确地在成立索引时要求索引被压缩.分为行级和页级压缩.索引中的非叶子页面不承受页面范例下的压缩.
7、特别索引范例
7.1、全文索引:
对文本型的字段索引
7.2、空间索引:
关于空间范例的数据举行索引
7.3、XML:
从2005引入XML后,对XML范例
8、索引的附件特点
8.1、差别的列排序次序:
可对一个索引中的差别摆列行升降序布列.
8.2、在计算列上的索引:
可以在计算列上成立索引,只要计算列的表达式符合一定的限制,比方根源表是肯定的.
8.3、BIT数据范例列上的索引:
成立在BIT数据列上的索引本身不是很好的长处,但是关于覆盖索引,当涵盖了BIT列时就很有效.
8.4、作为一个查询处理的CREATE INDEX语句:
8.5、并行索引成立:
可以在max degree of parallelism配置参数来掌握CREATE INDEX语句中的处理器数目,也可以利用exec sp_configure ‘maxdegree of parallelism'
8.6、在线索引成立:
可以在成立索引时削减锁的机会.
8.7、考虑数据库引擎调整顾问
9、小结
为了决意特别查询的索引键列,需求评价查询的WHERE子句和衔接条件.像列挑选性、宽度、数据范例和列次序这些因素.因为索引主如果为了检索少量行,所以索引挑选性必须非常高.
为了得到更好性能,尝试利用覆盖索引完好覆盖查询.
SQL Server数据库优化其索引的小本领
关于索引的常识:影响到数据库性能的最大因素就是索引.由于该问题的复杂性,我只大概简单的谈谈这个问题,不过关于这方面的问题,目前有好几本不错的书籍可供你参阅.我在这里只谈论两种SQL Server索引,即clustered索引和nonclustered索引.当观察成立什么范例的索引时,你该当考虑数据范例和保存这些数据的column.一样,你也必须考虑数据库大概用到的查询范例以及利用的最为频繁的查询范例.
索引的范例
假如column保存了高度相关的数据,并且常常被次序拜候时,最好利用clustered索引,这是因为假如利用clustered索引,SQL Server会在物理上按升序（默许）大概降序重排数据列,这样便可以疾速的找到被查询的数据.一样,在搜索掌握在一定范围内的情形下,对这些column也最好利用clustered索引.这是因为由于物理上重排数据,每个表格上只有一个clustered索引.
与上面情形相反,假如columns包含的数据相关性较差,你可以利用nonculstered索引.你可以在一个表格中利用高达249个nonclustered索引——固然我想象不出实际利用场所会用的上这么多索引.
当表格利用主关键字（primary keys）,默许情形下SQL Server会自动对包含该关键字的column(s)成立一个独有的cluster索引.很明显,对这些column(s)成立独有索引意味着主关键字的唯一性.当成立外关键字（foreign key）关系时,假如你打算频繁利用它,那么在外关键字cloumn上成立nonclustered索引不失为一个好的办法.假如表格有clustered索引,那么它用一个链表来保护数据页之间的关系.相反,假如表格没有clustered索引,SQL Server将在一个仓库中保存数据页.
数据页
当索引成立起来的时刻,SQLServer就成立数据页（datapage）,数据页是用以加快搜索的指针.当索引成立起来的时刻,其对应的填充因子也即被设置.设置填充因子的目的是为了指导该索引中数据页的百分比.随着时间的推移,数据库的更新会损耗掉已有的闲暇空间,这就会招致页被拆分.页拆分的后果是降低了索引的性能,因而利用该索引的查询会招致数据存储的支离破裂.当成立一个索引时,该索引的填充因子即被设置好了,因此填充因子不能动态保护.
为了更新数据页中的填充因子,我们可以终止旧有索引并重建索引,并重新设置填充因子（注意:这将影响到当前数据库的运行,在重要场所请谨严利用）.DBCC INDEXDEFRAG和DBCC DBREINDEX是排除clustered和nonculstered索引碎片的两个号令.INDEXDEFRAG是一种在线操作（也就是说,它不会阻塞别的表格行动,如查询）,而DBREINDEX则在物理上重建索引.在绝大大都情形下,重建索引可以更好的消除碎片,但是这个长处是以阻塞当前发生在该索引所在表格上别的行动为代价换取来得.当呈现较大的碎片索引时,INDEXDEFRAG会花上一段对比长的时间,这是因为该号令的运行是基于小的交互块（transactional block）.
填充因子
当你履行上述办法中的任何一个,数据库引擎可以更有效的返回编入索引的数据.关于填充因子（fillfactor）话题已经超越了本文的范围,不过我还是提醒你需求注意那些打算利用填充因子成立索引的表格.
在履行查询时,SQL Server动态挑选利用哪个索引.为此,SQL Server按照每个索引上分布在该关键字上的统计量来决意利用哪个索引.值得注意的是,经过平常的数据库活动（如插入、删除和更新表格）,SQL Server用到的这些统计量大概已经"过期"了,需求更新.你可以通过履行DBCC SHOWCONTIG来查看统计量的状况.当你认为统计量已经"过期"时,你可以履行该表格的UPDATE STATISTICS号令,这样SQL Server就革新了关于该索引的信息了.
成立数据库保护筹划
SQL Server供应了一种简化并自动保护数据库的工具.这个称之为数据库保护筹划向导（Database Maintenance Plan Wizard ,DMPW）的工具也包含了对索引的优化.假如你运行这个向导,你会看到关于数据库中关于索引的统计量,这些统计量作为日记工作并按时更新,这样就减轻了手工重建索引所带来的工作量.假如你不想自动按期革新索引统计量,你还可以在DMPW中挑选重新组织数据和数据页,这将终止旧有索引并按特定的填充因子重建索引.
以上是“SQL Server 数据库索引其索引的小本领[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“SQL Server 数据库索引其索引的小本领”更多相关内容
中查找“SQL Server 数据库索引其索引的小本领”更多相关内容评论内容只代表网友观点，与本站立场无关！
评论摘要(共 0 条，得分 0 分，平均 0 分)
查看完整评论