本文作者：葉金榮涕癣，知數(shù)堂聯(lián)合創(chuàng)始人仿贬，MySQL DBA課程講師。Oracle MySQL ACE品抽，MySQL布道師。有多年MySQL及系統(tǒng)架構(gòu)設(shè)計(jì)經(jīng)驗(yàn)甜熔，擅長MySQL企業(yè)級(jí)應(yīng)用圆恤、數(shù)據(jù)庫設(shè)計(jì)、優(yōu)化腔稀、故障處理等盆昙。
multi-valued indexes有什么特點(diǎn)。
什么是multi-valued index
MySQL 8.0.17起焊虏，InnoDB引擎新增了對JSON數(shù)據(jù)類型的多值索引淡喜，即multi-valued index。它的作用是針對JSON數(shù)據(jù)類型中诵闭，同一條記錄有多個(gè)值的情況炼团，加上索引后，根據(jù)這些值條件查詢時(shí)疏尿，也可以指向同一條數(shù)據(jù)瘟芝。
假設(shè)有一條數(shù)據(jù)是 {"user":"Bob","zipcode":[94477,94536]}，意為Bob這位用戶褥琐，他擁有多個(gè)郵編"94477"和"94536"锌俱，這時(shí)候如果我們想對zipcode屬性加索引，就可以選擇使用多值索引了敌呈，在以往是不支持這個(gè)方式的贸宏。可以像下面這樣創(chuàng)建索引：（建議在PC端或橫版觀看磕洪，下同）
[root@yejr.me]> CREATE INDEX zips ON t1((
CAST(data->'$.zipcode' AS UNSIGNED ARRAY)));
在本例中的多值索引實(shí)際上是采用基于CAST()的函數(shù)索引吭练，CAST()轉(zhuǎn)換后選擇的數(shù)據(jù)類型除了BINARY和JSON，其他都可以支持褐鸥。目前multi-valued index只針對InnoDB表中的JSON數(shù)據(jù)類型线脚，其余場景還不支持。
multi-valued index怎么用
我們來看下一個(gè)JSON列怎么創(chuàng)建multi-valued index叫榕。
# 創(chuàng)建測試表
[root@yejr.me]> CREATE TABLE customers (
id INT NOT NULL AUTO_INCREMENT,
custinfo JSON,
primary key(id)
)engine=innodb;
# 寫入5條測試數(shù)據(jù)
[root@yejr.me]> INSERT INTO customers(custinfo) VALUES
('{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
('{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
('{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
('{"user":"Mary","user_id":72,"zipcode":[94536]}'),
('{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
# 執(zhí)行查詢浑侥，此時(shí)還沒創(chuàng)建索引，需要全表掃描
[root@yejr.me]> DESC SELECT * FROM customers WHERE
JSON_CONTAINS(custinfo->'$.zipcode',
CAST('[94507,94582]' AS JSON))\G
****************** 1. row ******************
...
type: ALL
possible_keys: NULL
key: NULL
...
rows: 5
filtered: 100.00
Extra: Using where
# 創(chuàng)建multi-valued index
[root@yejr.me]> ALTER TABLE customers ADD INDEX
zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
# 查看新的執(zhí)行計(jì)劃晰绎，可以走索引
[root@yejr.me]> DESC SELECT * FROM customers WHERE
JSON_CONTAINS(custinfo->'$.zipcode',
CAST('[94507,94582]' AS JSON))\G
****************** 1. row ******************
...
type: range
possible_keys: zips
key: zips
key_len: 9
ref: NULL
rows: 6
filtered: 100.00
Extra: Using where; Using MRR
multi-valued index底層是怎么存儲(chǔ)的
知道m(xù)ulti-valued index怎么用之后寓落，再來看下它底層是怎么存儲(chǔ)索引數(shù)據(jù)的。以上面的customers表為例荞下，我們利用innblock和bcview工具來確認(rèn)InnoDB底層是怎么存儲(chǔ)的伶选。
1. 先找到輔助索引page
先用innblock工具確認(rèn)輔助索引zips在哪個(gè)page上史飞。
[root@yejr.me]# innblock customers.ibd scan 16
...
===INDEX_ID:56555
level0 total block is (1)
block_no: 4,level: 0|*|
===INDEX_ID:56556
level0 total block is (1)
block_no: 5,level: 0|*|
由于數(shù)據(jù)量很小，這兩個(gè)索引都只需要一個(gè)page就能放下仰税，輔助索引keys存儲(chǔ)在5號(hào)page上构资。
2. 掃描確認(rèn)輔助索引數(shù)據(jù)
繼續(xù)用innblock掃描輔助索引，確認(rèn)有多少條數(shù)據(jù)陨簇。
[root@yejr.me]# innblock customers.ibd 5 16
...
-----Total used rows:12 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:216 heapno:7 n_owned 0,delflag:N minflag:0 rectype:0
(3) normal record offset:162 heapno:4 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:234 heapno:8 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:270 heapno:10 n_owned 0,delflag:N minflag:0 rectype:0
(6) normal record offset:126 heapno:2 n_owned 5,delflag:N minflag:0 rectype:0
(7) normal record offset:252 heapno:9 n_owned 0,delflag:N minflag:0 rectype:0
(8) normal record offset:180 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(9) normal record offset:144 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0
(10) normal record offset:198 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0
(11) normal record offset:288 heapno:11 n_owned 0,delflag:N minflag:0 rectype:0
(12) SUPREMUM record offset:112 heapno:1 n_owned 6,delflag:N minflag:0 rectype:3
-----Total used rows:12 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 6,delflag:N minflag:0 rectype:3
(3) normal record offset:126 heapno:2 n_owned 5,delflag:N minflag:0 rectype:0
(4) normal record offset:144 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0
(5) normal record offset:162 heapno:4 n_owned 0,delflag:N minflag:0 rectype:0
(6) normal record offset:180 heapno:5 n_owned 0,delflag:N minflag:0 rectype:0
(7) normal record offset:198 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0
(8) normal record offset:216 heapno:7 n_owned 0,delflag:N minflag:0 rectype:0
(9) normal record offset:234 heapno:8 n_owned 0,delflag:N minflag:0 rectype:0
(10) normal record offset:252 heapno:9 n_owned 0,delflag:N minflag:0 rectype:0
(11) normal record offset:270 heapno:10 n_owned 0,delflag:N minflag:0 rectype:0
(12) normal record offset:288 heapno:11 n_owned 0,delflag:N minflag:0 rectype:0
...
可以看到吐绵，總共有12條記錄，除去INFIMUM河绽、SUPREMUM這兩條虛擬記錄己单，共有10條物理記錄。為什么是10條記錄耙饰，而不是5條記錄呢纹笼，這是因?yàn)閙ulti-valued index實(shí)際上是把每個(gè)zipcode value對都視為一天索引記錄。再看一眼表數(shù)據(jù)：
[root@yejr.me]> select id, custinfo->'$.zipcode' from customers;
+----+-----------------------+
| id | custinfo->'$.zipcode' |
+----+-----------------------+
| 1 | [94582, 94536] |
| 2 | [94568, 94507, 94582] |
| 3 | [94477, 94507] |
| 4 | [94536] |
| 5 | [94507, 94582] |
+----+-----------------------+
上面寫入的5條數(shù)據(jù)中苟跪，共有10個(gè)zipcode廷痘，雖然有些zipcode是相同的，但他們對應(yīng)的id值不同削咆，因此也要分別記錄索引牍疏。也就是說， "zipcode":[94582,94536] 這里的兩個(gè)整型數(shù)據(jù)拨齐，實(shí)際上在索引樹中鳞陨，是兩條獨(dú)立的數(shù)據(jù)，只不過他們都分別指向id=1這條數(shù)據(jù)瞻惋。那么厦滤，這個(gè)索引實(shí)際上存儲(chǔ)的順序就應(yīng)該是下面這樣才對：
+---------+------+
| zipcode | id |
+---------+------+
| 94477 | 3 |
| 94507 | 2 |
| 94507 | 3 |
| 94507 | 5 |
| 94536 | 1 |
| 94536 | 4 |
| 94568 | 2 |
| 94582 | 1 |
| 94582 | 2 |
| 94582 | 5 |
+---------+------+
提醒下，由于InnoDB的index extensions特性歼狼，輔助索引存儲(chǔ)時(shí)總是包含聚集索引列值掏导，若有兩個(gè)值相同的輔助索引值，則會(huì)根據(jù)其聚集索引列值進(jìn)行排序羽峰。當(dāng)然了趟咆，以上也只是我們的推測，并不能實(shí)錘梅屉，直接去核對源碼好像有點(diǎn)難度值纱。好在可以用另一個(gè)神器bcview來查看底層數(shù)據(jù)。這里之所以沒有采用innodb_space工具坯汤，是因?yàn)樗鼘ySQL 5.7以上的版本兼容性不夠好虐唠，有些場景下解析出來的可能是錯(cuò)誤數(shù)據(jù)。
3. 用bcview工具確認(rèn)結(jié)論
按照推測惰聂，zips這個(gè)索引按照邏輯順序的話疆偿，第一條索引記錄是 [94477,3]才對咱筛，上面看到第一條邏輯記錄的偏移量是216，我們來看下杆故。
# 從上面掃描結(jié)果可知迅箩，一條記錄總消耗存儲(chǔ)空間是18字節(jié)
bcview customers.ibd 16 216 18
...
# 這里為了排版方便，我給人為折行了
current block:00000005 --對應(yīng)的pageno=5
--Offset:00216 --偏移量216
--cnt bytes:18 --讀取18字節(jié)
--data is:000000000001710d80000003000000400024
...
來分析下這條數(shù)據(jù)反番，要拆分成幾段來看沙热。
000000000001710d，8字節(jié)（BIGINT）罢缸，十六進(jìn)制轉(zhuǎn)成十進(jìn)制，就是 94477
80000003投队，4字節(jié)（INT）枫疆，對應(yīng)十進(jìn)制3，也就是id=3
000000400024敷鸦，record headder息楔，6字節(jié)，忽略
這表明推測結(jié)果是正確的扒披。
另外值依，如果按照物理寫入順序，則第一條數(shù)據(jù)id=1這條數(shù)據(jù)：
+----+-----------------------+
| id | custinfo->'$.zipcode' |
+----+-----------------------+
| 1 | [94582, 94536] |
+----+-----------------------+
這條物理記錄碟案，共產(chǎn)生兩條輔助索引記錄愿险，我們一次性掃描出來（36字節(jié)）：
bcview customers.ibd 16 126 36
...
current block:00000005
--Offset:00126
--cnt bytes:36
--data is:000000000001714880000001000000180036000000000001717680000001000000200048
...
同上，解析結(jié)果見下（存儲(chǔ)順序要反著看）：
0000000000017148 => 94536
80000001 => id=1
000000180036
0000000000017176 => 94582
80000001 => id=1
000000200048
可以看到价说，確實(shí)是把JSON里的多個(gè)值拆開來辆亏，對應(yīng)到聚集索引后存儲(chǔ)每個(gè)鍵值。至此鳖目，我們完全搞清楚了multi-valued index的底層存儲(chǔ)結(jié)構(gòu)扮叨。
