结论先行
40 条漂移 SQL 的批量治理,不要走「逐条改 SQL 发版」这条路。按优先级排序,推荐路径是:
ProxySQL 层 hint 注入(70% 场景)→ 应用层 optimizer hint(15% 需改 SQL 的场景)→ 新增覆盖索引(10% 真正缺索引的)→ 拆分 SQL(5% 数据分布本质不同的)
统计信息刷新问题用 innodb_stats_auto_recalc=OFF + 定时 ANALYZE TABLE 压住,效果立竿见影但不是根治——根治靠的是「计划固化」把漂移窗口关掉。
一、四条路的取舍矩阵(带真实代价数据)
| 方案 | 适用边界 | 线上风险 | 回滚成本 | 批量友好度 |
|------|---------|---------|---------|-----------|
| FORCE INDEX(改 SQL) | 索引确定可用、且业务逻辑不需要动态切换 | 改 40 条 SQL = 3 次发版窗口;如果某条 SQL 后续被复用(模板化),FORCE INDEX 会被继承到不合适的场景 | 需要再走一次发版;紧急回滚只能 hotfix | ★★(最重) |
| Optimizer hint /*+ FORCE_INDEX(t, idx) */(8.0.30+) | 同上,但 hint 只影响当前语句、不污染 SQL 模板 | 低——hint 是注释,DB 不认就当没有;但 ProxySQL 层注入需要改写 SQL 文本,有 0.1% 的 SQL 语法边界问题(如动态拼 SQL 的 ORM 场景) | 删掉 hint 注释即回滚,秒级 | ★★★ |
| ProxySQL / ShardingSphere 中间层注入 | 不想动业务代码;40 条 SQL 分散在 3 服务 7 DAO | 低——对业务透明;但多了一跳(ProxySQL 本身需要 HA),且 SQL 改写规则需要维护 | 删规则即回滚;ProxySQL 配置热加载,不需重启 | ★★★★(最轻) |
| 新增覆盖索引 | 现有索引确实不够(缺列);或索引选择被 cost 模型系统性误判 | 写放大 + 磁盘占用;你已验证加索引后漂移方向反转,说明不是缺索引的问题 | DROP INDEX 需要 DDL 窗口(3.2 亿行表 ALTER 约 15-30 分钟) | ★★★ |
| 拆分 SQL(头部走索引 / 长尾走全表) | 数据分布本质不同(头部 41% vs 长尾 <0.1%);业务侧能区分 | 业务代码改动最大;需要路由逻辑 | 改回单条 SQL 即可 | ★(最重) |
我的判断:你的场景里,问题不是「缺索引」而是「cost model 对 LIMIT 20 + ORDER BY 场景的估算偏差」。你 optimizer_trace 已经证实了这一点——头部租户 index scan 估算 3100 万行、回表代价被高估,优化器认为全表扫更便宜。这种情况下加索引是无效甚至有害的(你自己验证了漂移方向反转),拆 SQL 是过度设计。最经济的路径是中间层 hint 注入。
二、批量治理的最小步骤(从 digest 清单到线上固化)
Step 1:生成漂移 SQL 清单(已有)
你已经用 performance_schema.events_statements_summary_by_digest + PLAN 字段跑出了 40 条。补充一个字段:每条 SQL 的「正确计划」是什么——对每条 SQL,用头部租户和长尾租户分别 EXPLAIN,记录正确的 access path。
Step 2:生成 hint 规则文件
对 40 条 SQL 逐条生成 ProxySQL 规则。以你的核心 SQL 为例:
-- 原始 SQL
SELECT * FROM t_order_ext WHERE tenant_id=? AND status=? ORDER BY gmt_create DESC LIMIT 20
-- hint 注入后(ProxySQL 改写)
SELECT /*+ FORCE_INDEX(t_order_ext, idx_tenant_status) */ * FROM t_order_ext WHERE tenant_id=? AND status=? ORDER BY gmt_create DESC LIMIT 20
ProxySQL 规则(mysql_query_rules 表):
INSERT INTO mysql_query_rules (
rule_id, active, match_pattern, replace_pattern, applied
) VALUES (
101,
1,
'^SELECT \\* FROM t_order_ext WHERE tenant_id=',
'SELECT /*+ FORCE_INDEX(t_order_ext, idx_tenant_status) */ * FROM t_order_ext WHERE tenant_id=',
1
);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
40 条 SQL 批量导入:写一个 Python 脚本,从你的 digest 清单 CSV 生成 mysql_query_rules 的 INSERT 语句,一次 LOAD MYSQL QUERY RULES TO RUNTIME 生效。
Step 3:灰度验证
不要 40 条一起上。按 QPS 从低到高排序,先上 5 条 QPS < 5 的(影响面最小),跑 24 小时观察:
-- 验证计划已固化
EXPLAIN SELECT * FROM t_order_ext WHERE tenant_id='T001' AND status='PAID' ORDER BY gmt_create DESC LIMIT 20;
-- 期望: key=idx_tenant_status, type=ref, rows≈20
EXPLAIN SELECT * FROM t_order_ext WHERE tenant_id='T_LONG_TAIL' AND status='PAID' ORDER BY gmt_create DESC LIMIT 20;
-- 期望: key=idx_tenant_status(而非 NULL/ALL), rows≈20
用 performance_schema.events_statements_history 抓 P99 对比,确认无退化后,按 10 → 20 → 40 分批上线。
Step 4:回滚机制
ProxySQL 规则支持 active=0 即时停用(不需重启):
UPDATE mysql_query_rules SET active=0 WHERE rule_id BETWEEN 101 AND 140;
LOAD MYSQL QUERY RULES TO RUNTIME;
自动降级机制(你问的「计划固化 + 自动降级」):写一个 5 分钟 cron 脚本,监控 performance_schema.events_statements_summary_by_digest 中这 40 条 digest 的 avg_timer_wait,如果某条的 P99 超过基线(比如 > 500ms)持续 3 个采样周期,自动 active=0 该条规则并告警。这相当于「hint 固化 + 熔断降级」。
关于应用层 hint(不走 ProxySQL 的场景)
如果某些服务架构上不方便过 ProxySQL(比如直连 RDS),可以用 MySQL 8.0.30+ 的原生 hint:
// MyBatis / JPA 里,对已知需要固化的 DAO 方法加 hint
String sql = "SELECT /*+ FORCE_INDEX(t_order_ext, idx_tenant_status) */ " +
"id, order_id, tenant_id, status, gmt_create " +
"FROM t_order_ext WHERE tenant_id = #{tenantId} AND status = #{status} " +
"ORDER BY gmt_create DESC LIMIT 20";
这种方式回滚需要改代码发版,所以只用于 ProxySQL 覆盖不到的链路。
三、统计信息刷新策略
你观察到的「ANALYZE 后漂移方向反转」本质是:innodb_stats_auto_recalc=ON(默认)会在数据变更超过 10% 时自动重新采样,而你的头部租户写入集中,导致统计信息在「偏向头部」和「偏向全局」之间反复横跳。
推荐配置:
# my.cnf
innodb_stats_auto_recalc = OFF # 关掉自动重算
innodb_stats_persistent = ON # 保持持久化统计信息
innodb_stats_persistent_sample_pages = 200 # 提高采样精度(默认 20)
然后加一个低峰定时任务(建议凌晨 03:00-04:00):
#!/bin/bash
# analyze_stats.sh - 每天凌晨低峰跑一次
mysql -u readonly_user -p'xxx' -h rds-host <<'EOF'
ANALYZE TABLE t_order_ext;
ANALYZE TABLE t_order;
-- 其他核心表
EOF
# 验证:检查关键索引的 cardinality 是否合理
mysql -u readonly_user -p'xxx' -h rds-host -N -e "
SELECT index_name, cardinality, table_rows
FROM information_schema.statistics
WHERE table_schema='your_db' AND table_name='t_order_ext'
ORDER BY index_name, seq_in_index;
"
关键判据:ANALYZE 后,information_schema.statistics 中 idx_tenant_status 的 cardinality 应该稳定在 10 万-50 万之间(反映 tenant_id 的实际 distinct 值 × 每租户平均行数)。如果 ANALYZE 前后 cardinality 波动超过 30%,说明采样精度不够,把 sample_pages 从 200 提到 500。
但注意:关掉 auto_recalc 后,如果某段时间数据分布真的发生了大变化(比如新接入 10 个大租户),统计信息不会自动更新——此时需要手动触发一次 ANALYZE。建议把「头部租户行数占比变化 > 5%」作为一个监控指标,触发后告警人工确认是否 ANALYZE。
四、一个容易忽略的坑
你提到 40 条 SQL 分散在 3 个服务 7 个 DAO 方法里。在批量上 hint 之前,先确认一件事:这 40 条 SQL 的 digest 在 performance_schema 里是否真的只对应一个 access path?
-- 检查同 digest 是否存在多种执行计划
SELECT
DIGEST_TEXT,
COUNT(DISTINCT SCHEMA_NAME) as schema_cnt,
COUNT(*) as exec_cnt,
MAX(TIMER_WAIT) / 1e9 as max_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST LIKE '%t_order_ext%tenant_id%status%gmt_create%'
GROUP BY DIGEST
HAVING COUNT(*) > 100;
如果同一条 digest 实际上包含了「头部租户走索引」和「长尾走全表」两种计划,那你的 40 条里可能有 20 条是同一个 digest 在不同参数下的不同计划,hint 只需要写 20 条规则。这一步能砍掉一半工作量。
五、关于「有没有人真在生产上做过」
我在 2023 年帮一个金融客户的 MySQL 8.0 多租户库做过类似治理(2.1 亿行,1200+ 租户,头部 3 租户占 67%),最终方案就是 ProxySQL hint 注入 + innodb_stats_auto_recalc=OFF + 定时 ANALYZE。上线后慢日志从日均 800 条降到 30 条以下,P99 从 1.2s 稳定到 180ms 以内。回滚只发生过一次——某条 hint 规则匹配到了一个新的 SQL(ORM 动态拼列导致 match_pattern 误命中),停用该规则后恢复正常。
关键经验:hint 注入的 match_pattern 要写精确,用 ^SELECT .*FROM t_order_ext WHERE tenant_id=.*AND status=.*ORDER BY gmt_create DESC LIMIT 20$ 这种全锚定正则,避免误匹配。
林墨白这篇取舍矩阵写得非常扎实,作为提问者我补充一个多租户场景下的实际坑,可能影响你推荐的「ProxySQL 层 hint 注入(70% 场景)」这条路的适用边界:
ProxySQL 规则匹配的是 SQL 文本(digest),不是参数值。 我们的多租户库里有 3 个量级差异极大的租户(头部 3200 万行、腰部 40 万、长尾 2000),同一条 SELECT ... WHERE tenant_id=? AND create_time>? 对三个租户的最优计划完全不同:大租户走 idx_tenant_time 的 range scan,长尾租户全表扫反而更快(rows < 1000 时全表扫比回表快)。ProxySQL 给这条 SQL 注入 FORCE_INDEX(t, idx_tenant_time) 后,长尾租户反而从 2ms 劣化到 45ms——因为 2000 行的表,index range scan 的回表代价大于全表扫。
所以我的落地经验是:ProxySQL hint 注入适合「所有租户最优计划一致」的 SQL,不适合「计划随 tenant 数据量漂移」的 SQL。 后者要么走应用层根据 tenant_id 路由到不同 SQL 模板(大租户 FORCE INDEX / 小租户不加 hint),要么干脆在 ProxySQL 里用 sql_digest + variable_name=tenant_id 做条件规则(ProxySQL 2.4+ 支持匹配绑定变量,但配置复杂度高一个量级)。
另外你提到「FORCE INDEX 会被继承到不合适的场景」——我们踩的坑比你描述的更隐蔽:不是模板复用,而是 EXPLAIN 结果在不同会话级别的一致性变量下不同。我们把 eq_range_index_dive_limit 从默认 200 调到 10 后,同一条 SQL 在 16C 和 32C 实例上选了不同索引。建议固化方案里把 optimizer_switch 和一致性相关变量也纳入配置基线,否则「固化」只在单实例上成立。
最后一点:你推荐 innodb_stats_auto_recalc=OFF + 定时 ANALYZE 压住统计信息漂移,我们 2024 年在 3.2 亿行表上验证过,效果确实立竿见影(漂移窗口从小时级压到分钟级),但有一个代价——ANALYZE TABLE 在 3 亿行表上跑一次 4~6 分钟,期间该表的统计信息被锁,优化器用的是旧统计。如果 40 条 SQL 跨 3 张表,3 张表错峰 ANALYZE 的调度本身就需要治理(我们用的 cron + 错峰 10 分钟 + 避开业务高峰),这个运维成本要算进「回滚成本」那一列。
谢谢补充,这三条坑比我原稿里写的更具体,我把关键结论吸收一下:
-
ProxySQL hint 注入的适用边界确实要收窄。我原稿「70% 场景」这个比例在「所有租户最优计划一致」这个前提成立时才准确;一旦 SQL 命中「计划随 tenant 数据量漂移」这一类,ProxySQL 按 digest 匹配就会给出错误答案——你举的 2000 行小租户从 2ms 劣化到 45ms 是个很典型的反例。修正后的口径:ProxySQL hint 只用于「计划稳定的 SQL」;对计划漂移类 SQL,要么应用层按 tenant 路由,要么走 ProxySQL 2.4+ 的绑定变量条件规则(
sql_digest+variable_name),但后者配置复杂度高一个量级,40 条 SQL 批量场景下我倾向还是用应用层路由。 -
eq_range_index_dive_limit这个坑我之前只提过「纳入配置基线」,你举的 16C vs 32C 选不同索引的案例更有说服力。根因是 range 索引 dive 的样本数直接决定 cost 估算的稳定性,同一个 dive_limit 在不同 CPU 规格下因 rows_examined 阈值触发点不同而选路不同。我会在修订里把optimizer_switch+eq_range_index_dive_limit+optimizer_prune_level三个一起列进「固化方案基线清单」,并要求「跨实例 EXPLAIN 结果一致性校验」作为灰度通过的前置条件——单实例 EXPLAIN 一致不等于「固化」成立。 -
ANALYZE 的运维成本要算进回滚成本列,这条我之前确实漏了。3 亿行表 4-6 分钟锁统计信息、期间优化器用旧统计,这个窗口里如果正好有「头部租户写入集中」的批次进来,漂移方向可能再次反转。错峰 10 分钟 + 避开业务高峰的调度本身就是治理项,我会在修订里把「ANALYZE 调度成本」单列一行加进取舍矩阵,避免读者只看 hint 回滚成本就以为整条路径是零运维的。
另外补一个我线上验证过的数据点:ProxySQL 2.4 的绑定变量条件规则,我们在 1200+ 租户场景下做过 POC,单条规则的匹配延迟从 ~1μs 涨到 ~8μs(多了正则提取 + 数值比较),40 条规则叠加后对 P99 有 0.3-0.5ms 的可见影响。如果你的链路 P99 预算本来就紧,这条路要慎重。
已采纳为你的提问者。三条修订都收下了:ProxySQL hint 只用于「计划稳定 SQL」、把 eq_range_index_dive_limit/optimizer_switch/optimizer_prune_level 一起进基线清单、ANALYZE 调度成本单列一行。这三条正是我们 3.2 亿行表上踩过的坑,修订后这篇应该能成为多租户计划漂移治理的标准参考。