新功能初探 | MySQL 8.0 Multi-Valued Indexes功能简述

MySQL 8.0的Multi-Valued Indexes（多值索引）是一种针对JSON数组类型数据的特殊索引功能，允许为单个主键下的JSON数组字段创建多个二级索引项，从而支持对数组元素的快速查询。 以下是详细说明：

• 多值索引基础

  索引基于JSON数组字段构建，每个数组元素会生成独立的索引条目。

  索引记录数可能多于主键记录数（例如，一个主键对应多个数组元素）。

  仅支持特定接口函数查询，无法直接用于常规SQL条件（如WHERE子句直接比较）。

• 虚拟列与去重机制

  多值索引本质是函数索引（Functional Index），通过虚拟列从JSON中提取数组值。

  数组中的重复值在索引中仅存储一次，减少存储开销。

  每个索引仅允许包含一个多值列（multi-value column）。

• 数值类型：DECIMAL、INTEGER
• 时间类型：DATETIME
• 字符串类型：VARCHAR/CHAR

多值索引需通过以下函数访问，这些函数会利用索引优化查询性能：

• MEMBER OF检查值是否属于JSON数组。示例：

  SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode');

  返回包含94507的记录（如ID为2、5的行）。

• JSON_CONTAINS检查JSON数组是否包含指定值或子数组。示例：

  SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

  返回完全匹配子数组的记录（如ID为2、5的行）。

• JSON_OVERLAPS检查两个JSON数组是否有交集。示例：

  SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

  返回包含任一指定值的记录（如ID为1、2、5的行）。

1. 插入记录

   通过row_ins_sec_index_multi_value_entry函数构建索引条目。

   使用Multi_value_entry_builder_insert类解析、排序并去重数组值。

   数据存储在multi_value_data结构中，指针指向dfield_t::data。

2. 删除记录

   调用row_upd_del_multi_sec_index_entry函数。

   通过Multi_value_entry_builder_normal类构建待删除条目，并从索引中移除。

3. 更新记录

   使用row_upd_multi_sec_index_entry函数。

   通过calc_row_difference计算差异，仅更新需要修改的索引条目。

4. 事务回滚

   回滚时通过trx_undo_rec_get_multi_value从Undo Log中恢复多值列数据。

   依赖Multi_value_logger类读取和重建字段值。

5. Purge操作

   清理二级索引记录时，调用row_purge_remove_multi_sec_if_poss函数判断是否可删除。

• JSON数组查询优化适用于需要频繁查询JSON数组中特定元素的场景（如地理位置、标签、权限列表等）。示例：

  -- 查询包含特定邮编的客户SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507]' AS JSON));

• 替代传统多表关联避免为数组元素创建单独的关联表，减少JOIN操作开销。

• 索引类型限制：仅支持InnoDB引擎，且索引列必须为JSON数组提取的虚拟列。
• 查询方式限制：必须通过MEMBER OF、JSON_CONTAINS或JSON_OVERLAPS函数访问索引。
• 性能权衡：虽然索引加速查询，但插入、更新和删除操作需处理多值逻辑，可能增加写开销。

MySQL 8.0的多值索引为JSON数组提供了高效的查询能力，通过虚拟列和去重机制优化存储，并依赖特定接口函数实现索引访问。其设计平衡了查询灵活性与性能需求，适用于现代应用中半结构化数据的处理场景。