SQL Server 索引理论知识（全）
一、堆表
1、堆的结构
图1
堆是不含聚集索引的表。堆的 sys.partitions 中具有一行，对于堆使用的每个分区，都有 index_id = 0。默认情况下，一个堆有一个分区。当堆有多个分区时，每个分区有一个堆结构，其中包含该特定分区的数据。例如，如果一个堆有四个分区，则有四个堆结构；每个分区有一个堆结构。
根据堆中的数据类型，每个堆结构将有一个或多个分配单元来存储和管理特定分区的数据。每个堆中的每个分区至少有一个 IN_ROW_DATA 分配单元。如果堆包含大型对象 (LOB) 列，则该堆的每个分区还将有一个 LOB_DATA 分配单元。如果堆包含超过 8,060 字节行大小限制的可变长度列，则该堆的每个分区还将有一个 ROW_OVERFLOW_DATA 分配单元。
2、堆表的查询方式
1、sql server在sys.indexes表中查到对应的数据行，读取index_id值(因为堆没有索引所以是0)后，sql server开始读取first_iam_page值，获取堆的IAM的第一页(8KB)的位置。如图2：
图2
2、因为IAM将堆的各个区域联接在一起，sql server将根据IAM提供的区域地址，一个区域一个区域的查找，一个数据页一个数据页的查找，直到获取所需的数据为止。如图3：
图3
二、聚集索引表
1、聚集索引结构
图4
如图4所示，聚集索引首先有一个根节点，然后通过根节点指向各个索引页，索引页可能会有多层，但最终都是通过索引页指向数据页。
聚集索引是按 B 树结构进行组织的。索引 B 树中的每一页称为一个索引节点。B 树的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中，叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页均被链接在双向链接列表中。
2、聚集索引的查询方式
1）在sys.indexes表中查到对应的数据行，找到indid为1后，sqlserver开始读取root列的值。(列值是根页面的地址)，如图5：
图5
2）找到根页面后开始搜索，比如要搜索的表是10条记录一页，这里是找“1981”这个值，将索引的值“1981”与根页面的索引比较。由于“1981”是在1900到2000之间。所以sqlserver开始搜索1900所在的中间页。
3）找到“1900”所在的中间页后，将索引值“1981”继续与中间页的索引比较，由于“1981”是在1980到1990之间。所以sqlserver开始搜索1980所在的数据页。(注意这一步是根据中间页找数据页)
如图6：
4）找到“1980”所在的数据页后，将索引值“1981”继续与数据页的索引比较，很快就可以在这个数据页上找到“1981”的数据行了。如图7：
图7
三、索引的存储方式及访问顺序
1、聚集索引
A、存储方式
分别建立有唯一聚集索引和非唯一聚集索引的表后，各插入几条数据，对sys.system_internals_allocation_units进行查询，得出该表的数据页等使用情况
图8
a、唯一性聚集索引
使用下列dbcc指令查询数据的存储情况：
dbcc traceon(3604)
dbcc page(ymtang,1,127,2) --数据访问顺序
dbcc traceoff(3604)
得到结果（取了部分需要的）：
m_type = 1
6464C060: 1000d407 42202020 20202020 20202020 †....B
…
6464C3E0: 20202020 20202020 42424231 20202020 † BBB1
…
6464C830: 20202020 02000010 00d40741 20202020 † .......A
…
6464CBB0: 20202020 20202020 20202020 20202041 † A
6464CBC0: 41413120 20202020 20202020 20202020 †AA1
…
6464D000: 20202020 20202020 20202002 00000000 † .....
红色为行头，蓝色为行尾，数据以十六进制ASCII码存于数据页。
大家可以看到m_type=1即数据页面，大家应该很奇怪吧，为什么明明是聚集索引，却是数据页面呢？正如上面所提到，聚集索引的叶子页面即数据页面。因为这个表只有2~3条记录，所以root页面还达不到需要分为B树的程度，所以该root页面也是叶子页面。
b、非唯一性聚集索引
同样使用dbcc指令查看数据的存储情况
dbcc traceon(3604)
dbcc page(ymtang,1,175,2) --数据访问顺序
dbcc traceoff(3604)
699CC060: 1000d407 41202020 20202020 20202020 †....A
699CC830: 20202020 03000030 00d40741 20202020 † ...0...A
699CD000: 20202020 20202020 20202003 00000100 † .....
699CD010: df070100 00003000 d4074120 20202020 †......0...A
699CD7E0: 20202020 20202020 20200300 000100df † ......
699CD7F0: 07020000 003000d4 07412020 20202020 †.....0...A
因为在索引列插入了4条都是为“A”的数据，所以为了对数据行进行区别，sqlserver从第二个重复行开始数据行变成了变长（30表示变长）重复行的行尾新增8个字节，如：03 0000 0100 df070100 00，其中0100即1表示该表一共有1个变长字段，df07即2015变长字段结束的位置，最后四个字节0100 0000为非唯一索引的标识符，换算成10进制即1。
从页面中记录的顺序我们其实可以看得出来，聚集索引的行的物理顺序与行的实际存储没有太大关系，而是与记录槽的顺序的有关。
通过行尾的唯一标识符，即使有重复值，数据库引擎也能准确定位到相关数据，
中间层：
上面的测试主要是在单页中进行，下面将更多的讨论关于聚集索引的中间层。
通过以下指令，查询出多数据页的索引存储情况：
INSERT INTO testUniqueCluster VALUES('C','CCC1')
INSERT INTO testUniqueCluster VALUES('D','DDD1')
INSERT INTO testUniqueCluster VALUES('E','EEE1')
TRUNCATE TABLE tablepage;
INSERT INTO tablepage EXEC ('DBCC IND(testdb,testUniqueCluster,1)');
SELECT
b.name table_name,
CASE WHEN c.type=0 THEN '堆'
WHEN c.type=1 THEN '聚集'
WHEN c.type=2 THEN '非聚集'
ELSE '其他'
END index_type,
c.name index_name,
PagePID,IAMPID,ObjectID,IndexID,Pagetype,IndexLevel,
NextPagePID,PrevPagePID
FROM tablepage a,sys.objects b,sys.indexes c
WHERE A.ObjectID=b.object_id
AND A.ObjectID=c.object_id
AND a.IndexID=c.index_id
得出以下结果
图9
再通过对sys.system_internals_allocation_units查询表的页面概要信息
图10
从以上两个结果能看出IAM页仍然在第174页，但是根节点发生了变化，出现在了第192页——Pagetype=2的索引页面，而数据页173页还在，多出了数据页193页，第173页与第193页之间存在着互链关系。
通过
dbcc traceon(3604)
dbcc page(ymtang,1,173,2)
dbcc traceoff(3604)
和
dbcc traceon(3604)
dbcc page(ymtang,1,193,2)
dbcc traceoff(3604)
查看第173页和第193页的信息，会发现A,B,C,D数据均存在第173页，而E的数据存在193页，也就是说对于SQL Server来说索引的分裂应该是以最小代价进行，而不是完全均衡策略。
如果再往表中插入数据的话数据页将再次发生变化，如下图：
图11
B、访问顺序
聚集索引是按 B 树结构进行组织的。索引 B 树中的每一页称为一个索引节点。B 树的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中，叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页均被链接在双向链接列表中。
它的访问顺序就类似图11中所画的，在根节点中先判断键值所在的索引页，然后根据索引页中包含的指针，直接访问数据页的数据行。
2、非聚集索引
A、存储方式
首先，在观察非聚集索引存储方式之前，先了解一下它的存储结构，如下图所示：
图12
从上图可以看出，非聚集索引从根节点开始指向叶节点，也就是索引行，再通过索引行指向堆表的数据行或者行的聚集索引键，总的来说，聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。
接下来让我们看看非聚集索引分别在堆表和聚集索引表上的存储方式。
B、访问顺序
a、堆表中的非聚集索引
i堆表中的唯一非聚集索引
首先，在数据库中建立相应的表和索引：
CREATE TABLE testHeapIndex
(
name
CHAR(200),
type1
CHAR(900),
type2
CHAR(900)
)
--分别创建一个唯一索引和一个非唯一索引
CREATE UNIQUE INDEX idx_testHeapIndex1 ON testHeapIndex(type1)
CREATE INDEX idx_testHeapIndex2 ON testHeapIndex(type2)
--插入测试数据
INSERT INTO testHeapIndex VALUES('A','A1','A2')
INSERT INTO testHeapIndex VALUES('B','B1','B2')
INSERT INTO testHeapIndex VALUES('C','C1','B2')
INSERT INTO testHeapIndex VALUES('D','D1','B2')
INSERT INTO testHeapIndex VALUES('E','E1','C2')
INSERT INTO testHeapIndex VALUES('F','F1','F1')
INSERT INTO testHeapIndex VALUES('G','G1','G1')
INSERT INTO testHeapIndex VALUES('H','H1','G1')
INSERT INTO testHeapIndex VALUES('I','I1','G1')
INSERT INTO testHeapIndex VALUES('J','J1','J1')
完成数据和索引的插入创建后，先来看看该表的相应页面情况：
图13
图14
再获取一下该表的root地址及索引根节点
图15
从上面的两张表可以发现，唯一非聚集索引的root页在第204页，分别指向第198页与205页。
那么这个索引的具体存储和访问顺序是什么呢？通过dbcc page指令查看了页的存储详情后，总结出如下图的结果：
图16
从图中看出，索引的根节点为第204页，其中大于等于I1的指向第205页，小于I1的指向第198页。叶子页面（即索引页）中则包括每个索引条目的索引值和该索引指向的记录的位置（文件号+页面+插槽号），因为这是唯一索引所以不需要额外的字段来记录重复值。通过记录的位置，索引将定位到数据页的相应位置。如：
查询F，F1，F2这行数据，先从根节点判断，F1在第198页，随后在第198页查询到F1所在的位置是第一个文件的第202页的第二个插槽，接着便直接访问该位置，得到数据。
ii堆表上的非唯一非聚集索引
通过相同的方法，我们也能得出非唯一聚集索引在堆表上的访问顺序图，如图17：
我们可以发现，非唯一非聚集索引在堆表上的访问顺序及存储结构和唯一非聚集索引在堆表上的完全一致，尽管索引的值不一样，但是通过索引记录的位置（文件号+页号+插槽号）依旧能保证索引条目的唯一性，所以在页面信息中不用新增额外的字段进行区别。
b、聚集索引表中的非聚集索引
i唯一聚集索引表上的非聚集索引
⑴唯一聚集索引表上的唯一非聚集索引
在完成建表和建立索引后，我们还是先看一下表的存储情况，如图18，图19：
图18
图19
我们可以直观的看出，在testUniqueClusterIndex这张表上，存在一个聚集索引和两个非聚集索引，唯一聚集索引的根节点在第216页，分别指向数据页第212页、第217页和第222页，唯一非聚集索引的根节点在第218页，分别指向第214页和第219页，非唯一非聚集索引的根节点在第220页，分别指向第194页和第221页。那非聚集索引在唯一聚集索引表上是如何访问数据的呢？那就让我们通过图20来直接观察：
图20
可以看到，唯一非聚集索引先通过自己根节点进行判断，小于G1的在第214页，大于G1在第219页，然后根据他的叶子节点对应的唯一聚集索引的键值访问聚集索引的根节点，在唯一聚集索引的根节点再次进行判断，A～D在第212页，E～H在第217页，大于I的在第222页。最后根据唯一聚集索引的索引页上的键值和指针进行数据行的定位。
⑵唯一聚集索引表上的非唯一非聚集索引
由于在之前一个小分支中已经将非唯一非聚集索引建立在唯一聚集索引表中，所以铺垫过程就省去了，直接通过图来看下访问顺序，如图21：
图21
page(1:220)为该唯一聚集索引表上的非唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于G1的指向叶子页面page(1:221)，小于G1的则指向叶子页面page(1:194)页面。之所以在220页面的索引条目中包括聚集索引的键值，是因为该索引条目不唯一造成的，从图中可以看出，Type2=G1的索引条目有3条，所以需要聚集索引键值的存在才能保证该索引条目的歧义和唯一性。
非唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值；尽管非聚集索引不唯一，但因为聚集索引是唯一的，所以两者合起来能够保证该索引条目的唯一性，所以也不需要增加额外的辅助字段。
访问顺序是：首先经过非唯一非聚集索引的根节点和索引页的筛选，再通过聚集索引则马上就能定位到需要的数据行。
ii非唯一聚集索引表上的非聚集索引
⑴非唯一聚集索引表上的唯一非聚集索引
和之前做的准备工作相同，都是先建表，然后在相应的字段上建立索引，新建的非唯一聚集索引表的结构如图22、图23所示：
图22
图23
我们可以直观的看出，在testUnUniqueClusterIndex这张表上，存在一个聚集索引和两个非聚集索引，非唯一聚集索引的根节点在第237页，分别指向数据页第210页、第238页和第243页，唯一非聚集索引的根节点在第239页，分别指向第233页和第240页，非唯一非聚集索引的根节点在第241页，分别指向第235页和第242页。
图24为唯一非聚集索引在非唯一聚集索引表上的访问顺序
我们首先可以看到page(1:237)为聚集索引的根节点，该根节点包括2个索引值和三个索引指针指向相应的叶子页面，其中第二个索引值后面还带了一个identifer为3的值，这是因为该聚集索引不唯一，所以必须增加一个唯一标识才能定位到相应的下级节点中。而聚集索引的叶子页面则按照聚集索引的排序规则进行存储；注意在叶子节点中重复键值的聚集索引的尾部也带有相应的唯一标识值。
page(1:239)为该非唯一聚集索引表上的唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于H1的指向叶子页面page(1:240)，小于H1的则指向叶子页面page(1:233)页面。
注意非唯一非聚集索引的叶子页面中包括每个索引条目的索引值和其聚集键值，同时在重复聚集键值的后面增加了相应的唯一标识，因此三者结合起来就能够保证该索引条目的唯一性了。
访问顺序是：首先在唯一非聚集索引的根节点进行判断，在相应的索引页查找非唯一聚集索引的键值，因为有重复键值，所以再次根据键值尾部的唯一标识值进行定位，定位到非唯一聚集索引的根节点后，按照根节点的索引值定位到数据行所在的数据页，并得到结果。
⑵非唯一聚集索引表上的非唯一非聚集索引
图25
page(1:241)为该非唯一聚集索引表上的非唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于G1的指向叶子页面page(1:242)，小于G1的则指向叶子页面page(1:235)页面。
注意非唯一非聚集索引的叶子页面中包括每个索引条目的索引值和其聚集键值，同时在重复聚集键值的后面增加了相应的唯一标识，虽然非唯一聚集索引的索引键值是重复的，但因为聚集索引键值和唯一标识已经是唯一的，所以三者结合起来依然能够保证该索引条目的唯一性。
非唯一非聚集索引在非唯一聚集索引表中的访问顺序类似于唯一非聚集索引在非唯一聚集索引表中的访问顺序，只不过在最初非唯一非聚集索引的根节点进行筛选时索引键会增加一个非唯一聚集索引的键值，并在该索引键的行尾额外增加一个唯一标识符。通过该根节点的筛选定位到索引页后，再根据非唯一聚集索引的键值和行尾的标识符非唯一聚集索引的根节点进行筛选，然后定位到数据页的具体数据行。
四、包含列的非聚集索引
讨论完普通情况下的聚集索引和非聚集索引后，我们将进入一个比较特殊的情况下的话题，那就是包含列的非聚集索引。
进入研究包含列的非聚集索引前，我们先看一下多字段的非聚集索引，然后再看在相同字段建立包含列的非聚集索引，比较下两者有什么区别。
经过之前非聚集索引的测试后，可以发现其实多字段与单字段的情况是类似的，只不过多字段的情况下非聚集索引的索引键会把字段都列入键值中作为索引的键值。所以关于多字段非聚集索引请参考本文档前几页关于非聚集索引的存储和访问情况的内容。
1、堆表上的包含列非聚集索引
开始讨论堆表上的包含列非聚集索引，那首先第一步要做的还是建表和索引，下图则为堆表的索引情况，如图26，27所示：
图26
图27
从上面两张图可以看出，该包含列索引的根节点在第255页，分别指向第244页、第252页和第256页。其中小于I1的指向第252页，大于I1并小于N1的指向第244页，大于N1的指向第256页。
图28
如图28所示，是包含列的非聚集索引在堆表上的访问顺序，黑色线是根据索引键值进行的数据访问，通过根节点与索引页的定位筛选，定位到具体的数据行，而红色的线为索引的非键列，用于覆盖更多查询的非聚集索引，直接指向所在的数据行的键值。在非聚集索引中，非键列是存储在叶级别中的，由于实验时数据量较少，所以索引只有叶节点。
2、唯一聚集索引表上的包含列非聚集索引
图29
图30
如图29、30所示，是在唯一聚集索引表上建立的包含列索引的存储情况，唯一聚集索引的根节点为第263页，分别指向第249页、第259页和第265页，包含列非聚集索引的根节点在第250页，分别指向第261页、第264页和第266页。了解了基本结构后来让我们看下具体的索引访问顺序，请见下图31：
图31
从上图中不难发现，包含列的非聚集索引在查询数据时与普通的非聚集索引在唯一聚集索引表中的访问顺序类似，都是先通过根节点对非聚集索引和聚集索引的键值进行筛选，再进入非聚集索引的索引页，由于此处数据量较小，索引页也就是叶节点，所以非聚集索引的非键列也存储在此处，然后根据对应的聚集索引键值到聚集索引的根节点进行筛选，随后直接指向数据页。非键列在此处也是起到扩展非聚集索引的作用，在搜索非键列时也可以根据非聚集索引来搜索。
3、非唯一聚集索引表上的包含列非聚集索引
图32
图33
用相同的方法建立了非唯一聚集索引表后在两个字段上建立包含列的非聚集索引，结果如上图32和图33所示。
因为建表时将两个非聚集索引的字段都定义为CHAR（900），并成功创建了索引，可见包含列的非聚集索引可以让非聚集索引不受到索引大小的限制，但是也不是无限扩展，最大非键列数为1023列，也就是最大的表列数减1，还是会受到表的列数限制。
完成以上步骤后，通过dbcc指令来看下每个页的存储情况，下面将通过图的形式来反映索引的访问顺序。如图34所示：
图34
由图34可知，包含列的非聚集索引在非唯一索引表上先通过非聚集索引的键值和聚集索引的键值以及因聚集索引上的键值出现重复值而为了唯一标识的唯一标识符定位到非聚集索引的索引页，然后通过聚集索引的键值和唯一标识符筛选，直接指向数据页，从数据页查询数据。非聚集索引的非键列则存储在叶节点，通过keyhashvalue在数据库内部表示。
4、包含列的非聚集索引总结
包含列的非聚集索引在存储方式上与非聚集索引相似，不同的地方在于在非聚集索引的叶节点上会存储非键列，并通过keyhashvalue在数据库内部进行表示。非聚集索引通过增加非键列可以扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。这是因为非键列具有下列优点：
1）它们可以是不允许作为索引键列的数据类型。
2）在计算索引键列数或索引键大小时，数据库引擎不考虑它们。
当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。这样可以实现性能提升，因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。
使用包含列的非聚集索引可以避免索引大小的限制。因为索引的最大大小为900个字节，最大列数为16个，所以通过包含列可以避免索引的大小限制（数据库引擎计算索引键列数和键值大小时，不考虑非键列）。但非键列的大小也是有准则的：
首先必须至少定义一个键列。最大非键列数为 1023 列。也就是最大的表列数减 1。
其次索引键列（不包括非键）必须遵守现有索引大小的限制（最大键列数为 16，总索引键大小为 900 字节）。
再是所有非键列的总大小只受 INCLUDE 子句中所指定列的大小限制；例如，varchar(max) 列限制为 2 GB。
在创建时也不能是随便将字段加入非键列，要考虑到性能的问题，添加过多的索引列（键列或非键列）会对性能产生下列影响：
1、一页上能容纳的索引行将更少。这样会使 I/O 增加并降低缓存效率。
2、需要更多的磁盘空间来存储索引。特别是，将 varchar(max)、nvarchar(max)、varbinary(max) 或 xml 数据类型添加为非键索引列会显著增加磁盘空间要求。这是因为列值被复制到了索引叶级别。因此，它们既驻留在索引中，也驻留在基表中。
3、索引维护可能会增加对基础表或索引视图执行修改、插入、更新或删除操作所需的时间。
创建完难免会有需要改动的时候，那在修改包含列的索引时也要注意以下几点：
1、除非先删除索引，否则无法从表中删除非键列。
2、除进行下列更改外，不能对非键列进行其他更改：
1）将列的为空性从 NOT NULL 改为 NULL。
2）增加 varchar、nvarchar 或 varbinary 列的长度。
