Mysql的索引底层数据结构

当在实际开发中，正常的sql执行效率都会出现在几百毫秒内。一旦超过一秒或者几秒算得上就是慢sql了，那么我们如何优化这些慢sql呢，我想你一定会想到索引，加了索引之后sql的查询性能会提升很多倍，这是为什么呢？原理是什么呢？。

下面我们介绍下Msql的索引数据结构

索引就类似我们书的目录页，当你在目录中寻找你需要的内容时就会很快找到是具体的哪个位置多少页。

索引的本质

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

下面我们看一个案例

                                                                        图1

对于上面这个2列7行的数据，假如执行select * from t where t.Col2=89 ,我们从从第一行往下要比对6次才能匹配到结果，而且数据库的数据是持久化在磁盘当中的而且是不规则分布的，就相当于每做一行数据的比对就会进行一次磁盘的I/O操作，这种效率是非常底下的。

所以mysql的索引就是解决这个痛点。

索引数据结构

• 二叉树

二叉树：从根节点开始，左边的子节点小于根节点，右边的子节点大于根节点，并且同一节点排好了顺序。

假如我们的索引数据结构是一个二叉树，如图1所示，给Col2添加一个索引，当我们需要查找Col2=89的时候，从上往下只需要进行两次的比对，只需要进行两次I/O操作就能查到对应数据，这就大大增加了我们的查询效率。

痛点1：如果是对于字段Col1加索引，Col1数据是依次有序递增的，我们看如下图。

                        图2

这个二叉树的结构就和链表一样了，查找Clo1=6也就需要查找6次了，效率并没有提高。

痛点2：当数据量大的时候，树的高度太高，查询效率依旧很慢。（所以hashMap在jdk1.8的时候当链表长度大于8时就会转化为红黑树）

• 红黑树

红黑树：是一种自平衡的二叉查找树，红黑树的每个节点都被赋予了一个颜色属性，这些颜色要么是红色，要么是黑色。会通过特定的操作来保持二叉查找树的平衡，从而获得较高的查找性能。

假如我们对Col1的索引使用红黑树结构,我们会得到如下图的一个数据结构

我们查找Col1=6的时候只需要查询3次，红黑树就解决了二叉树的痛点1。

但是痛点2依旧存在,当数据量很大的时候树的高度也会非常的高，查询效率也不会很快。

• B-Tree

1. 叶节点具有相同的深度，叶节点的指针为空
2. 所有索引元素不重复；节点中的数据索引从左到右递增排列
3. 节点中除了索引还带有数据data，data就是数据在磁盘中的地址

B-tree的目的是为了给每一个节点划分一定的空间，让不超过这个空间总大小的所有索引依次存放在这个节点中。这样做的目的是为了解决痛点2，减少树的高度,增加查询效率。

痛点3：B-Tree进行范围查询时，由于叶子节点之间都没有关联，比如查询 20< col<78  20在一个叶子节点上 49之后的 又在另外两个节点上，那么另外两个节点又得从根节点开始查找，会很麻烦，性能也不好。

• B+Tree

1. 是Mysql对B+Tree做的一种升级。非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引（因为空间有限）
2. 叶子节点包含所有索引字段（因为第三层叶子节点就可以放两千万的数据了，并且更利于检索）
3. 叶子节点用指针连接，提高区间访问的性能（解决痛点3）
4. 节点中的数据索引从左到右递增有序排列

查找30的逻辑如下：

1. 把根节点load到内存，找到15数据页
2. 把15数据页的节点load到内存，找到20数据页
3. 把20数据页load到内存，找到30对应的索引
4. 根据30索引的data找到磁盘中对应的数据（或者索引所在行的其它列数据就在data中）

Mysql给B+Tree 一页内存默认分配的是16KB

索引的最终目的是减少查询时间，在磁盘中最耗费时间的是I/O操作，所以mysql给InnoDB的索引数据页分配16KB就可以放置更多的索引数据。你可能会问为什么不再放更多索引在一页中呢或者全部放到一页中，这样肯定不行，内存的空间是昂贵且有限的，这样操作会内存是扛不住压力的甚至会给内存撑爆；而且数量很大进行I/O和在内存中进行数据比对也不会很快。16KB是Mysql觉得最合理的。

参考算法：第一层可以放 16KB/(8+6)B=1170 （个索引）；第二层的一页可以放16KB/(8+6)B=1170 （个索引）；第三层 16KB/1KB=16（个索引+data）；

