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

---

众所周知，在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;

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

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

Column	JSONName	Meaning
id	select_id	查询标识。id越大优先执行；id相同自上而下执行
select_type	Node	查询的类型
table	table_name	查询的表，（<union M,N> 与 临时表和结果集）
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

UNI