MySQL慢查询优化(线上案例调优)


PS:最近做了一个面试题精选精答的开源项目,假定想要了解更多MySQL相关的技术总结,可以看一看,假定对我们有协助,期望我们协助给一个star,谢谢我们了!

《面试指北》项目地址:github.com/NotFound9/i…

MySQL慢查询优化(线上案例调优)

之前建了一个技术交流群,我们感兴趣也可以进一下,期望可以和我们一同学习前进!
MySQL慢查询优化(线上案例调优)

文章阐明

这篇文章首要是记载自己最近在真实作业中遇到的慢查询的案例,然后进行调优分析的进程,欢迎我们一同讨论调优经历。(以下出现的表名,列名都是化名,实践数据也y ^ J 4 G d N i进行过一点微调。)

一.凌乱的深分页问题优化

布景

有一个article表,用于存储文章的根本信息的,有文章id,作者id等一些特色,有一个conY M Z R ] Wtent表,首要用于存储文章的内容,主键是article_id,需求需求将一些满足条件的作者发布的文章导入到其他一个库,所以我伙伴就在项目中先查询出了符合条c : u件的作者id,然后敞开了多个线程v c – = = 1,每个线程每次取一个作者id,实行查询和导入作业。

查询= # ~出作者id是1111,名z N 9 | u |下的全部文章信息,文章内容相关的信息的SQL如下:

SELECT
a.*, c.*
FROM
article a
LEFT JOIN content c ON a.id = c.article_id
WHERE
a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'm l _
LIMIT 210000,100

