MySQL - 显示进程列表

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

什么是 MySQL 进程列表？

MySQL 进程列表定义为当前正在由服务器中执行的一组用户线程执行的操作列表。

如果用户具有 PROCESS 权限，他们可以查看服务器中的所有线程，包括其他用户的线程。但是，如果用户没有此权限，非匿名用户只能访问其自身线程的信息；而匿名用户则无法访问线程信息。

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

MySQL SHOW PROCESSLIST 命令

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

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

语法

以下是 SHOW PROCESSLIST 命令的语法：

SHOW [FULL] PROCESSLIST

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

示例

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

SHOW PROCESSLIST\G

输出

以下是获得的输出：

*************************** 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)

示例

现在，让我们也尝试在 SHOW PROCESSLIST 命令中使用 FULL 关键字，如下例所示：

SHOW FULL PROCESSLIST\G

输出

获得的输出如下所示：

*************************** 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)

输出解释

从 SHOW PROCESSLIST 命令获得的输出结果集包含以下列：

• Id - 它是连接的标识。

• User - 这包含发出语句的 MySQL 用户的名称。

• Host - 发出语句的客户端的主机名（系统用户除外，因为它没有主机）。TCP/IP 连接的主机名以“主机名:客户端端口”格式表示，以便更容易确定客户端的操作。

• db - 这是线程的默认数据库，如果没有选择任何数据库，则为 NULL。

• Command - 显示相应线程代表客户端执行的命令类型，如果会话处于空闲状态，则显示 Sleep。

• Time - 线程处于当前状态的秒数。

• State - 一个动作、事件或状态，指示线程正在执行的操作。大多数状态对应于非常快速的操作。如果线程在给定状态下停留许多秒，则可能存在需要调查的问题。

• Info - 线程正在执行的语句。如果它没有执行任何语句，则显示 NULL。

使用客户端程序显示进程列表

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

语法

$sql = "SHOW PROCESSLIST";
$mysqli->query($sql);

sql = "SHOW PROCESSLIST";
con.query(sql)

String sql = "SHOW PROCESSLIST";
statement.executeQuery(sql);

show_processlist_query = "SHOW PROCESSLIST"
cursorObj.execute(show_processlist_query)

示例

以下是程序示例：

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "SHOW PROCESSLIST";
if($result = $mysqli->query($sql)){
    printf("Show query executed successfully...!\n");
    printf("Process list: \n");
    while($row = mysqli_fetch_array($result)){
        print_r($row);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Show query executed successfully...!
Process list: 
Array
(
    [0] => 5
    [Id] => 5
    [1] => event_scheduler
    [User] => event_scheduler
    [2] => localhost
    [Host] => localhost
    [3] =>
    [db] =>
    [4] => Daemon
    [Command] => Daemon
    [5] => 886450
    [Time] => 886450
    [6] => Waiting on empty queue
    [State] => Waiting on empty queue
    [7] =>
    [Info] =>
)
Array
(
    [0] => 602
    [Id] => 602
    [1] => root
    [User] => root
    [2] => localhost:54978
    [Host] => localhost:54978
    [3] => tutorials
    [db] => tutorials
    [4] => Sleep
    [Command] => Sleep
    [5] => 2994
    [Time] => 2994
    [6] =>
    [State] =>
    [7] =>
    [Info] =>
)
Array
(
    [0] => 641
    [Id] => 641
    [1] => root
    [User] => root
    [2] => localhost:56444
    [Host] => localhost:56444
    [3] => tutorials
    [db] => tutorials
    [4] => Query
    [Command] => Query
    [5] => 0
    [Time] => 0
    [6] => init
    [State] => init
    [7] => SHOW PROCESSLIST
    [Info] => SHOW PROCESSLIST
)  

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    sql = "SHOW PROCESSLIST"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**usage of SHOW PROCESSLIST command**");
      console.log(result);
      console.log("--------------------------");
    });

    sql = "SHOW FULL PROCESSLIST"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**usage of FULL keyword with the SHOW PROCESSLIST command:**");
      console.log(result);
    });
});  

 
Connected!
--------------------------
**usage of SHOW PROCESSLIST command**
[
  {
    Id: 5,
    User: 'event_scheduler',
    Host: 'localhost',
    db: null,
    Command: 'Daemon',
    Time: 1279159,
    State: 'Waiting on empty queue',
    Info: null
  },
  {
    Id: 310,
    User: 'root',
    Host: 'localhost:64181',
    db: null,
    Command: 'Sleep',
    Time: 6,
    State: '',
    Info: null
  },
  {
    Id: 311,
    User: 'root',
    Host: 'localhost:64182',
    db: null,
    Command: 'Sleep',
    Time: 4,
    State: '',
    Info: null
  },
  {
    Id: 329,
    User: 'root',
    Host: 'localhost:64371',
    db: 'tutorials',
    Command: 'Query',
    Time: 0,
    State: 'init',
    Info: 'SHOW PROCESSLIST'
  }
]
--------------------------
**usage of FULL keyword with the SHOW PROCESSLIST command:**
[
  {
    Id: 5,
    User: 'event_scheduler',
    Host: 'localhost',
    db: null,
    Command: 'Daemon',
    Time: 1279159,
    State: 'Waiting on empty queue',
    Info: null
  },
  {
    Id: 310,
    User: 'root',
    Host: 'localhost:64181',
    db: null,
    Command: 'Sleep',
    Time: 6,
    State: '',
    Info: null
  },
  {
    Id: 311,
    User: 'root',
    Host: 'localhost:64182',
    db: null,
    Command: 'Sleep',
    Time: 4,
    State: '',
    Info: null
  },
  {
    Id: 329,
    User: 'root',
    Host: 'localhost:64371',
    db: 'tutorials',
    Command: 'Query',
    Time: 0,
    State: 'init',
    Info: 'SHOW FULL PROCESSLIST'
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ProcessList {
  public static void main(String[] args) {
    String url = "jdbc:mysql://127.0.0.1:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SHOW PROCESSLIST";
            rs = st.executeQuery(sql);
            System.out.println("Show statement executed successfully...!");
            System.out.println("ProcessList: ");
            while(rs.next()) {
              String p_list = rs.getString(1);
              System.out.println(p_list);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Show statement executed successfully...!
ProcessList: 
5
13
43  

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# show trigger
show_processlist_query = "SHOW PROCESSLIST"
cursorObj.execute(show_processlist_query)
result = cursorObj.fetchall()
print("Processlist in the database:")
for row in result:
    print(row)
# close the cursor and connection
cursorObj.close()
connection.close()

Processlist in the database:
(5, 'event_scheduler', 'localhost', None, 'Daemon', 1029217, 'Waiting on empty queue', None)
(322, 'root', 'localhost:56077', 'tut', 'Sleep', 309, '', None)
(384, 'root', 'localhost:58907', 'tut', 'Query', 0, 'init', 'SHOW PROCESSLIST')