大量刪除導(dǎo)致MySQL慢查的示例分析

這篇文章將為大家詳細講解有關(guān)大量刪除導(dǎo)致MySQL慢查的示例分析，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

一、背景

監(jiān)控上收到了大量慢查的告警，業(yè)務(wù)也反饋查詢很慢，隨即打開電腦確認慢查的原因。

二、現(xiàn)象描述

通過平臺的慢查分析之后，我們發(fā)現(xiàn)慢查有以下特征：

• 慢查的表名都是 sbtest1，沒有其他的表；

• 大部分的慢查都是查表最新的數(shù)據(jù)，例如 select * from sbtest1 limit 1;

• rows examined 為 1，沒有掃描大量的數(shù)據(jù)。

三、問題分析

通對慢查的大致分析，SQL 本身沒有發(fā)現(xiàn)問題。那么是不是主機或者網(wǎng)絡(luò)等有問題呢？

經(jīng)過對網(wǎng)絡(luò)和主機磁盤的 IO 等的分析，負載均正常，沒有丟包的現(xiàn)象。

回到數(shù)據(jù)庫本身，慢查還在，確認下慢查到底是慢在哪里。

當(dāng)慢查在執(zhí)行的時候，大部分的都是表現(xiàn)在 Sending data 的狀態(tài)，我們通過 profiling 去確認下慢查的時間分布：

從圖中，我們可以看到 sending data 耗費的時間為 0.945 秒，基本占據(jù)了 SQL 執(zhí)行時間的99%。

那么 sending data 是什么意思呢，我們從官方文檔里面了解下。

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

Sending data 表示在讀取以及處理行數(shù)據(jù)以及發(fā)送數(shù)據(jù)到客戶端，由于數(shù)據(jù)只有一行，且當(dāng)時網(wǎng)絡(luò)確認正常，那么時間就是耗費在讀取和處理 select 的數(shù)據(jù)。

那為啥只取 limit 1，而且沒有 where 條件的 SQL 執(zhí)行掃描一行數(shù)據(jù)會這么慢呢？

打開監(jiān)控，看看有沒有啥指標異常。

我們注意到數(shù)據(jù)庫的 History list length 這個指標一直在升高，達到了幾萬。慢查的執(zhí)行時間是隨著 History list length 升高而變的更慢。當(dāng) History list length 一直居高不下的時候，說明了有大量的 UNDO 沒有被 purge。由于當(dāng)前數(shù)據(jù)庫的隔壁級別是 RR，開始比較早的事務(wù)，如果還沒提交，就需要通過 UNDO 去構(gòu)建對應(yīng)版本歷史時，保證數(shù)據(jù)庫的可重復(fù)讀（跟 MVCC 有關(guān)）。

既然 History list length 那么高，可能是有歷史事務(wù)出現(xiàn)異常沒有提交，也有可能是一致性快照的備份。可以通過 information_schema.innodb_trx 表去確認對應(yīng)的事務(wù)信息。經(jīng)過查詢，的確發(fā)現(xiàn)一個事務(wù)執(zhí)行了4個小時左右，沒有提交，且不是備份用戶。手動將該線程執(zhí)行 kill 操作，慢查消失。

3.1 聊一下 MVCC

MySQL InnoDB 支持 MVCC 多版本，可以在普通的 SELECT 時不加鎖。利用多版本讀取指定版本的行記錄，降低加鎖的次數(shù)，能極大提高數(shù)據(jù)庫的并發(fā)讀寫能力。

Innodb 在事務(wù)的某個時刻記錄下 MySQL 所有的活躍事務(wù)列表，保存到 read view 里面。在之后的查詢中，通過比較記錄的事務(wù)ID和 read view 里面的事務(wù)列表，判斷記錄是否可見。

3.1.1 Innodb 行記錄

在 Innodb 的行結(jié)構(gòu)中，還存在三個系統(tǒng)列，分別是 DATA_ROW_ID、DATA_TRX_ID、DATA_ROLL_PTR。

• DATA_ROW_ID: 如果表沒有顯示定義主鍵，則采用 MySQL 自己生成的 ROW_ID，為 48-bit，否則表示的是用戶自定義的主鍵值；

• DATA_TRX_ID：表示這條記錄的事務(wù) ID。如果是二級索引，只在 page 里面保存 trx_id；

• DATA_ROLL_PTR: 指向?qū)?yīng)的回滾段的指針。

3.1.2 read view

read view 是在 SQL 語句執(zhí)行之前申請的，其中 RC 隔離級別是每個 SELECT 都會申請，RR 隔離級別的 read view 是事務(wù)開始之后的第一個 SQL 申請，之后事務(wù)內(nèi)的其他 SQL 都使用該 read view。

read view 中有三個變量需要重點關(guān)注：

• low_limit_id：表示的是創(chuàng)建 read view 那一刻活躍的事務(wù)列表的最大的事務(wù) ID；

• up_limit_id：表示的是創(chuàng)建 read view 那一刻活躍的事務(wù)列表的最小的事務(wù) ID；

• trx_ids：表示的創(chuàng)建 read view 那一刻所有的活躍事務(wù)列表。

3.1.3 判斷記錄可見

• 當(dāng)記錄的 DATA_TRX_ID 小于 read vew 的 up_limit_id，說明該記錄在創(chuàng)建 read view 之前就已經(jīng)提交，記錄可見；

