MySQL 8.4 LTS 性能调优:执行计划深度解读

上期内容我们介绍了 MySQL 8.4 LTS 性能调优:索引优化策略 ，接下来我们继续聊聊如何在 MySQL 8.4 LTS 中精准读懂查询计划。

01. EXPLAIN三剑客：从传统表格到现代树形

MySQL的执行计划输出格式经历了三代演进，每种格式都有其独特的适用场景。

三种格式对比：

格式	命令	核心优势	适用场景
传统表格	EXPLAIN	简洁直观，兼容所有版本	快速索引检查
JSON格式	EXPLAIN FORMAT=JSON	成本信息详细，可编程解析	自动化性能分析
TREE格式	EXPLAIN FORMAT=TREE	执行顺序清晰，操作层级分明	复杂JOIN分析
ANALYZE	EXPLAIN ANALYZE	实际执行时间，真实行数	精确性能诊断

请注意，EXPLAIN ANALYZE 会实际执行查询，因此如果用于非常复杂的查询，可能需要很长时间才能产生结果。

案例：JSON格式的成本信息

-- 准备测试数据
CREATE TABLE country (
    code CHAR(3) NOT NULL,
    name VARCHAR(100) NOT NULL,
    continent VARCHAR(50),
    population INT UNSIGNED DEFAULT 0,
    PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO country (code, name, continent, population) VALUES
('JPN', 'Japan', 'Asia', 125000000),
('USA', 'United States', 'North America', 331000000);

-- 查看执行计划
mysql> EXPLAIN FORMAT=JSON SELECT * FROM country\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.45"  -- 总查询成本
    },
    "table": {
      "table_name": "country",
      "access_type": "ALL", -- 全表扫描
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.20",
        "prefix_cost": "0.45",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "code",
        "name",
        "continent",
        "population"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

案例：ANALYZE的实际执行

-- EXPLAIN ANALYZE 显示实际执行时间和行数
mysql> EXPLAIN ANALYZE SELECT * FROM country\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on country  (cost=0.45 rows=2) (actual time=0.0245..0.0285 rows=2 loops=1)

1 row in set (0.00 sec)

注意：actual time是实际执行时间，cost是优化器估算.

02. 核心字段解读：type、key_len与Extra的陷阱

在数据库实际管理过程中，需要明确知晓执行计划字段的精确含义，这些细节往往决定着索引是否被正确使用。

案例：key_len 的计算奥秘

-- 准备测试数据
CREATE TABLE City (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Population INT NOT NULL,
    INDEX pop_idx (Population)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO City (Name, Population) VALUES
('Target City', 10023),
('Small Town', 500),
('Big Metropolis', 5000000),
('Old Village', 10023),
('Other City', 25000);

-- 查看执行计划
mysql> EXPLAIN SELECT Name, Population FROM City WHERE Population=10023\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: City
   partitions: NULL
         type: ref
possible_keys: pop_idx
          key: pop_idx
      key_len: 4
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

输出结果可以观察到：key: pop_idx ， key_len: 4 ， 这里的 key_len 表示 INT 类型占4字节

如果是 NULL 列，key_len = 4 + 1（NULL标记位）

如果是 VARCHAR(n)， 在utf8mb4下：n x 3 + 2（长度前缀）+ 1（如果允许NULL）

Extra字段的深层含义：

Extra值 [1, 2, 3, 4, 5]	含义	性能影响	优化建议
Using where	使用 WHERE 句进行过滤	需检查是否有效利用索引	确保过滤列已建立索引，减少扫描行数
Using index	覆盖索引（Covering Index）	优！ 无需访问表数据（回表）	尽量让查询列都包含在索引中
Using temporary	使用临时表保存中间结果	差！ 可能触发磁盘 I/O	优化 GROUP BY 或 DISTINCT 涉及的列，匹配索引顺序
Using filesort	文件排序（无法利用索引排序）	差！ 消耗 CPU 和内存	为 ORDER BY 的列添加索引
Using index condition	索引下推（ICP 优化）	良！ 在存储引擎层过滤数据	MySQL 5.6+ 默认开启，尽量利用复合索引的前缀
Using join buffer	使用连接缓冲（通常是 BNL 算法）	中！ 常见于无索引的表连接	为 JOIN 关联条件的列添加索引

补充说明:

• Using temporary; Using filesort：这两个值同时出现通常意味着性能严重下滑，需要关注改进。
• Using index condition：在高并发场景下非常有用，因为它减少了存储引擎（InnoDB）向 MySQL 服务器层传递的数据量。

案例：聚簇索引识别

-- 表结构
CREATE TABLE employees (
  emp_no int NOT NULL,
  birth_date date NOT NULL,
  first_name varchar(14) NOT NULL,
  last_name varchar(16) NOT NULL,
  network_name varchar(15) NOT NULL,
  gender enum('M','F') NOT NULL,
  hire_date date NOT NULL,
  PRIMARY KEY (emp_no),
  UNIQUE KEY network_name (network_name),
  KEY hire_date (hire_date)
) ENGINE=InnoDB;

PRIMARY KEY (emp_no), 这就是聚簇索引. hire_date 是二级索引.

03. 视图算法与执行计划优化

升级 MySQL 版本时，SQL 性能波动也是需要重点关注的对象。

案例： TEMPTABLE 算法的影响

-- 创建视图（隐含TEMPTABLE算法的情况）
CREATE VIEW myview AS
SELECT * FROM city WHERE Name='Rome';

-- 执行计划显示
mysql> EXPLAIN SELECT * FROM mydb.myview WHERE Name="Rome"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

原因：myview使用了TEMPTABLE算法，无法合并优化

案例：AI场景中的视图优化

在AI特征工程中，经常使用视图封装复杂查询。建议显式指定MERGE算法，确保HeatWave等引擎能下推谓词优化。

-- AI特征视图
CREATE ALGORITHM=MERGE VIEW v_user_features AS
SELECT 
    user_id,
    AVG(session_duration) as avg_duration,
    COUNT(DISTINCT session_id) as session_count
FROM user_behavior
GROUP BY user_id;

-- 查询时谓词下推
SELECT * FROM v_user_features WHERE user_id = 123;
-- MERGE算法确保WHERE user_id = 123被下推到基表，避免全表扫描

04. MySQL 8.4 新特性：执行计划的现代化演进

MySQL 8.4 LTS 的执行计划诊断能力有了质的飞跃，特别是JSON格式的版本化和MRR优化的可视化。

explain_json_format_version=2（MySQL 8.4）

-- 切换到新版JSON格式（基于Access Paths）
SET SESSION explain_json_format_version = 2;

-- 对比版本1和版本2的输出差异
EXPLAIN FORMAT=JSON SELECT 1\G

-- 版本1（传统）：
mysql> EXPLAIN FORMAT=JSON SELECT 1\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "message": "No tables used"
  }
}
1 row in set, 1 warning (0.00 sec)


