談?wù)処nnoDB輔助索引的幾個特征。

閱讀目錄

0. 初始化測試表、數(shù)據(jù)

1. 問題1：索引列允許為NULL，對性能影響有多少

? ??結(jié)論1，存儲大量的NULL值，除了計算更復(fù)雜之外，數(shù)據(jù)掃描的代價也會更高一些

2. 問題2：輔助索引需要MVCC多版本讀的時候，為什么需要依賴聚集索引

? ??結(jié)論2，輔助索引中不存儲DB_TRX_ID，需要依托聚集索引實現(xiàn)MVCC

3. 問題3：為什么查找數(shù)據(jù)時，一定要讀取葉子節(jié)點，只讀非葉子節(jié)點不行嗎

? ??結(jié)論3，在索引樹中查找數(shù)據(jù)時，最終一定是要讀取葉子節(jié)點才行

4. 問題4：索引列允許為NULL，會額外存儲更多字節(jié)嗎

? 結(jié)論4，定義列值允許為NULL并不會增加物理存儲代價，但對索引效率的影響要另外考慮

5. 幾點總結(jié)

6. 延伸閱讀

本文開始之前，有幾篇文章建議先復(fù)習(xí)一下

InnoDB表聚集索引層高什么時候發(fā)生變化
淺析InnoDB索引結(jié)構(gòu)
Innodb頁合并和頁分裂
innblock | InnoDB page觀察利器

接下來，我們一起測試驗證關(guān)于輔助索引的幾個特點。

0. 初始化測試表、數(shù)據(jù)

測試表結(jié)構(gòu)如下：

[[email protected]]>?CREATE?TABLE?`t_sk`?(
??`id`?int(10)?unsigned?NOT?NULL?AUTO_INCREMENT,
??`c1`?int(10)?unsigned?NOT?NULL,
??`c2`?int(10)?unsigned?NOT?NULL,
??`c3`?int(10)?unsigned?NOT?NULL,
??`c4`?int(10)?unsigned?NOT?NULL,
??`c5`?datetime?NOT?NULL,
??`c6`?char(20)?NOT?NULL,
??`c7`?varchar(30)?NOT?NULL,
??`c8`?varchar(30)?NOT?NULL,
??`c9`?varchar(30)?NOT?NULL,
??PRIMARY?KEY?(`id`),
??KEY?`k1`?(`c1`)
)?ENGINE=InnoDB;

除了主鍵索引外，還有個?c1?列上的輔助索引。

用?mysql_random_data_load?灌入50萬測試數(shù)據(jù)。

[[email protected]]#?mysql_random_data_load?-hXX?-uXX?-pXX?test?t_sk?500000

1. 問題1：索引列允許為NULL，對性能影響有多少

把輔助索引列?c1?修改為允許NULL，并且隨機更新5萬條數(shù)據(jù)，將 c1 列設(shè)置為NULL

[[email protected]]>?alter?table?t_sk?modify?c1?int?unsigned;

[[email protected]]>?update?t_sk?set?c1?=?NULL?order?by?rand()?limit?50000;
Query?OK,?50000?rows?affected?(2.83?sec)
Rows?matched:?50000??Changed:?50000??Warnings:?0

#隨機1/10為null
[[email protected]]>?select?count(*)?from?t_sk?where?c1?is?null;
+----------+
|?count(*)?|
+----------+
|????50000?|
+----------+

好，現(xiàn)在觀察輔助索引的索引數(shù)據(jù)頁結(jié)構(gòu)。

