第一章 引言

1.1 EXPLAIN的作用与重要性

在日常数据库开发与运维中，SQL语句性能问题往往是最常见、最棘手的瓶颈之一。对于复杂查询，开发者常常陷入这样的困境：SQL语法完全正确，却运行得非常缓慢。此时，单靠直觉优化往往无从下手。

这时候，EXPLAIN 就成为我们最重要的武器。它可以让我们“窥探”数据库优化器（Optimizer）的内部决策过程，清晰地看到：

  • 数据库选择了哪张表作为驱动表；

  • 哪些索引被使用了，哪些被忽略了；

  • 是否出现了全表扫描、文件排序或临时表操作；

  • 每个步骤预估会扫描多少行数据。

换句话说，EXPLAIN就是SQL性能优化的放大镜。通过它，我们不仅能发现问题，还能验证优化是否生效。

1.2 为什么需要理解执行计划

很多开发者在学习SQL时停留在“能写出正确的语句”这个阶段，而忽略了“如何让SQL执行得高效”。然而在真实业务中：

  • 电商系统：订单表动辄千万级，若查询未使用合适索引，页面可能卡死；

  • 社交应用：好友关系、动态推送涉及复杂的多表JOIN，执行计划直接决定响应速度；

  • 数据分析：报表系统需要对大规模数据进行聚合，SQL性能影响业务决策时效。

因此，理解执行计划的意义在于：

  1. 定位问题：通过分析EXPLAIN输出，快速判断性能瓶颈来源。

  2. 指导优化：结合索引设计、SQL重写，减少不必要的扫描和排序。

  3. 验证效果：修改方案后再次EXPLAIN，对比优化前后的执行路径。

一句话总结：不会用EXPLAIN的人，只能盲目猜测SQL性能问题；而熟练掌握EXPLAIN的人，能有理有据地精准优化。

第二章 EXPLAIN基础

2.1 如何使用EXPLAIN

在 MySQL 中，EXPLAIN 的用法非常简单，只需要在 SQL 查询语句前加上 EXPLAIN 关键字 即可。例如：

-- 一个简单的单表查询
EXPLAIN SELECT * FROM orders WHERE order_id = 1001;

-- 一个多表连接查询
EXPLAIN SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 500;

执行后，MySQL 不会真正运行 SQL，而是返回一张 执行计划表。这张表告诉我们：

  • 优化器（Optimizer）是如何“打算”执行这条语句的；

  • 选择了哪些索引；

  • 预计会扫描多少行数据；

  • 是否需要排序、临时表等额外操作。

⚠️ 注意：

  1. EXPLAIN 只显示执行计划，不执行真正的查询，因此不会修改数据，也不会消耗大量资源。

  2. MySQL 8.0 开始，可以使用 EXPLAIN ANALYZE 来查看 实际运行过程（包括真实行数和耗时），这比传统的 EXPLAIN 更精确，但会真的执行 SQL。

例如：

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

输出中不仅包含计划，还会显示实际耗时（例如“actual time=0.003..0.005 rows=10”），适合做性能调试。

2.2 执行计划的输出格式

标准的 EXPLAIN 输出是一张表格，不同版本（5.7 与 8.0）字段略有差别，但核心字段基本一致。下面是一个典型输出：

EXPLAIN SELECT * FROM orders WHERE order_id = 1001;

结果可能如下：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE orders NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index
字段简介

  • id：查询的执行顺序标识（数值越大优先级越高）。

  • select_type：查询的类型（如 SIMPLE、PRIMARY、SUBQUERY 等）。

  • table：当前访问的表名或别名。

  • partitions：涉及的分区信息（若表使用了分区功能）。

  • type：连接类型，性能由好到坏排序：system > const > eq_ref > ref > range > index > ALL。

  • possible_keys：优化器认为可用的索引。

  • key：实际选择的索引。

  • key_len：使用索引的长度。

  • ref：索引比较时使用的列或常量。

  • rows：预计需要扫描的行数。

  • filtered：条件过滤后剩余的行数比例。

  • Extra：额外信息（是否用临时表、是否排序、是否覆盖索引等）。

💡 记忆小技巧：
可以把执行计划的表格理解为 “SQL执行的路线图”，每一行就是一步操作，而每个字段就是“这一步怎么走”的说明。

第三章 执行计划详解

3.1 各列含义与解析

3.1.1 table

table 字段表示当前行操作所涉及的表名或者别名，是理解查询执行顺序的起点。它告诉我们：优化器当前正在访问哪个表。

示例：
EXPLAIN SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 500;

可能输出如下：

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE o ref idx_amount idx_amount 4 const 1000 Using where
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 db.o.user_id 1 Using index
解析：

  • 第一行 o 表示优化器先扫描 orders 表。

  • 第二行 u 表示基于第一行结果，通过索引关联扫描 users 表。

  • 重要提示：在多表JOIN中，table 字段的顺序并不总是 SQL 中写的顺序，而是优化器决定的执行顺序。优化器会选择 成本最低的表先执行。

