MySQL 索引底层数据结构与算法

Laughing_Xie

已于 2022-08-02 23:44:53 修改

阅读量1k

点赞数 1

分类专栏： java小细节文章标签： b树数据结构 java

于 2022-05-24 14:44:41 首次发布

本文链接：https://blog.csdn.net/qq_56754651/article/details/124244678

版权

java小细节专栏收录该内容

11 篇文章 0 订阅

订阅专栏

索引是帮助MySQL高效获取数据的排好序的数据结构。

一张表的数据在磁盘上面是随机分布的，不一定数据绝对的是相邻的，如果要从磁盘上拿取一行记录，需要与磁盘做IO交互,交互越多，越消耗性能。(索引也是存储在磁盘上的)

索引是一种数据结构，数据结构有：二叉树（二叉查找树Binary Search Tree）、红黑树（Red Black Tree）、Hash表、B-Tree、B+Tree

索引详解：

1.假设索引采用二叉树数数据结构：

如图所示：我们建了一张表，一些个人数据，此时以age字段建一个索引，如果采用二叉树数据结构作为索引那么图如下：

此时如果进行查询：select * from user where age=29 ;

age为索引字段，会先去存储我们索引的数据页进行查询，29大于24，又小于31，很快找到29这个索引值，并且根据这个key值存储的磁盘文件地址值，再进行一次磁盘IO去查找这个地址存放的目标数据。如果age不是索引字段，则需要数据库一行一行数据去比对，比对6次以后才拿到目标数据，不停的进行磁盘IO，效率很低。所以如果用二叉树的数据结构去寻找数据，会比全表扫描的方式拿数据效率更高。

弊端：如果索引字段一直都是一种规律增加，树的高度会很高，而且查询效率依然很低，这种情况下，几乎变成了线性查找。用二叉树建索引，对于单边增长的索引基本没有提升效率性能的作用，所以mysql没有选择用二叉树。如图：

2.红黑树（二叉平衡树）

此时红黑树就应运而生，可解决此问题，红黑树不仅具有二叉树的特性，还有自己的附加属性，即使是单边增长，也会自动去调整进行优化，如果数据量很大的情况下，树的根节点到叶子节点会非常高，不可控（查找的次数跟高度紧密相关的）；

1.结点是红色或黑色。

2.根结点是黑色。

3.每个叶子结点都是黑色的空结点（NIL结点）。

4 每个红色结点的两个子结点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色结点)

5.从任一结点到其每个叶子的所有路径都包含相同数目的黑色结点。

这里不做深入讲解（因为我也不是专家）；这里解决问题的方式，就是利用红黑树的自旋的特性，如果出现线性的情况，红黑树的节点会进行左旋转或者又旋转，来达到树状结构的自我平衡

简单理解如图：

旋转重新组成合理的树状结构，这样树的高度降低，查找的效率提高！例如主键：

在大数据量的情况下，红黑树依然会有性能问题，例如百万或者更多级别的数据，红黑树的高度依然会特别高，因为基本的特性就是二分查找。此时B-Tree就出现了，在纵向高度无法突破的情况下，只能考虑横向扩展，可考虑横向放的索引数据增加，让一个节点存放更多的索引元素，同时可以分更多的叉出来。在磁盘上分配更大一块磁盘空间，一个大的节点里面可以放更多索引元素

对红黑树进行一定的改造之后，就可以支撑大数据量的查找，高度可控制在5以内。

B-Tree : 叶节点具有相同的深度，所有索引元素不重复，叶节点的指针为空，节点中的数据索引从左到右递增排列。

MySQL的索引底层用的 B+Tree ,是对B-Tree的一个改造，实际上是一个多叉平衡树。

B+Tree : 非叶子节点不存储data（有可能是此索引所在磁盘文件地址指针，也可能是此索引所在行的其他列的所有数据信息，跟存储引擎有关），只存储索引，可以放更多的索引；同时叶子节点维护了顺序访问指针（mysql优化为了双向指针），提高区间访问的性能。（所有的索引元素都是存储在叶子节点里面，如下图示）

特点：

1.会从叶子节点里面取很多相同的数作为冗余索引，用于构建B+Three。所有的数据都存储在叶子节点上

2.物理层面都称之为在数据页上面，每一个节点从左到右是排好序的（B-Three也具备），不论是节点内还是节点之间都是从左到右依次递增；

3.B+Three相邻叶子节点之间有一块空间维护了指针，指向前后的节点，B-Three没有

4.B-Three因为data元素在每个节点都有存储，导致在节点大小一样的情况下，所存储的索引将会变少，意味着树的高度会增加，所以选择了B+Three

5.范围查找的情况下，B+Three有指针，直接找到节点拿到数据结果集，B-Three 节点上没有维护双向指针，没法指向下一个节点，找到一组数据后，只能再次从根节点load内存中再去查找直到得到结果。

相关的索引数据只存储在叶子节点里面，这样做可以让非叶子节点能存储更多的索引，但也并不是说每一个节点索引可以无限存储，因为最后都是会放到内存里面去进行运算查找，需要考虑内存及成本。目前MySQL的每个节点分配的空间可以通过命令查看：

SHOW GLOBAL STATUS LIKE 'INNODB_page_size';

磁盘上每个节点分配了16384个字节，也就是16KB的空间。

