MySQL：底层数据结构与算法

这篇文章主要是介绍mysql底层的数据结构以及算法，主要包括索引数据结构红黑树、Hash、B树、B+树；索引是怎么支撑千万级表的快速查找；面试常见问B+树索引面试题解析；联合索引底层的数据结构是怎样的等问题。工作中经常遇到慢查询的问题或有时候DBA给我们发了一条慢查询SQL让我们去优化，我们第一时间想到的优化方法就是加索引，看看查询条件是不是走了索引以及啥时候走的索引，看是不是走了索引可以用explain查询工具。如果遇到一条慢查询SQL可以加索引，如果索引加的适当的话，即使是一条非常大数据量的表，加上索引之后，都会将SQL查询的效率提高一两个数量级。

一、索引的本质

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

1.1常见的索引数据结构

如下图是一张两列七行的表，这张表如果没有任何索引的话，想要从表中找到 0x77这行数据，用如下的SQL语句查询：

select * from t where t.col2=89;

如果这张表里面没有任何索引，那么这个SQL肯定是从第一行开始找起，会去比较第一行的col2字段是不是89，比较之后发现不是89，再接着找第二行…会逐行查找，一直会找到col2的值为89的这一行，没有索引会对这张表进行全表扫描。

数据库里面的数据都是放在mysql数据库安装目录下面的data目录下面，如下图所示：

如果表没有建立任何索引，那么mysql就会逐行从磁盘上一行一行读取，查询数据数据然后和我们的数据条件进行比对，查一行数据就是和磁盘做了一次IO操作，如果一行一行读取对比的话性能就比较慢。比如上面的select * from t where t.col2=89;这个查询语句，我们一行一行查询的话，至少要经过6次磁盘IO，如果表的数据很多的话，那么肯定会经过更多的磁盘IO，性能会很差。如果我们给这张表加上一个索引，

1.1.1二叉查找树(BST,Binary Search Tree二叉查找树)

左子树每个节点的值都不大于该节点的值，右子树每个节点的值都不小于该节点的值，由于篇幅原因，关于二叉查找树、红黑树、B树、B+树可以。但是如果把表中的数据合理的放到一棵二叉树中来的话，再要用select * from t where t.col2=89;这个查询语句查找的话，只需要做两次磁盘IO，如下图：

二叉树节点里面存放的是两个元素，一个是key一个是value，key就是我们的col2索引字段，value存放的是索引所在行的磁盘文件指针，比如key=89对应的索引所在行的磁盘文件指针是0x77，就可以根据0x77这个磁盘文件指针就可以快速的去磁盘上定位到那个节点，把那个节点加载到内存中，这就找到这一行数据了，可以看到使用二叉树的数据结构索引来存储的话查询效率会比没有加索引快很多。但是mysql数据库使用的数据结构并不是二叉查找树，因为当存储有序自增(自减)的这种数据的时候，数据从根节点顺序向左递减或者从根节点开始顺序向右递增的时候，这个时候二叉树会退化成链表，所以二叉查找树不适合做索引。

1.1.2红黑树

红黑树部分可以看前面的一篇博客：数据结构：二叉树、二叉查找树、红黑树、B树以及B+树一锅端
，MySQL之所以没有选择红黑树来存储，是因为当数据量特别大的时候，比如有1000w条数据的时候，有1000w条索隐要存储到红黑树中去，红黑树的高度是log2(1000w)=24，即1000w条数据如果用红黑树存储的话，树的高度是24，这样如果我们要查找的字段刚好位于叶子结点，这样我们最少要经过24次磁盘IO才能查找到我们的元素，而且红黑树在存储过程中要经过着色和自旋等复杂过程，这时候买个东西要等一小时才能出结果买家心态都崩了，所以红黑树不适合做MySQL索引。

1.1.3 Hash表

1.1.4 B-Tree

B树数据结构部分可以看看这里：数据结构：二叉树、二叉查找树、红黑树、B树以及B+树一锅端
从下图可以知道，如果要查找元素6，只需要查找2次即可：

但是B树存储也有个弊端，B树是以key-value形式存储的，value有可能存储的是数据表中的一行数据(innoDB是这么玩的，存储所在行的所有字段)，此时如果一个节点申请的存储空间为16k，每个key对应的data数据过大，则这个节点存储数据条数就越少，这样会造成树的高度比较大，磁盘IO比较多(但是还是要比红黑树要小)。

1.1.5B+ Tree

(1)概念

• 非叶子节点不存储数据，只存储索引(冗余)，可以放更多的索引；
• 叶子结点包含所有索引字段；
• 叶子结点用指针相连，提高区间访问性能
  
  (2)B+树从B树的优化点：
• B树所有节点都存储了data元素，而B+树所有非叶子结点都不存储data元素，B+树的非叶子结点可以存储更多的索引
• B+树叶子结点之间用指针连接，对于select * form t where c.col2>20这种范围查找有很好的支持；
• MySQL 对 B+Tree 做了优化，叶子节点使用的是双向指针，双向链表，加了方向指针方便进行范围查找；

(3)B树查找实战：
比如我们要查找 49 的数据：

• 先将根节点的数据(15, 56, 77) 做一次磁盘 I/O 操作取出加载到内存中，然后再在内存中做比对，找到对应的指针，查找到其对应的节点；

II. 将指针指向节点的数据(15, 20, 49) 做一次磁盘 I/O 操作取出加载到内存中，然后再在内存中做比对，找到对应的指针，接着去叶子节点获取数据；

(4)查看MySQL文件页大小（一个节点的大小）：

SHOW GLOBAL STATUS like 'Innodb_page_size'；

