第07篇：MySQL 执行计划与查询优化器深度解析：让慢 SQL 无处遁形

系列：MySQL 从基础到精深——Java SaaS 实战系列 · 第 07 篇
难度：⭐⭐⭐⭐☆ 适合中高级开发者
预计阅读时间：40 分钟
关键词：EXPLAIN 详解、MySQL 优化器 Cost 模型、Optimizer Trace、JOIN 顺序优化、filesort 消除、子查询优化、STRAIGHT_JOIN、SQL 审查自动化

速览摘要

本文深入 MySQL 查询优化器的决策机制，帮你真正读懂 EXPLAIN 输出（而不只是看"有没有走索引"）。第一部分系统解读 EXPLAIN 所有关键字段，重点讲 type 的警戒阈值、key_len 反推联合索引使用情况、Extra 各值的含义及优化方向。第二部分揭示 Cost-Based 优化器的估算逻辑，以及它"选错执行计划"的几种典型场景和强制干预手段。第三部分给出 JOIN 顺序、子查询、ORDER BY、GROUP BY 的系统化优化方案。第四部分提供 Optimizer Trace 的使用方法和 Java 侧的 SQL 自动审查工具链。

一、优化器是什么，为什么它有时会犯错

很多开发者有一个直觉：只要索引建对了，MySQL 就会把查询跑得很快。这个直觉大体是对的，但有一个被忽视的中间环节——优化器。优化器的职责是在所有可能的执行计划中，选择它认为"代价最小"的那个。注意是"它认为"——优化器的判断基于统计信息，统计信息是对实际数据分布的近似估算，而不是精确计数。当统计信息过期或数据分布极度不均匀时，优化器的估算就会出偏差，最终选出一个"在它看来最优、实际上很慢"的执行计划。

理解了这一点，你就能理解为什么有时候明明有索引，查询还是很慢——不是索引没建，而是优化器没选。也就明白了为什么 ANALYZE TABLE 有时候能让查询突然快起来——因为它更新了统计信息，让优化器重新做出更准确的决策。

二、EXPLAIN 详解：读懂执行计划的每一个字段

EXPLAIN 是 MySQL 自带的执行计划分析命令，是优化 SQL 性能最重要的工具。很多开发者对 EXPLAIN 的使用停留在"看看 key 列有没有值"的层面，这远远不够。完整地读懂 EXPLAIN 的每个字段，才能准确判断问题在哪里。

先用一个实际的 SaaS 查询场景来运行 EXPLAIN，后续的字段解读都基于这个例子：

EXPLAIN
SELECT
o.id,
o.order_no,
o.total_amount,
o.status,
o.created_at,
t.tenant_name
FROM orders o
JOIN tenant t ON t.id = o.tenant_id
WHERE o.tenant_id = 1001
AND o.status IN (1, 2)
AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20\\G

2.1 id 列：查询块的编号

id 表示查询中每个 SELECT 的序号。简单查询通常只有一个 id = 1；包含子查询或 UNION 的复杂查询会有多个 id。规律是：id 相同的行按顺序执行；id 不同时，id 越大的越先执行（因为大 id 通常是子查询，需要先得到结果才能继续外层查询）。

如果看到一个查询的 EXPLAIN 里有很多 id，说明这条 SQL 的结构很复杂，优化器要分拆成多个子步骤处理，通常是可以优化的信号。

2.2 select_type 列：查询类型

这个字段描述每个查询块属于哪种类型，最常见的几个值分别代表不同的查询结构。SIMPLE 是最理想的状态，表示没有子查询也没有 UNION，整个查询结构简单。PRIMARY 和 SUBQUERY 出现时，说明查询有嵌套，需要留意子查询的执行方式是否高效。DERIVED 出现时，表示 FROM 子句里有子查询（派生表），MySQL 会把派生表的结果物化到临时表，然后对这个临时表做后续操作——这是一个常见的性能隐患，因为临时表可能很大。

当你看到多个 DERIVED 行时，建议认真考虑能否用 CTE（MySQL 8.0 的 WITH 子句）或 JOIN 来改写，避免不必要的临时表。

