深入解析 MySQL EXPLAIN：从零读懂查询执行计划

2026-02-04 16:42:37 0条评论 3次阅读 0人点赞

在日常的数据库工作中，你是否曾经遇到过这样的情况：一条看起来逻辑并不复杂的 SQL 语句，在生产环境中执行起来却慢如蜗牛？作为开发人员或数据库管理员，我们往往习惯于从代码逻辑或硬件资源层面去排查问题，却容易忽视数据库内部究竟是如何“理解”并执行我们的 SQL 的。

这正是我们今天要探讨的核心话题：MySQL EXPLAIN。它能让我们拥有透视数据库内部运作的“超能力”，让我们在查询真正执行之前，就预判它的性能瓶颈。在这篇文章中，我们将深入探讨如何解读 EXPLAIN 的输出结果，分析查询执行计划，并以此为依据优化我们的数据库性能。让我们开始这段探索之旅吧。

什么是 MySQL EXPLAIN？

简单来说，MySQL EXPLAIN 是一个强大的分析工具，它为我们展示了 MySQL 优化器是如何处理 SELECT 语句的。当你不确定为什么数据库没有使用你预期的索引，或者想知道一个复杂的 JOIN 操作具体是按什么顺序连接表时，EXPLAIN 就是你手中的放大镜。

通过在查询前加上 EXPLAIN 关键字，MySQL 不会真正执行查询，而是返回一张“执行计划表”。这张表中的每一行都代表了查询的一部分，而不同的列则告诉我们关于访问方法、使用的索引、扫描的行数以及连接类型等关键信息。掌握它，就意味着你从“盲目优化”进阶到了“有的放矢”的专业阶段。

语法基础

使用 EXPLAIN 非常简单，其标准语法结构如下：

-- 标准用法
EXPLAIN SELECT column1, column2
FROM table_name
WHERE condition;

-- 在 MySQL 8.0.18+ 中，还可以分析其他语句
EXPLAIN TABLE table_name;

深入理解 EXPLAIN 输出列

在正式进入示例之前，我们需要先建立一套通用的认知体系。当你执行 EXPLAIN 后，会看到包含以下列的表格。让我们逐一看看这些列的实际含义：

  • id: 查询的序列号。这表示查询中 SELECT 子句的执行顺序。数字越大越先执行；如果数字相同，则从上往下执行。
  • select_type: 查询的类型，主要区分是简单查询、联合查询还是子查询。
  • table: 当前这一行正在访问的是哪张表。
  • partitions: 匹配的分区信息（如果表是分区表的话）。
  • type: 最重要的列之一。它展示了访问类型，即 MySQL 决定了如何查找表中的行。性能从好到差依次为：system > const > eq_ref > ref > range > index > ALL。我们的目标是尽量避免 ALL（全表扫描）。
  • possible_keys: 指出 MySQL 能使用哪些索引来优化查询。
  • key: 实际决定使用的索引。如果为 NULL，说明没有使用索引。
  • key_len: 使用的索引长度。越短通常越好（在不损失精度的情况下），这意味着索引更高效且可能覆盖了查询。
  • ref: 显示索引的哪一列被使用了，比如是一个常数或者是某个表的列。
  • rows: 估算需要扫描的行数。这是一个非常重要的估算值，数值越小越好。
  • filtered: 存储引擎返回的数据经过服务层过滤后剩余的百分比。
  • Extra: 包含额外的重要信息，比如“Using index”（使用了覆盖索引）或“Using filesort”（需要额外排序，应尽量避免）。

实战案例解析：从基础到进阶

光说不练假把式。让我们通过几个具体的场景，来看看 EXPLAIN 在实战中是如何发挥作用的。

场景 1：基础聚合与分组问题

让我们设想一个场景，假设我们有一个名为 INLINECODEea540677 的表，其中包含 INLINECODE8e371615、INLINECODE35072513、INLINECODE7b313f58 和 total_amount 这些列。我们想要检索每个客户所下订单的总金额。

首先，我们创建并填充数据：

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- 插入一些示例数据
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 1, ‘2024-04-01‘, 100.00),
(2, 1, ‘2024-04-05‘, 150.00),
(3, 2, ‘2024-04-10‘, 200.00),
(4, 2, ‘2024-04-15‘, 120.00);

