Mysql explain命令详解

原创

BLACK595

发布于 2024-09-25 09:28:18

2410

发布于 2024-09-25 09:28:18

什么是EXPLAIN

在 MySQL 中，EXPLAIN 语句用于获取关于查询执行计划的信息，模拟优化器执行SQL查询语句，帮助我们分析SQL查询的瓶颈。

通过 EXPLAIN ，我们可以了解到以下关键方面的信息：

id：查询顺序，越大查询越靠前。
select_type：表示查询的类型，例如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
table：涉及的表名。
partition：查询所设计的分区。
type：表示访问表的方式，常见的有 ALL（全表扫描）、index（索引全扫描）、range（索引范围扫描）、ref（使用非唯一索引或唯一索引的前缀进行查找）、eq_ref（使用唯一索引进行查找）等。
possible_keys：指出可能应用的索引。
key：实际使用的索引。
key_len：使用的索引长度。
rows：预计扫描的行数。
Extra：提供额外的执行计划信息，例如 Using filesort（表示需要额外的排序操作）、Using temporary（表示需要使用临时表）等。

创建一个explain的案例库

-- 创建城市表
DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
    city_id INT PRIMARY KEY AUTO_INCREMENT,
    city_name VARCHAR(50),
    population INT
);

-- 创建学生表
DROP TABLE IF EXISTS students;
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50),
    age INT,
    city_id INT
);

-- 创建老师表
DROP TABLE IF EXISTS teachers;
CREATE TABLE teachers (
    teacher_id INT PRIMARY KEY AUTO_INCREMENT,
    teacher_name VARCHAR(50),
    subject VARCHAR(50),
    city_id INT
);

-- 创建学生和老师的多对多关联表
DROP TABLE IF EXISTS student_teacher;
CREATE TABLE student_teacher (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    teacher_id INT
);

-- 为城市表插入一些示例数据
INSERT INTO cities (city_name, population)
VALUES
    ('北京', 20000000),('上海', 25000000),('广州', 18000000);

-- 为学生表插入一些示例数据
INSERT INTO students (student_name, age, city_id)
VALUES
    ('张三', 18, 1),('李四', 19, 2),('王五', 20, 3),('赵六', 21, 1),('孙七', 22, 2),('周八', 23, 3),('吴九', 17, 1),('郑十', 16, 2);

-- 为老师表插入一些示例数据
INSERT INTO teachers (teacher_name, subject, city_id)
VALUES
    ('赵老师', '数学', 1),('钱老师', '英语', 2),('孙老师', '物理', 3);

-- 为学生和老师的关联表插入一些示例数据
INSERT INTO student_teacher (student_id, teacher_id)
VALUES
    (1, 1),(2, 2),(3, 3),(4, 2), (5, 3),(1, 3),(2, 1), (3, 2);
    
-- 在学生表的 city_id 上创建索引
CREATE INDEX idx_student_city_id ON students(city_id);

-- 在学生表的 student_name 上创建索引
CREATE INDEX idx_student_name ON students(student_name);

-- 在老师表的 city_id 上创建索引
CREATE INDEX idx_teacher_city_id ON teachers(city_id);

-- 在学生和老师的中间表（student_teacher）上创建联合索引
CREATE INDEX idx_student_teacher ON student_teacher(student_id, teacher_id);

-- 在城市表上创建联合索引
CREATE INDEX idx_city ON cities(city_name, population);

学生表-老师表-地区表，学生和老师之间多对多，学生和老师都有一个地区对应。

使用explain

使用explain非常简单，只需要在正常的select前面加上explain关键字，便会返回分析过后的结果。例1：

EXPLAIN SELECT * FROM students s where s.student_id = 1

返回结果：

id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref       | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      | s     | const      | PRIMARY| PRIMARY       | PRIMARY  | 4       | const    | 1    | 100.00   |

那这些分析字段的意思是什么呢？下面我们会一一讲解。

id和table（查询表顺序）

