MySQL 生成列索引为什么没有被优化器使用

MySQL 生成列已经建好索引，查询却仍然走全表扫描，最常见的原因不是“优化器忽略了索引”，而是查询里的表达式没有满足生成列索引的匹配条件：表达式要与生成列定义一致，结果类型也要一致。比如生成列写的是 f1 + 1，查询改成 1 + f1，数学结果相同，优化器也可能不把它们视为同一个表达式。

先确认生成列和索引确实绑定在一起

生成列索引能被优化器考虑的前提，是生成列由包含函数或运算符的表达式计算，并且该列上确实存在索引。下面的结构与 MySQL 8.4 手册示例一致：gc 保存 f1 + 1 的结果，索引建在 gc 上。

CREATE TABLE t1 (
  f1 INT,
  -- 生成列保存可被索引的计算结果
  gc INT AS (f1 + 1) STORED,
  -- 索引必须建在生成列 gc 上
  INDEX idx_gc (gc)
);

-- 直接引用生成列时，优化器可以考虑 idx_gc
SELECT * FROM t1 WHERE gc > 9;

如果生成列只是简单引用另一列，例如 gc INT AS (f1) STORED，这类索引不属于本题讨论的表达式替换场景。先用 SHOW CREATE TABLE t1 核对真实定义，再继续排查查询语句。

查询表达式必须和生成列定义对得上

MySQL 允许查询不直接写出生成列名称，只要 WHERE、ORDER BY 或 GROUP BY 中的表达式与生成列定义匹配。例如：

-- 表达式与 gc 的定义完全一致，可进入生成列索引匹配
EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9;

-- 观察候选索引和最终使用的索引
SHOW WARNINGS;

排查时重点看三件事：运算顺序是否改变，比较值是否被写成了不同结果类型，以及操作符是否在支持范围内。等号、大小比较、BETWEEN 和 IN() 等有对应规则；不要先把所有“看起来等价”的写法都当成可匹配。

文档示例的 EXPLAIN 结果里，possible_keys 和 key 都出现生成列索引名，说明索引至少进入了计划构造。若 possible_keys 为空，优先修表达式匹配；若候选存在但 key 不是它，再考虑选择性、成本或索引提示。

JSON 字符串查询要检查 JSON_UNQUOTE

JSON 是另一个容易误判的地方。若生成列从 JSON 文档中提取字符串，推荐在定义中去掉 JSON 返回值外层的引号：

CREATE TABLE customer_profile (
  id BIGINT PRIMARY KEY,
  jdoc JSON,
  -- 去掉 JSON_EXTRACT 返回字符串时的额外引号
  doc_name VARCHAR(100) AS (
    JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))
  ) STORED,
  INDEX idx_doc_name (doc_name)
);

-- 查询表达式可与生成列定义对应
SELECT id FROM customer_profile
WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'Alice';

如果生成列定义只写 JSON_EXTRACT(jdoc, '$.name')，字符串比较时可能因为引号语义导致索引匹配受限。不要只把索引从普通索引改成联合索引；先统一 JSON 函数、返回类型和查询表达式。

用 EXPLAIN 把问题分成两类

这一区分很重要：第一种是“优化器没认出这个生成列索引”，第二种是“优化器认出了，但判断别的计划更便宜”。两者的修复动作完全不同。

相关问题

生成列索引一定会被使用吗？

不一定。表达式匹配只代表索引进入候选计划，最终仍由优化器根据成本选择。

f1 + 1 和 1 + f1 一样吗？

数学结果可能一样，但生成列索引匹配要求表达式一致，不能依赖这种交换顺序。

怎么确认 MySQL 是否重写到了生成列？

先看 EXPLAIN 的 possible_keys 和 key，再执行 SHOW WARNINGS 查看扩展 EXPLAIN 给出的重写提示。

完整条件可参考 MySQL 8.4 Optimizer Use of Generated Column Indexes。遇到“索引不生效”时，先判断表达式是否匹配，再判断成本模型是否选择它，定位会比盲目加索引更快。