MySQL explain与explain analyze

本文以案例介绍explain中的不同查询类型，并且介绍如何分析explain analyze结果。

1. explain中的select type

关于explain，详情查看这篇文章：索引

1.1 SIMPLE

注意，这里使用的数据库employees是mysql示例数据库。

在单表查询中，结果为SIMPLE：

explain select * from employees.departments;


在多表联表查询时，结果仍然是SIMPLE：

-- 查询目前部门经理
explain 
select 
  a.dept_no,
  a.dept_name,
  c.emp_no,
  concat(c.first_name , ' ' , c.last_name) as emp_name 
from employees.departments as a
left join employees.dept_manager as b
  on a.dept_no = b.dept_no
left join employees.employees as c 
  on b.emp_no = c.emp_no
where b.to_date > CURRENT_DATE();


1.2 PRIMARY与SUBQUERY

先回忆什么是子查询

TIP

如果子查询出现在where、having、select中，那查询类型就是SUBQUERY，根据是否依赖外层表/动态函数，又可以分为DEPENDENT SUBQUER和UNCACHABLE SUBQUERY。

如果子查询出现在from中，那查询类型就是DERIVED或DEPENDENT DERIVED。

• 子查询出现在where中

  如果子查询不依赖外层表，可以作为独立子查询执行：

  explain
  select *
  from emp_salary 
  where salary > (select avg(salary) from emp_salary);

  

  如果子查询依赖外层表：

  explain
  select *
  from emp_salary a
  where salary > (
    select avg(salary)
    from emp_salary b
    where b.name=a.name
  );

  

  如果子查询使用了rand()、uuid()等随机函数，则结果不可缓存：

  explain
  select *
  from emp_salary a
  where salary > (
    select avg(salary)+rand()
    from emp_salary b
  );

  

  注意：如果子查询同时依赖外层表和不可缓存，结果通常为DEPENDENT SUBQUERY：

  explain
  select *
  from emp_salary a
  where salary > (
    select avg(salary)+rand()
    from emp_salary b
    where b.name=a.name
  );

  

• 子查询出现在having中

  作为独立子查询：

  explain
  select 
    name,
    avg(salary)
  from emp_salary
  group by `name`
  having avg(salary) > (select avg(salary) from emp_salary);

  

• 子查询出现在select中，作为独立的子查询：

  explain
  select 
    name,
    avg(salary),
    (select avg(salary) from emp_salary) as avg
  from emp_salary
  group by `name`;

  

1.3 PRIMARY与DERIVED

如果子查询出现在from中，那么查询类型为DERIVED。

如果子查询是可以独立执行的：

-- 查询平均工资超过 9000 的员工
EXPLAIN
SELECT *
FROM
(
    SELECT 
        name,
        AVG(salary) AS avg_salary
    FROM emp_salary
    GROUP BY name
) t
WHERE avg_salary > 9000;


如果子查询依赖外层表：

EXPLAIN
SELECT *
FROM emp_salary a
JOIN LATERAL
(
    SELECT AVG(salary) avg_salary
    FROM emp_salary b
    WHERE b.name = a.name
) c
ON TRUE;


在 SQL 中，LATERAL 是一个非常有用的关键字，它的主要作用是允许右侧的子查询（或表函数）引用左侧已经出现的表或子查询中的列。

1.4 UNION

最简单的UNION：

explain
select name, salary from emp_salary a where name='张三'
UNION 
select name, salary from emp_salary b where name='张三';


DEPENDENT UNION：

EXPLAIN
SELECT *
FROM employees e
WHERE e.emp_no IN
(
    SELECT o.emp_no
    FROM online_employee o
    WHERE o.emp_no = e.emp_no

    UNION

    SELECT h.emp_no
    FROM history_employee h
    WHERE h.emp_no = e.emp_no
);


UNCACHEABEL UNION：

EXPLAIN
SELECT *
FROM emp_salary
WHERE name =
(
    SELECT name
    FROM emp_salary
    WHERE RAND()>0.5

    UNION

    SELECT name
    FROM emp_salary
    WHERE RAND()>0.5
    LIMIT 1
);


2. explain analyze结果解读

如果单纯使用explain，我们可以知道select_type、type、key等信息，但是对于梳理执行顺序不太直观。

因此，mysql提供了explain format=tree工具，可以以树形形式展示执行计划，例如：

explain format=tree
select 
  a.dept_no,
  a.dept_name,
  c.emp_no,
  concat(c.first_name , ' ' , c.last_name) as emp_name 
