概述
分享下最近因一条慢sql导致的数据库卡顿,因为是新项目,所以坑比较多。下面一起来看看吧!
服务器层面
项目经理反映说下午的时候系统很卡,观察了一下服务器,发现mysqld进程占了很大一部分资源,像这种情况80%以上都是sql引起的。
数据库层面
通过 show processlist 一看,发现很多是 Waiting for table metadata lock 状态的连接。
MySQL在进行一些alter table等DDL操作时,如果该表上有未提交的事务则会出现 Waiting for table metadata lock ,而一旦出现metadata lock,该表上的后续操作都会被阻塞。
慢查询
观察了一下当前数据库慢查询sql的情况,发现有一条sql执行需要到600秒
--200多行SELECT( CASE WHEN ( category.equipment_type = 'T' OR product.is_pallet = TRUE ) THEN 1 ELSE 0 END ) AS is_pallet,( CASE WHEN ( ob.project_code = 'FS' ) THEN 0 ELSE 1 END ) AS is_create_pallet,( pallet.capa city_length * pallet.capacity_width * pallet.capacity_height ) AS max_pallet_volume,... pallet.weight AS pallet__weight,pallet.capacity_length AS pallet__capacity_length,pallet.capacity_width AS pallet__capacity_width,pallet.capacity_height AS pallet__capacity_height FROMfsl_order_base_line AS pack_boxes_order_base_linesLEFT JOIN fsl_order_base AS ob ON pack_boxes_order_base_lines.order_base = ob.idLEFT JOIN fsl_thing_type AS product ON pack_boxes_order_base_lines.product = product.idLEFT JOIN fsl_thing_type AS category ON product.category = category.idLEFT JOIN fsl_thing_type AS pallet ON product.pallet = pallet.id WHERE(( ( ( ob.auto_releasing = 'Y' ) AND ( ob.project_code = 'DD' ) ) AND ( ob.order_status = '0' ) ) AND ( ob.model1 = 'pack' ) ) AND pack_boxes_order_base_lines.domain_name IN ( 'FSL' ) ORDER BYpack_boxes_order_base_lines.insert_date DESC,pack_boxes_order_base_lines.id DESC;
改写sql
set profiling=1;--改写如下:select .....pallet.capacity_length AS pallet__capacity_length,pallet.capacity_width AS pallet__capacity_width,pallet.capacity_height AS pallet__capacity_height FROM(select * from fsl_order_base_line where domain_name IN ( 'FSL' ) ) AS pack_boxes_order_base_linesLEFT JOIN (select * from fsl_order_base where auto_releasing = 'Y' and project_code = 'FS' and order_status = '0' and model1 = 'pack') AS ob ON pack_boxes_order_base_lines.order_base = ob.idLEFT JOIN fsl_thing_type AS product ON pack_boxes_order_base_lines.product = product.idLEFT JOIN fsl_thing_type AS category ON product.category = category.idLEFT JOIN fsl_thing_type AS pallet ON product.pallet = pallet.id ORDER BYpack_boxes_order_base_lines.insert_date DESC,pack_boxes_order_base_lines.id DESC;
其实原理就是缩小下结果集,调增后就剩100秒了。
对应的执行计划:
建立索引?
pt-online-schema-change --user=root -pxxx --host=1xxxx --alter "add INDEX idx_domain(domain_name)" D=test,t=fsl_order_base_line --execute
最新执行计划:
效果不大,选择去掉索引
pt-online-schema-change --user=root -pxxxx --host=xxxx --alter "drop INDEX idx_domain" D=test,t=fsl_order_base_line --execute
业务确认
最后跟业务和开发确认后发现这是一个定时器的逻辑:
如果数据正常的话,一次会拿到很少的数据,但中间中断过,导致这次里面有10000+的数据,而代码去针对这批数据逐行执行一堆逻辑,所以会一直很慢,加上还有更新操作,就导致了这条sql执行的很慢。之前都是很快就跑完了。
最终结果
在代码上做个限制,一次性不获取全部数据,每次只获取500行,跑完就提交。然后sql上也做相应的优化。
觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~