MySQL 索引底层数据结构

mrathena

已于 2024-08-19 10:15:39 修改

阅读量1.1k

点赞数 1

分类专栏：中间件文章标签： mysql

于 2021-07-07 16:46:49 首次发布

本文链接：https://blog.csdn.net/mrathena/article/details/118551728

版权

中间件专栏收录该内容

57 篇文章 2 订阅

订阅专栏

博文目录

文章目录

MySQL的数据存储

# 索引的数据结构

数据结构可视化演示(Data Structure Visualizations)
MySQL索引原理及慢查询优化

存储在硬盘上的文件, 很多时候都是不连续的, 尤其是数据库文件, 经过长期的不定期扩展与变更, 碎片化会非常严重, 导致像全表扫描这样连续访问数据可能会做大量的硬盘IO. 而硬盘IO的效率相对来说是比较低的, 虽然固态硬盘的IO效率相比机械硬盘有的量级的提升, 但是和CPU/内存IO相比, 硬盘IO还是差了几个量级, 大量的硬盘IO会严重影响程序性能

要想高效地找到某条记录, 就必须限制查找数据过程中硬盘IO的次数, 比如说4次IO内找到指定记录. 索引应用而生

索引 是能够帮助数据库高效查找数据的 有序 的数据结构

不同数据结构查找数据的效率不同. 常见的时间复杂度效率如下图
在这里插入图片描述

常见的数据结构中, 哈希和树的查找效率分别是 O(1) 和 O(logn), 是效率最快的两个, MySQL 也毫不意外的使用了这两种数据结构来做索引

MySQL索引的数据结构有两种选择, B+Tree 和 Hash

为什么不用二叉树做索引

二叉树, 右边的值大于根节点, 左边的值小于根节点

单边增长的数据列, 如id列, 生成的二叉树的搜索效率是O(n), 太慢了, 和链表没有区别了, 和全表扫描一样, 且高度不可控
在这里插入图片描述

为什么不用红黑树做索引

红黑树, 二叉平衡树, 一种自平衡二叉查找树, 在进行插入和删除操作时通过特定操作保持二叉查找树的平衡，从而获得较高的查找性能, 其效率是O(logn), 已经很快了
在这里插入图片描述
但是红黑树的高度不可控, 当表中记录行数很大时, 红黑树的高度可能会很高, 查找数据时可能需要很多次硬盘IO, 不太理想

那么, 如果能控制树的高度, 保证硬盘IO的次数只有几次, 那就满足条件了. 要想缩减高度, 那就得增加宽度了, 把每个节点里保存的数据量从1个变成多个

影响树结构查询效率的就是树的高度, 每一层需要一次硬盘IO

B树

B树, 多叉树, 平衡多路查找树.

叶节点具有相同的深度, 叶节点的指针为空
节点内或节点间的数据从左到右递增有序
子节点中的所有key都大于等于父节点空白格子左边的key, 都小于等于父节点空白格子右边的key
叶子结点和非叶子节点的所有数据构成完整数据(数据会存在非叶子节点中)

上图表示使用B树作为索引文件结构时的情况, data 表示每一行数据在硬盘中的地址, 空白格子存储子节点的硬盘地址

B+树

非叶子节点不存储数据, 只存储索引(冗余), 这样每个节点可以放更多的索引
叶子节点包含所有的索引字段
叶子节点间用指针连接, 提高区间访问的性能
叶子节点的数据构成完整数据, 非叶子节点都是冗余数据, 即非叶子节点的key在叶子结点中都有

B树和B+树的区别

B+树的非叶子节点不再持有数据, 全部都交给叶子节点来存储, 即所用的索引数据都在叶子结点上, 这样有利于控制树的高度
B+树相邻叶子结点之间有双向指针连接
B+树每次查找数据都得走整个树的高度, 而B树因为非叶子结点也持有数据, 所以当找到对应节点后拿到数据就会返回
B+数范围查找方便, B树不方便(叶子节点没有指针且数据不都在叶子节点上, 查的过程得从多层数据中找)

B+树的创建

B+树的构建是从下往上的, 当叶子结点满足分裂的条件时(叶子节点占用空间超过阈值), 将一个叶子节点分裂成两个叶子节点, 将第二个叶子节点的首元素提取到上一层节点中

每个节点中大致是这个样子: key - 下层key节点的地址 - key2 - 下层key2节点的地址 - key3 - 下层key3节点的地址

B+树的查找方式

从根节点找起, 先把根节点读取到内存中, 做判断找到下层节点的地址, 再把下层节点读取到内存中, …, 找到最终的叶子结点, 拿到对应的数据

整个查找过程中, 把一个树节点读取到内存中是一次硬盘IO, 耗时间的也基本上就是这几次硬盘IO的耗时, 内存IO基本忽略. 注意, 新版MySQL可能会把所有非叶子节点加载到内存中, 这样会节省几次硬盘IO, 就会更快

B+树做范围查找时, 因为是有序的, 所以先找到范围边界的叶子节点, 然后根据叶子节点间的双向指针遍历数据, 如>20, 找到20, 然后遍历到结尾, 如>20且<50, 找到20, 然后遍历到50

因为硬盘IO越多效率越低, 所以如果范围查找的数据很多时, 查到靠后的页时可能会因为硬盘IO次数多导致性能低, 这里分页并不能解决硬盘IO次数多的问题, 需要想办法把主键ID加到检索条件, 才能有效减少分页

