問題描述

您是否曾經(jīng)試圖殺死一個查詢，但它并沒有消失，而是在運行的查詢中停留了很長一段時間？或者你注意到一些被標記為被殺死的線程不時出現(xiàn)并且實際上并沒有死亡。這些僵尸線程是什么？為什么 MySQL 有時似乎無法快速終止查詢？本章對此問題進行剖析。

Threads and connections

MySQL 為每個客戶端連接使用單獨的線程。發(fā)送到 MySQL 的查詢由先前與查詢的連接關聯(lián)的線程處理。任何擁有足夠權限的人都可以通過運行SHOW PROCESSLIST命令查看當前活動線程的列表以及一些其他詳細信息，該命令返回一個類似表的視圖，每一行都是個連接。具體如下：

任何正在運行的查詢或列表中的任何現(xiàn)有連接都可以使用KILL命令進行終止。

命令語法：

KILL [QUERY | CONNECTION] <thread_id>

其中thread_id是來自進程列表輸出的第一列的值?？蛇x參數(shù)是只運行的查詢還是終止整個會話。它默認為后者，因此不需要指定CONNECTION 。

運行該命令實際上并沒有做任何事情，除了在所選線程內(nèi)設置一個特殊標志。因此 kill 操作不會與相應的 request 同步發(fā)生。在許多情況下，線程或查詢需要一些時間才能停止。在語句執(zhí)行的各個階段檢查標志。它何時準確發(fā)生或發(fā)生頻率如何，取決于線程實際正在執(zhí)行的工作。

例如：

• 在ALTER TABLE期間，當 MySQL 將數(shù)據(jù)重寫到新的臨時表中時，在從原始表中讀取每個行塊之前檢查它。在此過程中，臨時表被刪除，原始結(jié)構保持不變。
• 對于UPDATE和DELETE，在每個更新或刪除的行之后還有一個額外的檢查。如果查詢注意到請求終止之前，進行了任何更改，則必須回滾它們。注意：如果表的存儲引擎不支持事務（例如MyISAM），則更改無法回滾，因此該操作將導致部分更新！
• SELECT在讀取一行行后也會檢查它。

當然，這些只是基本示例，不同情況下，可能會有所不同。在某些情況下，某些查詢甚至可能無法終止。從
INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_INDEX表中讀取時發(fā)生過一次這種情況。數(shù)據(jù)庫當時存在性能問題，查詢執(zhí)行基本上停止在 InnoDB 深處的某個鎖上，并且它再也沒有回到可以再次檢查標志值的點上。

線程長時間處于終止狀態(tài)。這是什么意思？

實際上，可以有兩種不同的情況。如果發(fā)出KILL <thread_id>，則列表中的進程將被殺死。KILL QUERY <thread_id>不會終止連接，而是只停止連接中正在運行的查詢，因此在這種情況下，可能會出現(xiàn)查詢結(jié)束文本。

如果你看到這樣的東西怎么辦？

它可能是錯誤（例如錯誤#52528），或者更可能是數(shù)據(jù)庫在內(nèi)部執(zhí)行一些工作，以便任務終止后進行清理。

當然，沒有簡單的方法來確認這是否是一個錯誤。因此，為了弄清楚它，您應該尋找證明它不是錯誤的證據(jù)。您所看到的只是標準操作的效果，MySQL 在查詢或事務之后必須執(zhí)行該操作以進行清理。

一個線程在被殺死或查詢結(jié)束的情況下停留更長時間的最常見原因可能是等待InnoDB 表上的事務回滾。這有時可能需要很長時間才能完成，尤其是當必須刪除數(shù)十萬或數(shù)百萬的更改時。

如何驗證？

檢查是SHOW ENGINE INNODB STATUS\G的輸出。如果當前正在進行回滾，會有如下簡單的信息：

---TRANSACTION 0 10411, ACTIVE 28 sec, process no 15506, OS thread id 140732309711184 rollback 
mysql tables in use 1, locked 1 ROLLING BACK 7585 lock struct(s), heap size 751600, undo log entries 798854
 MySQL thread id 14 , query id 206 localhost root end 
update testdb.sometable set cc=sqrt(id)

該示例顯示了如果一個線程被標記為kill或query end，并且 InnoDB 引擎狀態(tài)報告同一線程的回滾，請等待它結(jié)束。

如果不是這樣呢？

它可能與從磁盤中刪除一些臨時表有關。一個ALTER TABLE可能需要丟棄一個非常大的臨時表，而在某些文件系統(tǒng)（例如ext3或ext4）上刪除大文件可能相當慢，因此可能需要幾秒鐘甚至更長的時間。任何 DML 語句也可以創(chuàng)建臨時表，但通常不會那么大。

在最極端的情況下，刪除文件也不會超過十或二十秒，但在 I/O 負載較重的情況下，它可能會更長，所以有什么方法可以查看是否有臨時表是否被創(chuàng)造？在Percona Server或MariaDB中，您檢查INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLE和INFORMATION_SCHEMA.TEMPORARY_TABLE的內(nèi)容。它僅適用于使用CREATE TEMPORARY TABLE語句手動建立的臨時表，不適用于 MySQL 隱式創(chuàng)建的執(zhí)行復雜查詢時的臨時表。

SESSION_ID與進程列表中的線程標識符相同，因此您可以殺死任何連接到臨時表的線程。

其他類型的臨時表不容易追蹤。有時可以在SHOW PROCESSLIST輸出中發(fā)現(xiàn)有用信息：

有時您可以檢查實例打開的臨時文件：

紅色標記的值是文件的大小。這些可以幫助您定位正在刪除的臨時表。

結(jié)論

有時看到一個數(shù)據(jù)庫線程被殺死，但線程并沒有立即被終止，或者一個查詢似乎無法結(jié)束，這可能是正常的。因為實際操作不會與 kill 命令同步執(zhí)行，之后 MySQL可能仍需要一些時間才能正確清理。不管真正的原因是什么，如果不重新啟動數(shù)據(jù)庫，就不可能擺脫這種掛起的線程。也沒有辦法像Unix 系統(tǒng)中的kill -9 <pid>那樣立即強制執(zhí)行kill。