MySQL查看数据库系统正在运行的进程

在 MySQL 5.6 以前，我们通过 show processlist 命令查看系统中正在运行的所有进程：

mysql> show processlist;
+------+--------------+---------------------+------------+---------+------+----------+------------------+
| Id   | User         | Host                | db         | Command | Time | State    | Info             |
+------+--------------+---------------------+------------+---------+------+----------+------------------+
|  240 | zentao       | localhost:42918     | zentao     | Sleep   |   24 |          | NULL             |
| 5350 | root         | localhost           | test       | Query   |    0 | starting | show processlist |
| 5421 | lwx          | localhost:48922     | NULL       | Sleep   | 4090 |          | NULL             |
| 5427 | root         | 112.50.84.94:7676   | sys        | Sleep   | 3342 |          | NULL             |
| 5428 | root         | 112.50.84.94:7512   | sys        | Sleep   | 3342 |          | NULL             |
| 5437 | root         | 112.50.84.94:7435   | sys        | Sleep   | 2839 |          | NULL             |
| 5438 | root         | 112.50.84.94:7439   | sys        | Sleep   | 2839 |          | NULL             |
| 5451 | qydpw        | 47.114.59.224:54944 | production | Sleep   | 1495 |          | NULL             |
| 5452 | qydpw        | 47.114.59.224:54946 | qydpw      | Sleep   | 1459 |          | NULL             |
| 5453 | qydpw        | 47.114.59.224:54950 | production | Sleep   | 1436 |          | NULL             |
| 5454 | qydpw        | 47.114.59.224:54954 | production | Sleep   | 1340 |          | NULL  

从 5.7 开始，我们又可以通过 sys.session 表来查看系统正在运行的所有进程：

mysql> SELECT * from sys.session\G

*************************** 1. row ***************************

thd_id: 29
conn_id: 4
user: root@localhost
db: test
command: Query
state: alter table (read PK and internal sort) 
time: 6
current_statement: ALTER TABLE b add index(b) # 当前运行ALTER TABLE命令给一张表添加索引
statement_latency: 5.78 s # 已经运行了 5.78秒
progress: 19.19 # 显示该命令完成的进度（19.19%）
lock_latency: 203.70 ms
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 4.85 MiB
last_wait: wait/io/file/innodb/innodb_data_file
last_wait_latency: Still Waiting
source: fil0fil.cc:5623
trx_latency: 5.68 s 
trx_state: ACTIVE
trx_autocommit: YES
pid: 23988
program_name: mysql

*************************** 2. row ***************************

thd_id: 28
conn_id: 3
user: root@localhost
db: test
command: Query
state: Sending data
time: 0
current_statement: insert into a select null, repeat(‘b‘, 255) from a # 执行INSERT操作
statement_latency: 303.54 ms
progress: NULL
lock_latency: 291.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 1 # 需要一个临时表来执行该语句
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 1.43 MiB
last_wait: wait/io/table/sql/handler
last_wait_latency: Still Waiting
source: handler.cc:3056
trx_latency: 410.06 ms  # 已经运行了410.06毫秒
trx_state: ACTIVE
trx_autocommit: YES # 这是一个自动提交的事务
pid: 23969
program_name: mysql

*************************** 3. row ***************************

thd_id: 27
conn_id: 2
user: root@localhost
db: mysql
command: Query
state: Sending data
time: 0
current_statement: SELECT * from sys.session # 执行查询语句
statement_latency: 23.01 ms
progress: NULL
lock_latency: 10.02 ms
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 4
tmp_disk_tables: 1
full_scan: YES
last_statement: NULL
last_statement_latency: NULL
current_memory: 3.25 MiB
last_wait: wait/synch/mutex/innodb/file_format_max_mutex
last_wait_latency: 31.69 ns
source: trx0sys.cc:781
trx_latency: 4.94 m # 事务已经持续了4.94分钟
trx_state: ACTIVE
trx_autocommit: NO
pid: 23950
program_name: mysql
3 rows in set (0.20 sec)

注：sys 数据库下的 session 和 processlist 这两张表的数据结构一模一样。