General Thread States

对于实践中可能出现的各种General Thread States

以下列表描述了与常规查询处理关联的线程状态值，而不是更复杂的活动，例如复制。 其中许多仅用于在服务器中查找错误。

• after create

This occurs when the thread creates a table (including internal temporary tables), at the end of the function that creates the table. This state is used even if the table could not be created due to some error.

当线程创建表（包括内部临时表）时，会在创建表的函数的末尾创建。即使由于某些错误而无法创建表，也会使用此状态

• analyzing

The thread is calculating a MyISAM table key distributions (for example, for ANALYZE TABLE).

线程正在计算MyISAM表密​​钥分发（例如，对于ANALYZE TABLE）

• checking permissions

The thread is checking whether the server has the required privileges to execute the statement.

线程正在检查服务器是否具有执行语句所需的权限

• checking table

The thread is performing a table check operation.

线程正在执行表检查操作

• cleaning up

The thread has processed one command and is preparing to free memory and reset certain state variables.

该线程已经处理了一个命令，正在准备释放内存并重置某些状态变量

• closing tables

The thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, verify that you do not have a full disk and that the disk is not in very heavy use.

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

• converting HEAP to MyISAM

The thread is converting an internal temporary table from a MEMORY table to an on-disk MyISAM table.

线程将内部临时表从MEMORY表转换为磁盘MyISAM表

• copy to tmp table

The thread is processing an ALTER TABLE statement. This state occurs after the table with the new structure has been created but before rows are copied into it.

For a thread in this state, the Performance Schema can be used to obtain about the progress of the copy operation. See Section 25.11.5, “Performance Schema Stage Event Tables”.

线程正在处理ALTER TABLE语句。此状态发生在已创建新结构的表之后，但是将行复制到该表之前

对于此状态的线程，可以使用性能模式来获取有关复制操作的进度。请参见第25.11.5节“性能模式阶段事件表

• copying to group table

If a statement has different ORDER BY and GROUP BY criteria, the rows are sorted by group and copied to a temporary table.

如果一条语句具有不同的ORDER BY和GROUP BY条件，则按行分组并将其复制到临时表中

• copying to tmp table

The server is copying to a temporary table in memory.

服务器正在复制到内存中的临时表

• altering table

The server is in the process of executing an in-place ALTER TABLE.

服务器正在执行就地ALTER TABLE

• copying to tmp table on disk

The server is copying to a temporary table on disk. The temporary result set has become too large (see Section 8.4.4, “Internal Temporary Table Use in MySQL”). Consequently, the thread is changing the temporary table from in-memory to disk-based format to save memory.

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

• creating index

The thread is processing ALTER TABLE … ENABLE KEYS for a MyISAM table.

线程正在为MyISAM表处理ALTER TABLE … ENABLE KEYS

• creating sort index

The thread is processing a SELECT that is resolved using an internal temporary table.

线程正在处理使用内部临时表解析的SELECT

• creating table

The thread is creating a table. This includes creation of temporary tables.

线程正在创建一个表。这包括创建临时表

• creating tmp table

The thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an on-disk table, the state during that operation will be Copying to tmp table on disk.

线程正在内存或磁盘上创建临时表。如果表在内存中创建，但后来转换为磁盘表，则该操作期间的状态将复制到磁盘上的tmp表

• committing alter table to storage engine

The server has finished an in-place ALTER TABLE and is committing the result.

服务器已完成就地ALTER TABLE并提交结

• deleting from main table

The server is executing the first part of a multiple-table delete. It is deleting only from the first table, and saving columns and offsets to be used for deleting from the other (reference) tables.

服务器正在执行多表删除的第一部分。它仅从第一个表中删除，并从其他（引用）表中保存要用于删除的列和偏移量

• deleting from reference tables

The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.

服务器正在执行多表删除的第二部分，并从其他表中删除匹配的行

• discard_or_import_tablespace

The thread is processing an ALTER TABLE … DISCARD TABLESPACE or ALTER TABLE … IMPORT TABLESPACE statement.

线程正在处理ALTER TABLE … DISCARD TABLESPACE或ALTER TABLE … IMPORT TABLESPACE语句

• end

This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements.

这在最后但在清理ALTER TABLE，CREATE VIEW，DELETE，INSERT，SELECT或UPDATE语句之前发生

• executing

The thread has begun executing a statement.

该线程已经开始执行一个语句

• execution of init_command

The thread is executing statements in the value of the init_command system variable.

线程正在执行init_command系统变量的值中的语句

• freeing items

The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up.

线程已经执行了一个命令。在这种状态下完成的项目的一些释放涉及查询缓存。这种状态通常是清理

• FULLTEXT initialization

The server is preparing to perform a natural-language full-text search.

服务器正在准备执行自然语言全文搜索

• init

This occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. Actions taken by the server in this state include flushing the binary log, the InnoDB log, and some query cache cleanup operations.

For the end state, the following operations could be happening:

* Removing query cache entries after data in a table is changed
* Writing an event to the binary log
* Freeing memory buffers, including for blobs

