学习地址:
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执行很慢,那个表没几个索引,就加了索引解决查询太慢的问题。
目录
编辑1.3:id值有相同,又有不同:id越大越先执行,id相同,从上往下顺序执行
二、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)
小结:![](https://img-blog.csdnimg.cn/f9c265b262014272a5771034be4a26e6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbGl1bWFuZ3R1dHUyOTE5,size_20,color_FFFFFF,t_70,g_se,x_16)
复合索引:
如果(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.索引需要逐步到位,而不是一步到位。