Explain:你见过这样的Sql吗?

2022 年 10 月 13 日
笔记
JAVA, MySQL, spring

上一篇我们讲到Mysql索引底层逻辑，为了了解后续sql知识，我们还是需要先学习一下相关“工具”得使用

一、Explain介绍

EXPLAIN是MySQl必不可少的一个分析工具，主要用来测试sql语句的性能及对sql语句的优化，或者说模拟优化器执行SQL语句。在select语句之前增加explain关键字，执行后MySQL就会返回执行计划的信息，而不是执行sql。

注意：如果from中包含子查询，仍会执行子查询，将结果放入到临时表中

Explain的用法还是很简单的，类似一个关键字，无需记住什么语法相关的东西，我们主要来看他的输出，接下来我们看一下他的常见输出并分情况进行讨论：

首先我们创建三张表并插入一些相关数据

-- ----------------------------

-- Table structure for user

-- ----------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

  `id` int(11) NOT NULL,

  `name` varchar(45) DEFAULT NULL,

  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Records of user

-- ----------------------------

INSERT INTO `user` VALUES ('1', '小明', '2022-09-15 15:52:18', '2022-09-13 15:52:21');

INSERT INTO `user` VALUES ('2', '小红', '2022-09-14 15:52:35', '2022-09-06 15:52:41');

INSERT INTO `user` VALUES ('3', '小可', '2022-09-15 15:52:55', '2022-09-15 15:52:58');

INSERT INTO `user` VALUES ('4', '张三', '2022-09-13 15:53:13', '2022-09-14 15:53:17');

INSERT INTO `user` VALUES ('5', '李四', '2022-09-15 15:53:35', '2022-09-15 15:53:37');

INSERT INTO `user` VALUES ('6', '王五', '2022-09-15 15:53:47', '2022-09-15 15:53:49');

INSERT INTO `user` VALUES ('7', '小小', '2022-09-15 15:54:06', '2022-09-15 15:54:08');

-- ----------------------------

-- Table structure for address

-- ----------------------------

DROP TABLE IF EXISTS `address`;

CREATE TABLE `address` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of address

-- ----------------------------

INSERT INTO `address` VALUES ('2', '上海');

INSERT INTO `address` VALUES ('1', '北京');

INSERT INTO `address` VALUES ('3', '北京');

INSERT INTO `address` VALUES ('5', '南京');

INSERT INTO `address` VALUES ('6', '武汉');

INSERT INTO `address` VALUES ('4', '深圳');

INSERT INTO `address` VALUES ('7', '长沙');

-- ----------------------------

-- Table structure for user_address

-- ----------------------------

DROP TABLE IF EXISTS `user_address`;

