JOIN 驱动表选错：优化器把 6000 万行日志表当外层，STRAIGHT_JOIN 与统计信息纠偏

结论先行：多表 JOIN 慢，十有八九不是「表太大」，而是优化器选错了「驱动表（外层表）」——它把过滤后仍剩千万行的大表放外层，让内表被嵌套循环扫描了千万次。这类问题 EXPLAIN 的 rows 看着都「正常」，真正的破绽藏在 type 和 rows 的组合里。下面用一个真实案例讲清机制、定位与两种修法。

一、现象与背景：一条「两边都有索引」的 JOIN，P99 卡在 6.2s

去年给一套运营分析系统做慢查询治理。环境：MySQL 8.0.32、InnoDB、单实例 16C64G、innodb_buffer_pool_size=48G。两张核心表：

• t_log：行为日志表，6000 万行、单表 92GB，主键自增 id，二级索引 idx_date_channel(log_date, channel, user_id)；
• t_user：用户表，48 万行，主键 uid，二级索引 idx_tag(tag, uid)。

运营后台一个高频接口：查「某渠道、某标签用户、最近 7 天的日志明细」：

SELECT l.id, l.log_date, l.channel, u.uid, u.tag
FROM t_log l
JOIN t_user u ON u.uid = l.user_id
WHERE l.log_date >= '2024-11-01' AND l.log_date < '2024-11-08'
  AND l.channel = 'h5'
  AND u.tag = 'vip';

业务方反馈：P50 只有 40ms，但 P99 稳定在 6.2s，偶发 14s 超时。EXPLAIN 输出（节选）：

id  select_type  table  type  key               rows     Extra
1   SIMPLE       u      ALL   NULL             480000   Using where
1   SIMPLE       l      ref   idx_date_channel  3200000  Using index

注意两个诡异信号：驱动表是 t_user（48 万行，且 type=ALL 全表扫），而不是本该选择性更高的 t_log。看起来「小表驱动大表」挺合理，为什么反而慢？

二、原理与根因：嵌套循环 JOIN 的成本公式，和它为什么会选错

MySQL 8.0 的 JOIN 默认走 Nested-Loop Join（嵌套循环），成本模型是：

总成本 ≈ 驱动表扫描行数 × 内表单次查找成本

关键点：驱动表被完整扫一遍，内表被「查找」驱动表行数那么多次。所以驱动表应该是「过滤后返回行数最少、且能驱动内表走索引」的那张。

把这条 SQL 的真实过滤量算出来：

• t_user 侧：tag='vip' 过滤后约 5 万行（tag 是低基数，48 万行里 vip 占 ~10%）；
• t_log 侧：log_date 7 天 + channel='h5' 过滤后约 180 万行。

按真实数据，正确的驱动表应该是 t_user（5 万行 < 180 万行）——优化器选对了方向。但问题出在内表 t_log 的查找方式：type=ref 走了 idx_date_channel，单次查找 rows≈3200000。也就是说内表每一次查找都命中约 320 万行（因为 channel='h5' 在 7 天里分布极宽，ref 定位到的前缀选择性很差），5 万次 × 320 万行的「准全扫」= 十几亿行的等价工作量。

根因一句话：内表 t_log 的索引前缀 idx_date_channel 选择性太差，ref 查找退化成「每次 320 万行」，而驱动表 t_user 又是全表扫——两者相乘就是灾难。 这不是「谁当驱动」的单点问题，而是「驱动表行数 × 内表单次查找行数」这个乘积爆了。

更深一层：为什么 t_log 的 idx_date_channel(log_date, channel, user_id) 前缀选择性这么差？因为范围列 log_date 放在了等值列 channel 前面，联合索引的最左前缀先落在范围列上，ref 无法把 channel 也收进等值定位，选择性大打折扣。这是「联合索引列顺序」和「JOIN 驱动表选择」两个问题叠加的产物。

三、具体做法：三步降成本，P99 从 6.2s → 180ms

第 1 步：修联合索引的列顺序（等值在前、范围在后、JOIN 键收尾）

-- 旧索引：范围列在前，选择性差
ALTER TABLE t_log DROP INDEX idx_date_channel,
  ADD INDEX idx_channel_date_user (channel, log_date, user_id);

把等值列 channel 提到最前，channel='h5' 就能走 ref 精确等值定位，log_date 再在其后做范围扫描。改完后内表单次查找 rows 从 320 万降到约 26 万（7 天 h5 的真实量级，符合预期）。

第 2 步：给内表 JOIN 键补上 user_id，让查找落到覆盖索引

idx_channel_date_user 已含 user_id，EXPLAIN 的 Extra 从 Using index 稳定出现（覆盖索引，免回表）。这一步是第 1 步的自然结果，但单独验证过：若 JOIN 键 user_id 不在索引里，即使列顺序对了，每次查找仍要回表，P99 只能到 900ms 量级。

第 3 步：用 STRAIGHT_JOIN 锁死驱动表顺序，防止统计信息漂移再次选错

改完索引后验证：

EXPLAIN
SELECT STRAIGHT_JOIN l.id, l.log_date, l.channel, u.uid, u.tag
FROM t_user u
JOIN t_log l ON l.user_id = u.uid
WHERE l.log_date >= '2024-11-01' AND l.log_date < '2024-11-08'
  AND l.channel = 'h5' AND u.tag = 'vip';

结果：驱动表 u（type=ALL，rows≈48 万，但 tag 过滤后实际 5 万）→ 内表 l（type=ref，rows≈26 万，Using index）。P99 从 6.2s 降到 180ms，P50 从 40ms 降到 12ms。