2.3 type 列：访问类型，最重要的性能指标

type 是 EXPLAIN 中最值得关注的字段，它描述了 MySQL 用什么方式访问表的数据，从好到差的顺序是：

system 和 const 是最优的情况。const 表示通过主键或唯一索引做等值查找，最多返回一行——这是最快的访问方式，因为 B+树一次定位就找到，代价固定。在 SaaS 系统里，按 id 查单条记录时应该都是 const。

eq_ref 出现在 JOIN 场景中，表示被驱动表通过唯一索引与驱动表关联，每次只取一行。和 const 一样高效，只是出现在 JOIN 而不是单表查询中。

ref 是最常见的"好"状态，表示通过普通索引（非唯一）做查找，可能返回多行，但都是从某个特定的 B+树位置开始扫描，效率较高。

range 表示索引范围扫描，用于 BETWEEN、>、<、IN 等范围条件。只要范围不是特别大（不扫描超过表的 20% 左右），range 通常是可以接受的。

index 是全索引扫描，比全表扫描好（因为索引比数据行小，I/O 少），但仍然是遍历整个索引树，数据量大时很慢。看到 index 要思考：能否通过缩小范围条件把它变成 range？

ALL 是全表扫描，每次都要读完整张表。在小表上可以接受，在百万行以上的表上几乎永远是需要优化的信号。生产告警规则：type = ALL 且 rows > 10000，必须优化后才能上线。

— 快速判断是否需要优化的经验规则（可作为 Code Review 检查点）：
— type = ALL → 必须优化（除非 rows 很小，如 < 1000）
— type = index → 需要评估（rows 大时需要优化）
— type = range → 通常可以接受，但要看 rows 数量
— type = ref 或更好 → 通常没有问题

2.4 key 和 key_len 列：实际使用的索引和长度

key 显示实际选中的索引名，possible_keys 显示所有候选索引。当 possible_keys 有值但 key 为 NULL 时，说明有可用的索引但优化器没有选——通常是因为优化器估算全表扫描比用索引更便宜（可能是表很小，或者索引的选择性很差）。

key_len 是使用了索引的字节数，通过它可以精确判断联合索引用了几列。很多开发者写了联合索引却不知道实际上只用了第一列，key_len 是发现这个问题的关键。

计算 key_len 的规则：每个字段的字节数等于其类型长度，NULL 字段额外加 1 字节（存储是否为 NULL 的标志位），VARCHAR 还需要额外加 2 字节（存储实际长度）。

举例：索引 (tenant_id BIGINT NOT NULL, status TINYINT NOT NULL, created_at DATETIME(3) NOT NULL)
tenant_id : 8 字节（BIGINT）
status : 1 字节（TINYINT）
created_at: 7 字节（DATETIME 秒精度 5 字节 + 毫秒 3 位额外 2 字节）

如果 key_len = 9 → 使用了 tenant_id(8) + status(1)，共两列
如果 key_len = 16 → 使用了全部三列（8+1+7=16）
如果 key_len = 8 → 只使用了 tenant_id 一列

当你发现 key_len 比预期小，说明后面的联合索引列没有被利用，
需要检查 WHERE 条件是否违反了最左前缀原则，或者某列的类型和传入参数不匹配（导致索引失效）。

2.5 rows 列：估算扫描行数

rows 是优化器估算本步骤需要扫描的行数，基于统计信息计算，不是精确值。它的价值在于：数量级判断（扫描 100 行和扫描 100 万行是本质不同的）；以及在 JOIN 中，每个表的 rows 的乘积代表了最坏情况下的总扫描量。

当 rows 估算严重偏离实际时（可以通过 EXPLAIN ANALYZE 在 MySQL 8.0.18+ 中看到实际行数），先执行 ANALYZE TABLE table_name 更新统计信息，再重新 EXPLAIN，很多时候执行计划会自动改善。

2.6 Extra 列：最丰富的诊断信息

Extra 字段包含了优化器在执行这个查询时使用的额外技术手段，是诊断性能问题最信息密集的字段。