CREATE TABLE `user_address` (

  `id` int(11) NOT NULL,

  `address_id` int(11) NOT NULL,

  `user_id` int(11) NOT NULL,

  `remark` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_user_address_id` (`address_id`,`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

首先来分析一下我们的表结构user表我们只有一个聚集索引，其实就是主键索引，那他的索引结构就只是id列，如下图所示：

address表不单单只有我们的聚集索引，也添加了一个二级索引，索引结构如下图所示：

user_address也不仅只有聚集索引，也有一个联合索引,相对应的列分别是address_id和user_id，索引结构如下图所示：

二、Explain中的列

1、id列

id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。

id列越大执行优先级越高，id相同则从上向下执行，id为null最后执行。

2、select_type列

2.1、Simple:简单查询，查询不包含子查询和union

EXPLAIN SELECT * from `user` WHERE id = 1;

2.2、Primary:复杂查询中最外层的select

2.3、Subquery:包含在select中的子查询（不在from子句中）

2.4、Derived：包含在from子句中的子查询。Mysql会将结果存放到一个临时表中，也成为派生表（derived的英文含义）

EXPLAIN SELECT (SELECT 1 FROM `user` WHERE id = 1) FROM (SELECT * FROM address WHERE id =1) der;

注意：这里要先关闭一下mysql5.7新特性对衍生表的合并优化

set session optimizer_switch='derived_merge=off';

3、Table

对应正在访问的哪一个表，显示的是表明或者是别命，可能是临时表或者union合并结果集如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别命

表明是derived的形式，表明使用了id为N的查询产生的衍生表，如下图所示：

derived后面的id号为3，表明使用id为3的这个查询产生的衍生表，也就是（SELECT * FROM address WHERE id =1）这个查询语句结果集所在的的临时表

当有union result的时候，表名是union n1,n2等形式， n1,n2表示参与union的id

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。例如：在索引列中取最小值，可以单独查找索引来完成，不需要在执行时访问表

上面的语句我们是通过主键id的方式来查找的，如果看过我们上一篇博客的读者就能够明白，此时直接查索引就可以了，找到最小的，无需查表。

4、possible_keys

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

5、key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

如果没有使用索引，则该列是null，如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用force index、ignore index。

基于4、5两个，有可能出现这种情况：possible_keys有值，key没有值，这种情况下有可能是因为分析的时候需要用索引，真正执行的时候发现不走索引的化还会快一点。

6、Type

这一列b表示关联类型或者访问类型，即Mysql决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：

system>const>eq_ref>ref>range>index>ALL

一般来说，的保证查询达到range级别，最好达到ref。

1）NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。例如：在索引列中取最小值，可以单独查找索引来完成，不需要在执行时访问表

2）system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

3）const：这个表至多有一个匹配行，

Const为常量的意思，他可能想要表达出查询的效率非常高，跟查一个常量式的，用我们的唯一索引，或者主键的时候，因为无重复值，所以查询效率非常高

4）eq_ref:多表连接中使用primary key或者 unique key作为关联条件，使用唯一性索引进行数据查找，也就是被关联表上的关联列走的是主键或者唯一索引这可能是在const之外最好的联接类型了，简单的select查询不会出现这种type

5）ref：使用了非唯一性索引进行数据的查找或者非唯一性索引的部分前缀

5.1)简单查询(非唯一索引)

5.2）关联表查询,idx_user_address_id是address_id和user_id的联合索引，这里使用到了user_address的左边前缀address_id部分

6）ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式,简单得来说就是二级索引等值查询也能搜索到值为null得行。（注意，此时在表中添加了一行为null得数据）

7）index_merge：在查询过程中需要多个索引组合使用

8）range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, 或者 IN()

9）index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比All快一些

那么这里提出一个问题，address表中其实有两个索引，一个是主键索引，一个是二级索引idx_name,那么为什么这里会使用二级索引而不是主键索引呢？

打开address表你会发现。。Address只有两个字段一个id，一个name，而idx_name这个索引树中就包含了name和id，而要查的字段都存在于idx_name索引树中，mysql有一个这样的优化原则，凡是我查找结果集的分析我查找出来，如果这个结果集的几个字段在我们所有索引里面都有，他会优先选择二级索引去查，因为二级索引小不管是主键索引还是二级索引都是从叶子节点的第一个开始找，遍历到最后一个

11）all:全表扫描，扫描你的聚簇索引的所有叶子节点，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要增加索引来进行优化了。

7、key_len列

这一列显示了mysql表示索引中使用的字节数，通过这个值可以算出具体使用了索引中的哪些列，举例来说，user_address的联合索引idx_user_address_id由address_id和user_id两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列address_id来执行索引查询，在不损失精度的情况下长度越短越好。

从上图可以看出这个查询用到了idx_user_address_id这个联合索引，这个索引有两个字段，但我们这个查询语句其实就用到了address_id，key_len是4，address_id是int类型，也就是4字节当两个都用到的时候，相应的key_len也就变成了8

Key_len计算规则如下:

字符串：

　　　　　　Char(n):n字节长度

　　　　　　Varchar(n)：如果是utf-8,则长度3n+2字节，加的2字节用来存储字符串长度

数值类型：

　　　　　　Tinyint:1字节

　　　　　　Smallint:2字节

