SQL执行计划解读

声明

5.6中desc看不到show warnings,也看不到filtered列
5.7的desc等于5.6的desc extended,这样可以看show warnings,5.6中filtered列非常不准,5.7好一些

先看一个执行计划

(root@localhost) [test]> desc select * from l;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

Ⅰ、展开分析每个字段

id列——表示sql执行的顺序

id相等,一般是简单关联,从上往下看即可
id不相等,一般为出现了子查询,先看大的再看小的
同时存在相等与不相等,一般相等的为一组,先看大的再看小的

有个潜规则叫:id相等从上往下看,id不等从下往上看

select_type——select类型,用于区分子查询,关联查询等

SIMPLE：简单查询,不包含子查询与union
PRIMARY：查询中包含子查询,最外层查询则被标记为PRIMARY
UNION：使用union连接select时,从第二个select开始都是UNION
SUBQUERY：select或者where后面的子查询(非from之后)都可能是SUBQUERY
DERIVED：在from中包含的子查询被标记为DERIVED(派生表)
DEPENDENT SUBQUERY：依赖外部查询的SUBQUERY
UNION RESULT：UNION的结果,对应ID为NULL

table——输出记录的表

查询中使用别名,则此处显示别名
不涉及表的操作,则显示为NULL
< derivedN > / < subqueryN > 由ID为N的查询产生的结果
< unionM,N > 由ID为M,N查询union产生的结果集

type——访问类型

system：const的特例,表中只有一行记录
const：使用唯一索引或主键,只取一行数据
eq_ref：多表join时,驱动表只返回一行数据,且这行数据是被驱动表的主键或唯一索引,且必须not null
ref：非唯一索引的扫描,通常为非唯一索引的等值查询
range：检索给定范围的行,使用一个索引来选择行,通常为where条件中出现between、<、>、in、like等
index：full index scan,根据索引读全表
ALL：full table scan,扫整个数据文件
fulltext：全文索引
ref_or_null：使用普通索引进行查询，但要查询null值
index_merge or：查询会使用到的类型,可能一条sql使用了两个索引,然后merge
unique_subquery和index_subquery：很少出现前一个是子查询的列是唯一索引,第二个是子查询的列是普通索引
MATERIALIZED：物化子查询

主要优化对象是index和ALL,有两种情况可以考虑保留index
只查询索引列,不回表或者使用索引进行排序或者聚合

possible_keys

优化器可能使用到的索引

key

优化器实际选择的索引

key_len

使用索引的字节长度

ref

等值查询会显示const
连接查询的话被驱动表此处显示驱动表的join列

rows

优化器预估的记录数量

filtered

根据条件过滤得到的记录的百分比

extra

Using index：优化器只需使用索引就能得到结果索引覆盖
Using index condition：优化器试用index condition pushdown优化，二级索引
Using index for group by：优化器只需使用索引就能处理group by 或者distinct语句
上面这3个基本上忽略吧,没什么参数好调的
Using temporary：使用临时表,常见于order by,group by
Using filesort：使用额外的排序调整sort_buffer_size
Using join buffer：优化器需要使用join buffer 调整join_buffer_size
Using MRR：优化器使用MRR优化调整read_cache_size
Using temporary：优化器需要使用临时表调整tmp_table_size
Using where：优化器使用where过滤

Ⅱ、分析两个执行计划看看

案例1

(root@localhost) [dbt3]> DESC SELECT
    ->     *
    -> FROM
    ->     part
    -> WHERE
    ->     p_partkey IN (SELECT
    ->             l_partkey
    ->         FROM
    ->             lineitem
    ->         WHERE
    ->             l_shipdate BETWEEN '1997-01-01' AND '1997-02-01')
    -> ORDER BY p_retailprice DESC
    -> LIMIT 10;
