MySQL generated column 索引为什么匹配不到等价表达式

MySQL 的 generated column 索引匹配不到“看起来等价”的条件，通常不是索引失效，而是优化器匹配的是表达式形状和结果类型。例如 generated column 定义为 f1 + 1 时，查询写成 1 + f1，即使数学结果相同，也不一定被识别为同一个表达式。

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

先固定 generated column 的表达式和结果类型

我处理这类问题时，第一步不是继续加索引，而是把“索引到底绑定了什么”写出来。下面的定义把 f1 + 1 作为 generated column 的值，并在该列上建立索引：

CREATE TABLE t1 (
    f1 INT NOT NULL,
    -- 把固定表达式保存为可索引的生成列。
    gc INT AS (f1 + 1) STORED,
    -- 索引绑定的是 gc 对应的生成表达式。
    INDEX gc_idx (gc)
);

这时 WHERE f1 + 1 > 9 具备被识别的基础；但 WHERE 1 + f1 > 9 不应因为“数学等价”就默认能用同一索引。比较值的类型也要留意：整数表达式与字符串比较可能让结果类型不一致。

按同一表达式形状改写查询

为了让查询稳定地落到生成列索引上，应用层最好把过滤条件集中成一种写法。不要一部分代码写 f1 + 1，另一部分代码写交换操作数或依赖隐式转换的形式。

-- 推荐：表达式形状和生成列定义保持一致，比较值也是整数。
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9;

-- 风险写法：数学上等价，但不应假设一定匹配 gc_idx。
EXPLAIN SELECT * FROM t1 WHERE 1 + f1 > 9;

-- 风险写法：字符串字面量可能改变比较的结果类型。
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > '9';

这里的原则有点像升级迁移时固定接口契约：不是把每个“等价写法”都交给优化器猜，而是让定义与查询保持同一表达式。MySQL 文档列出的可匹配运算符包括 =、、、>、>=、BETWEEN 和 IN()，但不同运算符还有参数位置和结果类型限制。

处理 JSON 字符串的引号与类型

JSON 是另一个常见陷阱。JSON_EXTRACT 返回 JSON 值；当它代表字符串时，索引表达式可能带着 JSON 字符串的引号。若应用实际用普通 SQL 字符串比较，generated column 定义通常要显式使用 JSON_UNQUOTE，让索引侧的字符串语义更接近查询条件：

CREATE TABLE user_profile (
    jdoc JSON NOT NULL,
    -- 去除 JSON 字符串外层引号，再限定可索引的字符类型。
    user_name VARCHAR(80)
        AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED,
    -- 索引最终绑定 user_name 这个字符串生成列。
    INDEX user_name_idx (user_name)
);

-- 查询路径和生成列的字符串语义保持一致。
EXPLAIN SELECT * FROM user_profile
WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'Alice';

如果定义里没有 JSON_UNQUOTE，某些直接比较 JSON 函数结果的写法可能仍被识别，但普通字符串比较不应想当然地复用该索引。还要注意字符集、排序规则和显式 CAST；只要索引表达式与查询表达式的字符串属性不同，匹配就可能中断。

用 EXPLAIN 和 SHOW WARNINGS 分层判断

看到索引没有被使用时，我会按三层判断，避免把所有问题都归咎于成本模型：

-- 先看候选索引和最终选中的索引。
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9;

-- 扩展信息用于观察优化器是否重写了条件。
SHOW WARNINGS;

如果 possible_keys 没有生成列索引，优先回到表达式一致性；如果有候选但 key 为空，再考虑数据分布、统计信息或其他索引的成本优势。必要时可以用索引提示做一次对照，但不要把提示当作修复表达式契约的替代品。

常见问题

数学等价的表达式为什么不一定共用 generated column 索引？

因为优化器要求查询表达式与生成列定义相同，并且结果类型相同；它不是通用的代数化简器。

直接查询 generated column 名称是不是最稳？

通常更直观，但应用也可以使用与定义匹配的表达式。关键是统一表达式、比较值类型以及 JSON 字符串处理方式。

看到 key 为空就一定是表达式写错了吗？

不一定。先看 possible_keys：没有候选时偏向匹配问题；有候选但未选中时，还可能是优化器基于成本选择了其他路径。