记一次mysql慢查询优化

问题背景

线上监控发现mysql慢查询,mysql最大执行时间60s,接口p99耗时1.88s

优化记录

检查索引

show create table xxx

线上query条件比较复杂,有接近10个查询字段,主要字段都已经加索引

检查索引顺序

explain select x from x where 1=1  and xxx and xxx2 order by x limit x offet x

抓取log中的慢查询sql,发现已经命中索引,并且sql中无复杂计算,索引条件生效,过滤数据从百万减少到千级
sql中的1=1是为了方便拼接where条件,防止where空查询

进一步定位

将where条件拆分排列组合,发现加了sort by id后导致查询很慢
检查表内容,发现有一个text字段特别大,

检查mysql排序变量大小

show VARIABLES like '%sort%'

其中 sort_buffer_size=262144,初步定位问题是字段太大,排序缓冲太小,导致排序变慢

sort_buffer_size:是MySql执行排序使用的缓冲大小。 如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。 如果不能,可以尝试增加sort_buffer_size变量的大小。 read_rnd_buffer_size:是MySql的随机读缓冲区大小。

优化

修改sql条件,先查id再查具体的内容,只通过id过滤可以减少排序缓冲占用,另外也可以增加 sort_buffer_size 大小

select x from x where id in (select id from x where xxx limit x offset x)

结果

接口p99和avg从1.8s减少到900ms,优化 50%+

posted @ 2021-08-06 11:34  致林  阅读(61)  评论(0编辑  收藏  举报