04-性能调优专题-MySQL性能调优-执行计划与SQL优化（explain）

学习地址：

https://www.bilibili.com/video/BV1es411u7we?p=13&t=690

简单介绍：当时项目数据量也是比较大的，核心数据分了2个库，业务表A 在 库A 分表分为8个表。业务表B在库B,分表分了16个表。

当时一条SQL执行超过10s就会在告警群一直报警，SQL出现慢查询，告警之后需要优化。当时不懂Explain的使用，然后就特意去B站学了，写了笔记。

我们当时解决慢查询的方案：

1.Explain分析SQL，看能否在写法上优化SQL。

2.使用强制索引，强制走查询更快的那个索引。

3.代码逻辑层面优化，比如查询1个月的数据量，1个月的数据量太大的话，可以分3段查询，将1个月分3段，然后再组装数据即可。

4.当时的项目表结构都是架构师之前设计好的，所以加索引是加不动的。但是后面的公司有解决过一个问题，SQL执行很慢，那个表没几个索引，就加了索引解决查询太慢的问题。

目录

一、MySQL 的 explain工具深度使用、剖析

1.id

1.1：id相同的时候，数据小的表优先执行

1.2：id值不同的时候，id越大越先执行

​编辑1.3：id值有相同，又有不同：id越大越先执行，id相同，从上往下顺序执行

2.selectType

 小结：​编辑

复合索引：

using where（需要回表查询）

单表优化

二、MySQL 调优 —— force index() 方法强制使用这个索引

---

一、MySQL 的 explain工具深度使用、剖析

（1）id

1：id相同的时候：数据小的表优先执行
2：id值不同的时候：id越大越先执行
3：id值有相同，又有不同：id越大越先执行，id相同，从上往下顺序执行

（2）selectType：primary，subquery,

（3）type：索引类型、类型

type:
    system/const:结果只有一条数据
    eqf_ref:结果多条，但是每条数据都是唯一的
    ref:结果多条，但是每条数据是0或多条

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge 
> unique_subquery > index_sub

system > const > eq_ref > ref > range > index > all ， 
要对type进行优化的前提：有索引

其中：system，const 只是理想状态

（4）possible keys : 可能会用到的索引

（5）key: 实际用到的索引

当possible key 和 key是null,代表没有索引

（6）key_len:索引的长度

作用：用于判断复合索引是否被完全使用

1.id

1.1：id相同的时候，数据小的表优先执行

id：id相同的时候，执行顺序是从上往下，顺序执行
table:table名字代表表名，先去查询的表
表的执行顺序，因表数据量的大小改变而改变，（因为笛卡尔积的影响）
 a表：4条数据
 b表：3条数据
 c表：2条数据 
a		b		c
4		3		2		=	4 * 3 = 12 * 2 = 24	
2		3		4		=	2* 3  = 6 * 4 =  24	
6条数据量小于12条数据量，程序执行6条数据就比12条快

（a表 2条，b表 3条，c表 4条） 执行顺序：a表先执行，b表第二，c表第三
（a表 5条，b表 3条，c表 4条） 执行顺序：b表先执行，c表第二，a表第三
结论：当id值相同的情况下，当表的数据量改变的时候，表的执行顺序就会发生改变，
数据小的表优先执行

1.2：id值不同的时候，id越大越先执行

本质：在嵌套子查询时，先查内层，再查外层

1.3：id值有相同，又有不同：id越大越先执行，id相同，从上往下顺序执行

（1）先执行c表

（2）再执行tc表

（3）最后执行t表

2.selectType

PRIMARY:包含子查询sql中的 主查询（最外层）

SUBQUERY:包含子查询SQL的 子查询（非最外层，非主查询）

 simple：简单查询（不包含子查询，union）

derived：衍生查询（使用到了临时表）

（1）在from子查询中只有一张表

用原来的4条数据的组成3条数据的组成临时表

 （2）在from子查询中，如果有table 1 union table2 ，则table1 就是 derived table2 就是 union

union：如上

union result: 告知开发人员，哪些表之间存在union

system，const 只是理想状态

const:仅仅能查到一条数据的sql ,用于primary key 或 unique索引，（类型 与 索引类型无关）

如果不是primary key 或者 unique 这2种索引，就不会是const

eq_ref:唯一性索引，对于每个索引建的查询，返回匹配唯一行数据，（有且只有一个，不能多，不能0）

select  * from  user where name = '';
(给name建索引，并且查到的值是唯一的，比如查到的值，张三，有且只能有一个张三)
user表里面的name都是唯一的，比如有200条数据，里面的每一条都是唯一的
使用场景：唯一索引，主键索引

 增加主键：

alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid); 

explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid;

此时：index是ref，而不是eq_ref(返回的数据都是唯一的，有且只有一个，不能多，不能为0)

 查询到的数据只有3行

 其实，teacher表中的数据存在6行，所以对应的teacherCard表里面的只有3行数据，还有3条就没有查到，所以为0（所以就是ref）

ref：非唯一性索引（对应每个索引建的拆线呢，返回匹配的所有行0，多） 

select * from teacher t  where t.name = 'zs';	存在2条name为zs的数据
返回了2条数据
explain select * from teacher t  where t.name = 'zs';
index就是ref（返回匹配的数据是多行）

range:检索指定范围的行，where后面是一个范围查询（between,>,=,in（特殊，有时候会失效）不设置为索引）

explain select t.* from teacher t where t.id in (1,2) ;
index就是range
注意：in的时候有时候会失效，索引类型会是all（没有索引类型）,其余几个不会

index:查询索引中的全部数据

