MySQL的EXPLAIN参数的简单解析

水木泛清華

已于 2023-03-27 08:49:18 修改

阅读量1.3k

点赞数

文章标签： mysql 数据库 java

于 2023-03-24 20:51:41 首次发布

本文链接：https://blog.csdn.net/One_Punch_Devil/article/details/129755580

版权

explain的参数主要有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra

1.id，查询语句的序号

1)id值越大，证明优先级越高，执行时会优先执行

2)id值相同时，由上至下顺序执行

3）同时存在相同序号和不同序号时，先执行优先级高的，然后相同序号的自上而下顺序执行

注：一般序号增加情况来自与语句中的子查询、UNION、UNION ALL

2.select_type,查询操作的执行类型

1）SIMPLE,简单查询，查询语句中不包含子查询、UNION、UNION ALL

语句：

EXPLAIN select count(0) from (SELECT ENT_NAME
FROM portal_task 
WHERE EXISTS (SELECT SERIAL_NO FROM portal_task WHERE SERIAL_NO=SERIAL_NO)) tmp_count

结果：

2）PRIMARY，查询中出现子查询时，最外层的查询会被标记为PRIMARY

语句：

EXPLAIN
( select '2022-09-30 23:59:59' AS ESTDATE, COUNT(*) AS SUM from dp_baseinfo
 where ESTDATE <= '2022-09-30 23:59:59' and ENT_TYPE='个体工商户' ) 
UNION 
( select '2022-10-31 23:59:59' AS ESTDATE, COUNT(*) AS SUM from dp_baseinfo 
where ESTDATE <= '2022-10-31 23:59:59' and ENT_TYPE='个体工商户' )

结果：

3）SUBQUERY，select、where中出现子查询，子查询会被标记为SUBQUERY

语句：

EXPLAIN SELECT ENT_NAME FROM portal_task 
WHERE  
SERIAL_NO=(SELECT SERIAL_NO FROM portal_task WHERE SERIAL_NO="1111")

结果：

4) UNION,当使用UNION、UNION ALL时，若除第一条select语句之外的其他语句出现在UNION、UNION ALL之后，就会别标记为UNION

UNION语句：

EXPLAIN
( select '2022-09-30 23:59:59' AS ESTDATE, COUNT(*) AS SUM from dp_baseinfo 
where ESTDATE <= '2022-09-30 23:59:59' and ENT_TYPE='个体工商户' ) 
UNION 
( select '2022-10-31 23:59:59' AS ESTDATE, COUNT(*) AS SUM from dp_baseinfo 
where ESTDATE <= '2022-10-31 23:59:59' and ENT_TYPE='个体工商户' ) 
UNION 
( select '2022-11-30 23:59:59' AS ESTDATE, COUNT(*) AS SUM from dp_baseinfo 
where ESTDATE <= '2022-11-30 23:59:59' and ENT_TYPE='个体工商户' )

结果：

UNION ALL语句：

EXPLAIN
( select '2022-09-30 23:59:59' AS ESTDATE, COUNT(*) AS SUM from dp_baseinfo 
where ESTDATE <= '2022-09-30 23:59:59' and ENT_TYPE='个体工商户' ) 
UNION ALL 
( select '2022-10-31 23:59:59' AS ESTDATE, COUNT(*) AS SUM from dp_baseinfo 
where ESTDATE <= '2022-10-31 23:59:59' and ENT_TYPE='个体工商户' ) 
UNION ALL 
( select '2022-11-30 23:59:59' AS ESTDATE, COUNT(*) AS SUM from dp_baseinfo 
where ESTDATE <= '2022-11-30 23:59:59' and ENT_TYPE='个体工商户' )

结果：

5）UNION RESULT,当使用UNION时会产生一个临时表用于存放UNION的数据，这个临时表会被标记为UNION RESULT

注：UNION会对取并集的数据进行去重和排序操作，所以只有在使用UNION时才会产生临时表用；使用UNION ALL时，数据会直接被合并，不会对数据进行去重和排序，所以不会产生临时表，因此在并集并不需要去重和排序的情况下，使用UNION ALL比UNION更有效率

3.table，查询语句操作的表名

4.partitions，分区情况

5.type，查询的种类

一般情况下type的优劣顺序

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

1）system:相当于系统表，只有一行数据，数据库引擎需要是MyISAM引擎

2）const：表示通过索引一次就找到

语句：

EXPLAIN SELECT ENT_NAME FROM portal_task WHERE  SERIAL_NO="1111"

结果：

出现情况：使用主键索引或唯一索引；条件部分为固定的值

3）eq_ref:查询某个值的所有行时，只有一条记录符合条件

4）ref：查询某个值的所有行，查询结果不唯一，且不存在NULL

语句：

EXPLAIN ( select '农、林、牧、渔业' AS INDUSTRYPHY, COUNT(*) AS SUM from dp_baseinfo 
where INDUSTRYPHY = '农、林、牧、渔业' and ENT_TYPE='企业' and DISTINCT_CN='岚山区' )

结果：

5）fulltext：使用索引类型为全文索引

6）ref_or_null：查询某个值的所有行，查询结果不唯一，存在NULL

7）index_merge：索引合并优化

8）unique_subquery：in（子查询）中，发生select 唯一属性列替换时

9）index_subquery：in（子查询）中，发生select 非唯一属性列替换时

10）range：只检索某个范围中的值时，存在范围判断时比较常出现

语句：

EXPLAIN( select '2022-08-31 23:59:59' AS ESTDATE, COUNT(*) AS SUM from dp_baseinfo 
where ESTDATE <= '2022-08-31 23:59:59' )

结果：

11）index：扫描全部索引，

扫描索引要比全表扫描快，因为索引文件一般比数据文件要小

语句：

EXPLAIN select count(0) from 
(SELECT ENT_NAME,INFOFLOW_NAME,APPLY_SUBJECT_NAME,BIZ_STATE_NAME,CUR_ACT_NAME,EDIT_URL FROM portal_task 
WHERE 
EXISTS (SELECT SERIAL_NO FROM portal_task WHERE SERIAL_NO=SERIAL_NO)) tmp_count

结果：

12）ALL：进行了全表扫描

SQL优化情况：一般情况优化到range，如果可以最好优化到ref级，具体还是要看sql执行时对空间资源和时间资源的需求，毕竟不是优化到某一级别执行时间就一定短或者占用空间资源就一定少。

6.possible_keys（可能用到的索引）和 key（实际使用的索引）

索引：

语句：

EXPLAIN( select '2022-08-31 23:59:59' AS ESTDATE, COUNT(*) AS SUM from dp_baseinfo
 where ESTDATE <= '2022-08-31 23:59:59' and INDUSTRYPHY='采矿业' )

结果：

7.key_len:索引允许使用的最大长度，并不是使用的实际长度

8.ref：索引使用情况，const常数代表索引使用正常

9.rows：代表查询优化器最终查询的行数

10.filtered：代表查询的过滤百分比，一般来说值越大越优

11.Extra：执行时所使用的查询方法（下面只列举比较常见的）

1）firstmatch：只选用内表的第一条与外表匹配的记录

2）using temporary：使用了临时表来进行数据排序

3）using index：使用了覆盖索引，表示所需数据只从索引中就全部获取，即索引将所需查询的列全部覆盖，正常情况使用覆盖索引会大大减少查询时间

4）using where：在查找使用索引的情况下，需要回表去查询所需的数据

5）using index condition：查找使用了索引，不需要回表查询，因为要过滤的字段在索引中

6）using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据（联合索引很容易出现这样的结果）

7）using join buffer：使用链接缓存