As of MySQL 8.0.17, InnoDB supports multi-valued indexes. A multi-valued index is a secondary index defined on a column that stores an array of values. A “normal” index has one index record for each data record (1:1). A multi-valued index can have multiple index records for a single data record (N:1). Multi-valued indexes are intended for indexing JSON arrays. For example, a multi-valued index defined on the array of zip codes in the following JSON document creates an index record for each zip code, with each index record referencing the same data record.
自MySQL 8.0.17 开始，InnoDB开始支持多值索引。多值索引是一种定义在存储数组值的列上的二级索引。 与常规索引相比,常规索引每条数据记录对应一个索引记录（是1:1的关系）。然而多值索引针对单条数据记录却可以有多个索引记录（呈现N:1)的关系。
多值索引旨在为json数组建立索引。例如下面的json文档中邮政编码定义的多值索引，会为每个邮政编码创建一个索引记录，并且每个索引都引用同一条数据记录。
{
"user":"Bob",
"user_id":31,
"zipcode":[94477,94536]
}
创建多值索引
我们可以使用create table、alter table、create index语句创建多值索引。这需要使用Cast（...AS...ARRAY)， 它将json数组中的同类型标量值转换为SQL数据类型数组。然后用数组中的值隐式地生成一个虚拟列。最后，在虚拟列上创建一个函数索引（也称虚拟索引）。在SQL数据类型数组的虚拟列上定义的函数索引构成了多值索引。
#create table
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON,
INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )
);
## alter table
ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
## create index
CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
多值索引也可以定义为复合索引的一部分。比如:
ALTER TABLE customers ADD INDEX idx_modified_zipcode(modified,
(CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
复合索引最多只能有一个多值键，但是多值键可以出现在复合索引的任何位置。比如：
ALTER TABLE customers ADD INDEX idx_modified_zipcode((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)),modified);
假设一个json字段product_info的格式如下：
[
{
"productId": 1724636604921876500,
"orderQuantity": 20,
"productSpecId": 1724636603915243500
},
{
"productId": 1632282440287490000,
"orderQuantity": 12,
"productSpecId": 761
},
{
"productId": 1515878952081830000,
"orderQuantity": 12,
"productSpecId": 1515878951960195000
},
{
"productId": 1504008471809167400,
"orderQuantity": 5,
"productSpecId": 1504008471746252800
},
...
]
我们想要对这个字段执行json_contains判断：JSON_CONTAINS(product_info -> '
CREATE INDEX idx_productId ON products((CAST(buyer_product_info->'$[*].productId' AS ARRAY)), delivery_time)
什么是函数索引？
函数索引是一种特殊类型的数据库索引，它不是基于表中的原始值直接创建，而是基于对列引用特殊函数或表达式后的结果来创建索引。简单来说，就是将函数或表达式的计算结果进行索引存储，以便在查询中涉及相同函数或者表达式运算时，能偶利用该索引快速定位和检索数据，提高查询性能。
语法：CREATE INDEX index_name ON table_name ((function_expression));
怎么利用多值索引
当在where子句中指定了以下函数时，优化器可以利用到多值索引：member of() 、json_contains() 、json_overlaps() 。
SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
多值索引可以定义为唯一键，如果定义为唯一键，则尝试插入已经存在于多值索引中的值将返回重复键错误。如果重复的值已经存在，尝试添加一个唯一的多值索引失败。
使用多值索引的注意事项
不支持在线创建多值索引
这意味着该操作使用algorithm=COPY。也就是说创建多值索引的操作将会阻塞DML。在多值索引中，每条记录所能包含的最大取值数据量是有上限的。
这个上限是由单个undo log页面可存储的数据量来决定的。
不过最大键数会受多种不同因素的影响，正是因为这种复杂多样的影响因素存在，所以很难去定义一个确定固定的数量限制。经过相关测试发现：在某些情况下，一个多值索引对于每条记录而言，允许存在多达 1604 个整数键（integer keys）。
当达到相应限制时，数据库会报告类似如下的错误信息：ERROR 3905 (HY000): Exceeded max number of values per record for multi-valued index 'idx' by 1 value(s)，这条错误提示表明多值索引 “idx” 中每条记录的取值数量超出了允许的最大值，并且还指出超出了具体几个值（此处示例中是超出了 1 个值）。
总体而言，在使用多值索引时，要清楚其在每条记录取值数量方面存在这样的限制情况，以便在设计数据库结构、插入或更新数据时避免因超出限制而引发错误。
参考文档：
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued
点个“赞 or 在看” 你最好看！
👇👇👇 谢谢各位老板啦！！！
