前言

上一篇介紹了 MySQL 的日志，這一篇將介紹內(nèi)存管理和磁盤管理相關(guān)的內(nèi)容。

內(nèi)存管理

MySQL 的數(shù)據(jù)都是存在磁盤中的，我們要更新一條記錄的時(shí)候，得先要從磁盤讀取該記錄，然后在內(nèi)存中修改這條記錄。修改完這條記錄后會(huì)緩存起來(lái)，下次有查詢語(yǔ)句命中了這條記錄，就可以直接讀取緩存中的記錄，不需要再?gòu)拇疟P獲取數(shù)據(jù)了。

Buffer Pool

MySQL 對(duì)數(shù)據(jù)的增刪改查都是在內(nèi)存中完成的，即在 Buffer Pool 中完成的。

緩存池是 Innodb 存儲(chǔ)引擎設(shè)計(jì)并實(shí)現(xiàn)的，是 InnoDB 中的一塊內(nèi)存區(qū)域，默認(rèn)大小是 128M。MySQL 啟動(dòng)后會(huì)初始化 Buffer Pool。

• 當(dāng)讀取數(shù)據(jù)時(shí)，如果數(shù)據(jù)存在于 Buffer Pool 中，客戶端就會(huì)直接讀取 Buffer Pool 中的數(shù)據(jù)，否則再去磁盤中讀取。
• 當(dāng)修改數(shù)據(jù)時(shí)，如果數(shù)據(jù)存在于 Buffer Pool 中，那直接修改 Buffer Pool 中數(shù)據(jù)所在的頁(yè)，然后將其頁(yè)設(shè)置為臟頁(yè)（該頁(yè)的內(nèi)存數(shù)據(jù)和磁盤上的數(shù)據(jù)已經(jīng)不一致），為了減少磁盤I/O，不會(huì)立即將臟頁(yè)寫入磁盤，后續(xù)由后臺(tái)線程選擇一個(gè)合適的時(shí)機(jī)將臟頁(yè)寫入到磁盤。

InnoDB 會(huì)把存儲(chǔ)的數(shù)據(jù)劃分為若干個(gè)「頁(yè)」，以頁(yè)作為磁盤和內(nèi)存交互的基本單位，一個(gè)頁(yè)的默認(rèn)大小為 16KB。因此，Buffer Pool 同樣需要按「頁(yè)」來(lái)劃分。

Buffer Pool 除了緩存「索引頁(yè)」和「數(shù)據(jù)頁(yè)」，還包括了 Undo 頁(yè)，插入緩存頁(yè)、自適應(yīng)哈希索引、鎖信息等等。

為了更好的管理這些在 Buffer Pool 中的緩存頁(yè)，InnoDB 為每一個(gè)緩存頁(yè)都創(chuàng)建了一個(gè)控制塊，控制塊信息包括「緩存頁(yè)的表空間、頁(yè)號(hào)、緩存頁(yè)地址、鏈表節(jié)點(diǎn)、鎖信息、LSN 信息」等等。

控制塊也是占有內(nèi)存空間的，它是放在 Buffer Pool 的最前面，接著才是緩存頁(yè)，控制塊和緩存頁(yè)之間灰色部分稱為碎片空間。

為了能夠快速找到空閑的緩存頁(yè)，可以使用鏈表結(jié)構(gòu)，將空閑緩存頁(yè)的「控制塊」作為鏈表的節(jié)點(diǎn)，這個(gè)鏈表稱為 Free 鏈表（空閑鏈表）。

為了能快速知道哪些緩存頁(yè)是臟的，于是就設(shè)計(jì)出 Flush 鏈表，它跟 Free 鏈表類似的，鏈表的節(jié)點(diǎn)也是控制塊，區(qū)別在于 Flush 鏈表的元素都是臟頁(yè)。

LRU

為了提高緩存命中率，MySQL 改進(jìn)了 LRU（Least Recently Used） 算法，將 LRU 劃分了 2 個(gè)區(qū)域：old 區(qū)域 和 young 區(qū)域。young 區(qū)域在 LRU 鏈表的前半部分，old 區(qū)域則是在后半部分。

old 區(qū)域占整個(gè) LRU 鏈表長(zhǎng)度的比例可以通過(guò) innodb_old_blocks_pc 參數(shù)來(lái)設(shè)置，默認(rèn)是 37，代表整個(gè) LRU 鏈表中 young 區(qū)域與 old 區(qū)域比例是 63:37。

改進(jìn)過(guò)后的 LRU 算法可以解決兩個(gè)問(wèn)題：預(yù)讀失效和 Buffer Pool 污染。

預(yù)讀失效

