什么是multi-valued index
MySQL 8.0.17起，InnoDB引擎新增了对JSON数据类型的多值索引，即multi-valued index。它的作用是针对JSON数据类型中，同一条记录有多个值的情况，加上索引后，根据这些值条件查询时，也可以指向同一条数据。
解密MySQL 8.0 multi-valued indexes
以上说明使用json字段加multi-valued 索引需要mysql版本>8.0.17,否则不建议使用json字段做多值查询
主要操作
MEMBER OF
检索当前查询值包含在字段中的数据
JSON_CONTAINS
查询结果必须包含所有的当前查询值
JSON_OVERLAPS
查询结果有一项满足当前值查询值即可
实操:
建表
CREATE TABLE `customers` (
`id` int NOT NULL AUTO_INCREMENT,
`custinfo` json DEFAULT NULL,
`type` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `type` (`type`),
KEY `zips_type` ((cast(json_extract(`custinfo`,_utf8mb4'$.zipcode') as unsigned array)),`type`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
数据
+----+-------------------------------------------------------------------+------+
| id | custinfo | type |
+----+-------------------------------------------------------------------+------+
| 1 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]} | 1 |
| 2 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} | 2 |
| 3 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]} | 5 |
| 4 | {"user": "Mary", "user_id": 72, "zipcode": [94536]} | 5 |
| 5 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]} | 5 |
+----+-------------------------------------------------------------------+------+
查询
MEMBER OF
SELECT * FROM customers WHERE 94582 MEMBER OF(custinfo->'$.zipcode');
+----+-------------------------------------------------------------------+------+
| id | custinfo | type |
+----+-------------------------------------------------------------------+------+
| 1 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]} | 1 |
| 2 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} | 2 |
| 5 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]} | 5 |
+----+-------------------------------------------------------------------+------+
JSON_CONTAINS
SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94582,94536]' AS JSON));
+----+------------------------------------------------------------+------+
| id | custinfo | type |
+----+------------------------------------------------------------+------+
| 1 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]} | 1 |
+----+------------------------------------------------------------+------+
JSON_OVERLAPS
SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94582,94536]' AS JSON));
+----+-------------------------------------------------------------------+------+
| id | custinfo | type |
+----+-------------------------------------------------------------------+------+
| 1 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]} | 1 |
| 2 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} | 2 |
| 5 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]} | 5 |
| 4 | {"user": "Mary", "user_id": 72, "zipcode": [94536]} | 5 |
+----+-------------------------------------------------------------------+------+
索引
需要测试一下multi-valued是否能建联合索引,满足一般常见的查询条件
单字段索引
ALTER TABLE customers ADD INDEX zips_type(CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY),type);
测试很简单,就不用做了.
联合索引
ALTER TABLE customers ADD INDEX zips_type(CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY),type);
单查zipcode会走联合索引
desc SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | customers | NULL | range | zips_type | zips_type | 9 | NULL | 3 | 100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
在满足条件的情况下会选择走zips_type的联合索引
desc SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94582]' AS JSON)) and type =5;
+----+-------------+-----------+------------+-------+----------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+----------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | customers | NULL | range | type,zips_type | zips_type | 14 | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------+------------+-------+----------------+-----------+---------+------+------+----------+-------------+
原理
todo
参考:
操作部分参考MySQL + JSON = 王炸！！
表结构参考解密MySQL 8.0 multi-valued indexes,同时加入索引测试.
