Mysql 8 索引原理

Mysql 8 索引原理

一.Mysql索引浅谈

1.索引是什么，为什么要使用索引？

索引就相当于字典的目录， 是为了提高我们的查找效率，就像我们翻字典，不可能直接知道要找的字在哪一页，要先找目录再找字的所在页一样。

2.Mysql 索引存储在哪里，为什么要这样存储？

Mysql的数据是存储在磁盘里的，通过磁盘I/O进行访问，索引是为了提高查找效率，想要提高查找效率，本质上是要减少磁盘I/O的次数或者单词磁盘I/O的量，

这样考虑的话，那索引肯定就是存储在内存里了，因为内存的I/O效率是磁盘I/O效率的上万倍。

但是要考虑两个问题，首先就是索引的大小跟数据量是有关系的，数据量越大，索引就越大，如果数据量级很高的话，索引就会非常大，可能高达几个GB，这么大的索引要存在内存里显然不现实，会导致内存占满服务崩溃一系列问题。

其次，如果关机或者宕机了导致索引丢失，就得手动重建一遍，这显然也不太现实。

所以Mysql将索引也存储在了磁盘里。

3.Mysql如何读取索引？

索引这么大，肯定不可能一次全读进内存，那怎么读，分块读呗。那一次读多少呢？这个就要涉及到操作系统了，内存和磁盘交互时有一个最基本的逻辑单位

“页”，相当于我们所说的“块”，不同操作系统对于页的大小定义不太一样，但一般是4k/8k，innodb则是16k，读取的时候一次可以读一页，可以读两页，但是必须是整页，也就是页的整数倍。

4.Mysql索引的设计原则

操作系统中有两个重要概念，分别是磁盘预读和局部性原理，这是为了提高计算机系统的 IO 性能而被提取出来的。

局部性原理是指在一段时间内，程序访问内存中的数据有很大的局部性，即程序更倾向于访问最近访问过的内存地址周围的内存地址，而不是随机的访问内存中的数据，于是便有了磁盘预读。

磁盘预读是指当磁盘读取一个块（通常为4KB或8KB）时，磁盘会自动预读取接下来的一定数量的块并缓存到内存中，以便于之后可能的访问。这样可以避免频繁的磁盘访问，提高读取效率。

因此，MySQL 索引的设计要尽可能充分利用磁盘预读和局部性原理，减少磁盘的随机读取，提升查询效率。

5.Mysql索引的存储格式

​ 还是以字典为例，字典目录存储格式是 笔画-页数，是一个Key-Value类型，因此Mysql索引格式能不能采用Key-Value格式存储，key是列值，value是行记录格式呢？考虑一个问题，Mysql的数据是存储在数据文件中的，而且一个数据文件存储多个行记录，所以得先找到文件再找到行，而且从哪读到哪呢，我不一定要读整行，所以还得有记录长度，但是采用文件名称+offset（偏移量）+记录长度与这样的话I/O次数又大大增加了，怎么办呢？利用磁盘预读和局部性原理。

​ Mysql在读取数据页的时候，一次性读取一个或多个连续的数据页，这样可以利用磁盘预读技术提高访问效率。同时，Mysql使用的InnoDB存储引擎还会使用缓冲池（Buffer Pool）来缓存经常使用的数据页，进一步提高访问速度。这种设计可以使得Mysql在处理数据时能够充分利用磁盘的局部性原理，提高数据访问效率。

​ 然后采用最开始行记录的存储格式，避免读取一整块的数据页时，还需要再次进行解析Value的三部分，进一步减少了数据访问的开销。另外，对于一些较小的表或者查询语句，Mysql可能会直接把整个表加载到内存中进行查询，这时采用行记录的存储格式也能够更加高效地利用内存空间，提高数据访问速度。

二.Mysql索引数据结构

可以用来存储索引的数据结构有很多，哈希表、二叉树、红黑树、平衡二叉查找树、二分查找树等，为什么要衍生出一个B+树呢？

1.哈希表存储索引

先想哈希表可不可以存储Mysql索引键值对，其实是可以的，而且查找和修改效率很高，但是有几个问题：

首先哈希表是散列表，要求把数据均匀散列再每一个存储空间众，需要设计性能优良的Hash算法，否则会产生大量的哈希冲突。

其次哈希表是无序表，如果需要进行范围查询，需要遍历整个哈希表，效率极低。

所以哈希表不是最优选择，但是也可以。

2.用二叉查找树来存储索引

Mysql索引键值对最早确实是用二叉查找树存储的，但是具体怎么存呢，在数据量很大的时候，索引树也会很大，肯定不肯能把整颗树存进一块中，那么怎么拆解呢，把几个节点存进一块中呗，但是如果将多个节点存储在一页中，可能会导致一个节点的数据被分配到不同的页面中，从而增加了IO的次数。那就一个节点存进一块中呗，这个时候查找的效率就跟树的高度成正比。

