多值索引简介
从MySQL 8.0.17 开始, InnoDB支持创建多值索引(Multi-Valued Indexes),该索引是在JSON存储值数组的列上定义的二级索引,对于单个数据记录可以有多个索引记录。此类索引特定的语法定义:
CAST(expression AS type ARRAY),例如CAST(data->'$.zipcode' AS UNSIGNED ARRAY)。 跟普通索引一样,也可以在EXPLAIN中查看到。
创建多值索引
跟其他索引一样,多值索引可以在建表时添加,也可以通过ALTER TABLE或者CREATE INDEX创建。
JSON对象字段索引
语法
复制
1
ALTER
TABLE
customers
ADD
INDEX
idx_mv_custinfo_list( (
CAST
( custinfo ->
'$.key'
AS
UNSIGNED array ) ) );
注意:这里在CAST语法外面有两层单括号!,如果少写一个会报错!
测试案例
PS:文中的案例是参考官方文档中的案例,只是作为测试,所以在命名等方面并不怎么规范,实际开发过程中要严格遵守公司团队的开发规范,不要偷懒!
复制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
DROP
TABLE
IF EXISTS `customers`;
/*建表语句*/
CREATE
TABLE
customers (
id
BIGINT
NOT
NULL
AUTO_INCREMENT
PRIMARY
KEY
,
modified DATETIME
DEFAULT
CURRENT_TIMESTAMP
ON
UPDATE
CURRENT_TIMESTAMP
,
custinfo JSON
NOT
NULL
);
/*插入写测试数据*/
INSERT
INTO
customers
VALUES
(
NULL
, NOW(),
'{"key":94582,"value":"asdf"}'
),
(
NULL
, NOW(),
'{"key":94568,"value":"gjgasdasdf"}'
),
(
NULL
, NOW(),
'{"key":94477,"value":"ghasdfsdf"}'
),
(
NULL
, NOW(),
'{"key":94536,"value":"hagsdfgdf"}'
),
(
NULL
, NOW(),
'{"key":94507,"value":"wasfgjdf"}'
);
/*添加多值索引*/
ALTER
TABLE
customers
ADD
INDEX
idx_mv_custinfo_list( (
CAST
( custinfo ->
'$.key'
AS
UNSIGNED array)) );
/*测试 MEMBER
OF
语法*/
SELECT
*
FROM
customers
WHERE
94507 MEMBER
OF
( custinfo ->
'$.key'
);
/*测试 JSON_CONTAINS 语法*/
SELECT
*
FROM
customers
WHERE
JSON_CONTAINS(
custinfo ->
'$.key'
,
CAST
(
'[94582]'
AS
JSON ));
/*测试 JSON_OVERLAPS 语法*/
SELECT
*
FROM
customers
WHERE
JSON_OVERLAPS (
custinfo ->
'$.key'
,
CAST
(
'[94477]'
AS
JSON ));
查看执行计划发现可以使用到索引:
如果需要给字符类型创建多值索引,则必须是utf8mb4字符集且排序规则是utf8mb4_0900_as_cs,否则报错该版本不支持:
如果要为binary二进制字符串创建多值索引的话,则排序规则必须是binary,否则报错不支持。
修改排序规则后可成功添加索引:
JSON数组对象索引
语法
已复制
1
ALTER
TABLE
customers
ADD
INDEX
idx_mv_custinfo_list( (
CAST
( custinfo ->
'$[*].key'
AS
UNSIGNED array ) ) );
注意:这里在CAST语法外面有两层单括号!如果少写一个会报错!
测试案例
已复制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
DROP
TABLE
IF EXISTS `customers`;
/*建表语句*/
CREATE
TABLE
customers (
id
BIGINT
NOT
NULL
AUTO_INCREMENT
PRIMARY
KEY
,
modified DATETIME
DEFAULT
CURRENT_TIMESTAMP
ON
UPDATE
CURRENT_TIMESTAMP
,
custinfo JSON
NOT
NULL
);
/*插入写测试数据*/
INSERT
INTO
customers
VALUES
(
NULL
, NOW(),
'[{"key":94582},{"key":94536}]'
),
(
NULL
, NOW(),
'[{"key":94568},{"key":94507},{"key":94582}]'
),
(
NULL
, NOW(),
'[{"key":94477},{"key":94507}]'
),
(
NULL
, NOW(),
'[{"key":94536}]'
),
(
NULL
, NOW(),
'[{"key":94507},{"key":94582}]'
);
/*添加多值索引*/
ALTER
TABLE
customers
ADD
INDEX
idx_mv_custinfo_list( (
CAST
( custinfo ->
'$[*].key'
AS
UNSIGNED array)) );
/*测试 MEMBER
OF
语法*/
SELECT
*
FROM
customers
WHERE
94507 MEMBER
OF
( custinfo ->
'$[*].key'
);
/*测试 JSON_CONTAINS 语法*/
SELECT
*
FROM
customers
WHERE
JSON_CONTAINS(
custinfo ->
'$[*].key'
,
CAST
(
'[94582, 94507]'
AS
JSON ));
/*测试 JSON_OVERLAPS 语法*/
SELECT
*
FROM
customers
WHERE
JSON_OVERLAPS (
custinfo ->
'$[*].key'
,
CAST
(
'[94477, 94582]'
AS
JSON ));
查看执行计划发现可以使用到索引:
在组合索引中创建多值索引
语法
语法跟普通组合索引差不多,同样也遵守最左匹配原则:
复制
1
2
ALTER
TABLE
customers
ADD
INDEX
idx_age_custinfo$list_modified
( age, (
CAST
( custinfo ->
'$[*].key'
AS
UNSIGNED ARRAY )), modified );
注意:这里在CAST语法外面需要使用小括号括起来!
测试案例
复制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DROP
TABLE
IF EXISTS `customers`;
/*建表语句*/
CREATE
TABLE
customers (
id
BIGINT
NOT
NULL
AUTO_INCREMENT
PRIMARY
KEY
,
age tinyint(4)
not
null
,
modified DATETIME
DEFAULT
CURRENT_TIMESTAMP
ON
UPDATE
CURRENT_TIMESTAMP
,
custinfo JSON
NOT
NULL
);
/*插入写测试数据*/
INSERT
INTO
customers
VALUES
(
NULL
, 21, NOW(),
'[{"key":94582},{"key":94536}]'
),
(
NULL
, 22, NOW(),
'[{"key":94568},{"key":94507},{"key":94582}]'
),
(
NULL
, 23, NOW(),
'[{"key":94477},{"key":94507}]'
),
(
NULL
, 24, NOW(),
'[{"key":94536}]'
),
(
NULL
, 25, NOW(),
'[{"key":94507},{"key":94582}]'
);
/*添加多值索引*/
alter
table
customers
DROP
INDEX
idx_age_custinfo$list_modified ;
ALTER
TABLE
customers
ADD
INDEX
idx_age_custinfo$list_modified ( age, (
CAST
( custinfo ->
'$[*].key'
AS
UNSIGNED ARRAY )),modified );
ALTER
TABLE
customers
ADD
INDEX
idx_age_custinfo$list_modified ((
CAST
( custinfo ->
'$[*].key'
AS
UNSIGNED ARRAY )), age,modified );
ALTER
TABLE
customers
ADD
INDEX
idx_age_custinfo$list_modified ( age,modified, (
CAST
( custinfo ->
'$[*].key'
AS
UNSIGNED ARRAY )) );
/*测试 MEMBER
OF
语法*/
SELECT
*
FROM
customers
WHERE
94536 MEMBER
OF
( custinfo ->
'$[*].key'
)
and
modified =
'2021-08-05 10:36:34'
and
age = 21;
查看执行计划发现可以使用到索引:
多值索引的局限
一个多值索引只允许包含一个属性的值
该索引目前只支持三个语法
目前只有MEMBER OF、 JSON_CONTAINS()、 JSON_OVERLAB()三种语法可以使用到多值索引。
( CAST( custinfo -> '$.key' AS UNSIGNED array)),语法中的array是可以不加的,之所以要强制加是因为如果不加就不是数组结构,不是数组结构就没法直接使用上述三个语法,需要通过JSON_ARRAY()等方法转换后才能使用,这样就会导致索引失效!因此不管需要加索引的字段是单个值的字段还是数组字段,都要加上array关键字。
该索引不支持用于表关联
不能结合前缀索引
不支持在线创建多值索引
这句话的意思是该操作使用 ALGORITHM=COPY,即通过新建一张表结构,再将数据复制过去的方式实现索引的创建。因此该过程中不允许DML操作。
binary字符集的排序规则必须是binary
utf8mb4字符集的排序规则必须是utf8mb4_0900_as_cs
其他任何字符集或排序规则都不能创建多值索引,创建时会报错当前版本不支持。
应用场景
多值索引的应用场景非常广泛!有了他之后很多关联关系表都可以不用了!举个简单的例子:用户标签,很多场景下会给用户贴上各种标签,比如1高 2富 3帅,为了后续的更高效的做统计或筛选查询,我们不能直接将这个标签作为一个字段存储,因为没有索引查询效率不高,所以很多时候会使用一张关联关系表来存储用户-标签的关系。但是现在有了多值索引,我们就可以将标签作为一个字段存储了!
这只是其中一个小场景,类似的场景非常多,用户可以换成任何事物,标签也可以换成其他任何属性,只要是这个事物存在多种属性值就行,存在一个多对多关系,那么在没有需要这个属性与其他表做表关联的请况下),都可以使用多值索引实现!多值索引不支持表关联,因此如果需要用该字段在做表关联的话就不合适了。