MYSQL PAGE頁(yè)及如何通過(guò)innochecksum來(lái)對(duì)ibd文件進(jìn)行查詢(xún)

這篇文章給大家介紹MYSQL PAGE頁(yè)及如何通過(guò)innochecksum來(lái)對(duì)ibd文件進(jìn)行查詢(xún)，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

問(wèn)：MYSQL的一個(gè)PAGE  頁(yè)多大

回答干脆利索，16K唄，我想這是大多數(shù)人的第一個(gè)反應(yīng)和回答，這個(gè)回答沒(méi)有毛病。但這16k里面到底有多少是你表中存儲(chǔ)的那些實(shí)實(shí)在在的數(shù)據(jù) ？？

這時(shí)95%的人肯能已經(jīng)......

我們都知道,MySql 存儲(chǔ)數(shù)據(jù)的物理單位，不是行，而是數(shù)據(jù)頁(yè)，默認(rèn)是一個(gè)16KB的數(shù)據(jù)單元。實(shí)際上 MYSQL的頁(yè)的大小是可以改變的，可以是8K可以是32K，UNIV_PAGE_SIZE 其實(shí)是定義一個(gè)MYSQL頁(yè)面大小的參數(shù)，同時(shí)UNIV_PAGE_SIZE_SHIFT也是與修改MYSQL 默認(rèn)頁(yè)大小有關(guān)的量，如果你的頁(yè)的大小為 16K 則UNIV_PAGE_SIZE_SHIFT的數(shù)字必須設(shè)置為 14 ，2乘以14次自己，就是16K。（16384）（順便問(wèn)一句 MYSQL一行能存儲(chǔ)的大小是多少？回味一下）

每個(gè)頁(yè)面中都會(huì)分配一個(gè)32位的整數(shù)頁(yè)碼，通過(guò)這個(gè)頁(yè)碼，頁(yè)面之間產(chǎn)生了關(guān)系，并且也限制了大小， 232 x 16 KiB = 64 TiB 這就算是一個(gè)表最大的存儲(chǔ)容量了。所以一個(gè)表的大小與單個(gè)頁(yè)面之間的關(guān)系如官方下面的圖，頁(yè)面是一個(gè)變量的話(huà)，其他都不變。

實(shí)際上MYSQL 的頁(yè)面存儲(chǔ)的格式也是有分門(mén)別類(lèi)的，在每個(gè)數(shù)據(jù)頁(yè)的的文件頁(yè)頭中38個(gè)字節(jié)不是白占用的，他主要負(fù)責(zé)以下的一些功能

1 監(jiān)測(cè)頁(yè)面的數(shù)據(jù)的正確性，F(xiàn)IL_PAGE_CHECKSUM, (還記得MYSQL 廣泛提供的頁(yè)面監(jiān)測(cè)的工具嗎)  4 字節(jié)

2 FIL_PAGE_OFFSET ，這個(gè)其實(shí)可以理解為用頁(yè)面組成的表的每頁(yè)的編碼，用來(lái)看看這個(gè)物理的文件到底有多少個(gè)頁(yè)面，當(dāng)前你訪(fǎng)問(wèn)的頁(yè)面在整體的位置

4個(gè)字節(jié)

3 FIL_PAGE_PREV  FILE_PAGE_NEXT ，我們都在說(shuō)MYSQL是B+ 樹(shù)的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)，其中這兩個(gè)值就表達(dá)了 B + 數(shù)據(jù)樹(shù)中的雙向鏈表的形式， 因?yàn)樗麄儍蓚€(gè)是指針，指定當(dāng)前頁(yè)面前一個(gè)頁(yè)面的地址，也指向了當(dāng)前頁(yè)面下一個(gè)頁(yè)面的地址。所以每個(gè)數(shù)據(jù)頁(yè)之間是一個(gè)“雙向鏈表”。這對(duì)數(shù)據(jù)的查詢(xún)時(shí)提取數(shù)據(jù)是非常重要的。

4 FIL_PAGE_LSN  被修改的日志序列位置LSN

