MySQL底层专题之索引数据结构和存储引擎

目录

一、索引

索引的本质：

索引的数据结构：

二叉树：

红黑树 ：

Hash表：

特点：

B-Tree：

B+Tree(B-Tree变种):

对比一下B和B+树：

为什么数据库底层选择B+而不是B-Tree：

为什么数据库底层选择B+而不是Hash：

 二、存储引擎

本文分为innoDB和MyISAM

1、在MyISAM下的B+索引：

2、在InnoDB下的B+索引：

 面试题来了：

三、聚集索引

四、联合索引/复合索引

一、索引

索引的本质：

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

数据表的每一行虽然是挨在一起的，但在实际上在磁盘上是随机分布的

索引的数据结构：

• 二叉树
• 红黑树
• Hash表
• B-Tree

二叉树：

如果我们查询像Col1这样的有序排列，二叉树没有起到任何优化的效果，无效率提升，如下图：

红黑树 ：

红黑树是一种​​自平衡的二叉搜索树，在普通二叉树的基础上通过额外的规则和操作来维持平衡

红黑树通过以下规则维持平衡：

1. ​​节点非红即黑​​
2. ​​根节点必须为黑​​
3. ​​红色节点的子节点必须为黑​​（即不能有连续红节点）
4. ​​从任一节点到其叶子的所有路径包含相同数量的黑节点​​（黑高相同）
5. ​​叶子节点（NIL节点）视为黑色​

在数据量大的时候，树的高度会很高，比如10亿数据时：log₂(10^9) ≈ 30层，每次查询需要30次节点访问

Hash表：

来看个例子：

当我们把下图这一列数据做一个hash索引的时候，

当我们存储索引的时候，他会对我们的索引做一次hash运算得到结果之后存到hash桶里去，类似于一种hash数组，当有一个新的数据算出来和前者一样，虽然两者的键不同（"Alice" vs "Jim"），但哈希函数计算后得到​​相同的桶索引，采用​​链地址法解决哈希冲突​，查询的时候会对链表采取遍历找到jim

链表的一个节点除了存索引元素之外还会存索引所在行的物理磁盘地址，下图

hash索引都在磁盘上

​

特点：

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

B-Tree：

B+Tree(B-Tree变种):

• mysql给每一行设置最大内存16kb
• 对于专门的内存数据库，整个B+树结构（包括所有父节点，也就是图中第一行）会常驻内存占，一个索引也就是key的大小（比如bigint占8个字节（8B）），但是不要经常占用内存
• 每一行索引的下一个是磁盘文件的地址，也就是白色的地方，分配6个字节
• 也就是16kb/（8+6）B=1170个，每一个节点都是16kb，因此一条线的三层就是1170*1170*16kb约为2k万，但是高度h=3

对比一下B和B+树：

B树：

B+树：

这时候就要抛出问题了：

为什么数据库底层选择B+而不是B-Tree：

一个完整的这个大部分也就1KB，也就是每个节点最多放16个条目

对于树结构来说，影响索引速率的是树的高度，而B+树存放2000w+也才3层，B树16的n次方=2000w+才能计算得到n层，效率显而易见

总结一下就是B+树节点存储1170个索引，但是B树只存储16个

为什么数据库底层选择B+而不是Hash：

Hash表仅能满足 “=”，“IN”，不支持范围查询

而我们的B+树是支持的（InnoDB的B+树叶子节点是双向链表（不仅支持顺序扫描，还支持逆序扫描）），有指针连接，同时B树也不支持！

 二、存储引擎

本文分为innoDB和MyISAM

每种存储引擎可以选择实现不同的索引结构

没有掌握存储引擎基本知识的请跳转：MySQL之储存引擎和视图-CSDN博客

1、在MyISAM下的B+索引：

先来看个表

.frm：框架

.MYD：MyISAM Data 存表数据

.MYI：MyISAM Idex 存索引

在MYI文件会把这些数据组织好索引，当从B+树寻找数据到最后一层，最后的data存的是真正的行数据所在的磁盘MYD的物理地址，拿到这个地址就可以去MYD文件里去定位具体数据

2、在InnoDB下的B+索引：

再来看InnoDB的表：

 .frm：框架

.ibd：存索引和表数据（包括二级索引）

对于同样的数据这是InnoDB的存储方式：

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引(一个表有且只有一个)：叶节点包含了完整的数据记录
• ​​InnoDB的B+树在运行时，非叶子节点（索引节点）会被动态加载到内存中

 面试题来了：

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

1.如果不建主键，InnoDB会选择一列互不相等的数据来组织B+树，如果选不到，会建立一个隐藏列，他会帮你维护一个唯一的id。

2.当我们从根节点查找元素的时候，中间经历过很多次数据比大小，整形比大小很快（UUID字符串会逐个字符比较ASCII码非常慢）

3.顺序写入​​：自增ID保证新数据总是插入到B+树的最后位置，不会一直往前面插队，减少页分裂和随机I/O。​​减少碎片​​：连续的主键值使数据物理存储更紧凑。

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

当我们不用主键索引的时候，就是二级索引即非聚集索引，比如按照姓名来索引，仍然会组织一个B+树，顺序按字符串来排序，但是仔细观察发现叶子节点上有聚集索引的主键（如果没有主键就是隐性创建一个聚集索引）

上面我们提到了一个新的概念：

三、聚集索引

联想到上面的MyISAM的MYI文件和MYD文件，索引和数据是分开的，正是这样，所以索引和数据放一起的是聚集索引

四、联合索引/复合索引

 多个字段共同成为一个索引

 按照索引左列原理排序，按照字段的先后顺序排序，前者字段相等就比后者，这个也是二级索引

为什么sql查询语句也要按照顺序，因为如果跳过了前者的字段，只比较后面的其实是乱的