MYSQL EXPLAIN 参考手册

作者

1. EXPLAIN 的作用：

简单来说，EXPLAIN 命令会返回 MySQL 执行 SELECT 查询的计划，包括：

  • 表的访问顺序： 哪个表先访问，哪个表后访问。
  • 表的访问方式： 是全表扫描，还是使用索引？
  • 使用的索引： 具体使用了哪个索引？
  • 扫描的行数： 预计要扫描多少行才能找到结果？
  • 额外的优化信息： 是否使用了临时表、排序等操作？

2. EXPLAIN 的用法：

在 SELECT 查询前面加上 EXPLAIN 关键字即可。

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

3. EXPLAIN 输出结果解读：

EXPLAIN 命令会返回一个表格，其中最重要的列包括：

  • id： 查询的标识符。 如果一个查询包含多个子查询，每个子查询都会有一个 id。id 值越大，优先级越高，越先执行。id相同，从上往下执行。 id 为 NULL 通常表示一个 UNION 操作的结果。
  • select_type： 查询的类型，常见的值有：
    • SIMPLE：简单查询，不包含子查询或 UNION。
    • PRIMARY：最外层的 SELECT 查询。
    • SUBQUERY：子查询。
    • DERIVED：在 FROM 子句中的子查询。
    • UNION：UNION 操作中的第二个或后续的 SELECT 查询。
    • UNION RESULT：从 UNION 的临时表检索结果。
  • table： 查询访问的表名。
  • partitions： 查询涉及到的分区。
  • type： 访问类型，表示 MySQL 如何查找表中的行，这是最重要的列之一，常见的值有（性能从好到坏）：
    • system：表只有一行记录，这是 const 类型的特例，通常不会出现。
    • const：MySQL 可以通过索引一次定位到目标数据，通常发生在使用了主键或唯一索引进行等值查询的情况下。
    • eq_ref：类似 const，但用于多表连接查询，通常发生在使用了主键或唯一索引进行连接的情况下。
    • ref：使用非唯一索引进行等值查询。
    • range：使用索引进行范围查询。
    • index：扫描整个索引树，通常发生在查询只涉及索引列的情况下。
    • ALL：全表扫描，性能最差，应该尽量避免。
  • possible_keys： MySQL 可能使用的索引。
  • key： MySQL 实际使用的索引。 如果为 NULL，表示没有使用索引。
  • key_len： 实际使用的索引的长度，单位是字节。
  • ref： 使用哪个列或常数与索引列进行比较。
  • rows： 估计要扫描的行数。 这个值越小越好，表示 MySQL 可以更快地找到结果。
  • filtered： 经过 WHERE 条件过滤后，剩余的行数百分比。
  • Extra： 包含一些额外的信息，常见的值有：
    • Using index：表示使用了覆盖索引，不需要回表查询。 性能很好。
    • Using where：表示需要使用 WHERE 子句来过滤数据。
    • Using temporary：表示使用了临时表来存储中间结果，通常发生在 ORDER BY 或 GROUP BY 操作中。 性能较差。
    • Using filesort：表示需要对结果进行排序，通常发生在 ORDER BY 操作中。 性能很差，应该尽量避免。
    • Using join buffer (Block Nested Loop): 表明使用了连接缓存来加速连接操作。

4. 如何利用 EXPLAIN 进行优化：

  • 尽量避免 ALL 类型的访问： 如果 type 列的值是 ALL，表示全表扫描，应该尽量优化查询，使用索引来避免全表扫描。
  • 检查 key 列： 确认 MySQL 实际使用了索引。 如果 key 列的值为 NULL，表示没有使用索引，需要考虑添加索引或者修改查询条件。
  • 减少 rows 的值： 尽量减少 MySQL 需要扫描的行数。 可以通过添加索引、优化查询条件等方式来减少 rows 的值。
  • 避免 Using temporary 和 Using filesort： 这两种情况都表示性能较差，应该尽量避免。 可以通过优化查询、添加索引等方式来避免使用临时表和文件排序。
  • 利用覆盖索引： 如果 Extra 列的值包含 Using index，表示使用了覆盖索引，性能很好。 应该尽量利用覆盖索引来避免回表查询。

5. 案例分析：

假设有以下表结构和数据：

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_order_date` (`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `orders` (`customer_id`, `order_date`, `amount`) VALUES
(123, '2023-01-01', 100.00),
(123, '2023-01-02', 200.00),
(456, '2023-01-03', 300.00),
(123, '2023-01-04', 400.00),
(789, '2023-01-05', 500.00);

执行以下查询：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

EXPLAIN 的输出结果如下：

+----+-------------+-------+------------+---------------------+---------------------+---------+-------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+---------------------+---------------------+---------+-------+------+-------------+
| 1 | SIMPLE | orders | NULL | ref | idx_customer_id,idx_order_date | idx_customer_id | 4 | const | 3 | 33.33 | Using where |
+----+-------------+-------+------------+---------------------+---------------------+---------+-------+------+-------------+

解读：

  • type 是 ref，表示使用了索引进行查询。
  • key 是 idx_customer_id，表示实际使用了 idx_customer_id 索引。
  • rows 是 3，表示估计要扫描 3 行才能找到结果。
  • Extra 是 Using where，表示需要使用 WHERE 子句来过滤数据。

优化：

虽然使用了索引，但是仍然需要使用 WHERE 子句来过滤数据，说明索引没有完全覆盖查询条件。 可以创建一个包含 customer_id 和 order_date 列的复合索引来优化查询：

CREATE INDEX idx_customer_id_order_date ON orders (customer_id, order_date);

创建索引后，再次执行 EXPLAIN 命令，Extra 列的值可能会变成 Using index，表示使用了覆盖索引，性能更好。

6. 总结：

EXPLAIN 命令是 SQL 优化的利器，可以帮助你了解 MySQL 是如何执行查询的，并找出潜在的性能问题。 通过仔细分析 EXPLAIN 的输出结果，你可以优化查询、添加索引、修改表结构等方式来提高查询性能。 记住，SQL 优化是一个持续的过程，需要不断学习和实践。

发表评论