前言
土人有土辦法,利用innodb_ruby觀測驗證innodb page header及overflow臨界點。
這是一篇封存了兩年的文章,最近拿出來重新整理釋出。
1、背景資訊
1.1 InnoDB Diagrams項目
首先看一下Jeremy Cole、Davi Arnaut兩位大神聯合維護的牛逼項目:InnoDB Diagrams。
在這個項目中,詳細介紹了InnoDB引擎資料結構、日志結構,以及InnoDB内部的運作機制。
為了配合這個項目,二位大神還開發了一個InnoDB資料檔案解析工具 innodb_ruby
1.2 InnoDB Record Header
InnoDB Diagrams項目中有一張圖介紹了InnoDB record header是如何構成的
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iMwQTNiVGM2YDZjVTZ2MjYmFWM0QjZiR2NmVzMlVzNw8CX5d2bs92Yl1iclB3bsVmdlR2LcNWaw9CXt92Yu4GZjlGbh5yYjV3Lc9CX6MHc0RHaiojIsJye.png)
關于這些頭資訊的解釋可見文章 The physical structure of records in InnoDB,本文不贅述。
簡言之,記住幾條關鍵規則
- 一條記錄的record header,至少是5位元組
- 對record header影響最大的是變長列數量,及其是否允許為NULL的屬性
關于變長列
- 每個變長列,如果列長度 <128 位元組,則需要額外1位元組
- 每個變長列,如果列長度 >=128 位元組,則需要額外2位元組
- 如果沒有任何變長列,則無需這額外的1-2位元組
- 變長類型為char/varchar/text/blob等
同學們可能會詫異,char為什麼也當做變長類型了?這是因為,當字元集非latin1時,最大存儲長度可能會超過255位元組,例如 char(65) utf8mb4 最長就可以存儲260位元組,此時在record header中需要用2位元組來表示其長度,是以也被當做變長類型了
關于列允許為NULL
- 每個列如果允許為NULL,則增加 1bit,不足8bit也需要額外1位元組
- 例如隻有2個列允許為NULL,隻需要2bit來表示,但也需要占用1位元組
P.S,在InnoDB的存儲結構裡,從tablespace到segment,再到extent、page,還是file層面,總有各種必要的header或trailer資訊需要消耗額外的位元組數,不像MyISAM那麼簡單。
1.3 innodb_ruby項目
上面提過,innodb_ruby工具可以幫助我們進一步了解InnoDB引擎資料結構、日志結構。
該項目用ruby語言開發(大神真是任性,選了這個比較冷門的開發語言)。
特别提醒,該項目已經多年未更新,有些資料類型發生了變化(例如最經典的5.6之後時間日期類型),它解析的可能就不準确了,在我下面的實測案例中也證明了這點。是以,我還用到另外一個輔助工具 innblock。
1.4 innblock工具
由八怪開發,用于掃描和分析InnoDB page,詳見
innblock | InnoDB page觀察利器2、定義不同資料類型時的record header消耗
根據上面的理論,我們接下來計算并驗證當表裡有各種不同的資料類型時,分别需要多少額外位元組。
關于測試環境
- MySQL版本:Percona Server 5.7.22-22
- 測試配套工具:innodb_ruby & innblock
特别提醒,測試表的字元集為utf8mb4。
經過計算和驗證,最終可以得到以下幾條結論:
- 每條記錄的record header(下面簡稱RH)基礎是5位元組(簡寫成 RH=5)
- 每當表中多一個列允許為NULL,則額外增加1bit,且不足8bit時也需要消耗1位元組(同理,不足16bit時需要消耗2位元組)
- 每當表中多一個最大定義存儲長度不超過255位元組的變長列(char/varchar)時,額外增加1位元組
- 每當表中多一個最大定義存儲長度超過255位元組的變長列(char/varchar/text/blob)時,額外增加2位元組
由此我們可以推斷出以下幾種場景所需的record header大小(橫屏觀看,下同)
場景 | 表定義 | 行長度 (位元組) | record header | 備注說明 |
1 | id int not null 提醒:無顯式主鍵 | 28 | 5 | 5(RH) +6(TRX_ID) +7(ROLL_PTR) +6(ROW_ID) +4(INT) = 28 |
2 | id int 提醒:無顯式主鍵,且未指定not null | 29 | 6 | 6(允許null) + 6 +7 +6 +4 = 28 |
3 | primary key(id) 提醒:id列是顯式主鍵 | 22 | 5 + 6 + 7 = 22 | |
id int not null, c1 char(10), c1列隻存儲一個字元'a' | 34 | 7 | 7(char+null)+ 6 + 7 + 4 + 10(c1) = 34 | |
c1 varchar(10), | 25 | 7(varchar+null) + + 4 + 1(c1) = 25 |
更多的場景,詳見下面這個彙總表格