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)

TIME
连接的在当前状态(STATE)的持续时间，单位为秒。
注意是“当前状态的持续时间”。

官方文档释义：
The time in seconds that the thread has been in its current state. For a slave SQL thread, the value is the number of seconds between the timestamp of the last replicated event and the real time of the slave machine.

一般状态变化非常快、在每个状态持续时间很短，如果持续多秒，说明就出现了问题。所以这个设定，让时间成为判断SQL是否正常的关键要素。如果TIME是连接存在的时间，那么就失去了这个意义了。

参考

MySQL processlist的state属性详解
https://learn.blog.csdn.net/article/details/98624463

MySQL使用Kill命令杀死/清理慢连接
https://learn.blog.csdn.net/article/details/98622192

MySQL基于information_schema的性能分析样例（监控连接数、数据量、事务和锁）
https://learn.blog.csdn.net/article/details/90261028

MySQL性能分析之show processlist及information_schema.processlist详解
https://learn.blog.csdn.net/article/details/98595277