说明：这里 t_user 仍是全表扫（48 万行），但因为它只是驱动表、被扫一次，成本可控；真正的杀手是内表被查找的次数。若想进一步优化，可给 t_user 加 idx_tag_uid(tag, uid) 让驱动表也走 ref，P99 还能再压到 60ms 量级。

第 4 步（补充，MySQL 8.0.17+）：给低基数列建直方图，从根上修 cardinality 估算

上面三步修完索引和顺序后，channel 和 tag 这两个低基数列（各自只有 5~10 个取值）的 cardinality 估算仍然可能不准——InnoDB 的索引统计信息默认只抽样 200 个 B+Tree 叶子页，对低基数列的分布估计偏差很大。MySQL 8.0.17 引入了 直方图（histogram） 机制，专门解决这个问题：

-- 给 channel 列建等宽直方图（256 桶）
ANALYZE TABLE t_log UPDATE HISTOGRAM ON channel;
-- 给 tag 列建等宽直方图
ANALYZE TABLE t_user UPDATE HISTOGRAM ON tag;

建完后用 SELECT JSON_PRETTY(SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, HISTOGRAM) 从 INFORMATION_SCHEMA.COLUMN_STATISTICS 查看直方图内容。优化器在估算 channel='h5' 的选择性时，会从直方图中精确读取该桶的占比（比如 h5 占 18.3%），而不是用 1/cardinality 的均摊值。

实测差异：本例中 channel 有 7 个取值，InnoDB 默认 cardinality 估算是 7（均摊），但实际 h5 只占 18.3%。均摊估算会让优化器以为 channel='h5' 过滤后剩 6000 万 / 7 ≈ 857 万行，而直方图告诉它实际是 6000 万 × 18.3% ≈ 1100 万行——直方图反而让估算更大了，这会让优化器更倾向选 t_user 当驱动表（因为 t_log 过滤后"看起来"更大了），恰好是正确方向。

注意：直方图是表级的，需要 ANALYZE TABLE ... UPDATE HISTOGRAM ON 手动触发，不会随 DML 自动更新。建议在凌晨批量灌数据后加一条定时任务刷新，否则直方图会过期、估算重新漂移。

四、踩过的坑与反直觉之处

1. 「小表驱动大表」不一定对。 直觉是「小表当外层」，但真正决定成本的是「驱动表过滤后行数 × 内表单次查找行数」。如果内表选择性差，小表驱动反而是灾难——本例就是。
2. EXPLAIN 的 rows 是估算，会骗你。 本例 rows 显示 l=3200000，看着「走了索引」，其实是「每次查找命中 320 万行」。rows 不是「这条 SQL 总共扫多少」，而是「内表单次查找的期望行数」。别把 type=ref 当成「索引很高效」，要看 rows 的绝对值。
3. 统计信息漂移会让优化器反复横跳。 我们后来发现，凌晨批量灌日志后，t_log 的 cardinality 估算偏了，优化器一度把 t_log 选成驱动表（方向反了），P99 又飙回秒级。修复靠 ANALYZE TABLE + 直方图刷新 + 必要时 STRAIGHT_JOIN 锁死顺序。这块和「执行计划漂移」是同一类病根，值得单独复盘。
4. 别一上来就 STRAIGHT_JOIN 硬怼。 它只是「锁顺序」，治标。若列顺序没修、内表选择性没提，锁死方向只会换个姿势慢。正确顺序是：先修索引列顺序（提选择性）→ 再建直方图（修估算）→ 最后锁驱动表顺序（防漂移）。
5. 直方图不是万能的，且有过期风险。 直方图只记录值的分布，不记录行级数据。如果 channel 的取值分布本身在变（比如新上了一个渠道），直方图不刷新就会给出错误的选择性估算。我们踩过的坑：新渠道 mini 上线后 3 天，channel='mini' 的选择性从直方图估算的 0%（没这个桶）变成实际的 12%，优化器给了一个极小的 rows 估计，把执行计划彻底带偏。教训：每次新增枚举值后，必须手动 ANALYZE TABLE ... UPDATE HISTOGRAM ON 刷新，不能依赖自动更新。

五、结论与可复用清单

• JOIN 慢先算乘积：驱动表过滤后行数 × 内表单次查找 rows，这个数才是真成本，别只看「谁大谁小」。
• 内表单次 rows 是核心指标：type=ref/range 但 rows 仍是百万级，说明索引前缀选择性差，优先修列顺序（等值在前、范围在后、JOIN 键收尾）。
• 联合索引列顺序口诀：等值列 → 范围列 → JOIN/排序收尾列。把范围列（如日期）放最前，会让等值列收不进 ref 定位。
• 覆盖索引是 JOIN 的隐藏收益：内表查找若含回表，成本翻倍；让 JOIN 键 + SELECT 列都进索引，Extra 出现 Using index。
• 低基数列建直方图（8.0.17+）：ANALYZE TABLE ... UPDATE HISTOGRAM ON，比 ANALYZE TABLE 的均摊估算精确得多；但需要手动刷新，新增枚举值后必须重建。
• STRAIGHT_JOIN 是防漂移的保险，不是提速的手段：在统计信息不稳的窗口期锁死正确顺序，但根治靠直方图 + 索引设计。
• 验证三板斧：改完索引后跑 EXPLAIN（看 rows 绝对值）→ EXPLAIN ANALYZE（看真实耗时与扫描行）→ 压测 P99（别信 P50）。

关联阅读：慢查询治理的正确顺序（先定位再动手）见陈砚舟《慢查询治理三件套》；「执行计划漂移」的统计信息根因见陈寒山《同一索引同一 SQL，凌晨 P99 飙到 3.4s》。