MySQL 如何使用show processlist进行过滤

港控/mmm° 2023-06-07 12:07 7阅读 0赞

在使用show processlist的时候，直接使用会显示很多的内容，无法很快找到需要的信息。

如何过滤操作呢？

其实，show processlist展示的内容是从`information_schema.processlist`数据表查询得到。

数据表`information_schema.processlist`各字段如下：

所以可以使用SQL语句的条件查询进行过滤，例如:

>select * from information_schema.processlist where Host like '10.26.201.199%';
    +---------+-------------+---------------------+------+---------+------+-------+------+---------+-----------+---------------+
    | ID      | USER        | HOST                | DB   | COMMAND | TIME | STATE | INFO | TIME_MS | ROWS_SENT | ROWS_EXAMINED |
    +---------+-------------+---------------------+------+---------+------+-------+------+---------+-----------+---------------+
    |  315835 | ora_manager | 10.26.201.199:45028 | NULL | Sleep   | 2796 |       | NULL | 2795673 |         1 |             0 |
    | 1354509 | ora_manager | 10.26.201.199:53340 | NULL | Sleep   |  976 |       | NULL |  975677 |         1 |             0 |
    |  240640 | ora_manager | 10.26.201.199:45802 | NULL | Sleep   |    4 |       | NULL |    3684 |         1 |             0 |
    | 1354510 | ora_manager | 10.26.201.199:53342 | NULL | Sleep   |  976 |       | NULL |  975678 |         1 |             0 |
    |  240641 | ora_manager | 10.26.201.199:45810 | NULL | Sleep   |    4 |       | NULL |    3687 |         1 |             0 |
    |  315836 | ora_manager | 10.26.201.199:45032 | NULL | Sleep   | 2796 |       | NULL | 2795672 |         1 |             0 |
    | 1354511 | ora_manager | 10.26.201.199:53344 | NULL | Sleep   |  976 |       | NULL |  975678 |         1 |          1103 |
    |  315837 | ora_manager | 10.26.201.199:45030 | NULL | Sleep   | 2796 |       | NULL | 2795674 |         1 |          1103 |
    |  240642 | ora_manager | 10.26.201.199:45812 | NULL | Sleep   |    4 |       | NULL |    3687 |         1 |          1103 |
    +---------+-------------+---------------------+------+---------+------+-------+------+---------+-----------+---------------+
    9 rows in set (0.00 sec)