解密MySQL 8.0 multi-valued indexes

布满荆棘的人生 2023-07-17 14:08 45阅读 0赞

> multi-valued indexes有什么特点。

### 什么是multi-valued index ###

MySQL 8.0.17起，InnoDB引擎新增了对JSON数据类型的多值索引，即multi-valued index。它的作用是针对JSON数据类型中，同一条记录有多个值的情况，加上索引后，根据这些值条件查询时，也可以指向同一条数据。

假设有一条数据是 `{ "user":"Bob","zipcode":[94477,94536]}`，意为Bob这位用户，他拥有多个邮编"94477"和"94536"，这时候如果我们想对zipcode属性加索引，就可以选择使用多值索引了，在以往是不支持这个方式的。可以像下面这样创建索引：（建议在PC端或横版观看，下同）

[root@yejr.me]> CREATE INDEX zips ON t1((
    CAST(data->'$.zipcode' AS UNSIGNED ARRAY)));

在本例中的多值索引实际上是采用基于CAST()的函数索引，CAST()转换后选择的数据类型除了BINARY和JSON，其他都可以支持。目前multi-valued index只针对InnoDB表中的JSON数据类型，其余场景还不支持。

### multi-valued index怎么用 ###

我们来看下一个JSON列怎么创建multi-valued index。

# 创建测试表
    [root@yejr.me]> CREATE TABLE customers (
     id INT NOT NULL AUTO_INCREMENT,
     custinfo JSON,
     primary key(id)
    )engine=innodb;
    
    # 写入5条测试数据
    [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]}');
    
    # 执行查询，此时还没创建索引，需要全表扫描
    [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
    
    # 创建multi-valued index
    [root@yejr.me]> ALTER TABLE customers ADD INDEX
    zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
    
    # 查看新的执行计划，可以走索引
    [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底层是怎么存储的 ###

知道multi-valued index怎么用之后，再来看下它底层是怎么存储索引数据的。以上面的customers表为例，我们利用innblock和bcview工具来确认InnoDB底层是怎么存储的。

#### 1. 先找到辅助索引page ####

先用**innblock**工具确认辅助索引zips在哪个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|*|

由于数据量很小，这两个索引都只需要一个page就能放下，辅助索引keys存储在5号page上。

#### 2. 扫描确认辅助索引数据 ####

继续用**innblock**扫描辅助索引，确认有多少条数据。

[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条记录呢，这是因为multi-valued index实际上是把每个zipcode value对都视为一天索引记录。再看一眼表数据：

[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条数据中，共有10个zipcode，虽然有些zipcode是相同的，但他们对应的id值不同，因此也要分别记录索引。也就是说， `"zipcode":[94582,94536]` 这里的两个整型数据，实际上在索引树中，是两条独立的数据，只不过他们都分别指向id=1这条数据。那么，这个索引实际上存储的顺序就应该是下面这样才对：

+---------+------+
    | 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特性，辅助索引存储时总是包含聚集索引列值，若有两个值相同的辅助索引值，则会根据其聚集索引列值进行排序。当然了，以上也只是我们的推测，并不能实锤，直接去核对源码好像有点难度。好在可以用另一个神器**bcview**来查看底层数据。这里之所以没有采用**innodb\_space**工具，是因为它对MySQL 5.7以上的版本兼容性不够好，有些场景下解析出来的可能是错误数据。

#### 3. 用bcview工具确认结论 ####

按照推测，zips这个索引按照逻辑顺序的话，第一条索引记录是 `[94477,3]`才对，上面看到第一条逻辑记录的偏移量是**216**，我们来看下。

# 从上面扫描结果可知，一条记录总消耗存储空间是18字节
    bcview customers.ibd 16 216 18
    ...
    # 这里为了排版方便，我给人为折行了
    current block:00000005 --对应的pageno=5
    --Offset:00216 --偏移量216
    --cnt bytes:18 --读取18字节
    --data is:000000000001710d80000003000000400024
    ...

来分析下这条数据，要拆分成几段来看。

000000000001710d，8字节（BIGINT），十六进制转成十进制，就是 94477
    80000003，4字节（INT），对应十进制3，也就是id=3
    000000400024，record headder，6字节，忽略

这表明推测结果是正确的。

另外，如果按照物理写入顺序，则第一条数据id=1这条数据：

+----+-----------------------+
    | id | custinfo->'$.zipcode' |
    +----+-----------------------+
    |  1 | [94582, 94536]        |
    +----+-----------------------+

这条物理记录，共产生两条辅助索引记录，我们一次性扫描出来（36字节）：

bcview customers.ibd 16 126 36
    ...
    current block:00000005
    --Offset:00126
    --cnt bytes:36
    --data is:000000000001714880000001000000180036000000000001717680000001000000200048
    ...

同上，解析结果见下（存储顺序要**反着看**）：

0000000000017148 => 94536
    80000001 => id=1
    000000180036
    0000000000017176 => 94582
    80000001 => id=1
    000000200048

可以看到，确实是把JSON里的多个值拆开来，对应到聚集索引后存储每个键值。至此，我们完全搞清楚了multi-valued index的底层存储结构。

#### 延伸阅读 ####

*  [innblock | InnoDB page观察利器][innblock _ InnoDB page]
 *  Multi-Valued Indexes，https://dev.mysql.com/doc/refman/8.0/en/create-index.html\#create-index-multi-valued

**来个调查，看看大家现在都用什么方式运行MySQL**

**全文完**

--------------------

由我主讲的知数堂「MySQL优化课」第17期已发车，我们的课程从第15期就升级成MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。

![format_png][]

--------------------

另外，我在腾讯课堂的短课程《**MySQL性能优化**》已开课，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。

下面是报名小程序码

![format_png 1][]

点“在看”给我一朵小黄花![format_png 2][]

[innblock _ InnoDB page]: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&idx=1&mid=2653930876&scene=21&sn=000298084f385654e587244d030df064#wechat_redirect
[format_png]: /images/20230528/8a0ff9d71130431fb212497b065528a8.png
[format_png 1]: /images/20230528/5e9e5a2d84034c45ba397959ed1fe5ee.png
[format_png 2]: /images/20230528/32957962eb2742a4a6ff9d2968fd4ce3.png