这发生在ALTER TABLE，DELETE，INSERT，SELECT或UPDATE语句的初始化之前。处于此状态的服务器采取的操作包括刷新二进制日志，InnoDB日志和一些查询缓存清理操作。

对于最终状态，可能会发生以下操作：

* 在表中的数据更改后删除查询缓存条目
* 将事件写入二进制日志
* 释放内存缓冲区，包括Blob

• Killed

Someone has sent a KILL statement to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock.

有人向线程发送了KILL语句，下次检查kill标志时应该中止它。在MySQL的每个主循环中检查该标志，但在某些情况下，线程可能需要很短时间才能死机。如果线程被某个其他线程锁定，则一旦其他线程释放锁定，该kill就会生效

• logging slow query

The thread is writing a statement to the slow-query log.

线程正在向慢查询日志写入语句

• login

The initial state for a connection thread until the client has been authenticated successfully.

连接线程的初始状态，直到客户端成功认证为止

• manage keys

The server is enabling or disabling a table index.

服务器启用或禁用表索引

• NULL

This state is used for the SHOW PROCESSLIST state.

此状态用于SHOW PROCESSLIST状态

• Opening tables

The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished. It is also worth checking that your table_open_cache value is large enough.

线程正在尝试打开一个表。这应该是非常快的程序，除非有事情阻止打开。例如，ALTER TABLE或LOCK TABLE语句可以阻止打开表，直到语句完成。还要检查你的table_open_cache值是否足够大

• optimizing

The server is performing initial optimizations for a query.

服务器正在执行查询的初始优化

• preparing

This state occurs during query optimization.

此状态发生在查询优化期间

• Purging old relay logs

The thread is removing unneeded relay log files.

线程正在删除不需要的中继日志文件

• query end

This state occurs after processing a query but before the freeing items state.

处理查询之后，在释放项目状态之前会发生此状态

• Receiving from client

The server is reading a packet from the client. This state is called Reading from net prior to MySQL 5.7.8.

服务器正在从客户端读取数据包。这个状态叫做MySQL 5.7.8之前从网上读取

• Removing duplicates

The query was using SELECT DISTINCT in such a way that MySQL could not optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.

该查询使用SELECT DISTINCT，使得MySQL无法在早期阶段优化不同的操作。因此，MySQL需要一个额外的阶段来删除所有重复的行，然后将结果发送给客户端

• removing tmp table

The thread is removing an internal temporary table after processing a SELECT statement. This state is not used if no temporary table was created.

线程正在处理SELECT语句后删除内部临时表。如果没有创建临时表，则不使用该状态

• rename

The thread is renaming a table.

线程正在重命名一个表

• rename result table

The thread is processing an ALTER TABLE statement, has created the new table, and is renaming it to replace the original table.

线程正在处理ALTER TABLE语句，已创建新表，并将其重命名为替换原始表

• Reopen tables

The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it.

线程获得了表的锁，但在获得基础表结构更改的锁之后注意到。它释放了锁，关闭了桌子，并试图重新打开它

• Repair by sorting

The repair code is using a sort to create indexes.

修复代码正在使用排序来创建索引

• preparing for alter table

The server is preparing to execute an in-place ALTER TABLE.

服务器正在准备执行就地ALTER TABL

• Repair done

The thread has completed a multi-threaded repair for a MyISAM table.

线程已经完成了MyISAM表的多线程修复

• Repair with keycache

The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.

修复代码通过密钥缓存逐个使用创建密钥。这比通过排序修复慢得多

• Rolling back

The thread is rolling back a transaction.

线程正在回滚事务

• Saving state

For MyISAM table operations such as repair or analysis, the thread is saving the new table state to the .MYI file header. State includes information such as number of rows, the AUTO_INCREMENT counter, and key distributions.

对于MyISAM表操作（如修复或分析），线程将新的表状态保存到.MYI文件头。状态包括诸如行数，AUTO_INCREMENT计数器和键分布之类的信息

• Searching rows for update

The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.

线程正在进行第一阶段，以便在更新之前查找所有匹配的行。如果UPDATE更改用于查找涉及的行的索引，则必须执行此操作

• Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

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

• Sending to client

The server is writing a packet to the client. This state is called Writing to net prior to MySQL 5.7.8.

服务器正在向客户端写入数据包。这种状态在MySQL 5.7.8之前被称为写入网络

• setup

The thread is beginning an ALTER TABLE operation.

线程正在开始ALTER TABLE操作

• Sorting for group

The thread is doing a sort to satisfy a GROUP BY.

线程正在做一个满足GROUP BY的排序

• Sorting for order

The thread is doing a sort to satisfy an ORDER BY.

线程正在做一个排序来满足ORDER BY

• Sorting index

The thread is sorting index pages for more efficient access during a MyISAM table optimization operation.

线程是排序索引页面，以便在MyISAM表优化操作期间更有效地访问

• Sorting result

For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.

对于SELECT语句，这与创建排序索引类似，但对于非临时表则类似

