MySQL 查询优化：使用 EXPLAIN 分析查询执行计划

什么是查询执行计划

在深入探讨 EXPLAIN 如何帮助我们进行查询优化之前，先来理解一下什么是查询执行计划。

当我们在 MySQL 中执行一条 SELECT 语句时，数据库需要决定如何最有效地检索我们所请求的数据。查询执行计划就是 MySQL 决定如何执行查询的详细步骤描述。它包括从哪个表开始读取数据，以何种顺序连接多个表，使用哪些索引，以及如何对数据进行排序等操作。

MySQL 的查询优化器负责生成查询执行计划。优化器的目标是找到一种执行查询的方式，使得在给定的硬件资源和数据分布情况下，尽可能快地返回结果。优化器会考虑多种因素，例如表的大小、索引的可用性、数据的分布以及查询条件等。

例如，假设有两个表 orders 和 customers，并且我们想要查询每个客户的订单信息。可能的执行计划之一是先扫描 orders 表，然后对于 orders 表中的每一行，在 customers 表中查找对应的客户信息。另一种可能是先扫描 customers 表，然后再查找相关的订单。查询优化器会根据各种因素来决定哪种方式更高效。

EXPLAIN 简介

EXPLAIN 是 MySQL 提供的一个强大工具，用于查看查询执行计划。通过在 SELECT 语句前加上 EXPLAIN 关键字，我们可以获取关于该查询如何执行的详细信息。这些信息包括表的访问顺序、使用的索引、预计的行数等。

例如，对于以下简单的查询：

SELECT * FROM users WHERE age > 30;

我们可以使用 EXPLAIN 来分析它：

EXPLAIN SELECT * FROM users WHERE age > 30;

执行上述 EXPLAIN 语句后，MySQL 会返回一个结果集，包含了关于该查询执行计划的详细信息。这个结果集通常包含多个列，每个列都提供了特定的信息，下面我们将详细介绍这些列的含义。

EXPLAIN 输出结果详解

id 列

id 列是 EXPLAIN 输出结果中的一个重要列，它标识了查询中每个 SELECT 子句的标识符。在复杂的查询中，可能会有多个 SELECT 子句，例如包含子查询或者联合查询（UNION）。

1. 单 SELECT 查询：对于简单的单个 SELECT 查询，id 的值通常为 1。例如：

EXPLAIN SELECT * FROM products;

这里 id 为 1，表示这是一个简单的单 SELECT 查询。

2. 子查询：当查询中包含子查询时，每个子查询会有一个唯一的 id 值。例如：

EXPLAIN SELECT * FROM orders WHERE customer_id = (SELECT customer_id FROM customers WHERE city = 'New York');

在这个例子中，外层的 SELECT 语句 id 为 1，而子查询 (SELECT customer_id FROM customers WHERE city = 'New York') 的 id 会是 2（假设没有其他更复杂的嵌套）。MySQL 会按照 id 值从大到小的顺序执行查询。也就是说，先执行 id 为 2 的子查询，然后再执行 id 为 1 的外层查询。

3. 联合查询（UNION）：在联合查询中，每个 SELECT 语句也会有一个唯一的 id。例如：

EXPLAIN SELECT product_name FROM products WHERE price < 100 UNION SELECT product_name FROM products WHERE category = 'electronics';

这里两个 SELECT 语句的 id 可能分别为 1 和 2。MySQL 会分别执行这两个 SELECT 语句，然后将结果合并起来。

select_type 列

select_type 列描述了 SELECT 子句的类型。常见的 select_type 值有以下几种：

1. SIMPLE：表示简单的 SELECT 查询，不包含子查询或联合查询。例如：

EXPLAIN SELECT * FROM employees WHERE department = 'HR';

这里的 select_type 为 SIMPLE。

2. PRIMARY：对于包含子查询或联合查询的复杂查询，最外层的 SELECT 语句的 select_type 为 PRIMARY。例如：

EXPLAIN SELECT * FROM orders WHERE order_amount > (SELECT AVG(order_amount) FROM orders);

外层的 SELECT 语句的 select_type 是 PRIMARY。

3. SUBQUERY：子查询中的第一个 SELECT 语句的 select_type 为 SUBQUERY。在前面的例子中，(SELECT AVG(order_amount) FROM orders) 这个子查询的 select_type 就是 SUBQUERY。

4. DERIVED：当子查询出现在 FROM 子句中时，这个子查询被视为派生表，其 select_type 为 DERIVED。例如：

EXPLAIN SELECT * FROM (SELECT product_name, price FROM products WHERE category = 'clothes') AS derived_table;

这里子查询 (SELECT product_name, price FROM products WHERE category = 'clothes') 的 select_type 为 DERIVED。

5. UNION：在联合查询中，除了第一个 SELECT 语句外，其他 SELECT 语句的 select_type 为 UNION。例如：

