MySQL系列——MySQL的执行计划

基于 MySQL 8.0 community 的版本

EXPLAIN 摘要

用 EXPLAIN 来可以拿到 MySQL 是怎样执行 SQL 语句的。EXPLAIN 会返回一个表，表中的每一行都表示一个 select 语句的执行的信息。

为了更生动的说明这个 MySQL 的执行计划，我用具体的例子阐述，我在本地建一个叫 school 的数据库，并建立 4 张表，分别是学生表，教师表， 分数表，课程表，写一些测试数据进去。

执行脚本文章末尾。

EXPLAIN 输出的格式说明

有的测试的数据库，现在我们针对 EXPLAIN 的输出做出一些说明和描述，理解一下 MySQL 的执行计划。我们可以简单执行一下 SQL 语句：explain select * from student;
输出如下：

• id
  id 就是生成的 select 查询的序号，如果 id 号相同，则从上往下执行；如果 id 不同，id 越大，优先级越高，越先被执行。比如：
  
  
• select_type
  select_type 主要是用来分辨查询的类型，是普通查询还是联合查询还是子查询，又有以下几种类型：

  • SIMPLE：简单的查询，不包含子查询和 union 查询

  • PRIMARY：查询中若包含任何复杂的子查询，最外层查询则被标记为 PRIMARY

  • UNION：第二个或者之后的 select 查询标记为 UNION

  • DEPENDENT UNION：跟 UNION 类似，dependent 表示 union 或 union all 联合而成的结果会受到外部表影响

  • UNION RESULT：从 union 表获取结果的 select

  • SUBQUERY：在 select 或者 where 列表中包含子查询

    子查询等于一个值得时候就是 subquery

  • DEPENDENT SUBQUERY： SUBQUERY 的子查询要受到外部表查询的影响

    子查询返回的是值得集合就是 DEPENDENT SUBQUERY

  • DERIVED：衍生，from 子句中出现的子查询，也叫派生类

  • DEPENDENT DERIVED：DERIVED 的子查询受到外部衍生表的影响

  • MATERIALIZED：物化子查询,子查询来自视图

  • UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被换缓存

  • UNCACHEABLE UNION：表示使用 union 的查询的结果不能被缓存

• partitions
  分区，分库分表才会有该值
• table
  对应行正在访问哪一个表，表名或者别名，可能还是临时表或者 union 合并的结果集。
  如果是具体的表名，则是从物理表中获取的数据；
  如果是类似 derivedN 的形式，表示使用了 id 为 N 的查询产生的衍生表；
  当有 union 结果集的时候，表名是 union n1,n2 等形式，n1,n2 表示参与 union 的 id；
• type
  表示 MySQL 在表中找到所需行的方式，或者叫访问类型，常见的类型有以下几种，从上到下性能依次到好
  • ALL:
    全表扫描
    
  • index:
    索引全扫描，遍历整个索引来查询匹配的行
    
    主要有两种情况：
    一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取；
    另外一种是使用了索引进行排序，这样就避免数据的重排序；
  • range:
    表示利用索引查询的时候限制了范围，在指定范围内进行查询，避免了 index 的全索引扫描。
    适用的操作符：
    =, <>, > , >=, <, <=, IS NULL, BETWEEN, LIKE, or IN ()
  • index_subquery:
    利用索引来关联子查询，不再扫描全表
  • unique_subquery:
    该连接类型类似于 index_subquery，使用的是唯一索引
  • index_merge:
    在查询过程中需要多个索引组合使用
  • ref_or_null:
    对于某个字段即需要关联条件，也需要 null 值的情况下，查询优化器会选择这种访问方式
  • ref:
    使用非唯一索引进行数据的查找，或者唯一索引的前缀扫描，返回匹配某个单独值得记录行，ref 还经常出现在 join 中。
  • eq_ref:
    使用唯一索引进行数据查找，对于每一个索引键值，表中只有一条记录匹配，简单来说就是多表连接中使用 primary key 或者 unique index 作为关联条件。
  • const:
    这个表至多有一个匹配行
  • system:
    表只有一行记录（等于系统表），这是const类型的特例
• possible_keys
  显示可能应用在这张表中的索引，一个或者多个，查询设计到的字段上若存在索引，则列出该索引，但不一定被实际查询使用