因为查询的这个数据库; – P :是机械硬盘的,在offset查询到20万时,查询时间现已特别长了,运维伙伴那儿直接收到报警,说这个库现已IO阻塞了,现已屡次进行主从切换了,_ j M , _ z我们就去navicat里面试着实行了一下这个语句,也是一直在等候, 然后对数据库实行shv Y c ;ow proceesslist 指令检查了一下,发现每个查询都是处于WritinK ( 7 {g to net的情况,没办法只能先把导入的项目暂时下线4 3 ^ J { 0 k $,然后实行kill指令将其时的查询都杀死进程(因为只是客户端Stop的话,MySQL服务端会持续查询)。

然后我们o Y ~初步分析这条指令实行慢的7 5 % X i 3原因:

是否是联合索引的问题

其时是索引情况如z U | U * W d g 2下:

article表的主键是id,author_id是一个一般索引
content表的主键是article_id

所以认为其时是实行流程是先去article表的一般索引author_id* d A 6 O S里面找到1111的全部文章id,然后根据这些文章id7 Q J h A $ T o o去article表的调集索引中找到全部的文章,然后拿每个文章id去content表中找文章内容等信息,然后判断create_tic L O ! Q s ` ] %me是否满足要求,进行过滤,最终找到offset为20000后的100条数据。

所以我们就将article的author_id索引改成了联合索引(author_id,create_time),这样联合索引(C K 8 5 ( = 0 Fauthor_id,createc ] %_time)中的B+树就是先装置author_id排序,再按照create_time排序,这样一初步在联合(author_id,create_time)查询出来的+ 2 X M文章id就是满足create_time < ‘2020-04-29 00:00:00’条件的,后边就不用进行过滤了,就不会就是符合就不用对create_time过滤。

流程的确是这个流程,但是去查询时,假定limit还是210000, 100时,k ( = . ) A p 5还是查不出数据,几分钟都没有数据,一直到navica提示超时,运用Explain看的话,的确射中索引了,假定将offset调小q V s ? . + Y,调成6000, 100,牵强可以查出数据,但是需求46s,所以瓶颈不在这儿。

真实原因如下:

先看关于深分页的两个查询,id是主键,val是一般索引

直接查询法

select * from test where val=4 limit 300000,5;

先查主( } t k键再join

select * from test a
inner join
(select id from test8 N M G + u $ x where val=4 limit 300000,5) at G ;s b
on a.id=b.id;

这两个查询的效果都是查询出offset是30000后的5条数据,差异在于第一个查询需求先去一般索引val中查询出300005个i, + o M D o K GdU / C – O z w r,然后去调集索引下读取300005个数据页,然后扔掉前面的300000个效果,只! Q – c m / { h回来最终5个效果,进程& 1 ! L n : _ W中会产生了许多的随机I/O。第二个查询一初步在一般索引val下就只会读取后5I – H f T m # x个id,然后去调集索引下读取5个数据页。

同理我们业务中那条查询其实是更加凌乱的情况,因为我们业务的那条SQL不只会读取article表中– k – `的210100条效果,而且会% D r z G o u H每条效果去content表中查询文章相关内容,而这张表有r $ 0 =几个TEXT类型的字段,我们运用show table status指令h ] t R S % 3 1检查表相关的信息发现

Name Engine Row_E ? a h v v yformat Ro= m . 1 G y gws Avg_Row_length
article InnoDB Compact 2F P ^ ; , 6 + @ i682682 266
content InnoDB Compact 2824768 16847

发现两H # ~ 7个表的数据量都是200多万的量级,article表的行均匀长度是266,content表L 7 C的均匀长度是, g ^ [ ~ N w16 / {847,简略来说是当 InnoDB 运用 Compact 或许 Redundant 格局存储极长的 VARCHAR 或许 BLOB 这类大目标时,我们并不会直接将全部的内容都存放在数据页节点中,而是将/ K t o b行数据中的前 768 个y U G _ 4 q . #字节存储在数据页中,后边会通过偏移量指向溢出页。

(具体了解可以看看这篇文章深度好文带你读懂MySQL和InnoDB)

MySQL慢查询优化(线上案例调优)

这样再从content表里面查询连续的100行数据时,读取每行数据时,还需求去读溢出P s @ p页的数据,这样就需求许多随机IO,因为机械硬盘的硬件特性,随机IO会比次第IO慢许多。所以我们后来又进行了测验,

只是从article表里面查m = P j W v g询limit 200000,100的数据,发现即使存在深分页的问题,查询时间仅a c O 3 + R X仅0.5s,因为articleQ # k ^表的均匀列长度是266,全部数据都存在数据页节点中,不存在页溢出,所以都是次第# T | ) . #IO,所以比较快。

//查询时间0.51s
SELECT a.* FROM article a` x % !
W W p 2 V * C KHERE a.author_id = 1111
AND a.create_time &lw ` L . ` 8 d Jt; '2020-04-29 00:00:00'
LIMIT 200100, 100

相反的,R 5 ; V o s ]我们直接先找出100个article_id去content表里面查询数据,发现比较慢,第一次查询时需求3s左右(也就是这些id的文章内容相关的信息都没有过,没有缓存的情况),第2次查询时o f q因为这? ~ X 3 X h些溢出页数据现已加载到buffer pool,所以大约0.04s。

SELECT SQL_NO_CACHE c.*
FROM artg I Y ` n ticle_content c
WHERE c.article_id in(100个article_id)

处理计划

所以; u )针对这个问题的处理计划首要有两种:

先查出主键id再inner join

非连续查询的情况下,也就是我们在查第100页的数据时,不一定查了第99页,也就是允许跳页查询的情况,那么就是运用先查主键再X & 0 4joB : b # Z j d 7in这种办法对我们的业务SQL进行改写成u 0 F – f下面这样,下查询出210000, 100时主键id,作为暂时表temp_tableu 8 m L 3,将article表与temp_table表进行inner join,查询出中文章相关的信息,而且去left Join content表查询文章内容相关的信息。 第一次| 9 m | z 2 N r W查询大约1.11s,后边每次查询大约0.15s

SELECT
a.*, c.*
FROM article a
INNER JOIN(
SELECT	id FROM	article a
WHERE	a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000 ,
100
) as temp_table ON a.id = temp_table.id
LEFT JOIN content c ON a.id = c.article_id

优化效果

优化前,offset抵达20万的量级时,查询时间过长,一直到超时。

优化后,offset抵达20万的量级时,查a @ M R u } x询时间为1.11s。

运用规划查询条件来束缚取出的数据

这种办法的大致思路如下,假定要查询test_table中of6 g c o ! Dfset为10000的后100条数据,假定我们事前已知第10000条数据的id,值为min_id_value

select * frok ` F b N i 2 A wm test_table where id > min_id_value order by id limit 0, 100,就是即运用条件i; u U 4 F 6d > min_id_value在扫描索引是4 m | 9 N % c跳过10000条记载,然后取100条数据即可,这种_ | V D处理办法的offset值便成为0了,V b i h但此种办法有束缚,有必要知道offset对应id,然后作为min_id_value,添加id > min_id_value的条件来进行过滤t B J * 2 X n,假定是用于分页查找的话,也就是有必要知道上一页的最大的id,所以只能一页一页得E y u查,不能跳页,但是因为我们的业务需求就是每次100条数据,进行分批导数据,所以我们这种场景是可以运用 s l c $ % t。针对这种办法,我们的业务SQL改写如下:

//先查出最大和最小的id
SELECT min(a.id) as min* z a 1 ! S_id , max(a.id) as max_id
FN e TROM article a
WHERE a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
//然后每次循环查找
while(min_id<max_id) {
SELECT a.*, c.* FROM article a LEFT JOIN content c ON a.id = c.article_id  WHERE a.author_id = 1111  AND a.id > min_idZ u k o 4 LIMIT 100
//这100条数据导入结束后,将100` } 9数据数据中最大的id赋值给min_id,以便导入下100条数据
}

