MySQL Thread

$ SHOW PROCESSLIST;

SHOW PROCESSLIST命令用于显示哪些线程正在运行，如果是SUPER权限可查看所有线程，否则只能查看自己的线程。

$ SHOW FULL PROCESSLIST;

SHOW PROCESSLIST只会列出前100条，SHOW FULL PROCESSLIST会列出所有。

结果显示

+-------+-----------------+-----------------------+---------+-------------+-------+---------------------------------------------------------------+-----------------------+
| Id    | User            | Host                  | db      | Command     | Time  | State                                                         | Info                  |
+-------+-----------------+-----------------------+---------+-------------+-------+---------------------------------------------------------------+-----------------------+

列名	描述
Id	编号标识，KILL杀死线程时使用。
User	当前用户，非root显示权限范围内的SQL语句。
Host	语句来与IP和端口，用于追踪出问题语句的用户。
db	进程目前连接的数据库名
Command	当前连接执行的命令
Time	状态持续时间，单位为秒。
State	当前连接的SQL语句的状态
Info	显示完整SQL语句

线程命令Command

Command显示当前连接执行的命令，一般是休眠sleep、查询select、连接connect。

命令	描述
sleep	休眠，连接资源未释放。
select	查询
connect	连接

• sleep休眠代表资源未释放，如果使用了连接池，sleep休眠状态应该恒定在一定数字范围内。

例如：前端数据输出时未及时关闭数据库连接，导致因网络连接速度产生大量sleep连接，当网速出现异常时，数据库会出现too many connections挂死。一般而言，数据查询和执行通常只需要不到0.01秒，而网速输入通常需要1秒左右甚至更长，原本数据连接在0.01秒即可释放，但因前端程序未执行close关闭连接操作，直接输入结果，造成结果未显示给用户前，数据库连接将一直维持在sleep状态。

线程状态State

运行状态	描述
Checking	正在检查数据表
Sleeping	正在等待客户端发送新请求
Updating	正在搜索匹配得记录并修改它们
Killed	发送了一个KILL请求给某线程
Locked	线程被其它查询锁住
Connect Out	复制Slave服务器正在连接Master服务器
Copying to tmp table on disk	将临时表从内存存储转为磁盘存储
Creating tmp table	正在创建临时表以存储部分查询结果
Deleting from main table	服务器正在执行多表删除中的第一部分，刚刚删除第一个表。
Deleting from reference tables	服务器正在执行夺标删除中的第二部分，正在删除其它表的记录。
Opening tables	正在尝试打开一个表
Reopen table	正尝试重新打开数据表
Closing tables	正在关闭已经用完的表
Flushing tables	正在执行FLUSH TABLES命令，等待其它线程关闭数据表。
Sending data	正在处理SELECT查询的记录，同时正在将查询结果发送给客户端。
Sorting for group	正在为GROUP BY做排序
Sorting for order	正在为ORDER BY做排序
Removing duplicates	正在执行SELECT DISTINCT查询
Repair by sorting	修复指令正在排序以创建索引
Searching rows for update	正在将符合条件的记录寻找出来以备更新
User Lock	正在等待GET_LOCK()
System lock	正在等待取得一个外部得系统锁
Upgrading lock	INSERT DELAYED延迟插入正在尝试获取一个锁表以插入新记录
Waiting for tables	等到其它线程关闭表
Waiting for handler insert	INSERT DELAYED延迟插入已经处理完所有待插入操作，正在等待新的请求。
Waiting for net, reading from net, writing to net	需检查网络连接和状态

• Closing tables

正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是的话，需要确认磁盘空间是否已经满了，或者磁盘是否正处于负重中。

• Copying to tmp table on disk

由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以节省内存，通常与连表查询有关，出现后将会导致磁盘I/O和CPU压力超载。

当索引或现有结构无法涵盖查询条件时，才会建立一个临时表来满足查询条件，此时会产生巨大而恐怖的I/O压力。可怕的搜索语句会导致这种情况，数据分析或周期性清理任务也会偶尔出现。

• Killed

发送了一个KILL请求给某线程，这个线程将会检查KILL标志位，同时会放弃下一个KILL请求。MYSQL会在每次的主循环中检查KILL标记位，不过有些情况下该线程可能会过一小段时间才能死掉。如果该线程被其它线程锁住了，那么KILL请求会在锁释放时立即生效。

• Locked

Locked表示线程被其它查询锁住，在MyISAM引擎时代Locked是高并发应用的噩梦，由于InnoDB数据引擎支持行锁定，可以很好地减少Locked状态的产生。

Locked经常出现在有更新操作的锁定中，切记更新操作要正确地使用索引，即便是低频次的更新操作也不能疏忽。

• Sending data

Sending data表示正在处理SELECT查询的记录，同时正在将查询结果发送给客户端，其实是从物理磁盘获取新数据的进程，如果影响结果集较多就需要从不同的磁盘碎片上去抽取数据，偶尔出现连接无碍。

如果Sending data连接过多，可能是某查询的影响结果集过大，也就是说查询的索引项不够优化造成的。

• Opening tables

正在尝试打开一个表，这个过程应该会很快，排除受到其它因素的干扰，比如在执行ALTER TABLE或LOCK TABLE语句以前，数据表无法被其它线程打开。

• Removing duplicates

正在执行SELECT DISTINCT查询，但MySQL无法在前一个阶段优化掉哪些重复的记录。因此，MySQL需要再次去掉重复记录，然后再把结果发送给客户端。

• Reopen table

获取对一个表的锁，但必须在表结构修改之后才能获得这个锁。已经释放锁关闭数据表，正尝试重新打开数据表。

• Searching rows for update

正在将符合条件的记录寻找出来以备更新，它必须在UPDATE要修改的记录前就完成。

• System lock

正在等待取得一个外部得系统锁，如果当前没有运行多个mysqld服务器同时请求同一个表，可通过增加--skip-external-locking参数来禁止外部系统锁。

• Waiting for tables

线程得到通知，数据表结构已经被修改，需重新打开数据表以取得新的结构。为了能重新打开数据表，必须等到所有其它线程关闭这个表。以下情况会产生这个通知：

命令	描述
FLUSH TABLES table_name	刷新表
RENAME TABLE	重命名
REPAIR TABLE	修复表
ANALYZE TABLE	分析表
OPTIMIZE TABLE	优化表

• Waiting for handler insert

INSERT DELAYED延迟插入已经处理完所有待插入操作，正在等待新的请求。

大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒，那么可能是有问题发生了，需要检查一下。

• Waiting for net, reading from net, writing to net

若有大量连接阻塞在waiting for net...上，大量出现则应迅速检查数据库到前端的网络连接和状态。

杀死线程kill

通过information_schema.processlist表中的连接信息生成所需处理掉的MySQL连接语句的临时文件，然后执行临时文件中生成的指令。

SELECT CONCAT("KILL ", Id, ";") FROM information_schema.processlist WHERE User="root";

查询并导出

SELECT CONCAT("KILL ", Id, ";") FROM information_schema.processlist WHERE User="root" INTO outfile "/tmp/a.txt";