一文学会MySQL的explain工具

阅读量：439 次

发布时间：2019-03-06

本文共 4230 字，大约阅读时间需要 14 分钟。

MySQL Explain 工具详解

引言

在数据库优化的过程中，了解查询执行计划至关重要。MySQL 提供了 EXPLAIN 工具，用于分析查询性能，帮助我们识别瓶颈并制定优化策略。本文将深入探讨 EXPLAIN 工具的功能及其输出格式，帮助开发人员更好地理解和优化查询。

Explain 工具简介

EXPLAIN 是 MySQL 用于获取查询执行计划的工具。通过在查询前使用 EXPLAIN 关键字，可以模拟 MySQL 优化器对查询的处理过程，从而分析以下关键信息：

查询是否使用了索引

查询是否进行了全表扫描

是否存在潜在的优化空间

使用场景

在日常开发中，当遇到慢查询时，建议在 SELECT 语句之前添加 EXPLAIN 关键字。例如：

EXPLAIN select * from tb_hero where hero_name = '李寻欢' and book_id = 1;

示例输出

运行上述查询，输出如下：

+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+|  1 | SIMPLE      | tb_hero | NULL       | ref  | idx_book_id_hero_name | idx_book_id_hero_name | 136     | const,const |    1 |   100.00 | NULL  |+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+

输出解析

EXPLAIN 输出的每一列都提供了不同的信息，帮助我们深入理解查询执行过程。以下是关键列的解析：

输出字段详解

1. id 列

id 列表示查询的唯一标识。每个 SELECT 语句对应一个 id，id 的顺序按照查询出现的顺序递增。id 越大，优先级越高。id 为 NULL 的查询最后执行。

2. select_type 列

select_type 表示查询类型。常见类型包括：

SIMPLE：简单查询，不包含子查询和 UNION。

PRIMARY：复杂查询的最外层。

SUBQUERY：包含在 FROM 子句中的子查询。

DERIVED：包含在 SELECT 列表中的子查询，结果存储在临时表中。

UNION：UNION 操作中的第二个和后续 SELECT。

3. table 列

table 列显示查询涉及的表或衍生表（派生表）。派生表的执行顺序由 id 决定，需先执行对应的派生表查询。

4. type 列

type 列反映查询性能的优化级别。性能从高到低依次为：

NULL > system > const > eq_ref > ref > range > index > ALL

NULL：不需要访问表或索引，直接获取结果。

system：表中只有一行数据，属于 const 类型的特殊情况。

const：用于主键或唯一索引的等值查询，仅需一次访问即可完成。

eq_ref：使用了 PRIMARY KEY 或 UNIQUE 索引进行等值比较。

ref：使用索引的左前缀或非唯一索引进行比较。

range：使用索引的范围（如 BETWEEN、LIKE 等）进行扫描。

index：全索引扫描，但不读取数据。

ALL：全表扫描，性能最差。

5. possible_keys 列

possible_keys 列显示 MySQL 可能使用的索引。实际使用的索引由 key 列决定。

6. key 列

key 列显示 MySQL 实际使用的索引。如果没有使用索引，key 为 NULL。

7. key_len 列

key_len 表示索引的键长，用于评估索引的选择性和效率。例如，int 索引的键长为 4，varchar 索引的键长取决于字符编码（utf8 为 3 字节，utf8mb4 为 4 字节）。

8. ref 列

ref 列显示与索引比较的字段或常数。

9. rows 列

rows 列显示估算的需要扫描的行数。rows 越小，查询效率越高。

10. filtered 列

filtered 列显示查询条件过滤掉的行百分比。

11. Extra 列

Extra 列提供额外信息，包括：

Using index：查询使用了索引。

Using where：查询条件未完全覆盖索引。

Using temporary：查询使用了临时表（如排序、分组等操作）。

Using filesort：查询无法通过索引完成排序，需额外排序。

Select tables optimized away：查询中的表被优化为不需要访问的表。

实际案例分析

示例查询

select * from tb_hero where hero_name = '李寻欢' and book_id = 1;

输出分析

+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+|  1 | SIMPLE      | tb_hero | NULL       | ref  | idx_book_id_hero_name | idx_book_id_hero_name | 136     | const,const |    1 |   100.00 | NULL  |+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+

结论

查询类型：SIMPLE，没有子查询或 UNION。

索引使用：使用了 idx_book_id_hero_name 索引。

效率：仅扫描了 1 行数据，效率非常高。

数据准备

为了演示 EXPLAIN 工具的使用，以下是初始化数据库的 SQL 脚本：

-- 创建数据库DROP DATABASE IF EXISTS `explain_test`;CREATE DATABASE `explain_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE `explain_test`;-- 创建表 tb_heroDROP TABLE IF EXISTS `tb_hero`;CREATE TABLE `tb_hero` (    `hero_id` int(11) NOT NULL,    `hero_name` varchar(32) DEFAULT NULL,    `skill` varchar(64) DEFAULT NULL,    `book_id` int(11) DEFAULT NULL,    PRIMARY KEY (`hero_id`),    INDEX `idx_book_id_hero_name` (`book_id`, `hero_name`) USING BTREE) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;-- 插入测试数据INSERT INTO `tb_hero` (`hero_id`, `hero_name`, `skill`, `book_id`)VALUES    (1, '李寻欢', '小李飞刀', 1),    (2, '令狐冲', '独孤九剑', 2),    (3, '张无忌', '九阳神功', 3),    (4, '郭靖', '降龙十八掌', 4),    (5, '花无缺', '移花接玉', 5),    (6, '任我行', '吸星大法', 2);