談談InnoDB輔助索引的幾個特征。

閱讀目錄

0. 初始化測試表、數據1. 問題1：索引列允許為NULL，對性能影響有多少? ??結論1，存儲大量的NULL值，除了計算更復雜之外，數據掃描的代價也會更高一些2. 問題2：輔助索引需要MVCC多版本讀的時候，為什么需要依賴聚集索引? ??結論2，輔助索引中不存儲DB_TRX_ID，需要依托聚集索引實現MVCC3. 問題3：為什么查找數據時，一定要讀取葉子節點，只讀非葉子節點不行嗎? ??結論3，在索引樹中查找數據時，最終一定是要讀取葉子節點才行4. 問題4：索引列允許為NULL，會額外存儲更多字節嗎? 結論4，定義列值允許為NULL并不會增加物理存儲代價，但對索引效率的影響要另外考慮5. 幾點總結6. 延伸閱讀

本文開始之前，有幾篇文章建議先復習一下

• InnoDB表聚集索引層高什么時候發生變化

• 淺析InnoDB索引結構

• Innodb頁合并和頁分裂

• innblock | InnoDB page觀察利器

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

0. 初始化測試表、數據

測試表結構如下：

[root@yejr.run]>?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萬測試數據。

[root@yejr.run]#?mysql_random_data_load?-hXX?-uXX?-pXX?test?t_sk?500000

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

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

[root@yejr.run]>?alter?table?t_sk?modify?c1?int?unsigned;

[root@yejr.run]>?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
[root@yejr.run]>?select?count(*)?from?t_sk?where?c1?is?null;
+----------+
|?count(*)?|
+----------+
|????50000?|
+----------+

好，現在觀察輔助索引的索引數據頁結構。

[root@yejr.run]#?innblock?test/t_sk.ibd?scan?16
...
Datafile?Total?Size:100663296
===INDEX_ID:46???--聚集索引(主鍵索引)
level2?total?block?is?(1)??--根節點,層高2(共3層),共1個page
block_no:?????????3,level:???2|*|
level1?total?block?is?(5)??--中間節點,層高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)??--葉子節點,層高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)??--根節點,層高1(共2層),共1個page
block_no:?????????4,level:???1|*|
level0?total?block?is?(509)??--葉子節點,層高0,共509個page
block_no:????????18,level:???0|*|block_no:????????19,level:???0|*|block_no:????????31,level:???0|*|
...

觀察輔助索引的根節點里的數據

[root@yejr.run]#?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}],????--對應c1列值為NULL
?:row=>[{:name=>"id",?:type=>"INT?UNSIGNED",?:value=>9}],????--對應id=9
?:sys=>[],
?:child_page_number=>18,????--指向葉子節點?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}],????--對應c1=2142714688
?:row=>[{:name=>"id",?:type=>"INT?UNSIGNED",?:value=>73652}],????--對應id=73652
?:sys=>[],
?:child_page_number=>2935,????--指向葉子節點2935
?:length=>12}

經過統計，根節點中c1列值為NULL的記錄共有33條，其余476條是c1列值為非NULL，共509條記錄。

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

看下這個SQL的查詢計劃

[root@yejr.run]>?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:?5ref:?const
?????????rows:?99112
?????filtered:?100.00
????????Extra:?Using?where;?Using?index

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

接下來，跑幾個SQL查詢。

SQL1，統計所有NULL值數量

[root@yejr.run]>?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，根節點(1)+葉子節點(33)，正好34個page。

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

SQL2, 查詢 c1 is null

[root@yejr.run]>?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個根節點外，還需要掃描12個葉子節點，只是為了返回一條數據而已，這代價有點大。

如果把SQL微調改成下面這樣

[root@yejr.run]>?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值，結果就不太一樣了

[root@yejr.run]>?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，這個看起來就正常了。

結論1，存儲大量的NULL值，除了計算更復雜之外，數據掃描的代價也會更高一些

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

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

[root@yejr.run]#?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 執行查詢，查看slow log，確認都需要掃描3個page，而如果換成介于這兩個值之間的數據，則只需要掃描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;

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

還有，當利用輔助索引讀取數據時，如果要讀取整行數據，則需要回表。

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

來個例子看看就知道了。

#無需回表時
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來實現的。

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

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

結論2，輔助索引中不存儲DB_TRX_ID，需要依托聚集索引實現MVCC

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

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

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

現在把id=9的記錄刪掉，看看輔助索引數據頁會發生什么變化。

[root@yejr.run]>?delete?from?t_sk?where?id?=?9?and?c1?is?null;
Query?OK,?1?row?affected?(0.01?sec)

先檢查第4號數據頁。

[root@yejr.run]#?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}
...

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

再查看第18號數據頁。

[root@yejr.run]#?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}
...

在這個數據頁(葉子節點)中，最小記錄已經被更新成 id=30 這條數據了。

可見，索引樹中的非葉子節點數據不是實時更新的，只有葉子節點的數據才是最準確的。

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

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

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

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

[root@yejr.run]>?update?t_sk?set?c1=0?where?c1?is?null;
[root@yejr.run]>?alter?table?t_sk?modify?c1?int?unsigned?not?null?default?0;

在修改之前，每條索引記錄長度都是10字節，更新之后卻變成了13個字節。
直接對比索引頁中的數據，發現不同之處

#允許為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需要多一個字節(共6字節)，但實際物理存儲中無需存儲NULL值。

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

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

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

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

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

5. 幾點總結

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

6. 延伸閱讀

• InnoDB表聚集索引層高什么時候發生變化

• 淺析InnoDB索引結構

• Innodb頁合并和頁分裂

• innblock | InnoDB page觀察利器

• jcole.us：The physical structure of InnoDB index pages

• jcole.us：B+Tree index structures in InnoDB

Enjoy MySQL :)

全文完。

---

由葉老師主講的「MySQL優化」課已升級到MySQL 8.0版本，掃碼開啟MySQL 8.0的修行之旅吧

總結

以上是生活随笔為你收集整理的distinct作用于后面所有的列吗_InnoDB索引允许NULL对性能有影响吗的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。