深入浅出：Mysql索引底层原理

一 索引的概念简介

对于一个开发，数据库是大家离不开的工具，特别是mysql，在目前很多中国的互联网企业大多选择mysql作为存数数据库。由此，数据库索引的正确使用对于每位工程师尤其是后端工程师来说是必须要掌握的技能。

那么什么是数据库的索引呢？上大学时老师大多是这样讲的：索引就是数据库的目录。细细品味这个定义，或许能够形象的说明索引在数据库的作用，但是看完大家往往还是不够清楚索引到底是个啥。

我们先来思考一个问题，假设没有任何索引，我们的SQL怎样去查找数据呢？

create table ‘student’

id	name	age
1	小明	23
2	小红	24
3	小绿	25
4	小蓝	26
5	小黑	27
6	小蓝	28
7	小黑	29

比如上面这张表，假设执行select * from student where name = ‘小蓝’；

那么mysql只能从第一条数据开始遍历，找到第一条数据的name字段，和要查询的‘小蓝’进行比较，如果是则返回一条数据并接着比较第二条，如果不是就继续比较第二条，直到把表内所有数据都轮循一遍最终才能拿到结果。

上述过程在数据量比较小的时候我们或许能够接受，但如果是上百万甚至上千万的数据，轮循过程是非常长的，需要的时间也是不可接受的，所以我们必须需要一种方式来快速查询我们需要的数据，而我们较易想到的就是各种各样的数据结构帮助我们把数据排个序，以方便我们快速查找，索引由此而来。

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

二 为什么是B+ Tree

首先给大家推荐一个非常好用的网站，数据结结构可视化工具https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

上面说到索引是一种排好序的数据结构，那怎样的数据结构才能最好的支持对数据的高效访问呢？

说到数据结构，我们马上能想到的是各种树形结构，二叉树，红黑树等等，下面我们来探究下这几种树的存储。

1，二叉树

二叉树非常简单，就是每个节点最多有两个子树的树结构，遵循右边的节点比左边的节点数字更大。假设按照数据ID的顺序对上面的表的age字段建立索引，二叉树结构如下

可以看到所有数据都集中在了右子树上，也是要通过轮询去查找，跟没有建索引相差不大，显然简单的二叉树结构并不合适。

2，红黑树

红黑树是一颗自平衡二叉查找树，也是我们常见的数据结构，JAVA8中的HashMap就是用红黑树进行了优化，大大增加了查找效率。假设我们用红黑树来存储上面的age索引字段

如果我们要查找age=29的，只需要查找4次，已经比二叉树进步了很多。

现在假设有数以百万的数据，2^20≈104 0000，红黑树的高度h>20了，形成了一棵很高的树。如果要查找叶子节点上的数字，经过的查找次数大于20次了，效率也并不高

3，B-Tree

怎样减小树的高度h呢？如果在每一个结点位置多放几个数据，并且每个数据位置都有左右两颗子树，这样树的每一层就会多存储很多数据。

BTree是平衡搜索多叉树，设树的度为m(m叉树，m>=2)，高度为h,则BTree要满足以下条件：

• 树中任何一个结点最多含有m个孩子;
• 每个非叶子节点由n个指针point和n-1个key组成，其中ceil(m/2)<=n<=m，ceil是向上取整函数，key和point相互间隔，结点两端一定是key;
• 每个叶子节点的高度都相同，等于h，叶子节点的指针都是null
• 非叶子节点的key都是[key,data]二元组，其中key表示索引值，data是索引所在行数据或者磁盘地址
• 所有的索引key元素不重复

简化一下的图如下

在BTree的机构下，使用二分查找方式，查找复杂度为h*log(n),一般来说树的高度是很小的（三中会做具体探讨），一般为3左右，所以BTtree是一个十分高效的查找结构。

对于BTree和下面的B+Tree的查找，增加，和删除过程不做具体赘述，如需要可参考：https://blog.csdn.net/endlu/article/details/51720299

4，B+Tree

B树还有优化的空间，而mysql索引实际上使用的是B+Tree。

B+Tree是BTree的一个变种：

• B+Tree的非叶子节点不存储数据值data，只存储健值，这样同样空间可以存储更多的健值
• 叶子节点没有指针，所有的键值（包括父节点已经有的键值）都会在叶子节点上，同时会对应存储data，行数据或者数据地址值
• 非叶子节点由n个键值key和n个指针point组成
• 所有的索引key元素有冗余（下图的最左边的15在每一层级都有）
• 叶子节点有横向指针连接，大大提高区间访问速度
  

对于横向指针，我们在进行区间查找（>,<,<=,>=,between and ）时也是非常希望用到索引的，这时横向指针就可以方便的取到一整个区间的索引值。这一点也是hash索引没有被广泛应用的原因（无法支持区间查找）。

三 如何做到支持千万级表查询

我们知道的是数据库存储的数据都是存储在磁盘上的，而索引也同样是会存储在磁盘上，只是会把根节点数据预加载到内存里。sql通过根节点先查找数据，再根据指针point将其子节点加载到内存进行查找，直到找到叶子节点的data。

mysql会预设定每一个结点的占用空间，可以通过如下sql查询：

如图所示，mysql预设值的索引结点的大小约为16kb

这一点也足以说明B+Tree会比BTree更加适合索引，因为BTree的每一层上的每一个叶子节点上都要存储point和key(包含data)，某些情况下data是每一行的全部数据，会占用比较大的存储空间，导致叶子节点不能存储更多的索引，而B+Tree每一行只有pioint和索引值，可以尽可能多的分布索引值。

