MySQL執行緒狀態詳解

一、show processlist

• Id：連接行程識別符號，是由 CONNECTION_ID() 函式回傳的值
• User：執行陳述句的 MySQL 用戶名稱，如果顯示的是“system user”，它指的是由MySQL生成的非客戶端執行緒正在執行內部任務，例如主備復制中從
  庫上使用的 I/O 或 SQL 執行緒或延遲行處理程式的執行緒，“unauthenticated user”指的是客戶端已經和服務端建立了 TCP/IP 連接但是還沒有對客戶端的用戶進行用戶密碼認證的執行緒，“event_scheduler”是指監視計劃任務調度事件的執行緒，
• Host：執行陳述句的客戶端的主機名，以 host_name：client_port顯示 (如果啟用了 skip_name_resolve 引數，則顯示為 ip:client_port 格式)
• Db：客戶端連接的默認資料庫（如果連接時指定了庫名），否則顯示為 NULL ，
• Command：執行緒正在執行的命令的型別，
• Time：執行緒處于當前狀態的時間數（以秒為單位），對于從庫 SQL 執行緒，該值是最后復制事件的時間和從庫的實際時間之間的秒數，
• State：提示執行緒正在做什么樣的操作，事件或狀態，
• Info：執行緒正在執行的陳述句，

二、command命令型別

• Binlog Dump：主庫執行緒用于將二進制日志內容發送到從庫
• Change user：執行緒正在執行更改用戶操作
• Close stmt：執行緒正在關閉一個預編譯好的陳述句
• Connect：從庫執行緒已經連接到主庫
• Connect Out：從庫正在連接到主庫
• Create DB：執行緒正在執行一個建庫操作
• Daemon：這個是 server 內部執行緒，不是客戶端連接的執行緒
• Debug：執行緒正在生成除錯資訊
• Delayed insert：是一個延遲插入處理程式的執行緒
• Drop DB：執行緒正在執行 drop database 操作
• Execute：執行緒正在執行一個預編譯好的陳述句
• Fetch：執行緒正在執行陳述句并從中獲取結果集
• Field List：執行緒正在檢索表列的資訊
• Init DB：執行緒正在選擇默認資料庫
• Kill：執行緒正在殺死其他執行緒
• Long Data：執行緒在執行陳述句并從中檢索并回傳長欄位（大欄位）型別的資料結果集
• Ping：執行緒正在處理服務器 ping 請求
• Prepare：執行緒正在執行預編譯一個陳述句
• Processlist：執行緒正在生成有關 server 執行緒的資訊
• Query：執行緒正在執行查詢陳述句
• Quit：執行緒正在終止
• Refresh：執行緒正在重繪表，日志或高速快取，或重置狀態變數或復制 server 資訊
• Register Slave：執行緒正在主庫上注冊從庫
• Reset stmt：執行緒正在重置預編譯陳述句
• Set option：執行緒正在設定或重置客戶端陳述句執行選項
• Shutdown：執行緒正在執行關閉 server
• Sleep：執行緒正在等待客戶端向其發送一個新陳述句請求
• Statistics：執行緒正在生成 server 狀態資訊
• Table Dump：執行緒正在將表內容發送到從庫

三、用戶執行緒狀態

• After create：當執行緒創建一個表完成時（包括內部臨時表），會出現這種狀態，
  即使由于某些錯誤而導致創建表最終出錯，也會出現此狀態
