大表JOIN大表无法单靠加索引解决，必须分层干预：先选对算法（Nested Loop或Hash Join），再压减参与JOIN的数据量，最后用窗口函数或物化中间结果兜底。
大表 JOIN 大表无法靠“加索引”一招鲜解决，必须分层干预：先让数据库选对算法（优先 Nested Loop Join 或 Hash Join），再压减参与 JOIN 的数据量，最后兜底用窗口函数或物化中间结果。硬写 SELECT * FROM big1 JOIN big2 ON ... 几乎必然触发 Using temporary; Using filesort 或 Block Nested Loop，查询秒变分钟级。
为什么EXPLAIN里总看到“Using join buffer (BNL)”
这不是警告，是判决书——说明被驱动表的 ON 字段没走索引，优化器被迫放弃 Index Nested-Loop Join，改用内存换时间的块嵌套循环。此时计算量 = 驱动表行数 × 被驱动表行数，千万级 × 千万级直接破百亿次比对。
- 确认被驱动表的关联字段是否为独立索引：
orders.user_id不能只依赖INDEX(user_id, created_at)，除非查询也带created_at条件；否则必须建单列索引INDEX(user_id) - 检查字段类型是否严格一致：
INT和BIGINT、VARCHAR(32)和VARCHAR(64)关联会隐式转换，索引失效 - MySQL 8.0+ 可开
mrr_cost_based=off启用 MRR 优化，缓解 BNL 的随机 IO 压力，但治标不治本
小表驱动大表在大表JOIN中还成立吗
成立，但前提是你能定义出哪个是“小表”。真实场景中，两张都是 TB 级，所谓“小”是相对的——比如按时间过滤后，WHERE date >= '2026-07-01' 可能把 10 亿订单压到 500 万行，它就变成事实上的驱动表。
- 永远把
WHERE条件前置到 JOIN 之前：不要写FROM big1 JOIN big2 ON ... WHERE big1.status = 'done'，而要写FROM (SELECT * FROM big1 WHERE status = 'done') AS filtered1 JOIN big2 ON ... - 用
STRAIGHT_JOIN强制顺序仅当EXPLAIN FORMAT=TREE明确显示优化器选错了驱动表时才用，否则干扰统计信息更新 - 分区表可成为天然“小表”：若
big1按date分区，big2按prov_id分区，且业务只查某省某天，则只需加载对应分区，物理数据量骤降
窗口函数能绕过大表JOIN的性能瓶颈吗
不能绕过 JOIN 本身，但能绕过 JOIN 后的全量聚合/排序。典型场景如“每个省最新一笔订单”，传统写法是子查询 + 自 JOIN，导致两次扫描大表；用 ROW_NUMBER() OVER (PARTITION BY prov_id ORDER BY date DESC, id DESC) 则在 JOIN 结果流上实时编号，外层 WHERE rn = 1 即可。
- 必须确保
PARTITION BY字段（如prov_id）在 JOIN 后有索引，否则窗口内部排序仍会全局排序 -
ORDER BY字段存在大量重复值时（如相同date），务必补主键或唯一字段，否则ROW_NUMBER()结果不可复现 - MySQL 8.0+ 支持，但语法敏感：推荐显式定义
WINDOW w AS (PARTITION BY prov_id ORDER BY date DESC)，避免嵌套OVER写法报错
真正卡住性能的往往不是 JOIN 动作本身，而是 JOIN 后的 GROUP BY、ORDER BY、LIMIT 这些操作被迫处理全量中间结果。所以优化锚点不在 ON 条件怎么写，而在“哪些数据必须参与 JOIN”——过滤越早、分区越细、窗口越准，中间集就越薄。
