sql性能分析explain（2. 举例理解）

最新推荐文章于 2021-07-07 16:26:52 发布

code_mzh

最新推荐文章于 2021-07-07 16:26:52 发布

阅读量296

点赞数

文章标签：数据库 mysql 索引

本文链接：https://blog.csdn.net/code_mzh/article/details/108298001

版权

单表优化（详细），多表优化

一，单表优化

今天来准备sql优化的环境，整个实验过程包含两张表，一张员工表emp和一张部门表dept，我们通过存储过程实现批量插入数据，1w条部门数据，5w条员工数据。下面给出具体的代码，大家可以结合注释理解执行。
直接全部复制执行即可，耐心等待，建议本地数据库，快

#创建表
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(10000, 60000);

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

#创建批量删索引存储过程，这个存储过程在我们之后的性能分析会用到
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");

一.全值匹配。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");

以第一条为例贴出图来详细说明一下整个优化的过程。

1.首先我们在没有建立任何索引的原始表的基础上进行第一次查询。

为了测试sql语句的效率，我们要不用缓存来查询，因此在语句中加上了sql_no_cache，执行这条语句所花费的时间为0.13s。然后我们使用explain语句对具体查询过程进行分析，我们着重关注以下几个指标：

type类型为all，表示需要遍历全表以找到匹配的行，
possible_keys为null意思是我们的查询中没有用到有关的索引字段，
所以实际所用到的索引key和key_len字段也为null，
执行时所需检查的行数rows为499066,需要查询的数量非常大，所以在没有建立索引之前的效率很低。

2.当我们在age,dept_id和name上面都建立索引之后

我们看到时间为<0.01s，使用explain进行分析具体来看，type类型为ref表示按照非唯一性索引进行扫描，相比之前的all有了很大的性能提升，possible_keys和key也都变成了idx_age_deptid_name,此时需要扫描的行数只有1行。所以在查询要尽量使用全值匹配（就是和间的索引一样）以提升性能。
这里有个问题：
我们在查询时涉及多个字段时，我们应该建立一个大的联合索引还是多个单列索引呢？在这里我直接给出结论：多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！深入了解具体过程和原理

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的写法来达到选择合适索引的目的
尽可能减少join语句中的循环总次数，小结果集驱动大结果集，就是子查询小，首先优化内层循环
保证join的被驱动表被索引，

code_mzh

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