优化建议：

  1. 确保驱动表（first table）行数尽量少，以减少JOIN开销。

  2. 对JOIN字段建立合适索引，保证关联表通过索引快速访问，而不是全表扫描。

  3. 对大表，考虑使用分区或条件预筛选减少扫描行数。

实践案例：

假设 orders 表有 100 万行，users 表有 10 万行，如果优化器先扫描 users 表，再JOIN orders，会导致巨量行扫描。使用 EXPLAIN 可以确认 优化器选择了正确的驱动表。

3.1.2 id

id 字段用于标识 查询中每个 SELECT 的执行顺序和层级。它的值越大，优先级越高；在多表 JOIN 或子查询中，id 可以帮助我们判断 哪一步先执行，哪一步后执行。

示例 1：单表查询
EXPLAIN SELECT * FROM orders WHERE order_id = 1001;
id select_type table type key rows Extra
1 SIMPLE orders const PRIMARY 1 Using index

解析：

  • 只有一条 SELECT，id 为 1。

  • SIMPLE 类型表示没有子查询或复杂操作。

示例 2：多表 JOIN
EXPLAIN SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 500;
id select_type table type key rows Extra
1 SIMPLE o ref idx_amount 1000 Using where
1 SIMPLE u eq_ref PRIMARY 1 Using index

解析：

  • 两行 id 都是 1，说明它们属于同一个 SELECT 查询。

  • 在 JOIN 场景中，id 一样的行表示 同一级别的执行顺序，优化器会按成本计算决定先访问哪张表（通常选择行数较少或条件更高效的表）。

示例 3：子查询或派生表
EXPLAIN SELECT name 
FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 500);
id select_type table type key rows Extra
2 DEPENDENT SUBQUERY orders ref idx_amount 1000 Using where
1 PRIMARY users eq_ref PRIMARY 1 Using index

解析：

  • id=2 的子查询先执行，属于 DEPENDENT SUBQUERY（依赖外层查询的子查询）。

  • id=1 是外层 SELECT。

  • id 数值越大，通常表示 先执行子查询，再执行外层查询。

优化建议：

  1. 理解执行顺序：通过 id 可以判断是否子查询先执行，可以考虑改写为 JOIN 或 EXISTS 来减少执行次数。

  2. 减少嵌套查询成本：尽量让高 id 的子查询扫描行数少，否则外层查询会重复多次执行。

  3. 分解复杂查询：如果 id 结构过多，说明查询嵌套复杂，可考虑拆表或优化索引。

3.1.3 select_type

select_type 用于标识每个 SELECT 的类型，它告诉我们 SQL 是简单查询、主查询、子查询还是派生表。掌握它可以帮助我们判断优化难点和改写空间。

常见类型说明
类型 含义 使用场景
SIMPLE 普通的单表查询或不含子查询/派生表的 JOIN SELECT * FROM orders WHERE order_id=1001
PRIMARY 外层查询，包含子查询或复杂派生表 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)
SUBQUERY 子查询（不派生表） SELECT name FROM users WHERE id IN (SELECT user_id FROM orders)
DEPENDENT SUBQUERY 依赖外层查询的子查询 SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id=u.id)
DERIVED 派生表（子查询作为临时表） SELECT * FROM (SELECT * FROM orders WHERE amount>500) AS tmp
UNION UNION 的外层 SELECT SELECT * FROM a UNION SELECT * FROM b
UNION RESULT UNION 的结果集合 MySQL 内部生成，用于 UNION 输出
示例 1：简单查询
EXPLAIN SELECT * FROM orders WHERE order_id = 1001;
id select_type table type key rows Extra
1 SIMPLE orders const PRIMARY 1 Using index

解析：

  • 单表查询，select_type 为 SIMPLE。

  • 不存在子查询或派生表，优化器处理最直接。

示例 2：子查询
EXPLAIN SELECT name 
FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 500);
id select_type table type key rows Extra
2 SUBQUERY orders ref idx_amount 1000 Using where
1 PRIMARY users eq_ref PRIMARY 1 Using index

解析：

  • 外层查询 id=1, select_type=PRIMARY

  • 内层子查询 id=2, select_type=SUBQUERY

  • 表明优化器会先执行子查询，再返回结果给外层查询。

示例 3：派生表
EXPLAIN SELECT u.name, tmp.total
FROM users u
JOIN (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id) tmp
ON u.id = tmp.user_id;
id select_type table type key rows Extra
2 DERIVED orders index NULL 1000 Using index
1 PRIMARY u eq_ref PRIMARY 1 Using index

解析：

  • 派生表（id=2, DERIVED）先执行，生成临时表 tmp

  • 外层 SELECT（id=1, PRIMARY）再 JOIN 派生表

  • 注意 DERIVED 类型会产生临时表，可能影响性能