现在，我们执行一个带有 GROUP BY 的查询，并使用 EXPLAIN 进行分析：

EXPLAIN SELECT customer_id, SUM(total_amount) AS total_orders 
FROM orders 
GROUP BY customer_id;

输出结果解析：

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using temporary |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------+

我们可以读出以下信息：

  • select_type 是 SIMPLE：这表明这是一个简单的查询，不包含子查询或 UNION。
  • type 是 ALL：这是一个性能警报！这意味着 MySQL 必须对整个 orders 表进行全表扫描。尽管数据量只有 4 行，但在生产环境中如果是百万级数据，这将非常缓慢。
  • Extra 显示 Using temporary：这也需要警惕。这意味着 MySQL 为了完成 GROUP BY 操作，需要创建一个临时表来存储中间结果。如果数据量大，这会导致严重的磁盘 I/O 开销。

优化建议： 为了消除全表扫描和临时表，我们可以考虑在 customer_id 上建立索引，或者如果经常按客户分组，调整表结构。

场景 2：排序与文件排序的性能陷阱

让我们考虑另一个场景，假设我们有一个名为 products 的表。我们想要检索特定类别下的所有产品，并按价格降序排列。

创建 products 表：

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(255),
    price DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO products (category_id, name, price) VALUES
(1, ‘Product 1‘, 10.99),
(1, ‘Product 2‘, 20.49),
(2, ‘Product 3‘, 15.99),
(1, ‘Product 4‘, 8.50),
(3, ‘Product 5‘, 30.00);

执行 EXPLAIN 分析：

EXPLAIN SELECT * 
FROM products 
WHERE category_id = 1 
ORDER BY price DESC;

输出结果解析：

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | products | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+

关键发现：

请关注 Extra 列中的 Using filesort。这是一个潜在的痛点。虽然 MySQL 可以通过 WHERE 条件过滤出 category_id = 1 的数据，但由于我们缺少合适的索引，MySQL 必须在内存中（或如果数据太大则在磁盘上）对结果进行额外的排序操作。这种“文件排序”操作是 CPU 密集型的，在大数据量下会显著降低查询速度。

解决思路： 如果我们经常按 INLINECODE0932eba3 查询并按 INLINECODEa77a71f8 排序，最佳实践是创建一个联合索引 (category_id, price)。这样，数据本身就是按照该顺序存储的，MySQL 可以直接读取有序数据，从而避免 filesort。

场景 3：子查询与连接的较量

在处理多表查询时，我们往往会纠结于是用 IN 子查询还是用 JOIN。让我们看看 EXPLAIN 如何揭示这两种写法的内部差异。

假设我们有两张表：INLINECODE9890c09e 和 INLINECODEc220a04e。我们想找出“下过订单的客户”。

准备数据：

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO customers VALUES (1, ‘Alice‘), (2, ‘Bob‘), (3, ‘Charlie‘);

-- 复用之前的 orders 表数据

方案 A：使用 IN 子查询

EXPLAIN SELECT * 
FROM customers 
WHERE customer_id IN (SELECT customer_id FROM orders);

分析子查询计划（MySQL 5.6 优化后）：

在现代 MySQL 版本中，优化器通常会将 IN 子查询优化为一种类似于 SEMI-JION（半连接）的形式。如果看到 selecttype 出现 INLINECODEa8585e86 或 MATERIALIZED，这表明子查询的执行方式是被相关联执行的，或者是先物化成一个临时表。在旧版本中，你可能会看到糟糕的重度循环依赖，导致性能极差。

方案 B：使用 INNER JOIN

EXPLAIN SELECT customers.* 
FROM customers 
INNER JOIN orders ON customers.customer_id = orders.customer_id;

分析 JOIN 计划：

在 JOIN 的输出中，你会看到两行数据。

  • 第一行（id=1）通常是驱动表（根据统计信息选择，通常是 customers）。
  • 第二行（id=1，但 table 是 orders）是被驱动表。

