MySQL 生成列索引为什么会受字符排序规则影响

我排查生成列索引时，最容易把两个现象混在一起：一是 utf8mb4_0900_ai_ci 与 utf8mb4_bin 对大小写、重音的比较结果不同；二是查询虽然写了相似表达式，优化器却没有把它改写成生成列索引查找。MySQL 生成列索引不是“只要建了就一定命中”，表达式要一致，结果类型也要一致，字符串的字符集和排序规则则要固定下来。

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

先用 SHOW CREATE TABLE 看生成列的定义，再用 CHARSET()、COLLATION() 检查查询值，最后用 EXPLAIN 判断是否真的走了生成列索引。不要只凭“查询返回了正确行”判断索引生效。

先区分排序规则变化和索引未命中

排序规则不是索引开关。它描述字符串比较、排序和等值判断的规则；例如不区分大小写的排序规则可能把 Abc 与 abc 看成相等，而二进制排序规则会按编码值区分它们。索引是否被采用，则还要看优化器能否把查询表达式识别为生成列定义。

这两个问题的排查顺序应该分开：先比较结果语义，再看执行计划。生成列定义为 JSON_UNQUOTE(JSON_EXTRACT(doc, '$.alias')) 时，查询若换成不同的函数顺序、不同的返回类型，或者只写了一个看似等价但实际属性不同的表达式，优化器可能无法匹配。此时即使两个写法在少量数据上返回相同结果，也不能证明索引已经被使用。

为生成列固定字符集与排序规则

下面用 JSON 中的 alias 作为示例。重点不是 JSON 本身，而是把生成列最终暴露给索引的字符串类型写清楚。表默认值可以作为兜底，但关键列不建议依赖数据库或连接的隐式默认值。

-- 显式固定生成列的字符集和排序规则，避免依赖表默认值
CREATE TABLE customer_profile (
    id BIGINT PRIMARY KEY,
    doc JSON NOT NULL,
    alias_key VARCHAR(64)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_ai_ci
        AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$.alias'))) STORED,
    INDEX idx_alias_key (alias_key)
);

-- 用一个包含大小写差异的值，观察当前排序规则下的比较语义
INSERT INTO customer_profile (id, doc)
VALUES (1, JSON_OBJECT('alias', 'Abc'));

这里使用 STORED 让生成值保存下来，索引直接建立在 alias_key 上。若业务需要区分大小写，应改成适合业务的二进制或区分大小写排序规则，并同步修改所有查询；不能只改查询常量而保留旧索引语义。

检查查询常量与表达式属性

连接字符集和排序规则会影响字符串字面量。排查时先把表定义和列属性打印出来，再用一个明确的查询值对照。CHARSET() 与 COLLATION() 只负责告诉你表达式当前拥有什么属性，不会替你修正不一致。

-- 先确认生成列和索引看到的真实定义
SHOW CREATE TABLE customer_profile;

-- 检查列表达式的字符集与排序规则
SELECT
    CHARSET(alias_key) AS key_charset,
    COLLATION(alias_key) AS key_collation
FROM customer_profile
LIMIT 1;

-- 显式让查询常量采用与生成列一致的字符属性
SELECT id
FROM customer_profile
WHERE alias_key = CONVERT('abc' USING utf8mb4)
                 COLLATE utf8mb4_0900_ai_ci;

如果业务语义是大小写不敏感，第一条查询可能返回插入的 Abc；如果切换到 utf8mb4_bin，结果就可能不同。迁移中最危险的不是某一个排序规则“更快”，而是写入、索引定义和读取端对相等的理解不一致。

用 EXPLAIN 判断生成列索引是否可匹配

MySQL 官方文档说明，优化器会在查询表达式与生成列定义一致、并且结果类型相同的情况下考虑生成列索引。等值、范围、BETWEEN 和 IN() 等操作还各有匹配限制，所以要看计划而不是猜。

-- 直接引用生成列，先建立一个最清晰的基线计划
EXPLAIN SELECT id
FROM customer_profile
WHERE alias_key = CONVERT('abc' USING utf8mb4)
                 COLLATE utf8mb4_0900_ai_ci;

-- JSON 提取表达式要与生成列定义保持同样的结构
EXPLAIN SELECT id
FROM customer_profile
WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.alias')) =
      CONVERT('abc' USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;

-- 查看优化器是否把表达式替换成了生成列
SHOW WARNINGS;

观察结果时关注 possible_keys、key 和扩展 EXPLAIN 的改写信息。若 possible_keys 没有 idx_alias_key，优先检查函数结构、返回类型和字符属性；若候选索引存在但 key 为空，再结合选择性、统计信息和其他索引判断成本，而不是继续修改 COLLATE。

把迁移和回归边界写进清单

生产修改前可以按下面的顺序留证：第一，保存 SHOW CREATE TABLE，确认生成列的字符集、排序规则和 STORED/VIRTUAL 属性；第二，为大小写、重音、空字符串和 NULL 准备最小数据集；第三，在应用连接池的真实字符集设置下执行参数化查询；第四，对直接引用生成列和重复表达式分别跑 EXPLAIN。

一句话总结：字符排序规则决定字符串怎么比较，表达式一致性决定生成列索引能否被优化器识别。把两条线分别验证，再把字符属性写进列定义和回归用例，问题就不会停留在“索引明明存在却没生效”的猜测上。

常见问题

只在查询里加 COLLATE，能修复生成列索引不命中吗？

不一定。它可能修正比较语义，但如果函数结构或结果类型仍与生成列定义不同，优化器依然可能无法匹配。应先对照 SHOW CREATE TABLE 和 EXPLAIN。

为什么 JSON_EXTRACT 后通常还要 JSON_UNQUOTE？

JSON_EXTRACT 返回的字符串带 JSON 引号语义，直接用于索引查找时可能和普通字符串比较的表达式不同。把 JSON_UNQUOTE 写进生成列定义，可以让索引键与字符串查询更直接地对齐。

生成列改了排序规则后要不要重建索引？

如果列定义或结果属性发生变化，应按 DDL 变更方案重建或重新生成相关索引，并重新跑大小写、重音和执行计划回归；不要假设旧索引自动具备新语义。