学习笔记-Explain的介绍

最新推荐文章于 2023-02-06 10:01:26 发布

众里阑珊

最新推荐文章于 2023-02-06 10:01:26 发布

阅读量245

点赞数

分类专栏：性能调优专题文章标签：学习数据库 mysql

本文链接：https://blog.csdn.net/qq_40777329/article/details/125901670

版权

性能调优专题专栏收录该内容

4 篇文章 0 订阅

订阅专栏

Explain工具介绍

使用Explain关键字可以模拟优化器执行sql语句，分析你的查询语句或是结构的性能瓶颈在select语句之前增加Explain关键字，MySql会在查询上设置标记，执行查询会返回执行计划的信息，而不是去执行这条sql。

Explain分析示例

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int NOT NULL DEFAULT '0' COMMENT '年龄',
  `address` varchar(20) NOT NULL DEFAULT '' COMMENT '地址',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_address` (`name`,`age`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='用户信息';
-- 进行插入数据
drop procedure if exists insert_us;
delimiter ;;
create procedure insert_us()
BEGIN
	declare i int;
	set i = 1;
	while (i <= 100000) DO
		insert into users(`name`,age,address) VALUES(concat('SanFeng',i),i,'北京');
		set i = i+1;
		end while;
	end;;
delimiter ;;
call insert_us();

Explain两个变种（MySql8已经不能用了）

Explain extended

会在explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句

EXPLAIN EXTENDED SELECT * FROM users WHERE id = 1;
SHOW WARNINGS;

Explain partitions

相比Explain多了个partitions字段，如果查询是基于分区表的话，会显示查询将访问的分区

EXPLAIN partitions SELECT * FROM users WHERE id = 1;
SHOW WARNINGS;

Explain中的列

id列

id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。id列越大执行的优先级越高，id相同则从上往下执行，id为null最后执行。

select_type列

select_type表示对应是简单还是复杂的查询。

set session optimizer_switch='derived_merge=off';
explain select (select 1 from users where id = 1) from (select * from users where id = 1) tep; 
set session optimizer_switch='derived_merge=on';

Explain展示列

simple

简单查询。不包含子查询和union

primary

复杂查询中最外层的select

subquery

包含在select中的子查询(不在from子句中)

derived

包含在from字句中的子查询

union

在union中的第二个和后面的select中

table列

table中表示当前行是访问哪一张表，当from子句中有子查询，table列是一个格式，表示当前查询依赖id = N 的查询，先执行id = N的查询；当有union时。

partitions列

如果查询是基于分区表的，会显示查询将访问的分区。

type列

这一列表示关联类型或访问类型，依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all 一般来说，得保证查询达到range级别，最好达到ref。

null

MySql能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。

system和const

MySql能对查询的某部分进行优化并将转换为一个常量，用于primary key或 unique key的所有列与常数比较时，所以表最多有一个匹配行，读取一次速度比较快，system是const的特例

eq_ref

primary key或unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。

ref

相比于eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值比较，可能会找到多个符合条件的行。

range

范围扫描通常出现在in()、between、<、>、>=等操作中，使用一个索引来检索给定范围行。

index

扫描全索引就拿到结果，一般是扫描二级索引，这种索引不会从索引树节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描速度比较慢的，这种查询一般为使用了覆盖索引，二级索引一般比较小，所以这种通常比all快一些。

all

即全表扫描，扫描当前表的聚簇索引的所有叶子节点，通常情况下这需要增加索引来优化。

possible_keys列

这一列显示查询可以使用那些索引列来查找，如果该列是null，则没有相关的索引。explain可能出现possible_keys有列而key显示null的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大选择了全表扫描。

key列

这一列显示MySql实际采用了哪个索引来优化改表的访问。如果没有使用索引，则该列是null；如果想强制MySql或忽略possible_keys列中的索引，在查询的时候使用force index、ignore index

key_len列

这一列显示MySql在索引里使用的字节数，通过这个值可以算出具体使用了那个索引中的那些列。

key_len计算规则

字符串 char(n) 和 varchar(n)，5.0.x 版本以后中，n代表字符数，而不是字节数，如果utf8，一个1数字或字母占用一个字节，一个汉字占3个字节

char(n)：如果存汉字长度就是3n字节

varchar(n)：如果存汉字则长度为3n+2字节，加的2个字节用来存储字符串长度，因为varchar是变长字符串
数值类型

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节
时间类型
date：3字节

timestamp：4字节

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