mysql中的explain语句与单表优化

最新推荐文章于 2022-12-19 14:31:33 发布

职场江湖指北

最新推荐文章于 2022-12-19 14:31:33 发布

阅读量276

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/BlueLotuss/article/details/108153401

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

mysql中的explain语句与单表优化

一、实验环境准备

今天来准备sql优化的环境，整个实验过程包含两张表，一张员工表emp和一张部门表dept，我们通过存储过程实现批量插入数据，1w条部门数据，50w条员工数据。下面给出具体的代码，大家可以结合注释理解执行。

#创建表
create table dept(
    id int(11) not null auto_increment,
		deptName varchar(30) DEFAULT null,
		address VARCHAR(40) DEFAULT null,
		ceo int null,
		PRIMARY key (id)
)engine=INNODB auto_increment=1 DEFAULT CHARSET=utf8;

create table emp(
    id int(11) not null auto_increment,
		empno int not null,
		name VARCHAR(20) DEFAULT null,
		age int(3) DEFAULT null,
		deptId int(11) DEFAULT null,
		PRIMARY key (id)
		#CONSTRAINT fk_dept_id foreign key (deptId) REFERENCES dept(id)
)engine=INNODB auto_increment=1 DEFAULT CHARSET=utf8;


show VARIABLES like '%log_bin%';

#开启允许创建函数
set GLOBAL log_bin_trust_function_creators = 1;

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

#创建函数

#用于随机生成字符
delimiter $$
create FUNCTION rand_string(n int) returns VARCHAR(255)
begin
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i int DEFAULT 0;
while i < n do
    #concat()是拼接字串，substring()是取字串【第二个入参是从哪个位置开始截取，第三个入参是指截取1个字符】，
		#floor()是取整，rand()是取[0,1)
    set return_str = concat(return_str, SUBSTRING(chars_str, floor(1+rand()*52), 1));
		set i = i + 1;
end while;
return return_str;
end $$

#用于随机生成多少到多少的编号
delimiter $$
create function rand_num(from_num int, to_num int) returns int(11)
begin
declare i int default 0;
set i = floor(from_num + rand()*(to_num-from_num+1));
return i;
end $$

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

#创建存储过程

#插入emp数据
delimiter $$
create procedure insert_emp(start int, max_num int)
begin
declare i int default 0;
set autocommit = 0;
repeat
    set i = i + 1;
		insert into emp(empno, name, age, deptId) values((start+i), rand_string(6), rand_num(30, 50), rand_num(1, 10000));
		until i = max_num
end repeat;
commit;
end $$

#插入dept数据
delimiter $$
create procedure insert_dept(max_num int)
begin
declare i int default 0;
set autocommit = 0;
repeat
    set i = i + 1;
		insert into dept(deptName, address, ceo) values(rand_string(8), rand_string(10), rand_num(1, 500000));
		until i = max_num
end repeat;
commit;
end $$

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

#执行存储过程

#往dept表添加1万条数据
delimiter ;
call insert_dept(10000);

#往emp表添加50万条数据
call insert_emp(100000, 500000);

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

#创建批量删索引存储过程，这个存储过程在我们之后的性能分析会用到
delimiter $$
create PROCEDURE proc_drop_index(dbname VARCHAR(200), tablename VARCHAR(200))
begin
    DECLARE done int DEFAULT 0;
		DECLARE ct int DEFAULT 0;
		DECLARE _index VARCHAR(200) DEFAULT '';
		DECLARE _cur CURSOR for SELECT index_name  FROM information_schema.STATISTICS WHERE table_schema=dbname and table_name=tablename
  AND seq_in_index = 1 AND index_name <>'PRIMARY';
    DECLARE continue HANDLER for not found set done = 2;
		OPEN _cur;
		fetch _cur into _index;
		while _index <>'' do
		    set @str = concat("drop index ", _index, " on ", tablename);
				PREPARE sql_str from @str;
				EXECUTE sql_str;
				DEALLOCATE PREPARE sql_str;
				set _index = '';
				FETCH _cur into _index;
		end while;
    close _cur;
end $$

#执行存储过程

delimiter ;
call proc_drop_index("mydb","t_emp");

二、Mysql中的explain语句

在Mysql的性能分析中我们免不了会使用explain语句。在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析查询语句或是表结构的性能瓶颈。

下面是一个简单的使用explain语句的例子，使用的表示我们在上一部分所创建的表，我们可以先来看看结果包含哪几个字段。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R0m5t2Xi-1598005456800)(C:\Users\jiangzhe\AppData\Roaming\Typora\typora-user-images\image-20200821150929076.png)]$

1.id

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

2.select_type

select_type表示了查询的类型，它的常用取值有：

simple，表示此查询不包含union查询或者子查询
primary，表示此查询是最外层的查询
union，表示此查询是union的第二或者随后的查询
dependent union,union中的第二个或者后面的查询语句，取决于外面的查询
union result，union的结果
subquery，子查询中的第一个select
dependent subquery:子查询中的第一个select，取决于外面的查询。即子查询依赖于外层查询的结果。

3.table

显示这一行的数据是关于哪张表的

4.type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-djMltDf1-1598005456802)(C:\Users\jiangzhe\AppData\Roaming\Typora\typora-user-images\image-20200821173434657.png)]$

5.possible_keys

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

6.key

实际使用的索引。如果为NULL，则没有使用索引。

查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

7.key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

key_len字段能够帮你检查是否充分的利用上了索引。

8、ref

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

9、rows

rows列显示MySQL认为它执行查询时必须检查的行数——越少越好。

10、Extra

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

Using filesort ：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
Using temporary ：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
USING index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
Using where：表明使用了where过滤
using join buffer：使用了连接缓存：
impossible where：where子句的值总是false，不能用来获取任何元组
select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

