兄弟们，咱们搞技术的，特别是和数据库打交道的，有没有过这种经历？

平时在开发环境写代码，数据量就几百条，那SQL写得叫一个“行云流水”，各种 SELECT *，各种 LEFT JOIN 连得飞起，跑起来也是嗖嗖的。结果一上线，真实数据量一上来，刚开始还好，过了一个月，突然有一天半夜，监控群炸了：CPU 飚到 100%，应用卡死，连接池爆满。

这时候老板站在你背后，眼神犀利地盯着屏幕，你冷汗直流，手忙脚乱地打开数据库客户端，除了机械式地给每个字段加索引，是不是脑子里一片空白？

一定要记住老哥这句话：你会写SQL，但不代表你懂SQL。

在 MySQL 8 的时代，如果你不懂 执行计划 (EXPLAIN)，那你就是蒙着眼睛在高速公路上狂奔的 CRUD Boy，撞墙是早晚的事。而一旦你掌握了它，你就是拥有了“上帝视角”的架构师，每一个慢查询在你眼里都是“裸奔”的。

今天，老哥我就结合 RHEL 8 + MySQL 8.0 环境，从最基础的字段解析，到 MySQL 8 独有的 EXPLAIN ANALYZE 大杀器，带你通盘掌握 SQL 调优的硬核技能。哪怕你是刚入行的新手，跟着我把这篇文章啃完，也能把 1 年经验用出 3 年的效果！

1 环境与数据准备 (不仅要看，还要练)

光说不练假把式。为了让大家能看到真实的优化效果，优化器的“脾气”只有在数据量足够大时才能摸得准。咱们先来构造一个电商核心业务场景。

环境： CentOS/RHEL 8 + MySQL 8.0.22 以上版本。

建表脚本

我们创建两张表：sys_orders（订单表）和 sys_order_detail（明细表）。

CREATE DATABASE IF NOT EXISTS shop_core;
USE shop_core;

