P0 索引修复 · 实测对比

本地副本 xipuerp 278 万行 / 766 MB 实测时间: 2026-06-13 13:00 基线: 同一份生产数据

把上一份报告里 P0 的 3 条索引加上,跑完同样的 6 个时间窗口对比 —— 结果跟我预想的不一样。索引在主查询那段起了作用(EXPLAIN 切到新索引、rows 估值少 73%), 但真正的瓶颈不在索引层,在 SQL 把「销售」和「退货」糅在一个查询里。这是个意外但有用的发现。

1 · 一句话结论

P0 单加索引,对这个 SQL 基本无效。

本来预想 1 月 1.9 秒 → 0.3 秒、半年 47 秒 → 5 秒。实测 1 月 1.85 秒(基本没变),半年 47 秒(也没变),1 周还反向变慢(0.27 秒 → 1.55 秒)。 诚实告诉你: 我上一份报告里的 P0 预估是错的,索引救不了这条 SQL。下面解释为什么,以及真正能救的路。

2 · 实测对比 · 6 个窗口

窗口
修前 (无 P0)
修后 (加索引)
变化
1 天
228 ms
220 ms
−4%
1 周
271 ms
1,557 ms
+474% ⚠️
1 月
1,868 ms
1,836 ms
−2%
3 月
13,013 ms
12,914 ms
−1%
6 月
47,644 ms
47,456 ms
−0%

6 个窗口里 5 个基本没变,1 周反向变慢 5 倍 — 索引没救成,反而引起优化器选错索引。

3 · 为什么索引不管用 · 真根因

关键实验:把 SQL 拆成两段单独跑

不带 retreat (销售部分)
98 ms
1 月窗口 · 主查询自己
单独跑 retreat 子查询
6,300 ms
1 月窗口 · 占总耗时 ~90%

真根因 = SQL 把销售 + 退货糅在一起,退货那段笛卡儿积

整条报表 SQL 长这样:

SELECT base.category_id, ...
FROM (...sales 销售部分...) AS base
LEFT JOIN (...retreat 退货回收部分...) AS stats
  ON base.category_id = stats.category_id

P0 索引救了销售那段(主查询从全表扫切到 idx_guide_time, rows 估值 26k → 7k), 但退货那段有个结构问题 — 它先扫 4.5 万行退货明细(xipunum_erp_retreat 整表), 再跟时间窗口出来的 7k 行做笛卡儿积(MySQL 这一步叫 Block Nested Loop), 中间临时结果 800 万行,这一步索引救不了。

EXPLAIN 显示 <derived6> rows=8,021,767 + Using join buffer (Block Nested Loop)。 这是 MySQL 在两个临时结果集之间唯一能做的 join 算法 — 必然慢。

附带发现 · 1 周窗口反向变慢的原因

加索引后优化器误判: 它把新的 idx_guide_time 当作"更优", 结果在小数据集(1 周)上反而绕了远路。这是典型的"加索引引入优化器抖动" — prod 上加索引必须配 ANALYZE TABLE 刷新统计信息,我已加上但效果仍这样, 说明这条索引本身的选择性就不够好(店员名 + 时间组合),没起到剪枝作用。

4 · 真正能救的方案

✅ 改 P0 推荐 · SQL 层拆查询(不动 prod schema)

既然销售部分 98 ms 飞快、退货部分 6.3 秒拖死,把这两部分拆成 2 个接口,前端并行调:

预估总收益: 1 月报表从 1.9 秒 → 0.1 秒 (默认),开退货展示 +6 秒(用户主动选才付代价)。 半年报表同理: 47 秒 → 0.5 秒(默认),开退货 ~30 秒。

✅ P1 长效 · 重写退货子查询用 JOIN 顺序提示

退货那段 SQL 让 MySQL 优化器先扫 7k 行驱动表(STRAIGHT_JOIN hint 或重写子查询), 把 Block Nested Loop 从 45k × 7k 改成 7k × 45k 的查找,理论上 1 月退货从 6 秒 → 0.5 秒。 改动量 ≈ 一个 mapper.xml 文件,改完 prod 立刻见效。

⚪ P2 长期 · 预聚合 + 读写分离

夜间预聚合「每店员每天每分类」的销售+退货合计放新表,查报表只 SUM 几天 — 数据量从 13k 行降到几百行。 读写分离把报表查询全甩到 RDS 只读副本。

5 · 这次错在哪 · 我的复盘

上一份报告我看 EXPLAIN 看到 Block Nested Loop + rows=6,645,842, 以为是"索引缺失 → 全表扫"这类经典问题,直接给了 P0 加索引方案。

错在没把 SQL 拆开分别测。如果我先跑「不带 retreat 主查询」 → 98 ms, 再跑「单 retreat 子查询」 → 6.3 秒,就会立刻明白瓶颈不是缺索引, 是结构问题 — 一个外层 JOIN 把 derived 表当外驱表导致 BNL。这类结构问题加索引救不了。

教训: 看 EXPLAIN 复杂查询时,光看顶层 rows 估值会误判。要把子查询单独抽出来分别 benchmark, 才能定位真瓶颈。这次成本是 30 分钟的索引尝试 + 一次预估打脸 — 比 prod 上加错索引便宜得多。 这就是 vol 让我先在本地副本实测的价值。

附录 · 加的索引列表 + 实测脚本

加的 4 条索引(本地副本)

ALTER TABLE xipunum_erp_outbound 
  ADD INDEX idx_guide_time (shopping_guide, creationtime);

ALTER TABLE xipunum_erp_retreat_order 
  ADD INDEX idx_retreat_time_number (creationtime, retrea_umber);

ALTER TABLE xipunum_erp_retreat 
  ADD INDEX idx_retreat_order_covering 
  (order_id, product_name, jin_zhong, retreat_amount);

ALTER TABLE xipunum_erp_xitong_log 
  ADD INDEX idx_type_time (type, creationtime);

ANALYZE TABLE xipunum_erp_outbound, xipunum_erp_retreat, 
              xipunum_erp_retreat_order, xipunum_erp_outbound_order;

EXPLAIN 验证 — 销售主查询切到新索引 ✓ (rows 26k→7k)。但退货部分仍 BNL,索引层无解。

不要把这 4 条索引推到 prod

实测说明这 4 条索引在这条 SQL 上无效,prod 上加只会徒增索引维护成本(每次 INSERT/UPDATE 都要更新索引)。 xipunum_erp_xitong_log.idx_type_time 在其他报表上仍有价值 — 单独排期后再加。 不要把这次 4 条作为打包推到 prod。