本文作者：葉金榮，知數(shù)堂聯(lián)合創(chuàng)始人，MySQL DBA課程講師。Oracle MySQL ACE，MySQL布道師。有多年MySQL及系統(tǒng)架構(gòu)設(shè)計(jì)經(jīng)驗(yàn)，擅長(zhǎng)MySQL企業(yè)級(jí)應(yīng)用、數(shù)據(jù)庫設(shè)計(jì)、優(yōu)化、故障處理等。
multi-valued indexes有什么特點(diǎn)。
什么是multi-valued index
MySQL 8.0.17起，InnoDB引擎新增了對(duì)JSON數(shù)據(jù)類型的多值索引，即multi-valued index。它的作用是針對(duì)JSON數(shù)據(jù)類型中，同一條記錄有多個(gè)值的情況，加上索引后，根據(jù)這些值條件查詢時(shí)，也可以指向同一條數(shù)據(jù)。
假設(shè)有一條數(shù)據(jù)是 {"user":"Bob","zipcode":[94477,94536]}，意為Bob這位用戶，他擁有多個(gè)郵編"94477"和"94536"，這時(shí)候如果我們想對(duì)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只針對(duì)InnoDB表中的JSON數(shù)據(jù)類型，其余場(chǎng)景還不支持。
multi-valued index怎么用
我們來看下一個(gè)JSON列怎么創(chuàng)建multi-valued index。
# 創(chuàng)建測(cè)試表
[root@yejr.me]> CREATE TABLE customers (
id INT NOT NULL AUTO_INCREMENT,
custinfo JSON,
primary key(id)
)engine=innodb;
# 寫入5條測(cè)試數(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對(duì)都視為一天索引記錄。再看一眼表數(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是相同的，但他們對(duì)應(yīng)的id值不同，因此也要分別記錄索引。也就是說， "zipcode":[94582,94536] 這里的兩個(gè)整型數(shù)據(jù)，實(shí)際上在索引樹中，是兩條獨(dú)立的數(shù)據(jù)，只不過他們都分別指向id=1這條數(shù)據(jù)。那么，這個(gè)索引實(shí)際上存儲(chǔ)的順序就應(yīng)該是下面這樣才對(duì)：
+---------+------+
| 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)然了，以上也只是我們的推測(cè)，并不能實(shí)錘，直接去核對(duì)源碼好像有點(diǎn)難度。好在可以用另一個(gè)神器bcview來查看底層數(shù)據(jù)。這里之所以沒有采用innodb_space工具，是因?yàn)樗鼘?duì)MySQL 5.7以上的版本兼容性不夠好，有些場(chǎng)景下解析出來的可能是錯(cuò)誤數(shù)據(jù)。
3. 用bcview工具確認(rèn)結(jié)論
按照推測(cè)，zips這個(gè)索引按照邏輯順序的話，第一條索引記錄是 [94477,3]才對(duì)，上面看到第一條邏輯記錄的偏移量是216，我們來看下。
# 從上面掃描結(jié)果可知，一條記錄總消耗存儲(chǔ)空間是18字節(jié)
bcview customers.ibd 16 216 18
...
# 這里為了排版方便，我給人為折行了
current block:00000005 --對(duì)應(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），對(duì)應(yīng)十進(jìn)制3，也就是id=3
000000400024，record headder，6字節(jié)，忽略
這表明推測(cè)結(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è)值拆開來，對(duì)應(yīng)到聚集索引后存儲(chǔ)每個(gè)鍵值。至此，我們完全搞清楚了multi-valued index的底層存儲(chǔ)結(jié)構(gòu)。