-- 版本2（8.4+）：
mysql> EXPLAIN FORMAT=JSON SELECT 1\G
*************************** 1. row ***************************
EXPLAIN: {
  "query": "/* select#1 */ select 1 AS `1`",
  "operation": "Rows fetched before execution",
  "query_type": "select",
  "access_type": "rows_fetched_before_execution",
  "estimated_rows": 1.0,
  "estimated_total_cost": 0.0,
  "estimated_first_row_cost": 0.0
}
1 row in set (0.00 sec)

FOR SCHEMA 子句（MySQL 8.4）

在不切换默认数据库的情况下查看其他schema的执行计划，无需USE切换即可诊断跨库查询

EXPLAIN FORMAT=TREE FOR SCHEMA mydb SELECT * FROM t WHERE c2 > 50\G

Multi-Range Read 可视化（MySQL 9.1.0）

-- 创建测试表（来自资料中的MySQL 9.1.0示例）
CREATE TABLE orders (
  id int NOT NULL AUTO_INCREMENT,
  key_column int DEFAULT NULL,
  order_no varchar(50) DEFAULT NULL,
  description text,
  PRIMARY KEY (id),
  KEY idx_key (key_column)
) ENGINE=InnoDB;

-- 强制开启MRR（忽略成本评估）
SET optimizer_switch='mrr=on,mrr_cost_based=off';

-- 执行查询并获取JSON计划
EXPLAIN FORMAT=JSON INTO @myq 
SELECT * FROM orders 
WHERE key_column BETWEEN 10 AND 100;

-- 查看结果（MySQL 9.1.0+新特性）
SELECT JSON_EXTRACT(@myq, "$.query_plan.multi_range_read");
-- 结果
mysql> SELECT JSON_EXTRACT(@myq, "$.query_plan.multi_range_read");
+-----------------------------------------------------+
| JSON_EXTRACT(@myq, "$.query_plan.multi_range_read") |
+-----------------------------------------------------+
| true                                                |
+-----------------------------------------------------+
1 row in set (0.000 sec)


-- 完整执行计划包含：
mysql> SELECT JSON_EXTRACT(@myq, "$.query_plan.operation")\G
*************************** 1. row ***************************
JSON_EXTRACT(@myq, "$.query_plan.operation"): "Index range scan (Multi-Range Read) on orders using idx_key over (10 <= key_column <= 100), with index condition: (orders.key_column between 10 and 100)"
1 row in set (0.000 sec)

MySQL 8.4 对成本估算的数字格式进行了优化，0.001到999999.5范围内的数字以十进制形式输出，不再使用科学计数法，这让执行计划的可读性大幅提升。

总结

今天内容就到这里，我们做个总结。

1. 格式选择策略：快速检查用传统EXPLAIN，自动化监控用FORMAT=JSON（8.4+推荐version=2），复杂JOIN用FORMAT=TREE，真实性能用EXPLAIN ANALYZE。
2. 字段精读要点：key_len计算决定索引使用效率，Extra中的Using filesort和Using temporary是优化红灯，尽量优化消除。
3. 视图算法警觉：TEMPTABLE算法阻碍优化，且导致视图不可更新，AI特征视图应显式声明 ALGORITHM=MERGE 确保谓词下推。
4. 版本新特性落地：MySQL 8.4的 explain_json_format_version=2 提供更精确的成本分解，9.1.0 的 multi_range_read 字段可视化帮助确认MRR优化生效。
5. 索引提示慎用：USE INDEX 是优化器建议而非强制，生产环境优先通过 ANALYZE TABLE 更新统计信息，而非依赖 hints，避免数据分布变化后的执行计划退化。

---

Have a nice day ~ ☕

🌻 近期内容 ▼

• Oracle Skills开源：AI工程正在进入"技能时代"
• 苦等三年！Oracle AI Database 26ai本地服务器版终于来了
• MySQL 8.0结束生命周期，8.4.9 LTS、9.7.0发版上线：一个时代的交接与新生

👉 这里有得聊

如果对国产基础软件（操作系统、数据库、中间件）、AI、Vibe Coding、OpenClaw 、Hermes Agent 等感兴趣，可以加群一起聊聊。关注微信公众号：(少安事务所)，后台回复[群]，即可看到入口。如果这篇文章为你带来了灵感或启发，请帮忙『点赞、推荐、转发』吧，感谢！ღ( ´･ᴗ･` )~