• Analyzing：執行緒正在 ANALYZE TABLE
• checking permissions：正在 server 中檢查執行緒是否具有執行陳述句所需的權限
• Checking table：執行緒正在執行表檢查操作
• cleaning up：執行緒已經執行完成了一個命令，并準備釋放所占用的記憶體和重置某些狀態變數
• closing tables：執行緒正在將表發生更改的資料重繪到磁盤并關閉表，
• converting HEAP to MyISAM：執行緒正在將內部臨時表從 MEMORY 引擎表轉換為磁盤 MyISAM 引擎的臨時表
• copy to tmp table：執行緒正在執行 ALTER TABLE 陳述句，此狀態發生在新結構的表已經創建好之后，執行 copy 舊表資料到新表中之前出現
• Copying to group table：如果陳述句使用了不同的 ORDER BY 和 GROUP BY 條件列，則按照 group by 對這些行資料進行排序，并將排序結果復制到臨時表
• Copying to tmp table：server 正在復制資料到記憶體臨時表
• altering table：server 正在執行 in-place 的 ALTER TABLE 的程序
• Copying to tmp table on disk：server 正在復制資料到磁盤臨時表，因為臨時結果集太大，所以，執行緒正在將記憶體臨時表轉換為基于磁盤的臨時表，以節省記憶體
• Creating index：執行緒正在執行一個 ALTER TABLE … ENABLE KEYS 陳述句
• Creating sort index：執行緒正在執行 SELECT 且使用到了內部臨時表
• creating table：執行緒正在創建表，包括創建臨時表時也會使用此狀態
• Creating tmp table：執行緒正在記憶體或磁盤上創建一個臨時表，如果表在記憶體中創建，但后來被轉換為磁盤表，則該操作期間的狀態將為“Copying to tmp table on disk”
• committing alter table to storage engine：server 已執行完成 in-place 演算法的 ALTER TABLE 陳述句，正在提交
• deleting from main table：server 正在執行多表洗掉陳述句中的第一部分，看到這個
  狀態表示正在從第一個表中洗掉，并保存后續用于洗掉其他表的列資料和偏移量
• deleting from reference tables：server 正在執行多表洗掉陳述句的第二部分，從其他表中洗掉匹配的行
• 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：執行緒正在執行一個初始化系統變數的陳述句
• freeing items：執行緒已經執行完成了一個命令，釋放一些涉及到 query cache 狀態
  的 items，這種狀態后通常緊隨 cleaning up 狀態之后
• FULLTEXT initialization：server 正在準備執行自然語言全文搜索
• init：這在 ALTER TABLE，DELETE，INSERT，SELECT 或 UPDATE 陳述句初始化之前發生的狀態，server 在此狀態下執行的操作包括重繪二進制日志，InnoDB 日志和一些查詢快取清理操作，對于這個狀態結束時，可能會有如下一些操作：
  當表中的資料更改后洗掉查詢快取條目
  將事件寫入二進制日志
  釋放記憶體緩沖區，包括 blob
• Killed：向執行緒發起一個 kill 操作，執行緒應該執行終止操作，在 MySQL 的每個主回圈中檢查執行緒的 kill 標志，但在某些情況下，殺死執行緒可能只需要很短的時間，但如果被 kill 的執行緒被其他執行緒鎖定，則需要等待其他執行緒釋放鎖之后，kill 命令才會生效并執行，
• logging slow query：執行緒正在向慢查詢日志寫一條陳述句
• login：連接執行緒的初始狀態，直到客戶端成功通過身份驗證
• manage keys：server 正在啟用或禁用表索引
• NULL：此狀態用于 SHOW PROCESSLIST 陳述句
• Opening tables：執行緒正嘗試打開一個表，打開表操作應該非常快，除非打開操作被阻止，例如，ALTER TABLE 或 LOCK TABLE 陳述句可以防止打開表，直到該陳述句完成，另外也可能是 table_open_cache 不夠大導致不能打開表，
• optimizing：server 正在對查詢執行初始優化
• preparing：此狀態發生在查詢優化期間
• Purging old relay logs：執行緒正在洗掉不需要的中繼日志檔案
• query end：此狀態出現在執行查詢陳述句之后但在釋放該查詢陳述句相關狀態 items 之前
• Reading from net：server 正在從網路讀取資料包，在 MySQL 5.7.8 之后該狀態叫做“Receiving from client” - Receiving from client：server 正在從客戶端讀取資料包，在 MySQL 5.7.8 叫做“Reading from net”
• Removing duplicates：查詢使用 SELECT DISTINCT 陳述句時，使 MySQL 無法在早期階段優化掉 distinct 操作，因此，MySQL 需要一個額外的階段來洗掉所有重復的行，然后將結果發送到客戶端
• removing tmp table：執行緒在 SELECT 陳述句執行完成后，正在洗掉內部臨時表，如果 SELECT 陳述句未創建臨時表，則不會出現此狀態
• rename：執行緒正在執行 rename 陳述句重命名表
• rename result table：執行緒正在執行 ALTER TABLE 陳述句重命名表，已經創建完成新表，并正在使用新表替換舊表名稱
• Reopen tables：執行緒獲得了表鎖，但是獲得鎖后，發現基礎表結構已經被改變了，
  于是釋放表鎖，并關閉表，嘗試重新打開表
