show processlist命令与kill 线程

show [full] processlist

show processlist显示正在运行的线程。如果有process权限，则可以查看所有正在运行的线程。否则，只能看到自己的线程。如果不使用full关键字，则只在info字段显示每个语句的前100个字符。

show processlist命令是非常有用的，如果你获得到“too many connections”错误信息，并且想知道什么正在运行。MySQL保留了一个额外的连接给超级管理员。

线程能够被kill掉，使用kill语句。

mysql> show processlist;
+----+------+--------------------+-----------+---------+------+-------------+------------------+
| Id | User | Host               | db        | Command | Time | State       | Info             |
+----+------+--------------------+-----------+---------+------+-------------+------------------+
| 36 | root | 172.16.100.19:7954 | tpcc_test | Sleep   |  456 |             | NULL             |
| 37 | root | 172.16.100.19:7969 | tpcc_test | Sleep   |  456 |             | NULL             |
| 42 | root | localhost          | NULL      | Query   |    0 | System lock | show processlist |
| 43 | root | 10.0.102.204:49224 | employees | Sleep   |   12 |             | NULL             |
+----+------+--------------------+-----------+---------+------+-------------+------------------+
4 rows in set (0.00 sec)

这个显示的几个字段还是比较好理解的。

• ID：连接标识。这个值和INFORMATION_SCHEMA.PROCESSLIST表的ID列，以及PERFORMANCE_SCHEMA中的threads中的process_id值是相同的。
• user： 发出该语句的MySQL用户。system user是指由服务器生成的用于内部处理任务的非客户端线程。这可以是用于复制的I/O线程，或SQL线程，也可以是延迟的行处理程序。未经身份验证的用户是指已经与客户端连接但尚未对客户端身份进行验证的线程。
• Host：客户端使用的主机。系统用户没有host值。
• db：连接的数据库。
• command：线程执行的命令

  A thread can have any of the following Command values:
  
  Binlog Dump:  This is a thread on a master server for sending binary log contents to a slave server.
  
  Change user: The thread is executing a change-user operation.
  
  Close stmt: The thread is closing a prepared statement.[关闭准备好的声明]
  
  Connect：A replication slave is connected to its master. 【已经连接】
  
  Connect Out：A replication slave is connecting to its master. 【正则连接】
  
  Create DB：The thread is executing a create-database operation.
  
  Daemon：This thread is internal to the server, not a thread that services a client connection. 【这个线程是服务器内部线程，而不是客户端线程】
  
  Debug：The thread is generating debugging information. 【线程正在生成调试信息】
  
  Delayed insert：The thread is a delayed-insert handler.【延迟插入处理】
  
  Drop DB：The thread is executing a drop-database operation.
  
  Error：
  
  Execute：The thread is executing a prepared statement. 【线程正在执行预准备的语句】
  
  Fetch：The thread is fetching the results from executing a prepared statement.【线程正在从预准备语句拉取结果】
  
  Field List：The thread is retrieving information for table columns.【该线程正则检索列表信息】
  
  Init DB：The thread is selecting a default database. 【线程正在选择一个默认数据库】
  
  Kill：The thread is killing another thread. 【线程正则kill掉其他线程】
  
  Long Data：The thread is retrieving long data in the result of executing a prepared statement.
  
  Ping：The thread is handling a server-ping request.
  
  Prepare：The thread is preparing a prepared statement.
  
  Processlist：The thread is producing information about server threads.
  
  Query： The thread is executing a statement.
  
  Quit：The thread is terminating.
  
  Refresh：The thread is flushing table, logs, or caches, or resetting status variable or replication server information.
  
  Register Slave： The thread is registering a slave server.
  
  Reset stmt： The thread is resetting a prepared statement.
  
  Set option： The thread is setting or resetting a client statement-execution option.
  
  Shutdown： The thread is shutting down the server.
  
  Sleep： The thread is waiting for the client to send a new statement to it.
  
  Statistics：The thread is producing server-status information.
  
  Table Dump：The thread is sending table contents to a slave server.
  
  Time：Unused.

• time： 线程已经在当前状态的时间。
• state：一个动作，一个事件，标识线程正在做什么。https://blog.csdn.net/sunqingzhong44/article/details/70570728
• info：Info contains the text of the statement being executed by the thread, or NULL if it is not executing one. By default, this value contains only the first 100 characters of the statement. To see the complete statements, useSHOW FULL PROCESSLIST.

