驗證一個小小的問題

語言: CN / TW / HK

在之前的文章提到過一個問題,而且網上很多文章也是這麼說的,前幾天有人對這個問題提出了一點不同的意見,抱著謹慎的態度做了一個測試。

問題是這樣的:COMPACT格式下,NULL值列表是否一定會佔用一個位元組的空間?

對於這個問題,我的回答和網上很多回答是一樣的,如果都是NOT NULL就不會有NULL值列表,所以不會佔用,反之則會佔用。

今天,就對這個問題做一個驗證。

儲存空間

先回顧一下之前的知識。

資料庫中的一行記錄在最終磁碟檔案中也是以行的方式來儲存的,對於InnoDB來說,有4種行儲存格式: REDUNDANTCOMPACTDYNAMICCOMPRESSED

InnoDB的預設行儲存格式是 COMPACT ,儲存格式如下所示,虛線部分代表可能不一定會存在。

變長欄位長度列表:有多個欄位則以逆序儲存,我們只有一個欄位所有不考慮那麼多,儲存格式是16進位制,如果沒有變長欄位就不需要這一部分了。

NULL值列表:用來儲存我們記錄中值為NULL的情況,如果存在多個NULL值那麼也是逆序儲存,並且必須是8bit的整數倍,如果不夠8bit,則高位補0。1代表是NULL,0代表不是NULL。如果都是NOT NULL那麼這個就存在了,每多8個NULL會多佔用一個位元組的空間。

ROW_ID:一行記錄的唯一標誌,沒有指定主鍵的時候自動生成的ROW_ID作為主鍵。

TRX_ID:事務ID。

ROLL_PRT:回滾指標。

最後就是每列的值。

為了說明清楚這個儲存格式的問題,我弄張表來測試,這張表只有 c1 欄位是NOT NULL,其他都是可以為NULL的。

可變欄位長度列表: c1c3 欄位值長度分別為1和2,所以長度轉換為16進位制是 0x01 0x02 ,逆序之後就是 0x02 0x01

NULL值列表:因為存在允許為NULL的列,所以 c2,c3,c4 分別為010,逆序之後還是一樣,同時高位補0滿8位,結果是 00000010

其他欄位我們暫時不管他,最後第一條記錄的結果就是,當然這裡我們就不考慮編碼之後的結果了。

這樣就是一個完整的資料行資料的格式,反之,如果我們把所有欄位都設定為NOT NULL,並且插入一條資料 a,bb,ccc,dddd 的話,儲存格式應該這樣:

測試

這裡存在一點點小問題,首先我看到了阿里的資料庫月報中的測試和描述。

從這段程式碼看出之前的猜想,也就是並不是Null標誌位只固定佔用1個位元組==,而是以8為單位,滿8個null欄位就多1個位元組,不滿8個也佔用1個位元組,高位用0補齊

他的意思是無論如何都會佔用一個位元組,但是看了他的測試,發現他的表是允許NULL的,所以他的這個測試無法說明我們要驗證的問題。

按照網上大佬給出的方案,建立表,然後插入測試資料,資料庫中存在NULL值。

CREATE TABLE test ( c1 VARCHAR ( 32 ),
   c2 VARCHAR ( 32 ),
   c3 VARCHAR ( 32 ),
   c4 VARCHAR ( 32 ) ) ENGINE = INNODB row_format = compact;

使用命令 SHOW VARIABLES LIKE 'datadir' 找到 ibd 檔案位置。

使用命令轉換 ibd 檔案為 txt 檔案。

hexdump -C -v test.ibd > /Users/irving/test-null.txt

開啟檔案找到 supremum 部分。

不用看那麼多,就看一部分:

03 02 02 01 是上面說的變長欄位長度列表,以為我們有4個欄位,所以4個位元組。
00 就是NULL標誌位
00 00 10 00 25 是資料頭5個位元組

這個肯定沒有問題,然後再次建立一張表,這時候欄位都是NOT NULL,然後再次執行命令。

CREATE TABLE test ( c1 VARCHAR ( 32 ) NOT NULL,
   c2 VARCHAR ( 32 ) NOT NULL,
   c3 VARCHAR ( 32 ) NOT NULL,
   c4 VARCHAR ( 32 ) NOT NULL ) ENGINE = INNODB row_format = compact;

拿到另外一個 ibd 檔案。

對比其實很清楚能發現問題,這時候已經沒有了NULL值列表的標誌位了。

SO,這個測試結果證明,如果存在任意NULL值,NULL值列表至少佔用一個位元組的空間,以後每多8個NULL值多佔用一個位元組,如果都是NOT NULL,則不會存在NULL值列表標記,不佔用空間。

巨人的肩膀: