MySQL explain

MySQL explain

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

原文地址:一张图彻底搞懂MySQL的 explain

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 // 包含不适合在其他列中显示但十分重要的额外信息

一张图搞定explain

详细内容

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;

image-20210305155353432

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);

image-20210305160441773

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;

image-20210305161057589

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;

image-20210305161323588

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)

image-20210305161457073

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)

image-20210305161457073

4. DERIVED

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

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

image-20210305161858360

5. UNION

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

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

image-20210305162101242

6. UNION RESULT

从UNION表获取结果的select

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

image-20210305162101242

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;

image-20210305162706541

2. system

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

没有实例

3. const

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

explain select * from tb_system where id=1;

image-20210305163504089

4. eq_ref

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

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

image-20210305164009586

5. ref

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

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

image-20210305164606001

6. ref_or_null

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

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

image-20210305164906298

7. index_merge

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

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

image-20210305165059557

8. range

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

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

image-20210305165245071

9. index

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

explain select id from tb_user;

image-20210305165605221

10. ALL

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

explain select * from tb_user;

image-20210305165706895

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;

image-20210305171532515

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);

image-20210305171734631

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);

image-20210305171931781

4. Using where

使用了where条件

image-20210305171931781

5. Using join buffer

使用了连接缓存

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

image-20210305172135057

6. impossible where

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

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

image-20210305172252612

7. distinct

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

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

image-20210305172721616

8. Select tables optimized away

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

explain select min(id) from tb_user;

image-20210305172902350

使用的数据表

/*
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');

# mysql 

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×