id表示的是表的查询顺序，table表示查询的哪张表。当id不同时，先看id，id数大的表先查。例2：

EXPLAIN SELECT * FROM students s1 where student_id = 
(SELECT student_id FROM students s2 where s2.student_name = "张三")

id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref       | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | PRIMARY     | s1    | const      | PRIMARY| PRIMARY       | PRIMARY  | 4       | const    | 1    | 100.00   | 
2  | SUBQUERY    | s2    |            | ref   | idx_student_name | idx_student_name | 203  | const    | 1    | 100.00   | Using index

可以看到嵌入的子查询表s2的id大于父查询，因此子查询的查询优先级大于父查询，查询表的顺序为s2->s1。

当查询中id相同了，我们单从id中就看不出查询顺序了，比如三个表的join查询，例3：

EXPLAIN SELECT * FROM students s 
join student_teacher st on st.student_id = s.student_id
join teachers t on t.teacher_id = st.teacher_id

返回结果：

id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref                  | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      | t     |            | ALL   | PRIMARY       |                    |         |                      | 3    | 100.00   | 
1  | SIMPLE      | st    |            | index | idx_student_teacher | idx_student_teacher | 10      |                      | 8    | 12.50    | Using where; Using index; Using join buffer (Block Nested Loop)
1  | SIMPLE      | s     |            | eq_ref| PRIMARY       | PRIMARY            | 4       | writing.st.student_id | 1    | 100.00   |

三个查询id都是为1，这时就看表出现的顺序了，查询顺序是自顶向下看的，所以这三个表的读取顺序为t->st->s。

select_type（查询类型）

SIMPLE：简单查询。
PRIMARY：主查询。当存在子查询时，外面的父查询便是主查询；当存在union查询时，union的第一段查询便是主查询。
SUBQUERY：子查询。
UNION：合并查询。当存在union查询时，除了第一段为主查询，后面出现在union后的都是合并查询。
UNION RESULT: 合并查询的结果。当存在union查询后，所有的union完会增加一个UNION RESULT表示所有合并查询的结果。

例4：

EXPLAIN SELECT * FROM students s1 where student_id = 1
UNION SELECT * FROM students s1 where student_id = 2

返回结果：

id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref      | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | PRIMARY     | s1    | const      | PRIMARY| PRIMARY       | PRIMARY  | 4       | const    | 1    | 100.00   | 
2  | UNION       | s1    | const      | PRIMARY| PRIMARY       | PRIMARY  | 4       | const    | 1    | 100.00   | 
   | UNION RESULT | <union1,2> |           | ALL    |               |          |         |          |      |          | Using temporary

UNION RESULT表示两条UNION合并后的结果，并不是一个真实存在的表，上面可以看到table列的<union1,2>表示id为1和2合并后的结果。

partitions（分区）

匹配的分区

type（访问表的方式）

这个字段是看我们查询效率时重点需要看的！首先他一般分为9个状态（这9个状态最好背下来），从左往右效率依次降低，分别是：

system > const > eq_ref > ref > ref_or_null > index_merge > range > index > all

下面我们依次讲下这9个状态。

system

当表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的类型就是system，当再加一条数据就是all，平时一般用不上，可以忽略。

const

const表示常量级别，当查询走的索引类型为主键索引或唯一索引与常数进行等值比较时会出现，比如在例4中，查询的id为1的学生，因为id为主键搜索，一张表只有一个id为1的，进行等值比较，只需要匹配一行数据，所以效率会非常高。

eq_ref

eq_ref出现在连表查询的时候，当被驱动表（主表）是通过主键索引或唯一索引或联合索引（联合索引要求每一列都进行联合匹配）进行访问关联的，则对于被驱动表就是eq_ref的类型。例5：

EXPLAIN SELECT * FROM students s 
join student_teacher st on st.student_id = s.student_id

返回结果：