• Repair by sorting：修復代碼正在使用排序來創建索引
• preparing for alter table：server 正在準備執行 in-place 演算法的 ALTER TABLE 語 句 - Repair done：該執行緒已完成 MyISAM 表的多執行緒修復
• Repair with keycache：修復代碼正在使用通過 key cache 逐個創建 key 的方法修復索引，這比通過排序索引修復的方法慢得多
• Rolling back：執行緒正在回滾事務
• Saving state：對于 MyISAM 表操作（如修復或分析），執行緒正在將新表狀態保存到.MYI 檔案頭，狀態包括：表資料行數,AUTO_INCREMENT 計數器和 key
  分布之類的資訊
• Searching rows for update：執行緒正在進行第一階段查找所有匹配的行，然后再更新它們，如果 UPDATE 正在更改用于查找涉及的行的索引，則必須先把 update 滿足匹配的行先查找出來
• Sending data：執行緒正在讀取和處理 SELECT 陳述句產生的資料行，并將資料發送到客戶端，因為在此狀態期間發生的操作可能產生大量的磁盤訪問（讀取），所以它通常是給定查詢的生存期內最長的運行狀態
• Sending to client：server 正在向客戶端寫入資料包，在 MySQL 5.7.8 之前叫做“Writing to net”
• setup：執行緒正在執行 ALTER TABLE 操作
• Sorting for group：執行緒正在執行一個 GROUP BY 排序操作
• Sorting for order：執行緒正在執行一個 ORDER BY 排序操作
• Sorting index：執行緒正在排序索引頁面，以便在 MyISAM 表優化操作期間實作更高效的訪問
• Sorting result：對于 SELECT 陳述句，這類似“Creating sort index”狀態，但是針對于非臨時表
• statistics：server 正在計算統計資訊以優化查詢執行計劃，如果一個執行緒在這個狀態很長一段時間，server 可能是磁盤執行其他作業而阻塞了統計資訊的操作，也有可能發生了鎖等待，
• System lock：執行緒呼叫了mysql_lock_tables()，執行緒狀態從未更新過，這是一個非常常見的狀態，出現該狀態的原因有很多，例如，執行緒將請求或正在等待表的內部或外部系統鎖定，當 InnoDB 在執行 LOCK TABLES 期間等待表級鎖時，可能會發生這種情況，如果此狀態是由外部鎖請求引起的，如果您不使用多個mysqld 服務器訪問同一 MyISAM 表，則可以使用–skip-external-locking 選項禁用外部系統鎖，但是，默認情況下外部鎖定是禁用的，因此此選項可能無效，
  對于 SHOW PROFILE，此狀態表示執行緒正在請求鎖定
• update：執行緒準備開始更新表
• Updating：執行緒搜索且正在更新資料行
• updating main table：server 正在執行多表更新陳述句的第一部分，該狀態表示正在
  更新第一個表，并保存列值和偏移量以用于更新其他（參考）表
• updating reference tables：server 正在執行多表更新陳述句的第二部分，更新其他表
  的匹配行
• 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
• OPTIMIZE TABLE

• Waiting for table flush：執行緒正在執行 FLUSH TABLES，并且正在等待所有執行緒關閉所訪問的表，或者執行緒得到一個表的底層結構已經改變的通知，它需要重新打開表以獲得新的結構，但是，要重新打開表，它必須等待，直到所有其他執行緒都關閉了舊表結構的訪問，如果另一個執行緒已在表中使用 FLUSH TABLES 或下列陳述句之一，則就會出現這個通知：

• FLUSH TABLES tbl_name
• ALTER TABLE
• RENAME TABLE
• REPAIR TABLE
• ANALYZE TABLE
• OPTIMIZE TABLE

