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

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

創(chuàng)新互聯(lián)為您提適合企業(yè)的網(wǎng)站設(shè)計(jì)?讓您的網(wǎng)站在搜索引擎具有高度排名，讓您的網(wǎng)站具備超強(qiáng)的網(wǎng)絡(luò)競(jìng)爭(zhēng)力！結(jié)合企業(yè)自身，進(jìn)行網(wǎng)站設(shè)計(jì)及把握，最后結(jié)合企業(yè)文化和具體宗旨等，才能創(chuàng)作出一份性化解決方案。從網(wǎng)站策劃到網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)， 我們的網(wǎng)頁(yè)設(shè)計(jì)師為您提供的解決方案。

一、背景

---

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

二、現(xiàn)象描述

---

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

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

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

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

三、問(wèn)題分析

---

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

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

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

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

從圖中，我們可以看到 sending data 耗費(fèi)的時(shí)間為 0.945 秒，基本占據(jù)了 SQL 執(zhí)行時(shí)間的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)時(shí)網(wǎng)絡(luò)確認(rèn)正常，那么時(shí)間就是耗費(fèi)在讀取和處理 select 的數(shù)據(jù)。

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

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

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

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

3.1 聊一下 MVCC

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

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

3.1.1 Innodb 行記錄

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

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

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

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

3.1.2 read view

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

read view 中有三個(gè)變量需要重點(diǎn)關(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，說(shuō)明該記錄在創(chuàng)建 read view 之前就已經(jīng)提交，記錄可見；

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

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

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

3.1.4 DATA_ROLL_PTR

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

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

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

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

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

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

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

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

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

SQL 為 select * from sbtest1 limit1。

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

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

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

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

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

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

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

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

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

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

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

當(dāng)前標(biāo)題：大量刪除導(dǎo)致MySQL慢查的示例分析
分享地址：http://muchs.cn/article26/gdedjg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、微信公眾號(hào)、動(dòng)態(tài)網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)、自適應(yīng)網(wǎng)站、網(wǎng)站設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)