重点关注 type 列：

  • 如果 INLINECODEdd69d9b7 表的 INLINECODEafe26b71 是 INLINECODEc763e36b，这意味着对于 INLINECODE44dce7dd 表中的每一行，orders 表中只能找到唯一一行匹配（通常是因为 JOIN 的列是主键或唯一索引）。这是最高效的连接类型。
  • 对比之下，如果是 IN 子查询且未优化好，可能会出现 INLINECODE143346c7 或依赖子查询，性能通常不如 INLINECODE7330408a 稳定。

场景 4：强制索引与索引失效陷阱

有时候，MySQL 优化器也会“犯傻”，选择了错误的索引，或者干脆没选索引。这时候，我们可以用 FORCE INDEX 来干预。

假设我们在 INLINECODE9dd7241d 表的 INLINECODE8400d8ba 列上加了一个索引，但我们要查询所有商品（或者一个很大的范围）。

CREATE INDEX idx_price ON products(price);

EXPLAIN SELECT * FROM products WHERE price > 5;

结果分析：

你可能会惊讶地发现，尽管 INLINECODE06d89bf8 有索引，但 INLINECODEcd5de32e 依然是 ALL（全表扫描）。

为什么？

这是因为优化器经过计算发现，如果要回表（回到主键索引去查其他列）的次数太多（比如超过表中总行数的 20%-30%），直接全表扫描反而更快。这也就是所谓的“索引失效”场景之一。

如果你明确知道这没问题（比如你只想要 price 数据，不需要 *，即覆盖索引），或者数据分布不均，你可以强制使用索引：

EXPLAIN SELECT * FROM products FORCE INDEX(idx_price) WHERE price > 5;

这告诉我们：不要盲目迷信索引，要看 EXPLAIN 的 INLINECODE3696fba2 估算和 INLINECODEe30fa80e 信息（是否有 Using index，即覆盖索引）。

常见性能指标速查表

在分析结果时，你可以参考以下经验法则：

  • type 列的优先级：

– 好：INLINECODE96bb3daa, INLINECODE989b3f3c, range。

– 中：index（索引扫描，依然比全表快，但读取了整个索引树）。

– 差：ALL（全表扫描，必须优化）。

  • Extra 列的关键词：

– Using index：完美！使用了覆盖索引，不需要回表查数据。

– Using where：正常，服务器层在使用 WHERE 条件过滤。

– Using temporary：警惕！使用了临时表处理查询（常见于 GROUP BY, DISTINCT）。

– Using filesort：警惕！需要额外的排序步骤，消耗内存和 CPU。

  • rows 列：

– 这是一个估算值，但它直接影响了查询的 CPU 和 I/O 开销。尽量通过索引让它变小。

总结与后续步骤

通过阅读这篇文章，我们学习了如何使用 EXPLAIN 来拆解 MySQL 的查询执行计划。我们从最基础的语法入手，深入了解了每一列的含义，并通过四个不同的实战场景——从聚合分析到排序优化，再到子查询与 JOIN 的选择，以及索引失效的边缘情况——掌握了如何像专家一样分析查询性能。

掌握 EXPLAIN 并不是一蹴而就的，它需要我们在实际工作中不断地尝试和验证。当你遇到慢查询时，不要急着去修改代码，先让它 EXPLAIN 一下，问问数据库：“你打算怎么干？”答案往往就藏在那个表格的 INLINECODEa070dca3 和 INLINECODEa1106684 列里。

接下来的建议：

  • 建立监控习惯：在你的开发环境中，对每一个上线的复杂查询都跑一遍 EXPLAIN。
  • 持续优化：数据库的数据是动态变化的，现在的最优索引可能半年后就不是了，定期回顾执行计划。
  • 学习更多：深入了解 EXPLAIN FORMAT=JSON，它提供了比表格形式更详细的嵌套信息，适合解决极复杂的性能问题。

现在，打开你的数据库终端，试着对你手头最复杂的那个查询执行一次 EXPLAIN 吧！

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。如需转载，请注明文章出处豆丁博客和来源网址。https://shluqu.cn/25799.html
点赞
0.00 平均评分 (0% 分数) - 0 票

相关文章美国1G带宽/1T流量高速vps $17.99/年