关于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的根底上额定提供一些查问优化的信息，失去优化当前的可能的查问语句（不肯定是最终优化的后果）。

先搭建测试环境：

<code class="sql">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的值越大，代表优先级越高，越先执行。

<code class="mysql">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

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

<code class="sql">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：

<code class="sql">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执行打算

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

喜欢 (0)赏

[搞代码]

分享 (0)