慢查询日志里那条 1.8 秒的 SQL
7 月底，DBA 每周发的慢查询报表里，我们订单库有条 SQL 排第一：执行 14.7 万次，平均 1.83 秒，扫描行数 28 万。SQL 长这样：
SELECT order_no, user_id, amount, status, create_time
FROM orders
WHERE user_id = 10086
AND status IN (2, 3, 4)
AND create_time >= '2019-05-01'
AND amount > 1000;
表上有 1200 万行，索引情况是：
mysql> SHOW INDEX FROM orders;
+--------+------------+------------------+--------------+-------------+-----------+
| Key_name | Seq_in_index | Column_name | Cardinality | Index_type |
+--------+------------+------------------+--------------+-------------+-----------+
| PRIMARY | 1 | id | 11834219 | BTREE |
| idx_user | 1 | user_id | 842109 | BTREE |
| idx_user | 2 | create_time | 9312447 | BTREE |
+--------+------------+------------------+--------------+-------------+-----------+
我第一反应是"有 idx_user 联合索引，走最左前缀，user_id 等值匹配加 create_time 范围，很标准啊"。看执行计划：
mysql> EXPLAIN SELECT order_no, user_id, amount, status, create_time
-> FROM orders
-> WHERE user_id = 10086
-> AND status IN (2, 3, 4)
-> AND create_time >= '2019-05-01'
-> AND amount > 1000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: range
possible_keys: idx_user
key: idx_user
key_len: 13
ref: NULL
rows: 2841
filtered: 1.00
Extra: Using index condition
rows 只有 2841，但 filtered 是 1.00，也就是 1%。乘以 2841 大概还剩 28 行需要返回。Extra 里是 Using index condition，看着挺正常。那 1.8 秒花在哪了？
回表才是大头
我打开了 profiling 看时间都去哪了：
mysql> SET profiling = 1;
mysql> SELECT ... ;
mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000081 |
| checking permissions | 0.000007 |
| Opening tables | 0.000023 |
| init | 0.000041 |
| System lock | 0.000012 |
| optimizing | 0.000018 |
| statistics | 0.000214 |
| preparing | 0.000026 |
| executing | 0.000004 |
| Sending data | 1.792146 |
| end | 0.000019 |
| query end | 0.000011 |
| closing tables | 0.000013 |
| freeing items | 0.000061 |
| cleaning up | 0.000023 |
+----------------------+----------+
1.79 秒全在 Sending data。这个状态名的意思是"正在读取并处理数据行"，不是真的在发送数据。这里就是回表的时间。
回表是怎么回事：InnoDB 的二级索引叶子节点存的是索引列 + 主键值，不是整行数据。通过 idx_user 找到满足 user_id 和 create_time 条件的记录后，拿到主键 id，再拿着 id 去聚簇索引（主键树）里把完整行读出来，才能拿到 order_no、amount、status 这几个不在索引里的字段。
也就是说，索引扫描出 2841 条，就要做 2841 次回表。每次回表是一次 B+ 树的查找，如果对应的主键页不在 Buffer Pool 里，就是一次随机磁盘 IO。我们的 Buffer Pool 是 8 GB，表加索引总共 6.2 GB，命中率看着有 98.4%，但剩下那 1.6% 落在磁盘上，平均一次随机读 8 毫秒，2841 × 1.6% × 8ms ≈ 364 毫秒。加上 MVCC 版本判断、行格式解析、加锁的开销，1.8 秒说得通。
ICP：把过滤条件下推到存储引擎
Using index condition 这个提示就是 Index Condition Pushdown，MySQL 5.6 引入的。它的作用是：status 和 amount 这两个条件虽然不在 idx_user 里，但 MySQL 会先把它们交给 InnoDB，让 InnoDB 在回表之前就判断一遍。
等等，status 和 amount 都不在 idx_user（user_id, create_time）里，InnoDB 怎么判断？答案是：ICP 只对索引中实际包含的列生效，这里索引列只有 user_id 和 create_time，都被 WHERE 用上了，所以 ICP 其实没帮上忙。
我把 ICP 关掉对比一下，验证它到底有没有起作用：
mysql> SET optimizer_switch = 'index_condition_pushdown=off';
mysql> EXPLAIN SELECT ... \G
Extra: Using where
mysql> SELECT ... ; -- 实际执行
2841 rows in set (1.81 sec)
1.81 秒，跟开着的 1.83 秒几乎一样，Extra 从 Using index condition 变成 Using where。确认了：ICP 在这个查询上没起作用，因为能下推的条件已经全部用于索引定位了。
那 ICP 什么时候有用？举个例子。假设索引是 (user_id, create_time, status)，查询条件不变：
mysql> ALTER TABLE orders ADD INDEX idx_user_status (user_id, create_time, status);
mysql> EXPLAIN SELECT ... \G
key: idx_user_status
key_len: 15
rows: 2841
filtered: 10.00
Extra: Using index condition; Using where
这时 create_time 是范围查询，根据最左前缀原则，create_time 之后的 status 无法用于索引定位（范围列后面的索引列失效）。没有 ICP 的话，MySQL 会拿 2841 条索引记录全部回表，取到完整行后再在 Server 层判断 status IN (2,3,4)。有 ICP 的话，InnoDB 在索引层就能读到 status 的值，先过滤掉不满足的，再回表。
| 索引 | ICP | 回表行数 | 耗时 |
|---|---|---|---|
| (user_id, create_time) | off | 2841 | 1.81 s |
| (user_id, create_time) | on | 2841 | 1.83 s |
| (user_id, create_time, status) | off | 2841 | 1.76 s |
| (user_id, create_time, status) | on | 287 | 0.19 s |
回表行数从 2841 降到 287，耗时从 1.76 秒降到 0.19 秒，快了 9.3 倍。这次是真的起作用了。
覆盖索引：干脆不回表
0.19 秒还是不够快，这条 SQL 一天跑 14.7 万次。再想想：查询要的字段是 order_no, user_id, amount, status, create_time，如果把它们全放进索引，就不用回表了。
mysql> ALTER TABLE orders
-> ADD INDEX idx_cover (user_id, create_time, status, amount, order_no);
mysql> EXPLAIN SELECT order_no, user_id, amount, status, create_time
-> FROM orders WHERE user_id = 10086
-> AND status IN (2,3,4)
-> AND create_time >= '2019-05-01'
-> AND amount > 1000\G
key: idx_cover
key_len: 13
rows: 2841
filtered: 11.11
Extra: Using where; Using index
Extra 里出现了 Using index，这就是覆盖索引的标志。注意它和 Using index condition 的区别：Using index 是"索引覆盖了所有需要的列，不用回表"；Using index condition 是"用了ICP，但仍然可能回表"。名字很像，含义完全不同，我一开始就是被这两个搞混的。
mysql> SELECT ... ;
2841 rows in set (0.008 sec)
1.83 秒降到 8 毫秒，229 倍。这个数字我反复确认了几次，因为差距大到不太真实。原因是彻底消除了 2841 次随机回表，全部变成索引上的顺序扫描，而且索引页基本都在 Buffer Pool 里。
代价也得说清楚：
- 索引变大了。
idx_cover五个字段，索引文件从 380 MB 涨到 1.1 GB，Buffer Pool 的有效缓存率下降。 - 写变慢了。每次 INSERT/UPDATE 要维护的索引从 2 个变 3 个，实测订单创建接口的 DB 耗时从 4.1 毫秒涨到 5.7 毫秒。
- 字段顺序有讲究。等值条件列在前（user_id），范围条件列次之（create_time），剩下的过滤列和 select 列放后面。这个顺序下
key_len是 13（user_id 8 字节 + create_time 5 字节），说明索引定位只用到前两列，后面三列纯粹是为了覆盖。
我们评估下来，这条 SQL 是订单列表页的主查询，读写比大概 200:1，牺牲 1.6 毫秒的写换取 1.8 秒的读，非常划算。
ICP 不是什么时候都能用
顺手补几个 ICP 的限制条件，都是我在实验里踩到的。
聚簇索引上不生效。InnoDB 的聚簇索引（主键树）叶子节点就存着完整行数据，不需要回表，ICP 没有意义。所以你看到 type: eq_ref、key: PRIMARY 的查询，Extra 里永远不会出现 Using index condition。
索引覆盖时不需要。如果索引已经覆盖了所有查询列（Using index），回表这一步都没有了，ICP 自然也用不上。
虚拟生成列、子查询条件不支持下推。我们有个查询条件写了 WHERE JSON_EXTRACT(ext, '$.type') = 'A'，这个没法下推，因为 JSON_EXTRACT 是 Server 层的函数，InnoDB 不认识。
触发器里的条件不下推。这个比较冷门，官方手册里提到了，我实际没验证过。
还有个容易忽略的：ICP 的收益跟过滤条件的选择性直接相关。如果下推的条件能过滤掉 90% 的行，收益巨大；如果只能过滤掉 5%，收益微乎其微，还可能因为多做了判断而更慢。我们那个 status IN (2,3,4) 过滤掉了 90%，所以效果显著。
判断某个 ICP 到底省了多少次回表，MySQL 8.0 的 EXPLAIN ANALYZE 能看到真实数据：
mysql> EXPLAIN ANALYZE SELECT ... \G
-> Index range scan on orders using idx_user_status
over (user_id = 10086 AND '2019-05-01' <= create_time),
with index condition: (orders.status in (2,3,4))
(cost=... rows=2841) (actual time=0.091..184.221 rows=287 loops=1)
rows=2841 是优化器预估的扫描行数，actual ... rows=287 是 ICP 过滤后真正回表的行数。这两个数字的差就是 ICP 的收益。MySQL 8.0.18 之前 EXPLAIN ANALYZE 的输出格式还不太一样，我们用的是 8.0.17，输出略简略，但关键信息都有。
这是我觉得 MySQL 8.0 最实用的改进之一。以前只能看 rows 和 filtered 的估算值，经常和实际差一个数量级，现在有 actual 了，调优不用再靠猜。
还有个更好的办法
上线覆盖索引后我在想，2841 行里最终满足 amount > 1000 的只有 300 多行，能不能让 amount 也参与索引定位？不行，因为 create_time 是范围查询，它后面的列都无法用于定位，只能用于覆盖或 ICP 过滤。
但换个索引列顺序可以：
mysql> ALTER TABLE orders ADD INDEX idx_cover2 (user_id, status, create_time, amount, order_no);
mysql> EXPLAIN ... \G
key: idx_cover2
key_len: 10 -- user_id(8) + status(2)，两个等值列都用上了
rows: 312
Extra: Using where; Using index
把 status 提到 create_time 前面，两个等值列都用于定位，rows 从 2841 直接降到 312。耗时 0.003 秒。
这个改动的代价是：如果别的查询只用 user_id + create_time 做条件，idx_cover2 就用不上了。我们统计了一下，按 user_id + 时间范围 查的 SQL 有另外 3 条，都是后台管理系统的低频查询，所以最终保留了两个索引：idx_cover2 服务高频的那个列表页，idx_user 保留给其他查询。
留个问题
关于《MySQL 索引下推与覆盖索引优化实战》里这个坑，你当时是怎么处理的？欢迎在评论区聊聊你踩过的类似情况。