ADADADADAD

MySQL 管理长时间运行查询[ mysql数据库 ]

mysql数据库时间：2024-12-25 09:55:48

作者：文/会员上传

如何提高mysql查询效率

简介：

最常用的查询select concat ('kill ',id,';') from information_schema.processlistwhere time >= 2-- and user = '业务账号'and command not in ('sleep','Connect')and s

以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。

最常用的查询

select concat ('kill ',id,';') from information_schema.processlistwhere time >= 2-- and user = '业务账号'and command not in ('sleep','Connect')and state not like ('waiting for table%lock');and info like '%Metabase%'mysql -uroot -s -N -p -h-e "select concat ('kill ',id,';') from information_schema.processlist where INFO like 'SELECTxxxFROM%' " > kill.sql

RDS提供的存储过程：

create event my_long_running_query_monitoron schedule every 5 minutestarts '2015-09-15 11:00:00'on completion preserve enable dobegindeclare v_sql varchar(500);declare no_more_long_running_query integer default 0;declare c_tid cursor forselect concat ('kill ',id,';') from information_schema.processlistwhere time >= 3600and user = substring(current_user(),1,instr(current_user(),'@')-1)and command not in ('sleep')and state not like ('waiting for table%lock');declare continue handler for not foundset no_more_long_running_query=1;open c_tid;repeatfetch c_tid into v_sql;set @v_sql=v_sql;prepare stmt from @v_sql;execute stmt;deallocate prepare stmt;until no_more_long_running_query end repeat;close c_tid;end;

参考：https://help.aliyun.com/knowledge_detail/41735.html?spm=a2c4g.11186631.2.20.51106998SvntYb

RDS中的参数

loose_max_statement_time

管理长查询的shell脚本

#!/bin/bashpassword=xxxxxxmysql -uroot -p$password -N -s -e "select concat ('kill ',id,';') frominformation_schema.processlist where time >= 300 -- and user = '业务账号' and command not in ('sleep','Connect')and state not like ('waiting for table%lock');" > killmysqlsession.txt#cat killmysqlsession.txt | while read line#do#echo $line#mysql -uroot -p$password -e "$line"#donemysql -uroot -p$password < killmysqlsession.txt#或者登陆实例source killmysqlsession.txt