MySQL explain

  • ​MySQL explain​
  • ​​explain作用​​
  • ​​explain的执行效果​​
  • ​​explain包含的字段​​
  • ​详细内容​
  • ​​id字段​​
  • ​​select_type字段​​
  • ​​type字段​​
  • ​​table字段​​
  • ​​possible_keys字段​​
  • ​​key字段​​
  • ​​key_len字段​​
  • ​​ref字段​​
  • ​​row字段​​
  • ​​partitions字段​​
  • ​​filtered字段​​
  • ​​Extra字段​​

explain关键字可以模拟MySQL优化器执行SQL语句,可以很好地分析SQL语句或表结构的性能瓶颈

2021年3月5日17:33:33

explain作用

  1. 表的读取顺序如何
  2. 数据读取操作有哪些操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间是如何引用
  6. 每张表有多少行被优化器查询

...

explain的执行效果

mysql> explain select * from tb_user where id = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_user
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)

explain包含的字段

  1. id // select查询的序列号,包含一组数字,表示查询中执行select字句或操作表的顺序
  2. select_type // 查询类型
  3. table // 正在访问哪个表
  4. partitions // 匹配的分区
  5. type // 访问的类型
  6. possible_keys // 显示可能应用在这张表中的索引,一个或多个,但不一定实际使用到
  7. key // 实际使用到的索引,如果为NULL,则没有使用索引
  8. key_len // 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,值越大效率越高
  9. ref // 显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引上的值
  10. rows // 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需读取的行数
  11. filtered // 查询的表行占表的百分比
  12. Extra // 包含不适合在其他列中显示但十分重要的额外信息

详细内容

id字段

1. id相同

explain select * from tb_subject sub,tb_user us,tb_user_subject usu where sub.id=usu.subject_id and us.id=usu.user_id and us.id=1;

2. id不同

​如果是子查询,id的序号会递增,id的值越大优先级越高,越先被执行​

读取顺序:tb_user > tb_user_subject

explain select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1);

3. id相同又不同

​id如果相同,则从上往下顺序执行。id不同,则id值越大,越先执行​

explain select * from tb_subject sub LEFT JOIN tb_user_subject usu on sub.id=usu.subject_id where usu.user_id=1 union all select * from tb_subject sub LEFT JOIN tb_user_subject usu on sub.id=usu.subject_id where usu.user_id=1;

select_type字段

1. SIMPLE

​简单查询,不包含子查询或union查询​

explain select * from tb_subject sub,tb_user us,tb_user_subject usu where sub.id=usu.subject_id and us.id=usu.user_id and us.id=1;

2. PRIMARY

​查询中若包含任何复杂的子查询,最外层查询则被标记为主查询PRIMARY​

explain select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1)

3. SUBQUERY

​在select或where中包含子查询​

explain select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1)

4. DERIVED

​在from列表中包含的子查询被标记为DERIVED​

explain select name from (select * from tb_user us where id=1) tmp;

5. UNION

​若第二个select出现在union之后,则被标记为UNION​

explain select * from tb_user where id=1 union select * from tb_user where id=2;

6. UNION RESULT

​从UNION表获取结果的select​

explain select * from tb_user where id=1 union select * from tb_user where id=2;

type字段

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL //最好到最差

备注:掌握以下10中常见的即可

NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

1. NLL

​MySQL能够在优化阶段分解查询语句,在执行阶段不用再访问表或索引​

explain select min(id) from tb_user;

2. system

​表中只有一行记录,这是const类型的特例,平时不太会出现,可以忽略​

没有实例

3. const

​表示通过索引一次就找到了,const用于比较primary key或unique索引,​

explain select * from tb_system where id=1;

4. eq_ref

​唯一性索引扫描,对于每个索引键,表中只有一条记录与之对应,常见于主键或唯一索引扫描​

explain select subject_id from tb_user_subject usu LEFT JOIN tb_subject su on usu.subject_id=su.id;

5. ref

​非唯一性索引扫描,返回匹配某个单独值得所有行​

explain select subject_id from tb_subject su LEFT JOIN tb_user_subject usu on usu.subject_id=su.id;

6. ref_or_null

​类似ref,但是可以搜索值为NULL的行​

explain select * from tb_user_subject usu where subject_id=1 or subject_id is null;

7. index_merge

​表示使用了索引合并的优化方法​

explain select * from tb_user_subject where id = 1 or subject_id='2';

8. range

​只检索给定范围的行,使用一个索引来选择行​

explain select * from tb_user where id BETWEEN 1 and 3;

9. index

​Full index Scan,Index与All区别:index只遍历索引树,通常比All快,因为索引文件通常比数据文件消息,也就是虽然all和index都是读全表,但index是从索引中读的,而all是从硬盘读的​

explain select id from tb_user;

10. ALL

​Full Table Scan,将遍历全表以找到匹配行​

