Slow query 慢查询 是指 执行很慢的SQL语句。一般会设置一个阈值，例如，100ms，执行时间超过100ms的都会判定为慢查询。 慢查询是一种危险的信号，MySQL 服务可能很快不可用。当大量出现的时候，应该立即kill。
下文主要介绍如何批量kill 慢查询。
首先，查看有哪些慢查询的SQL：
select * from information_schema.processlist where command='query' and time > 20;
其中，限定条件中加了command 和 time。
有时候，也不会限制command。
因为，对于慢查询，原因可能是空闲连接仍然占有锁，也需要注意。
例如，下面的语句，空闲连接占用锁，导致慢查一直在增加：
mysql >select * from information_schema.processlist where time > 20;
+------------+---------------------+--------------------+--------------+---------+------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | ROWS_SENT | ROWS_EXAMINED |
+------------+---------------------+--------------------+--------------+---------+------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
| 1073085839 | orange_server | 10.88.6.26:46595 | orchestrator | Query | 24 | updating |
delete from
database_instance_maintenance
where
maintenance_active is null
and end_timestamp < NOW() - INTERVAL 7 DAY | 23850 | 0 | 0 |
| 1073085838 | orange_server | 10.88.6.26:46600 | orchestrator | Query | 27 | updating |
delete from
database_instance_maintenance
where
maintenance_active is null
and end_timestamp < NOW() - INTERVAL 7 DAY | 26850 | 0 | 0 |
| 1073084469 | orange_manager | 10.1.10.68:32852 | orchestrator | Sleep | 1806 | | NULL | 1805814 | 16 | 16 |
| 1073085824 | orange_server | 10.88.6.26:46594 | orchestrator | Query | 21 | updating |
delete from
database_instance_maintenance
where
maintenance_active is null
and end_timestamp < NOW() - INTERVAL 7 DAY | 20850 | 0 | 0 |
| 1073085828 | orange_server | 10.88.6.26:46615 | orchestrator | Query | 25 | updating |
delete from
database_instance_maintenance
where
maintenance_active is null
and end_timestamp < NOW() - INTERVAL 7 DAY | 24850 | 0 | 0 |
| 1073085829 | orange_server | 10.88.6.26:46605 | orchestrator | Sleep | 30 | | NULL | 29850 | 0 | 0 |
| 1073085769 | orange_server | 10.88.6.26:46547 | orchestrator | Query | 26 | updating |
delete from
database_instance_maintenance
where
maintenance_active is null
and end_timestamp < NOW() - INTERVAL 7 DAY | 25850 | 0 | 0 |
| 1073085833 | orange_server | 10.88.6.26:46614 | orchestrator | Query | 23 | updating |
delete from
database_instance_maintenance
where
maintenance_active is null
and end_timestamp < NOW() - INTERVAL 7 DAY | 22850 | 0 | 0 |
| 1073085782 | orange_server | 10.88.6.26:46545 | orchestrator | Query | 22 | updating |
delete from
database_instance_maintenance
where
maintenance_active is null
and end_timestamp < NOW() - INTERVAL 7 DAY | 21850 | 0 | 0 |
| 1073085825 | orange_server | 10.88.6.26:46601 | orchestrator | Query | 29 | updating |
delete from
database_instance_maintenance
where
maintenance_active is null
and end_timestamp < NOW() - INTERVAL 7 DAY | 28851 | 0 | 0 |
| 1073085785 | orange_server | 10.88.6.26:46553 | orchestrator | Query | 28 | updating |
delete from
database_instance_maintenance
where
maintenance_active is null
and end_timestamp < NOW() - INTERVAL 7 DAY | 27851 | 0 | 0 |
+------------+---------------------+--------------------+--------------+---------+------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
11 rows in set (0.02 sec)
当出现大量慢查的时候，很可能将MySQL 服务拖垮。当机立断，应该赶紧kill 慢查询。
批量kill 慢查的方式如下，首先找到慢查询id，然后进行kill。
以下SQL，可以组合成kill 语句，直接拷贝执行即可。
mysql >select concat('kill ', id,';') from information_schema.processlist where time > 20;
+-------------------------+
| concat('kill ', id,';') |
+-------------------------+
| kill 1073080655; |
| kill 1073085823; |
| kill 1073084469; |
| kill 1073085837; |
| kill 1073085778; |
| kill 1073085869; |
| kill 1073085772; |
| kill 1073085787; |
| kill 1073085786; |
| kill 1073085780; |
| kill 1073085779; |
| kill 1073085858; |
+-------------------------+
12 rows in set (0.03 sec)
以上，介绍了批量kill 慢查询的方式。