mysql中的查询计划及sql语句性能分析

准备测试数据

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `dep_id` int DEFAULT NULL,
  `age` int DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `cus_id` int DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customer` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `department` (
  `id` int NOT NULL AUTO_INCREMENT,
  `deptName` varchar(30) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



 -- 测试数据
INSERT INTO employee VALUES ( 1, '鲁班', 1, 10, 1000.00, 1 );
INSERT INTO employee VALUES ( 2, '后裔', 1, 20, 2000.00, 1 );
INSERT INTO employee VALUES ( 3, '孙尚香', 1, 20, 2500.00, 1 );
INSERT INTO employee VALUES ( 4, '凯', 4, 20, 3000.00, 1 );
INSERT INTO employee VALUES ( 5, '典韦', 4, 40, 3500.00, 2 );
INSERT INTO employee VALUES ( 6, '貂蝉', 6, 20, 5000.00, 1 );
INSERT INTO employee VALUES ( 7, '孙膑', 6, 50, 5000.00, 1 );
INSERT INTO employee VALUES ( 8, '蔡文姬', 30, 35, 4000.00, 1 );

 -- 测试数据
 INSERT INTO department VALUES (1, '研发部(RD)', '2层');
 INSERT INTO department VALUES (2, '人事部(HR)', '3层');
 INSERT INTO department VALUES (3, '市场部(MK)', '4层');
 INSERT INTO department VALUES (4, '后勤部(MIS)', '5层');
 INSERT INTO department VALUES (5, '财务部(FD)', '6层');
  -- 测试数据
 INSERT INTO customer VALUES (1, 'zs');
 INSERT INTO customer VALUES (2, 'lisi');
 INSERT INTO customer VALUES (3, 'wangwu');

explain的简介

mysql中可以使用explain这个关键字来获取（查询）sql语句的查询执行计划的。使用explain关键字，可以模拟mysql优化器执行的sql语句，从而知道mysql是如何处理sql语句的。通过explain可以分析查询语句或表结构的性能瓶颈。

explain的作用

①、查看表的读取顺序

②、数据读取操作的操作类型

③、查看哪些索引可以使用

④、查看哪些索引被实际使用

⑤、查看表之间的引用

⑥、查看每张表有多少行被优化器执行

explain的使用方法

explain sql语句

explain select * from employee;

explain执行计划输出中的各个列的详解

id

描述：select查询的序列号,包含一组数字，该组数字表示查询中执行select子句或操作表的顺序

id值的三种情况如下：

id相同

-- 分析的sql语句
explain select * from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id;

分析的结果截图：

从上图中可以看到，id列的值都是1。那么该条sql语句的执行顺序是由上到下，也就是说 先查询的c表 然后查询 e表 最后查询d表。

id不同

-- 分析的sql语句
EXPLAIN SELECT * from department  WHERE id = (SELECT id from employee WHERE id= (SELECT id from customer WHERE id = 1) )

从上图中可以看到，id列的值是1、2、3。那么该条sql语句的执行顺序是从大到小（由下到上），也就是说 id列的值是3的先执行 其次是id列的值是2 最后是id列的值是1再执行。

这里都是子查询，如果是子查询，id的序号会递增，id值越大优先级越高，优先被执行。**

id相同和不同

-- 分析的sql语句
EXPLAIN  SELECT * FROM department d, ( SELECT * FROM employee GROUP BY dep_id ) t WHERE d.id = t.dep_id;

从上图中可以看到，id列的值是1、1、2。那么该条sql语句的执行顺序是怎样的呢？根据上面的①和②这里应该也能猜到了。该条sql语句的执行顺序是 先执行id列的值是2的，其次执行id列的值是1的（最上面那个id列的值是1的，也就是table列的值是d），最后执行中间那个id列的值是1的。

上图中有一个select_type列，其中select_type有一列的值是derived，而derived表示 衍生出来的虚表。再次说明，id值越大，优先级越高，越先执行。

总结

相同，顺序走（由上到下）,不同，看谁大，大的先执行。

select_type

**描述：**查询类型，主要用于区别普通查询，联合查询，子查询等复杂查询。

select_type列的值主要有以下6种情况：

①、SIMPLE：简单的select查询，查询中不包含子查询或者UNION

②、PRIMARY：查询中若包含任何复杂的子查询，那么最外层的查询则被标记为primary

③、SUBQUERY：在select或where中包含了子查询

④、DERIVED：在from列表中包含的子查询被标记为derived(衍生)，把结果放在临时表当中。

⑤、UNION：若第二个select出现在union之后，则被标记为union。若union包含在from子句的子查询中，外层select将被标记为deriver。

⑥、UNION RESULT：从union表获取结果select。两个UNION合并的结果集在最后。

table

**描述：**显示当前查询的数据是关于哪张表的。

partitions

描述：如果查询是基于分区表的话，会显示查询访问的分区。

type（重要）

描述：

表示访问某个表的类型。更专业一点的解释就是：type代表着mysql对某个表的执行查询时的访问方法，其中type列的值就表明了这个访问方法是个啥。通过type可以知道mysql是做了全表扫描还是范围扫描等，从而知道当前的sql语句到底要不要去优化。

type列的值一般最常用的有7种，按照最好到最差来排序 分别是：system>const>eq_ref>ref>range>index>ALL。

system

表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么该表的type列的值就是system。这是const类型的特例，平时不会出现，也不用奢求将sql优化到这种级别的。

const

表示通过索引（主键索引或唯一索引）一次就找到了那一条数据。这里和上面那个system的区别就是 system表里面只能有一条数据，而const表示表中可能会有多条数据，但是const能直接从多条数据中直接定位到那一条数据（通过主键索引或唯一索引）。因为只匹配一行数据，所以const速度很快。

eq_ref

唯一性索引扫描。对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

employee表中有五条数据，而department表中有对应的五条数据，其中employee的id（主键索引）和department的id（主键索引）是一 一对应的，所以这里就会出现eq_ref，eq_ref也就是这个意思。

eq_ref基本上很难在单表上出现，一般都是在多表的情况下才会出现eq_ref。

ref

非唯一性索引扫描。大白话解释一下就是：出现该连接类型的条件是， 查找条件列使用了索引而且不是使用的主键索引和唯一索引（unique），使用的是普通索引。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使 使用索引快速查找到了第一条数据，仍然不能停止扫描，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的age列添加一个普通的索引

ALTER TABLE employee ADD INDEX idx_age(age) USING BTREE;

range

指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。一般就是在你的where语句中出现between，and，<，>，or，in等查询，那么type列的值就是range

index

Full Index Scan。index与All区别为index类型只遍历索引树，通常比All要快，因为索引文件通常比数据文件要小。all和index都是读全表，但index是从索引中读取，all是从硬盘当中读取。

ALL

将表中的所有数据进行了扫描（全表扫描），从硬盘当中读取数据。如果出现了All 且数据量非常大，那么该条sql必须去做优化的。

说下要求：一般来说，要保证SQL查询至少达到range级别，最好能达到ref级别。

possible_keys

**描述：**表示这张表中可能会用到的索引（一个或多个），查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用到，可能自己创建了4个索引，在实际执行sql查询的时候，根据mysql内部的自动判断，只使用了3个。

key（重要）

**描述：**mysql在执行的时候实际使用到的索引，如果为NULL，则没有使用索引。

其它说明：

查询中若使用了覆盖索引，则该索引仅出现在key列表中。

覆盖索引：查询的字段和建立的字段刚好吻合，这种我们称为覆盖索引。

possible_keys与key关系：前者表示理论应该用到哪些索引，后者表示实际用到了哪些索引。

key_len

**描述：**表示索引中使用的字节数，可通过该列计算查询中使用的索引长度 。下面为了演示这种情形，给employee表添加一个复合索引。

ALTER TABLE employee ADD INDEX idx_name_dep_id_age(name, dep_id, age) USING BTREE;

ref

描述：

索引是否被引入到，到底引用到了哪几个索引。

这里就不写加索引的语句了，直接上几张截图看吧

rows

描述：

根据表的统计信息及索引选用情况，大致估算出找到所需的记录所需要扫描（读取）的行数。表有多少行被优化器查询过。没有建立索引和建立索引之后 rows所显示的数据肯定是不一样的。这里就不进行截图演示了。

filtered

**描述：**满足查询的记录数量的比例，注意是百分比，不是具体记录数，值越大越好，filtered列的值依赖统计信息，并不十分准确。对于单表查询来说，这个filtered列的值没什么意义，更关注在连接查询中对应的执行计划记录的filtered列的值。关于这里的多表demo也就先不演示了。

Extra

**描述：**顾名思义，Extra列是用来说明一些额外信息的，可以通过这些额外信息来更准确的理解mysql到底将如何执行给定的查询语句。mysql提供的额外信息有好几十个，这里就不一个一个介绍了，只挑一些平时常见的或者比较重要的做下说明。

①、Using filesort：专业术语成为“文件排序”。说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行。mysql无法利用索引完成排序操作称为"文件排序"，当你看到using filesort的时候，那么一定要优化该条sql语句。（得到所需结果集，需要对所有记录进行"文件排序" 出现这个 表示该条SQL语句性能较低，需要进行优化）

**关于filesort的更多详解：**filesort 并不是说通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作。文件排序是通过相应的排序算法，将取得的数据在内存中进行排序。mysql需要将数据在内存中进行排序，所使用的内存区域也就是我们通过 sort_buffer_size 系统变量所设置的排序区。这个排序区是每个 Thread 独享的，所以说可能在同一时刻 在mysql中可能存在多个 sort buffer 内存区域。

filesort分两种：

双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。排序后再吧查询字段依照行指针取出，共执行两次磁盘io。

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。 执行一次磁盘io。

在mysql4.1版本之前只有第一种排序算法 双路排序。第二种算法是从mysql4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sort buffer 空间。当然，mysql4.1开始的以后所有版本同时也支持第一种算法。

典型说明：在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

Using filesort示例截图：

②、Using temporary：在许多查询的执行过程中，mysql可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，mysql很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示using temporary提示。当你看到using temporary的时候，那么一定要优化该条sql语句。（需要建立临时表(temporary table)来暂存中间结果，出现这个 表示该条SQL语句性能较低，通常情况下需要进行优化）

③、Useing index：表示相应的select中使用了覆盖索引，避免访问了表中的数据行，效率很好。如果同时出现了Using where 表明索引被用来执行索引键值的查找。如果没有同时出现Using where 表明索引 用来读取数据而非执行查找动作。（SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录，出现这个 表示该条SQL语句性能较好）

示例截图：

using index示例截图如下：

using where using index示例截图如下：

④、Using where：说明使用了where过滤（SQL使用了where条件过滤数据 需要需要优化该条SQL语句 需要配合explain结果中的type（连接类型）来综合判断）

⑤、Using join buffer(Block Nested Loop)：在连接查询执行过程中，当sql查询语句不能有效的利用索引加快访问速度，mysql选择退而求其次，一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。（需要进行嵌套循环计算 出现这个 表示该条SQL语句性能较低，需要进行优化）

打个比方：内层和外层的type均为ALL，rows均为4，需要循环进行4*4次计算。

典型说明：两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

⑥、impossible where：where子句中的值总是false 获取不到任何数据。出现这种提示通常情况下说明你的sql语句有误，请看情况选择是否进行修改相应的sql语句。

⑦、Using index condition：确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。（出现这个 表示 该条SQL语句性能也较高，但不如Using index）