优化效果

优化前,offset抵达20万的量级时,查询时间过长,一直到超时。

优化后,offset抵达20万的量级时,因为知道第20万条数据的id,查询时c * / 8 i ( Q刻为0.34) q r r (s。

二.联合索引问题优化

联合索引其实有两个效果:

1.充分运用where条件,缩小规划

例如我们需求查询以下语句:

SELECT * FROM test WHERE a = 1 AND b = 2

假定对字段a建立单列索引,对b建立单列索引,那么在查询时,只能选择走索引% 7 M + # g | )a,查询全部a=1的主键id,然后进行回表,在B u b 9回表的进程中,在调集索引中读取每一行数据,然后过滤出] N 6 4 V r M o ^b = 2效果集,或许走索引b,也是这样的进程。
假定对a,b建立了联合I H f y . Q | x Z索引(a,b),那么在查询时,直接在联合索引中先查到a=1的节点,然后根据b=2持续往下查,查出符合条件的效果集,进行回表。

2.防止回表(此刻也叫掩盖索引)

这种情况就是假定我们只$ & 6 : ,查询某几D = : v G 1 ? p G个常用字段,例如查询a和b如下:

SELECT a,b FROM test WHERE a = 1 AND b = 2

对字段a建立单列索引,对b建立单列索引就需求像上面所说的,查到符合条件的主键id调集后需求去调集索引下回表查询,但是假定我们要查询的字段自身在联合索引中就都包含了,那么就不用回表了。

3.削减需求回表的数据的行数

这种情况就是假定我们需求查询a>1而且b=2的数据

SELECT * FROM test WHERE a > 1 AND b = 2

假定建立的是单列索引a,那么在查询时会在单列索引a中把a>1的主f 6 |键id全部查找出来然后进行回表。
假定建立的是联合索引(a,b),根据最左前缀匹配原则,因为a的查询条件是一个规划查找(=或许in之外的查询条件都是规划查找),这样虽然在联合索引中查询时只能射中索引a的部分,b的部分射中不了,只能根据a>1进行查询,但是因为联合索引中每个叶子节点包含b的信息,在查询出全部a>1的主键id时,也会对– v 8 # Fb=2进行筛选,这样需求回表的主键id就只有a>1而且b=2这部分了,所以回表的数据量会变小。

我们业务中碰7 – / + y W V L到的就是第3种情况,我们的业务SQL本来e p 1 B更加凌乱,还会join其他表,但是因为优化的瓶颈在于建立联合索引,所2 – K Y以进行Y z M了一些简化,下面是U _ q | X简化后的SQL:

SELECT
a.id as article_id ,
a.title as title ,
a.autho5 Q f .r_id as author_id
from
article a
where
a.create_time bQ / - Z -etween '2020-03-29 03:00:J k 4 z N r00.003'_ X M ~
and '2020-04-29 03:00:00.003'
and a.sta3 l [ 2tus = 1

我们的需求其实就是从article表中查询出最近一个月,status为1的文章,我们本来就是针对create_time建了单列索引,效果在慢查询日志中发现了这条语句,查询时间需求0.91s左右,所以初步尝试着进行优化。

为了便于测验,我们在表平分别对create_time建立了单列索引create_time,对(create_time,status)建立联合索引idx_createTime_status。

强制运用idx_createTime进行查0 K C l = F

SELECT
a.id as article_id ,
a.title as title ,
a.author_id as author_id
from
article a  FORCE INDEX(id0 4 0 [x_createTime)
where
a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

强制l ^ B M 7 g运用idx_createTime_sW u otatus进行查询(即使不强制也是会选择这个索引)

SELECT
a.id as article_id ,
a.title as title ,
a.author_id as author_id
from
article a  FORCE INDEX(idx_createTime_status)
wz F I I T 0 U D .here
a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

优化效果:

优化前运用idx_createTime单列索引,查询时间为0.91s

优化前运用idx_createTime_staj B 7 @ Utus联Z ( ; ] T d @ J合索引,查询时间为0.21s

EXPLAIN的效果+ ( ? h 8 P g .如下:

id type key key_len rows filtered Extra
1 range idx_createTime 4 311608 25.00 Using index condition; U5 r } O NsinC e Y _ Ng wheL C mre
2 range idx_createE = ] R Z : q LTime_e % ; h Y 8status 6 310812 100.00 Using index condition

原理分析

先介绍一下EXPLAIN中Extra列的各种取值的意义

Using filesort

当Query 中包含 ORDER BY 操作,而且无法运用索引完结排序操作的时分,MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序,不然| 3 7 E G _ J 8从磁盘排序。Explain不会显现的奉告客户端用哪种排序。

Using index

仅运用索引树中的信息从表中检索列信息,而不需求进行附加查找来读取实践行(运用二级掩盖索引即可获取数据)。 当查询仅运用作为单个索引的一部分的列时,可以运f } p N 8 g –用此战略。

Using temporary

要处理查询,MySQL需求创立一个暂时表来保存效果。 假定查询包含不同列的GROUP BY和ORDER BY子句,则通常会g , ] ^ 7发生这种情况。官方解释:”为了处理查询,MySQL需求创立一个暂时表来容纳效果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。很明显就是通过wh& Z g W `ere条件一次性检索出来的效果集太大了,内存放不下了,只能通B c Z过加暂时表来辅佐处理。

Usiu ^ 0 c ong where

标明当where过滤条件中的字段无索引时,MySQL Sever层接收到存储引n 2 C s擎(例如innodb)的效果集后,根据6 l ) & swhere条件中的条件f [ H . _ 4 m o @进行过滤。

Using index condition

Using index condition 会先条件过滤索引,过滤完索引后找到全部符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行;

我们的实践案例中,其实 o b就是走单个索引idx_createTime时,只能从索引中查出 满足a.create_tiW ; v &me between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'条件的主键id,然后进行回表,因为idx_createTime索引中没有status的信息,只能回表后查出全部的主键id对应的行。然后innodb将效果集回来给MySQL Sever,MySQL Sever根据status字段进行过滤,9 ] } @ i q j筛选出status为1的字段,所以第一个查询的Explain效果中的Extra才会显现Using where。

filtered字段标明存储引擎回来! D 6的数据在server层过滤后,剩下多少满足查询@ . D的记载数量的比例,这个是预估值,因为staF F @ ) y 1 xtus取值是null! [ a V 2 ! ( i,1,2,3j i % q L,4,所以这儿给的25%。

所以第二4 D : S L个查询与第一个查询的差异首要在于一初步去idx_cb O 7reateTD E &ime_statuP r B G M @ Ss查到的效果集就是满足status是1E s c的id,所以去调集索引下进行回表查询时,扫描的行数会少许多(大约是2.7万行与15万行的差异),之后innodb回来给MySQL Server的数据就是满足条件status是1的效果集(2.7万行),不用再进行筛选了,所以第二个查询才会快这么多,时` o L s + k !刻是优化前的23%。(两种查询办法的EXPLAIN预估扫描行数都是30万行左右是因为idx_createTime_status只射中了createTime,因为createTime不是查单个值,查的是规划)

