MySQL-查询优化器和执行计划一文搞定

文章目录

• • 索引分类
  • 索引简介
  • 查询优化器概述
  • 执行计划
  • • 查询执行计划的方法
  • select_type 查询的类型
  • select_type 查询的类型
  • Extra 优化器优化提示信息
  • 最后

---

众所周知，在mysql数据库中，调优的重中之重的就是索引，而我们在使用查询优化器和执行计划时，其实也是为了检查SQL执行时是否击中了索引，从而根据执行情况来进行SQL优化。

今天我们就来解开MySQL中查询优化器和执行计划的神秘面纱，但是在这之前，我们先来认识一下索引，索引是最核心的优化前提。

索引分类

按照索引实现方式划分的话，索引有两种类型：1、Btree 2、Hash

• Btree 这种类型的索引是比较常见的，比如我们最熟悉的InnoDB搜索引擎中，就是使用Btree这种类型
• Hash这种类型的索引一般不常见，因为它主要存在于MySQL自身的底层实现中，与数据库的功能使用关联不大。

按照索引功能划分，可分为以下几种：

• 普通索引：单纯为了加快查询速度
• 全文索引：针对text字段类型加索引
• 唯一索引：加快查询速度，且约束该列的值不能重复
• 主键索引：和唯一索引功效类似，但是一张表只能有一个主键索引，而唯一主键可以有多个

索引简介

• 聚集索引（聚簇索引）
  • InnoDB表都会有一个称为聚簇索引的特殊索引（默认为主键，如果表没有主键，则选择唯一键，如果连唯一键都没有，则会选择MySQL隐式的rowid键）
• 二级索引（非聚簇索引）
  • 所有的非聚簇需索引，都被称为二级索引
• 单列索引
  • 只涉及一个列的索引称为单列索引
• 多列索引（复合索引、联合索引、组合索引）
  • 涉及到多个列的所有称为多列索引，最多涉及16列
• 覆盖索引
  • 从索引本质上说，覆盖索引其实并不算一种索引，它其实是指的一种特殊的场景：“查询的SQL语句所需要的列，被包含在查询条件的索引列里面”，简单说就是条件列包含了查询列这种场景
  • 比如：select max(user_code) from user_info where user_code > 1001;

在了解了索引的基本概念之后，下面，我们就来揭开查询优化器和执行计划的面纱。

查询优化器概述

在MySQL数据库中，有一个核心模块，叫做查询优化器。查询优化器的主要任务就是找到执行SQL查询的最佳计划

查询优化器会根据表、列、索引的详细信息以及SQL语句中的条件等等方面的调整，来让SQL更高效的执行

查询优化器选择执行效率最高的查询的一组操作称为"查询执行计划"，也称为EXPLAIN计划。

下面，就进入MySQL的另一个核心模块，执行计划的篇章。

执行计划

上面也提到过，执行计划就是效率最高的查询的一组操作，而我们在平时使用MySQL数据库时，如果遇到了SQL低效的情况，就可以通过分析EXPLAIN计划来发现导致低效的原因。

查询执行计划的方法

EXPLAIN ;
explain [FORMAT=JSON] SQL语句，[]代表可选项
FORMAT=JSON用意是以json格式输出执行计划的内容，G 输出格式由列转行的方式

接下来，我们执行一条SQL，看一下这条SQL的执行计划是什么样子的？

explain select * from pcmc_user;

执行完成之后，执行计划是这个样子的：

当然，看到执行计划的这些输出项，估计很多人都是一脸蒙圈的，作为热心肠的虹猫，肯定体贴的帮大家都整理出来了每个输出项的含义喽，是不是很贴心！！！

Column	JSONName	Meaning
id	select_id	查询标识。id越大优先执行；id相同自上而下执行
select_type	Node	查询的类型
table	table_name	查询的表，（ 与 临时表和结果集）
partitions	partitions	是否为分区表
type	access_type	本次查询表连接类型，从这个输出项可以看到本次查询的大概效率
possible_keys	possible_keys	可能选择的索引
key	key	实际使用的索引
key_len	key_length	使用的索引长度
ref	ref	哪一列或常数在查询中和索引键一起使用
rows	rows	估计查询的行数，预计需要扫描的记录数越少越好
filtered	filtered	被条件过滤掉的行数百分比
Extra	None	优化器优化查询的一些提示信息