[[email protected]]#?innblock?test/t_sk.ibd?scan?16
...
Datafile?Total?Size:100663296
===INDEX_ID:46???--聚集索引(主鍵索引)
level2?total?block?is?(1)??--根節(jié)點,層高2(共3層),共1個page
block_no:?????????3,level:???2|*|
level1?total?block?is?(5)??--中間節(jié)點,層高1,共5個page
block_no:???????261,level:???1|*|block_no:???????262,level:???1|*|block_no:???????263,level:???1|*|
block_no:???????264,level:???1|*|block_no:???????265,level:???1|*|
level0?total?block?is?(5020)??--葉子節(jié)點,層高0,共5020個page
block_no:?????????5,level:???0|*|block_no:?????????6,level:???0|*|block_no:?????????7,level:???0|*|
...
===INDEX_ID:47???--輔助索引
level1?total?block?is?(1)??--根節(jié)點,層高1(共2層),共1個page
block_no:?????????4,level:???1|*|
level0?total?block?is?(509)??--葉子節(jié)點,層高0,共509個page
block_no:????????18,level:???0|*|block_no:????????19,level:???0|*|block_no:????????31,level:???0|*|
...

觀察輔助索引的根節(jié)點里的數(shù)據(jù)

[[email protected]]#?innodb_space?-s?ibdata1?-T?test/t_sk?-p?4?page-dump
...
records:
{:format=>:compact,
?:offset=>126,????--第一條記錄
?:header=>
??{:next=>428,
???:type=>:node_pointer,
???:heap_number=>2,
???:n_owned=>0,
???:min_rec=>true,????--min_rec表示最小記錄
???:deleted=>false,
???:nulls=>["c1"],
???:lengths=>{},
???:externs=>[],
???:length=>6},
?:next=>428,
?:type=>:secondary,
?:key=>[{:name=>"c1",?:type=>"INT?UNSIGNED",?:value=>:NULL}],????--對應(yīng)c1列值為NULL
?:row=>[{:name=>"id",?:type=>"INT?UNSIGNED",?:value=>9}],????--對應(yīng)id=9
?:sys=>[],
?:child_page_number=>18,????--指向葉子節(jié)點?pageno?=?18
?:length=>8}
...
{:format=>:compact,
?:offset=>6246,????--最后一條記錄(next=>112,指向supremum)
?:header=>
??{:next=>112,
???:type=>:node_pointer,
???:heap_number=>346,
???:n_owned=>0,
???:min_rec=>false,
???:deleted=>false,
???:nulls=>[],
???:lengths=>{},
???:externs=>[],
???:length=>6},
?:next=>112,
?:type=>:secondary,
?:key=>[{:name=>"c1",?:type=>"INT?UNSIGNED",?:value=>2142714688}],????--對應(yīng)c1=2142714688
?:row=>[{:name=>"id",?:type=>"INT?UNSIGNED",?:value=>73652}],????--對應(yīng)id=73652
?:sys=>[],
?:child_page_number=>2935,????--指向葉子節(jié)點2935
?:length=>12}

經(jīng)過統(tǒng)計，根節(jié)點中c1列值為NULL的記錄共有33條，其余476條是c1列值為非NULL，共509條記錄。

葉子節(jié)點中，每個page大約可以存儲1547條記錄，共有5萬條記錄值為NULL，因此需要至少33個page來保存（ceiling(50000/1547) = 33)。

看下這個SQL的查詢計劃

[[email protected]]>?desc?select?count(*)?from?t_sk?where?c1?is?null\G
***************************?1.?row?***************************
???????????id:?1
??select_type:?SIMPLE
????????table:?t_sk
???partitions:?NULL
?????????type:?ref
possible_keys:?k1
??????????key:?k1
??????key_len:?5
??????????ref:?const
?????????rows:?99112
?????filtered:?100.00
????????Extra:?Using?where;?Using?index

從上面的輸出中，我們能看到，當(dāng)索引列設(shè)置允許為NULL時，是會對其納入索引統(tǒng)計信息，并且值為NULL的記錄，都是存儲在索引樹的最左邊。

接下來，跑幾個SQL查詢。

SQL1，統(tǒng)計所有NULL值數(shù)量

[[email protected]]>?select?count(*)?from?t_sk?where?c1?is?null;
+----------+
|?count(*)?|
+----------+
|????50000?|
+----------+

查看slow log

InnoDB_pages_distinct:?34
...
select?count(*)?from?t_sk?where?c1?is?null;

共需要掃描34個page，根節(jié)點(1)+葉子節(jié)點(33)，正好34個page。