預(yù)讀機(jī)制：程序是有空間局部性的，靠近當(dāng)前被訪問(wèn)數(shù)據(jù)的數(shù)據(jù)，在未來(lái)很大概率會(huì)被訪問(wèn)到。所以，MySQL 在加載數(shù)據(jù)頁(yè)時(shí)，會(huì)提前把它相鄰的數(shù)據(jù)頁(yè)一并加載進(jìn)來(lái)，目的是為了減少磁盤 IO。

但是可能這些被提前加載進(jìn)來(lái)的數(shù)據(jù)頁(yè)，并沒有被訪問(wèn)，相當(dāng)于這個(gè)預(yù)讀是白做了，這個(gè)就是預(yù)讀失效。

劃分這兩個(gè)區(qū)域后，預(yù)讀的頁(yè)就只需要加入到 old 區(qū)域的頭部，當(dāng)頁(yè)被真正訪問(wèn)的時(shí)候，才將頁(yè)插入 young 區(qū)域的頭部。如果預(yù)讀的頁(yè)一直沒有被訪問(wèn)，就會(huì)從 old 區(qū)域移除，這樣就不會(huì)影響 young 區(qū)域中的熱點(diǎn)數(shù)據(jù)。

Buffer Pool 污染

當(dāng)某一個(gè) SQL 語(yǔ)句掃描了大量的數(shù)據(jù)時(shí)，在 Buffer Pool 空間比較有限的情況下，可能會(huì)將 Buffer Pool 里的所有頁(yè)都替換出去，導(dǎo)致大量熱數(shù)據(jù)被淘汰了，等這些熱數(shù)據(jù)又被再次訪問(wèn)的時(shí)候，由于緩存未命中，就會(huì)產(chǎn)生大量的磁盤 IO，MySQL 性能就會(huì)急劇下降，這個(gè)過(guò)程被稱為 Buffer Pool 污染。

Buffer Pool 污染并不只是查詢語(yǔ)句查詢出了大量的數(shù)據(jù)才出現(xiàn)的問(wèn)題，即使查詢出來(lái)的結(jié)果集很小，也會(huì)造成 Buffer Pool 污染。

比如，在一個(gè)數(shù)據(jù)量非常大的表，執(zhí)行了這條語(yǔ)句：

select * from user where name like "%a%";

可能這個(gè)查詢出來(lái)的結(jié)果就幾條記錄，但是由于這條語(yǔ)句會(huì)發(fā)生索引失效，所以這個(gè)查詢過(guò)程是全表掃描的，接著會(huì)發(fā)生如下的過(guò)程：

• 從磁盤讀到的頁(yè)加入到 LRU 鏈表的 old 區(qū)域頭部；
• 當(dāng)從頁(yè)里讀取行記錄時(shí)，也就是頁(yè)被訪問(wèn)的時(shí)候，就要將該頁(yè)放到 young 區(qū)域頭部；
• 接下來(lái)拿行記錄的 name 字段和字符串 xiaolin 進(jìn)行模糊匹配，如果符合條件，就加入到結(jié)果集里；
• 如此往復(fù)，直到掃描完表中的所有記錄。

為了解決這個(gè)問(wèn)題，MySQL 對(duì)進(jìn)入到 young 區(qū)域條件增加了一個(gè)停留在 old 區(qū)域的時(shí)間判斷。只有在 old 區(qū)域停留時(shí)間超過(guò)一定時(shí)間，才會(huì)被插入到 young 區(qū)域頭部。

另外，MySQL 針對(duì) young 區(qū)域其實(shí)做了一個(gè)優(yōu)化，為了防止 young 區(qū)域節(jié)點(diǎn)頻繁移動(dòng)到頭部。young 區(qū)域前面 1/4 被訪問(wèn)不會(huì)移動(dòng)到鏈表頭部，只有后面的 3/4被訪問(wèn)了才會(huì)。

臟頁(yè)刷盤

把臟數(shù)據(jù)刷回磁盤的技術(shù)又稱 checkpoint 技術(shù)。

MySQL 的臟頁(yè)落盤是由后臺(tái)線程定期異步執(zhí)行的。

1. 當(dāng) redo log 滿了的情況下，會(huì)主動(dòng)觸發(fā)臟頁(yè)刷新到磁盤；
2. Buffer Pool 空間不足時(shí)，需要將一部分?jǐn)?shù)據(jù)頁(yè)淘汰掉，如果淘汰的是臟頁(yè)，需要先將臟頁(yè)同步到磁盤；
3. MySQL 認(rèn)為空閑時(shí)，后臺(tái)線程回定期將適量的臟頁(yè)刷入到磁盤；即使非空閑時(shí)，也會(huì)見縫插針地刷盤；
4. MySQL 正常關(guān)閉之前，會(huì)把所有的臟頁(yè)刷入到磁盤；

