SQL Server索引的道理深入解析

发布时间：05/13 来源：未知浏览：关键词：

前言

此文是我以前的笔记整理而来，以索引为入口进行探究相干数据库知识（又做了修改以让人更好消化）。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不要太长，否则一页容不了几笔记载，这样大大添加了索引页数目，而且索引数据所占的空间也大大添加了。

总结

以上就是这篇文章的全部内容了，但愿本文的内容对大家的学习或者工作拥有一定的参考学习价值，要是有疑难大家可以留言交换，感谢大家对我们的支撑。

打赏