type
status
date
slug
summary
tags
category
icon
password
catalog
sort

前言：为啥要啃官方文档？

上一篇我们聊了MySQL优化器的进化史，从早期的规则驱动到如今的智能决策，它的升级始终跟着业务复杂度走。但对咱们2018年入行的Java后端开发来说，光知道历史不够——日常写SQL、调接口性能时，更需要搞懂“优化器为什么这么选执行计划”“怎么让它按预期走”。而这些答案，就藏在MySQL官方文档关于优化器的核心章节里。
你可能觉得官方文档枯燥，但我这几年开发中，好几次靠啃文档解决了棘手问题。印象最深的是一个订单列表接口，明明建了索引却跑8秒，查文档才发现是“子查询没被优化器扁平化”，按文档里的规则调整写法后，直接降到0.3秒。原来很多时候不是优化器“不听话”，是我们没摸透它的“操作手册”。
这一章咱们就深读官方文档里最核心的优化器章节，用开发视角拆解开：从优化器的基础工作流程，到分区裁剪、范围优化的具体规则，再到超图优化器的决策逻辑。每个知识点都贴文档原文、配开发场景案例，保证你看完就知道“写SQL时该参考文档哪条规则”“调优时该查文档哪个参数”。
记住：真正的优化高手，都是能把官方文档嚼碎了再咽下去的人。

2.1 Optimizer Overview：优化器到底是个啥？

官方文档第一句话就说：“The MySQL query optimizer uses cost-based optimization to determine the most efficient execution plan.” 翻译过来就是：“MySQL查询优化器用基于成本的优化来选最高效的执行计划。” 听着简单，但这里面藏着三个关键点：

2.1.1 优化器的核心任务：选“最便宜”的路

咱们可以把优化器想象成个“精打细算的导游”。当你给它一条SQL（相当于告诉它“我要去这个景点”），它得根据当前路况（表结构、索引、数据分布）规划出一条“花钱最少”的路线（执行计划）。这里的“钱”就是成本，包括IO成本（读磁盘的开销）、CPU成本（计算的开销）、内存成本（临时表/排序的开销）。
官方文档里画了个优化器工作流程图，虽然简单，但把核心步骤说透了：
我用大白话翻译一下这五个步骤：
  1. 解析SQL：先把SQL拆成“单词”，检查语法对不对（比如有没有少写WHERE关键字）；
  1. 逻辑转换：给SQL“整容”，比如把外连接转内连接、子查询转JOIN，让它更高效；
  1. 成本估算：给每个可能的执行计划算成本，比如全表扫描要花多少IO，走索引要花多少；
  1. 计划优化：再检查一遍有没有更优的细节，比如要不要用临时表、要不要排序；
  1. 生成计划：选成本最低的计划交给执行器去跑。

2.1.2 逻辑转换：优化器的“魔法整容术”

官方文档里重点讲了逻辑转换的几种“神操作”，这可是优化器的核心技能。咱们一个个说：

外连接转内连接：最实用的“瘦身术”

官方给了个例子：
表面看是左连接，但WHERE条件里t2.status IS NOT NULL其实把左连接的“左表所有行”特性给废了——因为右表如果没匹配到，t2.status就是NULL，会被这个条件过滤掉。这时候优化器会智能地把它转成内连接：
别小看这个转换！内连接可以自由选择驱动表，而左连接只能左表当驱动表。我之前遇到过一个用户表（300万行）左连接订单表（500万行）的查询，因为这个转换，优化器选了订单表当驱动表，查询时间从20秒降到了1.5秒！
怎么验证优化器有没有做这个转换？用EXPLAIN FORMAT=JSON看transformations字段：
看到outer_join_to_inner_join: true，就说明转换成功了。

子查询优化：从“嵌套循环”到“并肩作战”

子查询一直是优化器的难点，但从5.7开始，优化器学会了把很多子查询“扁平化”。比如IN子查询：
会被转成JOIN形式：
这是因为JOIN比子查询更容易优化连接顺序和索引使用。我有个电商客户的商品查询，用IN子查询时跑5秒多，优化器转成JOIN后直接降到0.3秒，效果立竿见影。
官方文档特别强调：EXISTS子查询比IN子查询更易优化。如果你的子查询结果集大，尽量用EXISTS代替IN，比如：

常量传递：提前算好“答案”