在 MySQL 的使用過(guò)程中，可能會(huì)出現(xiàn)抖動(dòng)（突然變得很慢，且 CPU 資源被大量占用），很大可能就是在刷盤，即情況 12。

change buffer

寫緩存，change buffer 是 buffer pool 的一部分，當(dāng)需要修改的數(shù)據(jù)頁(yè)不在緩存池內(nèi)時(shí)，會(huì)在 change buffer 中記錄數(shù)據(jù)變更，等未來(lái)數(shù)據(jù)被讀取時(shí)，再將數(shù)據(jù) merge 到緩存池中。

在 MySQL5.5 之前，叫插入緩沖（insert buffer）只針對(duì) insert 做了優(yōu)化；現(xiàn)在對(duì) delete 和 update 也有效，叫做寫緩沖（change buffer）。

使用 change buffer 之前：

1. 當(dāng)需要更新的數(shù)據(jù)不在緩存池中時(shí)，從磁盤中讀取數(shù)據(jù)頁(yè)到 buffer pool；（一次磁盤隨機(jī)讀）
2. 更新數(shù)據(jù)頁(yè)；（一次寫內(nèi)存）
3. 將數(shù)據(jù)頁(yè)更新記錄到 redo log，redo log 落盤。（一次磁盤順序?qū)?#xff09;

使用 change buffer 之后：

1. 當(dāng)需要更新的數(shù)據(jù)不在緩存池中時(shí)，不需要從磁盤中讀取數(shù)據(jù)頁(yè)，而是在寫緩存中記錄這個(gè)變更操作；（一次寫內(nèi)存）
2. 將數(shù)據(jù)頁(yè)更新記錄到 redo log，redo log 落盤；（一次磁盤順序?qū)?#xff09;
3. 當(dāng)訪問(wèn)到該記錄時(shí)，先從磁盤中讀取數(shù)據(jù)頁(yè)，再?gòu)膶懢彺嬷凶x取變更信息（如果有多個(gè)，則依次更新），最后更新到緩存池中，即 merge 操作。（一次磁盤隨機(jī)讀和一次寫內(nèi)存）
4. 將緩存頁(yè)和 change buffer 的更新記錄到 redo log，redo log 落盤。（一次磁盤順序?qū)?#xff09;

使用 change buffer，在更新頻率高、查詢頻率低的場(chǎng)景下（且不是更新完馬上查詢），相當(dāng)于可以減少一次磁盤隨機(jī)讀開銷。（相當(dāng)于只有步驟 12，和少量的步驟 34）

但是如果所有更新后面，都馬上要對(duì)這個(gè)記錄進(jìn)行查詢，那么 change buffer 反而會(huì)起到副作用。

merge 時(shí)機(jī)：

• 數(shù)據(jù)頁(yè)被讀取
• 后臺(tái)線程認(rèn)為數(shù)據(jù)庫(kù)空閑時(shí)
• 數(shù)據(jù)庫(kù)正常關(guān)閉時(shí)

change buffer 只能用于非唯一普通索引頁(yè)（non-unique secondary index page）。

因?yàn)槿绻饕O(shè)置了唯一屬性，在進(jìn)行修改操作時(shí)，InnoDB 必須進(jìn)行唯一性檢查。

比如，要插入(4,400)記錄，要先判斷表中是否已存 k=4 記錄，而這必須要將數(shù)據(jù)頁(yè)讀入內(nèi)存才能判斷。如果都已經(jīng)讀入到內(nèi)存，那直接更新內(nèi)存會(huì)更快，就沒必要使用 change buffer。

磁盤空間管理

空間碎片

MySQL 中有以下幾種可能出現(xiàn)空間碎片的情況：

• 修改行數(shù)據(jù)導(dǎo)致出現(xiàn)行間碎片；
• 插入和修改數(shù)據(jù)可能導(dǎo)致頁(yè)分裂，從而使數(shù)據(jù)頁(yè)中有大量空余空間（數(shù)據(jù)頁(yè)空余空間很難避免）；

刪除數(shù)據(jù)

delete

使用 delete 刪除一條記錄，只是在 B+ 樹中將記錄標(biāo)記為刪除狀態(tài)（通過(guò)隱藏列中的 deleted_bit），刪除后的記錄不會(huì)消失，且可以被復(fù)用。

