介绍

explain是mysql提供的一个优化sql的命令,在5.6以及以后的版本中,
除select,如insert,update和delete均可以使用explain查看执行计划，
从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈,
本次使用的mysql 版本为

@@version
5.6.38

但是为与5.6之后版本关联起来，所以部分内容可能还会含有其他版本的内容

使用方式

在sql语句前面增加 explain 即可,5.6.x版本显示部分字段需要添加部分关键字

explain SELECT * FROM TABLE_NAME

explain extended SELECT * FROM TABLE_NAME

准备

1.需要分析的sql

2.数据库工具或直接连接数据库

这里我们创建表来查看

CREATE TABLE `test_table_3` (
  `id`   BIGINT(63)  NOT NULL AUTO_INCREMENT,
  `ball_name` VARCHAR(125) NOT NULL DEFAULT '',
  `size`  INT(115)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ball_name_index` (`ball_name`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO test_table_3 (ball_name, size) VALUES ('1', 100);
INSERT INTO test_table_3 (ball_name, size) VALUES ('2', 200);
INSERT INTO test_table_3 (ball_name, size) VALUES ('3', 300);
INSERT INTO test_table_3 (ball_name, size) VALUES ('4', 400);
INSERT INTO test_table_3 (ball_name, size) VALUES ('5', 500);
INSERT INTO test_table_3 (ball_name, size) VALUES ('6', 600);
INSERT INTO test_table_3 (ball_name, size) VALUES ('7', 700);
INSERT INTO test_table_3 (ball_name, size) VALUES ('8', 800);
INSERT INTO test_table_3 (ball_name, size) VALUES ('9', 900);
INSERT INTO test_table_3 (ball_name, size) VALUES ('10', 1000);


CREATE TABLE `test_table_4` (
  `id`           BIGINT(15)  NOT NULL AUTO_INCREMENT,
  `ball_id`      BIGINT(15)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`ball_id`, `product_name`, `productor`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO test_table_4 (ball_id, product_name, productor) VALUES (1, 'A', '耐克');
INSERT INTO test_table_4 (ball_id, product_name, productor) VALUES (1, 'B', '李宁');
INSERT INTO test_table_4 (ball_id, product_name, productor) VALUES (1, 'A', '安踏');
INSERT INTO test_table_4 (ball_id, product_name, productor) VALUES (2, 'A', '耐克');
INSERT INTO test_table_4 (ball_id, product_name, productor) VALUES (2, 'C', '李宁');
INSERT INTO test_table_4 (ball_id, product_name, productor) VALUES (3, 'D', '361');
INSERT INTO test_table_4 (ball_id, product_name, productor) VALUES (4, 'A', '耐克');
INSERT INTO test_table_4 (ball_id, product_name, productor) VALUES (6, 'A', '耐克');
INSERT INTO test_table_4 (ball_id, product_name, productor) VALUES (9, 'E', '特步');

介绍explain查询出的每项的含义

执行sql

explain select * from test_table_3 where id = 2

列介绍

各列的含义如下:

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

select_type: SELECT 查询的类型.

table: 查询的是哪个表

partitions: 匹配的分区

type: join 类型

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引.

ref: 哪个字段或常数与 key 一起被使用

rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

filtered: 表示此查询条件所过滤的数据的百分比

extra: 额外的信息

每列数值分析

id:由数据库分配的id,无特定的某些数值

1. ID如果相同，可以认为是一组，从上往下顺序执行
2. ID不同,其中ID越大，优先级越高，越早执行
3. ID列为null的就表是这是一个结果集，不需要使用它来进行查询。

select_type

表示了查询的类型,主要用于区别普通查询，联合查询，子查询等的复杂查询。
它的值有:

SIMPLE, 表示此查询不包含 UNION 查询或子查询

PRIMARY, 表示此查询是最外层的查询

UNION, 表示此查询是 UNION 的第二或随后的查询

DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询

UNION RESULT, UNION 的结果

SUBQUERY, 子查询中的第一个 SELECT

DEPENDENT SUBQUERY(会严重消耗性能): 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）

MATERIALIZED：被物化的子查询

UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）

UNCACHEABLE UNION：UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

table

本次sql使用的表

1.如果查询使用了别名,那么这里显示的是别名
2.如果不涉及对数据表的操作，那么这显示为null。
3.如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id,
表示结果来自于这个查询产生。
4.如果是尖括号括起来的<union M,N>，与<derived N>类似,也是一个临时表，
表示这个结果来自于union查询的id为M,N的结果集。

type

提供了判断查询是否高效的重要依据. 通过此字段, 我们判断此次查询是全表扫描还是索引扫描

通常来说, 不同的 type 类型的性能关系如下:

system > const > eq_ref > ref > fulltext > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > ALL

ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

除了all之外，其他的type都可以使用到索引，
除了index_merge之外，其他的type只可以用到一个索引

常用的值有:

const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.

使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。
因为仅有一行，在这行的列值可被优化器认为是常数.
onst表很快，因为它们只读取一次！
其他数据库也叫做唯一索引扫描

        system: 表中只有一条数据. 这个类型是特殊的 const 类型.
        表中只有一行数据或者是空表，且只能用于myisam和memory表。
        如果是Innodb引擎表，type列在这个情况通常都是all或者index

eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高

出现在要连接多个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，
且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。

ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询. 

不像eq_ref那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现。
常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现。
总之，返回数据不唯一的等值查找就可能出现。   
ref可以用于使用=或<=>操作符的带索引的列。

fulltext
全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价，优先选择使用全文索引

ref_or_null
该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。
在解决子查询中经常使用该联接类型的优化。实际用的不多。5.7版本貌似已经废弃了,

unique_subquery
该类型替换了下面形式的IN子查询的ref：
value IN (SELECT primary_key FROM single_table WHERE some_expr)
子查询返回不重复值唯一值.
unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。


index_subquery
该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：
value IN (SELECT key_column FROM single_table WHERE some_expr)

range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个

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

index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index
通常比ALL快,因为索引文件通常比数据文件小。
可以使用索引排序或者分组的查询以及覆盖索引查询。

ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 
如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.在全表扫描时, possible_keys 和 key 字段都是 NULL, 表示没有使用到索引, 
并且 rows 十分巨大, 因此整个查询效率是十分低下的.
全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
需要避免的类型,效率最低。可以根据查询条件新增或者调整索引。

possible_keys

表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

如果该列是NULL，则没有相关的索引。
在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。

key

此字段是 MySQL 在当前查询时所真正使用到的索引.

select_type为index_merge时，这里可能出现两个以上的索引。
如果没有选择索引，键是NULL。
要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:

字符串

    char(n): n 字节长度

    varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.

数值类型:

    TINYINT: 1字节

    SMALLINT: 2字节

    MEDIUMINT: 3字节

    INT: 4字节

    BIGINT: 8字节

时间类型

    DATE: 3字节

    TIMESTAMP: 4字节

    DATETIME: 8字节

字段属性: NULL 属性占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

ref

如果是使用的常数等值查询,这里会显示const
如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段
如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示,包含有关MySQL如何解析查询的其他信息。
此字段能够给出让我们深入理解执行计划进一步的细节信息，比如是否使用ICP，MRR等
常见的有以下几种内容:

Using filesort
排序时无法使用到索引，常见于order by和group by语句中,
此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，
然后排序关键字并按顺序检索行。
出现这个,查询就需要优化了,MYSQL需要进行额外的步骤来对返回的行排序。
它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行.
这并不代表着真的使用了文件排序！

当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 
一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

Using index
查询无需回表，直接通过索引就可以获取查询的数据,不用扫描表数据文件,即覆盖索引！

Using temporary
表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，
执行计划中看不出来，需要查看status变量，
used_tmp_table，used_tmp_disk_table才能看出来。
发生这种情况一般都是需要进行优化的
-般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

Using where
表示查询返回的记录并不是所有的都满足查询条件,需要在server层进行过滤。
查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，
然后server层根据检查条件进行过滤再返回真正符合查询的数据。
5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层,不符合检查条件和限制条件的数据，直接不读取,
这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition

Using sort_union(...)/Using union(...)/Using intersect(...)

using intersect：表示使用and多个索引查询时,从处理结果获取交集
using union：表示使用or多个索引查询时,从处理结果获取并集
using sort_union和using sort_intersection：与前面两个对应的类似，
只是他们是出现在用and和or查询信息量大时,先查询主键,然后进行排序合并后,才能读取记录并返回。

using intersect：表示使用and多个索引查询时,从处理结果获取交集
using union：表示使用or多个索引查询时,从处理结果获取并集
using sort_union和using sort_intersection：与前面两个对应的类似，
只是他们是出现在用and和or查询信息量大时,先查询主键,然后进行排序合并后,才能读取记录并返回。

Using index for group-by
表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。
 注意,这里是非主键！

using join buffer（block nested loop），using join buffer（batched key accss）
5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。

firstmatch(tb_name)
5.6.x开始引入的优化子查询的新特性之一,常见于where字句含有in()类型的子查询。
如果内表的数据量比较大，就可能出现这个

loosescan(m..n)
5.6.x之后引入的优化子查询的新特性之一,在in()类型的子查询中，
子查询返回的可能有重复记录时，就可能出现这个

impossible where:where子句的值总是false，不能用来获取任何元组

select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

###
版本5.7以前，该项是解释分区显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

filtered

使用explain extended时会出现这个列。
5.7之后的版本默认就有这个字段。不需要使用explain extended了。
这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例,
注意是百分比，不是具体记录数。

参考资料

https://blog.csdn.net/bingoxubin/article/details/78720976

关闭重启jenkins