MySQL 函数索引表达式不一致为什么不命中

MySQL 函数索引已经创建，但查询仍然走全表扫描，最常见的原因不是“索引失效”，而是索引保存的表达式和 WHERE 中实际写出的表达式不是同一个结构。函数名、参数顺序、JSON 路径、CAST 类型或隐式转换只要发生变化，优化器就可能无法把查询谓词映射到这个索引。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/create-index.html

排查时先用 SHOW CREATE TABLE 看索引表达式，再让 WHERE 中的表达式与索引定义保持同构，最后用 EXPLAIN 的 key、key_len 和 rows 判断计划是否已经改变。不要先盲目重建同一个索引。

先确认索引实际记录的表达式

函数索引的表达式必须写在额外的一层括号中。下面的索引把 JSON 文档里的 status 提取出来并转换为字符类型；它记录的是完整表达式，而不只是 payload 这一个列。

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    payload JSON NOT NULL,
    -- 业务状态放在 JSON 文档中，查询时统一转成字符值
    KEY idx_order_status ((CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status')) AS CHAR(16))))
);

-- 先读取服务器保存的索引定义，不凭记忆猜表达式
SHOW CREATE TABLE orders;

如果线上表使用的是 CREATE INDEX，也要查看生成后的表定义。函数索引在 MySQL 中以隐藏的虚拟生成列实现，因此它仍然受生成列表达式限制；不能把子查询、变量或存储函数塞进索引表达式。

让查询表达式与索引表达式保持同构

下面这条查询保留了索引定义中的函数顺序、JSON 路径和字符类型，优化器才有机会把谓词识别为该函数索引上的条件。

-- 查询表达式与索引定义保持同样的函数嵌套和路径
SELECT id, payload
FROM orders
WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status')) AS CHAR(16)) = 'paid';

以下写法看起来结果相同，但不应直接假设它们等价于索引表达式：

-- 函数顺序、返回类型或 JSON 路径写法发生变化，可能无法映射到原索引
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status')) = 'paid';
WHERE CAST(payload->>'$.status' AS CHAR(16)) = 'paid';
WHERE JSON_EXTRACT(payload, '$.status') = '"paid"';

排障时建议先固定一种规范写法，把应用 SQL、报表 SQL 和后台查询统一到同一表达式。不要一边保留函数索引，一边让不同调用方自由改写 JSON 操作符。

排除类型、字符集和排序规则差异

表达式文本一致仍不代表比较语义完全一致。字符串返回类型、长度、字符集和排序规则会影响比较结果，也会影响优化器对索引键的判断。特别是 CAST 被省略、长度被改写，或参数从字符串变成数字时，先把类型写回索引定义中的形式。

-- 用与函数索引一致的类型表达边界，避免依赖隐式转换
SELECT id
FROM orders
WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status')) AS CHAR(16) CHARACTER SET utf8mb4) COLLATE utf8mb4_0900_ai_ci = 'paid';

-- 只有在确认现有索引表达式也包含同样的类型与排序规则后，才采用这类写法
SHOW CREATE TABLE orders;

如果应用绑定参数的类型不稳定，先记录实际 SQL 模板和参数类型，再决定是规范查询、重建索引，还是把表达式落成一个显式生成列。重建索引不是修复表达式不一致的第一步。

用 EXPLAIN 判断是否真的命中

确认查询改写后，再用执行计划看证据。possible_keys 表示优化器认为可用的候选索引，key 才是本次计划选择的索引；如果 possible_keys 有值但 key 仍为空，说明候选存在但计划没有采用。

-- 只查看计划，不把 EXPLAIN 输出误当成实际运行耗时
EXPLAIN
SELECT id, payload
FROM orders
WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status')) AS CHAR(16)) = 'paid';

计划仍未采用索引时，按“表达式定义—查询文本—类型语义—统计信息”的顺序检查。不要仅凭 possible_keys 就宣布命中，也不要把静态说明图当作真实执行证据。

保留回滚路径，必要时改用显式生成列

如果多个客户端很难保证表达式完全一致，可以把表达式改成可见生成列，再对列建立普通索引。这样查询直接使用列名，表达式规范集中在表结构中；代价是会增加一个表列，需要同步评估列数限制、写入开销和迁移窗口。

-- 过渡方案：把复杂表达式集中到可见生成列
ALTER TABLE orders
  ADD COLUMN status_text VARCHAR(16)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status'))) STORED,
  ADD INDEX idx_order_status_text (status_text);

-- 查询统一使用列名，保留清晰的回滚对象
SELECT id, payload
FROM orders
WHERE status_text = 'paid';

-- 确认新计划稳定后，再在维护窗口删除旧函数索引
ALTER TABLE orders DROP INDEX idx_order_status;

回滚时先恢复应用查询到旧表达式，确认旧索引仍存在，再删除新索引或生成列。生产变更记录至少包含索引定义、查询模板、EXPLAIN 关键字段和回滚语句。

值班速查：函数索引不命中的处理顺序

1. 用 SHOW CREATE TABLE 记录服务器实际保存的函数表达式。
2. 逐字对照 WHERE 中的函数嵌套、参数顺序、JSON 路径和 CAST 类型。
3. 清理隐式转换、字符集和排序规则差异，统一应用 SQL 模板。
4. 用 EXPLAIN 同时看 possible_keys、key、key_len 和 rows。
5. 多调用方无法统一时，考虑可见生成列，并保留旧索引直到新计划稳定。

相关问题

函数索引一定要写两层括号吗？

是。表达式要用额外括号与普通列名区分，例如 INDEX ((LOWER(name)))；只写成单层列定义会被解析为列或产生语法问题。

possible_keys 有索引但 key 为空，算命中吗？

不算。它只说明进入候选集合，实际采用情况看 key；还要结合估算行数和查询选择性判断是否值得使用。

重建同名函数索引能解决表达式不一致吗？

通常不能。应先明确索引和查询要遵循的表达式，再选择改查询或重建索引，避免在没有定位差异前重复 DDL。