• Waiting for lock_type lock：server 正在等待獲得一個 THR_LOCK 鎖或者從元資料鎖定子系統中獲取一個 MDL 鎖，其中 lock_type 表示正在等待獲得的 MDL 鎖的型別，THR_LOCK 只有一種（Waiting for table level lock），MDL 鎖有如下幾種：

• 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

• Waiting on cond：執行緒正在等待條件變為 true 的通用狀態，沒有特定的狀態資訊可用
• Writing to net：server 正在向網路寫入資料包，從 MySQL 5.7.8 之后叫做“Sending to client”

四、dump執行緒狀態

• Finished reading one binlog; switching to next binlog：執行緒已經完成讀取 binlog 文
  件，并切換到下一個 binlog 檔案
• Master has sent all binlog to slave; waiting for more updates：執行緒已經從二進制日志中讀取了所有剩余的更新日志，并將它們發送到從庫，執行緒當前處于空閑狀態，正在等待新的更新資料的事件寫入二進制日志中
• Sending binlog event to slave：執行緒已經從二進制日志中讀取了一個事件，現在將其發送到從庫（二進制日志由事件組成，一個事件通常是由發生更新的資料和一些其他資訊組成）
• Waiting to finalize termination：執行緒停止時發生的非常短暫的狀態，執行緒正在執行停止執行緒相關的動作

五、IO執行緒狀態

• Checking master version：在建立與主庫的連接之后非常短暫的狀態，表示正在檢查主庫的版本號
• Connecting to master：執行緒嘗試連接到主庫
• Queueing master event to the relay log：執行緒已讀取一個事件，并將其復制到中繼日志，以便 SQL 執行緒進行重放
• Reconnecting after a failed binlog dump request：執行緒正在嘗試重新連接到主庫
• Reconnecting after a failed master event read：執行緒正在嘗試重新連接到主庫，當重連連接成功時，狀態將變為“Waiting for master to send event” - Registering slave on master：在連接到主庫成功之后非常短暫的狀態，表示正在向主庫注冊從庫的連接資訊（如從庫的 IP 和埠資訊等）
• Requesting binlog dump：在與主庫建立連接成功之后非常短暫的狀態，使用當
  前的 I/O 執行緒位置，向主庫發送從當前位置開始的二進制日志的內容的請求
• Waiting for its turn to commit：如果啟用了 slave_preserve_commit_order 引數，則
  表示從庫 I/O 執行緒正在等待較舊的作業執行緒提交資料
• Waiting for master to send event：執行緒已經連接到主庫并且正在等待新的二進制
  日志事件，如果主庫空閑，這可能持續很長時間，如果等待時間持續超過slave_net_timeout 秒，則從庫 I/O 執行緒發生超時，此時，從庫 I/O 執行緒認為主庫的連接斷開，會嘗試重新連接主庫
• Waiting for master update：連接到主庫之前的初始狀態
• Waiting for slave mutex on exit：執行緒停止時短暫發生的狀態，表示正在回收 I/O 執行緒的相關互斥資源
• Waiting for the slave SQL thread to free enough relay log space：如果 relay_log_space_limit 變數設定值不為 0，那么當中繼日志總大小增長到超過此值時， I/O 執行緒會等待，直到 SQL 執行緒通過重放中繼日志內容并洗掉重放完成的中繼日志以釋放中繼日志占用的空間，使其滿足中繼日志中大小不大于 relay_log_space_limit 變數的值時，I/O 執行緒才可以繼續寫入中繼日志操作，
• Waiting to reconnect after a failed binlog dump request：如果二進制日志 dump 請求失敗（由于斷開連接），那么執行緒在進入 sleep 狀態，此時出現此狀態，然后I/O 執行緒定期嘗試重新連接主庫，重試之間的間隔時間可以使用 CHANGE MASTER TO 陳述句的 MASTER_CONNECT_RETRY 選項指定

• 要注意，從庫的 I/O 執行緒連接主庫是有心跳機制的，當主庫超過這個心跳時間沒有發送新的 event 到 slave 上時，I/O 執行緒就對主庫發起一個心跳請求，如果請求成功就重置心跳時間，當主庫有新的 event 發送到 slave 時，這個心跳時 間 也 會 進 行 重 置 ， 心 跳 時 間 由 change master 陳述句的MASTER_HEARTBEAT_PERIOD 選項設定（以秒為單位），范圍 0 到 4294967 秒，解析度（毫秒）最小非零值為0.001，表示 1 毫秒，將間隔設定為 0 時表示禁用心跳，默認值是slave_net_timeout 配置引數的二分之一，so，理論上是不會出現主從資料庫正常的情況下因為主庫沒有寫資料而導致從庫 I/O 執行緒斷開的情況，

