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

106 阅读8分钟

一、索引是什么

索引是一个排好序的数据结构,帮助MySQL高效获取数据,减少IO(磁盘的一次IO是不高的)次数;
解决的目标:
1)减少IO次数;
2)检索次数;
3)准确、精简、可控;

二、索引数据结构

1)二叉树;

存储的每个节点存储的结构为:Key:Value;Key就是值,Value就是对应磁盘的地址;
利用二叉树的特性(右边大于左边)进行查找处理;
存在等问题:如果创建索引的列是顺序递增类型(col1)那么会出现单边增长的情况,一旦出现这种情况,加了索引等于没有,没有减少查找的次数;
![](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4b61cb9e8662406db3ee8f34026fdf69~tplv-k3u1fbpfcp-zoom-1.image)

2)红黑树;

又称二叉平衡树,会自动将子节点进行自旋自动平衡;
存在的问题:如果数据量很大的情况下,创建的子节点会很多,造成节点过高(即不可控),在查询叶子节点的时候I/O次数不可控;
3)Hash表;
![](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/39d0ca80fb594fb28d57b9d454ad13f3~tplv-k3u1fbpfcp-zoom-1.image)
对值内容先进行哈希闪电算法进行运算,存入对应的HASH表中,在查询中只需要对值进行hash然后对照哈希表获取到对应的磁盘地址;
对范围查找或者排序不能提供很好的支撑;

4)B-Tree;

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/02442cecdb3b49c3b3cb675e5e430d4d~tplv-k3u1fbpfcp-zoom-1.image)
叶节点具有相同的深度,叶节点的指针为空;
所有索引元素不重复;
节点中的数据索引从左到右递增排序;
MySQL大节点容量空间推荐16KB;通过SHOW GLOBAL STATUS LIKE 'Innodb_page_size'语句查看;
在数据量不可控的情况下B-Tree算法就会出问题,占用空间越大,能够存储的索引也就越少,这样的情况与红黑树没有什么区别;

5)B+Tree

是B-Tree的变种,与B-Tree的区别在于:
1)非叶子节点(存在下级的节点)不存储Data,只存储索引(冗余的索引,子节点的第一个元素或者特定位置的索引),可以存放更多的索引;
2)叶子节点包含所有索引字段,即表的所有索引元素,也成为磁盘页;
3)叶子节点用指针链接,提高区间访问的性能;
4)叶子节点data中才最终存放数据的硬盘地址;

每个节点中的元素都是从左到右递增的,同子节点同级节点也是从左到右递增的,是排好序的;
次级节点大于或等于父节点;每个同级子节点的头尾相邻部分都具有一个双向指针——用于存储地址位置(注:同级第一个子节点与最后一个子节点也有一个双向指针),用于处理范围检索;
查找的时候:先把节点Load到内存(RAM)中,再通过算法(例如:二分算法)查找,将查找到的节点再次Load到内存中再次进行查找;注意这里耗时的就是Load过程,节点查找是在内存中通过高效算法进行的,可以忽略不计;
每个节点的长度可以在MySQL中使用:SHOW GLOBAL STATUS LIKE 'Innodb_page_size';语句进行查看,大概值为16KB,这个值可以修改,但是不建议进行修改,因为这个值是MySQL经过大量测试后得出的结论;当日是正常标准的情况下,非标准的另类除外,改之前请先确认自己不是吃瓜人员;
![](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/776b4cb804354f2081f7f3de71b497d6~tplv-k3u1fbpfcp-zoom-1.image)

BigInt类型的举例:

BigInt占用8个字节,即:8B;
地址指针为6个字节,即:6B;
那么16KB的节点最终容纳的容量为:16384/(8+6)=1,170.285714285714;即可以存储大约1170个Bigint类型的值;
假设现在节点高度为3,子节点单Data存储容量为1KB,那么最终这个索引的B+Tree的最终子节点能够容纳:1170*1170*16=21902400条数据;也就是说千万级别的数据,仅需要3次IO即可找到;
目前MySQL已经将所有非叶子节点的所有全部放到内存中,这样更加快速,最终将查找到的子节点Load到内存中进行输出;
由于非根节点存储的仅为指针,所以节点横向可以存储更多的指针
指针大概占用6个字节+bigint类型的主键占用8个字节=16个字节;
16*1024/16=1170;子集有多少个指针就是1170*N;

三、存储引擎

**存储引擎是形容数据表的**;索引结构存储在MySQL安装目录Data文件夹下;

1)MyISAM

