Mysql 简明教程

MySQL - Show Processlist

MySQL 数据库提供了一个多用户环境，允许多个客户端同时访问数据库。一个进程被定义为一个用户在 MySQL 服务器上执行的操作。多个用户可以同时在 MySQL 服务器上同时运行多个进程。

MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. A process is defined as the operations performed by a user on the MySQL Server. Multiple processes can be run on a MySQL Server concurrently by multiple users.

What is MySQL Process List?

MySQL process list 被定义为当前由在服务器内执行的用户线程集执行的操作列表。

The MySQL process list is defined as the list of operations currently being performed by the set of user threads executing within the server.

如果用户拥有 PROCESS 特权，他们可以看到服务器上的所有线程，包括其他用户的线程。但是，如果一个用户没有这样的特权，则非匿名用户只能访问有关他们自己的线程的信息；而匿名用户不能访问线程信息。

If a user has the PROCESS privilege, they can see all threads in a server, including threads of other users. But if a user does not have such privilege, non-anonymous users have access to information about their own threads only; while anonymous users have no access to thread information.

要检索有关在 MySQL 服务器上运行的这些进程的信息，我们可以使用 SHOW PROCESSLIST 命令。

To retrieve information about these processes running on a MySQL Server, we can use the SHOW PROCESSLIST command.

The MySQL SHOW PROCESSLIST Command

MySQL SHOW PROCESSLIST 命令用于显示有关在 MySQL 服务器上运行的当前进程的信息。

The MySQL SHOW PROCESSLIST command is used to display information about the current processes running on a MySQL Server.

这个语句在处理“太多连接”错误时特别有用，因为它提供了有关这些连接及其操作的详细信息。此外，MySQL 为具有 CONNECTION_ADMIN 特权（或旧版本中的 SUPER 特权）的管理员保留了一个额外的连接，以确保他们始终可以访问系统。

This statement is especially useful when dealing with a "too many connections" error, as it provides details about these connections and their operations. Additionally, MySQL reserves one extra connection for administrators with CONNECTION_ADMIN privilege (or SUPER privilege in older versions), to ensure they can always access the system.

Syntax

以下是 SHOW PROCESSLIST 命令的语法 −

Following is the syntax of the SHOW PROCESSLIST Command −

SHOW [FULL] PROCESSLIST

这里，FULL 关键字是可选的。但是，如果您省略 FULL 关键字，SHOW PROCESSLIST 仅显示信息字段中每个语句的前 100 个字符。

Here, the FULL keyword is optional. But if you omit the FULL keyword, SHOW PROCESSLIST displays only the first 100 characters of each statement in the Info field.

Example

让我们看一个示例来说明 SHOW PROCESSLIST 命令的用法。我们将使用 '\G' 分隔符垂直打印信息表 −

Let us see an example to show the usage of SHOW PROCESSLIST command. We will use the '\G' delimiter to print the information table vertically −

SHOW PROCESSLIST\G

Output

以下是所获得的输出 −

Following is the output obtained −

*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 1065209
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 56
   User: root
   Host: localhost:51995
     db: customers
Command: Query
   Time: 0
  State: init
   Info: SHOW PROCESSLIST
2 rows in set (0.00 sec)

Example

现在，我们还可以在 SHOW PROCESSLIST 命令中使用 FULL 关键字，如下例所示 −

Now, let us also use the FULL keyword with the SHOW PROCESSLIST command as shown in the following example −

SHOW FULL PROCESSLIST\G

Output

获得的输出如下所示 −

The output obtained is as shown below −

*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 1065138
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 56
   User: root
   Host: localhost:51995
     db: customers
Command: Query
   Time: 0
  State: init
   Info: SHOW FULL PROCESSLIST
2 rows in set (0.00 sec)

Output Explanation

从 SHOW PROCESSLIST 命令获得的输出结果集具有以下列 −

The output result-set obtained from the SHOW PROCESSLIST command has the following columns −

Id − It is the identity of a connection.
User − This holds the name of a MySQL user who issued the statement.
Host − The host name of the client issuing the statement (except for system user, as there is no host for it). The host name for TCP/IP connections is represented in "host_name:client_port" format to make it easier to determine the actions of a client.
db − This is the default database for the thread, or NULL if none has been selected.
Command − Shows the type of command the corresponding thread is executing on behalf of the client, or shows Sleep if the session is idle.
Time − The time in seconds that the thread has been in its current state.
State − An action, event, or state that indicates what the thread is doing. Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.
Info − The statement the thread is executing. If it is executing no statement, NULL is shown.

Showing Process List Using Client Program

我们还可以使用客户端程序来展示进程列表。

We can also show the process list using Client Program.

Syntax

Example

以下是这些程序 −

Following are the programs −