from employees.departments as a
left join employees.dept_manager as b
  on a.dept_no = b.dept_no
left join employees.employees as c 
  on b.emp_no = c.emp_no
where b.to_date > CURRENT_DATE();

结果如下：

-> Nested loop left join  (cost=6.65 rows=8)
    -> Nested loop inner join  (cost=3.85 rows=8)
        -> Filter: ((b.to_date > <cache>(curdate())) and (b.dept_no is not null))  (cost=1.05 rows=8)
            -> Table scan on b  (cost=1.05 rows=24)
        -> Single-row index lookup on a using PRIMARY (dept_no=b.dept_no)  (cost=0.263 rows=1)
    -> Single-row index lookup on c using PRIMARY (emp_no=b.emp_no)  (cost=0.263 rows=1)

以上就是具体的执行顺序，理解原则：从上到下，从里到外。

所谓执行顺序，就是先从第一行开始看（Nested loop left join），是否有内层嵌套（此处有两个平级的内层嵌套：Nested loop inner join和Single-row index lookup on c using PRIMARY (emp_no=b.emp_no)），如果是的话，则先进入第一个内层嵌套，然后继续看是否有内层嵌套，有则进入，最终进入到Table scan on b，也就是在b表上进行全表扫描，然后看是否有同级算子，如果没有就往上回溯：Filter: ((b.to_date > <cache>(curdate())) and (b.dept_no is not null))，执行过滤条件Filter: ((b.to_date > <cache>(curdate())) and (b.dept_no is not null))，此时继续看是否有同级算子，有Single-row index lookup on a using PRIMARY (dept_no=b.dept_no)，则在a表上利用主键索引，最终回到第二行的Nested loop inner join，联表后再执行平级的Single-row index lookup on c using PRIMARY (emp_no=b.emp_no)，最后返回结果。

每一行后的(cost=xx rows=xx)表示估计的时间开销（单位毫秒）和返回的行数。

explain format=tree不会实际执行SQL，而explain analyze会实际执行一遍SQL，并且在估计值后加上实际耗时，例如：

explain analyze
select *
from emp_salary a
where salary > (
  select avg(salary)+rand()
  from emp_salary b
  where b.name=a.name
);

-> Filter: (a.salary > (select #2))  (cost=1.05 rows=8) (actual time=2.22..2.24 rows=4 loops=1)
    -> Table scan on a  (cost=1.05 rows=8) (actual time=1.49..1.49 rows=8 loops=1)
    -> Select #2 (subquery in condition; dependent)
        -> Aggregate: avg(b.salary)  (cost=0.45 rows=1) (actual time=0.00624..0.00628 rows=1 loops=8)
            -> Filter: (b.`name` = a.`name`)  (cost=0.35 rows=1) (actual time=0.00225..0.00314 rows=2 loops=8)
                -> Table scan on b  (cost=0.35 rows=8) (actual time=0.00178..0.00251 rows=8 loops=8)

explain analyze返回了四个实际值（actual time = start...end rows=x loops=y）：

• start time：表示在一次循环中，返回第一行的平均时间（单位毫秒）；
• end time：表示在一次循环中，返回所有行的平均时间（单位毫秒）；
• rows：表示在一次循环中，返回的平均函数；
• loops：表示有多少次循环；

因此，在判断某个执行器实际的耗时，应该使用end time 乘以 loops，判断返回的总行数，应该使用rows 乘以 loops。

分析explain analyze，主要看以下指标：

actual time
    → 时间花在哪里

loops
    → 执行了多少次

actual rows
    → 实际产生多少数据

estimated rows vs actual rows
    → 优化器估算是否准确

参考资料

[1] https://dev.mysql.com/blog-archive/mysql-explain-analyze/

附录

emp_salary表

CREATE TABLE emp_salary (
    emp_no INT ,
    name VARCHAR(50),
    salary DECIMAL(10,2),
    month int 
);
INSERT INTO emp_salary VALUES
(1,'张三',8000,202608),
(2,'李四',10000,202608),
(3,'王五',15000,202608),
(4,'赵六',5000,202608);

INSERT INTO emp_salary VALUES
(1,'张三',9000,202607),
(2,'李四',9000,202607),
(3,'王五',13000,202607),
(4,'赵六',9000,202607);

employees表

CREATE TABLE employees (
    emp_no INT PRIMARY KEY,
    name VARCHAR(50),
    dept_no VARCHAR(10)
);

CREATE TABLE online_employee (
    emp_no INT
);

CREATE TABLE history_employee (
    emp_no INT
);