　　　　　　Int:4字节

　　　　　　Bigint：8字节

时间类型：

　　　　　　Date：3字节

　　　　　　Timestamp：4字节

　　　　　　Datatime：8字节

如果字段允许为null，需要1字节记录是否为null

索引的最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来左索引

8、ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const(常量)，字段名(例如：address.id)

9、rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里面的行数，她只是一个预估值

10、Extra列

这一列展示的是额外信息。常见的重要值如下：

1）using index：使用覆盖索引

覆盖索引定义:mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般说用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键索引树里获取其他字段值

2）using where:使用where语句来处理结果，并且查询的列未被索引覆盖

3）using index condition:查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

4）using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化

user.name没有索引，此时创建了临时表来distinct

address.name建立了idx_name索引，此时查询时extras是using index,没有用临时表

5）using filesort：将用外部排序而不是索引排序，索引较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的

actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录，

address.name建立了idx_name索引，此时查询时extra是using index

6）Select tables optimized away：使用某些聚合函数(比如max、min)来访问存在索引的某个字段是

附：

1、派生表

派生表，是用于存储子查询产生的结果的临时表，这个子查询特指 FROM 子句里的子查询，如果是出现在其它地方的子查询，就不叫这个名字了，所以本质上来说，派生表也是临时表。

2、物化表

物化表，也是用于存储子查询产生的结果的临时表，这个子查询特指 WHERE 子句中查询条件里的子查询。

前导列：

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

3、dual表

Dual表其实就是一个虚表，你可以在没有表的情况下指定这个虚拟的表名。

4、int(1)、int(2)、int(3)…int(10)有什么区别？

不知道大家对于上面ken_len列的计算是否有疑问，为什么像int这类数据类型就是4字节，而不是int(num)中的num字节接下来就给大家解释一下。

Mysql中int是占4个字节,那么对于无符号的int，最大值就是2^32-1 = 4294967295

int后面的数字不能表示字段的长度，int(num)一般加上zerofill才有效果，它能够实现当该字段不足num位时补0的效果。

下面我们给出一个例子：

首先我们创建一个测试表

CREATE TABLE `intTest` (
  `id` int(1) ZEROFILL UNSIGNED NOT NULL,
    `test` int(1) UNSIGNED ZEROFILL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

测试表中id列，test列是一个无符号整型，且设置的是int(1)，那么他是不是意味着不能插入4294967295这个4字节的最大数字，接下来我们就插入4294967295试一下：

[SQL]INSERT INTO `inttest` VALUES(4294967295,4294967295);

受影响的行: 1

时间: 0.013s

可以看到成功插入，没有报错，也就说明int(1)并没有限制只有1个字节，接下来我们把test字段的int中的位数改成5，id列不变并都插入数字1看一下有什么效果，

可以看到test列为int(5)，所以在不满足5位的时候会在前面补零，因为我们加上了zerofill属性，而id列我们设置了int(1)他就不会补零。这里面说明一下；为什么用命令行看：
因为在navicat中他是不显示这个零填充（小编的navicat是显示不出来的）

这里面还有一个小细节：其实我们在给某一列增加zerofill属性的时候，mysql会自动增加一个unsigned属性，我们可以看一下：

我们新增加一列test1:

ALTER TABLE `intTest` add COLUMN `test1` int(3) ZEROFILL;

向test1中插入负数

INSERT INTO `inttest` VALUES(2,2,-2);

此时会发生报错；因为小编的mysql处于严格模式下

SET sql_mode ="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

修改模式，再次插入

可以看到，插入负数的时候会存储为0,mysql会自动设置该列为无符号整数。

zerofill默认为int(10)，int默认为int(11)

下面我们做一个示例：

ALTER TABLE `intTest` add COLUMN `test2` int ZEROFILL;
ALTER TABLE `intTest` add COLUMN `test3` int;

上面就可以证明这一点。

不仅仅是int类型，上面提到的整数类型皆是如此。

Tags: JAVA MySQL spring