MYSQL调优之Explain关键字详解

吾爱主题阅读：176 2023-11-10 16:31:00 评论：0

explain关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或表结构的性能瓶颈。执行语句：explain + SQL语句。表头信息如下：

explain各个字段代表的意思

id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。
select_type ：查询类型或者是其他操作类型。
table ：正在访问哪个表。
partitions ：匹配的分区。
type ：访问的类型。
possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到。
key ：实际使用到的索引，如果为NULL，则没有使用索引。
key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值。
rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数。
filtered ：查询的表行占表的百分比。
Extra ：包含不适合在其它列中显示但十分重要的额外信息。

ID 字段

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

id 相同： 执行顺序由上而下：

explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';

id 不同： 如果是子查询，id 序号会递增，id 越大优先级越高，越先被执行。

explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = ''));

id 相同不同同时存在： id 如果相同，可以认为是一组，由上往下执行；在所有组里 id 越大，优先级越高，越先执行。

explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 where s1.id = t2.id;

select_type 字段

主要用于区别普通查询、联合查询、子查询等的复杂程度。
SIMPLE： 简单的 select 查询，查询中不包含子查询或者 UNION。
PRIMARY： 查询中若包含任何复杂的自查询，最外层查询为 PRIMARY。

SUBQUERY： 在 SELECT 或 WHERE 中包含子查询。
UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行。
DERIVED： 在 FROM 列表中包含的子查询被标记为 DERIVED（衍生）MySQL 会递归执行这些子查询，把结果放进临时表。

UNION： 若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION，若 UNION 包含在 FROM 子句的子查询，则外层SELECT 将被标记为 DERIVED。
UNION RESULT： 从 UNION表中获取结果的 SELECT。

table字段

显示这行数据是关于那张表

type字段

首先说一下这个字段，要记住以下10个状态，（从左往右，越靠左边的越优秀）

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

NULL

MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。存在这样一种情况，大家都知道索引是将数据在B+Tree中进行排序了，所以你的查询速率才这么高，那么B+树的最边上的叶子节点是不是要么是最大值要么是最小值啊？既然你都知道了，那MySQL比你更知道啊！当你要查询最大值或者最小值时，MySQL会直接到你的索引得分叶子节点上直接拿，所以不用访问表或者索引。

NULL的前提是你已经建立了索引。

SYSTEM

表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。

const

表示通过索引一次就能找到，const用于比较 primary和 unique索引。因为只匹配一行数据，所以很快。

简单来说，const是直接按主键或唯一键读取。

eq_ref

用于联表查询的情况，按联表的主键或唯一键联合查询。多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。

ref 可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据。

ref_or_null 类似ref，但是可以搜索值为NULL的行。

index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

range

只检索给定范围的行，使用一个索引来选择行。一般where语句中出现between、<、>、in等的查询。这种范围扫描索引比全表扫描要好，因为只需开始索引的某一点，而结束另一点，不用扫描全部索引；

index

Full Index Scan，index与 ALL区别为 index类型只遍历索引树，索引文件通常比数据文件小。index从索引中读取，而All是从硬盘读取；

ALL

从磁盘中读取；如果一个查询的type是All,并且表的数据量很大，那么请解决它！！！

possible_keys字段

这个表里面存在且可能会被使用的索引，可能会在这个字段下面出现，但是一般都以key为准。

key字段

实际使用的索引，如果为null,则没有使用索引，否则会显示你使用了哪些索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

ref字段

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

rows字段和Filter字段

rows是根据表的统计信息和索引的选用情况，优化器大概帮你估算出你执行这行函数所需要查询的行数。

Filter是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。

Extra字段

Using index

表示相应的 select操作中使用了覆盖索引（convering index），避免访问了表的数据行，效率不错！

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序 order by和分组查询 group by。

Using fileSort

表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序。

MySQL无法利用索引完成的排序操作称为“文件排序”。导致该问题的原因一般是Where条件和order by子句作用在了不同的列上，一般可以通过合适的索引来减少或者避免。（出现表示不好）

上面提到的常见情况，SQL语句通常写成这样select * from a where type = 5 order by id，这类语句一般会产生Using filesort这个选项，即使你在type和id上分别添加了索引。我们想一下它的工作过程，先根据type的索引从所有数据信息中挑选出满足type = 5条件的，然后根据id列的索引信息对挑选的数据进行排序，所以产生了Using filesort选项。可以通过联合索引解决这个问题，即在type, id两列上建立一个联合索引。

Using where

查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra 中为 Using where。

所谓前导列，就是在创建复合索引语句的第一列或者连续的多列。比如通过：CREATE INDEX comp_ind ON table1(x, y, z)创建索引，那么x,xy,xyz都是前导列，而yz，y，z这样的就不是。

using where,using index

查询的列被索引覆盖，并且 where筛选条件是索引列之一但是不是索引的前导列，Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据；

查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查询到符合条件的数据

Using index condition

查询的列不全在索引中，where条件中是一个前导列的范围

查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）

NULL（既没有Using index，也没有Using where Using index，也没有using where）。

查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)。

Using join buffer

使用了连接缓存。

小表驱动大表

在表连接过程中。一般选择小表作为驱动表，大表作为被驱动表。

驱动表(小表)的连接字段无论建立没建立索引都需要全表扫描的。被驱动表(大表)如果在连接字段建立了索引，则可以走索引。如果没有建立索引则也需要全表扫描。

两张表连接的情况

被驱动表的连接字段有索引：主键索引

对于驱动表中的每一条数据，到被驱动表的聚簇索引上寻找其对应的数据。

被驱动表的连接字段有索引：二级索引

对于驱动表上的每一条数据，到被驱动表的二次索引上寻找其对应的数据id，然后再根据数据id到聚簇索引上寻找对应的数据。

被驱动表的连接字段没有索引

对于驱动表上的每一条数据，都要到被驱动表上进行一次全表遍历，找到对应的数据。

join buffer的作用

就是针对被驱动表的连接字段没有索引的情况下需要进行全表扫描，所以引入了join buffer内存缓冲区来对这个全表扫描过程进行优化。