SQL Server 数据库索引其索引的小技巧
2024-08-31 00:57:22
供稿：网友
一、什么是索引
减少磁盘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语句可能有帮助。
二、索引设计建议
索引设计建议如下：
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.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) 太多并行的顺序插入：
如果想并行插入新行，那么把它们分布在多个页面中会更好，有聚簇索引的话，所有插入都会集中在最后一页，形成巨大的“热点”，可以通过创建另一列上的索引（该索引不会将行按照新行相同的顺序来排序）来将插入操作随机分布在整个表，这个问题只在大量的同时插入时发生。如果磁盘热点成为性能瓶颈，那么可以通过降低表的填充因子来容纳到中间页面。这样热的页面将在内存中，也有利于性能。
四、非聚簇索引
非聚簇索引不影响表页面中数据的顺序，对于堆表，行定位器指向数据行的RID的指针。对于非堆表，指向聚簇索引的索引键。
4.1、非聚簇索引维护：
为优化维护开销，SQLServer添加一个指向旧数据页的指针，以在页面分割之后指向新的数据页面，而不是更新所有相关非聚簇索引的行定位器。将聚簇索引作为行定位器降低了非聚簇索引相关的开销。
4.2、定义书签查找：
当查询请求不是优化器选择的非聚簇索引一部分时，需要一个查找，这对一个聚簇索引来说是一个关键字查找，对堆表来说是一个RID查找。成为：书签查找。
这种查找根据索引行的行定位器值，从表中读取对应的数据行，除了索引页面上的逻辑读操作以外，还需要一个数据页面的逻辑读。但是如果查询需要列中的索引，那么不需要访问数据页面，这种叫做【覆盖索引】，这些书签查找是大结果集最好使用聚簇索引的原因。聚簇索引不需要书签查找，因为叶子页面和数据页面相同。
4.3、非聚簇索引建议：
1. 何时使用非聚簇索引：
在需要从一个大表中读取少量行时最有效。随着行数增多，书签查找的开销成比例增加。索引列应该有很高的选择性。
有一些索引需求不适合于聚簇索引：
l 频繁更新的列
l 宽关键字
2. 何时不使用非聚簇索引：
非聚簇索引不适合检索大量行的查询。此时使用聚簇索引更好。因为不需要单独的书签查找来检索数据行。如果需要从表上读取大量的结果集，那么在过滤和连接条件中的非聚簇索引没有帮助，除非使用非聚簇索引——覆盖索引。
五、聚簇索引VS 非聚簇索引
选择聚簇索引或非聚簇索引主要考虑因素：
l 检索的行数量；
l 数据排序需求；
l 索引键宽度；
l 列更新频度；
l 书签开销；
l 任何磁盘热点；
5.1、聚簇索引相对非聚簇索引的好处：
在没有索引的表上选择索引的类型时，聚簇索引通常是首选。
尽量使用具有高选择性的列读取小的结果集是该列上创建非聚簇索引很好的启示，但在同意列上的聚簇索引可能同样有利甚至更好。
注意：尽管许多数据检索中聚簇索引胜过非聚簇索引，但是一个表只有一个聚簇索引，因此，应当将聚簇索引保留在最有力的情况下。
5.2、非聚簇索引相对聚簇索引的好处：
非聚簇索引在以下情况优先于聚簇索引：
l 索引键尺寸很大。
l 为了避免聚簇索引重建时需要重建所有非聚簇索引的相关开销。
l 是数据库读取程序工作于非聚簇索引页面上，同时写入程序对数据页面中的其他列（不包括非聚簇索引中）进行修改以避免阻塞。
l 当查询所有引用列（来自一个表）可以安全地容纳非聚簇索引中时。
在不需要跳转到数据行的情况下，非聚簇索引的性能应该和聚簇索引一样好（甚至更好）。非聚簇索引键包含所有表中需要的列是有可能的。
六、高级索引技术
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.1、全文索引：
对文本型的字段索引
7.2、空间索引：
对于空间类型的数据进行索引
7.3、XML：
从2005引入XML后，对XML类型
八、索引的附件特性
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、考虑数据库引擎调整顾问
九、小结
为了决定特殊查询的索引键列，需要评估查询的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中选择重新组织数据和数据页，这将停止旧有索引并按特定的填充因子重建索引。