5 FIL_PAGE_TYPE 當(dāng)前頁(yè)面的類(lèi)型  頁(yè)面是B+樹(shù)葉子節(jié)點(diǎn)， UNDO LOG ，索引節(jié)點(diǎn)，insert buffer空閑列表，insertbuffer位圖， 系統(tǒng)頁(yè)面，事務(wù)系統(tǒng)數(shù)據(jù)，BLOB頁(yè)面，文件空間頭，擴(kuò)展描述頁(yè) 等

6 LSN_FLUSH_LSN 系統(tǒng)表空間的一個(gè)頁(yè)中定義，與普通的數(shù)據(jù)頁(yè)面無(wú)關(guān)

7 FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID  頁(yè)面數(shù)據(jù)那個(gè)表空間

其中關(guān)于當(dāng)前ibd 文件的中存儲(chǔ)的數(shù)據(jù)的類(lèi)型可以通過(guò)mysql 自帶的工具 

innochecksum 來(lái)進(jìn)行查看，這里我們打開(kāi)MYSQL 中的一個(gè)ibd文件

在一個(gè)MYSQL Per = 1 情況下的頁(yè)面，我看可以看到這個(gè)文件中 有 1337個(gè)索引的頁(yè)面，我們的索引節(jié)點(diǎn) INODE PAGE 只有一個(gè)，還有452個(gè)新分配的頁(yè)面，一個(gè)insert buffer bitmap 頁(yè)面， 1個(gè)文件 file spave header

我們將這些數(shù)字加在一起，1792 * 16384/1024/1024 = 28MB

從實(shí)際當(dāng)中表的ibd文件，我們也可以驗(yàn)證。

所以文件的存儲(chǔ)空間與我們的從Ibd文件中導(dǎo)出的數(shù)據(jù)記錄頁(yè)面的信息的組合最終得出的數(shù)據(jù)存儲(chǔ)頁(yè)面大小是一致的。

現(xiàn)在馬上就有一個(gè)疑問(wèn)

問(wèn)：那這28MB 的數(shù)據(jù)空間里面有沒(méi)有還可以寫(xiě)入數(shù)據(jù)的頁(yè)面。

SELECT round((data_length+index_length)/1024/1024,2) FROM information_schema.tables WHERE   table_schema='employees'   AND table_name='dept_emp';

實(shí)際當(dāng)中通過(guò)數(shù)據(jù)庫(kù)的方式SQL的語(yǔ)句來(lái)獲得表占用的數(shù)據(jù)空間，與通過(guò)innochecksum 獲得空間之間是對(duì)不上的。

對(duì)比一下，目前通過(guò) innochecksum 獲得數(shù)據(jù)存儲(chǔ)空間占用是 20.89 MB

而通過(guò)語(yǔ)句來(lái)獲得是17.09MB

到底哪個(gè)更準(zhǔn)確

我們對(duì)目前的表進(jìn)行

OPTIMIZE TABLE dept_emp;  讓表中的空間進(jìn)行一次整理

然后我看一下數(shù)值到底有什么改變，通過(guò)SQL 來(lái)計(jì)算后結(jié)果是 24.55

而我們?cè)俅瓮ㄟ^(guò)innochecksum來(lái)對(duì)ibd文件進(jìn)行查詢(xún)，占用的數(shù)據(jù)空間在20.77左右。

同時(shí)也做了其他的一些表的空間使用，以及free空間的計(jì)算，可以證明通過(guò)SQL 來(lái)獲得當(dāng)前表的ibd的空間使用，與實(shí)際的表在LINUX下的使用情況是對(duì)不上的。

而通過(guò)innochecksum 來(lái)獲得數(shù)據(jù)表的使用情況，是比較穩(wěn)定，并且也是比較準(zhǔn)確的。另外OPTIMIZE后會(huì)導(dǎo)致通過(guò)SQL 來(lái)計(jì)算表的空間占用浮動(dòng)較大，而innochecksum 不會(huì)受到影響，并能準(zhǔn)確返回實(shí)際的磁盤(pán)空間使用的情況。

關(guān)于MYSQL PAGE頁(yè)及如何通過(guò)innochecksum來(lái)對(duì)ibd文件進(jìn)行查詢(xún)就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。