MySQL 函数索引怎样给 JSON 路径建立可用索引

我第一次给订单表里的 JSON 用户编号加索引时，索引能成功创建，查询却仍然走全表扫描。后来把问题缩小到一个细节：建索引时的表达式返回了什么类型，WHERE 中的表达式又返回了什么类型。两边不是同一个表达式契约，MySQL 就不一定把它们视为同一条索引路径。

MySQL 官方地址：https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html

现在处理这类需求，我会先把 JSON 路径收敛成一个可索引的标量类型，再让建索引表达式和查询表达式保持一致。MySQL 8.4 可以用 JSON_VALUE() 配合 RETURNING 直接建立函数索引；如果必须使用 JSON_UNQUOTE() 或 CAST()，还要额外留意长度和排序规则。

先把 JSON 路径变成明确的标量

JSON 列不能像普通整数列那样直接建立普通索引。真正进入索引的是从文档中提取出的一个值，例如用户编号、租户编号或状态码。这个值最好在索引定义时就确定为 UNSIGNED、DECIMAL 或有界的 CHAR，不要让索引背着一个无法确定边界的长文本类型。

-- 用一列 JSON 保存业务扩展字段，只把稳定的用户编号提取出来建索引
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  payload JSON NOT NULL
);

-- RETURNING UNSIGNED 把 $.user_id 固定成可比较的数值
CREATE INDEX idx_orders_user_id
  ON orders ((JSON_VALUE(payload, '$.user_id' RETURNING UNSIGNED)));

这里的双层括号不是装饰：外层表示函数索引的 key part，内层包住完整表达式。路径还应该指向标量叶子节点；如果路径落到对象或数组，应该先重新检查数据结构，而不是继续给索引表达式叠加转换。

优先用 JSON_VALUE 建立函数索引

对单值路径来说，JSON_VALUE() 的好处是可以在函数内部写清返回类型。比如用户编号用无符号整数，金额用定点数，日期用日期类型。查询时重复同一个表达式，优化器才有机会把条件和索引 key part 对上。

-- 索引表达式和查询表达式使用完全相同的路径与返回类型
CREATE INDEX idx_orders_user_id
  ON orders ((JSON_VALUE(payload, '$.user_id' RETURNING UNSIGNED)));

-- 用同一个类型契约过滤订单，避免外层再做隐式字符转换
EXPLAIN SELECT id
FROM orders
WHERE JSON_VALUE(payload, '$.user_id' RETURNING UNSIGNED) = 10086;

如果 JSON 中没有这个路径，JSON_VALUE() 默认会得到 SQL NULL。这意味着索引可以容纳“没有用户编号”的记录，但这类记录不会匹配等值条件。若字段缺失应该被视为数据错误，应在写入流程或查询策略中单独处理，而不要把缺失值悄悄当成某个有效编号。

我通常会把三类字段分开决策：用于等值和范围判断的编号、数量选择数字类型；需要金额计算的字段选择合适精度的 DECIMAL；只做短文本等值查询的字段才考虑有界字符类型。返回类型不是为了让 DDL 看起来严格，而是为了让索引和查询有同一个数据契约。

CAST 能解决类型问题，也可能带来排序规则问题

旧项目里常见的写法是 payload->>'$.name'，再配合 CAST 建函数索引。它能把 JSON 解包结果从长文本收窄到一个可索引长度，但还不能只看“索引创建成功”。CAST 产生的字符排序规则，可能和 JSON_UNQUOTE() 的排序规则不同；查询表达式与索引表达式因此不一致，索引就可能不被使用。

-- 为字符串路径给出明确长度，并显式指定与查询侧一致的排序规则
CREATE INDEX idx_orders_name
  ON orders ((
    CAST(payload->>'$.name' AS CHAR(64)) COLLATE utf8mb4_bin
  ));

-- 查询侧也保持相同的路径语义，避免混用另一套转换表达式
EXPLAIN SELECT id
FROM orders
WHERE payload->>'$.name' = 'James';