优化建议：

  1. 避免不必要的派生表：可用 JOIN 或 EXISTS 改写，减少临时表开销。

  2. 子查询改写：将 SUBQUERY 或 DEPENDENT SUBQUERY 改为 JOIN，可减少重复扫描。

  3. UNION 场景：了解 UNION 与 UNION ALL 的差异，UNION 会去重，可能产生临时表，UNION ALL 性能更优。

3.1.4 partitions

partitions 字段显示当前查询所访问的 分区信息。

  • 如果表没有使用分区，该字段为 NULL。

  • 如果表是分区表，则会显示优化器访问的具体分区列表。

理解 partitions 字段有助于确认 分区裁剪（Partition Pruning）是否生效，从而避免扫描不必要的分区，提高查询性能。

示例 1：无分区表
EXPLAIN SELECT * FROM orders WHERE order_id = 1001;
id select_type table partitions type key rows Extra
1 SIMPLE orders NULL const PRIMARY 1 Using index

解析：

  • orders 表未分区，所以 partitions 显示 NULL

示例 2：按范围分区表

假设 orders 表按年份分区：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

执行查询：

EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2023;
id select_type table partitions type key rows Extra
1 SIMPLE orders p2023 ref idx_date 500 Using index

解析：

  • partitions = p2023，说明优化器只扫描 2023 年分区

  • 分区裁剪生效，避免全表扫描

示例 3：未能裁剪的情况
EXPLAIN SELECT * FROM orders WHERE MONTH(order_date) = 5;
id select_type table partitions type key rows Extra
1 SIMPLE orders p2022,p2023 ref idx_date 2000 Using index

解析：

  • partitions 显示多个分区

  • 优化器无法裁剪，因为条件使用了函数 MONTH()，无法直接匹配分区键

  • 建议：使用范围条件或虚拟列生成年份/月份索引，提升分区裁剪效果

优化建议：

  1. 分区裁剪：查询条件应尽量直接使用分区键，避免函数或表达式包装。

  2. 索引与分区结合：分区表仍需创建索引，保证分区内访问高效。

  3. 监控 partitions 字段：通过 EXPLAIN 确认每次查询扫描的分区数量，越少越好。

3.1.5 type

type 字段表示 MySQL 优化器选择的 连接类型（join type）或访问类型，它是评估 SQL 性能最直观的指标之一。

  • 类型越靠前，性能越好；

  • 类型越靠后，扫描行数越多，查询越慢。

常见 type 类型及性能排序
type 含义 性能等级 说明
system 表只有一行 最优 特殊情况，系统表只有一行
const 常量查找 极佳 通过 PRIMARY KEY 或 UNIQUE 索引查找单行
eq_ref 索引唯一匹配 很好 JOIN 使用 UNIQUE 或 PRIMARY KEY，最多匹配一行
ref 索引非唯一匹配 较好 JOIN 使用普通索引，可匹配多行
range 索引范围查找 中等 使用索引范围扫描，如 BETWEEN, >, <
index 全索引扫描 较差 遍历整个索引，效率比全表扫描稍好
ALL 全表扫描 最差 遍历整个表，数据量大时最慢
示例 1：const 类型
EXPLAIN SELECT * FROM orders WHERE order_id = 1001;
id table type key rows Extra
1 orders const PRIMARY 1 Using index

解析：

  • PRIMARY KEY 精确查找，type = const，性能最佳

  • 扫描行数仅 1 行

示例 2：ref 类型
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
id table type key rows Extra
1 orders ref idx_userid 50 Using index

解析：

  • user_id 上有非唯一索引，可能匹配多行

  • type = ref，性能良好，但比 const 稍差

示例 3：range 类型
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
id table type key rows Extra
1 orders range idx_date 1000 Using index

解析：

  • 范围扫描索引，扫描行数增加

  • type = range，性能中等

  • 优化思路：增加更精细的索引或条件，减少扫描行数

示例 4：ALL 类型（全表扫描）
EXPLAIN SELECT * FROM orders WHERE amount > 500;
id table type key rows Extra
1 orders ALL NULL 1000000 Using where

解析：

  • 没有索引可用，扫描整张表

  • type = ALL，性能最差

  • 优化建议：为条件列创建索引，避免全表扫描

优化建议：

  1. 优先让 type 出现 system、const、eq_ref、ref 类型；

  2. 避免 ALL 和 index 类型的全表或全索引扫描；

  3. 可以通过 覆盖索引、联合索引、分区裁剪 等方式优化扫描行数；

  4. 分析 EXPLAIN 输出的 rows 字段，结合 type 判断潜在性能问题。

3.1.6 possible_keys 与 key

概念说明

  • possible_keys：优化器认为 可能可用的索引列表。

    • 它显示哪些索引可被用于查询条件，但不代表最终一定使用。

  • key：优化器 实际选择使用的索引。

    • 可能与 possible_keys 相同，也可能为空（表示未使用索引）。

理解这两个字段可以帮助我们判断索引是否被合理利用，或者为什么优化器没有选择某个索引。

示例 1：单列索引
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    INDEX idx_userid(user_id)
);