树的高度为3层最总叶子节点可以放 1170*1170*16=21902400（个索引+data） （三次磁盘I/O）

而且高版本的Mysql会把所有的非叶子节点加载到常驻内存中，就等于只需要进行叶子节点的一次I/O就够，那速度是相当的快了。

解决痛点3：对于叶子节点有双向指针，由于B+Tree的页是排好序的数据结构，当进行范围查询 >a   或 <a时  只需要在索引中找到a的位置，然后再通过a的双向指针取大于或者小于的索引就行了，不需要再次从根节点开始查询了。

• hash

1. 对索引的key进行一次hash计算就可以定位出数据存储的位置
2. 很多时候Hash索引要比B+ 树索引更高效
3. 仅能满足 “=”，“IN”，不支持范围查询
4. hash冲突问题

                                                        

hash创建索引会把索引列的数据存储到一个hash结构里边，如上图。理想情况下有可能只需要进行一次磁盘I/O就定位到索引的位置了。但是hash索引只是用与等值查询，对于范围查询它就失效了需要走全表扫描

mysql的存储引擎是针对表的，而不是针对数据库。在navicat工具中建表就能看见可以选择存储引擎。

Mysql常用存储引擎之MyISAM

MyISAM索引文件和数据文件是分离的(非聚集)

叶子节点存的data为索引数据所在行的磁盘地址

myisam存储引擎创建的表会生成三个文件

• .frm文件：数据表结构相关信息
• .MYD文件：表数据
• .MYI文件：索引

Mysql常用存储引擎之InnoDB

InnoDB索引实现(聚集)

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

                                                                        图3

                                                                        图4

InnoDB存储引擎创建的表只会生成两个数据文件

• .frm文件：数据表结构相关信息
• .ibd文件：表索引+数据

1. 图3中的索引就叫做聚簇索引，叶子节点data中放的是索引所在行除了本身其它所有列的数据，就等于 索引+data=一行完整数据。
2. InnoDB的辅助索引和MyISAM的索引都叫非聚簇索引：索引和数据是分开的。查找数据需要进行回表操作。
3. 图4中的索引叫非主键索引或叫辅助索引
4. InnoDB表中只有一个聚簇索引，辅助索引的叶子节点中的data就是存的对应聚簇索引中得主键索引位置。

下面我们看两个面试题

1.为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

mysql中innoDB存储引擎创建的表，在存储数据的时候必须要用一颗B+Tree来组织，如果表有主键索引，那么这个主键索引就是这颗B+Tree。如果没有建立主键，mysql会自己选一列数据都不相等的列来组织B+Tree，如果没有这样的列，那么mysql会给这个表建立一个隐藏列(维护唯一ID)来组织B+Tree。所以建议innoDB表必须建主键，以免给mysql资源带来不必要的浪费。

推荐使用整型作为主键的原因之一：查找索引，整型比较大小肯定比字符串性能好。

推荐使用整型作为主键的原因之二：整型占的存储空间比字符串要小很多

推荐使用整型自增作为主键的原因：B+Tree数据结构，当插入一个有序数据时，B+Tree会在子节点后面再添加一个节点存放元素；当存放一个不是有序的数据时，B+Tree会对叶子节点进行分裂然后做在平衡（耗费资源）；所以选择整型自增作为主键的效率肯定比不是自增得好。

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

1.数据存放在聚簇索引中 而不存放在辅助索引中，可以大大得节省存储空间

2.如果数据存放在多个索引中，有可能导致数据不一致。如果给主键索引先插入数据，再同步到辅助索引是可以解决不一致的问题，但是使事情的复杂度变高了。需要减少系统的复杂度

索引最左前缀原理

联合索引的底层数据结构长什么样？

联合索引：多个字段公共组织成一个索引

InnoDB的联合索引也是B+Tree的数据结构。如上图中建立了三个字段的联合索引

会按照索引建立的先后顺序，根据每个字段联合排序维护到B+Tree中，第一个字段都相同，就会看后面的字段，第一个字段不相同就会直接排好序。

使用联合索引必须也得按照建立索引的顺序去用

比如 select * from t where t.name='Bill' and age=30;会生效

select * from t where t.position='dev' and age=22;就不会生效

最左前缀原理其实就是基于排好序来实现的。