前言： 线程是真正工作的“马”，无论是中间件还是数据库要分析系统性能问题、资源瓶颈，要了解当时正在做什么是非常必要的，我们常用show processlist
或show full processlist
查看数据库线程状态，其中比较关注的是 State 列，此列表示该连接此刻所在的状态。那么你真的了解不同 State 值所表示的状态吗？下面根据官方文档列出来线程状态具体含义。
· After create
This occurs when the threadcreates a table (including internal temporary tables), at the end of thefunction that creates the table. This state is used even if the table could notbe created due to some error.
· Analyzing
The thread is calculating a MyISAM
table key distributions (for example, for ANALYZE TABLE
).
· checking permissions
The thread is checking whetherthe server has the required privileges to execute the statement.
· Checking table
The thread is performing atable check operation.
· cleaning up
The thread has processed onecommand and is preparing to free memory and reset certain state variables.
· closing tables
The thread is flushing thechanged table data to disk and closing the used tables. This should be a fastoperation. If not, verify that you do not have a full disk and that the disk isnot in very heavy use.
· converting HEAP to ondisk
The thread is converting aninternal temporary table from a MEMORY
table to an on-disk table.
· copy to tmp table
The thread is processing an ALTER TABLE
statement. This state occurs after the table with the new structurehas been created but before rows are 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.12.5, “Performance Schema Stage Event Tables”.
· Copying to group table
If a statement has different ORDERBY
and GROUP BY
criteria, the rows are sorted by group andcopied to a temporary table.
· Copying to tmp table
The server is copying to atemporary table in memory.
· altering table
The server is in the process ofexecuting an in-place ALTER TABLE
.
· Copying to tmp table on disk
The server is copying to atemporary 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 fromin-memory to disk-based format to save memory.
· Creating index
The thread is processing ALTERTABLE ... ENABLE KEYS
for a MyISAM
table.
· Creating sort index
The thread is processing a SELECT
that is resolved using an internal temporary table.
· creating table
The thread is creating a table.This includes creation of temporary tables.
· Creating tmp table
The thread is creating atemporary table in memory or on disk. If the table is created in memory butlater is converted to an on-disk table, the state during that operation will beCopying to tmp table on disk
.
· committing alter table to storage engine
The server has finished anin-place ALTER TABLE
and is committing the result.
· deleting from main table
The server is executing thefirst part of a multiple-table delete. It is deleting only from the firsttable, and saving columns and offsets to be used for deleting from the other(reference) tables.
· deleting from reference tables
The server is executing thesecond part of a multiple-table delete and deleting the matched rows from theother tables.
· discard_or_import_tablespace
The thread is processing an ALTERTABLE ... DISCARD TABLESPACE
or ALTER TABLE ... IMPORT TABLESPACE
statement.
· end
This occurs at the end butbefore the cleanup of ALTER TABLE
, CREATE VIEW
, DELETE
, INSERT
, SELECT
, or UPDATE
statements.
· executing
The thread has begun executinga statement.
· Execution of init_command
The thread is executingstatements in the value of the init_command
system variable.
· freeing items
The thread has executed acommand. 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 toperform a natural-language full-text search.
· init
This occurs before theinitialization of ALTER TABLE
, DELETE
, INSERT
, SELECT
, or UPDATE
statements. Actions taken by the server in this state includeflushing the binary log, the InnoDB
log, and some query cachecleanup operations.
For the end
state,the following operations could be happening:
o Removing query cache entries after data in a table is changed
o Writing an event to the binary log
o Freeing memory buffers, including for blobs
· Killed
Someone has sent a KILL
statement to the thread and it should abort next time it checks thekill flag. The flag is checked in each major loop in MySQL, but in some casesit might still take a short time for the thread to die. If the thread is lockedby some other thread, the kill takes effect as soon as the other threadreleases its lock.
· logging slow query
The thread is writing astatement to the slow-query log.
· login
The initial state for aconnection thread until the client has been authenticated successfully.
· manage keys
The server is enabling ordisabling a table index.
· NULL
This state is used for the SHOWPROCESSLIST
state.
· Opening tables
The thread is trying to open atable. This is should be very fast procedure, unless something preventsopening. For example, an ALTER TABLE
or a LOCK TABLE
statement can prevent opening a table until the statement isfinished. It is also worth checking that your table_open_cache
value is large enough.
· optimizing
The server is performinginitial optimizations for a query.
· preparing
This state occurs during queryoptimization.
· Purging old relay logs
The thread is removing unneededrelay log files.
· query end
This state occurs afterprocessing a query but before the freeing items
state.
· Receiving from client
The server is reading a packetfrom the client. This state is called Reading from net
prior toMySQL 5.7.8.
· Removing duplicates
The query was using SELECT DISTINCT
in such a way that MySQL could not optimize away the distinctoperation at an early stage. Because of this, MySQL requires an extra stage toremove all duplicated rows before sending the result to the client.
· removing tmp table
The thread is removing aninternal temporary table after processing a SELECT
statement. This state is not used if no temporary table wascreated.
· 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 replacethe original table.
· Reopen tables
The thread got a lock for thetable, but noticed after getting the lock that the underlying table structurechanged. It has freed the lock, closed the table, and is trying to reopen it.
· Repair by sorting
The repair code is using a sortto create indexes.
· preparing for alter table
The server is preparing toexecute an in-place ALTER TABLE
.
· Repair done
The thread has completed amultithreaded repair for a MyISAM
table.
· Repair with keycache
The repair code is usingcreating keys one by one through the key cache. This is much slower than Repairby sorting
.
· Rolling back
The thread is rolling back atransaction.
· Saving state
For MyISAM
tableoperations such as repair or analysis, the thread is saving the new table stateto the .MYI
file header. State includes information such as numberof rows, the AUTO_INCREMENT
counter, and key distributions.
· Searching rows for update
The thread is doing a firstphase to find all matching rows before updating them. This has to be done ifthe UPDATE
is changing the index that is used to find the involved rows.
· Sending data
The thread is reading andprocessing rows for a SELECT
statement, and sending data to the client. Because operationsoccurring during this state tend to perform large amounts of disk access(reads), it is often the longest-running state over the lifetime of a givenquery.
· Sending to client
The server is writing a packetto the client. This state is called Writing to net
prior to MySQL5.7.8.
· setup
The thread is beginning an ALTER TABLE
operation.
· Sorting for group
The thread is doing a sort tosatisfy a GROUP BY
.
· Sorting for order
The thread is doing a sort tosatisfy an ORDER BY
.
· Sorting index
The thread is sorting indexpages for more efficient access during a MyISAM
table optimizationoperation.
· Sorting result
For a SELECT
statement, this is similar to Creating sort index
, butfor nontemporary tables.
· statistics
The server is calculatingstatistics to develop a query execution plan. If a thread is in this state fora 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 statethat can occur for many reasons.
For example, the thread isgoing to request or is waiting for an internal or external system lock for thetable. 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 andyou 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 islikely that this option will have no effect. For SHOW PROFILE
, this state means the thread is requesting the lock (not waitingfor it).
· update
The thread is getting ready tostart updating the table.
· Updating
The thread is searching forrows to update and is updating them.
· updating main table
The server is executing thefirst part of a multiple-table update. It is updating only the first table, andsaving columns and offsets to be used for updating the other (reference)tables.
· updating reference tables
The server is executing thesecond part of a multiple-table update and updating the matched rows from theother tables.
· User lock
The thread is going to requestor 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 waitingfor it).
· 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
systemvariable is being set.
· Waiting for tables
The thread got a notificationthat the underlying structure for a table has changed and it needs to reopenthe table to get the new structure. However, to reopen the table, it must waituntil all other threads have closed the table in question.
This notification takes placeif another thread has used FLUSH TABLES
or one of the following statements on the 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 to close their tables, or the threadgot a notification that the underlying structure for a table has changed and itneeds to reopen the table to get the new structure. However, to reopen thetable, it must wait until all other threads have closed the table in question.
This notification takes placeif another thread has used FLUSH TABLES
or one of the following statements on the table in question: FLUSHTABLES tbl_name
, ALTER TABLE
, RENAME TABLE
, REPAIR TABLE
, ANALYZE TABLE
, or OPTIMIZE TABLE
.
· Waiting for lock_type lock
The server is waiting toacquire a THR_LOCK
lock or a lock from the metadata lockingsubsystem, where lock_type
indicates the type of lock.
This state indicates a wait fora THR_LOCK
:
o Waiting for table level lock
These states indicate a waitfor a metadata lock:
o Waiting for event metadata lock
o Waiting for global read lock
o Waiting for schema metadata lock
o Waiting for stored function metadata lock
o Waiting for stored procedure metadata lock
o Waiting for table metadata lock
o Waiting for trigger metadata lock
For information about tablelock indicators, see Section 8.11.1, “InternalLocking Methods”. Forinformation about metadata locking, see Section 8.11.4, “MetadataLocking”. To see whichlocks are blocking lock requests, use the Performance Schema lock tablesdescribed at Section 25.12.12,“Performance Schema Lock Tables”.
· Waiting on cond
A generic state in which thethread is waiting for a condition to become true. No specific state informationis available.
· Writing to net
The server is writing a packetto the network. This state is called Sending to client
as of MySQL5.7.8.
记得点在看哦，
让大家也看看。