//查询效果行数是15万行左右
SELECT count(*) from article aY U J X
where a.post_time
between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'
//查询效果行数是2万6行左右
SELECT count(*) from article a
where a.post_time
between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'
and a.audit_status = 1

发散考虑:假定将联合索引(createTime,status)改成(status,createTime)会怎么样?

where
a.creaF z d R E F yte_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

根据最左匹配的原则,因为我们的where查询条件是这样,假定是(createTime,status)那么索引就只能用到createTime,假定是(status,createT4 r l . dime),因为status是查询单个值,所以status,createTime都可以射中,在(status1 j + &,createTime)索引中扫o * +描行数会削减,但是因为(createTime,status)这个索引自身值包含createTime,status,id三个字段的信息,数据量比较小,而一个数据页是16k,可以存储1000个以上的索引数据节点,而且是查询到createTime后,进行的次第IO,所以读取比较快,总得的查询时间两者根本是一起。4 x } / G 4下面是测验效果:

首要创立了(status,createTime)名叫idx_status_createTo ) )ime,

SELECT
a.id as article_id ,
a.title as title ,
a.autho8 - ( l a f m Ar_id as author_id
from
article a  FORE ^ 2 z h y ZCE INDEX(idx_status_createTime)
where
a.create_time be* _ # d E 9 itween '2020-03-22 03:00:00.003'
a0 B ) _ { ! 1 Xnd '2020-04-22 03:00:00.003'
and a.status = 1

查询时间是0.21,跟第二种办法(createTime,status)索引的查询时间根本一起。

Explain效果对比:

id type kI z C *ey key_len rows filtered Extra
2 range idx_createTime_status 6 310812 100.00 Using index condition
3 range idx_status_createTime 6 52542 100.00 Usin8 1 m @ n sg index conditioB 3 @ 5n

扫描行数的确会少一些,因为在idx_status_createT, } L uime的索引中,一初步根据status = 1扫除掉了stag = 1 8 Ztus取值为其他值的情况。

发表评论

提供最优质的资源集合

立即查看 了解详情