備注：需要用Percona版本才能在slow query log中有InnoDB_pages_distinct信息。

SQL2, 查詢 c1 is null

[[email protected]]>?select?id,c1?from?t_sk?where?c1?is?null?limit?1;
+------+------+
|?id???|?c1???|
+------+------+
|?9607?|?NULL?|
+------+------+

查看slow log

InnoDB_pages_distinct:?12
...
select?id,c1?from?t_sk?where?c1?is?null?limit?1;

這次的查詢需要掃描12個page，除去1個根節(jié)點外，還需要掃描12個葉子節(jié)點，只是為了返回一條數(shù)據(jù)而已，這代價有點大。

如果把SQL微調(diào)改成下面這樣

[[email protected]]>?select?id,c1?from?t_sk?where?c1?is?null?limit?10000,1;
+-------+------+
|?id????|?c1???|
+-------+------+
|?99671?|?NULL?|
+-------+------+

可以看到還是需要掃描12個page。

InnoDB_pages_distinct:?12
...
select?id,c1?from?t_sk?where?c1?is?null?limit?10000,1;

SQL3, 查詢 c1 任意非NULL值
如果把 c1列條件改成正常的int值，結(jié)果就不太一樣了

[[email protected]]>?select?id,?c1?from?t_sk?where?c1??=?907299016;
+--------+-----------+
|?id?????|?c1????????|
+--------+-----------+
|?365115?|?907299016?|
+--------+-----------+
1?row?in?set?(0.00?sec)

slow log是這樣的

InnoDB_pages_distinct:?2
...
select?id,?c1?from?t_sk?where?c1??=?907299016;

可以看到，只需要掃描2個page，這個看起來就正常了。

結(jié)論1，存儲大量的NULL值，除了計算更復(fù)雜之外，數(shù)據(jù)掃描的代價也會更高一些

另外，如果要查詢的c1值正好介于兩個page的臨界位置，那么需要多讀取一個page。

掃描第31號page，確認該數(shù)據(jù)頁中的最小和最大物理記錄

[[email protected]]#?innodb_space?-s?ibdata1?-T?test/t_sk?-p?31?page-dump
...
records:
{:format=>:compact,
?:offset=>126,
?:header=>
??{:next=>9996,
???:type=>:conventional,
???:heap_number=>2,
???:n_owned=>0,
???:min_rec=>false,
???:deleted=>false,
???:nulls=>[],
???:lengths=>{},
???:externs=>[],
???:length=>6},
?:next=>9996,
?:type=>:secondary,
?:key=>[{:name=>"c1",?:type=>"INT?UNSIGNED",?:value=>1531865685}],
?:row=>[{:name=>"id",?:type=>"INT?UNSIGNED",?:value=>1507}],
?:sys=>[],
?:length=>8}
?...
{:format=>:compact,
?:offset=>5810,
?:header=>
??{:next=>112,
???:type=>:conventional,
???:heap_number=>408,
???:n_owned=>0,
???:min_rec=>false,
???:deleted=>false,
???:nulls=>[],
???:lengths=>{},
???:externs=>[],
???:length=>6},
?:next=>112,
?:type=>:secondary,
?:key=>[{:name=>"c1",?:type=>"INT?UNSIGNED",?:value=>1536700825}],
?:row=>[{:name=>"id",?:type=>"INT?UNSIGNED",?:value=>361382}],
?:sys=>[],
?:length=>8}?

指定c1的值為 1531865685、1536700825 執(zhí)行查詢，查看slow log，確認都需要掃描3個page，而如果換成介于這兩個值之間的數(shù)據(jù)，則只需要掃描2個page。

InnoDB_pages_distinct:?3
...
select?id,?c1?from?t_sk?where?c1??=?1531865685;

InnoDB_pages_distinct:?3
...
select?id,?c1?from?t_sk?where?c1??=?1536700825;

InnoDB_pages_distinct:?2
...
select?id,?c1?from?t_sk?where?c1??=?1536630003;

