MySQL SHOW PROCESSLIST 状态详解

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL SHOW PROCESSLIST 状态详解，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11234字，纯文字阅读大概需要17分钟。

内容图文

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.

·         <span style="font-size:13px;f 本文系统来源：http://lee90.blog.51cto.com/10414478/1944808

内容总结

以上是互联网集市为您收集整理的MySQL SHOW PROCESSLIST 状态详解全部内容，希望文章能够帮你解决MySQL SHOW PROCESSLIST 状态详解所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端