MySQL 8.4 生成列索引优化怎样判断是否命中

我最近排查一条 JSON 条件查询时，发现表上明明有生成列索引，EXPLAIN 却没有把它放进实际执行计划。这个问题通常不是“索引完全失效”，而是查询表达式没有和生成列定义精确匹配。

MySQL 8.4 的判断方法很直接：先看 EXPLAIN 的 possible_keys 和 key，再用 SHOW WARNINGS 查看优化器是否把原表达式改写成生成列。官方资料入口：

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

看到索引名出现在 possible_keys 只能说明它有机会被使用；只有 key 真的选中该索引，才算当前计划命中。若两列都为空，优先检查表达式是否逐字等价、结果类型是否一致，以及比较运算符是否在支持范围内。

先把生成列表达式固定下来

先用一个最小表表达订单金额筛选场景。这里把 amount + 1 保存为生成列，并为生成列建立索引。实际项目中也可以选择 VIRTUAL，但需要确认存储引擎和索引方案；STORED 会保存计算值，同时索引也会占用空间。

-- 生成列保存稳定表达式，并为它创建索引
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    amount DECIMAL(12, 2) NOT NULL,
    amount_plus_one DECIMAL(12, 2)
        GENERATED ALWAYS AS (amount + 1) STORED,
    INDEX idx_amount_plus_one (amount_plus_one)
);

-- 让查询表达式与生成列定义保持同一计算形式
EXPLAIN SELECT id, amount
FROM orders
WHERE amount + 1 > 100;

不要先把生成列写成一个表达式、再在查询里随意改写。官方规则要求表达式本身相同，并且结果类型也要相同；例如 amount + 1 与 1 + amount 不应当当成完全等价的匹配写法。

EXPLAIN 要同时看 possible_keys 和 key

我判断是否命中时不会只看一列。possible_keys 表示优化器认为可能有用的索引，key 表示当前计划实际选择的索引。下面是阅读重点：

-- 先观察候选索引与实际索引，不要只看 rows
EXPLAIN SELECT id, amount
FROM orders
WHERE amount + 1 > 100;

-- 扩展信息可以帮助判断优化器如何理解这条条件
SHOW WARNINGS;

如果结果类似 possible_keys: idx_amount_plus_one、key: idx_amount_plus_one，说明索引既可用又被当前计划采用。若只有 possible_keys 有值，代表索引是候选项，但成本模型选择了别的路径；若两者都为空，则应先回到表达式匹配问题。

SHOW WARNINGS 能揭示有没有改写

MySQL 8.4 在识别到匹配的生成列索引时，可能把查询中的表达式理解为对生成列的引用。执行 EXPLAIN 后紧接着执行 SHOW WARNINGS，可以看到扩展的改写说明。

-- EXPLAIN 之后立刻查看优化器给出的扩展说明
EXPLAIN SELECT id
FROM orders
WHERE amount + 1 > 100;

-- 注意：SHOW WARNINGS 读取的是上一条语句的扩展信息
SHOW WARNINGS;

阅读时重点确认条件是否出现了生成列名，例如 amount_plus_one > 100。这比只看一段估算行数更容易解释“为什么选中”或“为什么没有识别”。不过执行计划仍然受数据分布、成本估算和其他索引影响，所以“识别到生成列”与“最终选择该索引”是两个不同问题。

这四类差异最容易让索引不命中

1. 运算顺序不同

生成列定义为 amount + 1 时，查询最好也写成 amount + 1。不要把 1 + amount 当成可以自动归一化的同一表达式。

2. 比较值改变了结果类型

表达式匹配除了结构相同，还要求结果类型一致。数字表达式不要在关键比较处混入字符串字面量；出现类型转换时，先检查生成列的数据类型和查询参数绑定类型。

3. 运算符不在优化范围

文档列出的常见支持运算符包括 =、、、>、>=、BETWEEN 和 IN()。其中 BETWEEN、IN() 对可替换的位置还有额外限制，不能只凭普通比较的经验推断。

4. JSON 字符串没有去掉引号

如果生成列从 JSON 中提取字符串，定义中通常需要使用 JSON_UNQUOTE(JSON_EXTRACT(...))，否则索引查找的字符串值可能无法按预期匹配。示例写法如下：

-- JSON 字符串索引需要明确去掉 JSON 字符串引号
CREATE TABLE user_docs (
    id BIGINT PRIMARY KEY,
    jdoc JSON NOT NULL,
    doc_name VARCHAR(100)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))
        ) STORED,
    INDEX idx_doc_name (doc_name)
);

-- 查询中的表达式要与生成列保持同一语义
EXPLAIN SELECT id
FROM user_docs
WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'Alice';

最后用一张表做判断

我自己的排查顺序是“先看结构，再看候选，再看实际选择，最后看改写”。这样能把“索引没建好”“表达式没匹配”和“索引可用但成本不划算”分开处理。生成列索引并不是只要存在就会生效，真正可靠的判断必须落到当前查询的执行计划上。

相关问题

生成列一定要使用 STORED 吗？

不一定。MySQL 允许虚拟生成列和存储生成列，但是否可索引、占用多少空间以及读写成本要结合存储引擎和实际场景决定。本文重点是优化器识别表达式索引的条件。

为什么 key 有索引名但查询仍然慢？

命中只说明计划选择了这个索引，不等于所有查询都足够快。还要结合扫描范围、返回行数、回表成本、数据分布和其他过滤条件判断。