EXPLAIN SELECT product_name FROM products WHERE price < 100 UNION SELECT product_name FROM products WHERE category = 'electronics';

第二个 SELECT 语句的 select_type 是 UNION。

6. UNION RESULT：当 EXPLAIN 输出结果中有联合查询时，会有一个额外的行，其 select_type 为 UNION RESULT，用于表示联合查询的结果合并操作。

table 列

table 列很直观，它显示了 EXPLAIN 输出结果所对应的表名。在单表查询中，这就是我们查询的表。例如：

EXPLAIN SELECT * FROM suppliers;

table 列的值为 suppliers。

在多表连接查询中，table 列会列出每个参与连接的表。例如：

EXPLAIN SELECT * FROM orders JOIN order_items ON orders.order_id = order_items.order_id;

EXPLAIN 的输出结果中会有两行，table 列分别显示 orders 和 order_items，表示这两个表参与了连接操作。

在子查询或派生表的情况下，table 列可能会显示 <derivedN> 或 <subqueryN>，其中 N 是对应的子查询或派生表的 id。例如：

EXPLAIN SELECT * FROM (SELECT product_name, price FROM products WHERE category = 'clothes') AS derived_table;

这里 table 列会显示 <derived2>（假设子查询的 id 为 2）。

partitions 列

partitions 列在表进行分区的情况下才有意义。它显示了查询将访问的分区。

假设我们有一个按日期分区的 sales 表，查询某个时间段的销售数据：

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2),
    PARTITION BY RANGE (YEAR(sale_date)) (
        PARTITION p0 VALUES LESS THAN (2020),
        PARTITION p1 VALUES LESS THAN (2021),
        PARTITION p2 VALUES LESS THAN (2022)
    )
);

EXPLAIN SELECT * FROM sales WHERE sale_date BETWEEN '2020-01-01' AND '2020-12-31';

在这个例子中，partitions 列可能会显示 p1，表示查询只会访问 2020 年对应的分区 p1。如果查询涉及多个分区，partitions 列会列出所有相关的分区。

type 列

type 列是非常关键的一列，它显示了表的访问类型。不同的访问类型效率差异很大，从最佳到最差的常见类型顺序为：system > const > eq_ref > ref > range > index > ALL。

1. system：这是最理想的访问类型，表示表只有一行记录（系统表）。这种情况非常少见，通常在查询系统表时可能出现。例如，查询 information_schema.tables 中特定的系统表信息，并且该表只有一行满足条件时可能出现 system 类型。

2. const：const 类型表示通过索引一次就找到记录。当查询条件使用了主键或者唯一索引，并且条件值是常量时，通常会出现这种类型。例如：

EXPLAIN SELECT * FROM users WHERE user_id = 1;

如果 user_id 是主键，那么这里的 type 可能就是 const。MySQL 可以直接通过索引找到 user_id 为 1 的记录，效率非常高。

3. eq_ref：eq_ref 类型通常出现在多表连接中，并且连接条件是使用主键或者唯一索引。对于每个来自前面表的行组合，从当前表中读取一行。例如：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT
);

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

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

在这个例子中，如果 orders 表和 customers 表的连接条件是基于主键 customer_id，那么 customers 表的 type 可能就是 eq_ref。MySQL 可以高效地通过 orders 表中的 customer_id 值，在 customers 表中找到对应的唯一记录。

4. ref：ref 类型表示使用非唯一索引进行查找。当查询条件使用了非唯一索引，并且条件值是常量时，可能会出现这种类型。例如：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    category VARCHAR(50),
    INDEX(category)
);

EXPLAIN SELECT * FROM products WHERE category = 'electronics';

这里 category 是一个非唯一索引，查询条件 category = 'electronics'，type 可能就是 ref。MySQL 会通过 category 索引找到所有 category 为 electronics 的记录。

5. range：range 类型表示使用索引范围扫描。当查询条件使用了 BETWEEN、IN、>, < 等操作符，并且涉及索引列时，通常会出现这种类型。例如：

EXPLAIN SELECT * FROM products WHERE product_id BETWEEN 10 AND 20;

如果 product_id 是索引列，那么 type 就是 range。MySQL 会在 product_id 索引上进行范围扫描，找到满足条件的记录。

6. index：index 类型表示全索引扫描。MySQL 会扫描整个索引树来获取数据，而不是扫描表数据。这种情况通常发生在查询需要的所有列都包含在索引中，也就是所谓的“覆盖索引”。例如：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    INDEX(first_name, last_name)
);

EXPLAIN SELECT first_name, last_name FROM employees WHERE first_name LIKE 'A%';

这里查询的列 first_name 和 last_name 都在索引中，type 可能就是 index。虽然是全索引扫描，但由于不需要回表获取数据，效率也相对较高。