三、单表查询优化

1.全值匹配。where后面有哪些字段，就给哪些字段建立索引。

EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and deptid = 4 and emp.name = 'abcd';#未建立索引前0.13s
create index idx_age_deptid_name on emp(age, deptId, name);
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and deptid = 4 and emp.name = 'abcd';#建立索引后<0.01s

#调整where后面的条件的顺序，也能用上索引。因为mysql有优化器，能调整顺序。
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and emp.name = 'abcd' and deptid = 4 ;
#记得在最后调用我们第一部分写好的存储过程清掉索引，以便后续进行查询性能分析
call proc_drop_index("mydb", "emp");

我以第一条为例贴出图来详细说明一下整个优化的过程。首先我们在没有建立任何索引的原始表的基础上进行第一次查询。为了测试sql语句的效率，我们要不用缓存来查询，因此在语句中加上了sql_no_cache，执行这条语句所花费的时间为0.13s。然后我们使用explain语句对具体查询过程进行分析，我们着重关注以下几个指标：type类型为all，表示需要遍历全表以找到匹配的行，possible_keys为null意思是我们的查询中没有用到有关的索引字段，所以实际所用到的索引key和key_len字段也为null，执行时所需检查的行数rows为499066,需要查询的数量非常大，所以在没有建立索引之前的效率很低。当我们在age,dept_id和name上面都建立索引之后，我们看到时间为<0.01s，使用explain进行分析具体来看，type类型为ref表示按照非唯一性索引进行扫描，相比之前的all有了很大的性能提升，possible_keys和key也都变成了idx_age_deptid_name,此时需要扫描的行数只有1行。所以在查询要尽量使用全值匹配以提升性能。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bSrwbSTQ-1598005456804)(C:\Users\jiangzhe\AppData\Roaming\Typora\typora-user-images\image-20200821142953631.png)]$

这里有个问题：我们在查询时涉及多个字段时，我们应该建立一个大的联合索引还是多个单列索引呢？在这里我直接给出结论：多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！(想要深入了解具体过程和原理的请参考https://blog.csdn.net/Abysscarry/article/details/80792876)

2.最佳左前缀法则。如果索引了多列，查询从索引的最左前列开始并且不跳过索引中的列。

#最佳左前缀匹配，优化器会调整sql顺序，从索引的最左边开始匹配，若中间断开则后面的索引全都失效。
create index idx_age_deptid_name on emp(age, deptId, name);
#执行下面的查询可以发现ken_len为73，说明用上了完整的三个字段。
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and deptid = 4 and emp.name = 'abcd';
#下面两个查询的key_len均为5，因为删掉了'and deptid = 4 ',只能用上一个索引age，我们建立索引的顺序为age,deptId,name，中间缺失了deptId字段导致后面的name索引失效。
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and emp.name = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name = 'abcd' and emp.age = 30;

#删掉了'emp.age = 30',一个索引都用不上，explain的type为all，key_len为null，查全表
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.deptId = 4 and emp.name = 'abcd';

#清掉索引
call proc_drop_index("mydb", "emp");

3.在索引列上做任何操作（计算、函数、类型转换等），都会导致索引失效。where筛选条件尽量避免使用函数导致索引失效。

create index idx_name on emp (name);
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name like 'abc%'; #执行时间<0.01s
#下面语句的查询结果和上面一样，但是使用了left函数，导致建立在name字段上的索引失效
EXPLAIN SELECT SQL_NO_CACHE * from emp where left(emp.name, 3) = 'abc'; #执行时间0.01s
call proc_drop_index("mydb", "emp");

4.总结:范围查询，右边索引失效。但是使用范围的字段索引不会失效

create index idx_age_deptid_name on emp(age, deptId, name);
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and emp.deptId > 20 and emp.name = 'abc'; #这里执行过程中key_len为10，说明只用了age和deptid这俩个索引，因为emp.deptId > 20的范围判断导致后面的建立在name字段上的索引失效。
create index idx_age_name_deptid on emp(age, name, deptId);#
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and emp.deptId > 20 and emp.name = 'abc'; #将范围判断条件放在索引的最后，我们可以发现key_len为73，利用上了所有的索引字段。
call proc_drop_index("mydb", "emp");

5.所有负面操作，比如is not, not in, !=, <>, is not null,都用不上索引

create index idx_name on emp (name);
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name <> 'abc%';#执行显示实际未用上索引，扫描行为499066
create index idx_age on emp (age);
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age is null;#能用上age索引
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age is not null;#不能用上age索引
call proc_drop_index("mydb", "emp");

6.总结：’%'开头用不上索引，字串类型的字段索引按首字母开头构成一个tree，通配符开头则只能全树匹配

create index idx_name on emp (name);
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name like 'abc%';#能用上索引
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name like '%abc%';#不能用上索引
call proc_drop_index("mydb", "emp");

7.字符串类型的字段不加单引号导致索引失效。比如`where name = 123`，name为字串类型时，给name建索引会失效。

create index idx_name on emp (name);
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name = 123;#不能用上索引，因为name=123做了类型转换，name是varchar类型

四、一般性建议

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

最后，本博客的部分代码参考了Android_la的博客，想要了解更多的内容也可以去ta的博客学习，一起进步！

职场江湖指北

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql中的explain语句与单表优化

mysql中的explain语句与单表优化一、实验环境准备今天来准备sql优化的环境，整个实验过程包含两张表，一张员工表emp和一张部门表dept，我们通过存储过程实现批量插入数据，1w条部门数据，50w条员工数据。下面给出具体的代码，大家可以结合注释理解执行。#创建表create table dept( id int(11) not null auto_increment, deptName varchar(30) DEFAULT null, address VARCHAR(40)
复制链接

扫一扫