JSON 路径索引 怎样减少文档扫描

MySQL 的 JSON 路径过滤要减少文档扫描，应把高频查询的固定标量路径变成可搜索的索引键，并让查询使用相同的路径、返回类型和比较语义。MySQL 8.4 可以在 JSON_VALUE(... RETURNING ...) 表达式上建立函数索引；也可以把路径值提取为生成列，再为生成列建索引。普通 JSON 列不会因为保存了二进制 JSON 就自动拥有每个路径的搜索索引。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html。本文沿用“背景、旧写法、新规则、代码对照、兼容处理、采用建议”的解释顺序，讨论固定客户编号路径的等值查询；SQL 是未执行的原创示例，没有实测耗时或扫描行数。

背景：只查一个客户编号却读取大量文档

假设订单表的 payload 包含 customer_id、地区等扩展属性，接口经常根据客户编号检索订单。业务只想找到某个编号的少量记录，如果没有对应访问路径，数据库可能需要读取大量行并计算 JSON 表达式。这里“减少文档扫描”具体指缩小候选行范围，不代表对任意 JSON 内容建立全文检索。

优先挑选路径稳定、值类型稳定、过滤后结果相对少的字段。客户编号适合本例，是因为它是确定的数字标量；任意深度搜索、数组任意成员和全文关键词都不是同一类问题。不要把一个路径索引理解为给整份文档的所有键建索引。

旧写法的问题是缺少可搜索的标量索引

-- 在独立示例库建表；payload 保存订单扩展属性。
CREATE TABLE order_documents (
    id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    payload JSON NOT NULL
) ENGINE=InnoDB;

-- 示例数据只用于说明形态，三行数据不能证明性能收益。
INSERT INTO order_documents(id, payload) VALUES
(101, JSON_OBJECT('customer_id', 4207, 'region', 'east')),
(102, JSON_OBJECT('customer_id', 7812, 'region', 'north')),
(103, JSON_OBJECT('customer_id', 4207, 'region', 'west'));

-- 此时尚未为该表达式建立索引，查看的是优化器估算计划。
EXPLAIN SELECT id
FROM order_documents
WHERE JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED) = 4207;

这个过滤表达式可以表达查询要求，但表上只有主键，没有对应客户编号的索引。主键可以按订单 ID 定位，却不能直接按文档内部编号缩小查找范围。仅把 JSON_EXTRACT 换成另一种提取语法，也不会凭空生成访问结构。

示例故意保持数据量很小，不能拿它的计划选择代表大表行为。业务数据分布、选择性和投影列都会影响优化器决策。先明确需要建立哪一种索引，再在自己的数据环境中判断是否采用。

新规则：固定路径、返回类型和索引表达式

本例使用固定路径 $.customer_id 和 RETURNING UNSIGNED。前者规定取文档中的哪个值，后者使索引键与数值比较采用明确类型。省略返回类型可能引入默认字符串语义；不能因为内容看起来都是数字，就把字符串索引和数值索引视作同一个契约。

路径值不存在、JSON null、对象或无法转换的字符串，也需要明确处理。按文档默认行为，JSON_VALUE 对路径缺失返回 SQL NULL，对部分转换错误也可返回 NULL，但无效 JSON 或路径本身不属于可用 ON ERROR 统一掩盖的情形。实际系统应在写入边界限定客户编号为合法正整数；不能把坏值全部转换成零并继续当正常客户。

用前后 SQL 对照建立路径索引

-- 固定客户编号路径和无符号整数类型，创建函数索引。
ALTER TABLE order_documents
    ADD INDEX ix_customer_path
    ((JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED)));

-- 查询使用相同表达式；不要给数值常量加引号。
EXPLAIN SELECT id
FROM order_documents
WHERE JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED) = 4207;

-- 只有需要完整订单文档时才选择 payload，减少不必要的返回列。
SELECT id, payload
FROM order_documents
WHERE JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED) = 4207;

函数索引的表达式放在双层括号内，这是索引表达式语法的一部分。对照修改前后计划时，关注 possible_keys 是否出现候选索引，以及 key 是否实际选中；只看到 possible_keys 不能声称已经走了索引。

rows 是估计值，不是已运行查询的真实扫描行数。小表或者客户编号覆盖绝大多数行时，全表访问可能比索引访问更便宜，所以不能承诺建完索引必然选择它。本文没有运行 EXPLAIN，也没有伪造固定计划输出。

选择 id 与选择 id, payload 的访问成本不同。在 InnoDB 中，二级索引包含主键信息，但是否能只靠索引满足具体查询仍取决于计划和列需求。即便过滤使用索引，返回完整文档时仍可能需要读取对应记录。

兼容时采用显式生成列并约束数据

-- 替代方案：仅在独立示例表演示，不与上面的函数索引重复叠加。
CREATE TABLE order_documents_named (
    id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    payload JSON NOT NULL,
    customer_id BIGINT UNSIGNED GENERATED ALWAYS AS
        (CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer_id')) AS UNSIGNED)) VIRTUAL,
    INDEX ix_customer_id (customer_id)
) ENGINE=InnoDB;

-- 直接查询生成列，避免依赖优化器识别不同写法的等价表达式。
EXPLAIN SELECT id
FROM order_documents_named
WHERE customer_id = 4207;

生成列方案给索引值一个稳定列名，查询直接使用 customer_id，便于 ORM 表达。它仍要求目标 MySQL 版本和存储引擎支持相应生成列索引；本文示例限定为 InnoDB，不把别的数据库或引擎行为套用进来。

这段替代方案假设路径存在且值可以正确转换为无符号整数。JSON null、无效数字、负数和超长值必须事先制定数据规则，不能认为 CAST 和 JSON_VALUE 在所有异常输入上都完全等价。如果改变 NULL 或错误处理策略，需要同时核对原有查询语义。

优化器对生成列表达式的匹配有表达式与结果类型要求。为了让查询维护更直观，本例直接引用生成列名，不依赖优化器把任意重写自动识别成索引表达式。不要同时为同一路径无目的地叠加两种索引。

采用建议：先看选择性，再衡量维护成本

为现有大表增加索引还涉及构建耗时、空间和元数据锁等执行条件，应按已有变更流程安排。本文 SQL 的建表对象是独立演示表，不建议把示例名称直接替换成生产大表后立即执行。

有索引但 key 仍为空怎么办？

先比较路径、返回类型、比较常量类型和实际筛选比例。不要第一步就使用 FORCE INDEX；强制计划可能掩盖统计与选择性问题。

字符串路径也能这样做吗？

可以按字符串语义设计，但需要额外确定长度、字符集与排序规则，并让查询保持相同约定。不能直接照搬本文数值表达式。

数组搜索能用同一个索引吗？

本文索引针对单个标量路径，不覆盖任意数组成员查询。数组多值索引属于另一种设计，需按具体操作符与版本能力单独处理。

参考资料：JSON_VALUE 与索引示例 https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html；生成列索引的匹配规则 https://dev.mysql.com/doc/refman/8.4/en/generated-column-index-optimizations.html；JSON 间接索引 https://dev.mysql.com/doc/refman/8.4/en/create-table-secondary-indexes.html。