teacher表存在 id name age字段
index:代表查id字段的所有值
explain select t.tid from teacher t: tid是索引，只需要扫描索引表，
不需要所有表中的所有数据
    只需要去扫描索引字段tid的全部数据

all：查询表中的全部数据

teacher表存在 id name age字段
all:代表查id字段,name,age。把表里面的数据全部都查了一遍，全表扫描
explain select t.cid from teacher t -- cid不是索引，需要全表扫描，
即需要表中的所有数据 

lkey_len:索引的长度：

ref：注意与type的ref值区分。

作用：指明当前表所参照的字段。

 

 8.row:被索引优化查询的数据个数（实际通过索引而查询到的 数据个数）

9.Extra：

using filesort:性能消耗大，需要 “额外” 的一排序（查询）一般出现在order by 中

排序之前，先查询

explain select t.* from test02 t where a1 = '' order a1 -- 不是usring filesort

需要排序的a1字段，已经查出来了，所以直接排序就ok，就不存在额外的排序（查询）

explain select t.* from test02 t where a1 = '' order a2 -- usring filesort

查询过程：查a1，查a2,根据a2排序

order by a2:根据a2字段排序，需要查询a2，所以进行了额外的查询（查a2）

 小结：

复合索引：

如果（a,b,c,d）是复合索引，和使用的顺序全部一致（且不跨列使用），则复合索引全部使用。

如：

select a,c
    from test
    where  a = 
    and b =
    and c =
    and d = ;

 using temporary ：性能损耗大，用到了临时表。一般出现在group by 中,已经有表了，但不适用，必须再来一张表。

explain select a1 from test01 where a1 in('1','2','3') group by a1;
不会出现	using temporary
先where a1，查询数据，作为一张表，
然后group by a1，在之前查出来的表中，已经存在a1字段，所以在之前的表中直接group by a1就可以了
不需要做额外的操做，就不会出现using temporary

explain select a1 from test02 where a1 in('1','2','3') group by a2';
出现	using temporary
先把where a1 查出字段a1，来作为一个表，
group by a2，因为第一次查的表中没a2，所以mysql会去把a2查出来放一个表，然后再分组group by a2，
分好组之后再把结果 和 第一次存放a1字段的表一起合并，得出最终结果的表
做了额外的操作，就出现了using temporary

过程解析：

on... join... where... group by... having... select dinstict... order by... limit...

using index:性能提升，索引覆盖，覆盖索引。原因：此次查询不读取源文件，直接从索引文件中获取数据，（不存在回表查询）。

只要使用到的列全在索引列中，就是索引覆盖

select age from test where age = 2：
使用到了age
查询的值也只是age
只需要去索引表里面查询age就行，不需要去全表里面查age，
如果索引文件才1m,全表文件1G,在索引文件，和全表文件查age区别就明显了

例如：test02表中，有一个复合索引（a1,a2,a3）

如果用到了using index 时，会对 possible key,和key造成影响：

1.如果没有where ，索引只会出现在key中，

2.如果有where,索引出现在key 和 possible key中

using where（需要回表查询）

假如age时索引列，

但是查询语句，select age ,name from test where age = 1;

age可以在索引表里面查询到，但是name必须回原表查,所以就是using where

impossible where:where 子句永远为false

explain select * from test where age =1 and age =2;

age既等于1 又等于2的数据不存在，显然，where后面的条件不可能成立，所以就出现了impossible where

create table test03
(
 	a1 int(4) not null,
a2 int(4) not null,
a3 int(4) not null,
a4 int(4) not null
);
alter table test03 add index idx_a1_a2_a3_a4(a1,a2,a3,a4);

explain select a1,a2,a3,a4 from test03 where a1 = 1 and a2 = 2 and a3 = 3 and a4 =4; -- 推荐写法 
分析：using index 因为索引的使用顺序（where后面的顺序）和复合索引的顺序一致

explain select a1,a2,a3,a4 from test03 where a2 = 1 and a1 = 2 and a4 = 3 and a3 =4; -- 一定不要这样写
分析：
    虽然随便编写的顺序 和 复合索引的顺序不一致，但是sql在实际执行前，经过了sql优化器的优化调整，
 结果与上一条sql一致的。

explain select a1,a2,a3,a4 from test03 where a2 = 1 and a1 = 2 and a4 = 3 ; 
分析：
    a1和a2走了索引，但是a4跨列了，导致索引失效，就回表查询
    using where ,using index ,
    using where：是因为索引顺序a1,a2,a3,a4,此处跳过了a3,直接a2到a4，所以会回表查询a4，所以就是usring where 
    
    总结：
    (1)如果（a,b,c,d）复合索引，和使用的顺序全部一致，（且不跨列使用），则复合索引全部使用。
    如：select a,c from test where a = and b = and c = and d =
    (2)where 和 order by 拼起来，不要跨列使用
    
    

单表优化

优化：加索引
alter table book add index idx_bta(bid,typeid,authorid);

索引一旦进行 升级优化，需要将之前废弃的索引删掉，防止干扰。
drop index idx_bta on book;

根据sql实际解析的顺序，调整索引的顺序：
alter table book add index idx_tab(typeid,authorid,bid);-- 虽然回表查询bid，但是bid是索引

再次优化（之前是index级别），思路：因为之前范围查询in有时会失效，因此交换 索引的顺序，authorid第一位，typeid第二位
drop index idx_tab on book;
alter table book add index idx_atb(authoried,typeid,bid);
explain select bid from book where authorid = 1 and typeid in(1,3) order by typeid desc
-- 小结
a.最佳做前缀，保持索引的定义和使用的顺序一致性。
b.索引需要逐步到位，而不是一步到位。

二、MySQL 调优 —— force index() 方法强制使用这个索引