Using index 是最好的状态——覆盖索引命中，不需要回表。如果你的高频查询没有这个标志，可以考虑是否能通过把 SELECT 的列加入索引来实现覆盖索引。

Using index condition 表示索引下推（ICP）生效，存储引擎在索引层就做了额外过滤，减少了回表次数。这是一个好的信号，说明 MySQL 正在做优化。

Using where 表示 Server 层在存储引擎返回结果后还做了额外过滤。这本身不一定是坏事（有些过滤条件确实无法在索引层完成），但如果 rows 很大且有 Using where，说明存储引擎返回了大量行，大部分都被 Server 层过滤掉，索引的过滤效率不高。

Using filesort 是一个需要注意的信号，意味着 ORDER BY 没有能利用索引的有序性，MySQL 需要额外做一次排序。“filesort” 这个名字有误导性——它不一定是文件排序，小结果集会在内存中排序，大结果集才会溢写到磁盘。但无论如何，额外排序都有开销，特别是在高并发时会消耗大量内存。消除 filesort 的方法是建立包含 ORDER BY 列的索引（且该列在索引中的顺序要和 ORDER BY 的顺序一致）。

Using temporary 是比 Using filesort 更需要关注的信号，意味着查询中用到了临时表（通常是 GROUP BY 或 DISTINCT 操作，且无法用索引满足）。临时表既消耗内存，又消耗 CPU，在高并发时会成为严重的性能瓶颈。消除方法是为 GROUP BY 列建立索引，使 MySQL 可以利用索引的有序性直接分组，而不需要先物化结果再排序。

当 Extra 同时出现 Using filesort 和 Using temporary 时，是最坏的组合——既要建临时表，又要在临时表上排序，内存和 CPU 双重压力。这种查询在并发 10 个以上时往往会让数据库服务器的 CPU 飙满。

三、优化器的 Cost 模型与"选错计划"的根本原因

MySQL 的优化器是 Cost-Based（基于代价），它会评估所有可能的执行方案，选择估算代价最低的。代价的计算公式大致是：

执行代价 = (需要读取的数据页数 × I/O 代价权重)
+ (需要比较/计算的行数 × CPU 代价权重)

这个计算依赖两个关键输入：统计信息（表的行数、索引的区分度、数据的分布情况）和系统参数（I/O 和 CPU 的相对代价权重，在 mysql.server_cost 和 mysql.engine_cost 表中可以查看和调整）。

优化器"选错"执行计划的情况，几乎都可以归因到这两个输入的偏差：

统计信息过期或不准。表的数据量快速增长后，统计信息还停留在旧状态，优化器误以为表很小，选了全表扫描；或者某个索引的区分度统计不准，优化器误以为用索引反而比全表扫描扫更多数据。ANALYZE TABLE 可以强制更新统计信息。MySQL 也有自动统计信息更新机制（当数据变化超过 10% 时触发），但对于数据量极大的表，这个触发条件可能很久都达不到。

数据分布极度不均匀。比如一张订单表，99% 的订单状态是"已完成"（status = 3），1% 是"待处理"（status = 0）。如果你查询 WHERE status = 0，优化器应该用索引快速定位这 1% 的数据；但如果统计信息显示 status 列的区分度很低（确实低，因为大部分值都是 3），优化器可能误判为"用索引收益不大，全表扫描算了"。这种情况用 FORCE INDEX 强制指定索引，或者用 Optimizer Hint 调整优化器决策。

四、Optimizer Trace：看见优化器的思考过程

当 EXPLAIN 告诉你用了某个执行计划，但你不理解"为什么是这个"，就可以打开 Optimizer Trace，看到优化器评估每个方案的详细代价计算过程：

— 开启 Optimizer Trace（当前会话）
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1000000; — 增大 Trace 的内存限制（默认太小）

— 执行你要分析的 SQL
SELECT * FROM orders
WHERE tenant_id = 1001 AND status = 1
ORDER BY created_at DESC LIMIT 20;

— 查看 Trace 结果（JSON 格式，内容很长）
SELECT * FROM information_schema.OPTIMIZER_TRACE\\G

