5-Mysql-Explain查询执行计划

本文知识点来源于网络，仅限学习笔记使用，记录学习过程与心得，如有不适，请联系。

一、性能分析

1.1 表设计准则

1.1.1 关系型数据库三范式

1. 数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。
   第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库。
2. 要求数据库表中的每一个实例或行必须可以被唯一的区分
3. 要求一个数据库表中不包含已在其他表中包含的非主关键字信息。

1.1.2 拆表

大表拆小表，有大数据的列单独拆成小表。

1. 在一个数据库中，一般不会涉及属性过多的表。
2. 在一个数据库中，一般不会有超过500/1000万数据的表。
3. 有大数据的列单独拆成小表（如富文本编辑器，CKeditor）。

1.2 sql在Mysql内部的执行过程

1.2.1 DQL执行过程

1. 客户端发送一条查询语句给服务器
2. 服务器通过权限校验后，先检查缓存，如果命中了缓存，则立即返回在缓存中的结果，否则进入下一阶段。
3. 服务器端进行SQL解析，预处理，再由优化器根据该SQL所涉及的数据表的统计信息进行计算，生成对应的执行计划。
4. Mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将结果返回给客户端。

1.2.2 查询优化器

写的任何sql，到底是怎么样真正执行的，按照什么条件执行的，最后执行的顺序可能会有多个执行方案，查询优化器根据对数据表的统计信息（如索引，数据量）在真正执行一条sql之前，会根据自己内部的数据，进行综合的查询，根据Msql自身的统计信息，从多种执行方案当中，选择一个它认为最有的执行方案去执行。

1.2.3 做优化，做什么

做优化就是让查询优化器按照我们的想法，帮我们选择最优的执行方案，减少查询优化器内部自身查询优化造成的可能的不必要的IO。

二、Explain查看执行计划

2.1 使用方式

EXPLAIN sql语句

EXPLAIN SELECT * FROM employee where age = 34;

输出：

2.2 属性分析

2.2.1 id-select查询的序列号

包含一组数字，表示查询中执行select子句或操作表的顺序，值有三种情况：

1. id值相同表示执行顺序由上到下执行

EXPLAIN SELECT * FROM employee e,department d,customer c WHERE e.dep_id = d.id AND e.cus_id = c.id;

2. id值都不相同：执行顺序按id值降序，id大的优先执行

EXPLAIN SELECT * FROM department d where d.id = (SELECT e.id FROM employee e WHERE e.id = (SELECT c.id FROM customer c WHERE c.id = 1));

3. id值有相同的有不同的：id大的先执行，然后id相同的按从上到下执行 如e -> d -> derived2

EXPLAIN SELECT * FROM department d, (SELECT * FROM employee e GROUP BY e.dep_id) t WHERE d.id = t.dep_id;

2.2.2 select_type

查询类型，主要用去分别普通查询，联合查询，子查询等复杂查询

1. SIMPLE: 简单select查询，查询中不包含子查询或者union
2. PRIMARY: 查询中若包含任何复杂的子查询，最让层被标记PRIMARY
3. SUBQUERY:查询中若包含任何复杂的子查询，对应PRIMARY剩下的被标记SUBQUERY
4. DERIVED:在from列表中包含的子查询被标记为derived（衍生）
5. UNION: 从union表获取结果select
6. UNION RESULT: 两个union合并的结果集临时表

EXPLAIN SELECT * FROM employee e LEFT JOIN department d ON e.dep_id = d.id
UNION
SELECT * FROM employee e RIGHT JOIN department d ON e.dep_id = d.id;

2.2.3 table

显示当前数据是关于哪张表的

2.2.4 partitions

如果查询时基于分区表的话，会显示查询访问的分区

2.2.5 type

访问类型，最好达到ref级别，有以下值：

1. system：最好的情况，表里只有一行记录，是const的特例
2. const：通过索引一次就找到了

EXPLAIN SELECT * FROM employee WHERE id = 1;

3. ref：非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回的所有匹配某个单独值的行，可能会找到多个复核条件的行，所以它应该属于查找和扫描的混合体。
   例：将employee表的dep_id字段设置索引

EXPLAIN SELECT * FROM employee e,department d WHERE e.dep_id = d.id;

不是一一对应关系

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

EXPLAIN SELECT * FROM employee e,department d WHERE e.id = d.id;

一一对应

5. range：只检索给定范围行，一般就是你在where语句中出现between,<,>,in等

EXPLAIN SELECT * FROM employee WHERE id < 15;

6. index：只遍历索引树，通常比ALL快，因为索引文件比数据文件小

EXPLAIN SELECT id FROM employee;

7. ALL：全表扫描，从硬盘中读取

EXPLAIN SELECT * FROM employee;

2.2.6 possible_keys

可能会使用到的索引

2.2.7 key

实际使用到的索引

2.2.8 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度

2.2.8 ref

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

2.2.9 rows

每张表有多少行被优化器查询过，越少越好。

2.2.10 filtered

满足查询的记录数量的比例（%），值越大越好，该值并不准确。

2.2.11 Extra

额外信息

1. Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行（Mysql中无法利用索引完成排序操作称为文件排序）
2. Using temporary：产生临时表
3. Using index：使用了索引
4. Using where：使用了条件
5. Using join buffer：使用了连接缓存
6. impossible where：这个值永远不是真的条件永远不成立