在 MySQL 5.6 以前，我們通過 show processlist 命令查看系統(tǒng)中正在運行的所有進程：

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 表來查看系統(tǒng)正在運行的所有進程：

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 # 已經(jīng)運行了 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 # 執(zhí)行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 # 需要一個臨時表來執(zhí)行該語句 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 # 已經(jīng)運行了410.06毫秒 trx_state: ACTIVE trx_autocommit: YES # 這是一個自動提交的事務(wù) 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 # 執(zhí)行查詢語句 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 # 事務(wù)已經(jīng)持續(xù)了4.94分鐘 trx_state: ACTIVE trx_autocommit: NO pid: 23950 program_name: mysql 3 rows in set (0.20 sec)

注：sys 數(shù)據(jù)庫下的 session 和 processlist 這兩張表的數(shù)據(jù)結(jié)構(gòu)一模一樣。

總結(jié)

以上是生活随笔為你收集整理的MySQL查看数据库系统正在运行的进程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。