二叉树在数据量级比较高的时候，树的高度会很高，查找效率严重下降，而且一个节点只存储一个数据，这一个节点往往很小，比一块要小得多，针对这两点进行优化，于是诞生了B树

3.用B树来存储索引

B树采用多叉方式存储Mysql索引键值对，一个节点可以存储多个数据元素，尽量让一个节点和操作系统中的一块差不多大，并且有多叉。

但是随着数据量的不断增加，B树的分支因子也需要不断增加，这样才能存储更多的关键字。但是，如果分支因子过大，会导致每个节点所存储的关键字减少，增加磁盘IO的次数，降低查找的性能。

如果树上的节点满了，再插入数据时，就需要将该节点分裂为两个节点，同时将中间的关键字上移到它的父节点中，保证父节点的关键字数量也不超过限制。分裂后，将原节点左侧的关键字分配给新节点，右侧的关键字保留在原节点中。新节点的关键字范围是原节点的右侧区间，原节点的关键字范围是左侧区间。通过节点分裂，B树可以在不改变树高的情况下继续添加新的关键字。

4.用B+树来存储索引

​ B+树对B树进行了优化。

​ 首先B+树只在叶子节点存储实际数据，非叶子节点只存储关键字和指向子节点的指针，这样非叶子节点可以存储更多的关键字，单个非叶子节点存储的关键字多了，非叶子节点总数就减少了，树的层高也就减小了，尽管叶子节点存储全部数据需要单独增加一层。

​ 一般情况下，B+树比B树更适合用于索引结构。但是，如果数据量比较小，比如只有几百条数据时，B树的高度很低，可以快速定位到目标数据，此时B树可能会比B+树更快。

但是，在实际应用中，数据量很少会只有几百条，往往会有上万甚至上亿条数据需要处理，这种情况下使用B+树更为适合。

其次，B+树每个叶子节点都分别存储一个指向前一个叶子节点和后一个叶子节点的指针，形成了双向有序链表，可以支持区间查询，

B树因为实际数据分布在树的不同层，无法这么做，所以B树不支持范围查询，基于两点，B+树更适合做数据库索引。

5.结论

​ Mysql最终采用了B+树作为默认的索引，使用哈希作为备选索引。一般情况下，3-4层的B+树足以支撑千万级别的数据量存储，具体是3还是4由索引列值所占大小决定。

三.Mysql索引存放问题

1.一个表中可以有多少个索引？

根据列的不同，索引的个数也不同，但是可以有多个。

2.一个索引对应一棵树还是多个索引对应一棵树？

一个索引对应一棵树。

3.索引的叶子节点存放数据，那么多个索引的时候数据放几份？

先说结论，只放一份。

4.那么如何实现数据只存放一份？

其中一颗索引树叶子节点存放数据，其他索引树的叶子节点存放那一颗树的叶子节点上的key。存放数据的索引树一般是主键（数据库id）索引树，但也可能是别的。

在innodb存储引擎中，mysql的数据在进行插入的时候必须要跟某一个索引列绑定存储，如果有主键索引就选择主键索引，没有主键索引就选择唯一索引，没有唯一索引就自动生成一个6字节的rowid列，给这个rowid自动加上索引并绑定。

5.聚簇索引与非聚簇索引

聚簇索引：跟数据绑定存储的索引

非聚簇索引：没有跟数据绑定存储的索引

6.为什么Mysql数据要跟索引绑定

因为要保证能跟根据Key直接找到行记录。

四.索引相关概念

1.回表

2.索引覆盖

3.最左匹配原则

最左匹配原则：从左向右依次匹配，匹配不上结束。

以上四个查询语句中，第一个肯定会走索引，第二个也会走索引，第三个肯定不会，因为没匹配上name，就结束，不走索引。第四个其实也会走索引，因为Mysql有优化器。

为了便于理解最左匹配原则，看一个例子

有一个组合索引 (col1, col2, col3) ，这个索引只有在以下情况下才能被利用：

1. 以 col1 开头的查询条件，比如 WHERE col1 = ‘value2’；
2. 以 col1 和 col2 开头的查询条件，比如 WHERE col1 = ‘value2’ AND col2 = ‘value2’；
3. 以 col1、col2 和 col3 开头的查询条件，比如 WHERE col1 = ‘value2’ AND col2 = ‘value2’ AND col3 = ‘value3’。

这篇文章是我听了连鹏举老师的Mysql索引直播课后自己总结了一下，收获良多，当然个别知识会有出入。

. 以 col1、col2 和 col3 开头的查询条件，比如 WHERE col1 = ‘value2’ AND col2 = ‘value2’ AND col3 = ‘value3’。

这篇文章是我听了连鹏举老师的Mysql索引直播课后自己总结了一下，收获良多，当然个别知识会有出入。

视频链接：https://pan.baidu.com/s/16WMOs1mEdeCnbgGm4p7X2A 提取码: 8888