MySQL多表关联慢查询优化：从20秒到3.6秒的NLJ实战

最近帮某开发者处理了一条 MySQL 多表关联的慢查询场景很典型后台订单报表四张表关联订单量 200 万出头查一次要 20 多秒。第一反应是加索引加上之后降到 7 秒离目标还远。后面把重心放到 NLJNested Loop Join嵌套循环连接上调整驱动表、JOIN 顺序配合索引重写 SQL同一台机器上稳定跑到 3.6 秒左右。这篇就把整个排查和优化过程拆开讲包括 NLJ 的工作机制、为什么它是多表关联最高效的底座、怎么把“小表驱动大表”真正落地以及哪些场景下别盲目迷信 NLJ。1. 慢查询20秒的真相多表关联到底慢在哪1.1 一条典型到不能更典型的慢查询某后台的订单报表需求运营人员查询某个时间段内的订单需要展示下单用户、商品名称、所属门店按时间倒序默认只看第一页 100 条。表结构大致是orders、users、products、stores四张表关联字段分别为user_id、product_id、store_id。当时的量级是这样的orders约 215 万行users约 48 万行products约 12 万行stores约 6000 行。SQL 写法看起来很“标准”SELECT o.order_id, u.user_name, p.product_name, s.store_name, o.amount, o.order_time FROM orders o LEFT JOIN users u ON o.user_id u.id LEFT JOIN products p ON o.product_id p.id LEFT JOIN stores s ON o.store_id s.id WHERE o.order_time 2024-01-01 ORDER BY o.order_time DESC LIMIT 100;结果就是慢第一次跑 21.8 秒之后走了缓存也要 18 秒左右。慢查询日志里几乎每分钟都会出现一次数据库 CPU 直接被打到接近 50%。这类 SQL 我见过太多问题几乎全部集中在关联策略上。MySQL 处理多表关联的默认方式就是嵌套循环连接也就是 NLJ。它会先选一张驱动表取出每一行然后去另一张表里找匹配记录循环往复。慢就慢在驱动表选错或者被关联的表没有可用索引时内层循环退化成扫全表。1.2 驱动表失控一张全表扫描引发的连锁反应执行计划EXPLAIN一跑四个表全显示typeALLExtra 里还有Using temporary; Using filesort。这里要先解释下 MySQL 当时实际做了什么。优化器把orders当成了驱动表先做全表扫描扫出 215 万行然后挨个去users、products、stores里匹配数据。这三个表当时在关联列上都没有索引所以内层也就是全表扫描。这带来什么后果在最坏情况下理论比较次数接近 215 万 × 48 万 × 12 万 × 6000。虽然 MySQL 有 Join Buffer 和块嵌套循环BNL兜底不至于真的做天文数字级的比较但整个关联过程还是要把能找到的数据轮询一遍。线下讲案例时经常有人问明明只要取 100 条为什么不能直接出结果因为在 NLJ 的模型里MySQL 得先把满足条件的所有关联行拼出来再排序再截取前 100 条。数据量一大这套流程的代价根本不是“取 100 条”的代价而是“所有匹配行”的代价。1.3 临时表和文件排序被忽略的第二重伤害原始 SQL 里还有一个ORDER BY o.order_time DESC。多表关联之后如果排序列没有出现在驱动表可以直接使用的索引中MySQL 会把关联结果先放进临时表再在临时表上做文件排序。这一步非常耗资源。215 万行关联完成后写临时表、排序、再取前 100 条内存、磁盘 IO 都在烧。更无奈的是排序这步和关联本身是分开算的——就算你在关联列上加了索引让内层查询变快了最后那批临时表排序的代价依然存在。这也是为什么“只加索引、不改 SQL”往往只能解决一半问题的原因。所以当时我给的第一版优化建议就是先看执行计划再动 SQL而不是急着堆索引。2. NLJ的工作机制为什么嵌套循环反而是最高效的选择2.1 嵌套循环连接的三个动作拆解NLJ 本质上就是两重循环用一个很粗糙的伪代码表示for each row r in driving_table: -- 外层驱动表 for each matched row s in inner_table: -- 内层被驱动表 output r s关键点有两个。第一外层循环的驱动行数量要尽量小第二内层循环必须能走索引。否则每次匹配都是全表扫描一次查询等于把被驱动表翻了几万遍。很多人一听“嵌套循环”四个字就认为它慢其实在生产环境里我们说的 NLJ 绝大多数是 INLJIndex Nested-Loop Join索引嵌套循环连接也就是内层通过索引查找的版本。只有完全没有索引可用时才退化成 SNLJSimple Nested-Loop Join那才是真正的每行全表扫。2.2 为什么“小表驱动大表”不是传说是数学嵌套循环模型的效率其实很好估算总成本 ≈ 驱动表扫描行数 × 每次内层索引查找的代价。举个例子假设驱动表经过条件过滤后剩下 1 万行内层表每次通过主键查询耗时为 0.1 毫秒到 1 毫秒那么总耗时就是 1 万次索引查询大概率在 1 秒内完成。反过来如果让 200 万行的表做驱动表先把 200 万行扫一遍再让内层表做 200 万次查询或全表扫描结果会差好几个数量级。这里要说个容易踩的坑“小表驱动大表”并不是简单地看表的行数而是要看两张表经过 WHERE 条件过滤之后到底各剩多少行。这个值在优化器里叫结果集基数cardinality。如果一个大表经过条件过滤后只剩几百行它照样是适合做驱动表的“小表”。2.3 被误解的“最高效”NLJ 不丢人有段时间网上很多文章推崇 HASH JOIN搞得好像 NLJ 是落后方案一样。实际在 OLTP 场景里NLJ 反而是更合适的选择。原因很简单INLJ 可以逐行返回结果第一行数据能很快拿回来适合分页、短查询。HASH JOIN 需要先把一侧的数据全部读进内存构建哈希表才能开始匹配内存占用明显更高。在关联行数可控、索引能命中的前提下INLJ 的响应时间通常优于 HASH JOIN。所以结论是NLJ 不是最高效的查询方式准确说法应该是“NLJ 配上合适的索引和驱动表才是绝大多数多表关联场景下最高效的方案”。这个前提一旦不满足再高级的循环也是白搭。3. 性能翻5倍的改写实操驱动表、索引与JOIN顺序的调配3.1 先想业务语义哪些表必须留哪些关联可以换优化慢查询前我先没急着写新 SQL而是把这句话的业务意图捋了一遍。订单表是主表时间过滤条件也在订单表上用户、商品、门店本质上是对维度信息的补充。原始 SQL 用LEFT JOIN含义是“即使订单缺少用户、商品或门店信息也要保留订单记录”。但实际业务系统里user_id、product_id、store_id都是必填字段都有值。换句话说改成INNER JOIN不会丢任何一行数据却能让优化器有更多选择空间。紧接着把订单表的筛选和排序提前到子查询里先尽可能缩小驱动结果集。改写后的 SQL 长这样SELECT o.order_id, u.user_name, p.product_name, s.store_name, o.amount, o.order_time FROM ( SELECT id, user_id, product_id, store_id, amount, order_time FROM orders WHERE order_time 2024-01-01 ORDER BY order_time DESC LIMIT 100 ) o JOIN users u ON o.user_id u.id JOIN products p ON o.product_id p.id JOIN stores s ON o.store_id s.id ORDER BY o.order_time DESC;这里有个细节必须说清楚把LIMIT 100下推到子查询里是否安全在这个场景下安全因为订单表和另外三张维度表都是多对一或一对一关系关联不会改变订单行的数量。但如果某个关联是一对多比如商品和商品标签提前 LIMIT 就可能错失真正需要展示的数据。所以不要照抄这个写法要先确认关联基数。3.2 索引落地过滤列、关联列、覆盖索引的优先级SQL 改写完之后索引才登场。按照收益从高到低排序orders.order_time必须建索引。它是过滤条件也是排序字段子查询要靠着它快速定位时间段内的订单。users.id、products.id、stores.id本身是主键天然有索引可以直接用。对orders表我额外建了一个覆盖索引(order_time, user_id, product_id, store_id, amount)。这样子查询里从索引就能拿到全部需要字段不需要回表。ALTER TABLE orders ADD INDEX idx_order_time (order_time); ALTER TABLE orders ADD INDEX idx_order_report (order_time, user_id, product_id, store_id, amount);这个覆盖索引会不会拖慢写入会有一点毕竟索引本身也要维护。但为了一个高频报表查询建专用索引性价比完全划算。如果你确认orders表有大量并发写入可以先只建order_time索引再看效果一步步加。3.3 每一步改动的理由对照很多读者喜欢直接要“优化前后差异”我把改动点整理成了表格方便逐项对照改动点原始写法优化写法为什么这样改关联类型LEFT JOININNER JOIN业务字段必填结果集不变给优化器更多选择驱动表orders 全表子查询先过滤排序驱动表基数从 215 万降到几千NLJ 成本大降排序位置最后统一排序子查询内先按时间排序让 LIMIT 可以下推避免关联后大临时表排序LIMIT 位置关联结束后截取子查询内提前截取少算大量无用关联行前提是关联不改变行数关联列索引无主键 order_time 覆盖索引内层查询从 ALL 变为 ref/eq_ref避免回表返回字段SELECT * 风格明确列出需要的列减少临时表和网络传输覆盖索引才有意义4. 同一份数据的三组实测不同关联写法的执行计划与耗时对比4.1 测试环境与数据构造为了让结论可复现我把当时的环境和数据说清楚。测试环境是 MySQL 8.08 核 16G 内存SSD关闭查询缓存每轮查询跑 10 次取平均值。数据规模orders215 万行users48 万行products12 万行stores6000 行。订单表里 2024-01-01 之后的数据约有 8000 行整体数据分布比较均匀没有严重倾斜。4.2 三种写法的执行计划与耗时我分别测了三种状态结果差异非常大写法执行计划关键特征平均耗时写法A原始三 LEFT JOIN关联列无索引驱动表 orders 全表扫描 ALL临时表 文件排序21.8 秒写法B原始 SQL 关联列索引驱动表仍 orders 全表扫描内层变 ref临时表 文件排序仍在7.2 秒写法C子查询提前过滤 INNER JOIN 覆盖索引驱动表为 8000 行左右的子查询结果typerange内层 eq_ref无临时表无文件排序3.6 秒从 21.8 秒到 3.6 秒提升大约 6 倍。标题里说的“翻 5 倍”是保守口径因为不同数据分布下结果会有浮动但只要索引和驱动表方向对了5 倍以上是很稳的。4.3 EXPLAIN 的读法别只看 type还要看 rows 和 Extra这个 case 最有价值的地方是执行计划的变化我建议每个人优化前都把 EXPLAIN 当体检报告。type字段写法A 全是ALL写法C 驱动表是range内层查询变成eq_ref或ref。从全表扫描到索引范围扫描质的区别。rows字段写法A 估算扫描 215 万行写法C 驱动表估算只有 8000 行。注意这是估算值不是精确值但量级已经说明问题。Extra字段写法A、B 都出现Using temporary; Using filesort写法C 这两项完全消失。消失意味着没有大临时表、没有磁盘排序。possible_keys和key要看有没有生效。有时候建了索引但 SQL 写法导致索引用不上key显示 NULL这时候就要检查是否发生了隐式类型转换或字符集不匹配。MySQL 8.0 里还可以用EXPLAIN ANALYZE看实际执行时间和每步返回行数。我当时的做法是先EXPLAIN快速判断方向再用EXPLAIN ANALYZE确认瓶颈在哪个环节。5. NLJ不是万能药这些场景下别盲目套用5.1 大表等值关联无索引优化器会自己换成 HASH JOIN 或 BNLNLJ 也有明显不适用的时候。比如两个大表做等值关联关联列两边都没有索引。这时候如果强行 NLJ内层每匹配一行都要全表扫一遍代价基本是灾难级的。MySQL 8.0.18 开始引入了 HASH JOIN遇到无法走索引的等值关联时优化器可能会选择 HASH 方式先把小表构建成哈希表再让大表去匹配。5.7 里则是用 BNL 的 Join Buffer 批量缓存。这时正确的思路不是“禁用 HASH JOIN 强制 NLJ”而是先评估能否加索引让 NLJ 走上索引路。如果两个表都非常大加索引也盖不住量级那就该考虑从架构层面解决比如预聚合、宽表、汇总表。总之不要一听 NLJ 高效就把所有 JOIN 都往 NLJ 上套。5.2 深分页、多对多、隐式转换NLJ 失效的常见现场有三个场景我经常提醒人注意。深分页。NLJ 在执行LIMIT 200000, 100时不会聪明到只算最后那 100 条。它要把前面 200000 条也关联出来再扔掉。深分页场景即使索引全部到位性能照样骤降更适合用游标、书签等方式改写。多对多关联。比如商品表和标签表关联一个商品多个标签关联后行数会膨胀。如果把LIMIT下推到子查询里可能出现第 1 个商品占掉多个分页位置的情况结果不准。这种场景要先做聚合或选择合适的驱动表而不是直接照搬“提前 LIMIT”的写法。隐式类型转换和字符集不匹配。varchar类型的关联列和int类型的关联列比较时MySQL 会做隐式转换导致索引失效。两张表关联列字符集不同比如一张是utf8mb4另一张是utf8mb4_general_ci也会出现类似问题。这就是为什么建表规范里反复强调关联字段类型、字符集必须保持一致。5.3 几个可复用的排查习惯优化多表关联这件事与其背结论不如养成一套排查习惯。我现在的固定流程是这样的拿到一条慢查询先单独跑EXPLAIN不看耗时只看执行计划。确认驱动表是哪个当前驱动表估算行数多少内层查询的type是什么。如果内层是ALL优先考虑给关联列建索引这是 NLJ 能高效运行的前提。如果驱动表行数太大看 WHERE 条件能否下推能否用子查询先缩小结果集。再看Extra里有没有Using temporary和Using filesort有的话处理排序字段。最后压测的时候用线上真实数据分布验证不要只造一个很小的测试库。最后再说点个人感受。优化多表关联很多时候不需要高深算法就是把 NLJ 的“路况”修好索引在、驱动表小、临时表消失性能自然就上来了。后来我在另一个统计类项目里用同样的思路处理一张五表关联的报表从 35 秒压到了 4 秒左右全程没换数据库也没上中间件。如果你手上的多表查询也慢得离谱建议按这个顺序试一遍先 EXPLAIN 看执行计划确认驱动表补齐关联列索引再把过滤和排序尽量下推到子查询。大概率会和我这次一样收获远超预期。