那天下午一条已经跑了两年多的后台报表 SQL 突然从 2 秒多涨到 38 秒业务方连续打了几次电话。把语句捞出来执行EXPLAIN一切看着都正常——索引都在没有全表扫type列全是ref和eq_ref。真正变的是第三行table列里两张表的顺序和上个月完全相反。优化器自己换了驱动表而且换错了。那一刻能救急的手段不多MySQL 的STRAIGHT_JOIN是最直接的那一个你告诉优化器别算了就按我写的顺序连它照做语句立刻回到 2 秒级。STRAIGHT_JOIN这个东西说穿了不复杂但在实际项目里用不好会很难看。它不是索引不是缓存也不改变任何一行的结果集它只是把先读哪张表、后读哪张表这个决定权从优化器的成本模型手里夺回来交给你。这篇文章想聊清楚三件事优化器凭什么替你做这个决定、它什么时候会算错、以及你凭什么相信自己写的顺序比它算出来的更对。全文围绕驱动表、成本估算、EXPLAIN验证、OPTIMIZER_TRACE排查、JOIN_ORDER提示这几条主线展开给到可以直接复现的建表脚本、造数脚本和对照实验。适合已经会写多表JOIN、但被慢查询折腾过的后端和 DBA 读者如果你是刚学 SQL 的新手前面的原理部分也能看懂只是案例里的数字需要你自己跑一遍才更有感觉。1. STRAIGHT_JOIN 不是语法糖它到底把什么决定权收了回来1.1 一条多表 JOINMySQL 内部在替你算什么写一条SELECT ... FROM a JOIN b JOIN c的时候你脑子里的执行顺序大概是从左往右。MySQL 不是。它把FROM后面这几张表当成一个候选集合然后用一套基于成本的搜索算法去枚举连接顺序挑一条它认为总代价最低的路。你写的书写顺序对它来说只是一个初始排列几乎不影响最终选择。这套搜索算法并不做全排列枚举。表一多排列组合是阶乘级爆炸10 张表就有 360 多万种顺序。MySQL 采用的是贪心式的深度优先搜索配上optimizer_search_depth这个参数控制搜索深度默认 62基本等于不限制。它会先估算每张表单独作为驱动表时的行数和代价选一个起点然后逐步把新表加进当前前缀每次加表都重新估算前缀结果集的大小和累计代价最终留下一条它认为最便宜的路。关键在于两个词估算和代价。估算靠的是统计信息——每张表的行数、索引基数、选择性代价靠的是一套内部公式把随机读页、顺序读页、行比较、临时表写入这些动作折算成成本单位。两样东西都不是真实执行数据全是基于采样的推断。推断可以很准也可以错得离谱。STRAIGHT_JOIN存在的全部理由就是当推断错了的时候你能手动纠偏。我在一个日志分析项目里见过一次特别典型的估算失真一张日增百万行的行为表user_id上建了普通索引索引基数显示 800 万但表里实际有 4000 万行。原因很简单InnoDB 的统计信息默认是采样出来的innodb_stats_persistent_sample_pages默认只抽 20 个页。数据分布特别不均匀的时候20 个页根本代表不了全表估算行数能差出五六倍。这种偏差一旦渗进多表JOIN的成本计算优化器选错顺序是家常便饭。1.2 两种 STRAIGHT_JOIN 写法作用范围不一样STRAIGHT_JOIN在 MySQL 里出现了两次位置不同作用域不同很多人混着用。第一种是写在SELECT关键字后面当作修饰符SELECT STRAIGHT_JOIN o.order_no, u.mobile FROM t_order o JOIN t_user u ON o.user_id u.id WHERE o.create_time 2024-05-01 AND u.status 1;这种写法作用于整条语句里所有的JOIN效果是强制所有表按FROM子句里出现的先后顺序连接第一个出现的表就是驱动表。第二种是写在FROM子句里当连接类型用SELECT o.order_no, u.mobile FROM t_order o STRAIGHT_JOIN t_user u ON o.user_id u.id WHERE o.create_time 2024-05-01 AND u.status 1;这种写法只作用于这一个JOINSTRAIGHT_JOIN在这里同时承担了INNER JOIN的语义和一个顺序约束。这两张表之间等价于内连接并且左边必须先读。两种写法的最终效果在单层场景下一样但在多表语句里差别明显。SELECT STRAIGHT_JOIN是全局收紧FROM里的写法是局部收紧。我一般推荐第二种理由很实在局部写法只影响一处出问题时排查范围小全局写法像是在整条语句外面套了一层你别自己想了的罩子后续如果需求变化加了一张表很容易忘了这里还锁着顺序。提示STRAIGHT_JOIN和STRAIGHT JOIN是同一个东西中间有没有下划线都行。但官方文档统一写成带下划线的形式团队里最好统一一种写法省得代码评审时互相较劲。1.3 它没有禁用优化器别把它当成万能开关一个特别常见的误解以为加上STRAIGHT_JOIN就是把优化器关掉了。不是。优化器依然在干活它照样会对每张表决定走哪个索引key列那一套逻辑不变决定用哪种连接算法Nested Loop、Block Nested Loop、Hash Join决定是否下推条件、是否物化派生表、是否用覆盖索引对单表上的WHERE条件做条件重排和索引选择。它唯一不做的是换表顺序。这一点很重要因为这意味着STRAIGHT_JOIN救不了索引缺失的问题。如果你把顺序锁对了但被驱动表上连接列没有索引那还是Block Nested Loop还是慢。顺序和索引是两件独立的事顺序只决定探多少次索引决定每次探得多贵。两者都对了才快。还有一层要注意STRAIGHT_JOIN在FROM里出现时会打断某些外连接的语义处理。比如FROM a LEFT JOIN b ... STRAIGHT_JOIN c ...后面这个STRAIGHT_JOIN实际当内连接用c不会被当成外连接的一部分。混合外连接和内连接的语句写之前一定先想清楚哪些条件放在ON里、哪些放在WHERE里否则结果集会悄悄变少而你以为是STRAIGHT_JOIN的锅。2. 优化器把驱动表选反的几类高频场景2.1 统计信息过期与采样偏差InnoDB 的统计信息有一套自己的更新策略。表的修改行数超过大约 10%或者执行了ANALYZE TABLE统计信息才会刷新。中间这段时间里优化器手里拿的是旧数据。表在快速增长的时候旧数据的偏差会一天比一天大。采样的影响更隐蔽。innodb_stats_persistent打开默认就是打开的的时候统计信息会持久化到mysql.innodb_table_stats和innodb_index_stats两张系统表里重启不丢。但采样的页数由innodb_stats_persistent_sample_pages控制默认 20 页。20 个页对一张均匀分布的大表够用对一张有明显热点的表就不够看。我见过一张用户行为表event_type字段有 30 多个取值其中 3 个占了 85% 的数据量索引基数显示的分布和真实分布差了三倍。优化器用这个基数去估算某类事件过滤后还剩多少行结果多估了 20 倍。估算行数多估了优化器就会觉得这张表过滤完还剩好多行不能当驱动表于是换了一张它以为过滤得干净的。换完之后每行 probe 下去可能碰上大表性能就拐了。处理这类问题最基础的动作是定期跑ANALYZE TABLE尤其是那些增量写入大、数据分布不匀的表。为什么要定期跑是因为统计信息是代价模型的输入输入错了后面所有的计算都建立在流沙上。这个动作成本不高一张千万行表几十秒到几分钟放到低峰期跑就行。ANALYZE TABLE t_order, t_user, t_shop;跑完之后再看EXPLAIN很多时候执行计划自己就正过来了根本用不上STRAIGHT_JOIN。这是很重要的一条经验先用ANALYZE排除统计问题再考虑手动锁顺序。不然你把错误的顺序写死在 SQL 里下次表数据变了你连它本来会怎么选都不知道了。2.2 多条件独立假设导致的严重低估优化器在估算多个WHERE条同时作用时的过滤率默认假设各字段相互独立。意思是如果city_id 12的选择率是 5%status 2的选择率是 10%它就认为两个条件同时成立的概率是 0.5%。这个假设在很多业务场景下错得离谱因为字段之间往往强相关——比如city_id和region_id天然是一对多的关系status和pay_time在业务上也有先后因果。举个具体例子。表里 100 万行province A有 30 万行30%city A-01有 10 万行10%。在业务上city A-01本身就已经蕴含了province A但优化器不知道它会按 30% × 10% 3% 来估估出 3 万行。真实值是 10 万行低估了 3 倍多。低估之后会发生什么优化器认为这张表过滤完只剩很少那它当驱动表最划算于是把它排到前面。但实际执行时这张表过滤完有 10 万行每行都要去被驱动表探一次被驱动表如果连接列上没有合适的索引探一次就是一次全表范围的查找。本来应该小表探大表的 5 万次操作变成了大表探小表的 10 万次带全表扫的操作量级完全不同。判断自己是不是踩了独立假设的坑最快的办法是用EXPLAIN看rows列再跟实际COUNT(*)对一下。SELECT COUNT(*) FROM t_order WHERE province A AND city A-01; EXPLAIN SELECT * FROM t_order WHERE province A AND city A-01;如果EXPLAIN的rows和COUNT(*)差三倍以上基本就是独立假设或者统计信息的问题。前者可以靠直方图缓解MySQL 8.0 支持ANALYZE TABLE ... UPDATE HISTOGRAM后者靠ANALYZE TABLE。2.3 派生表、子查询物化之后统计信息断档派生表FROM子句里的子查询在 MySQL 5.7 之后会被物化5.7 之前主要是临时表。物化之后优化器对派生表内部的数据分布基本上一无所知它只能按子查询里单表估算出来的行数去参与外层的连接顺序决策。这带来一个相当常见的问题子查询本身过滤得很干净你心想这不就是个小表吗外连接拿它驱动肯定对但优化器不这么认为。它看子查询的估算行数是 80 万而外层另一张表估算 2 万于是把外层那张表排前面去了。等真正跑起来子查询物化出来的临时表其实只有 5000 行最优解反过来了。SELECT STRAIGHT_JOIN u.mobile, x.total FROM t_user u JOIN ( SELECT user_id, SUM(amount) AS total FROM t_order WHERE create_time 2024-05-01 GROUP BY user_id ) x ON x.user_id u.id;上面这个写法里派生表x是第一张表STRAIGHT_JOIN把它强制成驱动表。之所以值得这么做是因为派生表经过GROUP BY之后行数通常很少让它驱动t_user的主键eq_ref查找一共就探那么几千次。反过来如果让t_user驱动每次都要在物化临时表里扫那就没有索引可用了。要确认派生表物化之后的真实行数可以用EXPLAIN FORMATTREE或者EXPLAIN ANALYZE8.0.18看实际返回行数。EXPLAIN ANALYZE SELECT u.mobile, x.total FROM t_user u JOIN ( SELECT user_id, SUM(amount) AS total FROM t_order WHERE create_time 2024-05-01 GROUP BY user_id ) x ON x.user_id u.id;EXPLAIN ANALYZE会真的执行一次注意别在生产上跑没加LIMIT的聚合输出的每一行都带上actual rowsN和估算的rows并排显示。两者差得越多STRAIGHT_JOIN越有存在的价值。2.4 唯一索引和普通索引的估算方式不一致还有一个容易被忽略的点优化器对唯一索引等值查找和普通索引等值查找的代价估算完全不同。唯一索引等值查到 0 或 1 行优化器直接按 1 行算几乎不需要统计信息普通索引等值查找就要看基数基数越小选择性越差估算的行数越多。这个差异在多表连接里会被放大。表 A 的连接列是主键被驱动时的eq_ref成本极低表 B 的连接列是普通索引被驱动时是ref成本高一个档次。优化器会因为成本差异把 B 排到前面当驱动表但它没考虑到 B 作为驱动表时过滤后可能还有一大堆行每行再去 A 里探主键总次数反而更多。一个现场订单表t_order和用户表t_user按user_id连接t_order.user_id上是普通索引t_user.id是主键。-- 优化器可能选择的顺序 EXPLAIN SELECT o.order_no, u.mobile FROM t_user u JOIN t_order o ON o.user_id u.id WHERE u.level 3;如果u.level 3过滤后还有几十万行而t_order.user_id的普通索引基数很低很多用户只有一两单优化器会低估每行探下去的成本把t_user当成驱动表。实际上按时间范围把t_order先砍到几千行再探t_user主键成本低得多。这就是典型的索引类型差异误导顺序选择。3. 搭一个可复现的翻车现场从建表到 EXPLAIN 对照3.1 造三张表造出真实的偏差光讲原理不够得有个能自己跑一遍的场景。下面这套练手用的表结构在 MySQL 8.0 上验证过数据是自己灌的跟任何真实业务无关。CREATE DATABASE IF NOT EXISTS demo_sj DEFAULT CHARSET utf8mb4; USE demo_sj; -- 店铺表2 万行category_id 只有 20 个取值 CREATE TABLE t_shop ( id INT PRIMARY KEY AUTO_INCREMENT, category_id INT NOT NULL, shop_name VARCHAR(64) NOT NULL, KEY idx_category (category_id) ) ENGINEInnoDB; -- 商品表约 300 万行每个店铺平均 150 个商品 CREATE TABLE t_goods ( id INT PRIMARY KEY AUTO_INCREMENT, shop_id INT NOT NULL, price DECIMAL(10,2) NOT NULL, on_sale TINYINT NOT NULL DEFAULT 1, KEY idx_shop (shop_id), KEY idx_on_sale (on_sale) ) ENGINEInnoDB; -- 订单明细表约 8000 万行 CREATE TABLE t_order_item ( id BIGINT PRIMARY KEY AUTO_INCREMENT, goods_id INT NOT NULL, qty INT NOT NULL, amount DECIMAL(10,2) NOT NULL, KEY idx_goods (goods_id) ) ENGINEInnoDB;造数用个笨办法写个储过程循环插或者用脚本生成INSERT批量灌。下面是一个简化的灌数思路实际练手时把循环次数按机器能力调小一点几十万行也能看出效果。DELIMITER $$ CREATE PROCEDURE p_load() BEGIN DECLARE i INT DEFAULT 1; WHILE i 20000 DO INSERT INTO t_shop (category_id, shop_name) VALUES (i MOD 20 1, CONCAT(shop_, i)); SET i i 1; END WHILE; SET i 1; WHILE i 3000000 DO INSERT INTO t_goods (shop_id, price, on_sale) VALUES (i MOD 20000 1, ROUND(RAND()*500, 2), RAND() 0.85); SET i i 1; IF i % 50000 0 THEN COMMIT; END IF; END WHILE; SET i 1; WHILE i 2000000 DO INSERT INTO t_order_item (goods_id, qty, amount) VALUES (i MOD 3000000 1, 1 (i MOD 5), ROUND(RAND()*300, 2)); SET i i 1; IF i % 50000 0 THEN COMMIT; END IF; END WHILE; END$$ DELIMITER ; CALL p_load(); ANALYZE TABLE t_shop, t_goods, t_order_item;这段循环逻辑写起来不难但跑起来会比你想象的久。千万级数据在普通 SSD 上灌进去也得十几分钟。我一般习惯把表结构和造数脚本缝在一起灌完之后立刻跑一遍ANALYZE让统计信息跟上避免后面实验时把统计问题和顺序问题混在一起看。3.2 用 EXPLAIN 对比优化器自己选和STRAIGHT_JOIN 强制实验用的查询是统计特定几个类目下、在售商品的成交额。类目选 3 个on_sale 1是硬性条件。-- 版本一让优化器自己决定 EXPLAIN SELECT SUM(oi.amount) FROM t_order_item oi JOIN t_goods g ON oi.goods_id g.id JOIN t_shop s ON g.shop_id s.id WHERE s.category_id IN (3, 7, 11) AND g.on_sale 1;跑出来你会看到三行的table顺序。在t_order_item数据量最大、t_shop数据量最小的情况下很多环境里优化器会把t_shop或t_goods排前面因为它们的总行数少。但如果s.category_id IN (3,7,11)过滤之后还有 3000 家店g.on_sale 1过滤之后还有 255 万件在售商品那么从t_goods驱动的成本其实非常高——255 万次去t_shop里探主键再 255 万次过滤后剩的去t_order_item里探idx_goods再加回表。-- 版本二强制从 t_shop 开始以小驱大 SELECT STRAIGHT_JOIN SUM(oi.amount) FROM t_shop s JOIN t_goods g ON g.shop_id s.id JOIN t_order_item oi ON oi.goods_id g.id WHERE s.category_id IN (3, 7, 11) AND g.on_sale 1;版本二故意把t_shop放到第一位。逻辑上先从 2 万条里砍到 3000 条3000 次探idx_shop拿到 45 万件商品过滤on_sale剩下约 36 万件36 万次探idx_goods拿到明细再聚合。整个过程的最大扫描量是 36 万次索引查找 一次聚合可控。而版本一在某些环境里会从t_goods的idx_on_sale扫描起步。on_sale只有 0 和 1 两个值选择性极差等值扫描要回表 255 万次然后再去探t_shop和t_order_item。EXPLAIN里的rows可能显示几百万filtered显示很小看起来过滤得很好但真实成本都压在回表上。具体用哪种写法不是死板的关键在于你要能拿EXPLAIN前后对比出rows和Extra列的变化。真正该看的指标是观察项优化器自选STRAIGHT_JOIN 后结论第一张表驱动t_goodst_shop从 300 万降到 2 万驱动表过滤后行数255 万3000数量级差异被驱动表访问类型ref / eq_refref / eq_ref都还好是否存在 Using join buffer可能无有 join buffer 说明被驱动表没索引要警惕是否有 Using temporary有无或减少临时表必然伴随排序开销最后一行Using join buffer (hash join)要特别留意。MySQL 8.0.18 之后引入 Hash Join在等值连接且被驱动表没有可用索引时会比 Block Nested Loop 快很多但仍然远不如索引驱动。看到这条的时候第一个该问的问题不是我要不要加STRAIGHT_JOIN而是被驱动表的连接列为什么没索引。3.3 用 OPTIMIZER_TRACE 看优化器的账本EXPLAIN只给你结果不给你理由。想知道优化器为什么选了这个顺序得把它的内部记录翻出来。开optimizer_trace属于会话级操作对生产影响可控记得开了之后马上关。SET SESSION optimizer_trace enabledon; SET SESSION optimizer_trace_max_mem_size 1048576; SELECT SUM(oi.amount) FROM t_order_item oi JOIN t_goods g ON oi.goods_id g.id JOIN t_shop s ON g.shop_id s.id WHERE s.category_id IN (3, 7, 11) AND g.on_sale 1; SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G SET SESSION optimizer_trace enabledoff;TRACE输出是一大段 JSON关键看两个节点join_optimization→considered_execution_plans优化器列出了它考虑过的所有顺序每一条都带cost_inforows_estimation每张表每个索引访问路径的预估行数误差一般出在这里。我印象最深的一次排查是某条查询里t_goods的rows_estimation显示idx_shop的预估行数是 150 万而那条 SQL 单独跑COUNT(*)却只有 45 万。差了 3 倍多。再翻回available_statistics看到idx_shop的基数是 2 万采样页数是 20。就是这个采样页数太低导致估算被抬高。走ANALYZE TABLE之后重跑计划自己就正过来了STRAIGHT_JOIN反而不用加。optimizer_trace有一个已知限制它的内存默认 1MB复杂语句可能会被截断而且在 MySQL 8.0 里它对某些优化比如派生表合并的输出不如EXPLAIN ANALYZE直观。我的经验是两者配合用TRACE看理由ANALYZE看真实执行。4. STRAIGHT_JOIN 与 LEFT JOIN、LIMIT、聚合混用时的真实行为4.1 LEFT JOIN 与 STRAIGHT_JOIN 叠加时谁说了算LEFT JOIN的语义要求左表是驱动表这是 SQL 标准里定死的优化器不换STRAIGHT_JOIN也不应该去跟它拧着来。但如果你在SELECT后面随手加了个STRAIGHT_JOIN而FROM里又是A LEFT JOIN B优化器的行为就会变得很奇怪它一方面要保证B的外连接语义一方面又收到了照FROM顺序走的指令。如果A本来就是左表两者不冲突如果FROM里写成B LEFT JOIN ASTRAIGHT_JOIN要求B先读LEFT JOIN也要求B先读还是没冲突。真正容易翻车的是混用的场景SELECT STRAIGHT_JOIN s.shop_name, g.goods_name, oi.amount FROM t_shop s LEFT JOIN t_goods g ON g.shop_id s.id JOIN t_order_item oi ON oi.goods_id g.id;这条语句既有一个LEFT JOIN又有一个内连接STRAIGHT_JOIN写在外层。语义上g为空时oi也应该为空所以整条语句等价于t_goods那一段的外连接被内连接吞掉了。这种写法显然不是作者的本意但因为STRAIGHT_JOIN恰好把顺序锁成s → g → oi结果看起来还能跑问题就被藏在结果集的行数里了。一个更实用的经验STRAIGHT_JOIN和LEFT JOIN尽量不要出现在同一条语句里。如果确实需要用LEFT JOIN的语义就直接写LEFT JOIN然后把外连接的方向保证清楚让优化器自己去选每段的顺序因为 MySQL 对外连接的顺序处理本来就比较保守不太容易大错如果需要控制内连接段的顺序那就把那一段单独拆出来写成一个子查询或者视图别在同一层混着。4.2 和 LIMIT 分页一起用的时候顺序影响会被放大带LIMIT的查询里连接顺序的影响比普通查询大得多因为一旦顺序对了优化器有机会提前终止扫描。这个机制叫stop reading after enough rows中文一般叫满足条件即停。SELECT STRAIGHT_JOIN s.shop_name, oi.id, oi.amount FROM t_shop s JOIN t_goods g ON g.shop_id s.id JOIN t_order_item oi ON oi.goods_id g.id WHERE s.category_id 3 ORDER BY oi.id LIMIT 20;如果从t_shop出发过滤完只剩少量店铺接着探t_goods、t_order_item凑满 20 条就可以停实际读的行数远远不到表的全量。但优化器如果选了t_order_item驱动它要先把全表按id扫一遍哪怕只取前 20 条再反查t_goods和t_shop是否满足条件不满足的丢掉再继续扫。两种情况相差好几个数量级。注意STRAIGHT_JOIN加ORDER BY加LIMIT的组合能不能停下来不取决于你写的顺序取决于ORDER BY的列在驱动表上有没有排好序。oi.id在t_order_item上是主键但它在第三张表上那就停不了。这就是为什么分页查询里如果能按驱动表的列排序效果才好得多。4.3 聚合查询里的写法细节聚合加上STRAIGHT_JOIN最需要注意的是不能让聚合出现在被驱动表那一侧之前。看一下写法-- 写法 A聚合在子查询里 SELECT STRAIGHT_JOIN s.shop_name, x.total FROM t_shop s JOIN ( SELECT g.shop_id, SUM(oi.amount) AS total FROM t_goods g JOIN t_order_item oi ON oi.goods_id g.id GROUP BY g.shop_id ) x ON x.shop_id s.id WHERE s.category_id 3;这种写法里STRAIGHT_JOIN其实作用不到子查询内部它只强制s是驱动表、x是被驱动表。子查询内部要不要用STRAIGHT_JOIN得单独加。所以不要以为写了一个STRAIGHT_JOIN就全语句生效——它按层生效不跨子查询层。-- 写法 B聚合在顶层 SELECT STRAIGHT_JOIN s.shop_name, SUM(oi.amount) AS total FROM t_shop s JOIN t_goods g ON g.shop_id s.id JOIN t_order_item oi ON oi.goods_id g.id WHERE s.category_id 3 GROUP BY s.shop_name;写法 B 里STRAIGHT_JOIN对整个连接链生效s是驱动表剩下两张按顺序下来聚合发生在连接之后。这种写法如果在t_shop层过滤完之后只剩几十家店那整个连接的输入就不大聚合的排序开销也很小。但如果t_shop过滤后有几万家而t_order_item有几千万行从t_shop驱动的总成本反而可能高于让优化器从t_order_item开始扫。这里没有绝对答案得有EXPLAIN ANALYZE的真实数据来支撑。5. 除了 STRAIGHT_JOIN调连接顺序还有哪些路可走5.1 MySQL 8.0 的 JOIN_ORDER、JOIN_PREFIX、JOIN_SUFFIX 提示STRAIGHT_JOIN有一个明显的短板它只能按FROM子句里的书写顺序来你不能指定表 C 排第一、表 A 排第二这种非书写顺序。以典型的小表驱动场景书写顺序通常也就是业务逻辑上的自然顺序调整起来别扭。MySQL 8.0.20 之后引入的三个优化器提示补上了这个短板SELECT /* JOIN_ORDER(s, g, oi) */ s.shop_name, SUM(oi.amount) AS total FROM t_order_item oi JOIN t_goods g ON oi.goods_id g.id JOIN t_shop s ON g.shop_id s.id WHERE s.category_id 3 GROUP BY s.shop_name;JOIN_ORDER里列出的顺序就是最终连接顺序和FROM里书的顺序无关。JOIN_PREFIX只固定最前面几个表后面的顺序交给优化器自己补全JOIN_SUFFIX相反只固定最后几个表。这几个提示的好处是局部性。STRAIGHT_JOIN一加整条语句的顺序全锁死了将来表的规模变化、数据分布变化你都没法让优化器自己调整JOIN_PREFIX只锁前缀后面的顺序交给优化器灵活很多。我自己的使用习惯是只想知道某两张表谁先谁后优先用JOIN_PREFIX把前两张表写死后面的让它自己算整条语句的所有表顺序都不能错通常是三张表以内用JOIN_ORDER在 5.7 及更早版本的环境里只能用STRAIGHT_JOIN写法就得迁就FROM的顺序。JOIN_ORDER也有代价MySQL 8.0 文档明确说过用这些提示会降低优化器对搜索空间的探索如果写错了优化器不会替你纠正。所以用了提示之后一定得跟一次EXPLAIN ANALYZE确认计划就是你想的那条别想当然。5.2 用索引提示间接影响顺序STRAIGHT_JOIN和JOIN_ORDER都是对顺序的硬约束。还有一条路是软影响通过USE INDEX、FORCE INDEX或者IGNORE INDEX让某张表的某个索引变成唯一可行或者完全不可行优化器自然就会换顺序。SELECT s.shop_name, SUM(oi.amount) AS total FROM t_order_item oi FORCE INDEX (idx_goods) JOIN t_goods g ON oi.goods_id g.id JOIN t_shop s ON g.shop_id s.id WHERE s.category_id 3 GROUP BY s.shop_name;这种写法一般不被推荐因为它绑得太死了——一旦idx_goods改名或删掉整条语句直接报错。但在一些极端场景下它确实有存在价值比如某张表有两个候选索引其中一个索引的最后几列恰好能让WHERE条件用上Using index condition而另一个索引完全用不上这时候FORCE INDEX比STRAIGHT_JOIN对业务逻辑的侵入更小。我自己更倾向于用JOIN_ORDER/JOIN_PREFIX解决顺序问题用FORCE INDEX解决某个索引必须被选上的问题两者不要混着用。混用之后出问题时你根本分不清是顺序问题还是索引问题。5.3 各种手段放在一起怎么选下面这张表是我在项目里做取舍时常用的对照参数根据自己的环境调整结论大体不会差手段作用范围优点缺点适合场景STRAIGHT_JOIN整条语句或单个 JOIN所有版本都支持写法最简单顺序和书写绑定不能跨层5.7 环境、局部的两三表连接JOIN_ORDER单条语句顺序和书写解耦支持任意顺序只在 8.0.20 可用写错优化器不纠8.0 环境、复杂多表JOIN_PREFIX前缀若干张表只锁前缀留出后续优化空间同上三张表以上、只对前两张有把握JOIN_SUFFIX后缀若干张表固定尾部表同上尾部有强过滤或特殊逻辑的场景FORCE INDEX单张表能精确控制索引选择索引改名即失效索引选择错误但顺序没问题ANALYZE TABLE全表统计最根本的修复不锁任何东西需要定期执行计划不确定但数据分布健康看这张表会发现一个规律越靠前的方案越硬越靠后的方案越软。硬约束意味着可控性强、可移植性差软方案意味着对优化器友好、但对统计信息依赖高。我一般的原则是能靠ANALYZE解决的绝不写死顺序能靠JOIN_PREFIX局部解决的绝不整句JOIN_ORDER不到万不得已不用STRAIGHT_JOIN和FORCE INDEX。6. 实际项目里关于 STRAIGHT_JOIN 的几个坑6.1 加了 STRAIGHT_JOIN 之后索引反而没被选上有一种情况特别迷惑加了STRAIGHT_JOIN之后EXPLAIN里顺序确实变了但key那一列从原来的某个索引变成了NULLtype从ref变成ALL。快了一点又慢了回去甚至更慢。原因通常是这样原计划里驱动表是t_big它可以走idx_filter来过滤WHERE条件被驱动表是t_small走主键STRAIGHT_JOIN之后驱动表换成t_smallt_small上的WHERE条件没有合适的索引只能全表扫。全表扫的代价虽然小但被驱动表那边每行探下去总成本就翻了。要避免这个坑加STRAIGHT_JOIN之前先单独看一遍每张表的单表执行计划EXPLAIN SELECT * FROM t_shop WHERE category_id 3; EXPLAIN SELECT * FROM t_goods WHERE on_sale 1;如果发现某张表单独查都得走全表扫那它就不适合当驱动表STRAIGHT_JOIN换个顺序可能更差。经验是这样驱动表一定要能在WHERE条件下走一个选择性够好的索引否则宁可不要动顺序。6.2 数据量变化之后写死的顺序会变成新问题这是我踩得最深的一个坑。一年前为了修一条报表 SQL 加了STRAIGHT_JOIN当时t_shop过滤完只剩 200 家店驱动t_goods完全没问题。半年后业务扩了 30 倍t_shop过滤完变成 6000 家t_goods涨到 5000 万行STRAIGHT_JOIN还在那儿锁着t_shop驱动结果这条 SQL 从 3 秒变成 4 分钟谁都不敢动因为不知道去掉STRAIGHT_JOIN会不会更慢。后来总结出的做法是加了STRAIGHT_JOIN的语句一定要在 SQL 注释里记清楚加它的那一天的理由和数据量级比如2024-03 场景t_shop 过滤后约 200 行t_goods 约 300 万行顺序固定可降本 8 倍定期比如每季度用EXPLAIN ANALYZE复核一次跟注释里的数据量做对比数据量翻了 10 倍以上就重新评估复核结论写回注释不是发个邮件就完了。这套流程听起来繁琐但比线上突然出问题、三分钟定位、五分钟决策要便宜得多。6.3 团队协作里 STRAIGHT_JOIN 的可读性问题STRAIGHT_JOIN还有个软性的问题它把优化器的意图藏起来换成一句对后来者来说毫无上下文的语法。新来的同事看到一个STRAIGHT_JOIN很难判断这是真的有必要还是当年随手加的很容易要么不敢删要么删了之后出事。我处理的办法是把STRAIGHT_JOIN当成一种需要写文档的东西。具体写法-- 强制 t_shop 驱动category_id3 过滤后约 3000 行 -- 从 t_shop 驱动时最大扫描为 36 万次索引查找 -- 从 t_goods 驱动需先按 on_sale 回表 255 万次慢约 40 倍。 -- 2024-05 复核ANALYZE 后统计信息准确计划稳定保留。 SELECT STRAIGHT_JOIN s.shop_name, SUM(oi.amount) AS total FROM t_shop s JOIN t_goods g ON g.shop_id s.id JOIN t_order_item oi ON oi.goods_id g.id WHERE s.category_id 3 GROUP BY s.shop_name;这种注释看着啰嗦但确实省了大量沟通成本。它写清楚了三件事为什么用、当时的量级是多少、复核结论。后来者在改动之前至少知道该复核什么。还有一条经验STRAIGHT_JOIN不要和业务逻辑混在同一条 SQL 里。如果一条语句既要统计业务指标又要控制连接顺序最好把顺序控制的那部分逻辑抽到视图或者单独的 DAO 层方法里业务层的 SQL 保持干净。这么做的原因很简单——业务 SQL 会经常被产品经理改来改去STRAIGHT_JOIN一被改乱排查成本远超它带来的收益。6.4 关于该不该用我自己的判断顺序最后说点主观的。碰到慢查询我的处理顺序一直没变先看EXPLAIN的rows和filtered跟真实COUNT(*)对一下确认估算准不准。估算不准的直接ANALYZE TABLE多半就完了根本轮不到STRAIGHT_JOIN。估算准但还是选错顺序的看被驱动表的连接列有没有索引。没有索引先补索引STRAIGHT_JOIN救不了没索引的连接。顺序和索引都对但计划