优化器还有个“小聪明”叫“常量传递”（Constant Propagation）。比如这条SQL：
优化器会把b = a转成b = 5，因为a已经确定是5了。这样存储引擎就能同时用a和b的索引，而不用先查a=5再过滤b=a。
源码里sql/sql_optimizer.cc的propagate_cond_constants函数就是干这个的：
这个优化在报表查询里特别有用，能减少大量不必要的计算。我见过一个财务报表，因为用了常量传递，过滤条件从3个减到1个，扫描行数直接少了80%。

2.1.3 成本估算：优化器的“算盘”怎么打？

官方文档里说：“Cost-based optimization uses table and index statistics to estimate costs.” 意思是成本估算全靠统计信息。那具体怎么算呢？

基础成本公式

官方没直接给公式，但从源码和文档描述能推出来核心公式：
这些参数都能调，比如SSD硬盘IO快，就可以把io_block_read_cost调低：

统计信息是“算盘珠子”

成本估算准不准，全看统计信息准不准。统计信息包括：
  • 表的总行数（table_rows）；
  • 索引的基数（cardinality，索引列不重复值的数量）；
  • 数据页数量（data_length / innodb_page_size）；
  • 直方图（8.0新增，记录数据分布）。
怎么看统计信息？用SHOW TABLE STATUS和SHOW INDEX：
如果发现Rows（估算行数）和实际行数差太远，就得更新统计信息：
MySQL 8.0默认会自动更新统计信息（innodb_stats_auto_recalc=ON），但大表可能更新不及时，这时候就得手动跑ANALYZE。

2.1.4 常见误区：优化器不是“万能神”

官方文档虽然没明说，但实战中优化器经常掉“坑”里。我总结了三个最常见的：

误区1：优化器永远选最优计划

错！优化器是“估算”成本，不是“实测”。如果统计信息不准，它可能选个超烂的计划。比如有个表实际100万行，但统计信息显示1万行，优化器可能会选全表扫描而非索引。
解决办法：定期更新统计信息，大表可以调大采样量：

误区2：加了索引优化器就会用

错！优化器觉得索引成本高就不用。比如WHERE status = 'active'，如果90%的行都是active，优化器会觉得全表扫描比走索引还快（因为索引还要回表）。
解决办法：用覆盖索引，把查询列都包含在索引里：

误区3：子查询转JOIN一定更快

不一定！如果子查询结果集很小（比如10行以内），子查询可能比JOIN快，因为JOIN要处理连接逻辑。优化器偶尔也会“过度优化”，把小结果集的子查询强行转JOIN，反而变慢。
解决办法：用优化器提示（hint）禁转换：

2.2 Partition Pruning：分区表的“减肥术”

官方文档说：“Partition pruning is a performance optimization that limits the number of partitions that MySQL must examine when querying a partitioned table.” 翻译过来就是：“分区裁剪是个性能优化，能让MySQL查分区表时只看必要的分区，其他的都剪掉。” 这玩意儿简直是大表的“救星”！

2.2.1 分区裁剪的核心逻辑：只扫“有用的区”

咱们先想个场景：有个订单表orders，按月份分区，存了2020-2025年的数据，共60个分区。现在要查2023年10月的订单，如果不裁剪，得扫60个分区；如果能裁剪，只扫1个分区，性能差60倍！
分区裁剪的核心就是：根据WHERE条件里的分区键，算出需要访问的分区，其他分区直接跳过。但不是所有分区类型都能裁剪，官方文档里写得很清楚：
分区类型
能否裁剪？
裁剪效果
RANGE分区
能
按范围精准裁剪（如日期区间）
LIST分区
能
按列表值裁剪（如地区ID）
HASH分区
有限支持
只能单分区或全表，无法区间裁剪
KEY分区
有限支持
类似HASH，裁剪效果差
最常用的是RANGE分区（按范围）和LIST分区（按枚举值），咱们重点讲这两个。

2.2.2 RANGE分区裁剪：按时间/数值范围“切蛋糕”

RANGE分区最适合按时间或数值分区，比如订单表按月份分区：
现在查2023年10月的订单：
优化器会算出TO_DAYS(order_time)在TO_DAYS('2023-10-01')到TO_DAYS('2023-11-01')之间，刚好对应p202310分区，其他分区全被剪掉！
怎么验证裁剪效果？用EXPLAIN PARTITIONS：
看partitions列，如果只显示p202310，说明裁剪成功！我之前帮一个外卖平台调优，用这个方法把月订单查询从5分钟压到了30秒，就是因为只扫了1个分区。

