SQL Server索引的原理深入解析

前言

此文是我之前的笔记整理而来，以索引为入口进行探讨相关数据库知识（又做了修改以让人更好消化）。sql server接触不久的朋友可以只看以下蓝色字体字，简单有用节省时间；如果是数据库基础不错的朋友，可以全看，欢迎探讨。

索引的概念

索引的用途：我们对数据查询及处理速度已成为衡量应用系统成败的标准，而采用索引来加快数据处理速度通常是最普遍采用的优化方法。

索引是什么：数据库中的索引类似于一本书的目录，在一本书中使用目录可以快速找到你想要的信息，而不需要读完全书。在数据库中，数据库程序使用索引可以重啊到表中的数据，而不必扫描整个表。书中的目录是一个字词以及各字词所在的页码列表，数据库中的索引是表中的值以及各值存储位置的列表。

索引的利弊：查询执行的大部分开销是i/o，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读取表的每一个数据页，如果有索引指向数据值，则查询只需要读少数次的磁盘就行啦。所以合理的使用索引能加速数据的查询。但是索引并不总是提高系统的性能，带索引的表需要在数据库中占用更多的存储空间，同样用来增删数据的命令运行时间以及维护索引所需的处理时间会更长。所以我们要合理使用索引，及时更新去除次优索引。

1.聚集索引和非聚集索引

索引分为聚集索引和非聚集索引

1.1 聚集索引

表的数据是存储在数据页中（数据页的pagetype标记为1），sqlserver一页是8k，存满一页就开辟下一页存储。如果表有聚集索引，那么一笔一笔物理数据就是按聚集索引字段的大小升/降排序存储在页中。当对聚集索引字段更新或中间插入/删除数据时，都会导致表数据移动（造成性能一定影响），因为它要保持升/降排序。

注意，主键只是默认是聚集索引，它也可以设置为非聚集索引，也可以在非主键字段上设置为聚集索引，全表只能有一个聚集索引。

一个优秀的聚集索引字段一般包含以下4个特性：

(a).自增长

总是在末尾增加记录，减少分页和索引碎片。

(b).不被更改

减少数据移动。

(c).唯一性

唯一性是任何索引最理想的特性，可以明确索引键值在排序中的位置。

更重要的是，索引键指唯一的话，它在每条记录里才可以正确指向源数据行rid。如果聚集索引键值不唯一，sqlserver就需要内部生成uniquifier 列组合当作聚集键保证“键值”唯一性；如果非聚集索引键值不唯一，就会增加rid列（聚集索引键或者堆表中的行指针）保证“键值”唯一性。

思考（可略过）：索引“键值”在非叶子节点也有保证唯一性，原因应该是为了明确索引记录在非叶子节点中的位置。比如有个非聚集索引字段name2，表中有很多name2='a'的记录，导致name2='a'在非叶子节点上有多条索引记录（节点），这时候再insert一笔name2=‘a'的记录时，就可以根据非叶子节点的rid和新增记录的rid很快确定要insert到哪个索引记录（节点）上，如果没有非叶子节点的rid，那得遍历到所有name2='a'的叶子节点才能确定位置。另外，当我们select * from table1 where name2<='a'时，返回的数据是按非聚集索引name2和rid排序的，很好理解返回的数据就是按这边索引存储的顺序排序的。这是这条sql查询时有用到name2索引的结果，如果数据库查询计划因“临界点”问题选择直接表数据扫描，那返回的数据默认就是按表数据的顺序排序的。

为了“键值”唯一性，对于聚集索引，uniquifier 列只在索引值重复时增加。对于非聚集索引，如果创建索引时没定义唯一，rid会在所有记录增加，就算索引值是唯一的；如果创建索引时定义唯一，rid只在叶子层增加，用于查找源数据行，即书签查找操作。

(d).字段长度小

聚集索引键长度越小，一页索引页就可以容纳更多索引记录，进而减少索引b树结构的深度。例如，一个百万记录的表有一个int聚集索引，可能只需要3层的b树结构。如果把聚集索引定义在更宽的列（比如uniqueidentifier列需要16 字节），那么索引的深度会增加到4层。任何聚集索引查找需要4个i/o操作（确切的说是4个逻辑读），原先只要3个i/o操作。
同样，非聚集索引里会包含聚集索引键值，聚集索引键长度越小非聚集索引记录也就越小，一页索引页就可以容纳更多索引记录。

1.2 非聚集索引

也是存储在页中（pagetype标记为2的页，叫索引页)。比如表t建立了一个非聚集索引index_a，那么表t有100条数据的话，那么索引index_a也就有100条数据（准确的说是100条叶子节点数据，索引是b树结构，如果树的高度大于0，那么就有根节点页或中间节点页数据，这时索引数据就超过100条），如果表t还有非聚集索引index_b，那么index_b也是至少100条数据，所以索引建越多开销越大。

更新索引字段、插入一条数据、删除一条数据都会造成索引的维护从而造成性能的一定影响。在不同情况下，性能影响是不同的。比如当你有一个聚集索引，插入的数据又都是在末尾，这样几乎是不会造成数据移动，影响较小；如果插入的数据在中间位置，一般会导致数据移动，而且可能产生分页和页碎片，影响就会稍大一点（如果插入到的中间页有足够的剩余空间容纳插入的数据，而且位置是在页末，也是不会造成数据移动）

2.索引的结构

都说sqlserver的索引是b树结构（这边假定你对b树结构有一定了解），那它到底长什么个模样呢，可以用sql语句来查看它的逻辑呈现。

