面试题：MySQL 的索引下推是什么？

1. 什么是索引下推？

索引下推（Index Condition Pushdown, ICP）是 MySQL 5.6 引入的一项优化技术，主要用于优化复合索引的查询性能。它的核心思想是将 WHERE 条件中索引列的部分过滤条件下推到存储引擎层，从而减少回表次数和减少 MySQL Server 层需要处理的数据量。

2. 索引下推的作用

在没有索引下推的情况下，存储引擎会先根据索引的最左前缀原则检索数据，然后将所有满足最左前缀条件的数据返回给 MySQL Server 层，再由 Server 层根据其他条件进行过滤。

而启用索引下推后，存储引擎可以在索引扫描阶段直接过滤掉不满足条件的记录，从而减少回表操作和 Server 层的处理负担。

3. 索引下推的适用场景

复合索引：当查询条件涉及复合索引的多个列时，ICP 可以将部分条件下推到存储引擎层。
范围查询：当查询条件中包含范围查询（如 >、<、BETWEEN）时，ICP 可以提前过滤掉不符合条件的记录。
减少回表次数：ICP 可以减少存储引擎回表（访问主键索引或数据行）的次数，从而提升查询性能。

4. 索引下推的工作流程

假设有一个复合索引 (A, B)，查询语句为：

SELECT * FROM table_name WHERE A = 1 AND B > 10;

没有 ICP 的情况：
1. 存储引擎根据索引 (A, B) 找到所有 A = 1 的记录。
2. 将这些记录返回给 MySQL Server 层。
3. Server 层再根据 B > 10 的条件进行过滤。
启用 ICP 的情况：
1. 存储引擎根据索引 (A, B) 找到所有 A = 1 的记录。
2. 在存储引擎层直接过滤掉 B <= 10 的记录。
3. 只将满足 A = 1 AND B > 10 的记录返回给 MySQL Server 层。

通过 ICP，存储引擎可以提前过滤掉不符合条件的记录，从而减少回表次数和 Server 层的处理量。

5. 如何判断是否使用了索引下推？

可以通过 EXPLAIN 命令查看查询的执行计划。如果 Extra 列中显示 Using index condition，则表示使用了索引下推。

EXPLAIN SELECT * FROM table_name WHERE A = 1 AND B > 10;

输出示例：

+----+-------------+------------+------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+------------+------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | table_name | ref  | idx_A_B       | idx_A_B | 10     | const| 100  | Using index condition |
+----+-------------+------------+------+---------------+------+---------+------+------+-----------------------+

6. 索引下推的限制

仅适用于 InnoDB 和 MyISAM 存储引擎。
仅适用于二级索引（非主键索引）。
查询条件必须涉及索引列，且部分条件可以被下推到存储引擎层。

7. 如何启用或禁用索引下推？

索引下推默认是启用的。如果需要禁用，可以通过设置系统变量 optimizer_switch 来控制：

-- 禁用索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

-- 启用索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

8. 索引下推的性能影响

优点：
- 减少回表次数，降低 I/O 开销。
- 减少 Server 层需要处理的数据量，提升查询性能。
缺点：
- 在某些场景下，ICP 可能会增加存储引擎层的 CPU 开销。

9. 总结

索引下推是 MySQL 中一项重要的查询优化技术，特别适用于复合索引和范围查询的场景。它通过将部分过滤条件下推到存储引擎层，减少了回表次数和 Server 层的处理负担，从而提升了查询性能。在实际使用中，可以通过 EXPLAIN 命令判断是否使用了索引下推，并根据需要调整优化器设置。

THE END

MySQL 面试题