MySQL SHOW PROCESSLIST的执行状态

本文主要给大家简单讲讲MySQL SHOW PROCESSLIST的执行状态，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望MySQL SHOW PROCESSLIST的执行状态这篇文章可以给大家带来一些实际帮助。

8.14.2 General Thread States

The following list describes thread State values that are associated withgeneral query processing and not more specialized activities such asreplication. Many of these are useful only for finding bugs in the server.

· After create

This occurs when the thread creates a table (includinginternal 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 (forexample, for ANALYZE TABLE).

· checking permissions

The thread is checking whether the server has therequired 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 tofree memory and reset certain state variables.

· closing tables

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

· converting HEAP to MyISAM

The thread is converting an internaltemporary table from a MEMORY table toan on-disk MyISAM table.

· preparing for alter table

The server is preparing to execute anin-place ALTER TABLE.

· altering table

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

· copy to tmp table

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

For a thread in this state, thePerformance Schema can be used to obtain about the progress of the copyoperation. See Section 25.11.5,"Performance Schema Stage Event Tables".

· committing alter table to storage engine

The server has finished anin-place ALTER TABLE and is committingthe result.

· Copying to group table

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

· Copying to tmp table

The server is copying to a temporarytable in memory.

正在执行查询，并将结果集复制到一个临时表中。这种状态一般要么是做GROUP BY操作，要么是文件排序操作或者UNION操作

· Copying to tmp table on disk

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

结果集过大，内存临时表放不下，产生磁盘临时表

· Creating index

The thread is processing ALTER TABLE ... ENABLE KEYS fora MyISAM table.

· Creating sort index

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

· creating table

The thread is creating a table. This includes creation oftemporary tables.

· Creating tmp table

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

· deleting from main table

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

· deleting from reference tables

The server is executing the second part of amultiple-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.

· end

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

· executing

The thread has begun executing a statement.

· Execution of init_command

The thread is executing statements inthe value of the init_command systemvariable.

· freeing items

The thread has executed a command. Somefreeing of items done during this state involves the query cache. This state isusually followed by cleaningup.

命令已经在执行正在进行清理、更新日志索引等。大量出现这个state则需要检查是不是磁盘IO性能太差了

· FULLTEXT initialization

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

· init

This occurs before the initializationof ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. Actions taken by the server inthis state include flushing the binary log, the InnoDB log, and some query cache cleanupoperations.

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

§ Removing query cache entries after data in a table ischanged

§ Writing an event to the binary log

§ Freeing memory buffers, including for blobs

· Killed

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

· logging slow query

The thread is writing a statement to the slow-query log. 记录慢查询的时候，会产生这个state

· login

The initial state for a connection thread until theclient 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.

· Opening tables

The thread is trying to open a table.This is should be very fast procedure, unless something prevents opening. Forexample, an ALTER TABLE or a LOCK TABLEstatementcan prevent opening a table until the statement is finished. It is also worthchecking that your table_open_cache valueis large enough.

· optimizing

The server is performing initial optimizations for aquery.

· 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 aquery but before the freeingitems state.

· Receiving from client

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

· Removing duplicates

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

· removing tmp table

The thread is removing an internaltemporary table after processing a SELECT statement. This state is not used if notemporary table was created.

· rename

The thread is renaming a table.

· rename result table

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

· Reopen tables

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

· Repair by sorting

The repair code is using a sort to create indexes.

· Repair done

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

· Repair with keycache

The repair code is using creating keysone 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 oranalysis, the thread is saving the new table state to the .MYI file header. State includes information such asnumber of rows, the AUTO_INCREMENT counter,and key distributions.

· Searching rows for update

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

· Sendingdata【别被它的名字欺骗】

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

所谓的"Sending data"并不是单纯的发送数据，而是包括"收集 + 发送数据"。

这里的关键是为什么要收集数据，原因在于：mysql使用"索引"完成查询结束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到磁盘中去找到"数据行"上将需要返回的数据读取出来返回个客户端。

· Sending to client

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

· setup

The thread is beginning an ALTER TABLE operation.

· Sorting for group

The thread is doing a sort to satisfya GROUP BY.

· Sorting for order

The thread is doing a sort to satisfyan ORDER BY.

· Sorting index

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

· Sorting result

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

· statistics

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

· System lock

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

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

· update

The thread is getting ready to start updating the table.

· Updating

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

· updating main table

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

· updating reference tables

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

· User lock

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

· User sleep

The thread has invoked a SLEEP() call.

· Waiting for commit lock

FLUSH TABLES WITH READ LOCK is waiting for acommit lock.

· Waiting for global read lock

FLUSH TABLES WITH READ LOCK is waiting for aglobal read lock or the global read_only systemvariable is being set.

· Waiting for tables

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

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

· Waiting for table flush

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

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

等待其他会话关闭这个表的连接才能执行flush tables操作。

· Waitingfor lock_type lock

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

This state indicates a wait for a THR_LOCK:

§ Waitingfor table level lock

These states indicate a wait for a metadata lock:

§ Waitingfor event metadata lock

§ Waitingfor global read lock

§ Waitingfor schema metadata lock

§ Waitingfor stored function metadata lock

§ Waitingfor stored procedure metadata lock

§ Waitingfor table metadata lock

§ Waitingfor trigger metadata lock

For information about table lock indicators,see Section 8.11.1, "InternalLocking 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 locktables described at Section 25.11.12, "PerformanceSchema Lock Tables".

· Waiting on cond

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

· Writing to net

The server is writing a packet to thenetwork. This state is called Sending to client as of MySQL 5.7.8. 将结果集通过网络返回给客户端

> alter table students add column c1 int ,ALGORITHM=inplace; show profile过程如下：

+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| starting | 0.000060 |

| checking permissions | 0.000004 |

| checking permissions | 0.000003 |

| init | 0.000003 |

| Opening tables | 0.000023 |

| setup | 0.000021 |

| creating table | 0.060620 |

| After create | 0.000077 |

| System lock | 0.000006 |

| preparing for alter table | 0.097194 |

| altering table | 0.027624 |

| committing alter table to stor | 0.166890 |

| end | 0.000019 |

| query end | 0.023681 |

| closing tables | 0.000010 |

| freeing items | 0.000015 |

| cleaning up | 0.000015 |

+--------------------------------+----------+

17 rows in set, 1 warning (0.00 sec)

alter table students add column c2 int ,ALGORITHM=COPY; show profile过程如下：

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000052 |

| checking permissions | 0.000003 |

| checking permissions | 0.000015 |

| init | 0.000002 |

| Opening tables | 0.000047 |

| setup | 0.000034 |

| creating table | 0.026692 |

| After create | 0.000011 |

| System lock | 0.149286 |

| copy to tmp table | 0.051415 |

| rename result table | 0.212463 |

| end | 0.000025 |

| query end | 0.022857 |

| closing tables | 0.000013 |

| freeing items | 0.000114 |

| cleaning up | 0.000032 |

+----------------------+----------+

16 rows in set, 1 warning (0.00 sec)

其他会话flush tables with read lock时候，本会话等待更新数据的时候. show profile过程如下：

+------------------------------+------------+

| Status | Duration |

+------------------------------+------------+

| starting | 0.000081 |

| checking permissions | 0.000006 |

| Opening tables | 0.000009 |

| Waiting for global read lock | 733.001740 |

| Opening tables | 0.000441 |

| init | 0.000051 |

| System lock | 0.000027 |

| updating | 0.000086 |

| end | 0.000006 |

| query end | 0.000006 |

| closing tables | 0.000020 |

| freeing items | 0.000025 |

| cleaning up | 0.000019 |

+------------------------------+------------+

MySQL SHOW PROCESSLIST的执行状态就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的行业资讯。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。