MySQL 8.0 多值索引与 JSON 索引性能可验证指南

## 摘要

• 目标：验证 MySQL 8.0 针对 JSON 数组的多值索引（Multi-Valued Index, MVI）与函数索引在实际检索中的性能收益与适用边界。
• 结论概要：在 `MEMBER OF()`/`JSON_OVERLAPS()` 等查询模式下，正确建立 MVI/函数索引可将过滤成本从全表扫描降为索引查找，显著降低延迟与 CPU 使用；但写入成本与索引维护开销需评估。

## 背景与术语

• 多值索引（MVI）：对 JSON 数组元素建立索引，使 `MEMBER OF()`/集合重叠类查询可使用索引。
• 函数索引（Functional Index）：通过表达式或生成列（虚拟/存储）建立索引，例如对 `JSON_EXTRACT()` 的结果索引。
• 适用版本：MySQL 8.0.17+ 引入 MVI，8.0 支持函数索引与 JSON 相关内置函数。

## 测试环境与参数

• 版本：`MySQL 8.0.36`（示例版本，8.0.17+ 均可测试）。
• 硬件：8 vCPU、32GB RAM、NVMe SSD；操作系统 Linux x86_64；`innodb_flush_log_at_trx_commit=1`（默认一致性）。
• 关键配置：`innodb_buffer_pool_size` 设为物理内存的 50%–75% 以覆盖工作集；`sql_mode` 默认；`character_set_server=utf8mb4`。

## 数据模型与索引设计

CREATE DATABASE demo_mvi;
USE demo_mvi;

-- 业务表：每行记录含一个 JSON 数组字段，存储标签/技能/权限等集合
CREATE TABLE user_profile (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL,
  tags JSON NOT NULL,
  -- 虚拟生成列：提取数组元素，便于函数索引
  tags_all JSON GENERATED ALWAYS AS (JSON_EXTRACT(tags, '$[*]')) VIRTUAL,
  INDEX idx_tags_member ((CAST(JSON_EXTRACT(tags, '$[*]') AS UNSIGNED ARRAY)))
) ENGINE=InnoDB;

• 说明：`CAST(... AS <type> ARRAY)` 表达式用于创建 MVI，支持 `UNSIGNED/CHAR/DATE` 等；针对数值标签示例使用 `UNSIGNED ARRAY`。

## 加载数据（可复现）

-- 生成 100 万行示例（可按需缩减），每行 5–20 个随机标签（0–9999）
-- 可用应用侧批量插入或存储过程；以下为示意（建议应用批量导入更快）：
DELIMITER $$
CREATE PROCEDURE load_data(IN n INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < n DO
    INSERT INTO user_profile(name, tags) VALUES (
      CONCAT('user_', i),
      JSON_ARRAY( FLOOR(RAND()*10000), FLOOR(RAND()*10000), FLOOR(RAND()*10000),
                  FLOOR(RAND()*10000), FLOOR(RAND()*10000) )
    );
    SET i = i + 1;
  END WHILE;
END $$
DELIMITER ;

CALL load_data(100000); -- 示例加载 10 万行

## 查询模式与可利用索引

-- 精确成员：查找包含标签 1234 的所有用户（索引可用）
SELECT /*+ SET_VAR(optimizer_switch='derived_merge=on') */ id, name
FROM user_profile
WHERE 1234 MEMBER OF(tags);

-- 集合重叠：查找与集合 {1,2,3} 有交集的记录（索引可用）
SELECT id, name
FROM user_profile
WHERE JSON_OVERLAPS(tags, JSON_ARRAY(1,2,3));

-- 传统函数索引示例：对字符串标签索引
-- INDEX idx_tags_str ((CAST(JSON_EXTRACT(tags, '$[*]') AS CHAR(16) ARRAY)))

## 基准步骤

1. 预热：`SET GLOBAL innodb_buffer_pool_dump_now=ON;` 导入后进行读预热，或执行一次全表扫描。

2. 执行 4 类查询：

• 单值 `MEMBER OF()` 精确匹配（命中率高/低各 1 组）。
• `JSON_OVERLAPS()` 与小集合（大小 3–10）。
• 组合过滤：`MEMBER OF()` + 其他列条件。
• 无索引对照：移除索引或在未建索引副本表上运行。

3. 记录指标：95/99 分位延迟、每秒查询数（QPS）、InnoDB 行访问、`Handler_read_key`/`Handler_read_rnd_next`。

4. 写入开销评估：批量 `INSERT` 与 `UPDATE tags` 的事务时间、锁等待、`redo`/`undo` 增长。

## 结果分析（方法论）

• 预期：命中 MVI 时，`Handler_read_key` 增加、`Handler_read_rnd_next` 降低；扫描行数明显下降。
• 写入：`INSERT/UPDATE` 因索引维护导致事务耗时上升，尤其是高更新频率场景；可按写多读少评估是否仅使用函数索引或将集合改造为行式子表。
• 边界：
• 超大数组（> 数百元素）会放大索引大小与维护成本。
• 频繁 `JSON_SET/REMOVE` 更新场景可能更适合 EAV/归一化子表设计（1:N 行式）。

## 最佳实践

• 将集合拆分为行式子表用于高更新频率：

CREATE TABLE user_tag (
  user_id BIGINT NOT NULL,
  tag INT NOT NULL,
  PRIMARY KEY(user_id, tag),
  INDEX(tag)
) ENGINE=InnoDB;

• 对 JSON 保持只读/低频更新时使用 MVI，读性能与建模简洁性最佳。
• 结合覆盖索引与投影列，减少回表：仅返回 `id` 后应用侧批量 `GET` 明细。
• 监控：开启 `performance_schema` 与 `sys` 视图，定期查看 `schema_index_statistics`。

## 注意事项

• 确保版本≥8.0.17 以使用 MVI；较旧版本仅能使用函数索引/生成列。
• 索引表达式类型需与数据一致（如 `UNSIGNED ARRAY` 对数值标签）。
• 大量写入建议使用批量事务（`INSERT ... VALUES ...` 多值 或 `LOAD DATA`）。
• 避免在事务中混合长时间锁持有与大量 JSON 更新，减少死锁风险。

## 结论

在以标签/权限/技能等集合检索为主的场景中，MySQL 8.0 多值索引能显著降低查询成本；但在高更新或超大集合场景，行式归一化更稳健。结合函数索引与合理的数据建模，可兼顾读性能与可维护性。

## 附：观测命令

SHOW STATUS LIKE 'Handler_read%';
SHOW SESSION STATUS LIKE 'Innodb_rows_read';
SELECT * FROM sys.schema_index_statistics WHERE table_schema='demo_mvi';