通过表格中的输出项说明，想必大家对执行计划都有了初步的认识和了解，但估计很多小伙伴都会有同样的疑问，“查询的类型都有哪些呢？”、“查询表联接类型都有些什么类型呢？”、“优化器优化的提示信息都会提示什么呢？”。

那就让我们带着这些疑问，进入下面这几个关键输出项的世界中吧！

select_type 查询的类型

1. 场景一

   explain select * from pcmc_user where user_code = 'admin';
   

   SQL的执行计划为：

   从执行计划就可以看出，查询类型为：SIMPLE，查询表为：pcmc_user，用到的索引为：PRIMARY主键索引

   这就是一个简单的查询类型，没有任何其他的附加查询，这个操作的select_type就是 SIMPLE

2. 场景二

   explain select * from pcmc_user where user_code = 'admin' union select * from pcmc_user where user_code = 'test001';
   

   SQL的执行计划为：

从执行计划中就可以看出，根据id越大越优先执行的规则，先执行最外层 user_code = 'test001’的SQL查询，然后进行合并结果集，最后执行内层user_code = 'admin’的SQL查询。

PRIMARY : 最外层开始查询

UNIOn ：UNIOn 语句的第一个select查询为PRIMARY，第二个及之后的所有select语句的select_type都是UNIOn

UNIOn RESULT ：每个结果集取出来后，会做合并操作，这个操作的select_type就是UNIOn RESULT

3. 场景三

   explain select * from pcmc_user where user_code in (
   	select user_code from pcmc_user where user_code = 'admin' union select user_code from pcmc_user where user_code = 'test001'
   );
   

   SQL的执行计划为：

从执行计划中就可以看出，根据id越大越优先执行的规则，先执行子查询 user_code = 'test001’的SQL查询，然后执行子查询 user_code = 'admin’的SQL查询，合并结果集，最后执行外层层user_code in （xxx）的SQL查询。

DEPENDENT UNIOn：子查询中的UNIOn的操作，从 UNIOn 第二个查询及之后所有的select语句的select_type 都是 DEPENDENT UNIOn

DEPENDENT SUBQUERY ：子查询中内层的第一个select，依赖与外部查询的结果集，这个操作的select_type就是 DEPENDENT SUBQUERY

4. 场景四

   explain select * from pcmc_user where user_code in (
   	select max(user_code) from pcmc_user where menu_type is null
   );
   

   SQL的执行计划为：

从执行计划中就可以看出，根据id越大越优先执行的规则，先执行子查询 menu_type is null 的SQL查询，然后执行内层user_code = 'admin’的SQL查询。

SUBQUERY : 子查询内层查询的第一个select，结果不依赖于外部查询结果集

5. 场景五

   explain select * from pcmc_user a ,
   (
   	select max(user_code) as user_code from pcmc_user where menu_type is null
   ) b where a.user_code = b.user_code;
   

   SQL的执行计划为：

从执行计划中就可以看出，根据id越大越优先执行的规则，先执表联接查询 menu_type is null 的SQL查询，然后执行内层a.user_code = b.user_code的SQL查询。

DERIVED：派生表，子查询在from子句中

mysql不会为每个子查询都创建派生表，派生表的目的就是用于保存子查询中的中间结果

MySQL默认开启，可以关闭创建派生表，修改属性 set optimizer_switch = ‘derived_merge = off’

6. 场景六

   explain select * from pcmc_user where user_code in
   (
   	select user_code from pcmc_role_user where is_enabled = '1' 
   );
   

   SQL 的执行计划为：

从执行计划中就可以看出，根据id越大越优先执行的规则，先执表子查询查询 is_enabled = ‘1’ 的SQL查询，然后执行内层user_code in（）的SQL查询。

MATERIALIZED ：物化子查询，将子查询的结果放入查询缓存

从 MySQL 5.7.20 开始，查询缓存就被弃用了，并在MySQL 8.0中被删除

7. 场景七

   explain select * from pcmc_user where user_code =
   (
   	select max(LAST_INSERT_ID()) from pcmc_role_user 
   );
   

   SQL的执行计划为：

从执行计划中就可以看出，根据id越大越优先执行的规则，先执表子查询，然后执行内层user_code =（）的SQL查询。

UNCACHEABLE SUBQUERY ：结果集不能被缓存的子查询，不可物化每次都需要计算（动态计算、耗时操作）