+----+--------------+-------------+------------+--------+----------------------------------------------+--------------+---------+---------------------+--------+----------+----------------------------------+
| id | select_type  | table       | partitions | type   | possible_keys                                | key          | key_len | ref                 | rows   | filtered | Extra                            |
+----+--------------+-------------+------------+--------+----------------------------------------------+--------------+---------+---------------------+--------+----------+----------------------------------+
|  1 | SIMPLE       | part        | NULL       | ALL    | PRIMARY                                      | NULL         | NULL    | NULL                | 197706 |   100.00 | Using where; Using filesort      |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>                                   | <auto_key>   | 5       | dbt3.part.p_partkey |      1 |   100.00 | NULL                             |
|  2 | MATERIALIZED | lineitem    | NULL       | range  | i_l_shipdate,i_l_suppkey_partkey,i_l_partkey | i_l_shipdate | 4       | NULL                | 138672 |   100.00 | Using index condition; Using MRR |
+----+--------------+-------------+------------+--------+----------------------------------------------+--------------+---------+---------------------+--------+----------+----------------------------------+
3 rows in set, 1 warning (0.01 sec)

id 顺序
1 ② part表(外表)和subquery2(id=2产生的14w记录的表)进行关联,对于part表中所有记录都要关联,一共是19w行,再和l_partkey进行关联,最后排序用到using filesort
1 ③ 内表要加索引,所以mysql优化器自动把第一步取出来的数据添加了一个唯一索引,in里面是去重的(这其实是做了一个物化),所以是唯一索引,eq_ref表示通过唯一索引进行关联,和外表中的p_partkey关联
2 ① 先查lineitem表,是一个range范围查询,使用了i_l_shipdate索引,l_shipdate是date类型,占用四个字节,预估14万行记录,过滤出百分之百,materiallized表示产生了一张实际的表,并且去添加了索引,l_partkey,唯一索引(in里面是去重的)

注意一个细节

(root@localhost) [dbt3]> DESC SELECT
    ->     *
    -> FROM
    ->     part
    -> WHERE
    ->     p_partkey IN (SELECT
    ->             l_partkey
    ->         FROM
    ->             lineitem
    ->         WHERE
    ->             l_shipdate BETWEEN '1997-01-01' AND '1997-01-07')
    -> ORDER BY p_retailprice DESC
    -> LIMIT 10;
+----+--------------+-------------+------------+--------+----------------------------------------------+--------------+---------+-----------------------+-------+----------+----------------------------------------------+
| id | select_type  | table       | partitions | type   | possible_keys                                | key          | key_len | ref                   | rows  | filtered | Extra                                        |
+----+--------------+-------------+------------+--------+----------------------------------------------+--------------+---------+-----------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL                                         | NULL         | NULL    | NULL                  |  NULL |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE       | part        | NULL       | eq_ref | PRIMARY                                      | PRIMARY      | 4       | <subquery2>.l_partkey |     1 |   100.00 | NULL                                         |
|  2 | MATERIALIZED | lineitem    | NULL       | range  | i_l_shipdate,i_l_suppkey_partkey,i_l_partkey | i_l_shipdate | 4       | NULL                  | 29148 |   100.00 | Using index condition; Using MRR             |
+----+--------------+-------------+------------+--------+----------------------------------------------+--------------+---------+-----------------------+-------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

驱动表就变成了subquerry2,这时候优化器又把子查询作为了外表,说明优化器很聪明

in的子查询,优化器会帮你重写成join,并且帮你选择子查询到底是内表还是外表

(root@localhost) [dbt3]> DESC select
    ->     a.*
    -> from
    ->     part a,
    ->     (select distinct
    ->         l_partkey
    ->     from
    ->         lineitem
    ->     where l_shipdate between '1997-01-01' and '1997-02-01') b
    -> where
    ->     a.p_partkey=b.l_partkey
    -> order by a.p_retailprice desc
    -> limit 10;
+----+-------------+------------+------------+--------+----------------------------------------------+--------------+---------+-------------+--------+----------+---------------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys                                | key          | key_len | ref         | rows   | filtered | Extra                                             |
+----+-------------+------------+------------+--------+----------------------------------------------+--------------+---------+-------------+--------+----------+---------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                                         | NULL         | NULL    | NULL        | 138672 |   100.00 | Using where; Using temporary; Using filesort      |
|  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY                                      | PRIMARY      | 4       | b.l_partkey |      1 |   100.00 | NULL                                              |
|  2 | DERIVED     | lineitem   | NULL       | range  | i_l_shipdate,i_l_suppkey_partkey,i_l_partkey | i_l_shipdate | 4       | NULL        | 138672 |   100.00 | Using index condition; Using MRR; Using temporary |
+----+-------------+------------+------------+--------+----------------------------------------------+--------------+---------+-------------+--------+----------+---------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

