mysql执行计划explain

mysql 执行计划

explain 介绍

• mysql8.0为例：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

EXPLAIN为语句中使用的每个表返回一行信息 SELECT。它按照 MySQL 在处理语句时读取它们的顺序列出输出中的表。这意味着 MySQL 从第一个表中读取一行，然后在第二个表中找到匹配的行，然后在第三个表中，依此类推。当所有的表都被处理完后，MySQL 将选择的列输出，并在表列表中回溯，直到找到一个有更多匹配行的表。从此表中读取下一行，然后继续处理下一个表

创建两个表(student、scope)

• 创建表

create table student
(
    student_id bigint auto_increment comment '学生id'
        primary key,
    name       varchar(20) null comment '学生姓名',
    age        int         null comment '年龄'
)
    comment '学生表';


create table score
(
    score_id   bigint auto_increment comment '分数id'
        primary key,
    student_id bigint        not null comment '学生id',
    subject    varchar(20)   null comment '科目',
    score      decimal(5, 2) null comment '分数'
)
    comment '学生成绩表';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• 添加索引

create index idx_student_id
    on score (student_id);
create index idx_subject
    on score (subject);
1
2
3
4

• 初始化数据

insert into md_test.student (student_id, name, age)
values  (1, '小明', 18),
        (2, '张三', 19),
        (3, '李四', 19),
        (4, '王二', 17),
        (5, '孙悟空', 17),
        (6, '猪八戒', 20);

insert into md_test.score (score_id, student_id, subject, score)
values  (1, 1, '语文', 90.00),
        (2, 1, '数学', 98.00),
        (3, 2, '语文', 97.00),
        (4, 2, '数学', 87.00),
        (5, 3, '语文', 78.00),
        (6, 3, '数学', 88.00),
        (7, 4, '语文', 99.00),
        (8, 4, '数学', 100.00),
        (9, 5, '语文', 96.00),
        (10, 5, '数学', 78.00),
        (11, 6, '语文', 87.00),
        (12, 6, '数学', 88.00);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

explain 输出列

• 使用explain查询sql执行计划

explain
select * from student;
1
2

• 对应执行计划
  

id

• 标识符SELECT。SELECT这是查询中的序号 。NULL如果该行引用其他行的并集结果，则该值可以是。在这种情况下，该 table列显示一个值 like 表示该行引用具有值 和 的行的并集

select_type

• simple
  简单查询不包含子查询和union，如果查询中有任何复杂的子部分，则最外层部分标记为PRIMARY

explain
select * from student limit 1;
1
2

• PRIMARY
  最外层的select

explain
select (select st.name from student st where st.student_id = sc.student_id) as student_name, sc.score
from score sc;
1
2
3

• SUBQUERY SELECT子查询中的第一个
• DEPENDENT SUBQUERY 首先SELECT在子查询中，依赖于外部查询

explain
select (select st.name from student st where st.student_id = sc.student_id) as student_name, sc.score
from score sc;
1
2
3

• UNION SELECT中的 第二个或后面的语句UNION
• UNION RESULT 表示用来从UNION的匿名临时表检索结果的select

explain
select * from student s1 where s1.student_id=1
union
select * from student s2 where s2.student_id=4;
1
2
3
4

• derived 派生表(临时表)

table

• 输出行所引用的表的名称。这也可以是以下值之一
  : 当有 union 时，UNION RESULT 的 table 列的值为 。 MN
  ：该行引用值为 的行的派生表id结果 N。例如，派生表可能来自子句中的子查询 FROM。
  id ：该行引用值为 的行的具体化子查询的结果N

partitions

type

• 查询将匹配记录的分区
• 查询效率依次从最优到最差分别为

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
1

• const 该表最多有一个匹配行，在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。 const表非常快，因为它们只读一次。
• system 该表只有一行（=系统表）。这是连接类型的一个特例 const。

explain
select *
from student s1
where s1.student_id = 1;
1
2
3
4

• eq_ref
  primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录
  除了 systemand const类型，这是最好的连接类型

explain
select (select st.name from student st where st.student_id = sc.student_id) as student_name, sc.score
from (select * from score s where s.score > 80) sc;
1
2
3

• ref
  相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行

explain
select * from score sc where sc.student_id=3;
1
2

• ref_or_null
  这种连接类型类似于 ref，但 MySQL 对包含值的行进行了额外的搜索NULL。这种连接类型优化最常用于解析子查询

• index_merge
  此连接类型表示使用索引合并优化

• range
  仅检索给定范围内的行，使用索引来选择行。key 输出行中的列指示使用了哪个索引。包含key_len使用过的最长的密钥部分。该ref列 NULL适用于此类型。

range=可以在使用, <>, >, >=, , <, <=, IS NULL, <=>, BETWEEN, LIKE或 运算符将键列与常量进行比较时使用 IN()

explain
select *
from student
where student_id>2 and student_id<6;
1
2
3
4

• index
  连接index类型与 相同 ALL，只是扫描了索引树

possible_keys

1. 这一列显示查询可能使用哪些索引来查找
2. 如果此列是NULL，则没有相关索引
3. 要查看表有哪些索引，请使用. show index from table_name
   

key

1. 该key列表示 MySQL 实际决定使用的键（索引）
2. 如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index

key_len

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列

ref

该ref列显示将哪些列或常量与列中指定的索引进行比较 key以从表中选择行

rows

该rows列表示查询扫描的行数
对于InnoDB表格，此数字是一个估计值，可能并不总是准确的

Extra

查询的附加信息

• distinct
  一旦mysql找到了与行相联合匹配的行，就不再搜索了

• Using index
  select语句中使用覆盖索引，以避免回表查询（当查询仅使用属于单个索引的列时，可以使用此策略）

explain
select student_id
from student
where student_id=2;
1
2
3
4

• Using where
  使用了where过滤

explain
select *
from student
where student_id>2;
1
2
3
4

• Using filesort
• Using index condition