运营说：这个报表等到花儿都谢了
十一月底，运营同学提了个工单："销售明细报表要等 8 秒以上，导出的时候更是直接超时。"
我看了下 slow log，那条查询平均 8.4 秒，最慢的一次 21 秒：
# Time: 2020-11-24T14:22:31.882113+08:00
# User@Host: appuser[appuser] @ [10.20.1.31] Id: 88213
# Query_time: 21.441208 Lock_time: 0.000214 Rows_sent: 20 Rows_examined: 48213422
SET timestamp=1606198951;
SELECT o.order_no, o.create_time, u.nick_name, u.phone,
(SELECT SUM(oi.amount) FROM t_order_item oi WHERE oi.order_no = o.order_no) AS total_amount,
(SELECT COUNT(*) FROM t_order_item oi WHERE oi.order_no = o.order_no) AS item_count,
(SELECT MAX(p.pay_time) FROM t_payment p WHERE p.order_no = o.order_no) AS last_pay_time
FROM t_order o
LEFT JOIN t_user u ON o.user_id = u.id
WHERE o.status = 'FINISHED'
AND o.create_time >= '2020-10-01'
AND o.create_time < '2020-11-01'
ORDER BY o.create_time DESC
LIMIT 0, 20;
关键信息：Rows_sent: 20 但 Rows_examined: 48213422。返回 20 行，扫描了 4821 万行。这个比例说明优化空间巨大。
这个 SQL 是我自己两个月前写的，当时数据量小，跑了 300ms 就上线了。
第一步：EXPLAIN
mysql> EXPLAIN SELECT ... \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: o
partitions: NULL
type: ALL # 全表扫描！
possible_keys: idx_status, idx_create_time
key: NULL # 一个索引都没用上
key_len: NULL
ref: NULL
rows: 3821442
filtered: 1.00
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: u
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: shop.o.user_id
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY # 相关子查询！
table: oi
partitions: NULL
type: ALL # 又是全表扫描
possible_keys: NULL
key: NULL
rows: 12431122
filtered: 10.00
Extra: Using where
三个问题一目了然：
- 主表
o走全表扫描，possible_keys里有idx_status和idx_create_time，但没用上。 Using filesort，排序没走索引。- 三个
DEPENDENT SUBQUERY，每个都是全表扫描 1243 万行的t_order_item。
先解决相关子查询
这是最大的问题。DEPENDENT SUBQUERY 的意思是子查询依赖外层查询的每一行的值。外层扫出多少行，子查询就执行多少次。
主表过滤后大概有 38 万行（10 月份的已完成订单），每行要跑 3 个子查询，每个子查询全表扫 1243 万行：
380000 × 3 × 12431122 ≈ 1.4 × 10^13 次行扫描
不过 Rows_examined 显示的是 4821 万，说明优化器做了一点优化（子查询里有 LIMIT 或者提前终止），但依然是个天文数字。
改成 JOIN + GROUP BY，子查询只执行一次：
SELECT o.order_no, o.create_time, u.nick_name, u.phone,
agg.total_amount,
agg.item_count,
pay.last_pay_time
FROM t_order o
LEFT JOIN t_user u ON o.user_id = u.id
LEFT JOIN (
SELECT order_no,
SUM(amount) AS total_amount,
COUNT(*) AS item_count
FROM t_order_item
GROUP BY order_no
) agg ON agg.order_no = o.order_no
LEFT JOIN (
SELECT order_no, MAX(pay_time) AS last_pay_time
FROM t_payment
GROUP BY order_no
) pay ON pay.order_no = o.order_no
WHERE o.status = 'FINISHED'
AND o.create_time >= '2020-10-01'
AND o.create_time < '2020-11-01'
ORDER BY o.create_time DESC
LIMIT 0, 20;
但这样写更糟。因为派生表（FROM 里的子查询）会先物化成临时表，它要先对 1243 万行的 t_order_item 做全量 GROUP BY，再跟外表 JOIN。实测耗时 34 秒，比原来还慢。
正确的做法是先过滤主表，再用过滤后的结果去 JOIN。用延迟关联（deferred join）的思路：
SELECT o.order_no, o.create_time, u.nick_name, u.phone,
agg.total_amount, agg.item_count, pay.last_pay_time
FROM t_order o
LEFT JOIN t_user u ON o.user_id = u.id
LEFT JOIN (
SELECT oi.order_no,
SUM(oi.amount) AS total_amount,
COUNT(*) AS item_count
FROM t_order_item oi
INNER JOIN t_order o2 ON oi.order_no = o2.order_no
WHERE o2.status = 'FINISHED'
AND o2.create_time >= '2020-10-01'
AND o2.create_time < '2020-11-01'
GROUP BY oi.order_no
) agg ON agg.order_no = o.order_no
LEFT JOIN t_payment pay ON pay.order_no = o.order_no
WHERE o.status = 'FINISHED'
AND o.create_time >= '2020-10-01'
AND o.create_time < '2020-11-01'
ORDER BY o.create_time DESC
LIMIT 0, 20;
这个版本跑出来 5.2 秒，快了 60%。但还不够，主表的全表扫描和 filesort 还在。
第二步：解决主表的索引和排序
为什么现有索引没用上
表上有两个单列索引：
mysql> SHOW INDEX FROM t_order;
+---------+------------+------------------+--------------+-------------+-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality |
+---------+------------+------------------+--------------+-------------+-----------+
| t_order | 1 | idx_status | 1 | status | 6 |
| t_order | 1 | idx_create_time | 1 | create_time | 2841132 |
+---------+------------+------------------+--------------+-------------+-----------+
idx_status 的 Cardinality 只有 6（订单一共 6 种状态），区分度极差。用它要回表 38 万次，优化器评估下来不如直接全表扫描。
idx_create_time 区分度高（284 万），但 status 这个条件用不上，还是得回表过滤。
MySQL 5.6 之后有 index condition pushdown（ICP），能把 status 的过滤下推到存储引擎层，减少回表次数。但ICP 只对二级索引生效，而且要求过滤条件是索引的一部分。这里 status 不在 idx_create_time 里，ICP 帮不上忙。
建联合索引
查询条件是 WHERE status = ? AND create_time BETWEEN ? AND ?，排序是 ORDER BY create_time DESC。建一个 (status, create_time) 的联合索引：
ALTER TABLE t_order
ADD INDEX idx_status_createtime (status, create_time);
-- MySQL 8.0 支持降序索引，ORDER BY create_time DESC 能直接用上
ALTER TABLE t_order
ADD INDEX idx_status_createtime_desc (status, create_time DESC);
加完之后 EXPLAIN：
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: o
partitions: NULL
type: range # 从 ALL 变成 range
possible_keys: idx_status_createtime
key: idx_status_createtime
key_len: 87
ref: NULL
rows: 381422
filtered: 100.00
Extra: Using index condition; Using where # filesort 消失了
Using filesort 消失了——因为 (status, create_time) 这个索引本身就有序，取到的数据天然按 create_time 排好序，不需要额外排序。
耗时从 5.2 秒降到 1.8 秒。
为什么 DESC 索引没带来额外收益
我特意试了 MySQL 8.0 的降序索引 (status, create_time DESC)，结果跟普通索引耗时一样（1.78s vs 1.81s）。原因是MySQL 的 B+ 树索引支持双向扫描——叶子节点之间有双向链表，正序倒序扫都可以，只是扫描方向不同。降序索引真正的价值在混合排序场景，比如 ORDER BY a ASC, b DESC，这种情况下普通索引无论如何都要 filesort。
第三步：覆盖索引，消除回表
现在主表的扫描是 38 万行，每行都要回表取 order_no、user_id 这些字段。如果把这些字段都放进索引，就能避免回表。
ALTER TABLE t_order
DROP INDEX idx_status_createtime,
ADD INDEX idx_status_ct_cover (status, create_time, order_no, user_id);
type: range
key: idx_status_ct_cover
key_len: 87
rows: 381422
Extra: Using where; Using index # 出现 Using index，覆盖索引生效
Using index 说明所有需要的字段都在索引里，不需要回表查聚簇索引。耗时从 1.8 秒降到 1.1 秒。
但覆盖索引有代价：索引变大了，占用的内存和磁盘更多，也会拖慢写入。我们这个索引从原来的 87 字节/行涨到 121 字节/行，索引大小从 220MB 涨到 306MB。这是可以接受的——报表查询是高频操作，而这张表的写入 QPS 只有 200。
第四步：解决 LIMIT 20 却扫 38 万行的问题
现在还有个浪费：我们只需要 20 行，却要把 38 万行全部处理完才能排序取前 20。虽然排序已经走索引了，但 JOIN 和聚合还是要把 38 万行都算一遍。
改成延迟关联：先在主表里用覆盖索引找出这 20 个 order_no，再拿这 20 个去 JOIN 其他表：
SELECT o.order_no, o.create_time, u.nick_name, u.phone,
agg.total_amount, agg.item_count, pay.last_pay_time
FROM (
-- 子查询只扫索引，且只取 20 行
SELECT id, order_no, user_id, create_time
FROM t_order
WHERE status = 'FINISHED'
AND create_time >= '2020-10-01'
AND create_time < '2020-11-01'
ORDER BY create_time DESC
LIMIT 0, 20
) o
LEFT JOIN t_user u ON o.user_id = u.id
LEFT JOIN (
SELECT order_no, SUM(amount) AS total_amount, COUNT(*) AS item_count
FROM t_order_item
WHERE order_no IN ( /* 上面那 20 个 order_no */ )
GROUP BY order_no
) agg ON agg.order_no = o.order_no
LEFT JOIN (
SELECT order_no, MAX(pay_time) AS last_pay_time
FROM t_payment
WHERE order_no IN ( /* 同样 20 个 */ )
GROUP BY order_no
) pay ON pay.order_no = o.order_no
ORDER BY o.create_time DESC;
需要在代码里分两步查：先查主表拿 20 个 order_no，再查明细。这样反而更简单，也不用在 SQL 里写那个 IN：
public List<ReportRow> query(ReportQuery query) {
// 第一步：只查主表，走覆盖索引，0.2 秒
List<OrderBrief> briefs = orderMapper.queryBriefPage(query);
if (briefs.isEmpty()) return Collections.emptyList();
List<String> orderNos = briefs.stream()
.map(OrderBrief::getOrderNo)
.collect(Collectors.toList());
// 第二步：批量查明细，20 个 order_no 走索引，0.03 秒
Map<String, ItemAgg> itemAgg = itemMapper.aggByOrderNos(orderNos).stream()
.collect(Collectors.toMap(ItemAgg::getOrderNo, Function.identity()));
Map<String, LocalDateTime> payTime = paymentMapper.lastPayTimeByOrderNos(orderNos).stream()
.collect(Collectors.toMap(PayTime::getOrderNo, PayTime::getLastPayTime));
Map<Long, UserVO> users = userClient.batchGet(
briefs.stream().map(OrderBrief::getUserId).collect(Collectors.toList()));
// 第三步：内存里组装
return briefs.stream().map(b -> assemble(b, itemAgg, payTime, users))
.collect(Collectors.toList());
}
<!-- 第一步的 SQL，可以用覆盖索引 -->
<select id="queryBriefPage" resultType="OrderBrief">
SELECT id, order_no, user_id, create_time
FROM t_order
WHERE status = 'FINISHED'
AND create_time >= #{startTime}
AND create_time < #{endTime}
ORDER BY create_time DESC
LIMIT #{offset}, #{size}
</select>
拆成两步之后，第一趟 0.21 秒，第二趟 0.03 秒，加上组装总共 0.26 秒。
第五步：给子表加索引
顺手确认一下子表的索引情况：
mysql> SHOW INDEX FROM t_order_item;
+---------------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+---------------+------------+----------+--------------+-------------+
| t_order_item | 1 | idx_ono | 1 | order_no |
+---------------+------------+----------+--------------+-------------+
t_order_item 有 idx_ono，OK。但 t_payment 没有：
mysql> SHOW INDEX FROM t_payment;
Empty set (0.00 sec)
一张表一个索引都没有，只有主键。EXPLAIN 里能看到它是全表扫描（type=ALL，rows=892341）。加索引：
ALTER TABLE t_payment ADD INDEX idx_ono_paytime (order_no, pay_time);
(order_no, pay_time) 这个联合索引对 SELECT order_no, MAX(pay_time) ... GROUP BY order_no 是覆盖索引，连回表都省了。
索引重建：别忘了 ANALYZE
加完索引之后，统计信息可能还是旧的，优化器会用错索引。跑一次：
mysql> ANALYZE TABLE t_order, t_order_item, t_payment;
+------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| shop.t_order | analyze | status | OK |
+------------------+---------+----------+----------+
mysql> SHOW INDEX FROM t_order;
-- 重新看 Cardinality，确认统计信息更新了
另外 t_order 表因为之前有大量 UPDATE，索引碎片率不低。查一下：
mysql> SELECT table_name, data_length, index_length,
data_free,
ROUND(data_free / (data_length + index_length) * 100, 2) AS frag_pct
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_name = 't_order';
+------------+-------------+--------------+-----------+----------+
| table_name | data_length | index_length | data_free | frag_pct |
+------------+-------------+--------------+-----------+----------+
| t_order | 2147483648 | 1207959552 | 734003200 | 21.88 |
+------------+-------------+--------------+-----------+----------+
碎片率 21.88%（data_free 是碎片空间）。MySQL 8.0 用这条命令重建表并整理碎片：
-- MySQL 8.0 推荐，Online DDL，不阻塞 DML
ALTER TABLE t_order FORCE, ALGORITHM=INPLACE, LOCK=NONE;
-- 或者通用的
OPTIMIZE TABLE t_order;
OPTIMIZE TABLE 在 8.0 里是 online 的，但会重建表并重建所有索引，耗时较长（我们这张 2GB 的表跑了 8 分钟）。要在低峰期做，而且要注意它会让主从延迟上涨。我们是在凌晨两点执行的，从库延迟峰值 4 分钟。
重建之后碎片率降到 0.8%，索引大小从 1.2GB 降到 890MB。查询再快了 30ms。
优化结果
| 阶段 | 耗时 | 扫描行数 | 关键动作 |
|---|---|---|---|
| 原始 SQL | 8420ms | 4821 万 | — |
| 改 JOIN（错误改法） | 34100ms | 更糟 | 派生表全量物化 |
| 改 JOIN（延迟关联） | 5210ms | 1243 万 | 子查询只跑一次 |
| 加联合索引 | 1810ms | 38 万 | 消除 filesort |
| 覆盖索引 | 1120ms | 38 万 | 消除回表 |
| 拆两步查 | 260ms | 20 + 20 | LIMIT 提前 |
| 子表加索引 + 重建 | 203ms | 20 + 20 | 消除子表全扫 |
最终 203ms，比原来的 8420ms 快 41 倍。运营的反馈是"点下去就出来了"。
几个方法论
这次优化过程中反复用到的判断标准，写下来给自己备忘：
- 看
Rows_examined/Rows_sent的比值。这个比值越大，优化空间越大。超过 1000 就值得动手。 - EXPLAIN 三个字段优先看：
type（至少要到 range，最好 ref/eq_ref，ALL 和 index 都要警惕）、key（是否用上了预期的索引）、Extra（Using filesort和Using temporary是大忌）。 - 看到
DEPENDENT SUBQUERY先警觉。它意味着子查询要跑 N 遍。能改成 JOIN 就改。 - 派生表（FROM 里的子查询）会物化。不要指望优化器帮你把外层条件下推进去（MySQL 8.0 有一定优化但不保证）。拆分到应用层查两次往往更快。
- 联合索引的顺序：等值条件在前，范围条件在后，排序列跟在后面。
(status, create_time)比(create_time, status)好，因为status=是等值，create_time是范围，范围之后的列用不上索引。 - 覆盖索引不是免费的。索引变大、写入变慢。只在读远大于写的场景用。
下篇预告
这篇先把《SQL 优化实战：从 8 秒到 200 毫秒》里的坑列了，下一篇写我们当时是怎么在线上工程里真正落地的——包括那次让领导拍桌的故障复盘。