JSON 类型自 MySQL 5.7 引入以来，逐渐成为存储半结构化数据的热门选择。它允许在一列中保存嵌套对象、数组等灵活结构，避免了频繁改表。然而，当数据量增长到百万级，直接对 JSON 字段做条件过滤往往会导致全表扫描，性能急剧下降。如何在 JSON 上建立有效索引，并写出能命中索引的查询，是 MySQL 面试中频繁出现的高阶考点。本文从索引方案和查询优化两个维度展开，帮助你在面试中给出有深度的回答。
一、为什么 JSON 字段默认没有索引？
MySQL 的 InnoDB 引擎使用 B+ 树索引，索引键必须是确定长度且可排序的标量值。JSON 文档本身是变长的、可嵌套的，无法直接作为 B+ 树的键。因此，在 JSON 列上直接创建普通索引会报错：
-- 错误示例
CREATE INDEX idx_json ON t1 (json_col);
-- ERROR 3152 (42000): JSON column 'json_col' supports indexing only via generated columns on a specified JSON path.
MySQL 要求通过生成列（Generated Column） 或函数索引（MySQL 8.0.13+） 将 JSON 中的某个路径提取为标量，再对该标量建索引。
二、方案一：生成列 + 普通索引
这是最经典、兼容性最好的方案。思路是：新增一个生成列，用 JSON_EXTRACT 或 ->> 提取目标路径的值，然后在该生成列上建索引。
ALTER TABLE orders
ADD COLUMN customer_id BIGINT
GENERATED ALWAYS AS (json_col->>'$.customer_id') VIRTUAL,
ADD INDEX idx_customer_id (customer_id);
关键点：
- VIRTUAL vs STORED：VIRTUAL 列不占磁盘空间，仅在读取时计算，索引本身会持久化；STORED 列会实际存储值。对于索引目的，VIRTUAL 通常足够，且加列时不需要重建表（MySQL 8.0 支持 Instant Add Column）。如果该列还频繁用于 SELECT 输出，可考虑 STORED。
- 类型转换：
->>返回的是字符串（utf8mb4），如果 JSON 中存的是数字，比较时可能发生隐式类型转换，导致索引失效。更严谨的做法是显式 CAST：
ALTER TABLE orders
ADD COLUMN customer_id BIGINT
GENERATED ALWAYS AS (CAST(json_col->>'$.customer_id' AS UNSIGNED)) VIRTUAL,
ADD INDEX idx_customer_id (customer_id);
- NULL 处理：如果路径不存在，生成列值为 NULL，索引会记录 NULL，查询时需注意
IS NULL的语义。
三、方案二：函数索引（MySQL 8.0.13+）
MySQL 8.0.13 引入了函数索引，可以直接对表达式建索引，无需显式定义生成列：
ALTER TABLE orders
ADD INDEX idx_customer_id ((CAST(json_col->>'$.customer_id' AS UNSIGNED)));
底层实现仍然是隐藏的生成列，但语法更简洁。注意表达式必须用括号包裹，且必须是确定性函数。函数索引的局限在于：它只对完全相同的表达式生效，查询写法必须与索引表达式一致。
四、方案三：多值索引（MySQL 8.0.17+）
如果 JSON 字段中存储的是数组，且需要按数组元素过滤，普通生成列只能提取整个数组，无法建有效索引。MySQL 8.0.17 引入的多值索引（Multi-Valued Index） 解决了这个问题：
ALTER TABLE products
ADD INDEX idx_tags ((CAST(json_col->'$.tags' AS CHAR(32) ARRAY)));
多值索引允许一个文档对应多个索引条目，适用于 MEMBER OF、JSON_CONTAINS、JSON_OVERLAPS 等查询：
SELECT * FROM products
WHERE 'electronics' MEMBER OF (json_col->'$.tags');
限制：多值索引只能用于 MEMBER OF、JSON_CONTAINS、JSON_OVERLAPS 三种条件，且数组元素必须是标量。对于更复杂的数组查询，仍需回退到生成列或应用层处理。
五、查询优化：如何让索引真正命中？
建好索引只是第一步，查询写法决定了优化器是否使用索引。以下是面试中常被追问的要点。
1. 表达式必须与索引定义完全一致
如果索引建在 CAST(json_col->>'$.customer_id' AS UNSIGNED) 上，那么查询必须写成：
-- 能命中索引
SELECT * FROM orders
WHERE CAST(json_col->>'$.customer_id' AS UNSIGNED) = 1001;
-- 不能命中索引（类型不一致）
SELECT * FROM orders
WHERE json_col->>'$.customer_id' = '1001';
后者返回字符串，与索引的 UNSIGNED 类型不匹配，优化器无法使用索引。
2. 避免在 JSON 路径上使用函数包裹
-- 索引失效
WHERE JSON_EXTRACT(json_col, '$.customer_id') + 0 = 1001;
-- 索引生效
WHERE CAST(json_col->>'$.customer_id' AS UNSIGNED) = 1001;
3. 使用 EXPLAIN 验证
EXPLAIN SELECT * FROM orders
WHERE CAST(json_col->>'$.customer_id' AS UNSIGNED) = 1001;
关注 key 列是否显示索引名，type 是否为 ref 或 range。如果 type 是 ALL，说明走了全表扫描。
4. 覆盖索引减少回表
如果查询只需要 JSON 中的少数几个字段，可以把这些字段都做成生成列，并建立联合索引，实现覆盖索引：
ALTER TABLE orders
ADD COLUMN customer_id BIGINT GENERATED ALWAYS AS (CAST(json_col->>'$.customer_id' AS UNSIGNED)) VIRTUAL,
ADD COLUMN status VARCHAR(20) GENERATED ALWAYS AS (json_col->>'$.status') VIRTUAL,
ADD INDEX idx_cust_status (customer_id, status);
这样查询 SELECT customer_id, status FROM orders WHERE customer_id = 1001 可以直接从索引返回，无需回表。
5. 注意 JSON 路径中的通配符
$.items[*].price 这类通配符路径无法用于生成列索引，因为结果不是标量。如果业务需要按数组内元素过滤，应使用多值索引，或将数据模型改为关系表。
六、面试常见追问与回答要点
追问 1：JSON 字段能不能直接建索引？
不能。必须通过生成列或函数索引将 JSON 路径提取为标量。多值索引是例外，它针对数组元素建索引，但仅支持特定查询条件。
追问 2：VIRTUAL 生成列和 STORED 生成列在索引上有什么区别？
两者都能建索引。VIRTUAL 列不占磁盘，索引存储的是计算后的值；STORED 列会持久化值，读取时不需要计算。对于只用于索引的列，VIRTUAL 更节省空间，且加列时通常不需要重建表。
追问 3：为什么我的 JSON 查询明明建了索引却还是全表扫描？
常见原因：查询表达式与索引表达式不一致（类型转换、函数包裹）、使用了通配符路径、或者优化器认为全表扫描成本更低（数据量小或选择性差）。用 EXPLAIN 确认，并检查查询写法。
追问 4：多值索引和普通索引能同时用吗？
可以。多值索引针对数组元素，普通索引针对标量路径，两者互不冲突。但多值索引的查询条件受限，复杂场景仍需结合生成列。
七、总结
MySQL 中 JSON 字段的索引方案可以归纳为三条路径：生成列 + 普通索引（兼容性最好）、函数索引（8.0.13+ 语法简洁）、多值索引（8.0.17+ 针对数组）。查询优化的核心是保证查询表达式与索引表达式完全一致，避免隐式类型转换和函数包裹，并用 EXPLAIN 验证执行计划。在面试中，如果能进一步指出 VIRTUAL 与 STORED 的取舍、多值索引的限制、以及覆盖索引的用法，就能体现出对 MySQL 优化器行为的深入理解。
未经允许不得转载：任鹏个人博客 » MySQL 中的 JSON 字段索引方案与查询优化