EXPLAIN SELECT * FROM orders WHERE user_id = 123;
id table type possible_keys key key_len ref rows Extra
1 orders ref idx_userid idx_userid 4 const 50 Using index

解析：

  • possible_keys = idx_userid，优化器认为 user_id 索引可用

  • key = idx_userid，实际选择该索引

  • key_len = 4，使用了完整索引长度

  • ref = const，表示索引用常量匹配

示例 2：未使用索引
EXPLAIN SELECT * FROM orders WHERE amount > 500;
id table type possible_keys key key_len ref rows Extra
1 orders ALL NULL NULL NULL NULL 1000000 Using where

解析：

  • possible_keys = NULL，没有索引可用

  • key = NULL，优化器只能全表扫描

  • 优化建议：为 amount 列创建索引

示例 3：多列索引选择
CREATE INDEX idx_user_amount ON orders(user_id, amount);

EXPLAIN SELECT * FROM orders WHERE user_id=123 AND amount>500;
id table type possible_keys key key_len ref rows Extra
1 orders ref idx_user_amount idx_user_amount 4 const 50 Using where

解析：

  • possible_keys = idx_user_amount，多列索引可用

  • key = idx_user_amount，优化器选择该索引

  • key_len = 4，只使用索引第一个列（user_id）

  • Extra = Using where，表示 amount 条件未覆盖，需要过滤

⚠️ 注意点：

  • 对多列索引，优化器默认使用 最左前缀原则

  • 查询条件必须匹配索引最左列，才能完全使用索引

优化建议：

  1. 查看 possible_keys：确认哪些索引可被优化器考虑，如果为空，说明索引设计可能不合理；

  2. 查看 key：判断优化器最终选择了哪个索引，若未使用索引，可考虑调整 SQL 或索引顺序；

  3. 多列索引最左前缀：确保查询条件匹配索引最左列，提高索引利用率；

  4. 结合 type：如果 key 不为空，但 type = ALL，则说明索引未完全发挥作用，需要优化覆盖索引或条件顺序。

3.1.7 key_len

key_len 表示优化器 实际使用的索引长度（以字节为单位），用于判断索引覆盖的列数和查询是否能充分利用索引。

  • key_len 越长，通常意味着更多索引列被使用，查询更高效；

  • 对多列索引，key_len 可以帮助判断 是否使用了最左前缀；

  • 注意 key_len 与数据类型相关，例如 INT 占 4 字节，BIGINT 占 8 字节，CHAR(N) 占 N 字节。

示例 1：单列索引
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
id table type key key_len ref rows Extra
1 orders ref idx_userid 4 const 50 Using index

解析：

  • key_len = 4，使用了 user_id 的完整索引

  • INT 类型占 4 字节，完全覆盖

示例 2：多列索引（最左前缀）
CREATE INDEX idx_user_amount ON orders(user_id, amount);

EXPLAIN SELECT * FROM orders WHERE user_id=123;
id table type key key_len ref rows Extra
1 orders ref idx_user_amount 4 const 50 Using where

解析：

  • key_len = 4，只使用了索引的第一列 user_id

  • 第二列 amount 未使用，因此 Extra 显示 Using where

示例 3：多列索引全部使用
EXPLAIN SELECT * FROM orders WHERE user_id=123 AND amount>500;
id table type key key_len ref rows Extra
1 orders ref idx_user_amount 8 const 50 Using where

解析：

  • key_len = 8，使用了索引的两列

  • INT 类型占 4 字节，user_id + amount 共 8 字节

  • Extra 仍显示 Using where，因为 amount 条件是范围查询，没有完全覆盖

优化建议：

  1. 关注 key_len 与索引设计：确保查询条件能使用索引前缀，提高利用率；

  2. 判断覆盖索引：如果 key_len = 所有列长度，且查询字段被索引覆盖，可实现覆盖索引优化；

  3. 调试索引：key_len 偏小，说明索引部分列未被使用，可考虑调整索引顺序或修改查询条件。

3.1.8 ref

ref 字段表示优化器在使用索引时 引用的列或常量，即索引匹配条件来源。它帮助我们理解索引是如何被利用的。

  • 如果 ref 显示 const，表示使用常量匹配索引（通常是主键或唯一索引），性能最好。

  • 如果 ref 显示 列名，表示使用列与列的关系进行索引匹配（通常是 JOIN 条件）。

  • 如果 ref 显示 NULL，说明未使用索引。

示例 1：主键匹配
EXPLAIN SELECT * FROM orders WHERE order_id = 1001;
id table type key key_len ref rows Extra
1 orders const PRIMARY 4 const 1 Using index

解析：

  • ref = const

  • 说明 order_id 使用常量匹配 PRIMARY KEY

  • 扫描行数仅 1 行，性能最佳

示例 2：非唯一索引匹配
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
id table type key key_len ref rows Extra
1 orders ref idx_userid 4 const 50 Using index

解析：

  • ref = const

  • 使用常量匹配 user_id 索引

  • 匹配多行（50 行），type = ref

