关于java:MySQL执行计划explain

本文曾经收录到 github 仓库，仓库用于分享 Java 相干常识总结，包含 Java 根底、MySQL、Springboot、mybatis、Redis、rabbitMQ 等等，欢送大家提 pr 和 star！

github 地址：https://github.com/Tyson0314/…

gitlab 地址：https://gitee.com/tysondai/Ja…

简介

本文次要讲述如何通过 explain 命令获取 select 语句的执行打算，通过 explain 能够晓得 select 语句以下信息：

• 表的加载程序
• sql 的查问类型
• 可能用到哪些索引，实际上用到哪些索引
• 读取的行数
• …

explain 执行打算蕴含字段信息如下：别离是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12 个字段。

通过 explain extended + show warnings 能够在本来 explain 的根底上额定提供一些查问优化的信息，失去优化当前的可能的查问语句（不肯定是最终优化的后果）。

先搭建测试环境：

CREATE TABLE `blog` (
  `blog_id` int NOT NULL AUTO_INCREMENT COMMENT '惟一博文 id-- 主键',
  `blog_title` varchar(255) NOT NULL COMMENT '博文题目',
  `blog_body` text NOT NULL COMMENT '博文内容',
  `blog_time` datetime NOT NULL COMMENT '博文公布工夫',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `blog_state` int NOT NULL COMMENT '博文状态 --0 删除 1 失常',
  `user_id` int NOT NULL COMMENT '用户 id',
  PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户惟一 id-- 主键',
  `user_name` varchar(30) NOT NULL COMMENT '用户名 -- 不能反复',
  `user_password` varchar(255) NOT NULL COMMENT '用户明码',
  PRIMARY KEY (`user_id`),
  KEY `name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

CREATE TABLE `discuss` (
  `discuss_id` int NOT NULL AUTO_INCREMENT COMMENT '评论惟一 id',
  `discuss_body` varchar(255) NOT NULL COMMENT '评论内容',
  `discuss_time` datetime NOT NULL COMMENT '评论工夫',
  `user_id` int NOT NULL COMMENT '用户 id',
  `blog_id` int NOT NULL COMMENT '博文 id',
  PRIMARY KEY (`discuss_id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8

id

示意查问中执行 select 子句或者操作表的程序，id的值越大，代表优先级越高，越先执行。

explain select discuss_body 
from discuss 
where blog_id = (
    select blog_id from blog where user_id = (select user_id from user where user_name = 'admin'));

三个表顺次嵌套，发现最里层的子查问 id最大，最先执行。

select_type

示意 select 查问的类型，次要是用于辨别各种简单的查问，例如：一般查问 、 联结查问 、 子查问 等。

1. SIMPLE：示意最简略的 select 查问语句，在查问中不蕴含子查问或者交并差集等操作。
2. PRIMARY：查问中最外层的 SELECT（存在子查问的外层的表操作为 PRIMARY）。
3. SUBQUERY：子查问中首个 SELECT。
4. DERIVED：被驱动的 SELECT 子查问（子查问位于 FROM 子句）。
5. UNION：在 SELECT 之后应用了 UNION。

table

查问的表名，并不一定是实在存在的表，有别名显示别名，也可能为长期表。当 from 子句中有子查问时，table 列是 <derivenN>的格局，示意以后查问依赖 id 为 N 的查问，会先执行 id 为 N 的查问。

partitions

查问时匹配到的分区信息，对于非分区表值为 NULL，当查问的是分区表时，partitions 显示分区表命中的分区状况。

type

查问应用了何种类型，它在 SQL优化中是一个十分重要的指标。

system

当表仅有一行记录时（零碎表），数据量很少，往往不须要进行磁盘 IO，速度十分快。比方，Mysql 零碎表 proxies_priv 在 Mysql 服务启动时候曾经加载在内存中，对这个表进行查问不须要进行磁盘 IO。

const

单表操作的时候，查问应用了主键或者惟一索引。

eq_ref

多表关联 查问的时候，主键和惟一索引作为关联条件。如下图的 sql，对于 user 表（外循环）的每一行，user_role 表（内循环）只有一行满足 join 条件，只有查找到这行记录，就会跳出内循环，持续外循环的下一轮查问。

ref

查找条件列应用了索引而且不为主键和惟一索引。尽管应用了索引，但该索引列的值并不惟一，这样即便应用索引查找到了第一条数据，依然不能进行，要在目标值左近进行小范畴扫描。但它的益处是不须要扫全表，因为索引是有序的，即使有反复值，也是在一个十分小的范畴内做扫描。

ref_or_null

相似 ref，会额定搜寻蕴含 NULL 值的行。

index_merge

应用了索引合并优化办法，查问应用了两个以上的索引。新建 comment 表，id 为主键，value_id 为非惟一索引，执行explain select content from comment where value_id = 1181000 and id > 1000;，执行结果显示查问同时应用了 id 和 value_id 索引，type 列的值为 index_merge。

range

有范畴的索引扫描，绝对于 index 的全索引扫描，它有范畴限度，因而要优于 index。像 between、and、’>’、'<‘、in 和 or 都是范畴索引扫描。

index

index 包含 select 索引列，order by 主键两种状况。

1. order by 主键。这种状况会依照索引程序全表扫描数据，拿到的数据是依照主键排好序的，不须要额定进行排序。

2. select 索引列。type 为 index，而且 extra 字段为 using index，也称这种状况为索引笼罩。所须要取的数据都在索引列，无需回表查问。

all

全表扫描，查问没有用到索引，性能最差。

possible_keys

此次查问中可能选用的索引。但这个索引并不定一会是最终查问数据时所被用到的索引。

key

此次查问中确切应用到的索引。

rows

估算要找到所需的记录，须要读取的行数。评估 SQL 性能的一个比拟重要的数据，mysql 须要扫描的行数，很直观的显示 SQL 性能的好坏，个别状况下 rows 值越小越好。

filtered

存储引擎返回的数据在通过过滤后，剩下满足条件的记录数量的比例。

extra

示意额定的信息阐明。为了不便测试，这里新建两张表。

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `order_status` tinyint DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_order_id_createdate` (`user_id`,`order_id`,`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8

CREATE TABLE `t_orderdetail` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` int DEFAULT NULL,
  `product_name` varchar(100) DEFAULT NULL,
  `cnt` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_orderid_productname` (`order_id`,`product_name`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8

using where

查问的列未被索引笼罩，where 筛选条件非索引的前导列。对存储引擎返回的后果进行过滤（Post-filter，后过滤），个别产生在 MySQL 服务器，而不是存储引擎层。

using index

查问的列被索引笼罩，并且 where 筛选条件合乎最左前缀准则，通过 索引查找 就能间接找到符合条件的数据，不须要回表查问数据。

Using where&Using index

查问的列被索引笼罩，但无奈通过索引查找找到符合条件的数据，不过能够通过 索引扫描 找到符合条件的数据，也不须要回表查问数据。

包含两种状况：

• where 筛选条件不合乎最左前缀准则

• where 筛选条件是索引列前导列的一个范畴

null

查问的列未被索引笼罩，并且 where 筛选条件是索引的前导列，也就是用到了索引，然而局部字段未被索引笼罩，必须回表查问这些字段，Extra 中为 NULL。

using index condition

索引下推（index condition pushdown，ICP），先应用 where 条件过滤索引，过滤完索引后找到所有合乎索引条件的数据行，随后用 WHERE 子句中的其余条件去过滤这些数据行。

不应用 ICP 的状况（set optimizer_switch='index_condition_pushdown=off'），如下图，在步骤 4 中，没有应用 where 条件过滤索引：

应用 ICP 的状况（set optimizer_switch='index_condition_pushdown=on'）：

上面的例子应用了 ICP：

explain select user_id, order_id, order_status  
from t_order where user_id > 1 and user_id < 5\G;

关掉 ICP 之后（set optimizer_switch='index_condition_pushdown=off'），能够看到 extra 列为 using where，不会应用索引下推。

索引下推参考链接：索引下推例子 | 索引下推图解 | 索引下推优化

using temporary

应用了长期表保留两头后果，常见于 order by 和 group by 中。典型的，当 group by 和 order by 同时存在，且作用于不同的字段时，就会建设长期表，以便计算出最终的后果集。

filesort

文件排序。示意无奈利用索引实现排序操作，以下状况会导致 filesort：

• order by 的字段不是索引字段
• select 查问字段不全是索引字段
• select 查问字段都是索引字段，然而 order by 字段和索引字段的程序不统一

using join buffer

Block Nested Loop，须要进行嵌套循环计算。两个关联表 join，关联字段均未建设索引，就会呈现这种状况。比方内层和外层的 type 均为 ALL，rows 均为 4，须要循环进行 4 * 4 次计算。常见的优化计划是，在关联字段上增加索引，防止每次嵌套循环计算。

本文参考了一些优良的博客，感兴趣的能够理解下：

• Explain 执行打算

码字不易，如果本文写的不错，能够点个赞，让我晓得，反对我写出更好的文章！