• statistics

The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound performing other work.

服务器正在计算统计信息以开发查询执行计划。如果一个线程长时间处于这种状态，服务器可能是磁盘绑定的，执行其他工作

• System lock

The thread has called mysql_lock_tables() and the thread state has not been updated since. This is a very general state that can occur for many reasons.

For example, the thread is going to request or is waiting for an internal or external system lock for the table. This can occur when InnoDB waits for a table-level lock during execution of LOCK TABLES. If this state is being caused by requests for external locks and you are not using multiple mysqld servers that are accessing the same MyISAM tables, you can disable external system locks with the –skip-external-locking option. However, external locking is disabled by default, so it is likely that this option will have no effect. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).

线程已经调用了mysql_lock_tables（），线程状态从未更新。这是一个非常普遍的状态，可能由于许多原因而发生。

例如，线程将要求或正在等待表的内部或外部系统锁定。当InnoDB在执行LOCK TABLES期间等待表级锁时，可能会发生这种情况。如果此状态是由外部锁的请求引起的，并且您没有使用正在访问相同MyISAM表的多个mysqld服务器，则可以使用–skip-external-locking选项禁用外部系统锁。但是，默认情况下禁用外部锁定，因此这个选项很有可能不起作用。对于SHOW PROFILE，此状态表示线程正在请求锁定（不等待）

• update

The thread is getting ready to start updating the table.

• Updating

The thread is searching for rows to update and is updating them.

• updating main table

The server is executing the first part of a multiple-table update. It is updating only the first table, and saving columns and offsets to be used for updating the other (reference) tables.

• updating reference tables

The server is executing the second part of a multiple-table update and updating the matched rows from the other tables.

• User lock

The thread is going to request or is waiting for an advisory lock requested with a GET_LOCK() call. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).

线程将要求或正在等待使用GET_LOCK（）调用请求的咨询锁。对于SHOW PROFILE，此状态表示线程正在请求锁定（不等待）

• User sleep

The thread has invoked a SLEEP() call.

• Waiting for commit lock

FLUSH TABLES WITH READ LOCK is waiting for a commit lock.

• Waiting for global read lock

FLUSH TABLES WITH READ LOCK is waiting for a global read lock or the global read_only system variable is being set.

• Waiting for tables

The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

线程得到一个通知，表格的底层结构已经改变，需要重新打开表以获得新的结构。但是，要重新打开表格，必须等到所有其他线程都关闭该表。

如果另一个线程在相关表中使用FLUSH TABLES或以下语句之一，则会发生此通知：FLUSH TABLES tbl_name，ALTER TABLE，RENAME TABLE，REPAIR TABLE，ANALYZE TABLE或OPTIMIZE TABLE

• Waiting for table flush

The thread is executing FLUSH TABLES and is waiting for all threads to close their tables, or the thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

该线程正在执行FLUSH TABLES，并且正在等待所有线程关闭它们的表，或者该线程得到一个通知表明一个表的底层结构已经改变，并且需要重新打开该表以获得新的结构。但是，要重新打开表格，必须等到所有其他线程都关闭该表。

如果另一个线程在相关表中使用FLUSH TABLES或以下语句之一，则会发生此通知：FLUSH TABLES tbl_name，ALTER TABLE，RENAME TABLE，REPAIR TABLE，ANALYZE TABLE或OPTIMIZE TABLE

• Waiting for lock_type lock

The server is waiting to acquire a THR_LOCK lock or a lock from the metadata locking subsystem, where lock_type indicates the type of lock.

This state indicates a wait for a THR_LOCK:

* Waiting for table level lock

These states indicate a wait for a metadata 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

For information about table lock indicators, see Section 8.11.1, “Internal Locking Methods”. For information about metadata locking, see Section 8.11.4, “Metadata Locking”. To see which locks are blocking lock requests, use the Performance Schema lock tables described at Section 25.11.12, “Performance Schema Lock Tables”.

服务器正在等待从元数据锁定子系统获取THR_LOCK锁或锁，其中lock_type表示锁的类型。

此状态表示等待THR_LOCK：

* 等待桌面锁定

这些状态表示等待元数据锁定：

* 等待事件元数据锁定
* 等待全局读锁
* 等待架构元数据锁定
* 等待存储的功能元数据锁定
* 等待存储过程元数据锁定
* 等待表元数据锁定
* 等待触发元数据锁定

有关表锁定指示器的信息，请参见第8.11.1节“内部锁定方法”。有关元数据锁定的信息，请参见第8.11.4节“元数据锁定”。要查看哪些锁正在阻止锁定请求，请使用第25.11.12节“性能模式锁定表”中描述的性能模式锁定表

• Waiting on cond

A generic state in which the thread is waiting for a condition to become true. No specific state information is available.

线程等待条件成为true的一般状态。没有特定的状态信息可用

• Writing to net

The server is writing a packet to the network. This state is called Sending to client as of MySQL 5.7.8.

服务器正在将数据包写入网络。 这种状态称为MySQL 5.7.8发送给客户端