SQL Server 索引和表体系结构（聚集索引+非聚集索引）

聚集索引

概述

关于索引和表体系结构的概念一直都是讨论比较多的话题，其中表的各种存储形式是讨论的重点，在各个网站上面也有很多关于这方面写的不错的文章，我写这篇文章的目的也是为了将所有的知识点尽可能的组织起来结合自己对这方面的了解些一篇关于的详细文章出来，同时也会列出一些我自己有疑惑的地方拿出来探讨，介于表达能力有限，有些地方可能无法表达的很明了，还望大家包涵；对于文章中有不对的地方也希望大家能提出，写文章的目的就是为了共享资源；对于这个系列会写5篇文章，在接下来的几天里逐一发布，分别是“聚集索引体系结构”，“非聚集索引体系结构”，“堆体系结构”，“具有包含列的索引”，“表组织和索引组织”。

正文

• 定义

在 SQL Server 中，索引是按 B 树结构进行组织的。索引 B 树中的每一页称为一个索引节点。B 树的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中，叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页均被链接在双向链接列表中。

• 聚集索引单个分区中的结构

• 存储

在SQL Server中，存储数据的最小单位是页，数据页的大小是8K,，8个页组成一个区64K，每一页所能容纳的数据为8060字节，聚集索引的叶节点存储的是实际数据行，而且每页数据行是顺序存储，数据行基于聚集索引键按顺序存储，所以一个数据表只能建一个聚集索引。

非叶子节点（跟节点和中间级）存储的是索引记录，一条索引记录包含：键值（键值也就是聚集索引列的字段值）+指针（指向索引页或者数据页）

由于数据存储在数据页中，索引建存储在索引页中，所以检索单个索引列的数据要快于检索数据记录，因为不需要读取数据页，只需要在索引页中检索数据。

• 聚集索引列选择

窄列（字段长度短的列）：由于索引页存储的是索引记录，索引记录存储的是索引建值和指针，为了让索引列存储更多的索引记录，所以我们选择窄列。

不频繁更新的列：由于索引记录的指针指向数据页，如果数据频繁更新会造成索引页更新，同时由于非聚集索引的数据页的行指针指向聚集索引的数据行，更新聚集索引同时也会造非聚集索引页的更改造成IO消耗。

不重复的列：由于聚集索引的数据页中的数据记录是按聚集建的顺序存储，当向聚集列中插入重复的记录，当数据页超过8060K就会造成分页，分页会将原页中的一半记录插入到新页中，而产生索引碎片。

可以使用自增列作为聚集索引列（这里只是给个建议，需要根据实际的业务来）

非聚集索引

概述

对于非聚集索引，涉及的信息要比聚集索引更多一些，由于整个篇幅比较大涉及接下来的要写的“包含列的索引”，“索引碎片”等一些知识点，可能要结合起来阅读理解起来要更容易一些。非聚集索引和聚集索引一样都是B-树结构，但是非聚集索引不改变数据的存储方式，所以一个表允许建多个非聚集索引；非聚集索引的叶层是由索引页而不是由数据页组成，索引行包含索引键值和指向表数据存储位置的行定位器，

既可以使用聚集索引来为表或视图定义非聚集索引，也可以根据堆来定义非聚集索引。非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。

正文

• 单个分区中的非聚集索引结构

非聚集索引 Index_id>1 可以结合语句查询

SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number,p.rows,
x.first_page,x.root_page,x.first_iam_page,x.filegroup_id,x.total_pages,x.used_pagesFROM sys.allocation_units AS au    JOIN sys.partitions AS p ON au.container_id = p.partition_id    JOIN sys.objects AS o ON p.object_id = o.object_id
    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id
    join sys.system_internals_allocation_units as x on au.container_id=x.container_id  ORDER BY o.name, p.index_id;

非聚集索引行中的行定位器或是指向行的指针，或是行的聚集索引键，如下所述：

• 如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。

如果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

• 非聚集索引与聚集索引相比：