• Waiting to reconnect after a failed master event read：讀取主庫 binlog 時發生錯誤（由于斷開連接），I/O 執行緒在嘗試重新連接主庫之前，執行緒正在以 CHANGE MASTER TO 陳述句的 MASTER_CONNECT_RETRY 選項（默認為 60）設定的秒數進行 sleep（該時間是重連失敗之后的重試間隔時間）

六、SQL 執行緒狀態

• Killing slave：執行緒正在處理 STOP SLAVE 陳述句
• Making temporary file（append）before replaying LOAD DATA INFILE：執行緒正在執行 LOAD DATA INFILE 陳述句，并將從庫將要讀取的資料添加到臨時檔案中
• Making temporary file（create）before replaying LOAD DATA INFILE：執行緒正在執行 LOAD DATA INFILE 陳述句，且正在創建臨時檔案，臨時檔案中包含了從庫將要讀取行資料，注意：只有在 MySQL 5.0.3 之前的版本中，主庫記錄了原始LOAD DATA INFILE 陳述句時，才能遇到此狀態
• Reading event from the relay log：執行緒正在從中繼日志中讀取事件，以便進行重放
• Slave has read all relay log; waiting for more updates：執行緒已重做完所有的中繼日志檔案中的所有事件，正在等待 I/O 執行緒向中繼日志中寫入新的事件
• Waiting for an event from Coordinator：從庫使用多執行緒復制時（slave_parallel_workers 大于 1），此狀態表示一個 slave works 執行緒正在等待協調器執行緒（Coordinator 執行緒）分配日志事件
• Waiting for slave mutex on exit：執行緒停止時發生的非常短暫的狀態
• Waiting for Slave Workers to free pending events：當 Workers 執行緒處理的事件的總數量大小超過 slave_pending_jobs_size_max 系統變數的大小時，會發生等待操作（協調器執行緒不進行分配事件給 worker 執行緒），當 Workers 執行緒處理的事件的總數量大小低于 slave_pending_jobs_size_max 限制時，協調器恢復調度，只有當 slave_parallel_workers 設定為大于 0 時，此狀態才會出現
• Waiting for the next event in relay log：“Reading event from the relay log”狀態之前的初始狀態
• Waiting until MASTER_DELAY seconds after master executed event：SQL 執行緒已讀取事件，但并沒有進行應用，而是正在等待從庫設定的延遲復制時間失效，此延遲時間使用 CHANGE MASTER TO 的 MASTER_DELAY 選項設定
  ? SQL 執行緒的 Info 列也可以顯示陳述句的文本，這表示執行緒已經從中繼日志中讀取了一個事件，并從中提取了 SQL 陳述句，當前可能正在執行這個陳述句對應的事件，

七、 主從連接執行緒狀態

• Changing master：執行緒正在處理 CHANGE MASTER TO 陳述句
• Killing slave：執行緒正在處理 STOP SLAVE 陳述句
• Opening master dump table：此狀態發生在主庫創建 dump 表之后
• Reading master dump table data："Opening master dump table"狀態之后出現的狀態，表示正在從主庫 dump 表讀取資料
• Rebuilding the index on master dump table：“Reading master dump table data”狀態之后出現的狀態，表示正在重建主庫 dump 表索引

八、事件調度執行緒狀態

• Clearing：調度程式執行緒正在停止執行事件
• Initialized：調度程式執行緒已初始化完成，將要執行調度事件
• Waiting for next activation：調度程式具有非空事件佇列時，正在等待未來某個時間點激活佇列中的某個事件，以便進行調度并執行
• Waiting for scheduler to stop：執行緒發出 SET GLOBAL event_scheduler = OFF 并等待調度程式停止
• Waiting on empty queue：調度程式的事件佇列為空，因此調度程式處于休眠狀態