MySQL SQL调优系列之执行计划学习笔记

Bertha 。 2021-08-19 22:19 396阅读 0赞

继上一篇博客[《MySQL的索引知识学习笔记》][MySQL]之后，我再记录一篇MySQL执行计划方面的博客，本博客是我在学习尚硅谷的学习教程后，做的笔记，当然我不是为了所谓宣传，仅仅是学习记录的笔记。本来可以不分享出来，不过，分享出来的笔记不仅可以给网上的学习者参考学习，同时写在csdn比较方便，可以支持图片上传，也方便自己以后查找复习

附录：我创建的数据库方面的专栏

*  [SQL调优方面的专栏][SQL]
 *  [MySQL知识方面的专栏][MySQL 1]

### 文章目录 ###

*   *  一、执行计划概念
     *  二、Explain用法
     *  三、Explain属性介绍
     *   *  3.1 id属性
         *  3.2 select\_type属性
         *  3.3 table属性
         *  3.4 type属性
         *  3.5 possible\_keys属性
         *  3.6 key属性
         *  3.7 key\_len属性
         *  3.8 ref属性
         *  3.9 rows属性
         *  3.10 Extra属性
     *  四、案例学习

## 一、执行计划概念 ##

执行计划(Explain)：explain显示了mysql如何使用索引来处理select语句以及连接表，使用Explain关键字可以模拟MySQL优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。所以执行计划常用于SQL调优

## 二、Explain用法 ##

Explain的用法：

*  Explain + SQL语句

Explain的主要属性有id、select\_type、table、type、possible\_keys、key、key\_len、ref、rows、Extra，如何对这些属性进行比较详细的介绍

## 三、Explain属性介绍 ##

执行计划(Explain)包括如下属性：  
![在这里插入图片描述][20190915142241402.png]  
比较重要的属性有id、type、key、rows、Extra

### 3.1 id属性 ###

id属性其实就是显示了SQL的执行顺序，下面分类解释

*  id相同的情况

id相同的情况，执行顺序由上到下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]  
本例子来自尚硅谷教程介绍，从执行计划可以看出就是t1>t3>t2，因为SQL有`t1.other_column=''`的条件，所以先从t1开始执行，然后是t3，接着是t2

*  id不同的情况  
    id不同的情况，根据id优先级来看，优先级越高越先执行  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]
 *  id相同不同同时存在  
    这种是比较特殊的情况，不过还是按照优化先看，然后id相同的情况，还是按照从上到下的情况看  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]

### 3.2 select\_type属性 ###

select\_type就是查询的类型，用来区别普通查询、联合查询(union)、子查询等复杂查询，属性分为：  
![在这里插入图片描述][20190915143545802.png]

*  1、Simple  
    简单select查询，查询中不包括子查询、联合查询等
 *  2、PRIMARY  
    PRIMARY可以理解为主查询，当然是在包括子查询等情况下面，最外面的查询被称之为PRIMARY查询
 *  3、SUBQUERY  
    SUBQUERY就是子查询，一般用于select后面或者where条件后面
 *  4、DERIVED  
    DERIVED是衍生查询，一般用于from后面查询返回一个衍生表  
    ![在这里插入图片描述][20190915144526287.png]
 *  5、UNION  
    UNION是联合查询，UNION或者UNION ALL关键字后面的查询表查询都被标记为联合查询  
    ![在这里插入图片描述][20190915144850118.png]
 *  6、UNION RESULT  
    union result就是从union获取的查询结果

### 3.3 table属性 ###

table属性，很显然这个属性就是显示查询数据是关于哪张表的

### 3.4 type属性 ###

type属性表示MySQL找到数据行采取的方式，也称之为访问方式，显示了查询使用了何种类型

> 访问类型排序，从最好到最差的依次是(常用的)：  
> system>const>eq\_ref>ref>range>index>All

来自尚硅谷老师的归纳：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]

然后介绍一下这些级别

*  ststem  
    表只有一条记录的情况，显示type为system，这种一般在系统表才会出现，是const的一种特例
 *  const  
    const常量，一般用于where条件后面，用于比较主键索引或者唯一索引，索引一次就可以找到一条记录，一般返回一个常量
 *  eq\_ref  
    唯一性索引扫描，一般是主键Primary key或者唯一索引(union index)，只返回一条记录与之匹配
 *  ref  
    非唯一性索引扫描，也是一种索引访问，不过符合的记录有多条
 *  range  
    索引返回扫描，一般来说在where条件出现了between或者<、>、in等符合或者关键字就是索引访问扫描，不过建了组合索引，有些情况是会导致索引失效的
 *  index  
    index也就是索引全扫描(full index scan)，index和all的区别是：index扫描所有索引，all扫描所有数据，在mysql中，索引文件是比文件小，所以index扫描性能是比all好的
 *  All  
    all也是全表扫描，不建索引的情况，经常出现全表扫描(full table scan)