新建查询执行语法： dbcc ind(test,orderbo,-1) --其中test库的orderbo表有1万笔数据，有聚集索引id主键字段
（不妨自己动手建个表，有聚集索引字段，插入1万表数据，然后执行这个语法看看，会收获很多，百闻不如一见）

执行结果：

如上图，看到一个indexlevel=2的索引页2112（这边它就是b树的根节点，indexlevel最大的就是根节点，往下就是子级、子子级...只有一个根页作为b树结构的访问入口点），说明一定还有indexlevel=1的索引页和indexlevel=0的叶子页。由于这边是聚集索引，因此当indexlevel=0的叶子页就是数据页，存储的是一笔一笔的物理数据。如上图也可以看到，indexlevel=0的行的pagetype等于1，就是代表数据页，上面1.1章节讲到聚集索引时，也有提到pagetype=1；而如果是非聚集索引，indexlevel=0的叶子页，pagetype是等于 2，仍然是索引页。

同样，我们用sql命令dbcc page看一看

-- dbcc traceon(3604,-1) 
dbcc page(test,1,2112,3) 
 --根节点2112，可以查出它的两个子节点2280和2448，然后对这两个子节点再作dbcc page查询
dbcc page(test,1,2280,3) 
dbcc page(test,1,2448,3)

如上图，indexlevel=2的2112页有两个indexlevel=1的子节点2280和2448，子节点下又有子节点，每个节点负责不同的索引键值的区间（即上图的“id(key)”栏位，第一行值是null，表示最小值或倒序时的最大值）。这样的层级关系是不是就是一棵b树结构，其中indexlevel其实就是b树结构中的高度height。

sqlserver在索引中查找某一笔记录时，是从根节点往下找到叶子节点，因为所有数据地址都有存在叶子节点，这其实是b+树的特点之一（b树特点是如果查找的值在非叶子节点就找到，则就能直接返回，显然sqlserver不是这么做，要验证这一点你可以set statistics io on把统计开起来，然后select看下逻辑读的次数）。

既然一定会找到叶子节点，那么索引包含列只要在叶子节点记录就可以了，即非叶子节点没有记录包含列，“索引包含列”见下文第3章节。

b+树这个特点（所有数据地址都有存在叶子节点）也利于between value1 and value2 区间查询，只要找到value1和value2（在叶子节点），然后把中间串起来就是要的结果了。

sqlserver索引结构更像是b+树，最终是b树和b+树的混合版，数据结构都是人定的，不一定就是纯粹的b树或者单纯的b+树。

3.索引包含列和书签查找

谈到索引，这边再讲一个sqlserver2005开始增加的“索引包含列”功能，很实用。

比如，在大报表查询数据时，where条件用到索引字段name2，但是要select的字段是name1，这时候可以使用“索引包含列”把name1包含在索引字段name2中，大大提高查询性能。

语法： create [unique]  nonclustered/clustered index indexname on dbo.table1(name2) include(name1);

接下来分析为什么索引包含列可以大大提高性能。仍然使用dbcc page命令，查看一个非聚集索引并有包含列的索引数据情况：

由上图可知，包含列name1也存储在索引数据中。因此，当数据库用索引字段name2定位到要查找的某一行时，就可以直接把name1的值返回了，而不用再根据rid（上图是【heap rid(key)】列）定位到数据页中去取值，即减少了书签查找。当查询只返回一条数据，只有一次书签查找时当然没什么，如果查询返回的数据很大，每一笔都要去数据页找数据取出来，1000笔就是1000次书签查找，可想而知性能消耗很大，这时候“索引包含列”价值就大大体现出来了。

关于一次书签查找，表有聚集索引（比如id）时就是类似执行了一次 select name1 from table1 where id=1 ，利用聚集索引键id查找（查找方式就是索引id的b树结构查找），而如果表没有聚集索引，则是根据数据行指针（由“文件号2byte：页号4byte：槽号2byte”组成）查找。聚集索引键和行指针一般统称为rid（row id）指针。从这里我们可以想到，如果你的表没有很好的聚集索引字段，建议自增长的id字段做聚集索引主键（冗余出id字段也行），它符合自增长、不被更改、唯一性、长度小的特性，是聚集索引的很好选择。

自增长id绝大部分情况下是适用的，特殊的情况看具体需求而定吧。还有自增长id要考虑一个缺陷，当对表大数据量的并发insert记录时，可以想象每个线程都是要insert到末尾那个页，就会发生竞争和等待。解决这种情况你可以用uniqueidentifier类型字段（16字节，我是不建议使用）或者哈希分区（就是一个表分成多个表，大数据处理中分库分表是正常的）等。但是我建议先优化你的insert效率（insert性能本身是很快的），测试每秒并发insert数是否满足生产环境，以保留简单稳定高效的自增长id作法。

自增长id不一定就是用数据库提供的自增长，你也可以自己写算法生成一个并发情况下也能唯一的id（这时候一般长度是bitint，8字节整形），这种情况适合场景是分布式数据库中主从复制时id栏位是要求一定不能出错的情况（主从复制的一般模式下，主库的id是按主库增长，从库id也是按从库自己的增长，如果遇到死锁等原因导致主从复制不同步时，那从库的id就和主库的id自增长就对不上号了）。如果自增长id是冗余出的主键，那主从库id对不上号也就无影响。

另外，上图最后一列【row size】还告诉我们，索引列或索引包含列的size不要太长，否则一页容不了几笔记录，这样大大增加了索引页数量，而且索引数据所占的空间也大大增加了。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。