id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref                  | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      | st    |            | index | idx_student_teacher | idx_student_teacher | 10      |                      | 8    | 100.00   | Using where; Using index
1  | SIMPLE      | s     |            | eq_ref| PRIMARY       | PRIMARY            | 4       | writing.st.student_id | 1    | 100.00   |

从上面例子看，先看id和table列，知道会先从st表中找出一条数据的student_id去和s表匹配，s表的student_id为主键id，只找到一条结果匹配，符合eq_ref。

ref

出现与单表或联合查询时，当走的索引为普通索引，一个常量可能会对应多个结果时。例6：

EXPLAIN SELECT * FROM students s
join cities c on s.city_id = c.city_id

返回结果：

id | select_type | table | partitions | type  | possible_keys | key                  | key_len | ref                  | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      | c     |            | ALL   | PRIMARY       |                      |         |                      | 3    | 100.00   | 
1  | SIMPLE      | s     |            | ref   | idx_student_city_id | idx_student_city_id | 5       | writing.c.city_id    | 2    | 100.00   |

同样，先看id和table列，知道会先从c表中找出一条数据的city_id去和s表匹配，s表的city_id为普通索引，可能会有多个学生的城市相同，即city_id会有多个匹配，符合ref。

ref_or_null

与ref类型，只不过ref_or_null查询的值可能为null。例7：

EXPLAIN SELECT * FROM students where student_name = "张三" OR student_name is NULL

返回结果：

id | select_type | table       | partitions | type          | possible_keys | key                | key_len | ref      | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      | students    |            | ref_or_null   | idx_student_name | idx_student_name | 203     | const    | 2    | 100.00   | Using index condition

注意student_name上是有普通索引的，如果不加OR student_name is NULL条件就是走的ref类型。

index_merge

index_merge表示使用了两种或两种以上索引，最后取交集或者并集。例8：

EXPLAIN SELECT * FROM students where student_id = 2 OR student_name = "张三"

返回结果：

id | select_type | table       | partitions | type          | possible_keys | key                | key_len | ref      | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      | students    |            | index_merge   | PRIMARY,idx_student_name | PRIMARY,idx_student_name | 4,203   |          | 2    | 100.00   | Using union(PRIMARY,idx_student_name); Using where

在OR拼接的两个字段上都有索引，一个Select关键字只能使用一个索引，所以这里使用了合并索引为一个虚拟索引的办法，相当于扫描两个索引树取出主键并取并集再回表的操作。但如果这里使用AND，就不会用到任何索引。例9：

EXPLAIN SELECT * FROM students where student_id = 2 AND student_name = "张三"

返回结果：

id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref      | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      |       |            |       |               |          |         |          |      |          | Impossible WHERE noticed after reading const tables

这是因为如果没有一个同时包含student_id和student_name的联合索引，MySQL 可能认为通过索引查找的效率不如全表扫描。

range

如果索引获取某些范围区间的记录，就有可能使用range，比如>、<、in、like、<>、is null、betwween时。例10：

EXPLAIN SELECT * FROM students where student_name like "张%"

返回结果：

id | select_type | table       | partitions | type  | possible_keys | key                | key_len | ref      | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      | students    |            | range | idx_student_name | idx_student_name | 203     |          | 1    | 100.00   | Using index condition

index

当使用索引覆盖(索引覆盖：不需要回表就能找到需要的全部数据)，并且需要扫描全部索引。例11：

EXPLAIN SELECT student_id,student_name from students

返回结果：

id | select_type | table       | partitions | type  | possible_keys | key                | key_len | ref      | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      | students    |            | range | idx_student_name | idx_student_name | 203     |          | 1    | 100.00   | Using where; Using index

因为student_id和student_name上都有索引，所以不需要回表就能查到全部数据，如果还需要加上查age字段，那么就会走all索引。

all

全表扫描，扫描整张表，没有走任何索引，需要优化查询。

possible_keys和key（使用的索引）

possible_keys表示可能使用的索引，key表示经过优化器分析计算后，最终使用的索引。例12：

EXPLAIN SELECT * from students where student_id = 2 and student_name like '李%'