源码里的裁剪逻辑

分区裁剪的核心代码在sql/range_optimizer/partition_pruning.cc的prune_partitions函数里：
简单说就是三步：分析条件→生成范围→匹配分区。如果条件里的分区键用了函数，比如YEAR(order_time) = 2023，只要函数是“单调的”（比如YEAR、TO_DAYS），优化器也能裁剪，这点比早期版本强多了！

2.2.3 LIST分区裁剪：按枚举值“挑分区”

LIST分区适合按固定枚举值分区，比如按地区ID分区：
查华东地区的日志：
优化器会直接定位到p_east分区，其他3个分区不扫描。如果查华东和华南：
会同时扫p_east和p_south，这也符合预期。
但LIST分区有个坑：如果条件里用了范围，比如region_id > 2，优化器没法精准裁剪，可能会扫所有分区（因为LIST分区的枚举值不一定连续）。这时候就得用RANGE分区或者加索引了。

2.2.4 分区裁剪的“雷区”：这些情况剪不动！

官方文档没明说，但实战中很多情况会导致裁剪失败，我踩过的坑分享给你：

雷区1：分区键被函数/表达式包裹（非单调函数）

比如订单表按order_time分区，但查询条件写：
DATE_FORMAT不是单调函数（同一个结果可能对应多个输入），优化器算不出范围，只能全表扫。正确写法应该用范围条件：

雷区2：分区键在JOIN条件里，不在WHERE里

比如用用户表和订单表JOIN，按订单表分区键过滤：
这时候优化器在裁剪时看不到o.order_time的条件（因为JOIN顺序没确定），所以订单表会全分区扫描。解决办法：把条件写到子查询里，提前过滤：

雷区3：分区表用了外连接且条件在右表

左连接时，如果分区裁剪条件在右表，可能剪不动：
这时候优化器会认为o.order_time可能为NULL，条件不一定成立，所以不裁剪。解决办法：确认是内连接需求就用内连接，或者把条件移到ON子句：

2.2.5 分区裁剪调优实战：从5分钟到30秒的逆袭

分享个真实案例：某游戏公司的日志表game_log，按天分区（每天一个分区），存了1年的数据（365个分区）。有个查询要查最近7天的登录日志，原本跑5分钟，优化后30秒搞定。

原始SQL（慢查询）：

EXPLAIN PARTITIONS显示扫了所有365个分区，原因是分区键用的是TO_DAYS(log_time)，但查询条件里log_type = 'login'是个低选择性条件（90%的日志都是login类型），优化器觉得先过滤分区再过滤log_type成本高，直接放弃裁剪。

优化步骤：

  1. 加联合索引：(log_time, log_type)，让优化器先通过log_time定位分区，再用log_type过滤；
  1. 强制分区裁剪：用FORCE INDEX提示优化器走时间索引；
  1. 调整条件顺序：把时间条件放前面，明确告诉优化器优先裁剪。

优化后SQL：

EXPLAIN PARTITIONS显示只扫了最近7个分区，查询时间从5分钟降到30秒！

2.3 Range Optimizer：范围查询的“导航仪”

官方文档里说：“The range optimizer is used to find the best way to retrieve rows from a table when the WHERE clause contains range conditions.” 简单说，范围优化器就是专门处理带范围条件（比如>, <, BETWEEN, IN）的查询，帮你选最优的索引和访问方式。

2.3.1 范围优化器的核心任务：把条件“拆成区间”

咱们写WHERE a > 10 AND a < 20 AND b = 5这样的条件时，范围优化器会做两件事：
  1. 把条件拆成“区间”（比如a在(10,20)之间，b=5）；
  1. 找能覆盖这些区间的索引，计算每个索引的成本，选最优的。
官方文档把范围条件分成了几类，我用大白话翻译一下：
条件类型
例子
优化器处理方式
equality
a = 5
精准匹配，走ref访问
range
a > 5 AND a < 10
范围匹配，走range访问
ref_or_null
a = 5 OR a IS NULL
匹配值+NULL，走ref_or_null访问
in
a IN (1,2,3)
多值匹配，按范围处理
fulltext
全文索引条件
专门的全文索引优化

