共计 6886 个字符，预计需要花费 18 分钟才能阅读完成。
说到这儿，可能有的人会有些疑难：多值索引不就是联结索引吗，还须要独自开一篇来讲？
多值索引和基于多个字段的联结索引齐全不同，联结索引是基于多个一维字段，比方字段 r1 int, r2 int,r3 int，这三个字段的组合是联结索引。个别用于三个字段的联结查找，比方 r1 = 1 and r2 = 2 and r3 = 2 等等。
多值索引则不同，它是基于单个字段的，不同的是多值索引基于多维字段，比方数组：[1,2,3,4,5,6]，基于这样的一个数组来建设索引，能够用来检索数组内任何一个元素值。比方我要查找元素 3 是不是在这个数组里等等。
多值索引是在 MySQL 8.0.17 后才推出的新性能，次要目标是冲破 MySQL 单张表索引的硬性限度（不能超过 64 个）。
上面来讲几个例子介绍下多值索引的大抵实用场景。先来看看对于多个字段都有可能参加的查问，基于这个条件，建设上面表 t1，蕴含主键 ID 以及残余 6 个字段，表有 300W 行记录。
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`r1` int DEFAULT NULL,
`r2` int DEFAULT NULL,
`r3` int DEFAULT NULL,
`r4` int DEFAULT NULL,
`r5` int DEFAULT NULL,
`r6` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 3087152 |
+----------+
1 row in set (1.03 sec)相干示例数据如下：
mysql> select * from t1 limit 10;
+----+------+------+------+------+------+------+
| id | r1 | r2 | r3 | r4 | r5 | r6 |
+----+------+------+------+------+------+------+
| 1 | 460 | 81 | 24 | 876 | 308 | 911 |
| 2 | 632 | 424 | 225 | 854 | 593 | 402 |
| 3 | 232 | 951 | 60 | 445 | 47 | 898 |
| 4 | 350 | 56 | 230 | 981 | 213 | 122 |
| 6 | 972 | 493 | 547 | 256 | 639 | 428 |
| 7 | 221 | 823 | 449 | 774 | 526 | 306 |
| 8 | 953 | 847 | 372 | 322 | 493 | 496 |
| 9 | 2 | 523 | 606 | 460 | 482 | 30 |
| 13 | 701 | 418 | 984 | 665 | 374 | 876 |
| 14 | 256 | 650 | 481 | 454 | 828 | 779 |
+----+------+------+------+------+------+------+
10 rows in set (0.00 sec)那当初有一个需要：检索除了主键 ID 列外，其余列蕴含了数字 650 的记录。SQL 能够这么写：
select count(*) from t1 where r1 = 650 or r2 = 650 or r3 = 650 or r4 = 650 or r5 = 650 or r6 = 650;因为 6 个字段并集检索，为了检索效率，除了主键外，其余字段都别离独自加上索引，这样就能用上 MySQL 的 INDEX_MERGE 优化策略：
mysql> alter table t1 add key idx_r1(r1), \
-> add key idx_r2(r2), \
-> add key idx_r3(r3), \
-> add key idx_r4(r4), \
-> add key idx_r5(r5), \
-> add key idx_r6(r6);
Query OK, 0 rows affected (1 min 29.59 sec)
Records: 0 Duplicates: 0 Warnings: 0来进行下查问，执行了 210 毫秒，工夫也不快，不过曾经很优化了。
mysql> select count(*) from t1 where r1 = 650
-> or r2 = 650
-> or r3 = 650
-> or r4 = 650
-> or r5 = 650
-> or r6 = 650;
+----------+
| count(*) |
+----------+
| 18350 |
+----------+
1 row in set (0.21 sec)来看看查问打算，MySQL 把这个几个索引都给用上了。
mysql> explain select count(*) from t1 where r1 = 650 or r2 = 650 or r3 = 650 or r4 = 650 or r5 = 650 or r6 = 650\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index_merge
possible_keys: idx_r1,idx_r2,idx_r3,idx_r4,idx_r5,idx_r6
key: idx_r1,idx_r2,idx_r3,idx_r4,idx_r5,idx_r6
key_len: 5,5,5,5,5,5
ref: NULL
rows: 18392
filtered: 100.00
Extra: Using union(idx_r1,idx_r2,idx_r3,idx_r4,idx_r5,idx_r6); Using where
1 row in set, 1 warning (0.00 sec)那接下来看看如何简化这张表和这条 SQL：
建设一张表 t3，蕴含字段 ID 和一个 JSON 串，并且把表 t1 的数据处理后导入：
mysql> create table t3 (id int primary key, r_com json);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t3 \
> select id, json_object('id', json_array(r1,r2,r3,r4,r5,r6)) from t1;
Query OK, 3087152 rows affected (43.87 sec)
Records: 3087152 Duplicates: 0 Warnings: 0导入后的示例数据如下，
mysql> select * from t3 limit 10;
+----+----------------------------------------+
| id | r_com |
+----+----------------------------------------+
| 1 | {"id": [460, 81, 24, 876, 308, 911]} |
| 2 | {"id": [632, 424, 225, 854, 593, 402]} |
| 3 | {"id": [232, 951, 60, 445, 47, 898]} |
| 4 | {"id": [350, 56, 230, 981, 213, 122]} |
| 6 | {"id": [972, 493, 547, 256, 639, 428]} |
| 7 | {"id": [221, 823, 449, 774, 526, 306]} |
| 8 | {"id": [953, 847, 372, 322, 493, 496]} |
| 9 | {"id": [2, 523, 606, 460, 482, 30]} |
| 13 | {"id": [701, 418, 984, 665, 374, 876]} |
| 14 | {"id": [256, 650, 481, 454, 828, 779]} |
+----+----------------------------------------+
10 rows in set (0.00 sec)那此时针对表 t3 加一个多值索引：(能够看到多值索引的根底是一个数组)
mysql> alter table t3 add key idx_r_com ((cast(r_com->'$.id' as unsigned array)));
Query OK, 0 rows affected (58.17 sec)
Records: 0 Duplicates: 0 Warnings: 0接下来能够把之前的 SQL 改成如下：
select count(*) from t3 where 650 member of(r_com->"$.id");来执行下这条 SQL：(执行工夫 200 毫秒，和之前多个字段求并集的执行工夫差不多)
mysql> select count(*) from t3 where 650 member of(r_com->"$.id");
+----------+
| count(*) |
+----------+
| 18350 |
+----------+
1 row in set (0.20 sec)看下执行打算，间接走的咱们创立的多值索引：
mysql> explain select count(*) from t3 where 650 member of(r_com->"$.id")\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ref
possible_keys: idx_r_com
key: idx_r_com
key_len: 9
ref: const
rows: 153
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
1 row in set (0.25 sec)如果单单从下面的例子看不出来多值索引有啥值得夸耀的中央，无非就是把多个字段变为一个 JSON 字段而已，查问效率也没有高多少，如果了解到这儿，那就须要再多说几句了。MySQL 对于单表来说，开篇也说过，有硬性个数限度，这个个数为 64，也就是说一张表最多只能有 64 个索引。不过事实场景是，如果一张表须要建设 64 个索引，那这张表对于业务来讲，也就废了，基本上写入会巨慢。这会多值索引的用处就来了。
我来举个例子：比方建设一张新表 t2，501 个字段。（因为字段比拟多，须要写脚本来解决后续 DDL）
mysql> create table t2(id int auto_increment primary key);
Query OK, 0 rows affected (0.03 sec)这儿我别离写两个存储过程来结构表构造与表数据：
表构造结构存储过程代码：
DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_extend_t2_columns`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_extend_t2_columns`(IN f_num INT UNSIGNED)
BEGIN
DECLARE i INT DEFAULT 1;
SET @stmt = 'alter table t2';
WHILE i <= f_num DO
SET @stmt = CONCAT(@stmt,'add r',i,'int,');
SET i = i + 1;
END WHILE;
SET @stmt = LEFT(@stmt,CHAR_LENGTH(@stmt)-1);
PREPARE s1 FROM @stmt;
EXECUTE s1;
DROP PREPARE s1;
SET @stmt = NULL;
END$$
DELIMITER ;造数据存储过程代码：
DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_generate_t2_data`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_generate_t2_data`(
IN f_num INT UNSIGNED,
IN f_total_record INT UNSIGNED
)
BEGIN
DECLARE i ,j INT DEFAULT 1;
SET @@autocommit = 0;
WHILE i <= f_total_record DO
SET @s1 = 'insert into t2 (';
SET @s2 = 'values (';
SET j = 1;
WHILE j <= f_num DO
SET @s1 = CONCAT(@s1,'r',j,',');
SET @s2 = CONCAT(@s2,CEIL(RAND()*1000),',');
SET j = j + 1;
END WHILE;
SET @s1 = CONCAT(LEFT(@s1,CHAR_LENGTH(@s1) - 1));
SET @s2 = CONCAT(LEFT(@s2,CHAR_LENGTH(@s2) - 1));
SET @s1 = CONCAT(@s1,’)’);
SET @s2 = CONCAT(@s2,’)’);
SET @stmt = CONCAT(@s1,@s2);
PREPARE s1 FROM @stmt;
EXECUTE s1;
DROP PREPARE s1;
IF (MOD(i,100) = 0 ) THEN
COMMIT; END IF;
SET i = i + 1;
END WHILE;
COMMIT;
SELECT NULL,NULL,NULL INTO @s1,@s2,@stmt;
END$$
DELIMITER ;别离调用这两个存储过程来创立表构造和数据，给表 t2 增加 10W 条记录。
mysql> call sp_extend_t2_columns(500);
Query OK, 0 rows affected (0.09 sec)
mysql> call sp_generate_t2_data(500,100000);
Query OK, 1 row affected (12 min 59.48 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.46 sec)比方当初要进行之前的查问，那须要写的 SQL 就比拟长了，须要 OR 500 个字段：
select count(*) from t2 where r1 = 650 or r2 = 650 ... or r500=650SQL 长度也倒没有大关系，次要性能在这儿存在很大问题，这张表不容许建设这么多索引。（这儿省略验证过程，必定巨慢）那为了实现这类需要，革新下表 t2，变为表 t4，原理和之前革新表 t1 一样。
mysql> create table t4 (id int primary key, r_com json);
Query OK, 0 rows affected (0.02 sec)插入表 t2 的数据到 t4，还得拼个 SQL，或者写个存储过程。我简略写个存储过程如下：
DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_copy_t2_data_to_t4`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_copy_t2_data_to_t4`( )
BEGIN
DECLARE i INT DEFAULT 1;
SET @stmt = 'insert into t4 select id,json_object(''id'',json_array(';
WHILE i <= 500 DO
SET @stmt = CONCAT(@stmt,'r',i,',');
SET i = i + 1;
END WHILE;
SET @stmt = LEFT(@stmt,CHAR_LENGTH(@stmt)-1);
SET @stmt = CONCAT(@stmt,')) from t2');
PREPARE s1 FROM @stmt;
EXECUTE s1;
DROP PREPARE s1;
SET @stmt = NULL;
END$$
DELIMITER ;调用下这个存储过程，并且给表 t4 加上多值索引：
mysql> call sp_copy_t2_data_to_t4;
Query OK, 0 rows affected (23.41 sec)
mysql> alter table t4 add key idx_r_com ((cast(r_com->'$.id' as unsigned array)));
Query OK, 0 rows affected (2 min 15.08 sec)
Records: 0 Duplicates: 0 Warnings: 0速度也不敢恭维，不过绝对简洁性来说，要好过单个列查问，并且速度必定要优于之前的基于 t3 的全表扫。
mysql> select count(*) from t4 where 270 member of(r_com->'$.id');
+----------+
| count(*) |
+----------+
| 39501 |
+----------+
1 row in set (11.31 sec) 说到这里，想必对多值索引曾经有一个很粗浅的认知，特地是冲破 MySQL 单表索引硬性限度方面，十分杰出。
