Skip to content

0002.md

Latest commit

 

History

History
223 lines (136 loc) · 4.91 KB

0002.md

File metadata and controls

223 lines (136 loc) · 4.91 KB

面试题：了解索引下推吗？什么情况下会下推到引擎去处理？什么情况下索引会失效？

ICP 是 MySQL 5.6 引入的一项核心优化技术，用于减少回表，提高范围扫描效率。

问题核心：

  1. 什么是索引下推（ICP）？
  2. 什么时候 MySQL 会把过滤条件下推给存储引擎？
  3. 哪些情况下无法下推、导致索引失效？
  4. 实际工作中 ICP 能带来什么性能收益？

下面按大厂风格逐条拆解。

一、什么是索引下推（Index Condition Pushdown，ICP）？

一句话解释：

在使用二级索引扫描时，把能用索引过滤的条件尽量在存储引擎层用索引叶子节点做过滤，减少回表次数。

普通流程（无 ICP）：

二级索引扫描 → 拿到主键 → 回表查询整行 → MySQL Server 层做 WHERE 过滤

ICP 后：

二级索引扫描 → 在索引叶子节点上先做一部分 WHERE 条件过滤 → 回表更少

减少回表次数，就是 ICP 的价值。

典型意义： InnoDB 的回表是随机 IO，成本非常高（尤其 SSD 前时代）。ICP 能把回表从几万次减少到几百次，性能提升巨大。

二、什么情况下会触发索引下推？

ICP 的触发条件非常明确：

1）必须是二级索引（二级 B+Tree）扫描

因为主键索引本身就是聚簇索引，不存在“回表”的问题，ICP 没意义。

如下 SQL 会触发 ICP：

SELECT * FROM user WHERE age > 20 AND name LIKE 'A%';

若建了联合索引 (age, name)，且扫描走的是二级索引，则：

  • age > 20 是索引扫描条件
  • name LIKE 'A%' 可以在索引层过滤
  • 其他字段需要回表

MySQL 会将 name LIKE 'A%' 下推给 InnoDB B+Tree 层做过滤。

2）WHERE 条件除了前导列以外还有可过滤的字段

例如：

WHERE a = 10 AND b > 5

有联合索引 (a, b) 时：

  • a=10 是范围内匹配
  • b>5 可下推

视图如下流程：

索引层扫描 a=10 的范围  
→ 在索引层根据 b>5 做过滤  
→ 只对剩下的行回表

3）被下推的条件必须能在索引叶子节点上取到值

二级索引叶子节点存储：

  • 索引列（联合索引里的所有列）
  • 主键列

因此只有以下条件能下推：

  • 索引本身的列
  • 主键列

不能下推的：非索引列（因为索引叶子节点取不到）

三、什么情况下不会索引下推？（即 ICP 失效 / 索引失效）

ICP 不是什么条件都能下推，下面是不会下推的几类情况。

1）条件涉及非索引列时（叶子节点取不到值）

例如：

SELECT * FROM user WHERE age > 20 AND city = 'BJ';

索引只有：

INDEX (age)

city 字段不在索引中，B+Tree 叶子节点没有 city 值，索引无法帮忙过滤，必须回表。

→ city 条件不会下推。

2）出现函数、表达式导致索引失效

例如：

WHERE LEFT(name, 2) = 'Ab'

此时 name 列无法直接用于索引扫描，也无法在索引层过滤。

3）LIKE 以 '%' 开头

例如：

WHERE name LIKE '%abc'

这种情况下索引层无法判断范围，不会触发 ICP。

4）隐式类型转换

例如：

WHERE phone = 13800001111

phone 字段是 varchar，但传了数字，发生隐式转换 → 索引失效 → 自然不会下推。

5）联合索引未按最左前缀顺序使用

索引：(a, b)

条件：

WHERE b = 3

由于跳过 a，无法走索引扫描，也无法执行 ICP。

6）主键扫描不需要 ICP

主键索引 = 聚簇索引，不存在“回表”。

四、索引下推带来的真实收益（面试加分）

在高并发电商/金融系统中，二级索引扫描是常态。 回表通常是：

  • 随机 IO
  • 一次回表 cost 常常是几十倍

ICP 的实际好处：

  • 大幅减少需要回表的行
  • 尤其是 like、range、between 的查询
  • 在 SSD 时代仍然有效（减少 page access）

典型案例：从 100000 条记录，仅回表 300 条。

五、最终面试总结

你可以这样回答：

“索引下推是 MySQL 5.6 引入的一项优化。 它的作用是把一部分 WHERE 条件提前在存储引擎层做过滤，从二级索引的叶子节点上先过滤掉不满足条件的记录，从而减少回表次数。

ICP 会在两个条件下触发： 1）走的是二级索引扫描； 2）WHERE 条件中有部分字段能在索引叶子节点上直接获取，比如联合索引中的后续列。

但以下情况会导致索引下推无法执行： 1）条件涉及非索引列； 2）函数、表达式、隐式转换导致索引失效； 3）LIKE 前面有 '%'； 4）联合索引不按最左前缀使用。

索引下推的核心价值是减少回表次数，提高范围查询性能，是 InnoDB 重要的性能优化机制。”
⚡