这么改写,b表永远是外表,子查询只是产生一个派生表,但是没办法给它建索引,如果子查询出来的结果集很大,这时候性能就不如in了,in的话优化器会把它作为内表

案例2

(root@localhost) [dbt3]> DESC select max(l_extendedprice)
    ->     from orders,lineitem
    -> where o_orderdate between '1995-01-01' and '1995-01-31'
    -> and l_orderkey=o_orderkey;
+----+-------------+----------+------------+-------+--------------------------------------------+---------------+---------+------------------------+-------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys                              | key           | key_len | ref                    | rows  | filtered | Extra                    |
+----+-------------+----------+------------+-------+--------------------------------------------+---------------+---------+------------------------+-------+----------+--------------------------+
|  1 | SIMPLE      | orders   | NULL       | range | PRIMARY,i_o_orderdate                      | i_o_orderdate | 4       | NULL                   | 40696 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | lineitem | NULL       | ref   | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY       | 4       | dbt3.orders.o_orderkey |     3 |   100.00 | NULL                     |
+----+-------------+----------+------------+-------+--------------------------------------------+---------------+---------+------------------------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

orderkey上有索引,但是没用,用的是pk,orders表示外表,根据过滤条件把数据过滤出来做外表,然后跟lineitem表关联,用的是pk,关联的列是orders.o_orderkey

如果强行走orderkey索引,成本很高,需要回表,通过主键不用回表

案例3

(root@localhost) [dbt3]> DESC select *
    -> from
    ->     lineitem
    -> where
    ->     l_shipdate <= '1995-12-32'
    -> union
    -> select
    ->     *
    -> from
    ->     lineitem
    -> where
    ->     l_shipdate >= '1997-01-01';
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
|  1 | PRIMARY      | lineitem   | NULL       | ALL  | i_l_shipdate  | NULL | NULL    | NULL | 5409799 |    33.33 | Using where     |
|  2 | UNION        | lineitem   | NULL       | ALL  | i_l_shipdate  | NULL | NULL    | NULL | 5409799 |    50.00 | Using where     |
|NULL| UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
3 rows in set, 3 warnings (0.10 sec)

union result合并两张表会using temporary,使用临时表,union会去重,所以又去建了临时表,在上面加了唯一索引,这里就用了两个索引,所以一个sql只能用一条索引是不对的

案例4

(root@localhost) [employees]> DESC SELECT
    ->     emp_no,
    ->     dept_no,
    ->     (SELECT
    ->             COUNT(1)
    ->         FROM
    ->             dept_emp t2
    ->         WHERE
    ->             t1.emp_no <= t2.emp_no) AS row_num
    -> FROM
    ->     dept_emp t1;
+----+--------------------+-------+------------+-------+----------------+--------+---------+------+--------+----------+------------------------------------------------+
| id | select_type        | table | partitions | type  | possible_keys  | key    | key_len | ref  | rows   | filtered | Extra                                          |
+----+--------------------+-------+------------+-------+----------------+--------+---------+------+--------+----------+------------------------------------------------+
|  1 | PRIMARY            | t1    | NULL       | index | NULL           | emp_no | 4       | NULL | 331570 |   100.00 | Using index                                    |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ALL   | PRIMARY,emp_no | NULL   | NULL    | NULL | 331570 |    33.33 | Range checked for each record (index map: 0x3) |
+----+--------------------+-------+------------+-------+----------------+--------+---------+------+--------+----------+------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

对于这个sql,先执行了1再执行了2,2是dependent subquery,要依赖子查询,所以先执行了1,所以t1是外表,t2是内表,每次得关联33w * 33%次数,一共关联33w次,一共是33w * 10w次

行号问题,性能非常差

Ⅲ、补充

3.1 什么是物化？

通常来说查询的结果是不需要物化的,子查询产生的一张表,去重,加一个唯一键,上面还有个索引

A和B关联,B是子查询查出来的,本来这些数据都存放在内存中直接和A表关联,B肯定是外表,因为他没有索引,in的话就会去重,加唯一键,这时候就既可以是外表也可以是内表,这就是物化

explain format=json

这个东西还是蛮好用的,可以用来看看sql的执行成本

mysql优化器会选择一个成本最小的方式作为执行计划

posted @ 2018-06-26 00:54 91洲际哥阅读(724) 评论(0) 编辑收藏举报

刷新页面返回顶部