SQL Server 数据库索引其索引的小技巧

一、什么是索引

减少磁盘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 当查询所有引用列（来自一个表）可以安全地容纳非聚簇索引中时。
在不需要跳转到数据行的情况下，非聚簇索引的性能应该和聚簇索引一样好（甚至更好）。非聚簇索引键包含所有表中需要的列是有可能的。