示例 3：JOIN 条件匹配
EXPLAIN SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 500;
id table type key key_len ref rows Extra
1 o ref idx_amount 4 const 1000 Using where
1 u eq_ref PRIMARY 4 db.o.user_id 1 Using index

解析：

  • orders 表使用常量匹配 ref = const

  • users 表通过 JOIN 条件 u.id = o.user_id 匹配索引，ref = db.o.user_id

  • 说明优化器用 ref 指明索引匹配来源列

优化建议：

  1. 优先使用 const/ref：常量匹配性能最好，可通过 WHERE 条件优化索引匹配；

  2. 避免 NULL：ref = NULL 表示未使用索引，应调整索引或查询条件；

  3. 分析 JOIN：通过 ref 判断 JOIN 是否走索引，减少大表全表扫描；

  4. 调试覆盖索引：配合 key_len 判断索引是否完全覆盖查询条件。

3.1.9 rows

rows 字段表示 优化器估算需要扫描的行数，用来判断查询成本。

  • 数值越大，查询可能越慢；

  • 结合 type 字段，可以判断是否存在全表扫描或索引效率低下问题；

  • 注意：rows 是优化器的估算值，不是实际扫描行数（MySQL 8.0 可用 EXPLAIN ANALYZE 查看实际行数）。

示例 1：主键精确查找
EXPLAIN SELECT * FROM orders WHERE order_id = 1001;
id table type key key_len ref rows Extra
1 orders const PRIMARY 4 const 1 Using index

解析：

  • rows = 1，表示优化器只需扫描 1 行

  • type = const，效率最高

示例 2：索引范围扫描
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
id table type key key_len ref rows Extra
1 orders ref idx_userid 4 const 50 Using index

解析：

  • rows = 50，预估需要扫描 50 行

  • type = ref，使用索引匹配多行

  • 可以通过优化索引或条件减少 rows，提高查询性能

示例 3：全表扫描
EXPLAIN SELECT * FROM orders WHERE amount > 500;
id table type key key_len ref rows Extra
1 orders ALL NULL NULL NULL 1000000 Using where

解析：

  • rows = 1000000，表示需要扫描整张表

  • type = ALL，全表扫描性能最差

  • 优化建议：为 amount 创建索引或使用覆盖索引

优化建议：

  1. 关注 rows 与 type 的组合：

    • 小 rows + 好 type（const、eq_ref） = 高性能

    • 大 rows + ALL = 高成本

  2. 减少扫描行数：

    • 使用索引优化 WHERE 条件

    • 对多表 JOIN，确保驱动表行数尽量少

    • 分区表裁剪（partitions）可显著降低 rows

  3. 验证估算准确性：

    • 使用 EXPLAIN ANALYZE 获取实际扫描行数，调整统计信息（ANALYZE TABLE）

3.1.10 filtered

filtered 字段表示 优化器估算经过 WHERE 条件过滤后，能保留的行百分比，用于衡量行过滤效率。

  • 值越大，意味着 大部分行被保留下来；

  • 值越小，意味着 大部分行被过滤掉，优化器需要更多的行扫描来满足查询；

  • 结合 rows 字段，可以估算实际扫描的行数：

                实际扫描行数=rows×(filtered/100)

示例 1：高过滤率
EXPLAIN SELECT * FROM orders WHERE amount > 500;
id table type key key_len ref rows filtered Extra
1 orders ref idx_amount 4 const 1000 90.0 Using index

解析：

  • filtered = 90%，说明经过 WHERE 条件，大约 90% 的行被保留下来

  • 实际扫描行数 ≈ 1000 × 0.9 = 900 行

  • 高过滤率，索引使用效率较高

示例 2：低过滤率
EXPLAIN SELECT * FROM orders WHERE amount > 10;
id table type key key_len ref rows filtered Extra
1 orders ref idx_amount 4 const 1000 10.0 Using index

解析：

  • filtered = 10%，说明大多数行被过滤

  • 实际扫描行数 ≈ 1000 × 0.1 = 100 行

  • 尽管 rows 显示 1000，但实际有效数据行数少

  • 如果 filtered 太低，优化器可能选择其他索引或访问策略

优化建议：

  1. 提高过滤效率：

    • WHERE 条件尽量精准，减少扫描无效行

    • 使用覆盖索引，让索引本身过滤掉不必要的行

  2. 结合 rows 判断成本：

    • rows 大 + filtered 低 → 实际扫描行数大，可能影响性能

    • rows 小 + filtered 高 → 查询效率最佳

  3. 分析索引与条件匹配：

    • 如果 filtered 与预期差距大，可能需要更新表统计信息（ANALYZE TABLE）

3.1.11 Extra

Extra 字段显示 MySQL 执行查询时的 额外操作信息，通常包含以下几类：

