MySQL JSON 字段索引为什么没有命中

MySQL JSON 字段已经建了生成列索引，查询仍然走全表扫描时，问题通常不在“JSON 不能建索引”，而在查询表达式没有和生成列定义匹配。优化器要求表达式本身一致，结果类型也要一致；字符串路径还要特别处理 JSON_UNQUOTE()。

先把 JSON 路径固定成生成列，再用 EXPLAIN 看 possible_keys 和 key。如果生成列用的是 JSON_UNQUOTE(JSON_EXTRACT(...))，查询也不要随意改写、隐式转换或换成另一种结果类型。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/generated-column-index-optimizations.html

先确认索引匹配的两个条件

MySQL 可以在查询没有直接写出生成列名称时使用它的索引，但前提是 WHERE、ORDER BY 或 GROUP BY 中的表达式，能够匹配某个已索引生成列的定义。这里有两个容易被忽略的条件：

• 表达式要相同。生成列写成 f1 + 1 时，查询写成 1 + f1，不能按等价数学表达式处理。
• 结果类型要相同。整数表达式和字符串比较不能依赖隐式转换来“猜中”索引。

因此，判断问题时不要只看索引是否存在，要把生成列定义和实际查询放在一起比较。基线现象可以先记录为：EXPLAIN 的 possible_keys 为空，或虽然列出索引但 key 仍为 NULL。

用生成列把 JSON 路径变成可索引字段

以订单表中的客户名称为例，先固定 JSON 路径，再明确生成列的字符串类型。STORED 让生成值作为表的一部分保存，索引直接建立在生成列上。

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  payload JSON NOT NULL,
  customer_name VARCHAR(128)
    GENERATED ALWAYS AS (
      JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer.name'))
    ) STORED,
  INDEX idx_orders_customer_name (customer_name)
);

-- 查询表达式保留同一条 JSON 路径，避免临时 CAST 改变结果类型
EXPLAIN
SELECT id
FROM orders
WHERE JSON_EXTRACT(payload, '$.customer.name') = 'Alice';

官方文档特别提醒：JSON 函数返回的字符串可能带引号。把 JSON_UNQUOTE() 放在生成列定义里，优化器既能识别直接使用 JSON_EXTRACT() 的比较，也能识别同样显式调用 JSON_UNQUOTE() 的比较；如果生成列没有去掉引号，字符串索引匹配会更窄。

用 EXPLAIN 判断为什么没有命中

不要把“查询结果正确”当成“索引命中”。先看候选索引，再看最终选择：

-- possible_keys 表示优化器认为可能使用的索引，key 表示最终选择
EXPLAIN
SELECT id
FROM orders
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer.name')) = 'Alice';

-- extended EXPLAIN 可帮助观察表达式是否被改写为生成列
SHOW WARNINGS;

如果 possible_keys 中出现 idx_orders_customer_name，但 key 仍为空，说明索引是候选项却没有被选中，还要结合数据分布和其他索引判断。若两个字段都为空，优先回到生成列表达式与查询表达式逐字比较。

四类常见不命中原因

官方文档列出的可用操作符包括 =、、、>、>=、BETWEEN 和 IN()，但 BETWEEN、IN() 与 JSON 值比较仍有额外边界。若优化器选择了不合适的索引，可以再评估索引提示；不要把提示当成表达式不匹配的修复。

最后按这个顺序收敛问题

1. 从 SHOW CREATE TABLE orders 复制生成列的真实定义，不凭记忆重写。
2. 比较 JSON 路径、函数嵌套、运算顺序、返回类型和比较字面量。
3. 运行 EXPLAIN，分别记录 possible_keys 与 key。
4. 只有在表达式匹配后，才继续判断选择性、其他索引和索引提示。

一句话总结：JSON 索引命中依赖“生成列定义和查询表达式的可识别匹配”，不是只要给 JSON 列旁边加一个索引就能生效。对字符串路径，优先把 JSON_UNQUOTE() 固定在生成列定义中，再用 EXPLAIN 验证候选与最终选择。

相关问题

为什么查询结果对，但 key 还是 NULL？

结果正确只说明条件语义正确，不代表优化器选择该索引。先检查表达式和结果类型，再考虑选择性或其他索引成本。

必须在 WHERE 中直接写生成列名吗？

不一定。只要查询表达式与已索引生成列的定义匹配，MySQL 可以在没有直接引用生成列名称时考虑该索引。