MySQL processlist的state属性详解

关于processlist其他属性介绍，可查看之前的文章：MySQL性能分析 - (二) processlist使用及重要指标说明

state属性是processlist中分析性能的关键部分。

• 通过官方对不同状态的解释，可以判断sql执行时的底层运作，进而定位到深层的问题，比如网络吞吐量不够、磁盘I/O压力过大、CPU计算较多、内存紧张等瓶颈。
• 再结合processlist.info的explain结果，问题就可以定位到结果集过大、索引不理想检索行过多、排序较多、临时表过多过大的问题。

对STATE的判断、联想、以及SQL的explain，需要较多实践锻炼，这里暂时只能分享理论上可能的情况，具体判断还要实际中去摸索。

下面详细解析各类状态：

常见状态分析

Sending data

线程正在读取和处理SELECT语句的行 ，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问（读取），因此它通常是给定查询生命周期中运行时间最长的状态

这是最常见的状态，也是最模糊的状态。这个状态并不是字面理解的“发送数据”，在读取数据开始、到数据发到客户端之前，都可能是这个状态。如果在这个状态持续的时间比较久，需要拿到processlist.info，通过explain进一步定位问题。常见的问题有下面几种：

• 索引不理想、导致回表较多，磁盘压力大、一直在等待磁盘读完数据；可通过explain的rows看到特征
• 文件排序等待，特征是cpu会变高；可通过explain的extra看到特征

closing tables

该线程正在将更改的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果没有，请验证您没有完整磁盘并且磁盘使用不是很大

磁盘不足的重要状态。

Copying to tmp table on disk

服务器正在复制到磁盘上的临时表。临时结果集变得太大（请参见 第8.4.4节“MySQL中的内部临时表使用”，超过tmp_table_size）。因此，线程正在将临时表从内存更改为基于磁盘的格式以节省内存

配置的临时表空间不足时，临时表会从内存写入磁盘，这个动作比较耗时。

相关的状态还有Creating tmp table：

该线程正在内存或磁盘上创建临时表。如果表在内存中创建但稍后转换为磁盘表，则该操作期间的状态将为Copying to tmp table on disk

Updating

线程正在搜索要更新的行并正在更新它们

一般情况下update因为有主键约束会很快，但有一种情况发生时，会导致update等待严重： 有一个update语句没有走上唯一索引，进行了索引扫描，扫描时索引行是被锁住的，其他更新只能等待。

Writing to net

服务器正在将数据包写入网络

注意数据库服务器的网络吞吐量是否超限或网络环境较差，或者是否返回的数据太多了。

所有STATE速查表

官方给定的STATE非常多¹，MySQL5.6全部的STATE如下：