frm:文件存储表结构定义文件
myd:对应数据行;
myi:存储索引字段;
B+Tree叶子节点Data部分存储了主键及文件地址指针;
引擎文件和数据文件是分离的——非聚集,不包含数据所在行的数据;

假设:col1为索引字段;
MYI:文件中存储的是索引——B+Tree格式;
MYD:文件中存储的是数据;
select t.* from t where col1=30;
现在MYI中查找,然后再MYD中把数据提取出来,最后根据MYD中的硬盘位置指针找到硬盘中的位置;

2)Innodb

![](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/2dab6828776d40e08bb773786910b8f3~tplv-k3u1fbpfcp-zoom-1.image)
frm:文件存储表结构定义文件;
ibd:对应数据行+索引字段;
表数据文件本身就是按B+Tree组织的一个索引结构文件,即**IBD文件存储的内容格式**;
使用的是聚集索引-叶节点包含了完整的数据记录,整行的完整数据;
与MyISAM不同的地方是B+Tree叶子节点Data中包含了主键+其他所有字段;
**什么是聚集索引?**
聚集类型的索引:**索引与表数据列聚集在一起放在一个地方存储,Innodb下默认的聚集索引就是主键索引,非聚集索引最后都要做一次回表操作,即先定位主键,然后回到聚集索引中根据主键去定位数据;**
表数据文件本身是按B+Tree组织的一个索引结构文件;
聚集索引-叶节点包含了完整的数据记录;
聚集索引不会跨文件,不受跨文件的性能影响;
**一句话:**
**包含了数据及索引在一个文件中的类型被成为聚集索引,反之非聚集索引;**
**为什么InnoDB表必须有主键,并且推荐使用整形的自增主键?**
没有主键不能使用B+Tree来进行组织;如果建表的时候不指定主键,MYSQL会在所有字段中去筛选,如果没有筛选到合适的,在后台会增加一个row_Id隐藏字段,且这个字段是Int类型;
整型利于在B+Tree查找时进行比较,占用空间小,自增有利于节点从左到右从小到大依次递增;
如果插入的数据不是顺序从小到大从右到左的时候,会导致节点的分裂以及整个树的平衡,效率非常低;
如果是用的UUID,那么会根据Assll去比较,而且是一个一个的去比较,效果会略显低下;
**为什么要自增?**
因为B+Tree是一个**排好序的数据结构**,数据存储之后就是排好序的,检索快;另外每个子节点都有双向链表指针,对范围查找支撑性好,效率高;

如果不是自增,那么在构建B+Tree的时候会出现跳帧的情况(一个大节点变成了2个节点,然后再进行平衡,获取每个页节点的第一个元素提取到上级),如果插入的不是自增型,那么会减少分裂均衡的过程,提高效率;
**为什么非主键索引结构叶子节点存储的是主键值?**
![](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/62609c3c3b8441b38bf753e837459c42~tplv-k3u1fbpfcp-zoom-1.image)

非主键索引:辅助索引、二级索引;

区别在于叶子节点的Data区域,Data存的内容包括索引内容+主键元素;查询时通过索引内容找到主键对应的主键B+Tree;
这样设计优势在于一致性和节省存储空间,便于索引的维护;

3)HASH索引

![](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/8491ec582fd14049b5fed783f9f5585d~tplv-k3u1fbpfcp-zoom-1.image)

是哈希表的一种体现;如果将某列索引类型设置为Hash,那么会现将此列进行一次Hash,将结果放入一个Hash桶(即:Hash数组);再加入值的时候会优先对数据进行Hash,然后找到对应的位置,插入数据;

总结:
  • 对索引的Key进行一次Hash计算就可以定位出数据存储的位置;
  • 很多时候Hash索引要比B+Tree索引更高效,但是要注意:Hash碰撞及冲突问题(一个Key下存储多个数据);
  • 仅支持“=”及“IN”,不支持范围查找,Hash结果是没法范围比较的;

4)联合索引

也被成为复合索引,多个字段共同组织成一个索引;表中不推荐创建多个单值索引
复合索引也是非聚集索引;会将所有字段根据创建时的先后顺序进行B+Tree的构建,也是最左前缀的体现,由左到右依次进行比较及排序;
**第一个或上一个字段相等,不会比较后续字段;**
**第一个或上一个字段不相等,会比较后续字段;**
在查询的时候也是按这个模式进行比较及对比的,如果没有最左侧原则,那么就会发生不知道如何比较的问题,因为没有了持续,将会无法定位;
节点中存储多个索引字段;在子节点Data中保存的是字段+主键Key;
逐个字段进行综合比较排序,