如果一個(gè)數(shù)據(jù)頁(yè)上的所有記錄都被刪除了，那么整個(gè)數(shù)據(jù)頁(yè)就可以被復(fù)用了。而且如果相鄰的兩個(gè)數(shù)據(jù)頁(yè)的利用率都很小，系統(tǒng)就會(huì)把這兩個(gè)頁(yè)上的數(shù)據(jù)合并到其中一個(gè)頁(yè)上，另外一個(gè)數(shù)據(jù)頁(yè)就會(huì)被標(biāo)記為可復(fù)用。

所以只是刪除數(shù)據(jù)，占用的磁盤空間并不會(huì)減少，甚至可能增加。因?yàn)?delete 操作還會(huì)寫入 redo log 和 undo log，從而占用更多的磁盤空間。

truncate

truncate 用于清空表內(nèi)的數(shù)據(jù)，但是不會(huì)刪除表本身。立刻釋放磁盤空間。

drop

drop 用于刪除整個(gè)表/庫(kù)，包括表的結(jié)構(gòu)、屬性、索引等。立刻釋放磁盤空間。

速度：drop>truncate>delete

空間回收

經(jīng)過(guò)大量增刪改的表，可能存在大量的空洞（數(shù)據(jù)頁(yè)中可復(fù)用或未被使用的記錄）。目前，能夠回收表空間的辦法僅有一個(gè)，就是重建表，手段包括但不限于 optimize，alter table 等。alter table 的有些操作只能靠 rebuild 表來(lái)完成。

誤刪數(shù)據(jù)

數(shù)據(jù)行

使用 delete 語(yǔ)句誤刪數(shù)據(jù)行時(shí)，可以用 Flashback 工具通過(guò)閃回把數(shù)據(jù)恢復(fù)過(guò)來(lái)，即修改 binlog 的內(nèi)容，拿回原庫(kù)重放。

能夠使用這個(gè)方案的前提是，需要確保 binlog_format=row 和 binlog_row_image=FULL。

恢復(fù)數(shù)據(jù)比較安全的做法是恢復(fù)出一個(gè)備份，或者找一個(gè)從庫(kù)作為臨時(shí)庫(kù)，然后在這個(gè)臨時(shí)庫(kù)上執(zhí)行這些操作，確認(rèn)過(guò)數(shù)據(jù)后再恢復(fù)回主庫(kù)，免得出現(xiàn)對(duì)數(shù)據(jù)的二次破壞。

預(yù)防

設(shè)置 sql_safe_updates=on，當(dāng) delete 和 update 語(yǔ)句中沒有寫 where 條件，或者 where 條件里面沒有包含索引字段的話，這條語(yǔ)句的執(zhí)行就會(huì)報(bào)錯(cuò)。

數(shù)據(jù)表/庫(kù)

使用 drop table 或者 truncate table 語(yǔ)句誤刪數(shù)據(jù)表時(shí)，或者使用 drop database 語(yǔ)句誤刪數(shù)據(jù)庫(kù)時(shí)，主要有兩種方式可以恢復(fù)。

方案一：使用全量備份加增量日志（實(shí)時(shí)備份 binlog）。

恢復(fù)的流程大概如下：

1. 取最近一次全量備份；
2. 用備份恢復(fù)出一個(gè)臨時(shí)庫(kù)；
3. 從日志備份里面，取出備份點(diǎn)之后的日志；
4. 把這些日志，除了誤刪除數(shù)據(jù)的語(yǔ)句外，全部應(yīng)用到臨時(shí)庫(kù)。

如果是誤刪表，不能指定恢復(fù)某個(gè)表，所以恢復(fù)的速度很慢，且由于數(shù)據(jù)量很大，存在回復(fù)時(shí)間不可控的問(wèn)題。

方案二：延遲復(fù)制備庫(kù)。專門搭建延遲復(fù)制的備庫(kù)，只要在延遲時(shí)間內(nèi)發(fā)現(xiàn)問(wèn)題，就能直接用備庫(kù)快速恢復(fù)數(shù)據(jù)。

MySQL實(shí)例

使用 rm 命令誤刪整個(gè) MySQL 實(shí)例時(shí)，對(duì)于高可用的集群而言，只要選出一個(gè)新的主庫(kù)保證整個(gè)集群的正常工作，然后再把節(jié)點(diǎn)數(shù)據(jù)恢復(fù)，再接入集群即可。

最后

本文介紹了 MySQL 內(nèi)存管理和磁盤管理。在內(nèi)存管理部分，有一篇文章寫的非常全面：(十二)MySQL之內(nèi)存篇：深入探尋數(shù)據(jù)庫(kù)內(nèi)存與Buffer Pool的奧妙！

下一節(jié)將介紹 MySQL 存儲(chǔ)過(guò)程和觸發(fā)器。