-- 订单主表
CREATE TABLE `sys_orders` (
  `order_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `user_id` VARCHAR(32) NOT NULL COMMENT '用户ID (注意是varchar)',
  `order_no` VARCHAR(64) NOT NULL COMMENT '订单编号',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1-待支付,2-已支付,3-发货,4-完成',
  `total_amount` DECIMAL(10,2) NOT NULL COMMENT '总金额',
  PRIMARY KEY (`order_id`),
  KEY `idx_user_status` (`user_id`, `status`), -- 联合索引
  KEY `idx_create_time` (`create_time`)        -- 时间索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

-- 订单明细表
CREATE TABLE `sys_order_detail` (
  `item_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `product_name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `quantity` INT NOT NULL,
  PRIMARY KEY (`item_id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

造数据存储过程

咱们整一个存储过程，快速往 orders 表插 10 万条数据，detail 表插 20 万条左右数据。这在生产环境只能算“毛毛雨”，但足够演示执行计划了。

-- 执行造数 (耐心等待十几秒)
CALL generate_data();

-- 查看结果
mysql> select count(*) from sys_orders;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from sys_order_detail;
+----------+
| count(*) |
+----------+
|   199869 |
+----------+
1 row in set (0.00 sec)

2 读懂 EXPLAIN 的“天书” (核心字段全解析)

数据有了，现在我们随便跑一条 SQL，看看它的“体检报告”。

EXPLAIN SELECT * FROM sys_orders WHERE user_id = 'U1001' AND status = 2;

输出大概长这样（不同环境ID可能不同）：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	sys_orders	NULL	ref	idx_user_status	idx_user_status	131	const,const	1	100.00	NULL

兄弟们，别看列这么多，作为 DBA，平时我盯着看的主要是这 5 个核心指标：type, key, key_len, rows, Extra。

type: 访问类型 (性能的风向标)

这是重中之重！它告诉我们 MySQL 到底是怎么找数据的。我把常见的性能从 好 -> 坏 排个序，大家心里要有数：

1. system / const: 只有一行匹配。比如 WHERE primary_key = 1。这是性能的天花板，快得飞起。
2. eq_ref: 连表查询时，前表的一行对应后表的唯一一行（通常是主键或唯一索引关联）。
3. ref: 普通索引查找。比如我们上面的例子，user_id 不是唯一的，可能搜出多条。
4. range: 这是我们优化的底线！ 索引范围扫描。常见于 >, <, BETWEEN, IN。如果你的 SQL 跑出了 range，通常是可以接受的。
5. index (全索引扫描): ⚠️ 注意坑！ 很多人以为看到 index 就是走了索引，其实它是 Full Index Scan。它扫描了 B+ 树的所有叶子节点，只是比全表扫描少读了点数据（因为索引文件通常比数据文件小）。
6. ALL (全表扫描): ☠️ 红色警报！ 也是所谓的 Full Table Scan。MySQL 从硬盘头读到尾。如果是小表无所谓，大表出现 ALL，基本就是因为没建索引或者索引失效。

key & key_len: 到底用了哪个索引？

• possible_keys: MySQL 觉得可能会用到的索引（备胎）。
• key: 最终实际选用的索引（正宫）。如果是 NULL，恭喜你，全表扫描了。
• key_len: 索引使用的字节数。这个很有用！
  • 比如我们的联合索引 idx_user_status (user_id, status)。
  • user_id 是 varchar(32)，utf8mb4 编码下，最长 32*4 + 2(变长长度) = 130字节。
  • status 是 tinyint，1字节。
  • 如果 key_len 显示 130 或 131 (视是否允许NULL)，说明只用了 user_id，status 没用到！这能帮你检查联合索引是不是只用了一部分。

rows & filtered: 只是个估算！

这里的坑最多，老哥给你们总结几个最关键的。看懂这几个，你基本就拿捏住了：

• NULL (空): 😐 不好不坏，常规操作。
  • 这意味着查询走了索引，但是索引无法覆盖所有查询的列，所以必须回表（Back to Table）去主键聚簇索引里把原本的数据行捞出来。
  • 场景： 比如 SELECT * FROM sys_orders WHERE user_id = 'U1'. 索引里只有 user_id，但你要 *，MySQL 只能拿着 ID 回去查正文。这是最常见的状态，只要不是全表扫描（type=ALL），通常可以接受。

• Using index: 👍 好东西！
  • 这叫“覆盖索引”。查询的字段全在索引树上，直接从索引就能拿结果，压根不用回表。
  • 场景：SELECT user_id FROM sys_orders WHERE user_id = 'U1'. 这种性能极高，是我们优化的终极目标。

• Using index condition: 👌 值得鼓励的“小聪明”。
  • 这是 MySQL 5.6 引入的 ICP (Index Condition Pushdown) 特性。简单说，就是 MySQL 把一部分过滤工作“下沉”到了存储引擎层。
  • 大白话解释： 本来存储引擎只管拿数据，过滤是 Server 层的事。现在 Server 层把部分 WHERE 条件丢给存储引擎：“兄弟，你在查索引的时候顺便帮我把这个条件卡一下，不符合的就别回表捞数据了，省点 I/O。”
  • 结论： 比 Using where 强，比 Using index 弱，属于一种性能优化手段。

• Using where: 😐 普普通通。
  • 说明存储引擎读上来数据后，Server 层还得再过滤一遍。
  • 注意： 如果 type=ALL 且有 Using where，说明你在全表扫描并过滤，这种必须要优化！如果 type=ref 且有 Using where，通常问题不大。

• Using filesort: 🚨 红色警报！
  • 说明 MySQL 无法利用索引顺序来排序，必须在内存（Sort Buffer）或者磁盘里进行排序。
  • 比喻： 这就好比你去图书馆找书，书架上的书是乱的，你得把书全搬到地上，自己一本本排好序才能给读者。极度消耗 CPU。

• Using temporary: 🚨 红色警报！
  • 既然用到了临时表（可能是内存的也可能是磁盘的），性能通常好不到哪去。
  • 常见于 GROUP BY、DISTINCT 或复杂的 UNION。看到这个，一定要想办法优化索引或简化 SQL。

3 MySQL 8 的大杀器：EXPLAIN ANALYZE (不但要估算，还要实测)

以前我们用 EXPLAIN，就像是看地图估算时间：“这路应该不堵，大概 10 分钟”。但实际上可能路面塌陷，你堵了 1 小时。EXPLAIN 只是优化器的“预判”，有时候它的成本计算（Cost）并不代表真实的执行时间。

MySQL 8.0 引入了 EXPLAIN ANALYZE。 它不仅生成执行计划，ule还会真正运行这条 SQL，并告诉你每一步到底花了多久。

单表操作

实战演示：让 MySQL “汗流浃背”

为了看到真实的性能损耗，我们来构造一个无法利用索引排序的场景。我们查询 3 月份以后的订单（数据量大），并且强制按“金额”排序（无索引），取前 20 条。

-- 强制按 total_amount 排序，让它必须在内存里排
EXPLAIN ANALYZE SELECT * FROM sys_orders WHERE create_time > '2025-03-01' ORDER BY total_amount LIMIT 20;

输出解读 (TREE 格式)：

| -> Limit: 20 row(s)  (cost=10104.95 rows=20) (actual time=46.883..46.885 rows=20 loops=1)
    -> Sort: sys_orders.total_amount, limit input to 20 row(s) per chunk  (cost=10104.95 rows=99687) (actual time=46.882..46.883 rows=20 loops=1)
        -> Filter: (sys_orders.create_time > TIMESTAMP'2025-03-01 00:00:00')  (cost=10104.95 rows=99687) (actual time=0.030..32.416 rows=83350 loops=1)
            -> Table scan on sys_orders  (cost=10104.95 rows=99687) (actual time=0.028..25.081 rows=100000 loops=1)
 |

老哥带你深度拆解：

怎么看这棵树？记住口诀：从里往外看，从下往上看，先看缩进最深的。

1. Table scan on sys_orders (最底层):

• 发生了什么： 缩进最深，MySQL 选择了全表扫描。
• 数据说话：rows=100000 (扫描了10万行)，actual time=...25.081。光是把这10万行数据从硬盘/内存里读一遍，就花了 25毫秒。

1. Filter (过滤层):

• 发生了什么： 拿着刚才读出来的10万行，逐行比对 create_time > '2025-03-01'。
• 数据说话：rows=83350。说明大部分订单（8.3万条）都符合条件。这一步结束时，时间累积到了 32.416毫秒。

1. Sort: sys_orders.total_amount (性能杀手！):

• 发生了什么： 这里的 Sort 就是传说中的 Filesort！因为 total_amount 没有索引，MySQL 必须把这 83350 条数据扔到内存（Sort Buffer）里进行排序。
• 数据说话： 注意看时间，从 Filter 结束的 32ms 跳到了 Sort 结束的 46.883ms。这意味着，光是排序这一下，就消耗了 14毫秒 的 CPU 时间！
• 细节：limit input to 20 row(s) per chunk 说明 MySQL 采用了优先队列排序（Priority Queue），不用给8万行全排序，只维护最小的20个即可，否则时间会更长。

1. Limit (顶层):

• 最后取前20条返回。

[思考一下：为什么有时候 EXPLAIN 显示走了索引（比如 range），但 EXPLAIN ANALYZE 里的 actual time 还是很长？]  老哥点拨：  EXPLAIN 只能看到逻辑路径（走了索引），但它看不到 物理成本。 如果你看到  Index scan 很快，但像上面一样  Sort 这一层  actual time 暴涨，说明瓶颈不在“找数据”，而在“排序”。这时候加任何过滤索引都没用，必须加 包含排序字段的联合索引（例如  idx_time_amount(create_time, total_amount)）才能彻底消灭这个  Sort 节点，让性能直接起飞！

读懂多表关联 (JOIN) 的“套娃”结构**

单表看完了，咱们来看看多表关联。这可是小白最容易晕的地方。在 MySQL 8 的 TREE 格式里，JOIN 就像是一个“套娃”或者“嵌套循环”。

实战演示： 我们要查询“用户 U1 的所有订单详情”。这需要关联 sys_orders 和 sys_order_detail。

EXPLAIN ANALYZE 
SELECT d.* 
FROM sys_orders o 
JOIN sys_order_detail d ON o.order_id = d.order_id 
WHERE o.user_id = 'U1';

输出解读 (TREE 格式)：

-> Nested loop inner join  (cost=12.50 rows=5) (actual time=0.055..0.120 rows=5 loops=1)
    -> Index lookup on o using idx_user_status (user_id='U1')  (cost=2.50 rows=2) (actual time=0.045..0.050 rows=2 loops=1)
    -> Index lookup on d using idx_order_id (order_id=o.order_id)  (cost=2.10 rows=2) (actual time=0.015..0.020 rows=2.5 loops=2)

老哥带你深度拆解：

看完这个树，你得学会找 “谁是老司机（驱动表）”，“谁是乘客（被驱动表）”。

1. Nested loop inner join (最外层):

• 这告诉我们，MySQL 决定用 嵌套循环连接 (NLJ) 的方式来处理。你可以把它理解为两层 for 循环。

1. Index lookup on o (驱动表/外层循环):

• 位置： 缩进较少，排在上面。这就是 驱动表。
• 解读： MySQL 先去 sys_orders (别名 o) 表里找 user_id='U1' 的记录。
• 数据：rows=2 loops=1。找到了 2 个订单。注意，这里的 loops=1 说明这个动作只做了一次。

1. Index lookup on d (被驱动表/内层循环):

• 位置： 缩进更深，排在下面。这就是 被驱动表。
• 关键点： 注意看 loops=2！ 为什么是 2？
• 核心逻辑： 因为驱动表（订单表）找到了 2 条记录，所以内层循环就要执行 2 次。拿着订单 A 的 ID 去明细表查一次，再拿着订单 B 的 ID 去明细表查一次。
• 性能公式：被驱动表的查询成本 × 驱动表的行数。

[思考一下：如果驱动表扫出了 1 万条数据，会对被驱动表产生什么影响？]  老哥点拨： 那就是灾难！如果驱动表有 1 万行，被驱动表就要被查询 1 万次。这时候， 被驱动表的关联字段（order_id）必须要有索引，否则就是做 1 万次全表扫描，数据库直接宕机！
4 实战演练：常见“坑”与优化方案

知道了原理，咱们来看几个平时开发中最容易踩的坑。

场景一：联合索引的“最左前缀”原则失效

坑爹 SQL：

-- 索引是 (user_id, status)，但我跳过了 user_id 直接查 status
EXPLAIN SELECT * FROM sys_orders WHERE status = 1;

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | sys_orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99687 |    10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

结果： type: ALL。 分析： 联合索引就像是“按姓氏、再按名字”排序的电话簿。你现在只给我一个名字（status），不给姓氏（user_id），我没法查，只能从头翻到尾。

还有一种坑：在索引列上做运算。

EXPLAIN SELECT * FROM sys_orders WHERE LEFT(user_id, 2) = 'U1';

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | sys_orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99687 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

结果： type: ALL。 优化： 只要对索引字段用了函数，索引立马失效。改成 LIKE 'U1%' 就可以走 range 索引了。

场景二：隐式转换导致的灾难

坑爹 SQL：

-- user_id 是 VARCHAR 类型，但我查询时没加单引号，用了数字
EXPLAIN SELECT * FROM sys_orders WHERE user_id = 1001;

+----+-------------+------------+------------+------+-----------------+------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys   | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+------+-----------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | sys_orders | NULL       | ALL  | idx_user_status | NULL | NULL    | NULL | 99687 |    10.00 | Using where |
+----+-------------+------------+------------+------+-----------------+------+---------+------+-------+----------+-------------+

结果： type: ALL，且 Extra 显示 Using where。 分析： 这是新手最容易犯的错！MySQL 发现类型对不上，会偷偷把 user_id 转成数字进行比较，相当于执行了 CAST(user_id AS SIGNED)。一旦在索引列上加了隐式函数，索引全废！ 优化： 乖乖加上单引号 user_id = '1001'。

场景三：ORDER BY 导致的 Using filesort

坑爹 SQL：

-- 有 user_id 索引，但我非要按 total_amount 排序
EXPLAIN SELECT * FROM sys_orders WHERE user_id = 'U1001' ORDER BY total_amount;

+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra          |
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | sys_orders | NULL       | ref  | idx_user_status | idx_user_status | 130     | const |    2 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------+

结果： Extra 出现 Using filesort。 分析： 虽然 user_id 走了索引，但查出来的数据是按 user_id 排序的，不是按 total_amount 排序的。MySQL 只能把数据拿出来在内存里重排。 优化： 如果这个业务非常高频，建立联合索引 (user_id, total_amount)。这样查出来的数据本身就是按金额排序的，直接取就行，省去了排序的 CPU 消耗。

场景四：深分页问题 (LIMIT 100000, 10)

坑爹 SQL：

EXPLAIN SELECT * FROM sys_orders ORDER BY create_time LIMIT 90000, 10;

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | sys_orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99687 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+----------------+

结果： type: ALL 或者 index，扫描行数巨大。 分析： MySQL 需要查出 90010 条记录，抛弃前 90000 条，只取最后 10 条。这在数据量大时是灾难。

优化方案：延迟关联 (Late Row Lookup)

EXPLAIN SELECT * FROM sys_orders t1
JOIN (
    SELECT order_id FROM sys_orders ORDER BY create_time LIMIT 90000, 10
) t2 ON t1.order_id = t2.order_id;

+----+-------------+------------+------------+--------+---------------+-----------------+---------+-------------+-------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key             | key_len | ref         | rows  | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+-----------------+---------+-------------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL            | NULL    | NULL        | 90010 |   100.00 | NULL        |
|  1 | PRIMARY     | t1         | NULL       | eq_ref | PRIMARY       | PRIMARY         | 4       | t2.order_id |     1 |   100.00 | NULL        |
|  2 | DERIVED     | sys_orders | NULL       | index  | NULL          | idx_create_time | 5       | NULL        | 90010 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+-----------------+---------+-------------+-------+----------+-------------+

原理解析：

1. 子查询只查 order_id（主键），可以利用覆盖索引，不需要回表，速度极快。
2. 拿到 10 个 ID 后，再回表去拿完整的 * 数据。
3. 这样就把 90000 次回表 I/O 变成了 10 次。

场景五：多表关联时的“隐形杀手” (被驱动表无索引)**

兄弟们，JOIN 慢，90% 的原因是因为 被驱动表（Joined Table）的连接列上没有索引。

但在 MySQL 8 中，情况变了！以前没有索引会用“Block Nested Loop (BNL)”，慢得像蜗牛；现在 MySQL 8 引入了 Hash Join。

坑爹 SQL (模拟现场)： 假设我们手贱，把 sys_order_detail 表上的 idx_order_id 索引给删了，或者连接条件写错了导致无法走索引。

-- 假设 sys_order_detail 的 order_id 上没有索引
EXPLAIN ANALYZE SELECT * FROM sys_orders o JOIN sys_order_detail d ON o.order_id = d.order_id;

MySQL 8 的“救命”输出：

-> Inner hash join (no condition)  (cost=xxxx rows=xxxx) (actual time=...)
    -> Table scan on d  (cost=...) (actual time=...)
    -> Hash
        -> Table scan on o  (cost=...) (actual time=...)

分析与优化：

1. 现象： 你会看到 Inner hash join。这意味着 MySQL 放弃了循环查找，而是把其中一张表（通常是小表）全部读入内存，构建一个哈希表，然后扫描另一张大表去碰撞。
2. 它是好事还是坏事？

• 相比于 MySQL 5.7 的 BNL，Hash Join 是巨大的进步。它让本来要跑几个小时的烂 SQL，可能几分钟就跑完了。
• 但是！ 它依然意味着 全表扫描。它需要把两张表都读一遍。

• 优化方案：
  • 别指望 Hash Join 救命。给被驱动表的连接字段（order_id）加上索引，让它变回 Nested loop inner join 配合 Index lookup。
  • 对比： Hash Join 是全量扫描（扫 20 万行）；加了索引后的 NLJ 可能只需要扫描几十行。这中间的 I/O 差距是数量级的。

老哥心法：

• 小表驱动大表 原则依然适用，但在 MySQL 8 优化器面前，它会自动帮你选谁是小表，你不用太纠结 LEFT JOIN 还是 RIGHT JOIN（除非业务逻辑限制）。
• 死死盯住被驱动表： 只要 EXPLAIN 里出现了 Hash Join 或者 Block Nested Loop，第一时间检查关联字段有没有索引，或者关联字段的数据类型是否一致（避免隐式转换）。

总结

兄弟们，写到这，大概的套路你们应该都看明白了。EXPLAIN 是我们手里的静态地图，而 MySQL 8 的 EXPLAIN ANALYZE 则是实时导航，能告诉你哪里堵车。

最后，老哥再送大家三句 SQL 调优心法，这是我这十几年踩坑换来的：

1. 不要过度优化： 只有慢查询才需要优化。如果一个报表 SQL 一天只跑一次，跑 2 秒和 0.1 秒对业务没区别，别为了它把索引搞得巨复杂，导致插入数据变慢。
2. 索引不是越多越好： 索引是把双刃剑。查询快了，增删改（DML）必然变慢，因为要维护索引树。一张表的索引最好不要超过 5 个。
3. 核心关注点 Rows x Cost： 所有的优化手段（加索引、改写法），最终目的都是为了减少 MySQL 扫描的行数。扫描的数据越少，I/O 就越少，速度自然就快。