下面我们以主键索引为例，来看下一颗h=3的B+Tree可以存储多少数据：

假设id是bigint类型，拿一个id主键索引占用8字节（b）的存储空间，一个point指针大约占用6b存储空间，也就是说对于非叶子节点，一条主键索引的存储大约要占用14b的存储空间。

上面已经达到叶子节点大小为16kb,每个索引占用14b，可以得到每个非叶子节点大约可以存储1170个索引数据。

由于叶子节点要存储data，我们假设最坏的情况，data存储的是整行数据（不同的存储引擎不一样），每行大约占1kb的空间，那每个叶子节点能存储16个数据。

则综上，h=3时，一颗B+Tree大约能存储1170 × 1170 × 16 ≈ 2100 万 条数据

可以看到B+Tree只用高度3就存下了2000w的数据索引，这是非常有意义的。上面提到mysql不会把索引的全部数据结构加载到内存，而是根据查到的point一层一层加载，更小的高度h就意味着加载的次数更少，磁盘I/O更少，这一点也是提升查找速度的关键。

另外值得注意的是，B+Tree对于每条数据的查询速度是非常稳定的。因为非叶子结点不存储数据data，所有查询都会最终查询到叶子结点，而叶子结点的高度都相等，因此所有数据查询速度会大体相等。

四 myIsam和Innodb存储引擎的索引实现

我们知道数据都是存储在磁盘上的，那是以怎样的形式存储的呢？来看下面的图
（存储引擎的区别是对于每张表而言的，这一点需要清楚）

innodb存储引擎有frm和ibd2个文件
myisam存储引擎有frm,MYD,MYI3个文件

见名知意，frm即frame，框架，存储的是我们的建表语句DDL；
myisam的MYD,MYI，D即Data，I即index，就是表的数据和索引；
而在innodb中数据和索引一同存储到ibd文件中了

由此我们何以引出一个重要的概念，聚集索引和非聚集索引，实际上聚集索引就是将索引和数据存储到了一个文件中，而非聚集索引就是将索引和数据分开存储。

myisam存储引擎

myisam存储引擎存储索引具有如下特点：

• 是非聚集索引，索引和数据存储在两个文件中
• 叶子节点存储的是这条数据在数据文件（MYD）中的物理地址
• 主键索引和辅助索引的存储形式几乎是一样的，主键索引不重复，不能为null

在使用myisam存储引擎的表中根据索引查找数据的顺序如下：
根结点（常驻内存）查找到指针point —》 根据point I/O下一层结点，查找到point —》I/O叶子结点的数据到内存，查找到磁盘文件地址—》MYD文件中查找到需要的行数据

innodb存储引擎

innodb存储引擎索引具有以下特点：

• 表数据文件本身就是按照B+Tree组织的一个索引结构文件（ibd）文件；
• 主索引是聚集索引，叶子结点存储的是健值对应的数据本身data，辅助索引存储的叶子结点存储的是键值对应的数据的主键健值。因此，主键要选择合适的类型和长度，类型越简单越好，长度越小越好；
• 辅助索引中叶子节点存储主键的好处，一致性，行移动和页分列式不需要再改变叶子结点的存储方式；
  更少的存储空间，不需要存储整行数据；
• innodb存储引擎中必有主键索引，即使没有显示建立，mysql也会自己建立主键索引；
• 聚集索引的数据是根据主键的顺序保存的，因此推荐使用整型的自增ID，如果不是自增，B+Tree可能需要进行复杂的调整以保证符合整个B+Tree的特点；

使用innodb查询数据时，如果是根据主键索引，就直接在主键聚集索引的B+Tree可以查找到叶子结点的整行数据；如果是辅助索引，则先在辅助索引的B+Tree上查找到主键id，再渠道主键的B+Tree上查找到整行数据。

两者对比

简单的总结对比一下：
1，使用主键索引查询时，聚集索引只需查找一次，而非聚集索引需要查找两次，会启用更多的磁盘I/O
2，叶子结点而论，聚集索引要比非聚集索引占用更多的空间。这样会导致聚集索引再插入数据时比非聚集索引慢，因为需要检测主键是否重复，需要遍历所有的叶子结点，占用空间少的就会有更少的磁盘I/O
3，聚簇索引存储的是主键id,数据顺序和索引本身的顺序相同；非聚集索引的主索引和辅助索引，索引的顺序和数据物理顺序无关

五 复合索引原理

如图，联合索引就是将多个索引一起放到了B+Tree的结点上作为key，并且严格按照建索引是的顺序排列。例如建立（a,b,c）三列联合索引，那会先根据a索引排列，再根据b索引排列，最后是c。如上图，上面结点是先按照10002,10004排列，左下结点在10001仙童的情况下，在按照A,E,S排列，右下结点前两列索引都相同，再按照日期排列。

那么现在你应该可以试着解释为什么联合索引遵循最左前缀原则了。

浅出总结：

1，索引是帮助MySQL高效获取数据的排好序的数据结构；
2，mysql采用B+Tree作为底层索引的数据结构，因为它能在很小的高度下存储千万级的数据
3，myisam使用非聚集索引，索引文件和数据文件分开存储，叶子结点保存数据地址
innodb主索引使用聚集索引，表数据文件本身就是按照B+Tree组织的一个索引结构文件，叶子结点存储整行数据，辅助索引的叶子结点存储主键值
4，横向指针是为了方便区间查找
5，联合索引按顺序将多列数据存储到一个结点的一个索引key值上

欢迎关注个人公众号一起学习：