细说SQLServer索引原理
发布时间：2022-09-28 12:33:35 所属栏目：教程 来源：
导读： 表默认只有一个分区（sql server的分区表技术，可以将表进行水平拆分，这样就会产生多个分区）。 分区里面就是存储的数据，有两种存储形式：堆或B+树，具体结构下面细说。
一、页
页是数据存储的
一、页
页是数据存储的
|
表默认只有一个分区（sql server的分区表技术，可以将表进行水平拆分，这样就会产生多个分区）。 分区里面就是存储的数据，有两种存储形式：堆或B+树，具体结构下面细说。 一、页 页是数据存储的最小单位。 页类型分为：数据页、索引页、Log_mixed_page、Lob_tree_page、IAM页面 一个数据页可以存储8K（8192字节，减去96字节的头）大小的数据。数据页里面就是数据行，数据行不能跨页。 疑问：那一行数据可以超过8K吗，超过8K不就跨页了吗？ sql server 2000会有这个限制。sql server 2005 突破了每行8K的限制 但是sql server列的大小，仍不能超过8K（比如你不能定义varchar(9000) 或者 nvarchar(5000)）； 如果一行数据超出了8K，那么超出8K的字段会存到溢出页上，原数据行上有个指针指向到溢出页。 有人可能会说 varchar(max)、nvarchar(max)、text、image这种类型，其实不然，这种类型是LOB类型。 LOB(large object)是一种用于存储大对象的数据类型，每个LOB可以有2GB。LOB列可以跨多页，并且页不一定是连续的。 区(又叫扩展区) 区(又叫扩展区)是页的集合，一个区包括了8个页，区大小是64K。 注意：这里的区，不是表分区。每个表默认只有一个表分区。 二、堆结构 堆是一个没有聚集索引的表。表中的数据不按任何字段排序。 用"索引分配映射(IAM)"页将堆的页面联系在一起。如下图所示： 堆结构 堆结构 堆内的数据页和行没有任何特定的顺序；页面也不链接在一起，数据页之间唯一的逻辑连接是记录在IAM页内的信息， 页面与页面之间没有什么紧密的联系；用IAM页查找数据页集合中的每一页。 从数据存储管理上来讲，用堆去管理一个超大的表格是比较吃力的，经常使用的表格上都建立聚集索引。 可以通过扫描 IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区。 因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们，所以这意味着串行堆扫描连续沿每个文件进行。使用 IAM 页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回。 三、索引 MSSQL的索引存储结构是B+树，这是一种平衡多叉树。 1.B树和B+树的区别 树状图1 树状图1 B树的索引节点里面除了键值和指针之外，还有行数据。 树状图2 树状图2 B+树的索引节点里面，只有键值和指针。b+树的叶节点是个双向链表，范围查找非常快速。 2.为什么B+树更适合做索引？ 2.1因为页容量是固定的，所以B+树能容纳更多的索引值，那么索引深度就相对较浅，查找性能会更好。 2.2b+树叶级节点之间，是个双向链表，范围查找很快。 B+树结构： 当一个表上加了聚集索引后，其结构即成了一个B+树，数据记录成了B+树的一部分。 数据的物理顺序按索引字段的顺序来排列，因为物理排列顺序肯定是只有一种的，所以表上只能添加一个聚集索引。 聚集索引 下图是一个单字段聚集索引的存储结构图(假设是在Name上加的聚集索引) 聚集索引 聚集索引 mssql 索引_重建索引 mssql_mssql 索引 聚集索引是以B树结构存储的。根节点和中间节点都是索引页，叶子节点是数据页。 当表加了聚集索引的话，数据就不是按堆存储了，而是按B树结构存储的，数据记录成了B树的一部分，是B树的叶子节点。 索引页里面包含的是索引行，索引行由索引键值和指针构成，指针指向的是下一级索引的页ID。如果下一级是数据页，则指向的就是数据页ID（不是数据行的ID）。 数据页里面包含的就是数据行，如果数据行大小超过8060字节，那么超出的部分会存到溢出页，此行数据会有一个指针指向溢出页。 上面的图有一个缺陷（页之间的关联没有标明），相同层级的索引页之间是相互关联的，是个双向链表，每个索引页都有指针指向上下一页。 数据页也是一个双向链表，都会指上一页和下一页。数据在物理上不一定是连续的，但是在逻辑上一定是连续的。所以范围查询的时候是很快的。 数据是有序的，按照聚集索引字段的顺序来排列，所以一个表只能有一个聚集索引。如上图所示最右边的数据记录很明显可以看出是按照Name升序来排列的。 B+树的查找方式：如上图数据所示，假设要查找Name=Greene的记录 从根节点开始查找： >= ‘Bennet’ 且 < ‘Karsen’ 的数据 --> 进入索引页1007 （Greene的记录应该再查找此页） >= ‘Karsen’ 且 < ‘Smith’ 的数据 --> 进入索引页1009 >= ‘Smith’ 且 < xxxxxxx 的数据 --> 进入索引页1062 再从中间节点索引页1007查找： >= ‘Bennet’ 且 < ‘Greane’ 的数据 --> 进入数据页1132 >= ‘Greane’ 且 < ‘Hunter’ 的数据 --> 进入数据页1133 （Greene的记录应该再查找此页） >= ‘Hunter’ 且 < xxxxxxxx 的数据 --> 进入数据页1127 最后从数据页1133中取得Name=Greene的这行记录复制代码 3.根节点的索引键值是如何决定的？ 3.1根节点里面的存储索引键值是如何决定的？为什么是zhangsan，而不是lisi或者其他？ 取每个数据页的第一条的索引键值，向上形成索引页。 再用最底层的每个索引页的第一条向上形成索引页，这样依次向上推，直到根节点。这样根节点的索引键值就出来了 PS：即第一条记录肯定是在根节点里面的，下面的DBCC分析也佐证了这点。 4.索引的层数如何决定的？ 假设某表里1亿行数据，并且这1亿行数据刚好构成了1000万个数据页， 聚集索引字段是个Int型字段(Int类型为4字节)，一个索引页只能存储8K（8060字节）数据的： 那么数据页上层需要 4000万字节/8060字节=4963个索引页。 （因为索引指向是索引页的ID，所以数据页上层的索引只需要4000万字节） 再上一层(4963*4)/8060 = 3个索引页 再上一层1个索引页即可，至此就是根节点了。 索引的层数（即索引深度）是由索引键的大小和数量决定的。 （编辑：草根网_马鞍山站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
浙公网安备 33038102330472号