MySQL性能分析(四)之processlist详解

一、概述

进程列表表(information_schema.processlist表或SHOW PROCESSLIST命令)是MySQL的核心表之一，用于查看MySQL当前所有的连接进程，帮助管理员监控数据库活动并排查性能问题。

进程列表表是进程信息的来源之一INFORMATION_SCHEMA库中的PROCESSLIST在8.0以后的版本被标记为弃用，并将在未来的MySQL版本中被移除。因此，使用此表的SHOW PROCESSLIST的实现也已被弃用。建议改用performance_schema实现的processlist。

在结构上，两张表的结构基本相同，高版本中performance_schema下的表比information_schema中表多一个execution_engine(执行引擎)字段

processlist表可以直接被访问。如果用户拥有PROCESS权限，则可以访问所有线程的信息（包含其他用户的线程信息）。非匿名用户可以访问自己的线程信息、但不能访问其他用户的线程信息，匿名用户无法访问processlist中的线程信息。

二、命令详解

通过processlist表，可以查看当前服务器正在执行的线程信息。

mysql> select * from performance_schema.processlist\G;
*************************** 1. row ***************************
ID: 5
USER: event_scheduler
HOST: localhost
DB: NULL
COMMAND: Daemon
TIME: 21411
STATE: Waiting on empty queue
INFO: NULL
EXECUTION_ENGINE: PRIMARY
*************************** 2. row ***************************
ID: 714
USER: root
HOST: localhost
DB: performance_schema
COMMAND: Query
TIME: 0
STATE: executing
INFO: select * from performance_schema.processlist
EXECUTION_ENGINE: PRIMARY
2 rows in set (0.00 sec)

三、表结构与字段说明

表结构：

mysql> desc processlist;
+------------------+-----------------------------+------+-----+---------+-------+
| Field            | Type                        | Null | Key | Default | Extra |
+------------------+-----------------------------+------+-----+---------+-------+
| ID               | bigint unsigned             | NO   | PRI | NULL    |       |
| USER             | varchar(32)                 | YES  |     | NULL    |       |
| HOST             | varchar(261)                | YES  |     | NULL    |       |
| DB               | varchar(64)                 | YES  |     | NULL    |       |
| COMMAND          | varchar(16)                 | YES  |     | NULL    |       |
| TIME             | bigint                      | YES  |     | NULL    |       |
| STATE            | varchar(64)                 | YES  |     | NULL    |       |
| INFO             | longtext                    | YES  |     | NULL    |       |
| EXECUTION_ENGINE | enum('PRIMARY','SECONDARY') | YES  |     | NULL    |       |
+------------------+-----------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

• ID：连接标识符。这是在SHOW PROCESSLIST语句的Id列中显示的相同值，显示在Performance Schema线程表的PROCESSLIST_ID列中，并在线程内通过CONNECTION_ID()函数返回的值相同。
• USER：发出该语句的MySQL用户。system user的值指的是由服务器生成的非客户端线程，用于在内部处理任务，例如延迟行处理线程或在复制主机上用于处理I/O或SQL的线程。对于system user，Host列中没有指定主机。unauthenticated user指的是已与客户端连接关联但尚未对客户端用户进行身份验证的线程。event_scheduler指的是监视预定事件的线程。
• HOST：发出该语句的客户端的主机名（对于system user则没有主机）。对于TCP/IP连接，主机名以host:port(主机地址:端口)的格式显示，以便更容易确定哪个客户端在执行什么操作。
• DB：线程的默认数据库，如果未选择任何数据库，则为NULL。
• COMMAND：线程代表客户端执行的命令类型，如果会话处于空闲状态，则为Sleep。此列的值对应于客户端/服务器协议的COM_xxx命令和Com_xxx状态变量。
• TIME：线程在当前状态下已经经过的时间，以秒为单位。对于复制SQL线程，该值是上一个复制事件的时间戳与复制主机的实际时间之间的秒数。
• STATE：一个动作、事件或状态，表示线程正在进行的操作。大多数状态对应非常快速的操作。如果一个线程在特定状态停留了很多秒，可能存在需要调查的问题。
• INFO：线程正在执行的语句，如果没有执行语句则为NULL。该语句可以是发送给服务器的语句，或者如果该语句执行其他语句，则为最内层的语句。
• EXECUTION_ENGINE：查询执行引擎。该值可以是PRIMARY或SECONDARY。在MySQL HeatWave Service和HeatWave中使用，其中PRIMARY引擎是InnoDB，而SECONDARY引擎是HeatWave（RAPID）。对于社区版本、企业版（本地部署）以及没有HeatWave的MySQL HeatWave Service，该值始终为PRIMARY。