InnoDB_pages_distinct:?2
...
select?id,?c1?from?t_sk?where?c1??=?1536575377;

這是因為輔助索引是非唯一的，即便是在等值查詢時，也需要再讀取下一條記錄，以確認已獲取所有符合條件的數(shù)據(jù)。

還有，當(dāng)利用輔助索引讀取數(shù)據(jù)時，如果要讀取整行數(shù)據(jù)，則需要回表。

也就是說，除了掃描輔助索引數(shù)據(jù)頁之外，還需要掃描聚集索引數(shù)據(jù)頁。

來個例子看看就知道了。

#無需回表時
InnoDB_pages_distinct:?2
...
select?id,?c1?from?tnull?where?c1??=?1536630003;

#需要回表時
InnoDB_pages_distinct:?5
...
select?*?from?t_sk?where?c1??=?1536630003;

需要回表時，除了掃描輔助索引頁2個page外，還需要回表掃描聚集索引頁，而聚集索引是個3層樹，因此總共需要掃描5個page。

2. 問題2：輔助索引需要MVCC多版本讀的時候，為什么需要依賴聚集索引

InnoDB的MVCC是通過在聚集索引頁中同時存儲了DB_TRX_ID和DB_ROLL_PTR來實現(xiàn)的。

但是我們從上面page dump出來的結(jié)果也很明顯能看到，附注索引頁是不存儲DB_TRX_ID信息的。

所以說，輔助索引上如果想要實現(xiàn)MVCC，需要通過回表讀聚集索引來實現(xiàn)。

結(jié)論2，輔助索引中不存儲DB_TRX_ID，需要依托聚集索引實現(xiàn)MVCC

3. 問題3：為什么查找數(shù)據(jù)時，一定要讀取葉子節(jié)點，只讀非葉子節(jié)點不行嗎

在輔助索引的根節(jié)點這個頁面中(pageno=4)，我們注意到它記錄的最小記錄(min_rec)對應(yīng)的是(c1=NULL, id=9)這條記錄。

在它指向的葉子節(jié)點頁面中(pageno=18)也確認了這個情況。

現(xiàn)在把id=9的記錄刪掉，看看輔助索引數(shù)據(jù)頁會發(fā)生什么變化。

[[email protected]]>?delete?from?t_sk?where?id?=?9?and?c1?is?null;
Query?OK,?1?row?affected?(0.01?sec)

先檢查第4號數(shù)據(jù)頁。

[[email protected]]#?innodb_space?-s?ibdata1?-T?test/t_sk?-p?4?page-dump
...
records:
{:format=>:compact,
?:offset=>126,
?:header=>
??{:next=>428,
???:type=>:node_pointer,
???:heap_number=>2,
???:n_owned=>0,
???:min_rec=>true,
???:deleted=>false,
???:nulls=>["c1"],
???:lengths=>{},
???:externs=>[],
???:length=>6},
?:next=>428,
?:type=>:secondary,
?:key=>[{:name=>"c1",?:type=>"INT?UNSIGNED",?:value=>:NULL}],
?:row=>[{:name=>"id",?:type=>"INT?UNSIGNED",?:value=>9}],
?:sys=>[],
?:child_page_number=>18,
?:length=>8}
...

看到第四號數(shù)據(jù)頁中，最小記錄還是 id=9，沒有更新。

再查看第18號數(shù)據(jù)頁。

[[email protected]]#?innodb_space?-s?ibdata1?-T?test/t_sk?-p?18?page-dump
...
records:
{:format=>:compact,
?:offset=>136,
?:header=>
??{:next=>146,
???:type=>:conventional,
???:heap_number=>3,
???:n_owned=>0,
???:min_rec=>false,
???:deleted=>false,
???:nulls=>["c1"],
???:lengths=>{},
???:externs=>[],
???:length=>6},
?:next=>146,
?:type=>:secondary,
?:key=>[{:name=>"c1",?:type=>"INT?UNSIGNED",?:value=>:NULL}],
?:row=>[{:name=>"id",?:type=>"INT?UNSIGNED",?:value=>30}],
?:sys=>[],
?:length=>4}
...

