Mysql臟頁(yè)flush及收縮表空間原理解析

mysql臟頁(yè)

由于WAL機(jī)制，InnoDB在更新語(yǔ)句的時(shí)候，制作了寫(xiě)日志這一個(gè)磁盤(pán)操作，就是redo log，在內(nèi)存寫(xiě)完redo log后，就返回給客戶(hù)端， 即更新成功。

把內(nèi)存里的數(shù)據(jù)寫(xiě)入磁盤(pán)的過(guò)程，術(shù)語(yǔ)就是flush，在flush之前，實(shí)際數(shù)據(jù)和數(shù)據(jù)庫(kù)中的數(shù)據(jù)是不一致的，因?yàn)樵趓edo log基礎(chǔ)上更新了還未寫(xiě)入，數(shù)據(jù)庫(kù)是老的，當(dāng)內(nèi)存數(shù)據(jù)頁(yè)跟磁盤(pán)數(shù)據(jù)頁(yè)內(nèi)容不一致的時(shí)候，稱(chēng)這個(gè)內(nèi)存頁(yè)為臟頁(yè)，內(nèi)存寫(xiě)入后就一致了，稱(chēng)為干凈頁(yè)，

如果mysql偶爾運(yùn)行速度很慢，很可能是在刷臟頁(yè)。引發(fā)數(shù)據(jù)庫(kù)flush的過(guò)程

• redo log滿(mǎn)了，系統(tǒng)停止所有更新操作，將checkpoint向前推進(jìn)，騰出空間繼續(xù)寫(xiě)。
• 系統(tǒng)內(nèi)存不足，需要新的內(nèi)存頁(yè)不夠用，就會(huì)淘汰一些數(shù)據(jù)頁(yè)，留給別的數(shù)據(jù)頁(yè)使用，如果淘汰的是臟頁(yè)，就會(huì)先寫(xiě)到磁盤(pán)。
• mysql空閑的時(shí)候。
• 正常關(guān)閉mysql的時(shí)候
• 在第一種情況下，redo log滿(mǎn)了，這種情況是innodb要避免的，因?yàn)檎麄€(gè)系統(tǒng)都不能再更新了，這是不能接受的
• 第二種情況，內(nèi)存滿(mǎn)了，要先寫(xiě)到磁盤(pán)，innodb用緩沖池管理內(nèi)存，有三種狀態(tài)
• 還沒(méi)有用的內(nèi)存頁(yè)
• 用了并且是干凈頁(yè)
• 用了并且是臟頁(yè)（淘汰的時(shí)候需要寫(xiě)入到磁盤(pán)）

所以我們有時(shí)使用數(shù)據(jù)庫(kù)會(huì)發(fā)現(xiàn)數(shù)據(jù)庫(kù)性能突然下降，可能就是在處理臟頁(yè)。

刷臟頁(yè)控制策略

• Innodb_io_capacity參數(shù)，這個(gè)參數(shù)會(huì)告訴innodb你的磁盤(pán)io能力。（有公式計(jì)算）
• innodb刷盤(pán)主要兩個(gè)因素：臟頁(yè)比例和redo log的寫(xiě)盤(pán)速度
• innodb_max_derty_pages_pct是臟頁(yè)比例上限，默認(rèn)是75%，調(diào)整好Innodb_io_capacity參數(shù)值，使臟頁(yè)比例不要超過(guò)75%收縮表空間

場(chǎng)景例子：數(shù)據(jù)庫(kù)占用空間太大，把最大的表刪掉了一半數(shù)據(jù)，表的大小還是沒(méi)有變化。

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

加入要?jiǎng)h掉R4，InnoDB引擎只會(huì)把R4這個(gè)記錄標(biāo)記為刪除，如果之后再摻入一個(gè)ID在300-600之間的記錄時(shí)，會(huì)復(fù)用這個(gè)位置，但是磁盤(pán)文件的大小并不會(huì)縮小。

如果刪掉了一個(gè)數(shù)據(jù)頁(yè)上的所有記錄，這個(gè)數(shù)據(jù)頁(yè)就可以被復(fù)用。

注意：數(shù)據(jù)頁(yè)的復(fù)用跟記錄的復(fù)用是不同的。

• 比如R4這條記錄被刪除，如果插入一個(gè)ID是400的行，直接復(fù)用這個(gè)空間，但是如果插入ID是800的行，就不能復(fù)用這個(gè)位置了。
• 但是整個(gè)數(shù)據(jù)頁(yè)P(yáng)age A上的所有記錄刪除之后，pageA標(biāo)記為可復(fù)用，如果插入一條ID=50的記錄需要用新的數(shù)據(jù)頁(yè)的時(shí)候，PageA是可以背負(fù)用的。
• 如果我們用delete命令把整個(gè)表數(shù)據(jù)刪除，結(jié)果是所有的數(shù)據(jù)頁(yè)都會(huì)標(biāo)記為可復(fù)用，但是在磁盤(pán)上，文件不會(huì)變小。

插入數(shù)據(jù)流程

如果數(shù)據(jù)按照索引順序插入的，索引是緊湊的，但是如果是隨機(jī)插入的，就會(huì)造成索引的數(shù)據(jù)頁(yè)分頁(yè)。

如果pageA已經(jīng)滿(mǎn)了，在插入一行數(shù)據(jù)會(huì)怎樣？由于A滿(mǎn)了，在插入一個(gè)id是550的數(shù)據(jù)時(shí)，就會(huì)申請(qǐng)一個(gè)新的頁(yè)面pageB來(lái)保存數(shù)據(jù)，分裂完成后pageA的末尾就留下了空洞。

更新索引上的值也是刪除一個(gè)舊的值，再插入一個(gè)新值，也會(huì)造成空洞。

收縮空間

新建一個(gè)與表A相同結(jié)構(gòu)的表B，按照主鍵ID遞增的順序，把數(shù)據(jù)一行一行的從A里讀出來(lái)再插入到表B中，表B中無(wú)空洞，數(shù)據(jù)頁(yè)的利用率也更高，如果我們把表B作為臨時(shí)表，數(shù)據(jù)從表A導(dǎo)入到B中的操作完成后，用B替換A，從效果上也起到了收縮A的作用。

在整個(gè)DDL過(guò)程中，表A不能有更新，所以這個(gè)DDL不是online的，在5.6之后的版本中，流程做了更改：

建立一個(gè)臨時(shí)文件，掃描A中的所有數(shù)據(jù)頁(yè)，

用數(shù)據(jù)頁(yè)中A的記錄生成B+樹(shù)，存儲(chǔ)在臨時(shí)文件中

將所有對(duì)A的操作記錄在一個(gè)日志文件中

臨時(shí)文件生成后，將日志文件的操作應(yīng)用到臨時(shí)文件，得到一個(gè)邏輯數(shù)據(jù)上與表A相同的數(shù)據(jù)文件

用臨時(shí)文件替換表A的數(shù)據(jù)文件

圖示

可以看到，與圖3過(guò)程的不同之處在于，由于日志文件記錄和重放操作這個(gè)功能的存在，這個(gè)方案在重建表的過(guò)程中，允許對(duì)表A做增刪改操作。

使用alter table A engine=InnoDB命令來(lái)重建表。在MySQL 5.5版本之前，這個(gè)命令的執(zhí)行流程跟我們前面描述的差不多，區(qū)別只是這個(gè)臨時(shí)表B不需要你自己創(chuàng)建，MySQL會(huì)自動(dòng)完成轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表的操作。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。