如果业务并不需要兼容旧写法，我更倾向于使用 JSON_VALUE(payload, '$.name' RETURNING CHAR(64))，并把字符集、排序规则作为数据模型的一部分明确下来。对于大小写是否敏感、前后空格是否有意义，也应该先定规则再建索引，否则同一个名字在“有索引”和“无索引”时可能出现令人困惑的比较结果。

用 EXPLAIN 区分表达式问题和选择性问题

看到 type: ALL 时，我不会马上断定索引定义错误。先确认三件事：索引是否建在预期表上，WHERE 的表达式是否和索引表达式一致，样例数据中的值是否真的有足够选择性。表达式不匹配和优化器认为全表更便宜，处理方式并不一样。

-- 先只观察访问路径，不把 EXPLAIN 结果当成业务查询结果
EXPLAIN FORMAT=JSON
SELECT id
FROM orders
WHERE JSON_VALUE(payload, '$.user_id' RETURNING UNSIGNED) = 10086;

-- 用 SHOW CREATE TABLE 核对隐藏函数索引对应的表达式定义
SHOW CREATE TABLE orders;

如果索引表达式使用了 CAST、COLLATE 或不同的 JSON 函数，建议先把查询改成与 DDL 完全相同的形式，再看执行计划是否变化。若表达式已经一致但仍不选索引，再考虑值分布、统计信息和结果集大小，而不是继续添加重复索引。

版本或兼容性受限时用有类型的生成列

函数索引并不是唯一方案。在需要让旧版本客户端看见一个明确列名、或者团队希望把提取逻辑作为表结构的一部分管理时，可以使用生成列再建普通索引。关键仍然是生成列的数据类型要明确，索引和查询围绕这个列名展开。

-- 用有明确类型的生成列承接 JSON 路径提取结果
ALTER TABLE orders
  ADD COLUMN user_id BIGINT
    GENERATED ALWAYS AS (
      JSON_VALUE(payload, '$.user_id' RETURNING UNSIGNED)
    ) STORED,
  ADD INDEX idx_orders_user_id_generated (user_id);

-- 查询直接使用生成列，让表达式契约集中在表定义中
EXPLAIN SELECT id
FROM orders
WHERE user_id = 10086;

生成列会增加写入和更新时的维护成本，也会占用额外存储；函数索引同样不是零成本方案，因为索引项需要随着 JSON 文档变化而更新。只给真实过滤条件建立索引，并为路径缺失、类型错误和大小写规则准备边界样例，通常比为每个 JSON 字段都建索引更稳妥。

我现在使用的一张判断清单

1. 路径是否指向标量叶子，而不是对象或数组？
2. 索引表达式是否显式确定了数字、日期或有界字符类型？
3. CREATE INDEX 和 WHERE 是否使用相同的函数、路径、类型和排序规则？
4. 路径缺失、JSON null 和转换失败是否有不同的业务处理？
5. EXPLAIN 显示未使用索引时，究竟是表达式不匹配还是选择性不足？
6. 是否真的需要索引这个字段，能否接受写入维护和索引空间成本？

如果只记住一个原则，就是“先固定 JSON 路径的结果类型，再复用同一表达式”。JSON_VALUE(... RETURNING ...) 通常是最短的实现路径；需要兼容旧写法时，再把 CAST 的长度和 COLLATE 一并纳入设计。

相关问题

JSON 列可以直接建立普通索引吗？ 不能把整个 JSON 文档当作普通标量列直接索引；应先提取稳定路径，使用函数索引或生成列。

函数索引建成功就一定会被使用吗？ 不一定。查询表达式、类型、排序规则和数据选择性都会影响优化器的决定。

为什么 JSON_UNQUOTE 后还要 CAST？ JSON_UNQUOTE 可能得到无法直接建立无前缀索引的长文本类型，CAST 可以把它收窄为有界字符，但要同步处理排序规则。

路径缺失应该填 0 吗？ 只有当业务明确把缺失等同于 0 时才这样做。编号、金额和状态码通常应把缺失保留为 NULL 或在数据入口报错。