1. 索引的结构与作用

1.1 索引概述

　　数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

　　索引的作用：

• 通过创建唯一索引，可以保证数据记录的唯一性。
• 可以大大加快数据检索速度
• 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。
• 在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。
• 使用索引可以在检索数据的过程中使用查询优化器，提高系统性能。

1.2 SQL Server索引下的数据组织结构

　　在 SQL Server 数据库内，索引对象作为 8 KB 页的集合存储。 索引页也有一个96字节的页头，但尾部没有行偏移数组。

　　SQL Server 支持视图上的索引。视图上第一个允许的索引是聚集索引。在视图上执行 CREATE INDEX 语句时，视图的结果集被具体化，并且存储在与有聚集索引的表具有相同结构的数据库中。

　　1. 堆集结构

　　堆集结构不按任何特殊顺序存储数据行，数据页序列也没有任何特殊顺序。数据页不在链表内链接。在这种结构中，记录按其插入的先后顺序存放。

　　堆集在 sysindexes 内有一行，其 indid = 0。sysindexes.FirstIAM 列指向 IAM 页链的 IAM 首页，IAM 页链管理分配给堆集的空间。数据页之间唯一的逻辑连接是记录在IAM页内的连接信息。

　　通过扫描 IAM 页可以对堆集进行表扫描或串行读，以找到容纳这个堆集的页的扩展盘区。因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们，所以这意味着串行堆集扫描一律沿每个文件进行。使用 IAM 页设置扫描顺序还意味着堆集中的行一般不按照插入的顺序返回。

　　堆集结构插入容易，但查找不便。因为它所提供的唯一存取路径就是顺序搜索或顺序扫描，这种操作称为表扫描。
　　堆集结构删除比较麻烦，因为涉及删除记录和空间回收的问题。一般删除时只删除标记，等删除的记录累计到一定数量后再集中清理。

　　2. 聚集索引结构

　　聚集索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它的叶节点中存储的是实际的数据。

　　每个表只能有一个聚集索引，因为聚集索引规定数据在表中的物理存储顺序，而数据行本身只能按一个顺序存储。

　　应当在创建任何非聚集索引之前创建聚集索引。
　　聚集索引的大小平均是表的5%。
　　在创建索引时系统需要被索引的表1.2倍的磁盘空间，索引创建完成后自动回收。

　　聚集索引在 sysindexes 内有一行，其 indid = 1。数据链内的页和其内的行按聚集索引键值排序。所有插入都在所插入行中的键值与排序顺序相匹配时执行。

　　SQL Server2000 将索引组织为 B 树。索引内的每一页包含一个页首，页首后面跟着索引行。每个索引行都包含一个键值以及一个指向较低级页或数据行的指针。索引的每个页称为索引节点。B 树的顶端节点称为根节点。索引的底层节点称为叶节点。每级索引中的页链接在双向链接列表中。在聚集索引内数据页组成叶节点。根和叶之间的任何索引级统称为中间级。

　　对于聚集索引，sysindexes.root 指向它的顶端。SQL Server 沿着聚集索引浏览以找到聚集索引键对应的行。

　　3. 非聚集索引结构

　　非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和行定位器。

　　如果索引时没有指定索引类型，默认情况下为非聚集索引；
　　应当在创建非聚集索引之前创建聚集索引；
　　每个表最多可以创建249个非聚集索引；
　　最好在唯一值较多的列上创建非聚集索引；
　　经常需要联接和分组查询，应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。

　　非聚集索引与聚集索引一样有 B 树结构，但是有两个重大差别：
　　　　1、数据行不按非聚集索引键的顺序排序和存储。
　　　　2、非聚集索引的叶层不包含数据页。 相反，叶节点包含索引行。

　　Server 中，非聚集索引中的行定位器有两种形式：
　　　　1、如果表是堆集（没有聚集索引），行定位器就是指向行的指针。该指针用文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID。
　　　　2、如果表有聚集索引，或者索引在索引视图上，则行定位器就是行的聚集索引键。

　　4. 扩展盘区空间的管理

　　索引分配映射表（IAM）页映射数据库文件中由堆集或索引使用的扩展盘区。对于任何具有ntext、text和image类型的列的表，IAM页还映射分配给这些类型的页链的扩展盘区。这些对象中的每一个都有由一个或多个记录所有分配给自己的扩展盘区的IAM页组成的链。每个对象对每个包含扩展盘区的文件都至少有一个IAM。如果分配给对象的文件上的扩展盘区的范围超过了一个IAM页可以记录的范围，则扩展盘区可能会在文件上有多个IAM页。

 　　IAM页按需要分配给每个对象，并在文件内随机定位。Sysindexes.dbo.FirstIAM指向对象的IAM首页，这个对象的所有IAM 页用链条链接在一起。

