深入理解mysql索引(二)

tech2022-12-08 93

接上一篇，上一篇我们简单的学习了索引的本质问题：索引是什么，这一篇继续学习索引的数据结构

MySQL 的存储结构

mysql的存储结构分为：表空间、段、簇（区）、页、行。

表空间 Table Space

表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。分为:系统表空间、独占表空间、通用表空间、临时表空间、Undo 表空间。

段 Segment

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等，段是一个逻辑的概念。一个 ibd 文件(独立表空间文件)里面会由很多个段组成。创建一个索引会创建两个段，一个是索引段:leaf node segment，一个是数据段: non-leaf node segment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。也就是说，一个表的段数，就是索引的个数乘以 2。

簇(区) Extent

一个段(Segment)又由很多的簇(也可以叫区)组成，每个区的大小是 1MB(64 个连续的页)。每一个段至少会有一个簇，一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是簇。

页 Page

为了高效管理物理空间，对簇进一步细分，就得到了页。簇是由连续的页(Page) 组成的空间，一个簇中有 64 个连续的页。 (1MB/16KB=64)。这些页面在物理上和逻辑上都是连续的。跟大多数数据库一样，InnoDB 也有页的概念(也可以称为块)，每个页默认 16KB。页是 InnoDB 存储引擎磁盘管理的最小单位，通过 innodb_page_size 设置。一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个表空间最多存储 64TB 的数据。

注意：文件系统中，也有页的概念。操作系统和内存打交道，最小的单位是页 Page。文件系统的内存页通常是 4K。

# 查看有多少个数据页 show variables like 'innodb_page_size';

往表中插入数据时，如果一个页面已经写完，产生一个新的叶页面。如果一个簇的所有的页面都被用完，会从当前页面所在段新分配一个簇。如果数据不是连续的，往已经写满的页中插入数据，会导致叶页面分裂:

行 Row InnoDB 存储引擎是面向行的(row-oriented)，也就是说数据的存放按行进行存放。

AVL树存储索引的问题

首先，索引的数据，是放在硬盘上的。查看数据和索引的大小:

SELECT CONCAT( ROUND( SUM( DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS data_len, CONCAT( ROUND( SUM( INDEX_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS index_len FROM information_schema.TABLES WHERE table_schema = 'tfree6' AND table_name = 'horder';

当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次 IO。 InnoDB 操作磁盘的最小的单位是一页（或者叫一个磁盘块），大小是16K(16384 字节)。

所以每次和磁盘进行IO的时候，都加载一个16k的数据页。

如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个或者几十个字节，它远远达不到 16K 的容量，所以访问一个树节点，进行一次 IO 的时候，浪费了大量的空间

所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着跟磁盘交互次数就会过多。

如果是机械硬盘时代，每次从磁盘读取数据需要 10ms 左右的寻址时间，交互次数越多，消耗的时间就越多。

有问题就总要有解决的方案：

场景->问题->解决问题的方案->方案的落地

如果我是mysql的设计人员，我去面对这个问题，我会有以下的几种解决思路解决思路：

每次IO交互的时候，尽可能多的往数据页中存放树节点每个树节点，都尽可能多的存放更多的子节点的引用（关键字），这样我们的指针树也就越多，可以有更多的分叉（路数）

因为分叉数越多，树的深度就会减少（根节点是 0）。这样，我们的树就从原来的瘦高瘦高的样子，变成了矮胖矮胖的样子？这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。

AVL树 B Tree 通过上图我们可以看到，同样是插入17个元素，AVL树已经5层的深度了，而BTree才4层，如果数据越多，这个对比是越来越明显的

多路平衡查找树（Balanced Tree） - B Tree

特点：

跟 AVL 树一样，B 树在枝节点和叶子节点存储键值、数据地址、节点引用。分叉数（路数）永远比关键字数多 1。比如每个节点存储两个关键字，那么就会有三个指针指向三个子节点。这里要找到1，只需要进行3次IO操作，比AVL树效率要高不少。

节点拥有的子树数量称为 “度” 关键字数：N 度（Degree）:N + 1 Max Degree 设置为3 的意思是：一个树的最大拥有的子树数量为3，如果一个子树拥有三个关键字时。那意味着这个节点拥有的“度”为4，超过设置的3，这时候，这个节点必须分裂（注意不是AVL树的左旋或者右旋）如果删除节点，会有相反的合并的操作。所以这也就解释了：为什么不要频繁的更新的字段上创建索引或者为什么不要更新主键，因为索引节点上的关键字进行分裂和合并是非常占用内存资源的，并且需要大量的调整树结构。

B+ 树（升级版多路平衡查找树）

B+树比B树升级在哪了？通过B+树的数据结构图可以一窥端倪

MySQL 中的 B+Tree 的特点：

关键字的数量是跟路数（度degree）相等的B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索到关键字不会直接返回，会到最后一层的叶子节点。B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构它是根据左闭右开的区间 [ )来检索数据。

我们来研究下3层的B+树可以存多少数据？

假设一条记录是 1K，一个叶子节点(一页)可以存储 16 条记录。非叶子节点可以存储多少个指针? 假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点(一页)可以存储 16384/14=1170 个这样的单元(键值+指针)，代表有 1170 个指针。树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170117016=21902400。在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

MySQL 中的 B+Tree 的优点：

每个节点存储更多关键字;路数更多扫库、扫表能力更强(如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+Tree 拿到所有的数据)B+Tree 的磁盘读写能力相对于 B Tree 来说更强(根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载（IO）的关键字更多)排序能力更强(因为叶子节点上有下一个数据区的指针，数据形成了链表)范围查询更快：比如要查询从 22 到 60 的数据，当找到 22 之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率(不需要返回上层父节点重复遍历查找)。效率更加稳定(B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的)