前言
時學時總結,有不全和錯誤還請指正!
先來看一張MySQL
內部資料結構相關的簡圖:
從已上圖從上至下可以看到幾個概念:
- Tablespace:表空間
- Segment:段
- Extent:區
- Page:頁
- Row:行
簡短意賅的說明就是:我們寫的資料儲存在Row
,然後Row
又儲存在Page
,Page
儲存在Extent
,Extent
儲存在了Segment
,最終Segment
又儲存在了Tablespace
。
從大致上解讀如上,如果在細一點的分析可以看到以上圖還有更多的資訊,如:Tablespace
下有:Leaf node segment
-葉子段,Non-Leaf node segment
-非葉子段,Rollback segment
-回滾段。Row
下有:Trx id
-事務ID,Roll Pointer
-回滾指標,Col1..Coln
-資料欄位
除了以上的概念之外,還有很多的資訊從圖中無法得知。這就需要我們自己去學習和理解。而我就是嘗試去寫這些東西讓自己能夠理解這些東西。我打算從下至上的去講述,可能並不是特別深入,好在能夠有個大致的瞭解。
Row-資料行
我們透過insert
語句寫入的一條資料謂之為行
。
假設我們建立了一張資料表:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(12) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
並且寫入了一些資料:
insert into `test` set `id`=1,`name`='張三';
insert into `test` set `id`=2,`name`='李四';
這些資料理所當然的存在了MySQL
的“工作目錄”,而且也可以透過簡單的sql
語句查詢得到我們要的資料。但是MySQL
是如何儲存和查詢這些資料呢?這就需要先了解Row
的資料結構。
行格式分為幾種:在
5.0
之前用的是Redundant
,5.0
之後用的是Compact
。那麼首先先講講Compact
。
Compact格式
從上圖可以看到組成資料行
的基本構成。這些基本的構成怎麼就可以用來儲存我們的行記錄
呢?請繼續往下看。
Compact的記錄頭資訊
:記錄頭資訊
一共佔用了40bit
。這40bit
該如何充分利用呢?為了解釋這些位元組的作用,繼續以圖說明。
看如下的例子建立了一張資料表,並且插入一些資料行:
這些插入的資料是以二進位制儲存的,如果轉成16進位制部分資料如下圖,這部分的資料則是從第一行資料開始的。
根據圖中的解釋已經可以大致瞭解了一行記錄對應的Compact
資料結構。
MySQL是如何知道第一行資料的起始位置呢?
從上圖還可以得到一些資訊:
- 三個隱藏的列欄位:
RowID
(佔6B),TransactionID
(佔6B)即TrxID
,和Roll Pointer
(佔7B)。 - 變長欄位
03 02 01
逆序:從表結構知道t1 t2 t4
是變長欄位,分別對應第一行記錄的a bb ccc
,長度分別是01 02 03
。而在資料儲存的時候卻是以逆序儲存,為什麼呢?因為行記錄查詢過程是以記錄頭資訊
開始查詢行記錄
的。 - NULL標誌位(佔1B):從
記錄頭資訊
往前1B就是NULL標誌位。如第三行記錄出現了NULL值,NULL標誌位為06
,對應成二進位制則是0000 0110
。對應1
的位置表示是NULL
值,也即表示2、3列兩個欄位是NULL值。剩下的2個字元則不是NULL值,再往前取兩個字元則分別為第4、1列的長度。 - next_record:下一行
記錄頭資訊
的位置,類似指標指向下一條記錄。透過記錄頭資訊
的欄位佔用說明為16bit,即00 2b
,用16進製表示為0x2b
。注意到當前的位置是0x81
,和0x2b
相加為:0xac
。看向0xac
這一行的字元是:2b
。沒錯,你發現了它就是下一條記錄頭資訊
的next_record
。以此也就可以形成一個資料行
單向連結串列。(1)疑問:如果欄位定義了超過8個NULL值欄位,該如何儲存呢?
(2)表示變長段這裡是用1B
,用二進位制表示是:0000 0000
。表示成十進位制則取值範圍在:0~2^8-1(255)
。倘若varchar定義的變長大於255呢?則用2B
表示,最大長度在0~2^16-1(65535)
。65535
這也就是一個變長字串最大的長度了。
(3)NULL值除了標誌位佔了1B
,實際不存在其他佔用。
(4)設定為char
型別的欄位在不足長度時,會以0x20
佔位。這也說明了在字元達不到定義的長度下char
型別可能會存在空間浪費,而varchar
不會。
Redundant格式
格式如下截圖:
記錄頭資訊的截圖如下:
從中可以得到幾點資訊:
- n_fields(10bit):記錄中列的數量,佔用10bit。很好的解釋了為什麼行中最多有1023個列。
- 1byte_offs_flag:偏移列表為1B還是2B。什麼意思呢?
- 頭資訊佔用的是48bit。
同樣以示例來解讀:
將行記錄的二進位制轉成十六進位制檢視如下圖:
分析Redundant
和Compact
的異同:
(1)Compact
的存的是變長欄位長度列表,而Redundant
存的是欄位長度偏移列表。這也就意味著從header
開始查詢欄位值的策略也就發生了改變。但它們都是逆序儲存。
(2)處理NULL值的策略不同。Compact
專門用1B
記錄NULL值的列位置所在,Redundant
沒有。但它是如何記錄NULL值的呢?看到第三行資料的儲存。
逆序偏移量列表:21 9e 94 14 13 0c 06
。從header
開始往後數06B
是RowID
,從RowID
繼續往後數0x0c-0x06=0x06
即06B
則是TxId
。0x13-0x0c=0x07
即roll_pointer
,三個隱藏列則數完了。0x14-0x13=0x01
即1B的字元d
。從這突然跳到0x94
,0x9e-0x94=0x0a
即10B的NULL
值,接下來就是0x21-0x9e=0x03
的3B
fff。Compact
對NULL值是不佔用儲存的,而Redundant
的char
型別還是需要佔用儲存空間。所以Compact
較之Redundant
算是最佳化了。
字符集和char的關係
我們經常會用char(2)
的形式給一個欄位的定長。這個長度2
在MySQL4.1之前是表示2個位元組
,而之後則表示的是2個字元
。2個字元
的長度那到底是佔多少位元組呢?這個和MySQL的字符集相關。
- latin1 :英文字元佔用1B。
- GBK:英文字元佔用1B,中文字元佔用2B。
- utf8:英文字元佔用1B,中文字元佔用3B。
所以在某些情況下看似都是2個字元,但是佔用的位元組卻是不同的。這也就說明了為什麼在MySQL4.1之後char
型別也被視為varchar
型別,且佔用的位元組長度會被記錄在變長欄位長度列表
。值得注意的是在前面的例子中並沒有發現被視為varchar
型別,那是因為前面的例子選用的字符集是latin1
。
varchar的最大長度
(1)之前提到過varchar
的最大長度可以是65535
位元組,但是實際上並達不到這個長度。透過測試發現最大長度只可為65532
位元組(當然,這不是我做的實驗,感興趣可以自己嘗試)。
(2)在一個值得注意的是65535的長度指的是位元組長度,這個也和字符集相關。不同的字符集中英文字元佔用的位元組不同,但是總的位元組長度不可超過65535
即可。
(3)較之於Oracle VARCHAR2
最大存放4000
位元組,SQL Server
最大存放8000
位元組,MySQL最大可存放65532
位元組!然而需要注意的是這個65535
位元組並不是指每個欄位都可設定為65535
,而是一行記錄的總長度最大為65535
。比如下圖:
(4)資料頁
1頁大小是16KB
,即16384B
。這怎麼夠存入一個varchar
為36652
位元組的欄位呢?這裡就必須提到一個概念行溢位
。行溢位
顧名思義就是:一行放不下產生溢位了。
行溢位
首先看看Compact
和Redundant
對行溢位的表示:
在記錄中最大儲存768B
,剩下的就是用指標指向行溢位頁
。
對於儲存長欄位的話我們會用varchar
,text
,blob
等型別。使用了長欄位表示並不一定就會產生行溢位
。那麼什麼情況下會產生行溢位
呢?
也就是說一頁中至少可以存放兩條記錄,否則就會產生行溢位
。經過試驗發現一行的最大長度是varchar(8098)
。
Compressed和Dynamic行記錄格式
InnoDB1.*
版本開始引入了新的檔案格式。以前支援的Compact
和Redundant
被稱之為Antelope
-羚羊。新的檔案格式稱為Barracuda
-梭魚(下一個命名應該就是C開頭了吧~)。不同之處在於處理行溢位
的方式不同。如下圖:
而Compressed
和Dynamic
的不同之處在於Compressed
會對行溢位
進行zlib
壓縮。
參考
MySQL技術內幕(InnoDB儲存引擎)第二版
本作品採用《CC 協議》,轉載必須註明作者和本文連結