explain select * from tb_user;

table字段

数据来自哪张表

possible_keys字段

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

key字段

实际使用到的索引,如果为NULL,则没有使用索引,查询中若使用了覆盖索引(查询的列刚好是索引),则该索引进出现在key列表

key_len字段

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确度的情况下,长度越长越好,key_len显示的值为索引字段最大的可能长度,并非实际使用长度,即key_len是根据定义计算而得,不是通过表内索引出

ref字段

显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值

row字段

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需读取的行数

partitions字段

匹配的分区

filtered字段

查询的表行占表的百分比

Extra字段

包含不适合在其他列显示但十分重要的额外信息

1. Using filesort

说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作成为 [文件排序]

explain select * from tb_user order by name;

2. Using temporary

​使用了临时表保存中间结果,MySQL在对结果排序时使用临时表,常见于排序order by和分组查询group by​

explain select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1) union all select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1);

3. Using index

​表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,​

explain select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1);

4. Using where

​使用了where条件​

5. Using join buffer

​使用了连接缓存​

explain select * from tb_subject sub,tb_user us,tb_user_subject usu;

6. impossible where

​where自居的值总是false,不能用来获取任何元组​

explain select * from tb_user where id=1 and id=3;

7. distinct

​一旦MySQL找到了与行相联合匹配的行,就不再搜索了​

explain select distinct(name) from tb_user us left join tb_user_subject sub on sub.user_id=us.id;

8. Select tables optimized away

​select操作已经优化到不能再优化了,MySQL没有遍历表或索引就返回数据了​

explain select min(id) from tb_user;

使用的数据表

/*
Navicat MySQL Data Transfer

Source Server : loca
Source Server Version : 50624
Source Host : localhost:3306
Source Database : explain-learn

Target Server Type : MYSQL
Target Server Version : 50624
File Encoding : 65001

Date: 2021-03-05 17:30:25
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_subject
-- ----------------------------
DROP TABLE IF EXISTS `tb_subject`;
CREATE TABLE `tb_subject` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject_name` varchar(255) DEFAULT NULL COMMENT '学科名',
`subject_teacher` varchar(255) DEFAULT NULL COMMENT '学科老师姓名',
`subject_code` varchar(255) DEFAULT NULL COMMENT '学科代码',
PRIMARY KEY (`id`),
KEY `subject_code` (`subject_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_subject
-- ----------------------------
INSERT INTO `tb_subject` VALUES ('1', '语文', '张三', '1000');
INSERT INTO `tb_subject` VALUES ('2', '数学', '李四', '2000');
INSERT INTO `tb_subject` VALUES ('3', '英语', '王五', '3000');
INSERT INTO `tb_subject` VALUES ('4', '物理', '赵六', '4000');
INSERT INTO `tb_subject` VALUES ('5', '化学', '田七', '5000');
INSERT INTO `tb_subject` VALUES ('6', '生物', '李八', '6000');

-- ----------------------------
-- Table structure for tb_system
-- ----------------------------
DROP TABLE IF EXISTS `tb_system`;
CREATE TABLE `tb_system` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`desc` varchar(255) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_system
-- ----------------------------
INSERT INTO `tb_system` VALUES ('1', '我只有一行');

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`name` varchar(255) DEFAULT NULL COMMENT '用户名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`user_no` varchar(11) DEFAULT NULL COMMENT '工号,唯一',
PRIMARY KEY (`id`),
UNIQUE KEY `user_no` (`user_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', 'henry', '18', '0001');
INSERT INTO `tb_user` VALUES ('2', 'lucy', '20', '0002');
INSERT INTO `tb_user` VALUES ('3', 'jack', '66', '0003');

-- ----------------------------
-- Table structure for tb_user_subject
-- ----------------------------
DROP TABLE IF EXISTS `tb_user_subject`;
CREATE TABLE `tb_user_subject` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`subject_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `subject_id` (`subject_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_user_subject
-- ----------------------------
INSERT INTO `tb_user_subject` VALUES ('1', '1', '1');
INSERT INTO `tb_user_subject` VALUES ('2', '2', '1');
INSERT INTO `tb_user_subject` VALUES ('3', '3', '3');
INSERT INTO `tb_user_subject` VALUES ('4', '1', '4');
INSERT INTO `tb_user_subject` VALUES ('5', '2', '4');
INSERT INTO `tb_user_subject` VALUES ('6', '3', '6');
INSERT INTO `tb_user_subject` VALUES ('7', '1', '6');
INSERT INTO `tb_user_subject` VALUES ('8', '2', '5');
INSERT INTO `tb_user_subject` VALUES ('9', '3', '4');
INSERT INTO `tb_user_subject` VALUES ('10', '1', '3');
INSERT INTO `tb_user_subject` VALUES ('11', '2', '2');
INSERT INTO `tb_user_subject` VALUES ('12', '3', '1');