MySQL索引：InnoDB 的索引机制

coding now / 2023-07-24 / 原文

索引的种类
索引数据结构
索引的优缺点
表空间和B+树索引空间
- 非聚簇索引就是辅助索引
- 聚簇索引就是主键
  - 不要使用隐藏的主键
Index 和 Unique
- 查效率对比
- 增效率对比
- 其他效率
联合索引
- 优缺点
- 最左前缀
- 联合索引失效
回表与辅助索引
- 索引下推
- 回表
- 索引覆盖
自适应Hash索引
索引失效问题
索引效率

索引的种类

数据结构：B+树、Hash、Full-text
物理存储：聚簇索引、非聚簇索引
字段特性：Key、Unique、Index、Fulltext
字段个数：单列索引、联合索引

索引数据结构

索引就是搜索，将同一类事物 / 字段进行数据结构排列，方便查找
比如：B树的二分查找、hash的桶位路由查找等等
索引数据结构有：BST、AVLT、红黑树、B树、B+树

是否二分 BST
是否平衡
- 时刻平衡 AVL
- 惰性平衡红黑树

索引的优缺点

优点：

条件下推到索引，可以提高数据检索效率
唯一性索引可以保证字段不重复
加快数据的分组和排序
缺点：
存索引，需要占硬盘空间
创建索引开销
增删改操作，需要维护索引，维护开销
数据量越多，维护开销越大
普通索引/辅助索引（index类型索引）会导致频繁回表
左模糊查询，或者一些条件查询，会导致索引失效

表空间和B+树索引空间

在innodb中的表数据文件是.idb文件，用来描述表空间
表空间是由多个B+树索引空间组成
每建立一个索引就会在表空间中建立一个辅助索引，
每个辅助索引都会对当前字段在表空间中建立一个对应列的B+树索引数据结构，

非聚簇索引就是辅助索引

非聚簇索引就是辅助索引，它不包含完整的表字段，只包含索引字段和主键
辅助索引又叫二级索引，分为普通索引 Index 和唯一索引 Unique

聚簇索引就是主键

在innodb中，主键索引和所有行字段，都是放在一起的
而这种索引数据结构是一种索引和行数据混合的存储模式
这个就是innodb的模式

不要使用隐藏的主键

在innodb引擎创建的表中，如果不设计主键列，还是会有主键
而且是自动递增主键，只不过这个主键是系统自动分配的，叫做：row_id
但是这个主键不会提高检索效率
而且因为是自动递增，导致删除之后会留下外存碎片，降低外存空间使用率

Index 和 Unique

辅助索引分为普通索引 Index 和唯一索引 unique，他们一个是按照平衡二分排序，一个是在平衡二分的基础上保证唯一不重复
它们除了在唯一性上不一样，在性能上也有差异：

查效率对比

查询首先是层序遍历B+树的树节点，路由到叶子节点，磁盘读取对应页到内存，之后维护内存指针读取记录，之后：

Index在读内存页的字段记录中，如果读到了目标记录，还会继续移动指针读页内下一个记录；
如果恰好指针移动到了当页的最后一条记录，则会磁盘IO加载下一页；
这个过程直到读到第一个值不一样的记录，才会停止
Unique在读内存页的字段记录中，如果读到目标记录，就直接返回一个主键id，结束读取，不会读下一个记录
所以在查效率上，唯一索引Unique效率更高

增效率对比

增加也是先层序遍历到叶子节点，之后读取一页到内存，之后遍历页内记录：
第二步，看当前内存中是否有对应页，如果缓存了对应页：

内存中有缓存页：
- Index：会找到合适的位置插入数据；
- Unique：会找到合适的位置，然后判断是否有冲突，如果无冲突再插入；
内存无缓存的目标页：e
- Index：将待插入的数据放入 change buffer
- Unique：将数据页加载到内存作为缓存页，然后按照有缓存页的方式进行插入；

其他效率

其他操作上都是差不多的，都是修改B+树结构的模式
综上，Unique在查询上的效率高于Index，而且唯一性也意味着唯一次数的回表；
但是Unique在插入的效率会差一些；

联合索引

联合索引又称组合索引、复合索引

优缺点

最左前缀

联合索引失效

回表与辅助索引

索引下推

将条件子句尽可能的走对应字段的索引

回表

对应字段的索引找到了记录，但是因为辅助索引只能查到对应字段
所以如果待查询的字段存在一个或以上，不是辅助索引对应的字段，那么需要根据查到的主键进行回表
所谓回表就是回到聚簇索引（主键索引）中查询回表的主键对应的其他需要的字段
当然MyISAM、Memory之类的存储引擎必须要回表，因为他们的表数据跟索引不聚簇；