8. 场景八

   explain select * from pcmc_user where user_code =
   (
   	select max(LAST_INSERT_ID()) as user_code from pcmc_role_user 
   	union all select max(LAST_INSERT_ID()) as user_code from pcmc_role_user 
   );
   

   SQL的执行计划为：

从执行计划中就可以看出，根据id越大越优先执行的规则，先执表子查询中UNIOn ALL 中第二个语句的查询SQL，然后执行内UNIOn ALL 中第一个语句的查询SQL，然后将两个结果集合并，最后执行外层user_code =（）的SQL查询

UNCACHEABLE UNIOn：UNIOn 中第二个语句或者后面的语句属于不可缓存的子查询

到这里，select_type 查询的类型的所有场景就分析完了，我们再重新回顾一下。

Column	JSONName	Meaning
SIMPLE	None	简单查询（不使用UNIOn或者子查询）
PARIMARY	None	外层查询，主查询
UNIOn	None	UNIOn中第二个或后面的语句
DEPENDENT UNIOn	dependent(true)	UNIOn中第二个语句或后面的语句，独立于外部查询
UNIOn RESULT	union_result	UNIOn的结果
SUBQUERY	None	子查询中的第一个SELECT
DEPENDENT SUBQUERY	dependent(true)	子查询中的第一个SELECT，独立于外部查询
DERIVED	None	子查询在FROM子句中
METERIALIZED	meterialized_from_subquery	物化子查询
UNCACHEABLE SUBQUERY	cacheable(true)	结果集不能被缓存的子查询
UNCACHEABLE UNIOn	cacheable(true)	UNIOn中第二个语句或后面的语句属于不可缓存的子查询

select_type 查询的类型

1. 场景一

   explain select * from dual;
   

   SQL的执行计划：

NULL：不访问任何一张表

2. 场景二

   explain select * from pcmc_user where user_code = 'admin';
   

   SQL的执行计划：

system：根据主键查询系统表且这个表里面只有一条记录【特殊的const场景】

const：常量查询非常快，主键或者唯一索引的常量查询，表格最多只有一条记录符合查询

3. 场景三

   explain select * from pcmc_user a, pcmc_role_user b where a.user_code = b.user_code;
   

   SQL的执行计划：

eq_ref：使用PRIMARY或者UNIOn 和前面的结果集进行匹配

4. 场景四

   explain select * from pcmc_user where user_code = 'admin';
   

   SQL的执行计划：

ref：非聚集索引的常量查询

5. 场景五

   explain select * from pcmc_user where MATCH(user_code)  AGAINST ('admin');
   

   SQL的执行计划：

full text：查询的过程中，使用到了fulltext索引，（fulltext index 在 InnoDB 引擎中，只有5.6版本之后的支持）

6. 场景六

   explain select * from pcmc_user where  menu_type = '2' or menu_type is null;
   

   ref_or_null：跟ref查询类似，在ref查询的基础上，加多一个null值的条件判断

7. 场景七

   explain select * from pcmc_user where  menu_type = '2' or user_code = 'admin';
   

   SQL的执行计划：

index_merge：索引合并（分别两个查询条件的结果，然后合并）

8. 场景八

   set optimizer_switch='materialization=off';
   explain select * from pcmc_user where  user_code not in (
   	select user_code from pcmc_user where user_name like '%aa%'
   );
   set optimizer_switch='materialization=on';
   

   SQL的执行计划：

unique_subquery：IN 子查询的结果由聚簇索引或唯一索引覆盖

ps：这里是关闭了优化器才显出出这种表联接类型，MySQL中对这种查询SQL是默认进行优化的。

9. 场景九

   set optimizer_switch='materialization=off';
   explain select * from pcmc_user where  EMPLOYEE_CODE not in (
   	select EMPLOYEE_CODE from pcmc_user where EMPLOYEE_CODE like '%aa%'
   );
   set optimizer_switch='materialization=on';
   

   SQL的执行计划：

index_subquery：与unique_subquery类似，但是使用的是二级缓存

ps：这里是关闭了优化器才显出出这种表联接类型，MySQL中对这种查询SQL是默认进行优化的。

10. 场景十

    explain select * from pcmc_user where pagesize > 10;
    

    SQL的执行计划：

range：= ,<>,>,>=,<,<=,is null,BETWEEN,IN,<=>(恒等于，左边可以推出右边，右边也可以推出左边)