7. ALL：ALL 类型是最差的访问类型，表示全表扫描。MySQL 会扫描整个表来查找满足条件的记录。例如：

EXPLAIN SELECT * FROM orders WHERE order_date LIKE '%2022%';

这里 order_date 没有合适的索引，MySQL 只能全表扫描 orders 表来查找满足条件的记录，效率非常低。

possible_keys 列

possible_keys 列显示了在查询执行过程中可能使用的索引。这些索引是根据查询条件和表结构推断出来的。例如：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    INDEX(username),
    INDEX(email)
);

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

在这个例子中，possible_keys 列可能会显示 username，表示在查询过程中可能会使用 username 索引来查找记录。

需要注意的是，possible_keys 列列出的索引并不一定都会被实际使用，实际使用的索引会在 key 列中显示。

key 列

key 列显示了 MySQL 在查询执行过程中实际使用的索引。如果 key 列的值为 NULL，表示没有使用索引，可能需要进一步优化查询。例如：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

EXPLAIN SELECT * FROM products WHERE product_name LIKE '%widget%';

这里 product_name 没有合适的索引，key 列会显示 NULL，MySQL 会全表扫描 products 表。

如果我们在 product_name 列上添加一个索引：

CREATE INDEX idx_product_name ON products(product_name);

EXPLAIN SELECT * FROM products WHERE product_name LIKE '%widget%';

再次执行 EXPLAIN，key 列可能会显示 idx_product_name，表示 MySQL 使用了这个索引来执行查询。

key_len 列

key_len 列显示了实际使用的索引的长度。这个长度对于判断索引使用是否有效很有帮助。例如，在复合索引的情况下，key_len 可以告诉我们索引的哪些部分被实际使用了。

假设我们有一个复合索引 (col1, col2, col3)：

CREATE TABLE data (
    id INT PRIMARY KEY,
    col1 VARCHAR(50),
    col2 VARCHAR(50),
    col3 VARCHAR(50),
    INDEX idx_col1_col2_col3 (col1, col2, col3)
);

EXPLAIN SELECT * FROM data WHERE col1 = 'value1' AND col2 = 'value2';

在这个例子中，key_len 的值会反映出 col1 和 col2 被使用的索引长度。通过 key_len 的值，我们可以判断复合索引是否被充分利用。如果 key_len 的值小于复合索引中所有列的总长度，可能意味着索引没有被完全利用，需要进一步优化查询或索引结构。

ref 列

ref 列显示了哪些列或常量被用于与 key 列中显示的索引进行比较。例如：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    INDEX(customer_id)
);

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

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

在这个例子中，对于 orders 表，ref 列可能会显示 customers.customer_id，表示使用 customers 表中的 customer_id 列与 orders 表的 customer_id 索引进行比较。

如果查询条件是常量，ref 列会显示常量值。例如：

EXPLAIN SELECT * FROM products WHERE category = 'electronics';

这里 ref 列可能会显示 const，表示使用常量 'electronics' 与 category 索引进行比较。

rows 列

rows 列显示了 MySQL 估计为了执行查询需要扫描的行数。这只是一个估计值，实际的行数可能会有所不同，但它可以帮助我们大致了解查询的成本。例如：

EXPLAIN SELECT * FROM orders WHERE order_amount > 1000;

rows 列的值会显示 MySQL 估计需要扫描多少行 orders 表的数据来满足查询条件。如果这个值很大，说明查询可能需要扫描大量数据，效率可能较低，需要进一步优化。

filtered 列

filtered 列表示表中满足条件的记录所占的百分比估计值。例如，如果 filtered 的值为 50%，表示 MySQL 估计表中大约有 50% 的记录满足查询条件。结合 rows 列，我们可以更准确地估计查询需要处理的数据量。例如，rows 列显示需要扫描 1000 行，filtered 列显示 50%，那么实际满足条件的记录大约为 500 行。

Extra 列

Extra 列包含了一些额外的信息，这些信息对于查询优化非常有帮助。常见的 Extra 信息有以下几种：

1. Using index：表示查询使用了覆盖索引，即查询所需的所有列都包含在索引中，不需要回表操作。这是一种高效的查询方式。例如：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    INDEX(first_name, last_name)
);

EXPLAIN SELECT first_name, last_name FROM employees WHERE first_name LIKE 'A%';

这里 Extra 列可能会显示 Using index。

2. Using where：表示查询需要通过 WHERE 条件来过滤数据。这是比较常见的情况，如果查询没有使用索引来过滤数据，Extra 列可能会显示这个信息。例如：

EXPLAIN SELECT * FROM products WHERE product_name LIKE '%widget%';

这里由于没有合适的索引，MySQL 需要通过全表扫描并使用 WHERE 条件过滤数据，Extra 列会显示 Using where。