状态	说明
After create	当线程在创建表的函数末尾创建表（包括内部临时表）时，会发生这种情况。即使由于某些错误而无法创建表，也会使用此状态
altering table	服务器正在执行就地 ALTER TABLE
Analyzing	线程正在计算MyISAM表键分布（例如，for ANALYZE TABLE）。
checking permissions	线程正在检查服务器是否具有执行语句所需的权限
Checking table	该线程正在执行表检查操作
cleaning up	该线程已经处理了一个命令，并准备释放内存并重置某些状态变量
closing tables	该线程正在将更改的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果没有，请验证您没有完整磁盘并且磁盘使用不是很大
committing alter table to storage engine	服务器已完成就地 ALTER TABLE并提交结果
converting HEAP to MyISAM	该线程正在将内部临时表从 MEMORY表转换为磁盘 MyISAM表
copy to tmp table	线程正在处理一个ALTER TABLE语句。在创建具有新结构的表但在将行复制到其中之前，将发生此状态
Copying to group table	如果语句具有不同的条件ORDER BY和 GROUP BY标准，则按组对行进行排序并将其复制到临时表
Copying to tmp table	服务器正在复制到内存中的临时表
Copying to tmp table on disk	服务器正在复制到磁盘上的临时表。临时结果集变得太大（请参见 第8.4.4节“MySQL中的内部临时表使用”，超过tmp_table_size）。因此，线程正在将临时表从内存更改为基于磁盘的格式以节省内存
Creating index	线程正在处理ALTER TABLE … ENABLE KEYS一个MyISAM表
Creating sort index	线程正在处理SELECT使用内部临时表解析的线程
creating table	线程正在创建一个表。这包括创建临时表
Creating tmp table	该线程正在内存或磁盘上创建临时表。如果表在内存中创建但稍后转换为磁盘表，则该操作期间的状态将为Copying to tmp table on disk
deleting from main table	服务器正在执行多表删除的第一部分。它仅从第一个表中删除，并保存用于从其他（引用）表中删除的列和偏移量
deleting from reference tables	服务器正在执行多表删除的第二部分，并从其他表中删除匹配的行
discard_or_import_tablespace	线程正在处理ALTER TABLE … DISCARD TABLESPACE或ALTER TABLE … IMPORT TABLESPACE声明
end	这发生在结束，但的清理之前 ALTER TABLE， CREATE VIEW， DELETE， INSERT， SELECT，或 UPDATE语句
executing	该线程已开始执行语句
Execution of init_command	线程正在执行init_command系统变量值中的语句
freeing items	线程执行了一个命令。在此状态期间完成的一些项目的释放涉及查询缓存。这种状态通常紧随其后cleaning up
FULLTEXT initialization	服务器正准备执行自然语言全文搜索
init	出现这种情况的初始化之前 ALTER TABLE， DELETE， INSERT， SELECT，或 UPDATE语句。服务器在此状态下采取的操作包括刷新二进制日志，InnoDB日志和一些查询缓存清理操作； 对于end状态，可能会发生以下操作： * 删除表中的数据后删除查询缓存条目 * 将事件写入二进制日志 * 释放内存缓冲区，包括blob
Killed	发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉（比如SQL执行了比较耗时的操作，Kill后需要回滚事务和资源。回滚耗时要看操作的资源大小）。如果该线程被其他线程锁住了，那么kill请求会在锁释放时马上生效
logging slow query	该线程正在向慢查询日志写一条语句
login	连接线程的初始状态，直到客户端成功通过身份验证
manage keys	服务器正在启用或禁用表索引
NULL	该状态用于该SHOW PROCESSLIST状态
Opening tables	线程正在尝试打开一个表。这应该是非常快的程序，除非有什么东西阻止打开。例如，一个ALTER TABLE或一个 LOCK TABLE语句可以阻止在语句结束之前打开表。还值得检查您的table_open_cache价值是否足够大
optimizing	服务器正在对查询执行初始优化
preparing	在查询优化期间发生此状态
preparing for alter table	服务器正准备执行就地 ALTER TABLE
Purging old relay logs	该线程正在删除不需要的中继日志文件
query end	处理查询后但在freeing items状态之前发生此 状态
Reading from net	服务器正在从网络读取数据包
Removing duplicates	该查询使用 SELECT DISTINCT的方式是MySQL无法在早期阶段优化掉不同的操作。因此，在将结果发送到客户端之前，MySQL需要额外的阶段来删除所有重复的行
removing tmp table	该线程在处理SELECT 语句后删除内部临时表。如果未创建临时表，则不使用此状态
rename	该线程正在重命名一个表
rename result table	线程正在处理一个ALTER TABLE语句，创建了新表，并重命名它以替换原始表
Reopen tables	该线程获得了表的锁定，但在获取锁定之后注意到基础表结构发生了变化。它释放了锁，关闭了桌子，并试图重新打开它
Repair by sorting	修复代码使用排序来创建索引
Repair done	该线程已完成对MyISAM表的多线程修复
Repair with keycache	修复代码通过密钥缓存逐个创建密钥。这比慢得多Repair by sorting
Rolling back	该线程正在回滚一个事务
Saving state	对于MyISAM诸如修复或分析的表操作，线程将新表状态保存到.MYI文件头。状态包括诸如行数， AUTO_INCREMENT计数器和密钥分发之类的信息
Searching rows for update	该线程正在进行第一阶段以在更新之前查找所有匹配的行。如果 UPDATE要更改用于查找所涉及行的索引，则必须执行此操作
Sending data	线程正在读取和处理SELECT语句的行 ，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问（读取），因此它通常是给定查询生命周期中运行时间最长的状态
setup	线程正在开始一个ALTER TABLE操作
Sorting for group	线程正在进行排序以满足a GROUP BY
Sorting for order	线程正在进行排序以满足ORDER BY
Sorting index	该线程正在对索引页面进行排序，以便在MyISAM表优化操作期间进行更有效的访问
Sorting result	对于SELECT声明，这类似于Creating sort index非临时表
statistics	服务器正在计算统计信息以开发查询执行计划。如果线程长时间处于此状态，则服务器可能是磁盘绑定执行其他工作
System lock	该线程已调用mysql_lock_tables() ，并且线程状态尚未更新。这是一个非常普遍的状态，可能由于多种原因而发生； 例如，线程将请求或正在等待表的内部或外部系统锁定。InnoDB在执行期间等待表级锁定时会 发生这种情况LOCK TABLES。如果此状态是由外部锁的请求引起的，并且您没有使用多个访问相同 表的mysqld服务器，则MyISAM可以使用该–skip-external-locking 选项禁用外部系统锁 。但是，默认情况下禁用外部锁定，因此该选项很可能无效。对于 SHOW PROFILE，这个状态意味着线程正在请求锁定（不等待它）
update	线程正准备开始更新表
Updating	线程正在搜索要更新的行并正在更新它们
updating main table	服务器正在执行多表更新的第一部分。它仅更新第一个表，并保存用于更新其他（引用）表的列和偏移量
updating reference tables	服务器正在执行多表更新的第二部分，并更新其他表中的匹配行
User lock	该线程将要求或正在等待通过GET_LOCK()呼叫请求的咨询锁 。对于 SHOW PROFILE，此状态表示线程正在请求锁定（不等待它）
User sleep	线程已经调用了一个 SLEEP()调用
Waiting for commit lock	FLUSH TABLES WITH READ LOCK 正在等待提交锁定
Waiting for global read lock	FLUSH TABLES WITH READ LOCK 正在等待全局读锁定或read_only正在设置全局 系统变量
Waiting for tables	线程得到一个通知，表明表的底层结构已经改变，它需要重新打开表以获得新结构。但是，要重新打开表，它必须等到所有其他线程关闭了相关表。 该通知发生如果另一个线程已使用 FLUSH TABLES或有问题的表下面的语句之一：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE
Waiting for table flush	线程正在执行FLUSH TABLES并且正在等待所有线程关闭它们的表，或者线程得到一个表的基础结构已经更改的通知，并且它需要重新打开表以获取新结构。但是，要重新打开表，它必须等到所有其他线程关闭了相关表. 该通知发生如果另一个线程已使用 FLUSH TABLES或有问题的表下面的语句之一： FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE
Waiting for lock_type lock	服务器正在等待THR_LOCK从元数据锁定子系统获取 锁定或锁定，其中 lock_type指示锁定的类型。 此状态表示等待 THR_LOCK： * Waiting for table level lock 这些状态表示等待元数据锁定 * Waiting for event metadata lock * Waiting for global read lock * Waiting for schema metadata lock * Waiting for stored function metadata lock * Waiting for stored procedure metadata lock * Waiting for table metadata lock * Waiting for trigger metadata lock * 有关表锁指示器的信息，请参见第8.11.1节“内部锁定方法” * 有关元数据锁定的信息，请参见第8.11.4节“元数据锁定”
Waiting on cond	线程正在等待条件变为真的通用状态。没有具体的状态信息
Writing to net	服务器正在将数据包写入网络

---

1. MySQL官方文档 - STATE ↩︎