资深java工程师写mysql表子查询left join导致大事务-线上事故
背景
项目组来了一位资深java同学,分配第一个需求上线,上线后发现sql导致慢sql,立马就把版本回滚了,但是回滚后发现,还是慢....,所有的从库执行的sql都是超级慢的.....
上线的sql如下:
select
id, item_id, item_name, is_use, item_category_level_oms1, item_category_level_oms2,
item_category_level_oms3, item_category_level_oms4, item_type, sub_type, package_id,
package_type, item_channel, manage_type, trade_mode, tax_type, item_brand, item_brandName,
quanlity_five_hundred, addKnowledge, item_category_level_oms1_name, item_category_level_oms2_name,
item_category_level_oms3_name, item_category_level_oms4_name, tag_id, store_code
,
item_img_s
from t_ambient_x where item_id in (
select distinct(c.item_id) from t_ambient_x c left join t_ambient_x2 d
on c.item_id = d.data_source_id
and d.data_source_type = 1
WHERE c.item_name like CONCAT('%','荣耀V40 双超级快充 5000万超感光影像 5G手机','%')
)
order by addKnowledge desc
limit 0, 30
全表扫描.....
每次查询最少要3s。。。。
处理方案
1.回滚上个迭代版本->发现还是慢...查询还是失败
然后就奇了怪了,为啥回滚还是找不到具体报错.....查看之间是否会这样,发现发版前一切正常。
查看调用的超时的接口记录,发现居然一个count要9秒多,我去,这个库才60万左右的数据,也不致于吧.....
然后查看数据库相关的配置,发现一主,一从,从的配置比主的低4倍的配置,但是那也不至于啊~
排查下CPU,发现的确有打100%的情况。
先去主库查询发现:0.07s,但是从库发现6s
主:
从:
问题找到
难道从库有问题?
原来为什么没有问题现在有问题?
继续排查,发现原来从库有一个事务还在执行中...还是下午还在执行的....
最后发现,原来是本次上线的版本的sql存在慢sql问题导致前端页面在查询的时候所有这个页面的接口都超时,但是其他接口不会,因为一出问题,就一直在那里刷 ,所以堆积了115个大事务在互相抢CPU,只有单核的CPU互相都在抢资源所以count的时候都超时了,所以.....禁止lefjoin全表扫,特别n * n 这种.....
优化方案及处理措施:
1.将关于复杂查询拆成一个一个小查询,尽量避免关联查询,特别是n * n 这种;
2.将必要的字段加上索引;
3.尽量用like 字段 前缀不加 % 否则索引失效;
4.重复查询数据加上缓存,redis或guava;
5.核心或复杂代码提交发版前,让同组同事互相codeView;