— 分析完之后关闭（不要忘记，Trace 会显著增加每次查询的开销）
SET optimizer_trace = 'enabled=off';

Optimizer Trace 的输出是一个大段 JSON，初次看会让人不知所措。实际上只需要关注几个关键节点：considered_execution_plans（优化器评估的所有候选执行计划列表）里每个方案的 cost 字段；以及 best_access_path（每个表的最优访问路径）中为什么某些索引被排除了（会有 cause 字段说明原因）。

如果你发现优化器选择的执行计划代价比另一个更高，说明统计信息有问题，先做 ANALYZE TABLE；如果更新统计信息后仍然"选错"，考虑用 Hint 强制干预。

五、强制干预优化器：什么时候以及如何使用 Hint

让优化器自动选择通常是最好的——人工强制的方案可能在数据分布变化后失效，而优化器会自动适应。但在某些场景下，确实需要人工介入，干预方式有以下几种。

FORCE INDEX：强制使用指定索引。适用于已经确认索引正确但优化器没有选择的情况，是最直接的干预手段：

— 强制使用 idx_tenant_status_created 索引
SELECT * FROM orders FORCE INDEX (idx_tenant_status_created)
WHERE tenant_id = 1001 AND status = 1
ORDER BY created_at DESC LIMIT 20;

使用 FORCE INDEX 之前，必须通过 EXPLAIN 确认这个索引确实比优化器自己选的方案更优——不要凭感觉，要看数据。另外，当表的数据分布发生变化（比如之前 status=1 只有 1% 的行，后来变成了 50%），硬编码的 FORCE INDEX 可能从"加速"变成"减速"，这是它的主要风险。

STRAIGHT_JOIN：固定 JOIN 的顺序，按 SQL 书写顺序以左表为驱动表。适用于优化器选错了驱动表的情况：

— 强制 orders 作为驱动表，tenant 作为被驱动表
SELECT STRAIGHT_JOIN
o.*, t.tenant_name
FROM orders o
JOIN tenant t ON t.id = o.tenant_id
WHERE o.tenant_id = 1001;

STRAIGHT_JOIN 适合的场景是：你通过 EXPLAIN 看到优化器选择了一个很大的表作为驱动表，而你知道另一张表在 WHERE 过滤后行数更少，应该作为驱动表。同样，使用前要有数据支撑。

Optimizer Hints（MySQL 8.0 推荐）：比 STRAIGHT_JOIN 更灵活的新式 hint 语法：

— 指定 JOIN 顺序（orders 先，tenant 后）
SELECT /*+ JOIN_ORDER(o, t) */
o.*, t.tenant_name
FROM orders o
JOIN tenant t ON t.id = o.tenant_id
WHERE o.tenant_id = 1001;

— 强制使用特定索引（比 FORCE INDEX 更精确）
SELECT /*+ INDEX(o idx_tenant_status_created) */
o.id, o.total_amount
FROM orders o
WHERE o.tenant_id = 1001 AND o.status = 1;

Optimizer Hints 的语法更清晰，且不影响 SQL 的语义结构（放在注释里），是 MySQL 8.0 中推荐的干预方式。

六、JOIN 优化：驱动表选择与 Join Buffer

多表 JOIN 的性能很大程度上取决于驱动表（外层循环）的行数——驱动表每有一行，被驱动表就要做一次查找。因此，驱动表应该是过滤后行数最少的表。

很多开发者以为优化器总能选对驱动表，但在以下场景中它常常选错：没有 WHERE 条件过滤（两张表都很大）、统计信息不准（误判过滤后的行数）、多表 JOIN 时搜索空间太大（3 张以上的表，优化器的搜索时间有限制）。

当被驱动表没有合适的索引可以做关联，MySQL 会使用 Join Buffer——把驱动表的数据先缓冲到内存，然后对被驱动表做一次全表扫描，在内存中做匹配。这个行为会出现在 Extra 列的 Using join buffer (Block Nested Loop) 中，是需要优化的信号（解法是为被驱动表的关联列建立索引）。

— 分析 JOIN 性能的步骤：