(5) MySQL页文件默认为16KB，树的高度为3，能够存储多少数据？
们先看非叶子节点，假设主键ID为 bigint 类型，那么长度为8Byte，指针大小在Innodb源码中6B，一共14B，那么一页（即一个节点）可以存储 16KB/14B=1170 个索引元素和 1170个指针；根节点有1170个索引和1170个指针，树高度为2的节点就有1170个，那么叶子节点的数量为 1170x1170；每个叶子节点可以存储16KB，若每条数据比较大为1KB，那么每个叶子节点可以存储16条数据；那么，高度为3的 B+Tree 的叶子节点可以存储的数据量为 1170x1170x16=2000W；

(6) 在实际的MySQL中表的索引存储可以选择 Hash 或 BTree（MySQL索引实现的两种方式）
若索引使用的 Hash 存储的，当使用select * from t where col2=6这种查找的时候，存储的时候先做一次hash运算，根据

hash 的值就可以快速的定位数据的磁盘指针，这样就不管表里面有多少数据，我们的查询效率都非常的快；
在实际中为什么使用 B-Tree 或 B+Tree 来存储索引的方式更多，而不太使用 hash 呢？
原因1：若使用 select * from t where clo2 > 6，这种查找模糊范围的SQL，那Hash就不能搞定了，就不会走索引了；而且对排序hash也没有办法；
原因2：hash会产生 hash 碰撞，MySQL的底层对hash做了处理，很少会发生hash碰撞的；

二、MySQL存储引擎的实现

同一个数据库中不同的表可以设置不同的存储引擎。MySQL的数据是存储在data目录下面的，文件夹以数据库为单位，数据库文件夹下面存放数据：data/{数据库名}/表文件。

2.1MySAM存储引擎的实现

MySAM存储引擎的索引文件和数据文件是分开的，MySAM存储引擎的一个表有三个文件：.frm文件存储表结构定义、.MYD文件存储表数据、.MYI文件存储表中的索引字段数据。

MySAM存储引擎的索引的叶子结点的data中存储的是索引所在行的磁盘指针，它是一个非聚集索引。MySAM存储引擎的主键索引和非主键索引的存储的是差不多的，InnoDB的主键索引和非主键索引的存储是不一样的。

MySAM索引的数据结构是B+树，必然当我们执行:selelct * from t where col1=49;这个SQL语句的底层执行逻辑是：首先判断字段是否走索引，如果走索引的话，直接将定位到的49这个叶子结点全部加在到内存，然后在内存中去.MYI文件中快速定位到clo1=49这个节点元素，其data元素存储的就是49所在行的磁盘文件指针，快速通过.MYI文件中定位到的索引从.MYD文件中拿数据。

2.2 InnoDB存储引擎实现(聚集索引)

InnoDB存储引擎索引文件和数据文件是合一的(聚集)；InnoDB 存储引擎的1个表有2个文件：*.frm 文件存储表的结构； *.ibd 文件存储的是索引和数据；聚集索引和聚簇索引是一个东西的不同叫法，InnoDB的主键索引就是一个聚集索引，聚集索引就是索引和数据列全部放到一个文件中存储；非聚集索引就是MySAM的存储方式，他的索引文件是不包含索引所在行的数据的MySAM的索引是存储在.MYI文件中，而数据存储在.MYD文件中，索引和数据是分开存储的。

2.2.1表数据文件本身就是按照B+树组织的一个索引结构文件

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

2.2.2 聚集索引-叶子节点包含了完整的数据记录

InnoDB 存储引擎的索引的叶子节点的data中存储的是索引对应的所有数据；----聚集

2.2.3为什么InnoDb表必须包含主键，并且推荐使用整形的自增主键？（面试题）

a. 因为 MySQL对于 InnoDB 表设计的就是按照 B+Tree 组织存储数据的，若没有主键就没有办法去存储数据了；但是在平常我们建表的时候没有指定主键也是可以建成功的，这是因为 MySQL 会生成一个 rowid作为数据的唯一标识，mysql找了一行唯一索引作为主键索引，在每插入一行的时候给这个rowid生成一个整形的数值；

b. 若使用的 UUID 作为主键，在查找的时候需要去比较大小，字符串UUID比较的效率肯定低于数据的比较；在进行比较的时候会把数据拿到内存空间中做比较，UUID为字符串占用的内存空间就会较多；

c. 若是递增的，则插入的数据直接向后排，这个节点满了，直接新增一个节点就好了；若不是递增的，有个节点存储满了（5, 9），但是新插入了一个数据（7）在这个节数据的中间，则需要将这个节点先分裂，再平衡去满足 B+Tree 的结构；

2.2.4为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间，面试题)

在使用非主键索引查找的时候，先从非主键索引的树中查询到对应的主键值，然后使用主键值去到主键索引的树中去查找；
对于非主键单值索引，若索引字段的值为 null，则它的数据不会放到非叶子节点上，是放在叶子节点的链表的最前面的；（强烈不建议字段设置为null）
回答问题：为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）
答：如果一张表既有主键索引又有非主键索引，那么在插入数据之前先要维护一下索引，然后再将数据插入进去；若主键索引和非主键索引的叶子节点都存储具体的数据，则一个 insert语句插入成功的判断就是向主键索引中插入成功且向非主键索引中也插入成功，这样就造成了事务的问题，事务是很耗性能的；当然，如果主键索引和非主键索引的叶子节点都存储具体数据，会造成数据的同样的数据存储了几份，就造成了空间的浪费；

2.3 联合索引的底层存储结构长什么样？

以上的主键联合索引从左到右由字段 a，b，c 组成，会将多个字段一起存储到key里面去；联合索引在存数据或比较的时候，先比较联合索引最前面的字段，若最前面的字段值一样，则再比较第二个字段的值；联合索引的索引字段中有一个值为null，则将其放在叶子节点的最前面；可以认为null值是最小的。