3. Using temporary：表示 MySQL 在执行查询过程中需要使用临时表来存储中间结果。这通常发生在需要进行分组（GROUP BY）、排序（ORDER BY）或者联合查询（UNION）等操作时。例如：

EXPLAIN SELECT category, COUNT(*) FROM products GROUP BY category;

这里 Extra 列可能会显示 Using temporary 和 Using filesort，表示 MySQL 需要创建临时表来存储分组结果，并且需要进行文件排序操作。这种情况可能会导致性能问题，需要进一步优化。

4. Using filesort：表示 MySQL 需要进行文件排序操作来满足 ORDER BY 或 GROUP BY 子句的要求。如果 Extra 列显示 Using filesort，说明查询的性能可能会受到影响，因为文件排序通常比索引排序慢。例如：

EXPLAIN SELECT * FROM orders ORDER BY order_date;

如果 order_date 没有合适的索引，Extra 列会显示 Using filesort。

5. Range checked for each record (index map: N)：表示 MySQL 会对每个记录进行范围检查，N 是索引掩码。这种情况通常发生在查询条件涉及多个索引，并且 MySQL 需要对每个记录检查不同的索引范围。例如，在一个包含多个复杂条件的查询中，可能会出现这种情况。

使用 EXPLAIN 优化查询的示例

单表查询优化

假设我们有一个 employees 表，结构如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

现在我们要查询 department 为 'HR' 且 salary 大于 5000 的员工信息：

EXPLAIN SELECT * FROM employees WHERE department = 'HR' AND salary > 5000;

假设 EXPLAIN 输出结果中 type 为 ALL，key 为 NULL，说明没有使用索引，查询效率较低。我们可以考虑在 department 和 salary 列上添加索引：

CREATE INDEX idx_department_salary ON employees(department, salary);

再次执行 EXPLAIN：

EXPLAIN SELECT * FROM employees WHERE department = 'HR' AND salary > 5000;

此时 type 可能变为 range，key 显示为 idx_department_salary，说明索引生效，查询效率得到提升。

多表连接查询优化

假设有两个表 orders 和 order_items，结构如下：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    INDEX(order_id)
);

我们要查询每个订单的详细信息，包括订单日期、产品 ID 和数量：

EXPLAIN SELECT orders.order_date, order_items.product_id, order_items.quantity
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id;

假设 EXPLAIN 输出结果中 order_items 表的 type 为 ALL，说明连接操作效率较低。由于 order_items 表已经在 order_id 列上有索引，我们可以确保 orders 表的连接列 order_id 也有合适的索引（通常主键索引已经满足要求）。如果 orders 表没有合适的索引，可能需要添加。

另外，如果查询结果需要按照 order_date 排序：

EXPLAIN SELECT orders.order_date, order_items.product_id, order_items.quantity
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id
ORDER BY orders.order_date;

如果 Extra 列显示 Using filesort，我们可以在 orders 表的 order_date 列上添加索引：

CREATE INDEX idx_order_date ON orders(order_date);

再次执行 EXPLAIN，可能 Extra 列不再显示 Using filesort，查询性能得到提升。

子查询优化

假设有 products 表和 categories 表，结构如下：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    price DECIMAL(10, 2)
);

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);

我们要查询每个类别中价格最高的产品：

EXPLAIN SELECT product_name, price
FROM products
WHERE (category_id, price) IN (
    SELECT category_id, MAX(price)
    FROM products
    GROUP BY category_id
);

假设 EXPLAIN 输出结果显示子查询效率较低。我们可以尝试将子查询改写为连接查询：

EXPLAIN SELECT p.product_name, p.price
FROM products p
JOIN (
    SELECT category_id, MAX(price) AS max_price
    FROM products
    GROUP BY category_id
) sub ON p.category_id = sub.category_id AND p.price = sub.max_price;

通过这种方式，可能会提高查询效率。具体效果需要根据实际数据量和表结构进行测试和分析。

总结 EXPLAIN 的重要性

通过以上对 EXPLAIN 输出结果各列的详细介绍以及优化示例，我们可以看到 EXPLAIN 在 MySQL 查询优化中起着至关重要的作用。它让我们深入了解 MySQL 是如何执行查询的，帮助我们发现查询中存在的性能问题，例如是否使用了合适的索引、是否进行了全表扫描、是否存在文件排序或临时表操作等。

利用 EXPLAIN 的信息，我们可以针对性地进行优化，如添加或调整索引、改写查询语句等，从而显著提高查询性能，减少数据库的负载，提升应用程序的响应速度。在实际的数据库开发和维护工作中，熟练掌握和运用 EXPLAIN 是每个数据库工程师必备的技能。

同时，需要注意的是，查询优化是一个复杂的过程，不仅涉及到 EXPLAIN 分析，还需要考虑数据库的整体架构、数据分布、硬件资源等多种因素。但 EXPLAIN 始终是我们优化查询的重要起点和有力工具。