— Step 1：EXPLAIN 看每个表的 type 和 rows，找出哪个表的访问效率低
EXPLAIN SELECT o.*, t.tenant_name
FROM orders o
JOIN tenant t ON t.id = o.tenant_id
WHERE o.tenant_id = 1001 AND o.status = 1\\G

— Step 2：如果 Extra 出现 Using join buffer，说明被驱动表没有利用索引
— 解法：确认被驱动表的关联列（这里是 tenant.id）有索引（主键就是索引，通常没问题）
— 如果关联的是非主键列，需要为该列建索引

— Step 3：如果驱动表选错（较大的表被作为驱动表），用 STRAIGHT_JOIN 或 Hint 干预

七、ORDER BY 优化：消除 filesort 的系统方法

ORDER BY 的性能取决于 MySQL 能否利用索引的有序性直接获取有序结果，从而跳过额外的排序步骤。

要消除 filesort，ORDER BY 的列必须满足以下条件：出现在索引中；如果查询有 WHERE 条件，WHERE 用到的等值条件列在索引中排在 ORDER BY 列之前；ORDER BY 中多列的排序方向必须一致（要么全升序，要么全降序，混合方向无法用索引）。

— 假设索引：(tenant_id, status, created_at)

— 可以消除 filesort：WHERE 的等值列 + ORDER BY 列，符合索引顺序
SELECT * FROM orders
WHERE tenant_id = 1001 AND status = 1
ORDER BY created_at DESC LIMIT 20;
— EXPLAIN Extra: NULL（没有 filesort，因为 WHERE 的等值条件 + ORDER BY 列正好是索引前缀）

— 无法消除 filesort：ORDER BY 混合了升降序
SELECT * FROM orders
WHERE tenant_id = 1001
ORDER BY status ASC, created_at DESC LIMIT 20;
— 一列升序、一列降序，索引无法覆盖，必须 filesort
— MySQL 8.0 的 Descending Index 可以部分解决这个问题

MySQL 8.0 引入了降序索引（Descending Index），允许在建索引时为每列单独指定升降序，解决了混合排序无法消除 filesort 的问题：

— MySQL 8.0：建立混合排序方向的索引
CREATE INDEX idx_tenant_status_time
ON orders (tenant_id ASC, status ASC, created_at DESC);

— 现在这个查询可以直接用索引，不需要 filesort
SELECT * FROM orders
WHERE tenant_id = 1001 AND status = 1
ORDER BY created_at DESC LIMIT 20;

八、子查询优化：从相关子查询到 JOIN 的改写策略

子查询是 SQL 中最容易产生性能问题的结构之一，但并非所有子查询都需要改写，关键是区分两类子查询的性能特征。

非相关子查询（子查询不依赖外层查询的列）通常只执行一次，性能影响有限，MySQL 优化器也常常会自动把它优化成 JOIN。需要特别关注的是相关子查询（Correlated Subquery），它对外层查询的每一行都执行一次，当外层结果集有 N 行时，子查询执行 N 次，整体复杂度 O(N²)。

第 02 篇已经介绍了相关子查询改写为 JOIN 的基本方法，这里补充一个在 SaaS 系统中很常见的场景——“每组取 TopN”：

— 场景：每个租户取金额最高的前 3 笔订单

— 写法一：相关子查询（N² 复杂度，租户多时极慢）
— 对 orders 表的每一行 o1，都要执行一次统计当前租户内有多少笔金额比它高的子查询
— 假设有 100 个租户、每个租户 1000 条订单，就是 10 万次子查询执行
SELECT o1.*
FROM orders o1
WHERE (
SELECT COUNT(*)
FROM orders o2
WHERE o2.tenant_id = o1.tenant_id
AND o2.total_amount > o1.total_amount
) < 3;

这个写法在小数据量时看不出问题，但数据量上去之后，查询时间会随订单数量的平方级别增长。

— 写法二：MySQL 8.0 窗口函数（推荐）
— 整张表只需扫描一遍，在扫描过程中计算每行在其租户内的排名
— 复杂度 O(N log N)（排序），远优于 O(N²)

WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY tenant_id
ORDER BY total_amount DESC
) AS rn
FROM orders
WHERE status = 1
)
SELECT tenant_id, order_no, total_amount, rn
FROM ranked
WHERE rn <= 3
ORDER BY tenant_id, rn;

这里用 WITH CTE（公共表表达式）把排名计算封装成一个具名的中间结果，然后在外层查询中过滤 rn <= 3。这种写法不仅性能更好，可读性也远优于相关子查询——任何人看到这段 SQL，不需要额外解释就能理解"按租户分组，取金额前 3 名"的意图。在 MySQL 5.7 及以前没有窗口函数时，这类需求不得不在应用层用多次 SQL + Java 代码拼接，升级到 8.0 后，这是最值得迁移的一类查询模式。

九、GROUP BY 优化：索引分组 vs 临时表分组

GROUP BY 操作如果无法利用索引，就会使用临时表——把所有数据先放入临时表，然后对临时表做分组和聚合。当分组的数据量很大时，临时表可能超出 tmp_table_size 限制，从内存临时表变成磁盘临时表，性能急剧下降。

能否利用索引做 GROUP BY，同样遵循最左前缀原则。如果 GROUP BY 的列恰好是联合索引的前缀，MySQL 可以按索引顺序扫描，直接做流式聚合，不需要临时表：

— 假设索引：(tenant_id, status, created_at)

— 可以利用索引分组：GROUP BY 列是索引前缀
SELECT tenant_id, status, COUNT(*), SUM(total_amount)
FROM orders
WHERE tenant_id = 1001 — 等值条件，可以进一步缩小范围
GROUP BY tenant_id, status;
— EXPLAIN Extra: Using index（或 Using index for group-by），不需要临时表

— 需要临时表：GROUP BY 的列不是索引前缀
SELECT DATE(created_at) AS day, COUNT(*)
FROM orders
WHERE tenant_id = 1001
GROUP BY DATE(created_at); — DATE() 函数，破坏了索引的有序性
— EXPLAIN Extra: Using temporary; Using filesort

— 优化方案：为 DATE(created_at) 创建函数索引（MySQL 8.0）
ALTER TABLE orders
ADD INDEX idx_tenant_date ((tenant_id), (DATE(created_at)));
— 或者：增加虚拟生成列，对生成列建索引
ALTER TABLE orders
ADD COLUMN order_date DATE GENERATED ALWAYS AS (DATE(created_at)) VIRTUAL,
ADD INDEX idx_tenant_order_date (tenant_id, order_date);

十、Java 侧的 SQL 自动审查：从开发阶段拦截问题 SQL

性能问题发现得越早，修复成本越低。理想情况是在开发阶段的 Code Review 或 CI 流程中就拦截可能有问题的 SQL，而不是等到上线后从告警里发现。

下面提供一个基于 Spring AOP 和 LLM 的 SQL 自动审查工具，它在测试/开发环境对所有执行的 SQL 做 EXPLAIN 分析，并用 AI 生成简洁的审查意见：