标记 含义 优化思路
Using index 覆盖索引查询（索引包含所有需要的列） 查询无需回表，性能较高
Using where 查询中存在 WHERE 条件 与 type、key 配合判断索引使用情况
Using temporary 查询使用临时表（多用于 GROUP BY、ORDER BY、DISTINCT） 优化 GROUP BY/ORDER BY，尽量消除临时表
Using filesort 查询使用额外排序 为排序列建索引，避免 filesort
Using join buffer 多表 JOIN 时使用缓冲区 确认 JOIN 是否走索引，优化索引设计
Impossible WHERE WHERE 条件永远不成立 检查逻辑或数据类型错误
Using MRR 多范围读（Multi-Range Read）优化 索引范围扫描优化，可提高 I/O 效率
Using index condition 索引条件下推（Index Condition Pushdown） MySQL 5.6+ 支持，减少回表，提高效率
示例 1：Using index（覆盖索引）
EXPLAIN SELECT user_id, amount FROM orders WHERE user_id = 123;
id table type key rows Extra
1 orders ref idx_user_amount 50 Using index

解析：

  • 查询字段全部被索引覆盖

  • 不需要访问表数据（回表），性能提升明显

示例 2：Using temporary + Using filesort
EXPLAIN SELECT user_id, SUM(amount) FROM orders GROUP BY user_id ORDER BY SUM(amount) DESC;
id table type key rows Extra
1 orders ALL NULL 1000000 Using temporary; Using filesort

解析：

  • 使用临时表存放 GROUP BY 结果

  • 使用 filesort 对 SUM(amount) 排序

  • 优化思路：

    1. 可以创建联合索引 (user_id, amount)，部分消除临时表

    2. 考虑先聚合再排序，或通过应用层排序优化

示例 3：Using where
EXPLAIN SELECT * FROM orders WHERE amount > 500;
id table type key rows Extra
1 orders ALL NULL 1000000 Using where

解析：

  • 使用 WHERE 条件过滤行

  • 未使用索引（key=NULL），导致全表扫描

  • 优化建议：为 amount 创建索引，改写查询条件

优化建议：

  1. 消除 Using temporary 与 filesort

    • 对 GROUP BY 或 ORDER BY 列建立索引

    • 使用覆盖索引减少临时表开销

  2. 利用 Using index 提升性能

    • 确保查询字段都被索引覆盖

  3. 分析 Using join buffer

    • 多表 JOIN 时检查 ref 和 key，减少缓冲区使用

  4. 关注 Impossible WHERE

    • 避免逻辑错误导致无效扫描

3.2 JSON 格式执行计划

1. 功能介绍

  • EXPLAIN FORMAT=JSON 可以返回 结构化的执行计划，以 JSON 对象形式展现查询信息；

  • 包含表信息、访问类型、索引使用情况、行估算、额外操作以及优化器决策过程；

  • 支持嵌套结构，尤其适用于复杂 JOIN、子查询和派生表场景；

  • 便于程序化分析（如通过脚本或可视化工具处理）。

2. 使用方式
EXPLAIN FORMAT=JSON
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 500;

执行后返回类似 JSON：

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "users",
          "access_type": "ALL",
          "rows_examined_per_scan": 1000,
          "filtered": 100,
          "key": null
        }
      },
      {
        "table": {
          "table_name": "orders",
          "access_type": "ref",
          "possible_keys": ["idx_userid"],
          "key": "idx_userid",
          "key_length": 4,
          "ref": ["db.users.id"],
          "rows_examined_per_scan": 50,
          "filtered": 50
        }
      }
    ]
  }
}
3. 核心字段解析
JSON 字段 含义 对应 EXPLAIN 列
table_name 表名 table
access_type 访问类型 type
key 实际使用的索引 key
possible_keys 可能可用的索引 possible_keys
key_length 索引长度 key_len
ref 索引引用条件 ref
rows_examined_per_scan 预估扫描行数 rows
filtered 行过滤率 filtered
nested_loop 多表 JOIN 嵌套结构 通过 id/order 展示 JOIN 顺序
query_block 查询块信息 select_type + id 的结构化表示
4. 优势与应用场景

  1. 结构化分析

    • 嵌套查询、派生表和 UNION 的执行顺序一目了然；

    • 对比传统 EXPLAIN 输出，需要手动判断 id 和 select_type 的层级关系，JSON 更直观。

  2. 程序化处理

    • 可以通过脚本自动分析瓶颈表、扫描行数大或 type = ALL 的表；

    • 对复杂查询生成优化报告或可视化图表。

  3. 优化器调试

    • JSON 中包含 filtered、nested_loop、used_columns 等信息，可辅助判断索引覆盖情况、临时表使用及排序方式；

    • 可用于分析为何优化器选择特定索引或 JOIN 顺序。

5. 示例优化分析

  • 查询中 orders 表使用 type = ref，key = idx_userid，filtered = 50

  • JSON 可以直接看到 nested_loop 结构，确认 users 表为驱动表，orders 表为被驱动表

  • 通过 rows_examined_per_scan 可以计算预估扫描总行数，判断是否需要优化索引或调整 JOIN 顺序