在這個數(shù)據(jù)頁（葉子節(jié)點）中，最小記錄已經(jīng)被更新成 id=30 這條數(shù)據(jù)了。

可見，索引樹中的非葉子節(jié)點數(shù)據(jù)不是實時更新的，只有葉子節(jié)點的數(shù)據(jù)才是最準(zhǔn)確的。

結(jié)論3，在索引樹中查找數(shù)據(jù)時，最終一定是要讀取葉子節(jié)點才行

4. 問題4：索引列允許為NULL，會額外存儲更多字節(jié)嗎

之前流傳有一種說法，不允許設(shè)置列值允許NULL，是因為會額外多存儲一個字節(jié)，事實是這樣嗎？

我們先把c1列改成NOT NULL DEFAULT 0，當(dāng)然了，改之前要先把所有NULL值更新成0。

[[email protected]]>?update?t_sk?set?c1=0?where?c1?is?null;
[[email protected]]>?alter?table?t_sk?modify?c1?int?unsigned?not?null?default?0;

在修改之前，每條索引記錄長度都是10字節(jié)，更新之后卻變成了13個字節(jié)。
直接對比索引頁中的數(shù)據(jù)，發(fā)現(xiàn)不同之處

#允許為NULL，且默認值為NULL時
{:format=>:compact,
?:offset=>136,
?:header=>
??{:next=>146,
???:type=>:conventional,
???:heap_number=>3,
???:n_owned=>0,
???:min_rec=>false,
???:deleted=>false,
???:nulls=>["c1"],
???:lengths=>{},
???:externs=>[],
???:length=>6},
?:next=>146,
?:type=>:secondary,
?:key=>[{:name=>"c1",?:type=>"INT?UNSIGNED",?:value=>:NULL}],
?:row=>[{:name=>"id",?:type=>"INT?UNSIGNED",?:value=>48}],
?:sys=>[],
?:length=>4}


#不允許為NULL，默認值為0時
{:format=>:compact,
?:offset=>138,
?:header=>
??{:next=>151,
???:type=>:conventional,
???:heap_number=>3,
???:n_owned=>0,
???:min_rec=>false,
???:deleted=>false,
???:nulls=>[],
???:lengths=>{},
???:externs=>[],
???:length=>5},
?:next=>151,
?:type=>:secondary,
?:key=>[{:name=>"c1",?:type=>"INT?UNSIGNED",?:value=>0}],
?:row=>[{:name=>"id",?:type=>"INT?UNSIGNED",?:value=>48}],
?:sys=>[],
?:length=>8}

可以看到，原先允許為NULL時，record header需要多一個字節(jié)（共6字節(jié)），但實際物理存儲中無需存儲NULL值。

而當(dāng)設(shè)置為NOT NULL DEFAULT 0時，record header只需要5字節(jié)，但實際物理存儲卻多了4字節(jié)，總共多了3字節(jié)，所以索引記錄以前是10字節(jié)，更新后變成了13字節(jié)，實際上代價反倒變大了。

列值允許為NULL更多的是計算代價變大了，以及索引對索引效率的影響，反倒可以說是節(jié)省了物理存儲開銷。

結(jié)論4，定義列值允許為NULL并不會增加物理存儲代價，但對索引效率的影響要另外考慮

最后，本文使用的MySQL版本Percona-Server-5.7.22，下載源碼后自編譯的。

Server?version:????????5.7.22-22-log?Source?distribution

5. 幾點總結(jié)

最后針對InnoDB輔助索引，總結(jié)幾條建議吧。
a) 索引列最好不要設(shè)置允許NULL。
b) 如果是非索引列，設(shè)置允許為NULL基本上無所謂。
c) 輔助索引需要依托聚集索引實現(xiàn)MVCC。
d) 葉子節(jié)點總是存儲最新數(shù)據(jù)，而非葉子節(jié)點則不一定。
e) 盡可能不SELECT *，盡量利用覆蓋索引完成查詢，能不回表就不回表。