返回结果：

id | select_type | table       | partitions | type  | possible_keys | key      | key_len | ref      | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      | students    | const      | PRIMARY| PRIMARY,idx_student_name | PRIMARY  | 4       | const    | 1    | 100.00   |

在上述查询中，可能会使用到主键索引和唯一索引，但student_id为主键匹配，耗费更低，所以最终key为PRIMARY。

key_len

使用索引的长度（单位字节），越大越好，越大查询需要读取的数据页越少，表示索引使用的越充分。例13：

EXPLAIN SELECT * from cities where city_name = "北京" and population = 10

返回结果：

id | select_type | table  | partitions | type  | possible_keys | key      | key_len | ref          | rows | filtered | Extra   
-------------------------------------------------------------------------------------------------------------------------
1  | SIMPLE      | cities |            | ref   | idx_city      | idx_city | 208     | const,const | 1    | 100.00   | Using index

这里的key_len为208怎么来的呢？这里使用了city_name和，population的联合索引。先来看city_name的字节数，city_name为varchar（50）的变长字符，格式为utmbmb4，一个字符占4个字节（50*4 = 200）；表示变长字段的标识符占2个字节：NULL值列表占1个字节；city_name总共就是（200+2+1=203）个字节。再来看population，population为定长的int，int占4个字节，NULL值列表1个字节，population就是（4+1=5）个字节。所以总共的key_len就是两个字段字节数相加（203+5=208）字节。

同理下面这个查询key_len为203索引就没有上面查询利用充分。

SELECT * from cities where city_name = "北京"

ref

ref表示等值查询时需索引例匹配的信息，如果等于的是常量，ref为const；如果是一个函数，ref为func；常数的效率高于函数。

rows

预计需要读取的数据条数，值越小越好。

filtered

filtered表示在查询过程中，通过条件过滤后剩余结果的比例。越大越好。

filtered值越高，意味着在特定操作中筛选出的有效数据比例越大，可能表示查询的条件过滤效果较好，减少了后续操作需要处理的数据量，从而可能提高查询的性能。

例如，如果一个表有 1000 条记录，查询条件过滤后预计有 500 条符合，filtered可能显示 50.00，表示 50% 的记录可能符合条件。

Extra

Extra提供了关于查询执行的额外重要信息，其作用主要有以下几个方面：

指示查询使用的特殊策略或情况：
- Using index 表示查询只使用了索引，而无需回表读取实际的数据行。这是一种高效的查询方式。例如上面的例11，当执行一个只基于索引列的查询时，就可能出现这个提示。
- Using where 表示 MySQL 服务器在存储引擎检索行后再应用WHERE子句过滤。这意味着并非所有的行都能满足条件。
- Using temporary 表示为了完成查询，MySQL需要创建一个临时表来存储中间结果。这通常是不太优雅的，需要优化。
反映索引的使用情况：
- Using index condition 表示在索引扫描时，部分条件先在索引上进行判断，过滤掉不满足条件的数据，减少回表的数据量。
提示排序操作的相关信息：
- Using filesort 表示 MySQL 无法利用索引完成排序操作，需要额外进行文件排序。这可能会导致性能下降，应尽量避免。
显示连接操作的相关细节：
- Not exists 表示在左连接中，当在被驱动表中找不到匹配行时停止搜索。

例如，如果一个查询的 Extra 列显示 Using where; Using index，这意味着首先通过索引获取数据，然后应用 WHERE 子句进行进一步过滤。

总结

EXPLAIN在 MySQL 中是一个用于分析查询执行计划的重要工具。能帮助我们评估查询性能，通过查看输出的各种信息，如索引使用情况、数据读取方式、预估的行数等，能初步判断查询的效率，优化查询策略，熟练使用 EXPLAIN能帮助我们更加深入了解 MySQL 查询的内部执行机制，从而有效地优化查询，提高数据库的性能。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S9

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S9

#Mysql

#Explain

登录后参与评论

0 条评论

热度