11. 场景十一

    explain select user_code from pcmc_user; 
    

    SQL的执行计划：

index：执行full index scan，直接从索引中取到想要的结果数据，也就是可以避免回表

12. 场景十二

    explain select * from pcmc_user; 
    

    SQL的执行计划：

ALL：full data scan，这是最差的一种查询方式

到这里，所有的表联接类型就和大家介绍完了，大家也发现了这十二种场景，查询效率是依次递减的，因此我们一般查看执行计划时，根据access_type就可以大致分析到效率是差在哪？

下面让我们来回顾一下这些表联接类型：

type	Meaning
system	表仅一行数据（系统表），这是const连接类型的特例
const	表最多只有一个匹配行，在查询开始时被读取（唯一索引扫描）
eq_ref	使用PRIMARYKEY或UNIOn和结果集进行匹配
ref	使用二级索引进行匹配
ref_or_null	使用类型类似ref，只是搜索的行中包含NULL值，MySQL做了额外的查找
fulltext	使用全文索引时出现
index_merge	使用了索引合并优化
uniqe_subquery	IN 子查询的结果由聚簇索引或唯一索引覆盖
index_subquery	子查询中可以用到索引
range	使用索引检索给定范围内的行
index	该链接类型与ALl相同，除了扫描索引树。如果查询的字段都在索引列中，则使用index类型，否则为ALL类型
ALL	对于前面表的结果集中，进行了全表扫描，是最差的一种类型，应该考虑优化

Extra 优化器优化提示信息

在MySQL中，针对执行SQL，优化器进行一些优化操作，在优化结束后，会在执行计划里面给出一些优化的信息，帮助用户能够更好的理解MySQL具体是做了哪些优化。

1. 场景一

   explain select * from pcmc_user order by pagesize;
   

   SQL的执行计划：

Using filesort：使用外部排序而不是按照索引顺序排序结果【差，需要加索引】

2. 场景二

   explain select * from pcmc_user where user_code in
   (
   	select user_code from pcmc_user where user_code = 'admin' union select user_code from pcmc_user where user_code = 'test001'
   ); 
   

   Using temporary：使用了临时表

3. 场景三

   explain select * from pcmc_user where pagesize is null order by employee_code;
   

   SQL的执行计划：

Using index condition：用了索引做判断

4. 场景四

   explain select * from pcmc_user where user_code =
   (
   	select max(LAST_INSERT_ID()) AS user_code from pcmc_user 
   ); 
   

   SQL的执行计划：

Using index：表示MySQL使用覆盖索引，避免全表扫描

5. 场景五

   explain select * from pcmc_user where user_code < 10;
   

   SQL的执行计划：

Using where：通常是进行了全表/全索引扫描后再用WHERe子语句完成结果过滤【差，需要加索引或者SQL写的不好】

6. 场景六

   explain select * from pcmc_user where user_code is null;
   

   SQL的执行计划：

Impossible WEHRE：不成立的判断条件，例如user_code 不能为空

7. 场景七

   explain select * from pcmc_user where pagesize =
   (
   	select max(pagesize) AS pagesize from pcmc_user
   ); 
   

   SQL的执行计划：

Select tables optimized away：使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询【比较好】

到这里，优化器优化提示信息类型就和大家介绍完了，下面让我们一起回顾一下：

Column	Meaning
Using filesort	将用外部排序而不是按照索引顺序排列结果，数据较少时从内存排序，否则需要在磁盘完成排序，代价非常高，需要添加合适的索引
Using temporay	需要创建一个临时表来存储结果，这通常发生在没有索引的列进行GROUP BY时，或者ORDER BY里的列不都在索引里面，需要添加合适的索引
Using index	表示MySQL使用覆盖索引避免全表扫描，不需要再到表中进行二次查找数据，这是比较好的结果之一，注意不要和type中的index类型混淆
Using where	通常是进行了全表/全索引扫描后再用WHERe子语句完成结果过滤，需要添加合适的索引
Impossible WHERe	对WHERe子句判断的结果总是false而不能选择任何呼叫，例如：where 1 = 0，无需过多关注
Select tables optimized away	使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询，例如：MIN()、MAX()，这种也是比较好的结果之一

最后

如果大家对MySQL的优化器和执行计划还有什么疑问的话？如果有的话，欢迎加入粉丝群一起交流哈。