• key
  实际使用的索引，null 表示没有使用索引，查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠
• key_len
  表示索引中使用的字节数，可以通过 key_len 计算查询中使用的索引长度，在不损失精度的情况下长度越短越好
• ref
  显示索引的那一列被使用了，如果可能的话，是一个常数
• rows
  根据表的统计信息以及索引的使用情况，大致估算出找出所需记录需要读取的行数，直接反映 sql 找了多少数据，在完成目的情况下越少越好
• extra
  包含额外的信息
  • using filesort: 说明 mysql 无法用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
  • using temporary： 建立临时表来保存中间数据，查询完成之后把临时表删除
  • using index： 表示当前的查询覆盖索引的，直接从索引中读取数据，而不用访问数据表，如果同时出现 using where 表明索引别用来执行索引键值的查找，如果没有，表明索引被用来读取数据，而不是真的查找
  • using where： 使用 where 进行条件过滤
  • using join buffer：使用连接缓存
  • impossible where： where 语句的结果总是 false

官网参考文档

• Understanding the Query Execution Plan

测试表以及数据

• 数据库

  school 数据库创建 SQL >folded

  CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

• 表

  测试表 >folded

   # 学生表
  CREATE TABLE `student`(
  `student_id` VARCHAR(20),
  `student_name` VARCHAR(20) NOT NULL DEFAULT '',
  `student_birth` VARCHAR(20) NOT NULL DEFAULT '',
  `student_sex` VARCHAR(10) NOT NULL DEFAULT '',
  PRIMARY KEY(`student_id`)
  ) ENGINE=InnoDB;
  
  # 课程表
  CREATE TABLE `course`(
  `course_id`  VARCHAR(20),
  `course_name` VARCHAR(20) NOT NULL DEFAULT '',
  `teacher_id` VARCHAR(20) NOT NULL,
  PRIMARY KEY(`course_id`)
  ) ENGINE=InnoDB;
  
  # 教师表
  CREATE TABLE `teacher`(
  `teacher_id` VARCHAR(20),
  `teacher_name` VARCHAR(20) NOT NULL DEFAULT '',
  PRIMARY KEY(`teacher_id`)
  ) ENGINE=InnoDB;
  
  # 分数表
  CREATE TABLE `score`(
  `student_id` VARCHAR(20),
  `course_id`  VARCHAR(20),
  `student_score` BIGINT,
  PRIMARY KEY(`student_id`,`course_id`)
  ) ENGINE=InnoDB;

• 数据

  测试数据 >folded

  -- 插入学生表测试数据
  insert into student values('01' , '赵雷' , '1990-01-01' , '男');
  insert into student values('02' , '钱电' , '1990-12-21' , '男');
  insert into student values('03' , '孙风' , '1990-05-20' , '男');
  insert into student values('04' , '李云' , '1990-08-06' , '男');
  insert into student values('05' , '周梅' , '1991-12-01' , '女');
  insert into student values('06' , '吴兰' , '1992-03-01' , '女');
  insert into student values('07' , '郑竹' , '1989-07-01' , '女');
  insert into student values('08' , '王菊' , '1990-01-20' , '女');
  -- 课程表测试数据
  insert into course values('01' , '语文' , '02');
  insert into course values('02' , '数学' , '01');
  insert into course values('03' , '英语' , '03');
  -- 教师表测试数据
  insert into teacher values('01' , '张三');
  insert into teacher values('02' , '李四');
  insert into teacher values('03' , '王五');
  -- 成绩表测试数据
  insert into score values('01' , '01' , 80);
  insert into score values('01' , '02' , 90);
  insert into score values('01' , '03' , 99);
  insert into score values('02' , '01' , 70);
  insert into score values('02' , '02' , 60);
  insert into score values('02' , '03' , 80);
  insert into score values('03' , '01' , 80);
  insert into score values('03' , '02' , 80);
  insert into score values('03' , '03' , 80);
  insert into score values('04' , '01' , 50);
  insert into score values('04' , '02' , 30);
  insert into score values('04' , '03' , 20);
  insert into score values('05' , '01' , 76);
  insert into score values('05' , '02' , 87);
  insert into score values('06' , '01' , 31);
  insert into score values('06' , '03' , 34);
  insert into score values('07' , '02' , 89);
  insert into score values('07' , '03' , 98);