导购在手机端打开「品类销售统计」报表,选 1 个月窗口要等 2 秒,选 3 个月直接转圈圈打不开,半年报表完全没法用。
实测后台 SQL 耗时:
| 时间窗口 | 耗时 | 体感 |
|---|---|---|
| 1 天 | 228 ms | 正常 |
| 1 周 | 271 ms | 正常 |
| 1 月 | 1,868 ms | 明显卡 |
| 3 月 | 13,013 ms | 用户放弃 |
| 6 月 | 47,644 ms | 完全不能用 |
这条 SQL 叫 v4UserOutOrderCategoryTotal,把销售统计 + 退货统计糅在一个查询里,做了几层嵌套 JOIN。
EXPLAIN 拆解后定位:
| 子查询 | 耗时(1月) | 问题 |
|---|---|---|
| 销售统计 (sales) | ~100 ms | 正常 |
| 订单工费 (fee) | ~150 ms | DISTINCT 去重,可优化 |
| 退货统计 (retreat) | ~1,600 ms | 笛卡儿积级 BNL,占 90% 总耗时 |
真根因:退货那段子查询里 xipunum_erp_retreat 表(45,052 行)走 ALL 全表扫,跟另一个派生表形成 Block Nested Loop,数据量越大成几何级放大。
第一反应:加索引。在本地副本加了 4 条针对热点字段的索引(idx_guide_time 等),实测结果:
| 时间窗口 | 加索引前 | 加索引后 | 结论 |
|---|---|---|---|
| 1 天 | 228 ms | 220 ms | 持平 |
| 1 周 | 271 ms | 1,557 ms | 反而变慢 5.7x |
| 1 月 | 1,868 ms | 1,836 ms | 持平 |
| 3 月 | 13,013 ms | 12,914 ms | 持平 |
| 6 月 | 47,644 ms | 47,456 ms | 持平 |
真根因不在索引层,在 SQL 把销售和退货糅在一起这件事本身。加索引修不了笛卡儿积。
所有 4 条本地索引已 DROP,生产 RDS 未受影响。
既然实时算几十万行就是慢,那就 提前算好。报表场景:
建 3 张「日预聚合表」:
| 表名 | 维度 | 行数 | 用途 |
|---|---|---|---|
agg_outbound_daily |
日期 × 品类 × 导购 | 13,862 | 销售件数 / 金额 / 净重 |
agg_outbound_order_daily |
日期 × 订单 × 品类 × 导购 | 65,628 | 工费 DISTINCT 去重 |
agg_retreat_daily |
日期 × 品类 × 退货单 | 36,117 | 退货金额 / 重量 |
总计 11.5 万行(原始 18.8 万行,1.6x 压缩),占用空间 < 20MB。
每 5 分钟跑一次脚本,只重算最近 7 天的数据:
错过任何一次执行,下次自动追上(因为是「重算」不是「增量加」)。逻辑上幂等,没有补偿队列、没有重试栈、没有 zombie 状态。
聚合表方案要落地,前提是新 SQL 跟老 SQL 出来的数字 一字不差。用 6 月窗口做对账(那个 47 秒的 case):
对账字段:销售件数 / 销售金额 / 销售净重 / 工费(含手工费) / 退货重量 / 退货金额 — 每一个数字完全相等。
DISTINCT (order_id, category_id, order_fee)。修复后 diff 完全为空。这个坑只有靠 6 月窗口对账才能暴露,1 月窗口看不出来。
| 时间窗口 | 优化前 | 聚合表 | 加速 | 用户体感 |
|---|---|---|---|---|
| 1 天 | 228 ms | 47 ms | 5x | 已经够快 |
| 1 周 | 271 ms | 52 ms | 5x | 已经够快 |
| 1 月 | 1,868 ms | 122 ms | 15x | 等 2 秒 → 秒开 |
| 3 月 | 13,013 ms | 148 ms | 88x | 等 13 秒 → 秒开 |
| 6 月 | 47,644 ms | 183 ms | 260x | 不能用 → 秒开 |
| 全年 | 估 90+ 秒 | 203 ms | 400x+ | 完全不能用 → 秒开 |
build_agg_tables.sql,3 张新表,无对现有表的影响。backfill_agg.sql。本地耗时 4.5 秒,生产 RDS 估计 < 30 秒。*/5 * * * * 跑滑动 7 天重算脚本。可以放在 162 机器上。weberp.xipugold.com 的 ThinkPHP 代码里找到报表 SQL(应在 Model/ 目录),替换为聚合表版本。?use_agg=1 切换,选几个导购账号试用 1 周。xipunum_erp_ksiamges 改了 SKU 的品类映射 → 历史聚合数据不会自动追溯。
对策: 改完触发一次全量重建即可。这是低频操作。
/tmp/build_agg_tables.sql — 建表 DDL/tmp/backfill_agg.sql — 全量回填脚本/tmp/orig_sql.sql / agg_sql.sql — 1 月对账 SQL 对/tmp/orig_sql_6m.sql / agg_sql_6m.sql — 6 月对账 SQL 对/tmp/bench_agg.sh — 聚合表 bench 脚本/tmp/bench2.sh — 基线 bench(对照组)