当你的数据里只有几千几万那么 SQL 优化并不会发挥太大价值但当你的数据里去到了几百上千万SQL 优化的价值就体现出来了因此稍微有些经验的同学都知道怎么让 MySQL 查询语句又快又好是一件很重要的事情。要让 SQL 又快又好的前提是我们知道它「病」在哪里而 explain 关键字就是 MySQL 提供给我们的一把武器在我们所执行的 SQL 前面加上 explain 关键字MySQL 就不会真正去执行这条语句而是模拟优化器执行 SQL 查询语句最后会输出一系列的指标告诉我们这条语句的性能如何如下图所示。mysql explain select * from student where id 1 \G ****************************************************** id: 1 select_type: SIMPLE table: subject partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL ******************************************************总的来说explain 关键字可以告诉我们下面这么多信息表的读取顺序如何数据读取操作有哪些操作类型哪些索引可以使用哪些索引被实际使用表之间是如何引用每张表有多少行被优化器查询 ......今天我们就来介绍 explain 关键字的各个指标的含义。系好安全带准备发车了为了方便讲解这里新建了几张表并初始化了一些数据建表语句见附录。这些表的关系如下一共有老师、学生、课程三个实体分别为teacher、student、course。三个实体间的关系分别为老师教学生的关系teacher_student、学生的课程分数student_course。ID 字段ID 字段的值及其排列顺序表明 MySQL 执行时从各表取数据的顺序。一般情况下遵循下面两个原则ID 相同的组其执行优先级按照其顺序由上到下。ID 越大的组其执行优先级越高。对于下面这个例子EXPLAIN SELECT teacher.* FROM teacher, teacher_student WHERE teacher_student.student_name s001 AND teacher.NAME teacher_student.teacher_name该例子的输出为上面的输出一共有 2 条记录其 ID 都为 1这表示其归为一组。对于 ID 相同的组MySQL 按照顺序从上到下执行即先拿 teacher_student 表的数据再拿 teacher 表的数据。再来看下面这个例子EXPLAIN SELECT * FROM teacher WHERE NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name S002 )该例子的输出为上面的输出一共有 3 条记录其中第 1、2 条的 ID 相同第 3 条 ID 不同。那么其执行顺序就是 ID 值越大其越早执行。ID 相同的按顺序执行。上面的例子最早拿 teacher_student 表的数据之后是一个子查询组成的表最后拿 teacher 表的数据。结合 SQL 分析这也符合我们的常识。因为我们必须先把子查询的值算出来因此需要先把 teacher_student 表里的数据拿出来之后才可以拿去 teacher 表里查询。select_type 字段select_type 字段表示该 SQL 是什么查询类型一共有以下 6 种SIMPLE简单查询不包含子查询或 union 查询PRIMARY主键查询SUBQUERY在 select 或 where 中包含子查询DERIVEDfrom 中包含子查询UNIONUNION RESULTSIMPLE简单查询不包含子查询或 union 查询。-- 查询T001老师都教了哪些学生 EXPLAIN SELECT student.* FROM teacher, teacher_student, student WHERE teacher.NAME T001 AND teacher.NAME teacher_student.teacher_name AND teacher_student.student_name student.NAME可以看出其 3 个查询都是简单SIMPLE查询。因为 ID 相同所以其查询顺序是按顺序来的。首先从 teacher 表中取出数据之后从 student 表取出数据最后 teacher_student 表取数据。PRIMARY一般情况下如果查询中包含了任何复杂的子查询那么最外层查询会被标记为主查询。-- PRIMARY 查询哪些老师教授了选修数学课的学生 EXPLAIN SELECT * FROM teacher WHERE NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name ( SELECT student_name FROM student_course WHERE course_name shuxue ) )在上面的查询中首先是执行 ID 为 3 的查询即去 student_course 表取出选修了数学课的学生名字之后再去进行最外层的查询。可以看到最外层查询的 select_type 为 PRIMARY。SUBQUERY在 select 或 where 中包含子查询那么 select_type 会被标记为 SUBQUERY。以上面的查询为例-- PRIMARY 查询哪些老师教授了选修数学课的学生 EXPLAIN SELECT * FROM teacher WHERE NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name ( SELECT student_name FROM student_course WHERE course_name shuxue ) )在该查询中where 中包含了子查询因此在 explain 中有一个 ID 为 3 的查询被标记为 SUBQUERY。DERIVED在 FROM 中包含子查询那么 select_type 会被标记为 SUBQUERY。UNION类似包含 union 关键字的会被标记成 UNION 类型这种查询方式比较少这里不做深入讲解。UNION RESULT类似包含 union 关键字的会被标记成 UNION RESULT 类型这种查询方式比较少这里不做深入讲解。type 字段type 字段表示访问情况通常用来衡量 SQL 的查询效率。其值的查询效率从最好到最差分别为NULLsystemconsteq_refreffulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexALLNULLNULL 表示 MySQL 能够在优化阶段分解查询语句在执行阶段用不着再访问表或索引。explain select max(id) from teachersystem表只有一行记录等于系统表这是 const 类型的特列。出现的情况较少这里不深入介绍。constconst 表示该表最多有一个匹配记录。通常情况下是 SQL 中出现了主键索引或唯一索引。explain select * from teacher where name T002上面例子中teacher.name 字段为唯一索引字段所以通过该字段只能唯一找到一条记录因此其 type 类型为 const。eq_refeq_ref 表示主键索引或唯一索引的所有部分被连接使用 最多只会返回一条符合条件的记录。与 const 类型非常相似唯一的区别是 eq_ef 通常出现在联表的情况下而 const 通常出现在单表情况下。EXPLAIN SELECT * FROM teacher, teacher_student WHERE teacher.NAME teacher_student.teacher_name从上面的执行结果可以看出其首先全表扫描了 teacher_student 表之后使用 teacher.name 唯一索引去将联合 teacher 表的每一条记录。要注意的是eq_ref 这种情况重点在于读取本表中和关联表表中的每行组合成的一行。如果并没有关联表中每行这个概念那么就不会出现 eq_ref 这种类型。例如我在上面的 SQL 中加上 age 为 24 这个条件即 SQL 为EXPLAIN SELECT * FROM teacher, teacher_student WHERE teacher.NAME teacher_student.teacher_name and teacher.age 24执行计划变为会看到 type 类型都变为 ref 了eq_ref 消失了。refref 表示使用了非唯一索引扫描会返回匹配某个单独值的所有行。与 const 非常类似只不过 ref 会匹配到多个记录而 const 则只会匹配到单个记录。explain select * from teacher where age 24age 为普通索引表中有 2 条记录。表中数据为ref_or_null类似 ref但是可以搜索值为 NULL 的行。explain select * from teacher where age 24 or age is null当我们增加 age is null 查询条件后其 type 字段就变成了 ref_or_null。index_merge表示使用了索引合并的优化方法。索引合并指的是对多个索引分别进行条件扫描然后将它们各自的结果进行合并。EXPLAIN SELECT * from teacher where id 1 or age 24执行计划为可以看到使用了 index_merge 的查询类型。在 teacher 表中 id 和 age 都是索引其将两个字段的索引结果进行合并了。rangerange 表示检索给定范围的行使用一个索引来选择行key 列显示使用了哪个索引。一般就是在你的 where 语句中出现 between、、in 等的范围查询。EXPLAIN SELECT * FROM TEACHER where age between 10 and 20执行计划为上面语句中我们使用 between 进行范围查询因此 type 类型为 range。indexindex 表示只遍历索引树且只从索引树中获取数据。EXPLAIN SELECT id, age FROM TEACHER上面 SQL 中的 id、age 都是索引字段可以直接从索引树中读取。因此其 type 字段为 index表示此次查询数据可以直接从索引树获取到。但是如果查询的字段不在索引树中那么就是全表扫描了。例如EXPLAIN SELECT id, enter_time FROM TEACHER查询 SQL 的 enter_time 字段不是索引所以上面的查询就变成了全表查询ALL。ALLALL 表示该查询将遍历全表以找到匹配行这是最糟糕的一种查询方式。table 字段表示数据来自哪张表possible_keys 字段显示可能应用在这张表中的索引一个或多个。查询涉及到的字段若存在索引则该索引将被列出但不一定被实际使用。key 字段实际使用到的索引如果为 NULL则没有使用索引。查询中若使用了覆盖索引查询的列刚好是索引则该索引仅出现在 key 列表。select * from teacher where name T001上面这个查询中key 字段显示使用了 udx_name 这个索引也就是 name 这个字段作为索引。key_len 字段这一列显示了 mysql 在索引里使用的字节数通过这个值可以算出具体使用了索引中的哪些列。举例来说film_actor 的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成并且每个 int 是 4 字节。通过结果中的 key_len4 可推断出查询使用了第一个列film_id 列来执行索引查找。mysql explain select * from film_actor where film_id 2; ------------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | const | 1 | Using index | ------------------------------------------------------------------------------------------------------------key_len 计算规则如下字符串char (n)n 字节长度varchar (n)2 字节存储字符串长度如果是 utf-8则长度 3n 2数值类型tinyint1 字节smallint2 字节int4 字节bigint8 字节时间类型date3 字节timestamp4 字节datetime8 字节其他如果字段允许为 NULL需要 1 字节记录是否为 NULLref 字段这一列显示了在 key 列记录的索引中表查找值所用到的列或常量常见的有const常量funcNULL字段名例film.id。rows 列这一列是 mysql 估计要读取并检测的行数注意这个不是结果集里的行数。Extra 列这一列展示的是额外信息。distinct一旦 mysql 找到了与行相联合匹配的行就不再搜索了。mysql explain select distinct name from film left join film_actor on film.id film_actor.film_id; ------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | film | index | idx_name | idx_name | 33 | NULL | 3 | Using index; Using temporary | | 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | test.film.id | 1 | Using index; Distinct | -------------------------------------------------------------------------------------------------------------------------------------Using index这表示查找某个表的时候所需要的信息直接从索引就可以拿到而不需要再访问行记录。mysql explain select id from film order by id; ----------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ----------------------------------------------------------------------------------------- | 1 | SIMPLE | film | index | NULL | PRIMARY | 4 | NULL | 3 | Using index | -----------------------------------------------------------------------------------------上面例子中我只是选择了 id 列这个列本身是索引其信息直接在索引树中就可以拿到因此不需要再访问行记录。Using wheremysql 服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据再按 where 条件进行检查符合就留下不符合就丢弃。mysql explain select * from film where id 1; ------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | film | index | PRIMARY | idx_name | 33 | NULL | 3 | Using where; Using index | -------------------------------------------------------------------------------------------------------Using temporarymysql 需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的首先是想到用索引来优化。1. actor.name没有索引此时创建了张临时表来distinct mysql explain select distinct name from actor; ----------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ----------------------------------------------------------------------------------------- | 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary | ----------------------------------------------------------------------------------------- 2. film.name建立了idx_name索引此时查询时extra是using index,没有用临时表 mysql explain select distinct name from film; ------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------ | 1 | SIMPLE | film | index | idx_name | idx_name | 33 | NULL | 3 | Using index | ------------------------------------------------------------------------------------------Using filesortMySQL 中无法利用索引完成的排序操作称为「文件排序」。在 MySQL 中的 ORDER BY 有两种排序实现方式利用有序索引获取有序数据文件排序在 explain 中分析查询的时候利用有序索引获取有序数据显示 Using index 文件排序显示 Using filesort。至于什么时候使用索引排序什么时候使用文件排序这个问题太过于复杂这里不做深入介绍。1. actor.name未创建索引会浏览actor整个表保存排序关键字name和对应的id然后排序name并检索行记录 mysql explain select * from actor order by name; ---------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------- | 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | Using filesort | ---------------------------------------------------------------------------------------- 2. film.name建立了idx_name索引,此时查询时extra是using index mysql explain select * from film order by name; ------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------ | 1 | SIMPLE | film | index | NULL | idx_name | 33 | NULL | 3 | Using index | ------------------------------------------------------------------------------------------建表语句建表语句如下/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80019 Source Host : localhost:3306 Source Schema : test Target Server Type : MySQL Target Server Version : 80019 File Encoding : 65001 Date: 22/06/2020 08:59:15 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS course; CREATE TABLE course ( id int NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY udx_name (name) ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci; -- ---------------------------- -- Records of course -- ---------------------------- BEGIN; INSERT INTO course VALUES (2, shuxue); INSERT INTO course VALUES (3, yingyu); INSERT INTO course VALUES (1, yuwen); COMMIT; -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS student; CREATE TABLE student ( id int NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, age int DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY udx_name (name), UNIQUE KEY idx_age (age) USING BTREE ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci; -- ---------------------------- -- Records of student -- ---------------------------- BEGIN; INSERT INTO student VALUES (1, S001, 24); INSERT INTO student VALUES (2, S002, 23); INSERT INTO student VALUES (3, S003, 22); COMMIT; -- ---------------------------- -- Table structure for student_course -- ---------------------------- DROP TABLE IF EXISTS student_course; CREATE TABLE student_course ( id int NOT NULL AUTO_INCREMENT, student_name varchar(20) DEFAULT NULL, course_name varchar(20) DEFAULT NULL, PRIMARY KEY (id), KEY idx_student_name (student_name), KEY idx_course_name (course_name) ) ENGINEInnoDB AUTO_INCREMENT7 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci; -- ---------------------------- -- Records of student_course -- ---------------------------- BEGIN; INSERT INTO student_course VALUES (1, S001, yuwen); INSERT INTO student_course VALUES (2, S001, shuxue); INSERT INTO student_course VALUES (3, S001, yingyu); INSERT INTO student_course VALUES (4, S002, yuwen); INSERT INTO student_course VALUES (5, S002, shuxue); INSERT INTO student_course VALUES (6, S003, yuwen); COMMIT; -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS teacher; CREATE TABLE teacher ( id int NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, enter_time datetime DEFAULT NULL, age int DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY udx_name (name), KEY idx_age (age) USING BTREE ) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci; -- ---------------------------- -- Records of teacher -- ---------------------------- BEGIN; INSERT INTO teacher VALUES (1, T001, 2020-06-16 21:51:54, 12); INSERT INTO teacher VALUES (2, T002, 2020-06-15 21:52:02, 12); INSERT INTO teacher VALUES (3, T003, 2020-06-14 21:52:08, 24); INSERT INTO teacher VALUES (4, T004, 2020-06-14 21:52:08, 24); COMMIT; -- ---------------------------- -- Table structure for teacher_student -- ---------------------------- DROP TABLE IF EXISTS teacher_student; CREATE TABLE teacher_student ( id int NOT NULL AUTO_INCREMENT, teacher_name varchar(20) DEFAULT NULL, student_name varchar(20) DEFAULT NULL, PRIMARY KEY (id), KEY idx_teacher_name (teacher_name), KEY idx_student_name (student_name) ) ENGINEInnoDB AUTO_INCREMENT7 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci; -- ---------------------------- -- Records of teacher_student -- ---------------------------- BEGIN; INSERT INTO teacher_student VALUES (1, T001, S001); INSERT INTO teacher_student VALUES (2, T001, S002); INSERT INTO teacher_student VALUES (3, T001, S003); INSERT INTO teacher_student VALUES (4, T002, S001); INSERT INTO teacher_student VALUES (5, T002, S002); INSERT INTO teacher_student VALUES (6, T003, S001); COMMIT; SET FOREIGN_KEY_CHECKS 1;原文https://mp.weixin.qq.com/s/zlWVJrzSeIFr7f2X5H78Dg作者陈树义