文章目录
索引分类索引简介查询优化器概述执行计划查询执行计划的方法 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 <select statement>;explain [FORMAT=JSON] SQL语句，[]代表可选项FORMAT=JSON用意是以json格式输出执行计划的内容，\G 输出格式由列转行的方式
接下来，我们执行一条SQL，看一下这条SQL的执行计划是什么样子的？
explain select * from pcmc_user;
执行完成之后，执行计划是这个样子的：
当然，看到执行计划的这些输出项，估计很多人都是一脸蒙圈的，作为热心肠的虹猫，肯定体贴的帮大家都整理出来了每个输出项的含义喽，是不是很贴心！！！
通过表格中的输出项说明，想必大家对执行计划都有了初步的认识和了解，但估计很多小伙伴都会有同样的疑问，“查询的类型都有哪些呢？”、“查询表联接类型都有些什么类型呢？”、“优化器优化的提示信息都会提示什么呢？”。
那就让我们带着这些疑问，进入下面这几个关键输出项的世界中吧！
select_type 查询的类型
场景一
explain select * from pcmc_user where user_code = 'admin';
SQL的执行计划为：
从执行计划就可以看出，查询类型为：SIMPLE，查询表为：pcmc_user，用到的索引为：PRIMARY主键索引
这就是一个简单的查询类型，没有任何其他的附加查询，这个操作的select_type就是 SIMPLE
场景二
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
场景三
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
场景四
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，结果不依赖于外部查询结果集
场景五
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’
场景六
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中被删除
场景七
explain select * from pcmc_user where user_code =(select max(LAST_INSERT_ID()) from pcmc_role_user );
SQL的执行计划为：
从执行计划中就可以看出，根据id越大越优先执行的规则，先执表子查询，然后执行内层user_code =（）的SQL查询。
UNCACHEABLE SUBQUERY ：结果集不能被缓存的子查询，不可物化每次都需要计算（动态计算、耗时操作）
场景八
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 查询的类型的所有场景就分析完了，我们再重新回顾一下。
select_type 查询的类型
场景一
explain select * from dual;
SQL的执行计划：
NULL：不访问任何一张表
场景二
explain select * from pcmc_user where user_code = 'admin';
SQL的执行计划：
system：根据主键查询系统表且这个表里面只有一条记录【特殊的const场景】
const：常量查询非常快，主键或者唯一索引的常量查询，表格最多只有一条记录符合查询
场景三
explain select * from pcmc_user a, pcmc_role_user b where a.user_code = b.user_code;
SQL的执行计划：
eq_ref：使用PRIMARY或者UNION 和前面的结果集进行匹配
场景四
explain select * from pcmc_user where user_code = 'admin';
SQL的执行计划：
ref：非聚集索引的常量查询
场景五
explain select * from pcmc_user where MATCH(user_code) AGAINST ('admin');
SQL的执行计划：
full text：查询的过程中，使用到了fulltext索引，（fulltext index 在 InnoDB 引擎中，只有5.6版本之后的支持）
场景六
explain select * from pcmc_user where menu_type = '2' or menu_type is null;
ref_or_null：跟ref查询类似，在ref查询的基础上，加多一个null值的条件判断
场景七
explain select * from pcmc_user where menu_type = '2' or user_code = 'admin';
SQL的执行计划：
index_merge：索引合并（分别两个查询条件的结果，然后合并）
场景八
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是默认进行优化的。
场景九
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是默认进行优化的。
场景十
explain select * from pcmc_user where pagesize > 10;
SQL的执行计划：
range：= ,<>,>,>=,<,<=,is null,BETWEEN,IN,<=>(恒等于，左边可以推出右边，右边也可以推出左边)
场景十一
explain select user_code from pcmc_user;
SQL的执行计划：
index：执行full index scan，直接从索引中取到想要的结果数据，也就是可以避免回表
场景十二
explain select * from pcmc_user;
SQL的执行计划：
ALL：full data scan，这是最差的一种查询方式
到这里，所有的表联接类型就和大家介绍完了，大家也发现了这十二种场景，查询效率是依次递减的，因此我们一般查看执行计划时，根据access_type就可以大致分析到效率是差在哪？
下面让我们来回顾一下这些表联接类型：
Extra 优化器优化提示信息
在MySQL中，针对执行SQL，优化器进行一些优化操作，在优化结束后，会在执行计划里面给出一些优化的信息，帮助用户能够更好的理解MySQL具体是做了哪些优化。
场景一
explain select * from pcmc_user order by pagesize;
SQL的执行计划：
Using filesort：使用外部排序而不是按照索引顺序排序结果【差，需要加索引】
场景二
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：使用了临时表
场景三
explain select * from pcmc_user where pagesize is null order by employee_code;
SQL的执行计划：
Using index condition：用了索引做判断
场景四
explain select * from pcmc_user where user_code =(select max(LAST_INSERT_ID()) AS user_code from pcmc_user );
SQL的执行计划：
Using index：表示MySQL使用覆盖索引，避免全表扫描
场景五
explain select * from pcmc_user where user_code < 10;
SQL的执行计划：
Using where：通常是进行了全表/全索引扫描后再用WHERE子语句完成结果过滤【差，需要加索引或者SQL写的不好】
场景六
explain select * from pcmc_user where user_code is null;
SQL的执行计划：
Impossible WEHRE：不成立的判断条件，例如user_code 不能为空
场景七
explain select * from pcmc_user where pagesize =(select max(pagesize) AS pagesize from pcmc_user);
SQL的执行计划：
Select tables optimized away：使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询【比较好】
到这里，优化器优化提示信息类型就和大家介绍完了，下面让我们一起回顾一下：
最后
如果大家对MySQL的优化器和执行计划还有什么疑问的话？如果有的话，欢迎加入粉丝群一起交流哈。
