MySql--线程管理

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 本文字数： 920 阅读时长 ≈ 3 分钟

摘要

查看线程状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 会显示用户，客户端ip，访问的数据库，执行的命令及其状态等信息
mysql> show processlist;
+-----+-----------------+--------------------+----------+---------+--------+------------------------+------------------+
| Id  | User            | Host               | db       | Command | Time   | State                  | Info             |
+-----+-----------------+--------------------+----------+---------+--------+------------------------+------------------+
|   5 | event_scheduler | localhost          | NULL     | Daemon  | 628889 | Waiting on empty queue | NULL             |
| 438 | root            | localhost:56114    | mysql    | Query   |      0 | init                   | show processlist |
| 439 | root            | 1.119.161.30:53840 | novel_db | Sleep   |      5 |                        | NULL             |
| 440 | root            | 1.119.161.30:53849 | NULL     | Sleep   |      5 |                        | NULL             |
+-----+-----------------+--------------------+----------+---------+--------+------------------------+------------------+
# 或者，两者等价
mysql> select * from information_schema.processlist;

# 查看每个客户端连接数
mysql> select id,client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip,id from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;
+-----+--------------+------------+
| id  | client_ip    | client_num |
+-----+--------------+------------+
| 440 | 1.119.161.30 |          2 |
|   5 | localhost    |          2 |
+-----+--------------+------------+

# 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
mysql> select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
+--------------------------+
| concat('kill ', id, ';') |
+--------------------------+
| kill 5;                  |
+--------------------------+

# 关闭连接，注意kill只能关闭当前正在执行DML的操作，DDL不能关闭
mysql> kill 440;

State说明
通过show processlist;查看线程状态非常有用,这可以让我们很快地了解当前MySQL在进行的线程,包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。
在一个繁忙的服务器上，可能会看到大量的不正常的状态，例如statistics正占用大量的时间。这通常表示，某个地方有异常了。
线程常见的状态有很多，比如:

  • statistics: 服务器正在计算统计信息以研究一个查询执行计划。如果线程长时间处于此状态，则服 务器可能是磁盘绑定执行其他工作。
  • Creating tmp table: 该线程正在内存或磁盘上创建临时表。如果表在内存中创建但稍后转换为磁盘表，则该操作期间的状态将为 Copying to tmp table on disk
  • Sending data: 线程正在读取和处理SELECT语句的行，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问（读取），因此它通常是给定查询生命周期中运行时间最长的状态。
  • 其它状态参考：https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

  • 在MySQL 8.0及更高版本中也可以使用如下的方式查询尚未执行完成的命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
mysql> SELECT *  FROM performance_schema.threads where PROCESSLIST_COMMAND is not NULL and PROCESSLIST_COMMAND != 'Sleep'\G;
*************************** 1. row ***************************
          THREAD_ID: 44
               NAME: thread/sql/event_scheduler
               TYPE: FOREGROUND
     PROCESSLIST_ID: 5
   PROCESSLIST_USER: event_scheduler
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Daemon
   PROCESSLIST_TIME: 6313
  PROCESSLIST_STATE: Waiting on empty queue
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 8593
     RESOURCE_GROUP: SYS_default
   EXECUTION_ENGINE: PRIMARY
*************************** 2. row ***************************
          THREAD_ID: 48
               NAME: thread/sql/compress_gtid_table
               TYPE: FOREGROUND
     PROCESSLIST_ID: 7
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Daemon
   PROCESSLIST_TIME: 6313
  PROCESSLIST_STATE: Suspending
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 8597
     RESOURCE_GROUP: SYS_default
   EXECUTION_ENGINE: PRIMARY
*************************** 3. row ***************************
          THREAD_ID: 51
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 10
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: adsdk
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 5698
  PROCESSLIST_STATE: copy to tmp table
   PROCESSLIST_INFO: ALTER TABLE tbl_ap_appmoduleuser_i ENGINE = InnoDB
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 8636
     RESOURCE_GROUP: USR_default
   EXECUTION_ENGINE: PRIMARY
*************************** 4. row ***************************
          THREAD_ID: 52
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 11
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: executing
   PROCESSLIST_INFO: SELECT *  FROM performance_schema.threads where PROCESSLIST_COMMAND is not NULL and PROCESSLIST_COMMAND != 'Sleep'
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 8978
     RESOURCE_GROUP: USR_default
   EXECUTION_ENGINE: PRIMARY
4 rows in set (0.01 sec)

ERROR:
No query specified