6. 优化建议

  1. 结合 JSON 输出定位瓶颈

    • rows_examined_per_scan 大、type=ALL、filtered 低 → 优先优化

  2. 分析 nested_loop

    • 确认驱动表选择合理，尽量让小表驱动大表，减少扫描量

  3. 索引覆盖检查

    • 查看 used_columns 与 key 列，确保覆盖索引生效

  4. 调试复杂查询

    • 对 UNION、派生表、子查询使用 JSON 输出，可直观理解优化器决策

3.3 Extended EXPLAIN

1. 功能介绍

  • 使用 EXPLAIN EXTENDED 可以让优化器 显示查询重写后的执行计划；

  • 与普通 EXPLAIN 不同，它还可以通过 SHOW WARNINGS 查看优化器对查询的重写信息；

  • 常用于调试复杂查询、子查询优化、索引未生效问题。

2. 使用方式
EXPLAIN EXTENDED
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 500;

执行后：

SHOW WARNINGS;

返回类似：

Note: select_rewritten as
SELECT u.name, o.amount
FROM users AS u
INNER JOIN orders AS o
  ON u.id = o.user_id
WHERE o.amount > 500

解析：

  • select_rewritten 显示优化器对原查询的重写结果

  • 优化器可能将复杂表达式展开、合并子查询、消除冗余列等

3. 核心作用

  1. 调试索引未使用问题

    • 通过查询重写后的语句，判断优化器为何未选择某个索引；

    • 例如函数包装列或隐式类型转换可能导致索引失效。

  2. 分析子查询优化

    • EXPLAIN EXTENDED 能显示子查询被重写为 JOIN 或临时表的情况；

    • 有助于理解优化器执行顺序和成本。

  3. 理解优化器决策逻辑

    • 比如 WHERE 子句顺序调整、常量折叠（constant folding）、派生表展开等；

    • 对调优复杂查询和索引设计非常有帮助。

4. 示例：索引未生效
EXPLAIN EXTENDED
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

SHOW WARNINGS;

可能输出：

Note: select_rewritten as
SELECT * FROM orders
WHERE (order_date >= '2023-01-01' AND order_date < '2024-01-01');

解析：

  • 优化器将 YEAR(order_date) 转换为范围条件

  • 原始函数表达式可能导致索引无法直接使用

  • 优化建议：直接使用范围条件避免函数包装，提升索引利用率

5. 使用技巧

  1. 结合普通 EXPLAIN

    • 先看 EXPLAIN 输出 type、key、rows 等基本信息

    • 再用 EXTENDED + SHOW WARNINGS 分析查询重写细节

  2. 检查函数、类型转换

    • 查询中使用函数或隐式转换容易导致索引失效

    • EXTENDED 能帮助确认优化器的处理方式

  3. 复杂查询调优

    • 对 UNION、子查询、派生表使用 EXPLAIN EXTENDED，判断优化器是否做了优化

    • 可辅助索引设计和查询重写

6. 小结

  • EXPLAIN EXTENDED 用于获取优化器重写后的查询信息；

  • SHOW WARNINGS 查看重写后的 SQL，便于分析索引失效或优化器行为；

  • 对复杂查询调优、索引设计优化和性能分析非常实用。

第4章：性能优化策略

MySQL 查询性能优化的核心思路是 减少扫描行数、提高索引命中率、消除额外操作（临时表、文件排序）。通过 EXPLAIN 可以直观判断性能瓶颈，优化策略包括索引优化、查询重写、分区裁剪等。

4.1 避免全表扫描

全表扫描 (type = ALL) 是性能最差的访问方式，尤其在大数据量表上影响显著。

4.1.1 原因分析

  • 查询条件列没有索引

  • 查询使用函数或类型转换，导致索引失效

  • 多表 JOIN 驱动表选择不合理

4.1.2 优化方法

  1. 创建合适的索引

    • 单列索引或联合索引覆盖查询条件

    • 优先保证常用 WHERE 条件列和 JOIN 列被索引覆盖

      -- 示例：为 orders 表 amount 列创建索引
CREATE INDEX idx_amount ON orders(amount);

EXPLAIN SELECT * FROM orders WHERE amount > 500;

  2. 避免函数包装列

    -- 不推荐
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- 推荐
SELECT * FROM orders 
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

  3. 合理选择驱动表

    • 小表优先驱动大表

    • 避免大表全表扫描带动小表

  4. 使用分区裁剪

    • 对大表按日期、地区或业务维度分区

    • 查询只扫描部分分区，降低 rows

      -- 示例：按年份分区表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

4.2 消除文件排序 (Using filesort)

文件排序通常出现在 ORDER BY 或 GROUP BY 需要排序时，未能利用索引。

4.2.1 原因分析

  • 排序字段没有索引

  • 索引顺序与 ORDER BY 列不匹配

  • 联合排序（ORDER BY 多列）未使用最左前缀索引

4.2.2 优化方法

  1. 为排序字段创建索引

    -- 示例：订单按 amount 排序
CREATE INDEX idx_amount ON orders(amount);