/**
* SQL 审查切面：在开发/测试环境拦截 SQL 执行，做 EXPLAIN 分析。
*
* 为什么不在生产环境启用？
* EXPLAIN 本身有一定开销，对每条 SQL 都做 EXPLAIN 会让性能测试结果失真。
* 开发和测试阶段使用，帮助在问题到达生产之前暴露出来。
*
* 为什么用 AOP 而不是 P6Spy？
* AOP 可以和 Spring 生态深度集成，拿到方法级别的上下文信息（哪个 Service 的哪个方法），
* 审查报告可以精确定位到代码位置，而 P6Spy 只能看到 SQL 字符串。
*/
@Aspect
@Component
@Profile({"dev", "test"}) // 只在开发和测试环境激活，生产环境不启用
@Slf4j
public class SqlReviewAspect {

@Autowired
private JdbcTemplate jdbcTemplate;

@Autowired
private ChatClient chatClient;

// 只审查 SELECT 语句（写操作不需要 EXPLAIN）
// 只审查超过一定复杂度的 SQL（太简单的跳过，节省审查资源）
private static final int MIN_SQL_LENGTH_TO_REVIEW = 100;

@Around("@annotation(org.apache.ibatis.annotations.Select) || " +
"@annotation(org.springframework.data.jpa.repository.Query)")
public Object reviewAndProceed(ProceedingJoinPoint pjp) throws Throwable {
// 先执行原始方法（获取结果）
Object result = pjp.proceed();

// 在方法执行完成后，异步进行 SQL 审查（不阻塞主流程）
// 注意：这里的 SQL 是从注解中读取的静态 SQL，适合做静态审查
// 动态参数绑定后的 SQL 需要结合 P6Spy 拦截器获取
String methodName = pjp.getSignature().toShortString();
asyncReview(methodName);

return result;
}

private void asyncReview(String methodName) {
CompletableFuture.runAsync(() -> {
// 此处可以结合 MyBatis 的 MappedStatement 获取实际 SQL
// 简化示例：直接用方法名作为上下文，实际实现需要结合具体框架 API
log.debug("[SQL 审查] 方法 {} 的 SQL 已提交审查队列", methodName);
});
}

/**
* 对给定的 SQL 做完整审查：获取 EXPLAIN 结果，并调用 AI 生成审查意见。
*
* 这个方法可以在 CI 流程中调用（将所有 Mapper SQL 提取出来批量审查），
* 也可以在开发阶段手动调用（对某条可疑 SQL 做即时分析）。
*/
public SqlReviewReport review(String sql, Map<String, Object> sampleParams) {
if (sql == null || sql.trim().length() < MIN_SQL_LENGTH_TO_REVIEW) {
return SqlReviewReport.skipped("SQL 过短，跳过审查");
}

if (!sql.trim().toUpperCase().startsWith("SELECT")) {
return SqlReviewReport.skipped("非 SELECT 语句，跳过审查");
}

// 获取 EXPLAIN 结果：用示例参数替换 SQL 中的占位符，生成可执行的 SQL
String explainResult = getExplainAsText(sql, sampleParams);

// 构建给 LLM 的审查 Prompt
// 给 LLM 一个明确的专家角色定位，并列出具体的审查维度，能显著提升输出质量
String prompt = """
你是 MySQL 数据库性能专家，负责对以下 SQL 做 Code Review。

审查维度（按重要性排序）：
1. 全表扫描风险：EXPLAIN 的 type 是否为 ALL 或 index，rows 估算是否过大
2. 索引失效：是否有函数操作列、隐式类型转换、违反最左前缀等情况
3. 排序开销：是否有 Using filesort，能否通过调整索引消除
4. 临时表：是否有 Using temporary，原因是什么
5. N+1 风险：SQL 结构是否暗示调用方可能在循环中执行它
6. 返回列过多：是否 SELECT * 可以改为按需选列

SQL：
%s

EXPLAIN 结果：
%s

输出格式（JSON）：
{
"riskLevel": "HIGH/MEDIUM/LOW/OK",
"summary": "一句话概括主要问题",
"issues": ["具体问题1", "具体问题2"],
"suggestions": ["具体优化建议1（含 SQL 或 DDL 示例）"],
"canMergeToIndex": true/false // 是否可以通过调整索引解决
}

只返回 JSON，不加任何额外说明。
""".formatted(sql, explainResult);

try {
String response = chatClient.prompt(prompt).call().content();
return SqlReviewReport.fromJson(response, sql);
} catch (Exception e) {
log.warn("AI SQL 审查失败: {}", e.getMessage());
return SqlReviewReport.error("AI 审查服务不可用", sql);
}
}

private String getExplainAsText(String sql, Map<String, Object> params) {
// 简化处理：直接执行 EXPLAIN（实际项目中需要处理参数绑定）
try {
List<Map<String, Object>> rows = jdbcTemplate.queryForList("EXPLAIN " + sql);
StringBuilder sb = new StringBuilder();
for (Map<String, Object> row : rows) {
sb.append(String.format(
"table=%-20s type=%-10s key=%-30s key_len=%-6s rows=%-10s Extra=%s%n",
row.get("table"), row.get("type"), row.get("key"),
row.get("key_len"), row.get("rows"), row.get("Extra")
));
}
return sb.toString();
} catch (Exception e) {
return "EXPLAIN 执行失败：" + e.getMessage();
}
}
}

