背景

通常我们在通过SQL慢查询日志或其它三方工具分析出查询性能较差的SQL语句后,经常需要定位原因。那么对于MYSQL我们常用其内置的执行计划(EXPLAIN)命令对慢的语句进行模拟执行查询过程的分析,从而发现我们语句的性能瓶颈点,再进行有针对性地优化工作。

执行计划

一、为什么要分析执行计划细节 ,我们能得到哪些方面信息 ?

  1. 理解语句执行的任务拆分和任务的执行顺序;

  2. 分析执行计划拆分的任务内部的划分和含义;

  3. 分析单步执行任务的执行效果的理想程度;

  4. 了解语句的执行计划建议可以使用哪些索引;

  5. 查看执行计划究竟内部用上了哪些目前的索引;

  6. 追踪执行过程中,对表及字段的引用逻辑;

  7. 了解每个子任务过滤的表记录数,从而调整表的连接和组织方式;

二、语法

EXPLAIN +SQL语句 , 如:EXPLAIN  select * from  table;

image.png

详解分析结果

一、任务id理解

场景1. 执行任务id一样,表明查询优化器执行任务的顺序是至上而下顺序执行。

如下图查询语句,执行将从表t1开始,依次执行t3和t2的任务。

EXPLAIN  SELECT t2.* FROM t1,t2,t3 WHERE t1.id=t2.id AND t1.id=t3.id AND t1.other_column='';

image.png

场景2. 执行任务id不同, 查询优化器执行规则为:id值最大的任务优先被执行,按由大到小的顺序依次执行拆分的任务项。

如下图语句中执行过程:执行将从任务3开始,依次执行任务2和任务1;

EXPLAIN  SELECT t2.* FROM t2 WHERE id=(

    SELECT id FROM t1 WHERE id=(SELECT t3.id FROM t3 WHERE t3.other_column='')

);

image.png

场景3. 执行任务id相同与不同混合场景,查询优化器优先执行任务id最大的;任务id一样的部分单独分组,至上而下顺序执行。

如下图语句中执行过程: 任务将被优先执行,然后再从上到下依次执行id=1d的两个任务;

EXPLAIN

SELECT t2.* FROM( SELECT t3.idFROM t3 WHERE t3.other_column='' ) s1,t2 WHERE s1.id=t2.id ;

image.png

二、任务查询类型 - select_type 

从语句整体上,划分为多种不同的查询类型场景。

类型

描述

SIMPLE

简单的select查询,不包含子查询和UNION

PRIMARY

查询中若包含复杂的子部分,最外层查询则被标记为PRIMARY

SUBQUERY

SELECTWHERE列表中包含子查询

DERIVED

FROM列表中包含的子查询被标记为DERVIED(衍生)MYSQL会递归执行这些子查询,把结果放在临时表里面

UNION

若第二个SELECT出现在UNION之后,则被标记为UNION;

UNION包含在FROM子句的子查询中,外层SELECT被标记为DERIVED

UNION RESULT

UNION表获取结果的SELECT

1.SIMPLE类型,如:EXPLAIN  SELECT * FROM t_order;

image.png

2. PRIMARY和SUBQUERY类型

如:EXPLAIN  SELECT *,(SELECT   id   FROM   t_order_detail   WHERE  order_id=2) FROM   t_order  t1;

image.png

3. DERIVDE 衍生类型

如:EXPLAIN SELECT  t1.*  FROM t1,( SELECT t2.* FROM  t2 WHERE t2.id  =1) s2 WHERE t1.id=s2.id;

image.png

截图中的<derivde2>中"2"代表从t2实体表,衍生出的虚拟表

4. UNION / UNION RESULT类型

如以下语句: EXPLAIN  SELECT  *  FROM  t1   UNION  SELECT  *  FROM  t2;

image.png

三、建议可以使用哪些索引 - possible_keys

EXPLAIN

SELECT t1.orderno,t1.created_time,t2.goods_id,t2.id AS detailid

 FROM t_order t1 INNER JOIN t_order_detail t2 ON t1.id = t2.order_id 

WHERE t1.created_time >= '2020-07-18' AND t1.created_time <= '2020-07-20' ;

image.png

如上截图待优化语句中,红框中的索引,就是查询优化器建议使用的索引进行对应的优化

四、实际使用到的索引 - key

如下图中,就是查询优化器实际用到的索引(可能是多个)

CREATE INDEX  idx_col1_col2  ON t1 (col1, col2); 

EXPLAIN SELECT col1,col2 FROM t1 ;

image.png

五、分析是否充分用到了索引 - key_len

影响key_len值的因素:字符集设置、索引字段的长度(或字节数)、索引字段是否设置允许为null。

字符集和 char / varchar类型字节对应关系

字符集

单个中文占字节空间

单个英文占字空间

utf-8

3

1

gbk

2

1

场景一、对于char类型

单个字段key_len长度计算公式为 = 字符集占用最大字节空间 * 字段长度 + 是/否允许为null (是=1, 否=0) ;

image.png

image.png

image.png