> 备注：要保证sql查询，一般要达到range，或者ref级别

### 3.5 possible\_keys属性 ###

用于显示理论上扫描可能用到的索引

### 3.6 key属性 ###

用于显示实践上用到的索引，返回null表示没用到索引

### 3.7 key\_len属性 ###

表示索引使用的字节数，key\_len显示的是索引字段最大的可能长度，并非实际使用的长度

### 3.8 ref属性 ###

ref属性用于显示哪些常用或者列被用于查找索引，前提条件是走索引的  
![在这里插入图片描述][20190915153849905.png]

### 3.9 rows属性 ###

rows数据是根据统计信息及索引选用情况，大致估算出的记录数

### 3.10 Extra属性 ###

Extra可以理解为拓展或者额外的属性，包含了不在其它属性的信息，但是十分重要的额外信息，常用于分析定位性能问题，比较重要的属性有Using filesort、using temporary、using index

*  1、Using filesort  
    Extra信息显示这个信息说明无法利用索引完成排序order by，而选择了文件排序，这种情况是比较耗性能的，所以要看看索引失效的原因  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]  
    如图来自尚硅谷视频的例子，这里就出现了Extra信息为using filesort，原因是where条件里用了col1，order by后面只用了col3，而复合索引是3个列都建的，所以导致不能用到索引排序，用了文件排序代替，解决方法是在order by加上col2

> 备注：索引的两大作用：排序和查询

*  2、using temporary  
    出现这个信息表示，使用了临时表保存中间结果，出现原因是在对查询结果排序时使用了临时表，常见与order by和group by一起用的情况  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 5]  
    上面例子，就是using temporary的情况，在group by后面加上col2列，使索引能用得上
 *  3、Using index  
    出现using index说明出现覆盖索引(Covering index)，是效率不错的！如果using where也一起出现，说明索引被用来查询，只有一个说明只用来读取数据  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 6]

> 覆盖索引(Covering index)，一说索引覆盖，意思是查询时，数据只要从索引获取就可以(从叶子节点获取)，不需要读取数据行  
> 注意：查询时，要使用索引覆盖，就要select的列表只取需要的列就可以，不可以`select *` ,如果将所有的字段一起做索引会导致索引文件过大，影响查询性能

*  Using where  
    表示使用了where过滤条件
 *  Using join buffer  
    表示使用了连接缓存，也就是内外连接
 *  Impossible where  
    where子句的查询结果总是false，不能用来获取任何元组  
    ![在这里插入图片描述][20190915160708154.png]
 *  Select table optimized away  
    ![在这里插入图片描述][20190915160921495.png]
 *  Distinct  
    使用了distinct操作，在查到对一条记录后，就不查找相同的记录

## 四、案例学习 ##

*  复杂点的案例  
    来自视频教程的案例  
    ![在这里插入图片描述][20190915143335118.png]  
    尚硅谷教师的归纳  
    ![在这里插入图片描述][20190915143412832.png]

[MySQL]: https://smilenicky.blog.csdn.net/article/details/100846922
[SQL]: https://smilenicky.blog.csdn.net/article/category/8679315
[MySQL 1]: https://smilenicky.blog.csdn.net/article/category/7377146
[20190915142241402.png]: /images/20210813/9a1380de4f4d46439e79d5403e4d669d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210813/294246ea49f3445b805d478a8045f1af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: /images/20210813/e0677e2caa104634946f6cfc57d99cba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: /images/20210813/0871f02587dc4f4ab050f260084cb0a0.png
[20190915143545802.png]: /images/20210813/36b1647aaf7d4c95841e5aa7ea6f65d1.png
[20190915144526287.png]: /images/20210813/bd4a92c077d6496c9b6ad1054934513c.png
[20190915144850118.png]: /images/20210813/27a786c24d32499a8add91de08163a8a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]: /images/20210813/88be3f4145b1451e91fff785b9c95c21.png
[20190915153849905.png]: /images/20210813/01011886db2d42468f93df781722807a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]: /images/20210813/e8a6181030f24c7eb4796d0794ccb464.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 5]: /images/20210813/32aacddf2a1c4ba888ebdb48cdbbf9ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 6]: /images/20210813/f46c435691c94a98b9bb1580f344ff04.png
[20190915160708154.png]: /images/20210813/268cb12ab73a4c779720013ed7a8a38f.png
[20190915160921495.png]: /images/20210813/4893d66e32c445279d9b187a0e9ed415.png
[20190915143335118.png]: /images/20210813/342310a87fb6465c90688e7ac5913ed9.png
[20190915143412832.png]: /images/20210813/62067ccd010447188436f59124f34e3d.png