这段审查工具有几点设计考量值得说明。@Profile({"dev", "test"}) 确保它只在非生产环境激活，这是必要的安全边界——生产环境每条 SQL 额外跑一次 EXPLAIN 会带来明显的性能开销。异步审查（CompletableFuture.runAsync）确保审查不阻塞主业务逻辑，即使 AI 服务响应慢，用户也不会感知到等待。AI Prompt 明确列出了审查维度的优先级，让 LLM 的输出更聚焦于真正重要的问题，而不是泛泛而谈。JSON 格式的输出便于程序化处理——比如把 riskLevel = HIGH 的审查结果推送到团队的 Slack 频道，或者在 CI 流程中把高风险 SQL 作为 PR 审查的标注。

十一、本篇小结与下篇预告

本篇建立了一套系统性的 SQL 性能分析框架：从 EXPLAIN 的每个字段读出的信号（type 警戒线、key_len 反推索引使用列数、Extra 的诊断含义）到优化器的 Cost 模型和"选错计划"的根本原因，从强制干预手段（FORCE INDEX、STRAIGHT_JOIN、Optimizer Hints）到 JOIN 优化、ORDER BY 消除 filesort、GROUP BY 消除临时表的系统方法，最后到 Java 侧 SQL 自动审查工具的完整实现。

这篇文章的内容密度比较高，建议把"SQL 自动审查"的思路落地到你的项目里——哪怕只是在开发环境对高频 SQL 手动跑一遍 EXPLAIN，也能发现很多潜在的性能问题。

第 08 篇预告：这是本系列的核心专题——MySQL 5.7 → 8.0 升级实战全指南。我们会系统讲解 8.0 的重大新特性（窗口函数、CTE、函数索引、JSON 增强），逐一梳理破坏性变化（认证插件变更、字符集默认值改变、SQL 严格模式收紧、移除的功能），并提供 SaaS 系统的零停机升级操作手册（预检工具使用、滚动升级流程、回滚预案）和 Java 代码适配清单。这是系列中字数最多、工程价值最高的一篇，专为正在主导或计划 5.7 升级的团队设计。

FAQ

Q：EXPLAIN 显示 type=ALL，但查询其实很快，需要优化吗？

A：需要结合 rows 字段判断。如果 rows 很小（比如表只有几百行），全表扫描的实际代价比走索引还小（索引查找有固定开销），优化器选全表扫描是正确的。真正需要优化的是 type=ALL 且 rows 超过几千行的情况，特别是在高并发时，全表扫描会显著占用 I/O 资源。

Q：什么情况下应该选择 FORCE INDEX，什么情况下应该调整索引设计？

A：FORCE INDEX 是应急手段，不是长期方案。它绕过了优化器的自动决策，在数据分布变化后可能反而变慢，且代码可维护性差。真正的解法是理解"为什么优化器没有选这个索引"：如果是统计信息过期，ANALYZE TABLE 解决；如果是索引设计不符合查询模式（比如缺少覆盖索引列），调整索引设计解决。FORCE INDEX 只作为紧急止血措施，之后仍然要从根本上解决。

Q：EXPLAIN ANALYZE 和普通 EXPLAIN 有什么区别？

A：EXPLAIN ANALYZE（MySQL 8.0.18+ 支持）会真正执行查询，然后对比优化器的估算行数和实际扫描行数，是诊断"优化器估算偏差"最直接的工具。普通 EXPLAIN 只做估算，不实际执行，适合日常分析；EXPLAIN ANALYZE 因为实际执行，对 SELECT 有网络和 I/O 开销，对 DML 操作要非常谨慎（会真正修改数据），正式使用前要确认操作类型。

如果这篇文章对你有帮助，欢迎点赞收藏，你的支持是持续更新的动力。有问题欢迎在评论区留言，我会逐一回复。