2.3.2 单索引范围优化：把条件“套进索引”

单索引的范围优化比较好理解，比如对索引idx_a (a)，查询WHERE a BETWEEN 10 AND 20，优化器会直接用这个索引的范围扫描。但有几个细节需要注意：

范围条件后的索引列“失效”？

有个流传很广的说法：“索引列在范围条件后会失效”，比如WHERE a > 10 AND b = 5，对索引(a,b)来说，b=5不会被用到。这其实不绝对！
官方文档里说，对(a,b)索引，a > 10 AND b = 5时，优化器会先找a > 10的范围，然后在这个范围内扫描b=5的行。虽然b不能用来定位范围起点，但能在扫描中过滤数据，比没有强。
我做过测试：在100万行的表上，(a,b)索引，a > 500000 AND b = 10这个查询，用索引比不用快3倍，因为b=5在索引扫描中就过滤了大部分数据，不用回表。

IN条件的优化：转成范围

IN后面跟多个值时，优化器会把它转成范围。比如a IN (1,3,5)会被当成a=1 OR a=3 OR a=5，然后合并成多个单点范围。如果值很多（比如超过100个），优化器会用“索引潜水”（index dive）或统计信息估算行数。
eq_range_index_dive_limit参数控制这个行为：值小于等于这个参数时用索引潜水（精确计数），大于时用统计信息（估算）。默认是200，调大这个值能提高准确性，但会增加优化时间：

2.3.3 多列索引的“跳扫”优化：Skip Scan

MySQL 8.0.13新增了Skip Scan优化，解决了多列索引中“前导列没条件”的问题。比如索引(a,b)，查询WHERE b = 5，以前只能全表扫或全索引扫，现在Skip Scan能这样优化：
  1. 先找到a的所有不同值（比如a=1,2,3）；
  1. 对每个a值，查a=1 AND b=5、a=2 AND b=5……
  1. 合并结果。
这比全表扫快多了，尤其是a的不同值不多时。比如a是部门ID（10个部门），b是员工状态，查“所有部门状态为‘active’的员工”，用Skip Scan能快10倍！
怎么知道用了Skip Scan？看EXPLAIN的type列，如果是range且Extra有Using index for skip scan，就是用上了：

2.3.4 范围优化器的源码探秘：QUICK_RANGE结构

范围优化器的核心数据结构是QUICK_RANGE（在sql/opt_range.h里），每个范围条件都会被转成这个结构：
比如a BETWEEN 10 AND 20会被转成：
  • min_key=10，min_included=true；
  • max_key=20，max_included=true。
get_quick_select_keys函数（在sql/opt_range.cc）负责把WHERE条件拆成QUICK_RANGE数组，然后计算每个索引的成本。这个函数有2000多行，逻辑复杂，但核心就是“拆条件→算范围→估成本”。

2.3.5 范围查询调优实战：从全表扫到索引扫

有个电商的商品表products，结构如下：
查询“category_id=10，price>100且status=1的在售商品”：
原始执行计划是全表扫描，因为没有price的索引。但我们可以利用覆盖索引和范围优化：

优化步骤：

  1. 加联合索引：(category_id, status, price)，包含所有条件列和查询列；
  1. 利用范围优化：category_id=10和status=1是等值条件，price>100是范围条件，刚好匹配索引顺序；
  1. 验证执行计划：type应该是range，Extra有Using index（覆盖索引）。

优化后执行计划：

查询时间从5秒降到0.1秒，效果立竿见影！

2.4 Condition Filtering：过滤效果的“预测术”

官方文档里说：“Condition filtering is a method used by the optimizer to estimate the number of rows that will be filtered by WHERE clause conditions that are not used to access the table.” 简单说，条件过滤就是优化器估算“那些没用来定位索引的条件”能过滤掉多少行，让成本估算更准。

2.4.1 为啥需要条件过滤？

举个例子：有个表users，索引idx_age (age)，查询：
优化器先用age > 30通过索引定位到1000行，然后需要估算这1000行里有多少行满足city = 'Beijing'。这个“过滤比例”就是条件过滤要算的。
没有条件过滤时，优化器会假设过滤比例是10%（默认值），但实际可能只有5%。条件过滤能根据统计信息算更准的比例，让成本估算更合理。

2.4.2 过滤效果的计算：从统计信息到过滤比例

