很多后端同学都遇到过这种场景一条SQL在测试数据库上跑得飞快到了线上却像老牛拉车加索引也不管用甚至越加越慢。最后打开执行计划一看才发现问题出在MySQL优化器 (Optimizer) 的“脑回路”上——它没有选我们预期的那条路。优化器是决定一条SQL到底怎么执行的模块是整个查询处理链路里最需要被理解、也最容易被误解的部分。这篇文章我想用“庖丁解牛”的方式把MySQL优化器的核心概念一块一块拆开来讲讲它的成本模型、基数估计、访问路径和连接顺序再配合实操中的执行计划解读、优化器跟踪和常见误判案例帮你真正看懂优化器而不是靠猜。1. 优化器SQL与数据之间的“翻译官”1.1 一条SQL从发起到执行优化器处在哪一步先看一条SQL到达MySQL之后到底发生了什么。我们发出一个SELECT语句MySQL不会直接去存储引擎里面翻数据。整个流程大致是客户端把SQL发给服务器词法分析和语法分析模块先检查语法生成一棵解析树然后进入预处理检查表、字段是否存在做权限校验接着查询重写器会对SQL做一些逻辑变换比如视图展开、常量表达式计算、子查询改写再往后才是优化器真正出场的地方。优化器会基于解析树和重写后的查询结构生成很多种不同的“执行方案”也就是执行计划。每个计划都描述了“先读哪张表、走哪个索引、以什么顺序关联、如何过滤、如何排序”等等这一整套动作。优化器会估算每种方案的成本选一个成本最低的作为最终执行计划交给执行器去执行。也就是说SQL本身是“声明式”的它只告诉数据库“我想要什么”至于“怎么取”完全由优化器说了算。两个人写同样的结果可能因为JOIN顺序不同、索引选择不同最终性能差出几个数量级。1.2 优化器的目标和现实约束很多人对优化器有一个误解以为它会穷举所有可能的执行方式然后选一个数学上的最优解。真实情况是一个多表JOIN的查询可选的连接顺序总数是表数量的阶乘。如果十几张表关联穷举出来的数量大到根本算不完。所以MySQL优化器的目标不是“最优”而是在“有限的时间内找到足够好”的执行计划。它做了很多剪枝牺牲一部分搜索空间换取更短的决定时间。这个思路和手机上的导航软件其实很像导航不会把全世界所有路线都算一遍而是在你出发位置附近、道路等级和实时路况的综合信息下快速挑出几条候选路线再给出一条推荐路线。这也解释了一个常见现象为什么3张表的查询执行计划很稳定但8张表的JOIN执行计划经常变得很奇怪。表数量越多优化器越依赖启发式规则去剪枝越容易剪掉一些“看似不划算”但实际上很优的路径。理解这些现实约束是后面排查执行计划异常的第一步。2. 核心概念拆解成本模型、基数估计、访问路径与连接顺序2.1 成本模型每个计划都有“价格标签”MySQL优化器以成本为单位给执行计划打分。它不是一个抽象的概念而是一套可以量化的计算模型主要分为两类IO成本和CPU成本。IO成本指的是读取数据页产生的代价。比如全表扫描需要读取表聚簇索引中全部叶子节点页面走二级索引范围扫描需要先读取索引页面再根据主键回表读数据页。CPU成本指的是在内存中处理数据产生的代价比如比较、过滤、排序、计算函数等。InnoDB下成本可以通过MySQL内置的成本常量来估算。比如一个数据页的读取成本默认是1.0一行记录的CPU处理成本默认是0.2主键扫描时每条记录的CPU成本可能略有不同。这些值可以查询mysql.server_cost和mysql.engine_cost表也可以自定义调优。日常使用中我们不需要手工计算这些数值但需要理解成本模型的基本思路总成本约等于“读页次数 处理行数”的加权和。如果优化器认为“反正要找的数据占全表一大半”那走索引回表的成本可能比全表扫描更高它就会选择全表扫描。想知道优化器估算出的成本到底是多少可以使用EXPLAIN FORMATJSON SELECT * FROM orders WHERE statusPAID;输出结果里的query_cost字段就是优化器认为执行这个计划需要付出的总成本。这个数字不精确但它是理解优化器“脑回路”的第一手资料。2.2 基数估计统计信息是优化的地基成本模型里的“处理行数”不是真去数一遍而是靠估算。估算的输入是统计信息也就是表的数据行数、索引基数、数据分布情况等信息。MySQL通过定期采样把统计信息存在数据字典里优化器执行时不会去实时统计而是直接用这些已有数据。基数估计有多重要举个例子。订单表有1000万行按用户ID过滤某个用户可能有100条订单那优化器估算走idx_user_id返回100行然后再回表查询性价比很高。但如果统计信息还停留在一个月前当时这个用户只有1条订单而实际上现在已经积累到了100万条订单那优化器的估算就会严重失真最终可能选了一个非常差的索引或者干脆走全表扫描。实践中很多慢SQL的根因并不是索引缺失而是索引基数统计完全偏离实际。可以用SHOW INDEX FROM 表名看到索引的Cardinality值这个值就是优化器估算索引区分度的重要依据。如果这个值和实际相差很大就应该考虑用ANALYZE TABLE刷新生效。统计信息过期并不容易一眼看出来因为MySQL的采样本身带随机性大表的统计信息更新也不是实时的。所以生产环境里要养成定期检查统计信息、定期刷新的习惯尤其是大批量导入、批量删除之后。2.3 访问路径数据到底怎么被捞出来优化器要决定的第一件事是每张表的数据怎么取。这里涉及几个核心访问方式也是执行计划里type字段最常见的那几个值system表只有一行属于极端情况。const通过主键或唯一索引等值查询最多返回一行可以提前把这一行当成常量处理速度极快。eq_refJOIN时被驱动表通过主键或唯一索引等值匹配每行只匹配一行是一个很高效的路径。ref通过二级索引等值匹配可能返回多行比如查某个分类下的所有商品。range索引范围扫描常见于BETWEEN、、、IN等条件。index遍历二级索引的叶子节点比全表扫描代价略低但仍然相当于全索引扫描。ALL全表扫描不一定就是坏事但当表很大且选择性很强时它通常意味着优化器判断出问题了。不同访问方式对应不同的IO策略和回表需求。比如二级索引ref查到主键后需要回聚集索引取完整行如果查询需要的列都在二级索引里就直接走“覆盖索引”连回表都省掉Extra列会显示Using index。理解这些访问路径再看执行计划时就不慌了。看到ALL先别急着骂优化器先问一句这个查询条件的选择性到底高不高如果一条SQL要返回全表30%以上的数据全表扫描往往是更合理的方案。2.4 连接顺序多表JOIN如何决策当SQL里出现JOIN优化器需要决定先读哪张表、后读哪张表。MySQL的执行方式主要是嵌套循环连接也就是说先遍历驱动表的每一行再拿着驱动表的结果去被驱动表里查匹配行。这就直接引出一个经验法则用小表驱动大表。驱动表越小循环次数越少被驱动表上索引查找的次数也越少。优化器在估算连接顺序时会按照“前一个表的输出行数 * 后一个表的单行匹配成本”来累积计算总成本然后选择总成本最小的那个方向。但是注意实际的连接顺序并不是简单的“行数少的在前”。优化器还会考虑被驱动表是否有可用索引。如果一个小的驱动表在另一张超大表上没有任何可利用索引那每次循环都要全表扫描总成本非常高还不如反过来。所以优化器经常“看起来”违背了小表驱动大表的原则实际是为了避免被驱动表的全表扫描。MySQL 8.0引入了Hash Join用于等值连接场景下没有合适索引的情况它在某些情况下会改变执行计划形态。但核心思路还是一样的优化器会为每一对可能的连接方式估算出内存使用、CPU开销和IO开销最后选它认为最划算的那个。3. 优化器的决策机制和干预手段3.1 RBO与CBOMySQL主要靠什么优化器领域有两个基本流派一个是基于规则的优化RBO一个是基于成本的优化CBO。RBO根据固定的规则决定执行方式比如“有索引就一定要走索引”“小表一定放前面”规则写死不管实际数据分布如何。CBO则依靠统计信息和成本模型动态决策更贴近真实数据。MySQL当前主要采用的是CBO逻辑这也是为什么同样的SQL在不同数据分布下会有完全不同的执行计划。不过MySQL内部也保留了很多规则优化能力比如常量折叠把WHERE 11 AND col5化简成WHERE col5。条件化简把多个范围条件合并为一次索引范围扫描。子查询转换把IN (SELECT ...)改写成半连接。谓词下推把WHERE条件尽量下推到每张表读取时就过滤。这些规则执行得早会让后续的成本计算更准确。但真正决定访问路径和连接顺序的还是成本计算。所以我们要学会从成本角度去解释优化器行为而不是死记“有索引走索引”这种过时的经验。3.2 优化器如何剪枝搜索空间多表JOIN的搜索空间很大MySQL不会去评估所有连接顺序。它使用的是一种偏左深树的搜索策略也就是说最终计划基本可以看成一张表一张表“串”进去的过程而不是任意排列的笛卡尔积。在这个前提下优化器会做两件事。第一把部分不符合条件的JOIN顺序提前剪掉比如某张表的过滤条件特别强它大概率会成为驱动表。第二对候选计划分层比较每一轮只保留当前成本最小的几个计划继续扩展放弃成本过高的分支。这个策略保证优化器在表数量不多时能做出稳定的决策但也带来了一个问题某些情况下它看不到全局最优解。比如5张表关联明明有一个连接顺序需要较大的“前期代价”但能大幅压缩“后期代价”优化器可能在剪枝时就把这个方案丢了。对使用者来说这意味着我们在写JOIN查询时不能完全依赖优化器。如果表数量超过5张建议手动拆解查询、调整JOIN顺序或者使用STRAIGHT_JOIN这类提示来指定顺序。3.3 通过HINT和参数干预优化器优化器选错了怎么办MySQL提供了几种干预手段。最基础的是在SQL里加索引提示SELECT * FROM orders FORCE INDEX(idx_user_status) WHERE user_id 12345 AND status PAID;FORCE INDEX强制优化器使用指定索引IGNORE INDEX则让优化器忽略某个索引。但这种写法比较粗暴因为索引是否真的最优在SQL写死的一瞬间就不再动态变化了。表数据再变、统计信息再变这段SQL都会一直沿用同一个索引反而可能埋下隐患。更精细的做法是用优化器提示Optimizer HintsMySQL 8.0支持类似其他数据库的/* ... */风格SELECT /* INDEX(orders idx_user_status) */ * FROM orders WHERE user_id 12345 AND status PAID;还可以用optimizer_switch控制某些优化策略的开关。比如semijoin、materialization、mrr等。不过optimizer_switch是全局或者会话级别的改一个值会影响所有SQL强烈不建议在生产环境轻易全局修改。我的建议是先理解为什么优化器选错再决定干预手段。如果只是统计信息过期刷新统计信息就够了如果是因为数据分布极端可以考虑加更合适的索引实在不行再用HINT做局部干预而且要留好注释方便后续维护的人理解为什么这么写。4. 实操看懂EXPLAIN和常见优化器误判4.1 EXPLAIN字段速查EXPLAIN SELECT ...是使用频率最高的优化器诊断工具。它输出的每一行代表执行计划里的一张访问表或一个派生表子查询。字段含义重点关注id执行计划中操作的唯一编号编号越大越先执行select_type查询类型如SIMPLE、PRIMARY、SUBQUERY子查询会出现DERIVEDtable访问的表名或别名可能是派生表名type访问方式const/ref/range/index/ALLpossible_keys可能用到的索引候选集key实际选择的索引看它是否合理key_len索引中使用的字节数可判断用了几列rows优化器估算需要读取的行数偏差大说明统计信息有问题filtered经过条件过滤后剩余行的百分比越低说明索引选择越差Extra额外的执行信息Using index / Using filesort / Using temporary其中rows和filtered是最值得观察的两个字段。优化器会根据它们计算最终输出行数进而影响后续连接成本。4.2 一个慢SQL排查实录某系统的订单查询页面突然变慢SQL大概长这样SELECT id, order_no, user_id, created_at FROM orders WHERE user_id 12345 AND status PAID ORDER BY created_at DESC LIMIT 20;表上有两个索引KEY idx_user_id (user_id), KEY idx_user_created (user_id, created_at)直觉告诉我们idx_user_created更合适因为它既能过滤user_id又能按created_at排序还可能支持覆盖索引。但实际EXPLAIN发现优化器选了idx_user_idExtra里出现了Using filesort。原因是该用户数据是“热点”数据表里某个user_id的订单数量从几千涨到了十几万而统计信息还停留在采样时的几千行。优化器用旧的rows值估算认为扫描几千行再排序很便宜于是选择了更“窄”的索引。处理方式是先刷新统计信息ANALYZE TABLE orders;执行完成后EXPLAIN重新观察优化器改选了idx_user_createdFilesort消失慢查询恢复正常。这个案例说明很多“优化器乱选”的问题本质是基础数据不新鲜。4.3 统计信息过期的典型症状与处理统计信息过期有一些典型症状比如同一张表两次EXPLAINrows值差异巨大执行计划在表数据量明显变化后仍然纹丝不动或者优化器选择明显不合理的无用索引。InnoDB的统计信息默认是持久化的存储在mysql.innodb_table_stats和mysql.innodb_index_stats中。每次打开表或者表数据发生变化不一定都会触发重新采样。默认配置下自动采样发生在某些阈值条件下因此不能完全依赖自动更新。针对性处理方法手动执行ANALYZE TABLE 表名立即刷新统计信息。如果表特别大刷新耗时较长可设置在业务低峰期执行。调整采样页数innodb_stats_sample_pages增加页数得到更精确的基数但会拖慢采样速度。对于批量导入大数据的场景导入后应立即刷新统计信息。我自己在维护分区表时还踩过一个坑分区的统计信息更新粒度比较特殊有时要ANALYZE TABLE整个表才能让所有分区统计信息都刷新。如果只刷新了某个分区其他分区统计信息仍然是旧的优化器还是可能用旧数据做决策。4.4 优化器跟踪让优化器“开口说话”EXPLAIN只能告诉你最终结果不能告诉你为什么选择这条路。想看清优化器的每一步思考需要用优化器跟踪功能。基本步骤如下SET optimizer_trace enabledon; SELECT * FROM orders WHERE user_id 12345 AND status PAID ORDER BY created_at DESC LIMIT 20; SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;在OPTIMIZER_TRACE的输出里可以看到优化器记录了所有候选访问路径、每棵查询块的成本估算、选择索引的原因甚至包括它拒绝某个索引的理由。比较长但排查疑难杂症时极其有用。当初我遇到一个优化器坚持不走索引的案例EXPLAIN里怎么看都解释不通。后来打开OPTIMIZER_TRACE发现它认为该索引的回表成本和全表扫描差不了多少而当时的统计信息里索引基数是严重失真的。问题根源一下清楚并不是优化器“犯傻”而是它获取的决策素材本身就是错的。优化器跟踪在生产环境使用时要注意开启期间会额外产生开销建议只对特定会话开启排查完立刻关闭。4.5 EXPLAIN ANALYZE真实执行与估算对比MySQL 8.0.18之后EXPLAIN ANALYZE是一个非常实用的工具。它在真实执行SQL的同时返回每一层操作的耗时、实际行数和估算行数。EXPLAIN ANALYZE SELECT id, order_no, user_id, created_at FROM orders WHERE user_id 12345 AND status PAID ORDER BY created_at DESC LIMIT 20;输出的结果里会出现类似“actual time0.523..0.745 rows20 loops1”这样的信息。把actual rows和EXPLAIN里的rows放在一起对比就能发现估算偏离程度。比如优化器认为某索引回表需要读5万行实际执行只读了几十行说明统计信息中的基数严重低估/高估。反之如果实际读了100万行而估算只写了1000行那这个索引本身就不是一个高选择性的索引不应该作为主路径。使用EXPLAIN ANALYZE时要留意它是真正“跑一条SQL”不只是生成执行计划。对于写操作要格外谨慎因为它的完整使用流程可能会导致数据变更虽然一般EXPLAIN ANALYZE对DML也会回滚但生产库上我一般不轻易对大批量DML使用。5. 优化器调优中的经验与坑5.1 小心统计信息采样的随机性统计信息不是绝对精确的它带有采样随机性。特别是大表如果采用默认的采样页数基数估算可能偏差很大。一个比较典型的例子是索引A的基数比索引B高但采样偏差导致优化器认为A的区分度不如B最终选了B执行计划慢到让人绝望。解决方案是调整采样页数。innodb_stats_sample_pages默认取值可能与表大小不匹配。调大该值能提高统计精确度但会使ANALYZE TABLE更慢需要权衡。也可以通过手动修改统计信息表的方式做针对性修正但不推荐轻易这么做除非你确实理解InnoDB统计表的内部逻辑。另一个经验是表结构变更、大批量删除、大事务回滚之后应主动刷新统计信息不要等着下一次“自动采样”来兜底。5.2 子查询与派生表优化器有时会“犯傻”子查询的执行方式五花八门MySQL优化器会把子查询改写成半连接、物化表、延迟物化等策略。写法和执行计划之间不是一一对应的。常见问题场景是IN (SELECT ...)。开发人员喜欢这么写是因为直观但优化器可能把子查询物化成一个临时表造成额外的内存和磁盘开销。反过来某些场景下物化又比直接关联执行更快。我遇到过一个案例两个表做查询子查询返回几万行外层表匹配度很高。优化器选择先物化子查询再对外层表扫描结果临时表又大又慢。把SQL改写成JOIN后执行计划变成了小表驱动大表查询时间从秒级变成毫秒级。这并不是说子查询一定不好。实际上MySQL 8.0对派生表合并和延迟物化做了大量改进很多情况下子查询执行计划已经相当优秀。重点是不要凭感觉断定哪种写法快一定要看EXPLAIN。如果出现Using temporary并且临时表行数非常大就要警惕。5.3 ORDER BY ... LIMIT 的执行计划陷阱ORDER BY col LIMIT n是慢SQL高发区。优化器经常会在“走索引避免排序”和“全表扫描再排序取少量行”之间纠结。一种典型场景是查询条件中有范围条件比如created_at 某时间还需要按status排序。索引设计可能无法同时满足过滤和排序。优化器会估算如果走索引过滤虽然排序能省但回表行数很多如果全表扫再排序然后取LIMIT前20行虽然看起来粗暴但实际可能更快因为LIMIT可以提前终止排序过程。这导致了一个让很多人困惑的结果明明表上有完全匹配排序的索引优化器却选择Filesort。多跑几次EXPLAIN观察rows和Extra才能判断优化器判断是否合理。如果确实需要强制走索引排序可以考虑SELECT /* INDEX(orders idx_status_created) */ ...或者使用FORCE INDEX。但更推荐从索引设计角度重新思考例如把过滤频繁的列放在排序列前面让索引同时覆盖过滤和排序。5.4 不要盲目全局调参遇到慢查询有人第一反应是调大join_buffer_size、sort_buffer_size或者改optimizer_switch。全局参数一改确实可能立刻改变执行计划但也可能让原本正常的SQL走向另一条更差的路。比如关闭semijoin优化有时会让某些IN查询退化成逐行子查询执行性能暴跌。比如调大sort_buffer_size会让排序操作显得成本更低优化器更倾向于使用Filesort但大Buffer并不会解决根本的索引设计问题。调优应遵循“局部优先、精准干预”的原则先通过EXPLAIN、OPTIMIZER_TRACE拿到优化器的决策依据。优先用索引设计或SQL改写解决问题。再用HINT局部干预具体SQL。最后才考虑调整局部会话参数。避免在全局维度修改影响面大的开关。我在实际维护中碰到过一个案例为了救一个全表扫描的SQL某开发同学把全局optimizer_switch里的mrr关了结果当晚几十条依赖MRR的执行计划全部变化业务出现大面积服务异常。最后回滚参数单独优化那条SQL的索引才真正解决。最后的一点个人体会优化器不是一个玄学模块。它每一次选择背后都有统计数据、成本模型和搜索策略在支撑。排查慢SQL时我最大的心得不是去“猜测”优化器为什么这么做而是用EXPLAIN和OPTIMIZER_TRACE把它的决策依据挖出来。很多看似不可理喻的执行计划最后都能追溯到统计信息失真或者数据分布极端这两个根源上。另外优化器的决策质量高度依赖输入SQL的表达形式。同样的业务语义用JOIN写、用子查询写、用EXISTS写优化器可能生成完全不同的执行计划。不要迷恋某种固定写法要多用计划解释工具做对比。如果你刚接触这块建议从一个熟悉的慢SQL开始跑一次EXPLAIN再看一次EXPLAIN FORMATJSON然后试试EXPLAIN ANALYZE。把每个字段的含义弄明白把优化器算出来的成本和自己预估的对比一下。用不了几次你就能慢慢摸清MySQL优化器的那套“脾气”了。