所以截图1中的key_len=30的计算=3*10+0=30;

场景二、对于varchar类型

单个字段key_len长度计算公式为 = 字符集占用最大字节空间 * 字段长度+(预留长度) + 是/否允许为null (是=1, 否=0) ;

公式中的预留长度值: mysql官网的介绍是: 长度在1 ~ 255之间,预留长度=1; 长度在256 ~ 16384之间,预留长度=2;

image.png

image.png

所以截图2中的key_len=32的计算=3*10+2+0=32;

场景三、数字类型 (包括:int系列, 单精度、双精度、Decimal等)

单个字段key_len长度计算公式为 = 字段类型占字节空间 + 是/否允许为null (是=1, 否=0) ;

image.png

image.png

截图3中的key_len=4的计算=4+0=4;

image.png

image.png

截图4中的key_len=2的计算=1+1=2;

场景四、日期/日期时间/时间戳/时间等类型, 计算规则同场景三(这里不再赘述)。

其它须知原则

  1. 充分用到索引的key_len长度肯定比未充分用到的要长,但字段加索引的原则是优先选择长度较短的数据类型上加索引,查询更高效;

  2. 复合索引key_len原则:复合索引有最左前缀的特性,如果复合索引能全部使用上,则是复合key_len为各索引字段的key_len长度之和,这也可以用来判定复合索引是否部分使用,还是全部使用;

 附数据类型占字节空间对应表

数据类型

所占空间

最大值

INT

4字节

10位长度.21亿多

BIGINT

8字节

19位长

SMALLINT

2字节

-32768~32767

TINYINT

1字节

-128~127

FLOAT

4字节


DOUBLE

8字节


DECIMAL

4个字节存9个数字,小数点占1个字节


CHAR


255个字符

VARCHAR

0~65535字节

16384个字符

TEXT


16K

MEDUIMTEXT


16M

LONGTEXT


4G

DATETIME

5.5及以下为8字节;  5.6以上版本为5字节

1000~9999(YEAR)

DATE

3字节

1000~9999(YEAR)

TIME

3字节


TIMESTAMP

4字节

1970~2038

六、任务访问类型 - type

访问类型值,从最好到最坏依次排序级别是:system  > const  > eq_ref  > ref  > fulltext > ref_or_null > index_merge  > unique_subquery  > index_subquery >  range  >  index >  ALL

级别值详解

system:  优化器拆分的计划id对应表中只有一条记录

const:  通过索引一次就找到了目标数据

eq_ref:  唯一索引扫描,表中唯一记录与之匹配,常用在主键或唯一索引

如执行包含主键id的索引查询type即为eq_ref级:EXPLAIN   SELECT  *  from  t1,t2  where  t1.id=t2.id 

ref:  非唯一索引扫描

如执行非唯一索引查询type即为ref级:  EXPLAIN   select  count(DISTINCTcol1)  from t1 where col1='ac'

image.png

range: 检索给定范围的行,使用一个索引来选择行; (常用在bewteen and 、IN、<>); 

index : 计划执行的是全表扫描( 通过索引扫描全表, select出的字段全是索引列) ;

ALL: 同上全表扫描,但扫描的是全表数据;

需掌握级别:system  > const  > eq_ref  > ref

                    range  >  index >  ALL

一般查询需要保证达到range 级别,至少为ref

七、对表和列的引用 - ref

image.png

八、引用表多少行被优化器查询到 - row

image.png

九、十分重要的额外信息 - extra

Extra

描述

Using filesort

说明mysql会对数据使用一个外部的索引(由于不按索引排序规则排列查询条件导致),而不是用表内索引顺序进行读取数据。这种无法利用索引完成数据排序的情况成为文件排序

Using temporary

使用了临时表保存中间结果,mysql对查询结果排序时使用临时表。常在ORDER   BY GROUP   BY中出现

Using index

是否使用了覆盖索引

Using where

表明使用where进行了字段过滤

Using join buffer

表明使用了连接缓存

Impossible where

表明where子句的值总是false,   不能用来获取任何元组

1. 产生filesort文件排序的原因

创建复合索引时的字段排序顺序是key(a,b,c); 但是采用where或order by 时,却只按照c或其它字段排序,这样查询优化器就无法采用索引来排序,需重新建立排序规则,即产生了这种情况;

如下截图所示:

image.png

image.png

image.png

解决办法:order by c2,c3 ;

image.png

image.png

2. temporary表产生的原因

在order by或group by排序 中不采用索引字段排序顺序,导致产生中间临时表。

image.png

image.png

解决办法:group by c1,c2 ;

image.png

image.png

3.索引覆盖举例

image.png

image.png

4. join buffer理解:不循环数据逐一和另一张表匹配,而是缓存整个小表数据,整体和另一张表进行匹配方式,提升查询效率;

5. impossible_where:  无法查询到任何数据

image.png

总结

以上就是MYSQL执行计划模拟执行结果的所有细节分析。有任何关于这方面的问题,可以在下方留言,大家一起讨论。下次我们将给大家分享SQL高级查询优化的诸多干货,请继续关注!