官方文档里给了公式：
filtering_effect是0-1之间的数，越接近0说明过滤效果越好。
怎么算filtering_effect？分两种情况：

没有直方图时：用索引基数估算

比如city = 'Beijing'，优化器会查city列的基数（不同值的数量），假设总共有100个城市，就估算过滤效果是1/100=0.01。

有直方图时：直接查数据分布

8.0引入直方图后，优化器能更精准地估算。比如city列的直方图显示“Beijing”占5%的行数，过滤效果就是0.05。
生成直方图的命令：
查看直方图信息：

2.4.3 实战：用直方图提升估算准确性

有个社交APP的用户表，查询“age>25且gender='女'的用户”，索引是idx_age (age)。没直方图时，优化器假设gender='女'的过滤效果是0.5（默认值），估算行数=1000×0.5=500；但实际女性占30%，实际行数300。
加直方图后：
优化器通过直方图知道女性占30%，估算行数=1000×0.3=300，更接近实际。这时候优化器可能会选择不同的执行计划（比如是否用临时表排序），性能提升20%左右。

2.5 Hypergraph Optimizer：超图优化器的“智能决策术”

官方文档里说：“The hypergraph optimizer is a new query optimizer introduced in MySQL 8.0.22 that uses a hypergraph-based approach to find better join orders.” 超图优化器是8.0.22引入的新优化器，用超图模型来优化连接顺序，比旧的贪心算法更智能。

2.5.1 从“左深树”到“丛生树”：连接顺序的革命

旧优化器只能生成“左深树”（left-deep tree）的连接顺序，就像串糖葫芦：表A→表B→表C→表D，每次只能加一个新表。这种方式简单，但可能错过最优顺序。
超图优化器能生成“丛生树”（bushy tree），可以同时连接多个表组：比如（A→B）和（C→D）先分别连接，再把结果连接起来。这种方式能找到更优的连接顺序，尤其是多表连接时。
官方给的TPC-H测试数据显示，超图优化器在复杂查询上性能提升30%以上，比如Q3查询从4200ms降到1100ms！

2.5.2 超图的核心：用“超边”连接多个表

超图和普通图的区别是：普通图的边只能连两个顶点，超图的“超边”可以连多个顶点。在优化器里：
  • 顶点：表或表组（已经连接的多个表）；
  • 超边：连接条件，定义哪些表可以连接。
超图优化器的步骤：
  1. 把查询的表和连接条件转成超图；
  1. 枚举所有可能的“切分”（cut）方式，把超图分成两部分；
  1. 计算每种切分的成本，选成本最低的；
  1. 递归处理子图，直到生成完整的连接顺序。
这个过程比旧算法复杂，但能找到更优的连接顺序，尤其是表数量多的时候（比如10表以上连接）。

2.5.3 如何开启和使用超图优化器？

MySQL 8.0.22后默认开启超图优化器，也可以手动控制：
怎么知道用了超图优化器？看EXPLAIN FORMAT=JSON的optimizer字段：

2.5.4 超图优化器调优：内存与性能的平衡

超图优化器虽然智能，但计算成本更高，尤其是表多的时候可能消耗大量内存。可以通过optimizer_memory参数限制优化器使用的内存（默认1GB）：
如果遇到优化时间过长的问题，可以临时关闭超图优化器，或者简化查询（比如拆分子查询）。

结语：官方文档是最好的“调优字典”

这一章咱们把官方文档里关于优化器的核心章节拆了个遍，从优化器的基本流程到分区裁剪、范围优化、条件过滤，再到超图优化器，每个知识点都配了实战案例和源码片段。你可能会发现，很多之前觉得“玄学”的优化问题，在官方文档里都能找到答案。
记住：官方文档不是用来“背诵”的，而是用来“查阅”的。遇到慢查询时，对照文档里的优化规则，结合执行计划分析，你会发现优化器的决策逻辑其实很清晰。下一章，咱们就深入优化器超图，看看全球开发者们实战中总结的优化技巧，把官方知识和实战经验结合起来！
Keycloak 客户端授权服务MySQL优化器(1)编年史：从 “规则执行者” 到 “成本决策者” 的演化之路 读懂进化，掌控性能
Loading...
目录
0%
Honesty
Honesty
花には咲く日があり、人には少年はいない
统计
文章数:
108
目录
0%