　　IAM 页的页首说明IAM所映射的扩展盘区范围的起始扩展盘区。IAM中还有大位图，该位图内的每个位代表一个扩展盘区。位图的第一个位代表范围内的第一个扩展盘区，第二个位代表范围内的第二个扩展盘区，依此类推。如果一个位是0，则不将它代表的扩展盘区分配给拥有该IAM的对象。如果这个位是1，则将它代表的扩展盘区分配给拥有该IAM页的对象。

2. 索引类型

　　索引按不同的需求和分法可分为：聚集索引和非聚集索引，主键索引和非主键索引，唯一索引和非唯一索引，单列索引和复合索引

2.1 聚集索引和非聚集索引

　　聚集索引：行的物理存储顺序与索引顺序完全相同，每个表只允许建立一个聚集索引。默认情况下，SQL Server为主键约束建立的索引为聚集索引。
　　非聚集索引：不改变表中数据行的物理存储顺序。默认情况下， SQL Server为UNIQUE约束所建立的索引为非聚集索引。

　　建立聚集索引随时要改变表中数据行的物理顺序，所以应在其他非聚集索引建立之前建立聚集索引，以免引起SQL Server重新构造非聚集索引。

2.2 主键索引和非主键索引

　　主键索引：主键创建会自动创建聚集索引。频繁更改的属性列上不适宜创建主键聚集索引，因为SQL Server要求必须按照这些属性列的值重新安排记录的物理顺序，这将导致记录的物理移动。
　　非主键索引：在非主键的属性列上创建的索引，这些索引一般都是非聚集索引。

2.3 唯一索引和非唯一索引

　　唯一索引：索引列中不包含重复值。只有当唯一性是数据本身特征时，指定唯一索引才有意义。使用唯一索引不能完全等同于使用主键（唯一索引允许一个空值）。如果某列包含多行NULL值，则不能在该列上创建唯一索引。数据表创建唯一索引后，SQL Server将禁止INSERT语句和UPDATE语句向表中添加重复的键值行。
　　非唯一索引：非设置PRIMARY KEY约束或UNIQUE约束时的索引。

2.4 单列索引和复合索引

　　单列索引：指对表中单个列建立索引。多数情况下，单列索引是创建索引首选考虑的索引，因为单列索引代价相对较小，而对数据库查询效能提高很大。
　　复合索引：一个索引中包含了一个以上的列的索引。复合索引最多可以有16个列复合到一个索引中，并且这些列必须位于同一个表中。复合索引值的最大长度为900字节。例如，不可在定义为char(300)，char(300)，char(301)的三个列上创建单个索引。在使用复合索引检索时，把被索引的列作为一个单位。

3. 规划设计索引的一般原则

3.1 适合建立索引的查询类型

1. 搜索符合特定搜索关键字值的行（精确匹配查询）。
2. 搜索其搜索关键字值为范围值的行（范围查询）。
3. 在表T1中搜索根据联接谓词与表T2中的某个行匹配的行。
4. 在不进行显式排序操作的情况下产生经排序的查询输出，尤其是经排序的动态游标。
5. 在不进行显式排序操作的情况下，按一种有序的顺序对行进行扫描，以允许基于顺序的操作，如合并联接。
6. 以优于表扫描的性能对表中所有的行进行扫描，性能提高是由于减少了要扫描的列集和数据总量。
7. 搜索插入和更新操作中重复的新搜索关键字值，以实施PRIMARY KEY和UNIQUE约束。
8. 搜索已定义了FOREIGN KEY约束的两个表之间匹配的行。
9. 使用LIKE比较进行查询时，如果模式以特定字符串如“abc%”开头进行了索引，使用索引则会提高效率。

3.2 其他设计索引的准则

1. 一个表如果建有大量索引会影响INSERT、UPDATE和DELETE语句的性能。
2. 覆盖的查询可以提高性能。
3. 对小型表进行索引可能不会产生优化效果。
4. 应使用SQL事件探查器和索引优化向导帮助分析查询，确定要创建的索引。
5. 可以在视图上指定索引。
6. 可以在计算列上指定索引。

3.3 索引的特征

　　在确定某一索引适合某一查询之后，可以自定义最适合具体情况的索引类型。索引特征包括：

1. 聚集还是非聚集
2. 唯一还是不唯一
3. 单列还是多列
4. 索引中的列顺序为升序还是降序
5. 覆盖还是非覆盖

　　还可以自定义索引的初始存储特征，通过设置填充因子优化其维护，并使用文件和文件组自定义其位置以优化性能。

3.4 在文件组上合理放置索引

　　默认情况下，索引创建在基表所在的文件组上，该索引即在该基表上创建。不过，可以在不同于包含基表的文件组的其它文件组上创建非聚集索引。通过在其它文件组上创建索引，可以在文件组通过自带的控制器使用不同的物理驱动器时实现性能提升。