MySQL的页大小与B+树节点的大小

-- 索引page页大小(默认16k,16384byte,不推荐改)
show global status like 'Innodb_page_size';

B+树创建时, 会为每个节点申请一块连续的硬盘空间, 其大小就是这个值, 假设使用bigint做主键, mysql中1个bigint占8个byte, 后面的空白格子存放子节点硬盘地址, 在c语言底层占6个byte, 即一个key和一个子节点地址占14byte

一个非叶子节点共16384个byte, 大约能放1170个索引项

一个叶子节点中存储的数据项个数依索引类型而定

聚簇索引的话, data是完整的一行数据(正常一行数据不超过1kb), 一个节点可以放16个数据项左右
稀疏索引的话, data是数据的硬盘地址, 6byte, 加上bigint的8byte, 所以还是约1170个数据项

如果是聚簇索引, 3层高度, 存满的话, 可以放 1170×1170×16=两千万条数据, 超过的话可以再加一层

为什么不用B树而用B+树做索引

MySQL的节点大小为16k, B树的每一个节点都会携带一行完整的数据记录, 即一个节点约存放16个数据项. 而B+树一个非叶子节点可以存1170个数据项. 因为影响树结构查询效率的就是树的高度, 当存储相同数量的数据项且数据项个数比较大时, B树的高度一定远大于B+树, 查找时的硬盘IO次数就会多, 查询效率就会差

B+树使用叶子节点存储数据, 当做范围查询时, 因为节点内和节点间数据有序且叶子节点间存在双向指针, 所以做范围查询时会很方便

Hash索引

类似于 Java 的 HashMap, 哈希表, 数组链表

对索引的key进行一次hash计算就可以定位出数据存储的位置
很多时候, Hash索引要比B+树索引更高效
Hash索引仅支持 = in 等等值查询, 不支持范围查询, 如 > < between and
hash冲突问题

MySQL的数据存储

根目录 - data - 数据库名 - 表文件

不同的数据库存储引擎, 表文件的存储格式不一样, 注意, 存储引擎是作用在表上的, 同一个库里不同的表可以选不同的存储引擎

假如表 test 选用了 myisam 存储引擎, 则文件有 test.frm(表结构), test.MYI(索引), test.MYD(数据) 共3个
假如表 test 选用了 innodb 存储引擎, 则文件有 test.frm(表结构), test.ibd(索引和数据)

MyISAM 存储引擎的索引和数据是分开存储的, 其查询流程(假设走索引)是: 根据查询条件到myi文件中经过多次硬盘IO找到数据行对应的硬盘地址, 然后到myd文件中对应地址去读取数据

InnoDB 存储引擎的表数据文件ibd本身就是按照B+树组织的一个索引结构文件, 即索引的叶子节点直接携带整行数据而不是数据行的硬盘地址. 其查询流程也是经过多次硬盘IO找到叶子节点, 直接拿到叶子节点下的整行数据

多个索引也是放到同一个索引文件中的

聚簇索引(聚集索引)和非聚簇索引

聚簇索引: 索引的叶子节点直接持有整行数据
非聚簇索引: 索引和数据分开存储, 索引节点持有数据行的硬盘地址

两者查找元素时, 聚簇索引比非聚簇索引会少一次硬盘IO, 效率更高

InnoDB 存储引擎的主键索引会作为一个聚簇索引来组织数据, 一张表只有一个聚集索引

主键索引和非主键索引(二级索引)

对于 MyISAM 来说, 主键索引和非主键索引都是一样的, 都是非聚簇索引, 都是叶子节点持有数据行的硬盘地址

对于 InnoDB 来说, 主键索引通常都是聚簇索引, 叶子节点持有完整数据, 非主键索引的叶子节点则持有聚簇索引的key(通常为主键索引的key, 就是主键的值, 比如id字段的值), 通过非主键索引查找的时, 先找到对应的主键key, 然后那到主键key到主键索引中查找(从上到下再查一遍), 最后拿到完整的数据, 这种操作叫做回表
在这里插入图片描述

非主键索引为什么要持有聚簇索引的主键值?

减少了出现行移动或者数据页分裂时二级索引的维护工作（当数据需要更新的时候，二级索引不需要修改，只需要修改聚簇索引，一个表只能有一个聚簇索引，其他的都是二级索引，这样只需要修改聚簇索引就可以了，不需要重新构建二级索引）

聚簇索引也称为主键索引，其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引。因为索引（目录）只能按照一种方法进行排序(因为聚集索引上挂载了表的数据, 数据只能存一份, 不能存多份)。

非聚簇索引（普通索引）的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）

为什么InnoDB必须建主键? 且推荐使用整形的自增主键?

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

如果设置了主键，那么InnoDB会选择主键作为聚集索引(主键是唯一的)、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引，如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。
如果使用整形
整形比较大小很方便, 字符串比较大小需要按位逐个比较, 即使相差不大, 但也是有差距的. 另外整形占用4/8个byte比字符串占用空间小, 能节约存储空间
如果表使用自增主键
那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页. 而如果没有自增, 那就存在不是追加而是中间插入的情况, 这样如果要插入的那一页满了, 再插入时势必会导致这一页做分裂上提(甚至重新平衡)操作, 对性能有一定影响
如果使用非自增主键（如果身份证号或学号等）
由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。