EXPLAIN SELECT * FROM orders ORDER BY amount DESC;

  2. 联合索引覆盖 ORDER BY 列

    -- 联合索引 idx_user_amount 覆盖 user_id 与 amount
CREATE INDEX idx_user_amount ON orders(user_id, amount);

EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY amount DESC;

  3. 尽量减少排序量

    • 结合 WHERE 条件，减少扫描行数

    • 分页查询时使用索引列作为排序列，避免 filesort

4.3 避免临时表 (Using temporary)

临时表通常出现在 GROUP BY、DISTINCT、UNION 或子查询场景。

4.3.1 原因分析

  • 查询字段未被索引覆盖

  • 聚合或排序导致 MySQL 必须建立临时表

  • 派生表或子查询未优化

4.3.2 优化方法

  1. 使用覆盖索引

    -- orders 表索引覆盖 user_id 和 amount
CREATE INDEX idx_user_amount ON orders(user_id, amount);

EXPLAIN SELECT user_id, SUM(amount) 
FROM orders 
GROUP BY user_id;

  2. 查询重写

    • 将子查询改为 JOIN

    • 提前聚合或筛选

      -- 不推荐
SELECT user_id, SUM(amount) 
FROM (SELECT * FROM orders WHERE amount > 500) t
GROUP BY user_id;

-- 推荐
SELECT user_id, SUM(amount) 
FROM orders 
WHERE amount > 500
GROUP BY user_id;

  3. 合理分页

    • 使用索引列作为分页条件，避免临时表

      -- 分页查询优化
SELECT * FROM orders
WHERE order_id > 1000
ORDER BY order_id ASC
LIMIT 100;

4.4 覆盖索引的构建

覆盖索引（covering index）可以显著提升查询性能，因为查询无需访问表数据（回表）。

4.4.1 原理

  • 查询字段全部包含在索引中

  • EXPLAIN Extra 显示 Using index

  • 减少 I/O，提高查询效率

4.4.2 示例

-- orders 表创建覆盖索引
CREATE INDEX idx_user_amount_cover ON orders(user_id, amount);

EXPLAIN SELECT user_id, amount 
FROM orders 
WHERE user_id = 123;

解析：

  • 使用 idx_user_amount_cover

  • Extra = Using index，说明查询只读取索引数据

  • 避免回表，性能提升明显

4.4.3 优化建议

  1. 优先覆盖高频查询

    • 对业务查询量大、访问频繁的字段创建覆盖索引

  2. 结合 WHERE 和 ORDER BY

    • 覆盖索引不仅减少回表，还可消除 filesort 和临时表

  3. 注意索引列顺序

    • 最左前缀原则，确保索引列顺序与查询条件匹配

本章小结

  • 避免全表扫描：合理索引、分区表、函数优化

  • 消除文件排序：索引覆盖 ORDER BY、联合索引

  • 避免临时表：覆盖索引、查询重写、优化分页

  • 构建覆盖索引：减少回表、消除额外操作

通过 EXPLAIN 各字段和 JSON/EXTENDED 输出，可以快速定位性能瓶颈并应用优化策略。

# mysql # 性能优化 # 数据库
Logo
2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

C/C++ Linux系统编程：解析基本的进程创建、控制与进程状态，详解system，execve、waitpid函数

在开始介绍基本的函数之前，首先需要理解什么是"进程"。可以把一个进程想象成一个正在执行的程序。Linux学习篇10——Linux 进程管理深度解析：进程基础、常用指令、服务管理与系统监控-CSDN博客程序是静态的，是存储在磁盘上的可执行文件；而进程是动态的，是程序被加载到内存中并执行的一个实例。参数pathname：要执行的可执行文件的完整路径（如/bin/ls参数argv：传递给新程序的参数列表

avatar 2048 AI社区

法律研究中的“数据孤岛”怎么破？AI应用架构师的5个数据挖掘策略

假设你是一名律师，要处理一起“网购假货”的案件。找类似判决案例（比如最高法院的指导案例）；查相关法律法规（《消费者权益保护法》第55条）；看学术观点（学者对“假货”的定义解读）；参考行业数据（电商平台的假货投诉率）。但现实是：这些数据分散在中国裁判文书网（判决）、北大法宝（法规）、中国知网（论文）、阿里合规报告（行业数据）等多个系统，每个系统的搜索规则不同，数据格式（PDF/Word/数据库）不统

avatar 2048 AI社区

AI系统架构设计：AI应用架构师的实战经验总结

构建可靠、高效的AI系统远不止模型训练那么简单。本文汇集了资深AI应用架构师的实战经验，深入探讨AI系统架构设计的核心原则、关键组件、设计模式和最佳实践。通过生动的比喻、真实案例和代码示例，我们将带领读者从概念理解到实际落地，掌握构建生产级AI系统的完整知识体系。无论你是初涉AI领域的工程师，还是希望提升系统设计能力的技术负责人，这篇文章都将帮助你避开常见陷阱，设计出既满足业务需求又具备技术弹性的

avatar 2048 AI社区