　　如果表上有聚集索引，数据和该聚集索引将始终驻留在相同的文件组内。因此，可以在基表上创建一个聚集索引，指定另外一个文件组，在该文件组上新建索引（然后可以除去该索引，而只在新文件组内保留基表），从而将表从一个文件组移动到另一个文件组。

　　如果表的索引跨越多个文件组，则必须将所有包含该表及其索引的文件组一起备份，之后还必须创建事务日志备份。否则，只能备份索引的一部分，导致还原备份时无法恢复索引。

　　单个表或索引只能属于一个文件组，而不能跨越多个文件组。 

3.5 索引优化建议

1. 将更新尽可能多的行的查询写入单个语句内，而不要使用多个查询更新相同的行。仅使用一个语句，就可以利用优化的索引维护。
2. 使用索引优化向导分析查询并获得索引建议。
3. 对聚集索引使用整型键。另外，在唯一列、非空列或 IDENTITY 列上创建聚集索引可以获得比较好的性能收益。
4. 在查询经常用到的所有列上创建非聚集索引。这可以最大程度地利用隐蔽查询。
5. 物理创建索引所需的时间在很大程度上取决于磁盘子系统 。
6. 检查列的唯一性。
7. 在索引列中要注意检查数据的分布情况。

4. 索引的创建和删除

4.1 创建索引

　　利用Transact-SQL语句中的CREATE INDEX命令创建索引。
语法格式：
　　CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]
　　INDEX 索引名
　　ON  数据表名|视图名( 字段名 [ ASC | DESC ] [ ,...n ] )
　　[WITH    
　　[PAD_INDEX]
　　[ [, ] FILLFACTOR=填充因子]
　　[ [, ] IGNORE_DUP_KEY]
　　[ [, ] DROP_EXISTING]
　　[ [, ] STATISTICS_NORECOMPUTE]
　　[ [, ] SORT_IN_TEMPDB] ) ]
　　[ ON 文件组名]

参数说明：

1. UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。
2. CLUSTERED：用于指定创建的索引为聚集索引。
3. NONCLUSTERED：用于指定创建的索引为非聚集索引。
4. ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。
5. PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。必须和填充因子同时使用。
6. FILLFACTOR =填充因子：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。
7. IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集(注：有误，非聚集索引也可使用此选项)索引中的列中插入重复数据时SQL Server所作的反应。当使用该选项，表示当插入或更新记录时，忽略重复键值。
8. DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。
9. STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。
10. SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。

4.2 删除索引

　　利用SQL中的DROP INDEX命令删除索引
　　其语法形式如下：
　　DROP INDEX 表名.索引名[，…n]

5. 索引使用中的维护

5.1 维护索引的统计信息

　　显示指定索引的统计信息。
　　　　USE 教学管理
　　　　GO
　　　　DBCC SHOW_STATISTICS (学生表_备份,CLIDX_学生表_备份_身份)  
　　　　GO
　　更新指定表的索引统计信息。
　　　　USE 教学管理
　　　　GO
　　　　UPDATE STATISTICS 学生表          --更新学生表的所有索引的统计
　　　　GO
　　对指定数据库中所有表的索引统计进行更新。
　　　　USE 教学管理
　　　　GO
　　　　EXECUTE   sp_updatestats

5.2 维护索引碎片

　　索引碎片有两类：内部碎片和外部碎片。

　　内部碎片：
　　当索引页里还有空间可利用时，出现的碎片是内部碎片；内部碎片意味着索引占据了比他实际需要还要多的空间。在创建索引时指定一个较低的填充因子，就会产生内部碎片。有一定的内部碎片是好事，他可以因页里有空闲空间而避免插入多行数据时不必分裂页。分裂的新页需要重新建立索引链并且容易导致外部碎片。
　　外部碎片：
当数据页的逻辑顺序和物理顺序不匹配的时候，或者一个表的存储区不连续时，出现的碎片就是外部碎片。因为外部碎片，需要对多页操作，使访问效率低下，操作成本昂贵。

　　索引碎片的检测
　　用DBCC SHOWCONTIG命令查看索引碎片。
　　语法格式：
　　DBCC   SHOWCONTIG  （表名，索引名）

　　1. 重建和整理索引
　　删除并重新创建聚集索引
　　使用DORP INDEX和CREATE INDEX命令删除表上的聚集索引，随后再重新创建聚集索引，将对数据进行重新组织，能够使数据页填满，并且除去数据碎片。
　　2. 使用DBCC DBREINDEX命令。它能够在一次操作里重建一个表上的所有索引。但重建索引时表不可用。
　　3. 使用DBCC INDEXDEFRAG命令。删除索引碎片，提高索引扫描性能。该命令是一个联机操作，它不控制长期锁。但索引若完全破坏，则无能为力。格式如下：
　　DBCC  INDEXDEFRAG （数据库名，表名|视图名，索引名）

九，索引

原文：https://www.cnblogs.com/a-qi/p/13081745.html