A）叶子结点并非数据结点 B）叶子结点为每一真正的数据行存储一个“键-指针”对 C）叶子结点中还存储了一个指针偏移量，根据页指针及指针偏移量可以定位到具体的数据行。 D）类似的，在除叶结点外的其它索引结点，存储的也是类似的内容，只不过它是指向下一级的索引页的。

聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。而对于非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。

注意：上图中的数据页是聚集索引或者堆数据行，而不是非聚集索引的数据页，在非聚集索引中不存在数据页，非聚集索引中的叶子层和根节点与中间节点有点不同，它的指针是指向数据行，且如果非聚集索引如果是包含列索引，那么包含列仅仅存储在叶级别，而键值可以存储在所有级别，这块会在接下来的包含列索引中讲述。

对于根与中间级的索引记录，它的结构包括： A）索引字段值 B）RowId（即对应数据页的页指针+指针偏移量）。在高层的索引页中包含RowId是为了当索引允许重复值时，当更改数据时精确定位数据行。 C）下一级索引页的指针

对于叶子层的索引对象，它的结构包括： A）索引字段值 B）RowId

由于索引建值存储在索引页中，所以检索单独的索引键值效率是很高的，因为不需要定位到数据页在索引页中就能找到数据，对于当个字段建索引非聚集索引所占的空间要小于聚集索引，因为非聚集索引不需要存储数据行，对于建全覆盖索引除外。

• 非聚集索引列的选择

1. 同样非聚集索引避免选择宽列，这点与聚集索引一样。
2. 包含经常包含在查询的搜索条件（例如返回完全匹配的 WHERE 子句）中的列
3. 经常作为JOIN 或 GROUP BY 子句
4. 尽量避免使用组合列建索引，除非组合列在where中有使用，否则可以用包含列索引替代组合索引，选择组合字段做索引，组合字段的第一个字段选择很重要，第一个字段一定要经常被使用的字段，例如AB字段作为组合字段，当WHERE用A字段作为检索条件的时候，查询会使用索引查找；当你使用B作为WHERE的检索条件的时候，查询使用的是索引扫描，虽然我们不能绝对肯定查找的效率就一定比扫描要好，但是这也是告诉我们要合适的选择索引列，甚至的列之间的先后顺序。
5. 大量非重复值，如姓氏和名字的组合（前提是聚集索引被用于其他列）。不要选择例如性别这种重复值多的列，这种情况表扫描比查找效率会更高，所以有时候当我们用查询计划分析时不一定扫描就一定比查找就要差，我们要根据实际情况去分析问题。
6. 覆盖查询。 当索引包含查询中的所有列时，性能可以提升。查询优化器可以找到索引内的所有列值；不会访问表或聚集索引数据，这样就减少了磁盘 I/O 操作。使用具有包含列的索引来添加覆盖列，而不是创建宽索引键。有关详细信息，请参阅具有包含列的索引。 如果表有聚集索引，则该聚集索引中定义的列将自动追加到表上每个非聚集索引的末端。这可以生成覆盖查询，而不用在非聚集索引定义中指定聚集索引列。例如，如果一个表在 C 列上有聚集索引，则 B 和 A 列的非聚集索引将具有其自己的键值列 B、A 和 C

世界上没有绝对完美的事情，索引也是一样，给我们带来查询效率的同时也会有弊端

• 对表编制大量索引会影响 INSERT、UPDATE、DELETE 和 MERGE 语句的性能，因为当表中的数据更改时，所有索引都须进行适当的调整

总结

这篇文章更重要的是讲述索引的存储结构和查找方式，没有讲述索引的一些基本概念和语句的写法，网上有很多写的很好这方面的文章。希望写这篇文章能给大家带来帮助，文章中有一些内容是从别的作者哪里拷贝过来的，因为我觉得原作者（KissKnife）在这方面已经讲述的非常到位，所以借鉴了一下，同样如果文章中有讲述的不合理的地方还望大家提出。