进程的信息也可以通过mysqladmin processlist命令查看。

相比较之下show processlist和information_schema.processlist需要互斥锁，而performance_schema.threads不需要，threads对服务器性能影响最小，threads表还显示有关后台线程的信息。

kill线程

与MySQL服务器每个链接都在一个单独的线程中运行。可以使用如下语句杀死一个线程。

kill [connection| query] processlist_id

connection: 与kill processlist_id相同；中断连接正在执行的任何语句之后，中断连接。
query： 中断连接正在执行的语句，但是保持本身的连接。

有三种方法可以获得processlist_id，上面已经提到了！

当你使用kill时，一个特殊的kill标记被设置给这个线程。在大多数的情况下，它可能会等一段时间线程再死亡，因为kill标记仅在一些情况下被检测。

• During SELECT operations, for ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.
• ALTER TABLE operations that make a table copy check the kill flag periodically for each few copied rows read from the original table. If the kill flag was set, the statement is aborted and the temporary table is deleted.The KILL statement returns without waiting for confirmation, but the kill flag check aborts the operation within a reasonably small amount of time. Aborting the operation to perform any necessary cleanup also takes some time.
• During UPDATE or DELETE operations, the kill flag is checked after each block read and after each updated or deleted row. If the kill flag is set, the statement is aborted. If you are not using transactions, the changes are not rolled back.
• GET_LOCK() aborts and returns NULL.
• If the thread is in the table lock handler (state: Locked), the table lock is quickly aborted.
• If the thread is waiting for free disk space in a write call, the write is aborted with a “disk full” error message.

 

mysql> show processlist;
+----+------+---------------------+--------------------+---------+------+-------------+------------------+
| Id | User | Host                | db                 | Command | Time | State       | Info             |
+----+------+---------------------+--------------------+---------+------+-------------+------------------+
| 36 | root | 172.16.100.19:7954  | tpcc_test          | Sleep   |    8 |             | NULL             |
| 37 | root | 172.16.100.19:7969  | tpcc_test          | Sleep   |    8 |             | NULL             |
| 42 | root | localhost           | NULL               | Query   |    0 | System lock | show processlist |
| 43 | root | 10.0.102.204:49224  | employees          | Sleep   | 2564 |             | NULL             |
| 44 | root | 172.16.100.19:14529 | NULL               | Sleep   | 2322 |             | NULL             |
| 45 | root | 172.16.100.19:14770 | information_schema | Sleep   | 2315 |             | NULL             |
| 46 | root | 172.16.100.19:1508  | information_schema | Sleep   | 2314 |             | NULL             |
| 47 | root | 172.16.100.19:10572 | performance_schema | Sleep   | 2231 |             | NULL             |
| 48 | root | 172.16.100.19:11392 | performance_schema | Sleep   | 2231 |             | NULL             |
+----+------+---------------------+--------------------+---------+------+-------------+------------------+
9 rows in set (0.00 sec)

mysql> kill 43;              #kill掉43线程
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+------+---------------------+--------------------+---------+------+-------------+------------------+
| Id | User | Host                | db                 | Command | Time | State       | Info             |
+----+------+---------------------+--------------------+---------+------+-------------+------------------+
| 36 | root | 172.16.100.19:7954  | tpcc_test          | Sleep   |  386 |             | NULL             |
| 37 | root | 172.16.100.19:7969  | tpcc_test          | Sleep   |  386 |             | NULL             |
| 42 | root | localhost           | NULL               | Query   |    0 | System lock | show processlist |
| 44 | root | 172.16.100.19:14529 | NULL               | Sleep   | 3300 |             | NULL             |
| 45 | root | 172.16.100.19:14770 | information_schema | Sleep   | 3293 |             | NULL             |
| 46 | root | 172.16.100.19:1508  | information_schema | Sleep   | 3292 |             | NULL             |
| 47 | root | 172.16.100.19:10572 | performance_schema | Sleep   | 3209 |             | NULL             |
| 48 | root | 172.16.100.19:11392 | performance_schema | Sleep   | 3209 |             | NULL             |
+----+------+---------------------+--------------------+---------+------+-------------+------------------+
8 rows in set (0.00 sec)



MySQL [employees]> select * from employees;      #连接发现失败
ERROR 2013 (HY000): Lost connection to MySQL server during query