MySQL · 專家投稿 · InnoDB物理行中null值的存儲的推斷與驗證 · 數據庫內核月報

## 前言想寫這邊文章，是因為之前想寫一個解析innodb ibd文件的工具，在寫這個工具的過程中，發現邏輯記錄轉物理記錄的轉換中，最難的有兩部分，一是每行每字段null值占用的字節和存儲，二是變長字段占用的字節和存儲的格式。本文中重點針對第一種情況。之前看有關介紹compact行記錄格式: > 變長字段之后的第二個部分是NULL標志位，該位指示了該行數據中是否有NULL值，有則用1表示。該部分所占字節為1字節 > —–《InnoDB存儲引擎》之后便思考是否不管有多少個列都是NULL，該部分都只占1個字節呢？便有了如下測試 ## 本文約定邏輯記錄:record (元組) 物理記錄:row(行) 只討論compact行格式 ## 所用工具自己python寫的工具innodb_extract ## 測試數據 ### 表結構 ~~~ localhost.test>desc null_test; +------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | legalname | varchar(25) | YES | | NULL | | | industry | varchar(10) | YES | | NULL | | | province | varchar(10) | YES | | NULL | | | city | varchar(15) | YES | | NULL | | | size | varchar(15) | YES | | NULL | | | admin_department | varchar(128) | YES | | NULL | | +------------------+--------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) ~~~ ### 表內數據 ~~~ +----+------+-----------+----------+----------+------+------+------------------+ | id | name | legalname | industry | province | city | size | admin_department | +----+------+-----------+----------+----------+------+------+------------------+ | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 2 | TOM | NULL | NULL | NULL | NULL | NULL | NULL | | 3 | ALEX | NULL | NULL | NULL | NULL | NULL | HR | +----+------+-----------+----------+----------+------+------+------------------+ 3 rows in set (0.00 sec) ~~~ ## 分析數據通過工具看三行數據 ~~~ # python innodb_extract.py null_test.ibd infimum 7f 000010001c 8000000000000001 0000f1e27b17 b5000001680084 1 7e 0000180020 8000000000000002 0000f1e27b17 b5000001680094 544f4d 2 TOM 3e 000020ffb6 8000000000000003 0000f1e27b17 b50000016800a4 414c4558 4852 3 ALEX HR ~~~ 第一行: null標志位:0x7f (01111111) 說明:從右向左方向寫，一共7個null值 record header:000010001c Transaction Id:0000f1e27b17 Roll Pointer:b5000001680084 數據: 第二行: null標志位:0x7e (01111110) 說明：除第二列，其余均是null值 record header:0000180020 Transaction Id:0000f1e27b17 Roll Pointer:b5000001680084 數據: 第二列:544f4d => TOM 第三行: null標志位:0x3e (00111110) 說明:除了第2列和第8列，其余均是null值 record header:000020ffb6 Transaction Id:0000f1e27b17 Roll Pointer:b5000001680084 數據: 第二列:414c4558 => ALEX 第八列:4852 => HR ## 假設繼續上面，如果包含Null值的字段是8個，或者9個會是怎樣？ ## 深度剖析代碼片段，該函數將物理記錄轉化為邏輯記錄，版本5.5.31,源文件rem0rec.c， ~~~ rec_convert_dtuple_to_rec_comp( /*===========================*/ rec_t* rec, /*!< in: origin of record */ const dict_index_t* index, /*!< in: record descriptor */ const dfield_t* fields, /*!< in: array of data fields */ ulint n_fields,/*!< in: number of data fields */ ulint status, /*!< in: status bits of the record */ ibool temp) /*!< in: whether to use the format for temporary files in index creation */ { const dfield_t* field; const dtype_t* type; byte* end; byte* nulls; byte* lens; ulint len; ulint i; ulint n_node_ptr_field; ulint fixed_len; ulint null_mask = 1; ut_ad(temp || dict_table_is_comp(index->table)); ut_ad(n_fields > 0); if (temp) { ut_ad(status == REC_STATUS_ORDINARY); ut_ad(n_fields <= dict_index_get_n_fields(index)); n_node_ptr_field = ULINT_UNDEFINED; nulls = rec - 1; if (dict_table_is_comp(index->table)) { /* No need to do adjust fixed_len=0\. We only need to adjust it for ROW_FORMAT=REDUNDANT. */ temp = FALSE; } } else { nulls = rec - (REC_N_NEW_EXTRA_BYTES + 1); switch (UNIV_EXPECT(status, REC_STATUS_ORDINARY)) { case REC_STATUS_ORDINARY: ut_ad(n_fields <= dict_index_get_n_fields(index)); n_node_ptr_field = ULINT_UNDEFINED; break; case REC_STATUS_NODE_PTR: ut_ad(n_fields == dict_index_get_n_unique_in_tree(index) + 1); n_node_ptr_field = n_fields - 1; break; case REC_STATUS_INFIMUM: case REC_STATUS_SUPREMUM: ut_ad(n_fields == 1); n_node_ptr_field = ULINT_UNDEFINED; break; default: ut_error; return; } } end = rec; lens = nulls - UT_BITS_IN_BYTES(index->n_nullable); /* clear the SQL-null flags */ memset(lens + 1, 0, nulls - lens); ~~~ 結合COMPACT row格式來看: ~~~ row記錄格式如下: |---------------------extra_size-----------------------------------------|---------fields_data------------| |--columns_lens---|---null lens----|------fixed_extrasize(5)-------------|--col1---|---col2---|---col2----| |end<--------begin|end<-------beign|-------------------------------------|orgin---------------------------| ~~~ * 先看nulls = rec - (REC_N_NEW_EXTRA_BYTES + 1) rec為記錄開始的offset，也就是,extrasize也就是固定長度的record header的長度。注意null標志位和變長字段長度列表是從右->左的方向寫的(原因可參見下部分代碼)。所以nulls指向的是`null lens`后一字節開始的位置。 * 再看lens = nulls - UT_BITS_IN_BYTES(index->n_nullable) index->n_nullable指的是表結構中定義can be null的字段的個數，一個字段用一個bit來標記，UT_BITS_IN_BYTES將占用bit數轉為占用的字節數。所以lens指向的是column_lens后面一個字節的位置，即跳過了Null標志的占用的空間，同樣在寫入值的時候也是從后面向前面寫。 * memset(lens + 1, 0, nulls - lens) 將nulls空間清零。之后就是遍歷每一個字段，先對定義了can be null字段進行處理 ~~~ /* Store the data and the offsets */ for (i = 0, field = fields; i < n_fields; i++, field++) { const dict_field_t* ifield; type = dfield_get_type(field); len = dfield_get_len(field); if (UNIV_UNLIKELY(i == n_node_ptr_field)) { ut_ad(dtype_get_prtype(type) & DATA_NOT_NULL); ut_ad(len == REC_NODE_PTR_SIZE); memcpy(end, dfield_get_data(field), len); end += REC_NODE_PTR_SIZE; break; } if (!(dtype_get_prtype(type) & DATA_NOT_NULL)) { /* nullable field */ ut_ad(index->n_nullable > 0); if (UNIV_UNLIKELY(!(byte) null_mask)) { nulls--; null_mask = 1; } ~~~ 因為方向是從右向左寫，也就是從后往前寫，如果該字段為null，則將null標志位設為1并向前移1位，如果滿了8個，也就是有8個字段都為null則offset向左移1位，并將null_mask置為1 從這段代碼看出之前的猜想，也就是并不是Null標志位只固定占用1個字節==，而是以8為單位，滿8個null字段就多1個字節，不滿8個也占用1個字節，高位用0補齊 ~~~ ut_ad(*nulls < null_mask); /* set the null flag if necessary */ if (dfield_is_null(field)) { *nulls |= null_mask; null_mask <<= 1; continue; } null_mask <<= 1; } ~~~ 這段代碼是就是設置null字段與null標志位的映射關系，如果字段為null，則設置標志位為1。 ## 栗子驗證翻過來再看之前的例子，我們逐步的添加字段并設置default null看下null標志位的變化 * step 1，添加兩個并設置default null ~~~ localhost.test>alter table null_test add column `kind` varchar(15) DEFAULT NULL after `size`; Query OK, 3 rows affected (0.09 sec) Records: 3 Duplicates: 0 Warnings: 0 localhost.test>alter table null_test add column licenseno varchar(15) DEFAULT NULL after `kind`; Query OK, 3 rows affected (0.11 sec) Records: 3 Duplicates: 0 Warnings: 0.11 ~~~ 那么理論來講，第一行數據有9個null列了。滿8個null列之后，繼續向左寫移，寫1個bit之后開始占據兩個字節。我們通過工具解析之后看下 ~~~ # python innodb_extract.py null_test.ibd 01ff 000010001d 8000000000000001 0000f1e27c81 980000028c0084 1 01fe 0000180021 8000000000000002 0000f1e27c81 980000028c0094 544f4d 2 TOM 00fe 000020ffb3 8000000000000003 0000f1e27c81 980000028c00a4 414c455848 3 ALEX HR ~~~ 第一行null標志位變為0x01ff,即`00000001 11111111`一共有9個null字段，滿了8位之后，繼續向前占1個字節從右往左繼續寫同理，第二行0x01fe,即`00000001 11111110` 第三行0x00fe,`00000000 11111110` 再繼續添加8個字段并設置default null ~~~ localhost.test>desc null_test; +------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | legalname | varchar(25) | YES | | NULL | | | industry | varchar(10) | YES | | NULL | | | province | varchar(10) | YES | | NULL | | | city | varchar(15) | YES | | NULL | | | size | varchar(15) | YES | | NULL | | | kind | varchar(15) | YES | | NULL | | | licenseno | varchar(15) | YES | | NULL | | | admin_department | varchar(128) | YES | | NULL | | | null_col1 | varchar(15) | YES | | NULL | | | null_col2 | varchar(15) | YES | | NULL | | | null_col3 | varchar(15) | YES | | NULL | | | null_col4 | varchar(15) | YES | | NULL | | | null_col5 | varchar(15) | YES | | NULL | | | null_col6 | varchar(15) | YES | | NULL | | | null_col7 | varchar(15) | YES | | NULL | | | null_col8 | varchar(15) | YES | | NULL | | +------------------+--------------+------+-----+---------+----------------+ 18 rows in set (0.00 sec) ~~~ 最多Null字段的第一行目前有個17個null字段，對應17個Null bit ~~~ root@hebe211 ibd]# python innodb_extract.py null_test.ibd 01ffff 000010001e 8000000000000001 0000f1e27cce c60000017600840301fffe0000 1 01fffe 0000180022 8000000000000002 0000f1e27cce c6000001760094 544f4d 2 TOM 01fefe 000020ffb0 8000000000000003 0000f1e27cce c60000017600a4 414c45 5848 3 ALEX HR ~~~ 第一行null標志位變為0x01ff,即`00000001 11111111 11111111`?一共有17個null字段，滿了兩個8位之后，繼續向前占1個字節從右往左繼續寫同理，第二行0x01fe,即`00000001 11111111 11111110` 第三行0x00fe,`00000001 11111110 11111110` ## 結論允許null的字段需要額外的空間來保存字段Null到null標志位映射的對應關系，所以保存這個映射關系的null標志位長度并不是固定的。也就是null字段越多并不是越省空間。實際生產環境中應盡量減少can be null的字段。