mysql 慢查询调优实战

异常现象:

报错慢查询
原sql:

select id, infoid, orderid, utel, stel, refusetime, reasoncd, reason, deleteflag, params, cityid, paidanid, cateid, baojieworkertype, `operator`, refusesource, utel_encrypt, stel_encrypt 
from t_app_refusereason 
WHERE ( cityid = 18 and refusetime >= '2024-01-22 00:00:00' and refusetime <= '2024-01-23 00:00:00' and reasoncd in ( 38 , 52 , 51 , 10 , 9 ) and deleteflag = 1 ) 
order by id asc 
LIMIT 50;

索引信息:

 KEY `idx_refusetime_reasoncd_deleteflag_cityid` (`refusetime`,`reasoncd`,`deleteflag`,`cityid`) USING BTREE

进行复现:

发现确实特别慢

解析sql,发现走的是主键索引

在上线之前校验过,是可以走索引的,可能由于最近数据量的变化,mysql的优化策略变更导致不走二级索引量

寻找原因:

在 MySQL 中,当执行带有 ORDER BY 子句的查询时,数据库引擎可能会选择不使用二级索引而进行全表扫描。
这是因为在包含排序的情况下,数据库引擎可能认为通过覆盖索引并不会带来额外的性能提升,而直接进行全表扫描可能更为高效。

解决办法:

办法一:指定执行索引

SELECT id, infoid, orderid, utel, stel, refusetime, reasoncd, reason, deleteflag, params, cityid, paidanid, cateid, baojieworkertype, `operator`, refusesource, utel_encrypt, stel_encrypt 
FROM t_app_refusereason 
FORCE INDEX (idx_refusetime_reasoncd_deleteflag_cityid) 
WHERE (cityid = 18 AND refusetime >= '2024-01-22 00:00:00' AND refusetime <= '2024-01-23 00:00:00' AND reasoncd IN (38, 52, 51, 10, 9) AND deleteflag = 1) 
ORDER BY id ASC 
LIMIT 50;

办法二:先按二级索引里字段排序,再按id排序

SELECT id,infoid,orderid,utel,stel,refusetime,reasoncd,reason,deleteflag,params,cityid,paidanid,cateid,baojieworkertype,`operator`,refusesource,utel_encrypt,stel_encrypt 
FROM t_app_refusereason 
WHERE ( cityid = 18 AND refusetime >= '2024-01-22 00:00:00' AND refusetime <= '2024-01-23 00:00:00' AND reasoncd IN ( 38, 52, 51, 10, 9 ) AND deleteflag = 1 ) 
ORDER BY refusetime ASC ,id ASC 
LIMIT 50;

办法三:取消排序

SELECT id,infoid,orderid,utel,stel,refusetime,reasoncd,reason,deleteflag,params,cityid,paidanid,cateid,baojieworkertype,`operator`,refusesource,utel_encrypt,stel_encrypt 
FROM t_app_refusereason 
WHERE ( cityid = 18 AND refusetime >= '2024-01-22 00:00:00' AND refusetime <= '2024-01-23 00:00:00' AND reasoncd IN ( 38, 52, 51, 10, 9 ) AND deleteflag = 1 ) 
LIMIT 50;

原sql、方案一、方案二、方案三

最终方案:

最终选择办法三,取消排序

复盘:

为什么之前要排序?

之前遇到一种场景,在查询es时没有排序,在返回结果的边界时会出现乱序。比如分页50条查询,数据A和B都符合筛选条件,查第一页的时候数据A返回在第50条,查第二页的时候第一条希望返回数据B,结果返回的还是数据A

因为es获取数据是在不同片区拿数据,如果不指定排序字段会出现不稳定排序现象。

但是mysql索引是稳定的,所以无特殊排序要求的时候直接拿就可以了

转载请说明出处内容投诉
CSS教程_站长资源网 » mysql 慢查询调优实战

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买