将节点load进内存，就是一次磁盘IO，那么存储索引的时候，一个节点最多能存储 16k/14B=1170个索引(bigint类型 8字节+下一个文件地址6字节/指针)；叶子节点由于存储了索引及相关的数据，叶子节点假设每组大小为1KB则一个叶子节点可存16个数据；如图所示，B+Tree高度为3的时候，理论上所能存储的数据量则为：1170*1170*16=21902400 两千万的数据量。也就是千万级别的数据量情况下，树的高度只有3，效率很高，最多三次磁盘IO就能找到。（MySQL根节点常驻内存，高版本，有可能是把所有的非叶子节点都放在内存中的）

B+Tree 叶子节点维护有指针，右边元素的索引值都是大于左边的，所以当出现范围查找的时候，只需要找到对应的索引即可找到想要的结果集。顺着指针依次将后面所有的元素放到结果集里面，如果没有这个指针，则会每次都要再从根节点开始去找对应的索引找数据。

Hash：MySQL目前支持BTREE 和 HASH 两种索引方法，也就是两种索引存储数据结构，用Hash散列算法，key为索引值，value为存储数据的磁盘文件地址，同hash算出来的值进行映射，这样进行单条数据查找的时候会非常快，但是一般数据库用的很少，虽然在进行单一数据查找很快，但是hash无法支撑范围查找，范围查找的时候性能会非常低，所支持的场景单一。

1.对索引的key进行一次hash计算就可以定位出数据存储的位置，一般经过一次磁盘IO

2.仅能满足 “=”，“IN” 不支持范围查询，如果用不到此索引，就会全表扫描，B+Three支持（叶子节点有双向指针）

3.hash冲突问题

不同的存储引擎（引擎是用于形容数据库表的），底层用B+Tree存储的时候，存储文件还有一点点区别

1.MyISAM的索引文件和数据文件是分离的（非聚集）

磁盘上文件存储的时候，一张表至少三个文件，*_myisam.frm 表结构文件，*_myisam.MYD 表数据行文件，*_myisam.MYI 存储表数据的索引（B+Tree结构）。如果是这种类型的存储引擎，命中索引的时候先去 MYI文件中定位到这个索引，找到这个文件中存储的对应的磁盘文件指针，此时（叶子节点的data存储的是这个索引所对应的数据所在磁盘的所在行的文件指针），再去MYD文件中找到对应的数据，放到内存中去。

2.InnoDB的索引实现（聚集：叶子节点包含了完整的数据记录），支持事务，用的最多

一张使用InnoDB存储引擎的表，对应的文件有两个*_innodb_lock_frm （表结构文件）、*_innodb_lock_ibd（存储了索引+数据），（叶子节点里面的data 存储的是相应的行数据），比MyISAM少了一次磁盘IO。

为什么InnoDB必须有主键，且推荐使用整型自增主键？

表数据本身就是按B+Tree组织的一个索引结构文件，聚集索引-叶子节点包含了完整的数据记录

1.如果不设置主键依然可以建表存储，但是底层会自动识别一个字段或者单独建立一个字段作为主键。如果没有选到，会建一个隐藏列，帮你进行维护，帮你组织整张表的数据

2.非主键索引，data存储的不是具体的行数据，而是主键索引

3.用整型是因为查找索引的时候会经过很多的比较运算操作，这种情况下其他类型效率不会很高，比如用UUID的时候，字符串进行比较，需要逐位对比，如果英文跟数字又需要转位ASCII码来比较大小，影响效率。

4.如果非自增主键，就会存在后续新增的数据可能在之前的节点的中间，由于data存储的行数据，一个节点16KB大小，如果本来这个叶子节点已经这么大了，现在又要新增一个主键索引和数据，之前的叶子节点就需要做一个数据页分裂操作，才能将新增的数据加进去，影响性能。用自增，则可以保证每次新增的数据都是在后面，不会遇到数据页分裂的情况。

为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

MyISAM 非主键索引和主键索引没区别都是存储的文件地址值；InnoDB只有一个聚集索引（主键），如果建有非主键索引（二级索引），叶子节点data数据存储的为主键值，可节约存储空间（不可能有多少索引，放多少份数据，会浪费空间），每加一个索引，在索引文件中就会加一个树结构存放索引数据。如果用二级索引去查询数据，会先根据二级索引找到对应主键，再到聚簇索引中去寻找数据，这就是回表操作。

常用的联合索引的底层存储结构（复合索引：多个字段共同组织成一个索引）：

索引是排好序的数据结构

一般一张表不建议建很多单值索引，尽量用两到三个联合索引来解决，底层也是排好序的数据结构。底层依然是B+Three

索引最左原理

联合主键

如果第一个字段就已经能区分大小，就不会再看后面的字段了（如果是字符串会转ACSS码去对比），比如ellen 和 hellen 这两个索引值就是如此排的。

如果第一个字段都相等，接下来就看第二个字段，如果前面两个字段都相等，就看第三个字段，联合主键索引不可能存在三个字段都相等的情况，（如果是辅助索引可能相同，但是存放的data主键是不同的，那么就根据主键再去区分）

如果数据放满，需要提升某个索引，都是提升的最小的那个节点上去作为冗余索引，来构建这个树。

SELECT * FROM student WHERE name='ajax' and age=19;  # 肯定会走索引
SELECT * FROM student WHERE age=30 and score =20;    # 不会走索引
SELECT * FROM student WHERE score=30;                # 不会走索引

跳过了 `name` 就不能走索引，必须要先用 `name`字段才能走这个联合索引，因为是排好序的，比如第二条数据，跳过name 直接走age=30, 在第一个叶子节点里面age是排好了的，但是在整张表里面，并不是排好序的，就不符合索引的原理（排好序原理），因为现在不是排好序，意味着需要全表扫描才能找到所有age=30的元素！