MySQL索引：InnoDB 的索引机制

作者：@罗一
本文为作者原创，转载请注明出处：https://www.cnblogs.com/luoyicode/p/17576298.html

---

目录

• 索引的种类
• 索引数据结构
• 索引的优缺点
• 表空间和B+树索引空间
  • 非聚簇索引就是辅助索引
  • 聚簇索引就是主键
    • 不要使用隐藏的主键
• Index 和 Unique
  • 查效率对比
  • 增效率对比
  • 其他效率
• Innodb不支持的索引
• 联合索引
  • 优缺点
  • 最左前缀和最左匹配原则
  • 联合索引失效
  • 失效的原因：
• 回表与辅助索引
  • 索引下推（ICP）
    • MySQL分层架构
    • 配置索引下推
    • 流程：无索引下推
    • 流程：索引下推
    • 索引下推失效
  • 回表
  • 索引覆盖
• 自适应Hash索引
• ORDER BY
  • 1.部分走或失效
  • 2.完全走联合索引
• 索引失效问题
  • 普通索引失效
  • 唯一性索引失效
• 不建议加索引的情况

---

索引的种类

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

索引数据结构

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

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

索引的优缺点

优点：

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

表空间和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在插入的效率会差一些；

Innodb不支持的索引

不支持虚拟列索引和表达式索引，因为表达式和虚拟列需要计算，对于B+树这种结构来说，维护的计算代价过大

联合索引

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

优缺点

优点：

• 条件过滤的高效性
  可以用联合索引一次性过滤多个条件，避免多个二级索引树的查找开销
• 索引覆盖，联合索引实现索引覆盖可以不用回表
  缺点：
• 联合的列数过多，会导致维护开销、增删改的成本增大
• 如果查询条件是 ‘或’ 则不会走联合索引，而是部分走索引
• 索引失效问题

最左前缀和最左匹配原则

联合索引是有顺序的，默认是按联合的第一列进行排序，之后在相同第一列的情况下，才会对之后的列进行排序，也就是，联合索引按列从左到右的顺序分别进行排序。
最左匹配原则是说，根据where条件去匹配单列或联合索引过程中，如果是可以走联合索引，是从最左开始匹配选择最合适的索引(索引覆盖率最高的索引进行匹配)

联合索引失效

1. or
2. LIKE '%···'
3. where条件，跳过联合索引的最左前缀
4. 最左前缀列的 范围查询（不等于、大于、小于）
5. 最左前缀列的 IN 列表值过多 (通常小于10个)

失效的原因：

如果范围过多，就算走索引找到的记录也不是完整的记录，还是需要回表再查询，当范围过大，要访问索引之后再频繁回表，执行器会认为全表扫描的执行计划比走索引的效率更高

回表与辅助索引

索引下推（ICP）

索引下推（Index Condition Pushdown，ICP）是MySQL5.6的优化，改进了二级索引的扫描策略，可以减少服务层访问存储引擎的次数，以及减少存储引擎访问基表的次数。
ICP适用于 InnoDB 和 MyISAM ，可以减少回表次数

MySQL分层架构

• 服务层，也叫Server层，是用来解析SQL的语法语义，以及生成查询计划、接管从存储引擎层上推的数据进行二次过滤的分层
• 存储引擎层是按照Server层的请求，通过索引或者全表扫描的方式获取并返回数据的分层

配置索引下推

-- 查看索引下推是否开启
select @@optimizer_switch
-- 开启索引下推
set optimizer_switch="index_condition_pushdown=on"
-- 关闭索引下推
set optimizer_switch="index_condition_pushdown=off"

流程：无索引下推

无索引下推时，优化器不会将and条件连接成联合索引，比如现在有索引：
idx_age_name(age, name)，查询条件是 age=18 and name="罗一"
此时执行器会根据最左前缀匹配原则，匹配到age字段，但是不会将name字段联合，导致在age_name索引树上匹配到合适的age，会忽略掉name字段，导致回表匹配name字段，最后返回的结果在服务层按照name="罗一"进行过滤
这会导致明明当前索引树有name字段，却还要回表查询name值，最后还在服务层进行条件过滤

流程：索引下推

会合并and条件，判断是否可以走联合索引，防止回表

索引下推失效

1. 引用子查询条件，子查询不是存储引擎层执行的内容；
2. 引用表达式条件，比如：idx(col1+col2)，因为表达式和函数，也不是存储引擎层Innodb所可以维护的索引，所以会导致服务层执行器制定执行计划时，会放弃对应字段的索引；
3. 虚拟列上的索引，比如函数索引 idx(dosomething(col))

回表

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

索引覆盖

当然，通过回表的定义可知，其实如果定义的辅助索引或者联合索引包含了所有需求的字段，那么就不需要回表，这种情况就是索引覆盖

自适应Hash索引

用户无法手动创建hash索引；
如果一个加了Hash索引的字段的某一项被查询若干次后，那么这条Hash索引的记录就可以被加载到缓存中的散列表，当再次访问这个条件时，就会从缓存中直接返回索引对应的查询到的主键

ORDER BY

Order By是mysql中的排序方法，是对存储引擎返回的结果集进行排序的方法
在使用Order By时，如果排序的顺序跟联合索引顺序不一致，就会导致只能部分走索引
现在假设索引是：idx_age_name(age, name)：

1.部分走或失效

1. 非最左匹配：
   如果排序条件是：ORDER BY name, age ，此时只能部分走索引，走的是idx_age_name索引中age字段的部分索引；
2. DESC序：
   如果顺序是DESC序，会导致对应列的索引失效

2.完全走联合索引

如果排序条件是：ORDER BY age, name ，此时可以完全走索引。

索引失效问题

普通索引失效

1. 大范围查询 或 过多值的 IN 条件
   • 范围查询返回结果占总行数的比例在 20% 左右可以认为还能走索引
2. LIKE % 左模糊查询
3. 无最左前缀条件的联合索引
4. 排序操作 走部分索引和完全失效（desc序、非最左匹配）
5. 子查询结果列作为 IN 列表
   具体走不走索引可以通过 explain 查看执行计划判断

唯一性索引失效

• IN 条件中有重复值，违反唯一性索引
• 跟普通索引一样的失效条件

不建议加索引的情况

1. 查询条件中，很少使用的字段可以不加索引
2. 如果当前字段是枚举值、或者值的类型很少，并且分布很均匀，那么没必要加索引；但是如果某种值很少，那么也可以加索引
   • 举个例子，假如某个字段只有1、2、3
     如果1、2、3分布很均匀，那么可以不加索引；
     如果某个字段1几乎没有，但是2、3很常见，那么建议加索引

---

如果您觉得阅读本文对您有帮助，请点一下“推荐”按钮，您的“推荐”将是我最大的写作动力！欢迎各位转载，但是未经作者本人同意，转载文章之后必须在文章页面明显位置给出作者和原文连接，否则保留追究法律责任的权利。