解密MySQL 8.0 multi-valued indexes

什么是multi-valued index

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

假设有一条数据是 {"user":"Bob","zipcode":[94477,94536]},意为Bob这位用户mysql怎么读,他拥有多个邮编"9索引是什么意思4477"和"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数据类型,其余场景还不支json持。

multi-valued index怎么用

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

# 创建测试表

[root@yejr.me]> CREATE T索引超出矩阵维度ABLE customers (
id INT NOT NULL AUTO_Imysql基础命令NCREMENT,
custinfo JSON,
primary key(id)
)engimysql安装ne=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":"M数据格式有哪几种ary","user_id存储":72,"zipcode":[94536]}'),
('{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');

# 执行查询,此时还没创建索引,需要全表扫描
[root@yejr.me]> DESC SELECT * FROmysql安装配置教程M customers WHERE
JSON_存储CONTAINS(custinfo->'$.zipcode',串口数据格式
CAST('[94507,94582]'关系型数据库中的单元有多选题 AS JSON))\G
1. row
...
type: ALL
possible_keys: NULL
key: NULL
...
ro关系型数据库的优缺点ws: 5
filtered: 100.00
Extra: Using wher索引失效的几种情况e

# 创建multi-valued index
[root@yejr.me]> ALTER TABLE custo索引符号mers AD关系型数据库和非关系型区别D INDEX
zips((CAST(custinfo->'$.zi数据格式怎么设置pcode' AS UNSIGNED ARRAY)));

# 查看新的执关系型数据库的优缺点行计划,可以走索引
[root@yejr.me]> DESC SELECT * FROM customers WHERE
JSON_CONmysql数据库基础知识TAINS(custinfo->'$.zipcode',
CAST('[94507,94582]' AS JSON))\G
1. row
...
type: range
possib关系型数据库有哪些le_ke数据格式有哪几种y数据格式有哪几种s: 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工具来确认mysql数据库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|*|

由于数据量很小,这两个mysql数据库索引都只需json文件是干什么的要一个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,虽然有些zipcmysql面试题ode是相数据格式同的,但他们对应的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 |
+---------+------+

提醒下,由于Inn数据格式转换oDB的index extensions特性,辅助索引存储时总是包含聚集mysql数据库命令大全索引索引的优缺点列值,若有两个值相同的辅助索引值,则会根据其聚集索引列值进行排序。当然了,以上也只是我们的推测,并不能实锤,直接去核对源码好像有点难度。好在可以关系型数据库和非关系型用另一个神器bcview来查看底层数据。这里之所以没有采用innodb_space工具,是因为它对MySQL存储容量单位 5.7以上的版索引符号本兼容性不够好,有些场景下解析出来存储过程的可能是错误数据。

3. 用bcview工具确认结论

按照数据格式推测,zips这个索引按照逻辑顺序的话,第mysql数据库基础知识一条索引记录是 [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里的多个值拆开来,对应到聚集索引后存储每个键值。至此,mysql面试题我们完全搞清索引失效楚了multi-valued index的底层存储结构。

            </div>

什么是multi-valued indemysql安装配置教程x

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

假设有一条数据是 {"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]&gmysql数据库基础知识t; INS串口数据格式ERT INTO数据格式 customers(custinfo) VAL数据格式化UES
('{"user":"Jack"mysql基础命令,"user_id":37,"zipcode":[94582,94536]}'),
('{"user":"Jill","user_id":22,"zipcomysql安装配置教程de":[94568,94507,94582]}'),
('{"us关系型数据库中的单元有er":"Bob","user_id":31,"zipcode":[94477,94507]}'),
('{"user":"Mary","user_id":72,"zipcode":[94536]}'),
('{"user":"Ted","user存储器中存取速度最快的是_id":56,"zipcode":[94507,94582]}');

# 执json行查询,此时还没创建索引,需要全表扫关系型数据库有哪些
[root@yejr.me]> DESC SELECT * FROM cust关系型数据库的优缺点omers WHERE
JSON_CONTAINS(custinfo->'$.zipcode',
CAST('关系型数据库[94507,94582]' AS JSON))mysql数据库基础知识\G
1. row
...
type: AL关系型数据库的基本运算包含L
possible_keys关系型数据库和非关系型: NULL
key: NULL
...
rows:mysql 5
filtered: 100.00
Extra: Using where

# 创建multi-valued index
[root@yejr.me]> ALTER TABLE customers ADD INDEX
zips((CAST(custinfo->'$.zi索引是什么意思pcode' AS UNSIGNED ARRAY关系型数据库管理系统)));

# 查看新的执行计划,可以存储诸天走索引
[root@yejr.me]> DESC SELECT * FROM customers WHE数据格式RE
JSON_CONTAINS(custinfo->'$.zipcode',
CAST('[94507,94582]' AS JSON))\G
1. row
...
type: range
possible_keys: zips
key: zips
key_len: 9
ref:mysql密码忘记了怎么办 NULL
rows: 6
filtered: 100.00
Extra: Usin关系型数据库g 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条记录,除去INFIMUMjsonp、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是相同的,mysql安装但他们对应的id值不同,因此也要分别记录索引。也就是说, "zipcode":[94582,94536]这里的两个整型json数据数据,实际上在索引树中,是两条独立的数据,只不过他们都分别指向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来查看底层数据。这里之所以没有采用innjsonpodb_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] |
+----+-----------------------+

这条物理记录,共产生两条辅助索引记录,我们一次性扫描出来(36json怎么读字节):

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

可以看到,确实mysql安装配置教程是把JSON里的多个值拆开来,对应到聚集索引后存mysql基础命令储每个键值。至此,我们完全搞清楚了multi-valued index的底层存储结构。