• 如果記錄的 DATA_TRX_ID 和事務(wù)創(chuàng)建者的 TRX_ID 一樣，記錄可見；

• 當(dāng)記錄的 DATA_TRX_ID 大于 read view 的 up_limit_id，說明該記錄在創(chuàng)建 read view 之后進行的新建事務(wù)修改提交的，記錄不可見；

• 在 RR 隔離級別，如果 A 事務(wù)在 B 事務(wù)創(chuàng)建 read view 之前開始的，那么 B 事務(wù)里面的 SQL 是不能看到 A 事務(wù)執(zhí)行的修改的。因此還有一條規(guī)則：如果記錄對應(yīng)的 DATA_TRX_ID 在 read view 的 trx_ids 里面，那么該記錄也是不可見的。

3.1.4 DATA_ROLL_PTR

UNDO 日志是 MVCC 的重要組成部分，當(dāng)一條數(shù)據(jù)被修改時，UNDO 日志里面保存了記錄的歷史版本。當(dāng)事務(wù)需要查詢記錄的歷史版本時，可以通過 UNDO 日志構(gòu)建特定版本的數(shù)據(jù)。

每條行記錄上面都有一個指針 DATA_ROLL_PTR，指向最近的 UNDO 記錄。同時每條 UNDO 記錄包含一個指向前一個 UNDO 記錄的指針，這樣就構(gòu)成了一條記錄的所有 UNDO 歷史的鏈表。當(dāng) UNDO 的記錄還存在，那么對應(yīng)的記錄的歷史版本就能被構(gòu)建出來。

當(dāng)記錄對應(yīng)的版本通過 DATA_TRX_ID 比對發(fā)現(xiàn)不可見時，通過系統(tǒng)列 DATA_ROLL_PTR，找到對應(yīng)的回滾段記錄，繼續(xù)通過上述判斷記錄可見的規(guī)則，進行判斷，如果記錄依舊不可見，繼續(xù)通過回滾段查找之前的版本，直到找到對應(yīng)可見的版本。

3.2 慢查問題復(fù)現(xiàn)

經(jīng)過和業(yè)務(wù)方溝通，得知該表每天都有定時任務(wù)，會刪除歷史數(shù)據(jù)。大致了解到整個過程后，我們搭建模擬環(huán)境進行測試。

測試分為三個 session，其中 Sess1 執(zhí)行長事務(wù)，沒有提交。Sess2 對表的歷史數(shù)據(jù)做清理，清理了 2000 萬的數(shù)據(jù)。此時在 Sess3 執(zhí)行查詢，快慢情況如上圖所示。select * from sbtest1 limit 1 跟預(yù)期表現(xiàn)一樣，為很慢。但是 select * from sbtest1 order by id desc limit 1 執(zhí)行很快，這是為什么呢？

上圖為主鍵的記錄格式，在每條主鍵記錄的前面有個刪除標志位，然后是主鍵 ID,事務(wù) ID，回滾段指針，最后是行記錄。

當(dāng)記錄被執(zhí)行刪除的時候，MySQL 只是將記錄標記為已刪除，同時更新 DATA_TRX_ID 為自己刪除會話的事務(wù) ID，并沒有將記錄真正刪除。當(dāng)被刪除的記錄不再被其他事務(wù)需要的時候，會被 purge 線程刪除。purge 線程負責(zé)清理這些真正被刪除的記錄以及不再需要的 UNDO 日志。

回到慢查本身，我們來看看慢查的執(zhí)行過程。

SQL 為 select * from sbtest1 limit1。

• 通過主鍵，掃描到 ID=1 的記錄，根據(jù) MVCC 比對，發(fā)現(xiàn)自己的事務(wù) ID 大于記錄的 DATA_TRX_ID，匹配可見規(guī)則 1，記錄可見；

• 由于 ID=1 已經(jīng)被標記為 DELETED，刪除記錄可見；

• 由于表數(shù)據(jù)還沒有全部掃描完成，未滿足 limit 1，繼續(xù)掃描下一條記錄；

• 掃描到 ID=2 的記錄，根據(jù) MVCC 比對，發(fā)現(xiàn)自己的事務(wù) ID 大于記錄的 DATA_TRX_ID，匹配可見規(guī)則 1，記錄可見；

• 由于 ID=2 已經(jīng)被標記為 DELETED，刪除記錄可見；

• 由于表數(shù)據(jù)還沒有全部掃描完成，未滿足 limit 1，繼續(xù)掃描下一條記錄；

• 重復(fù) 4-6 步驟，直到滿足找到一條記錄，或者全表掃描完成。

由于被刪除的記錄有 2000 萬，Innodb 需要掃描 2000 萬的記錄，才能找到符合條件的第一條記錄，然后返回到 MySQL 的 Server 層。

當(dāng) SQL 為 select * from sbtest1 order by id desc limit1。

由于刪除的是老數(shù)據(jù)，當(dāng)從 ID 最大的方向開始掃描時，通過 MVCC 判斷可見，然后判斷記錄是否被標記為刪除的時候，記錄沒有被刪除，因此就可以快速返回到 Server 層，SQL 執(zhí)行效率就會很高。

關(guān)于“大量刪除導(dǎo)致MySQL慢查的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。