MySQL线程

$ 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
  正在检查数据表

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

• Connect Out
  复制从服务器正在连接主服务器

• Copying to tmp table on disk

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

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

• Creating tmp table
  正在创建临时表以存储部分查询结果

• Deleting from main table
  服务器正在执行多表删除中的第一部分，刚刚删除第一个表。

• Deleting from reference tables
  服务器正在执行夺标删除中的第二部分，正在删除其它表的记录。

• Flushing tables
  正在执行FLUSH TABLES命令，等待其它线程关闭数据表。

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

• Locked

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

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

• Sending data

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

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

• Sorting for group
  正在为GROUP BY做排序

• Sorting for order
  正在为ORDER BY做排序

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

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

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

• Repair by sorting
  修复指令正在排序以创建索引

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

• Sleeping
  正在等待客户端发送新请求

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

• Upgrading lock
  INSERT DELAYED延迟插入正在尝试获取一个锁表以插入新记录

• Updating
  正在搜索匹配得记录并修改它们

• User Lock
  正在等待GET_LOCK()

• 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";