3.1 COMMAND字段说明

• Binlog Dump 表示这是一个在复制源上的线程，负责将二进制日志内容发送到副本
• Change user 表示该线程正在执行一个改变用户操作
• Close stmt 该线程正在关闭一个prepared statement。
• Connect 被连接到源的复制接收线程和复制工作线程使用。
• Connect Out 一个副本正在连接到其源。
• Create DB 正在执行一个创建数据库操作
• Daemon 该线程是服务器内部的线程，而不是为客户端连接提供服务的线程
• Debug 线程正在生成调试信息
• Delayed insert 该线程是一个延迟写入处理程序
• Drop DB 该线程正在执行删除数据库操作
• Error 错误
• Execute 线程正在执行prepared statement
• Fetch 线程正在获取prepared statement的结果
• Field List 线程正在检索表列的信息
• Init DB 该线程正在选择默认数据库
• Kill 正在杀死另外一个线程
• Long Data 线程正在检索执行prepared statement的结果中的长数据
• Ping 该线程正在处理服务器ping请求
• Prepare 线程正在准备prepared statement
• Processlist 该线程正在生成有关服务器线程的信息。
• Query 在单线程复制应用程序线程以及复制协调器线程执行query时，用于用户客户端。
• Quit 线程正在终止。
• Refresh 线程正在刷新表、日志或缓存，或重置状态变量或复制服务器信息
• Register 从属线程正在注册副本服务器
• Reset stmt 线程正在重置prepared statement。
• Set option 线程正在设置或重置客户端语句执行选项。
• Shutdown 线程正在关闭服务器。
• Sleep 线程正在等待客户端向其发送新语句。
• Statistics 线程正在生成服务器状态信息。
• Time （目前未使用该状态）

COMMAND字段说明参考：https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html

3.2 STATE字段状态说明

• Sleep：连接处于空闲状态，没有正在执行的命令。
• Query：连接正在执行一个查询。
• Locked：连接正在等待锁定资源。
• Copying to tmp table：连接正在将结果复制到临时表中。
• Sending data：连接正在发送查询结果给客户端。
• Sorting result：连接正在对结果进行排序。
• Waiting for table flush：连接正在等待表的刷新。
• Repair by sorting：连接正在执行表的修复操作。
• Creating sort index：连接正在创建排序索引。

STATE字段说明参考：https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

四、监控示例

查询当前连接数

select count(*) from information_schema.processlist;

查询每个用户的连接数

select user, count(*) as conn_count
from information_schema.processlist
group by user
order by conn_count desc;

查询当前执行 SQL 语句最多的 IP

SELECT HOST, COUNT(*) AS conn_count 
FROM information_schema.processlist 
WHERE COMMAND != 'Sleep' 
GROUP BY HOST 
ORDER BY conn_count DESC;

查询执行时间超过 10 秒的 SQL

SELECT * FROM information_schema.processlist WHERE COMMAND='Query' AND TIME > 10;

查询所有正在执行的 SQL

SELECT ID, USER, HOST, DB, TIME, STATE, INFO 
FROM information_schema.processlist 
WHERE COMMAND='Query';

终止某个慢查询

KILL 12345;

kill掉这个链接的线程，具体看官网文档：https://dev.mysql.com/doc/refman/5.7/en/kill.html

拼接kill执行语句

-- 查询执行时间超过2分钟的线程，然后拼接成 kill 语句
select concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep'
and time > 2*60
order by time desc

五、总结

information_schema.processlist是MySQL监控的重要工具。提供所有连接的详细信息，结合COMMAND、STATE、TIME、INFO字段，可以分析慢查询、找出死锁、清理空闲连接，优化数据库性能。