MySQL生成列配合函数索引

MySQL 需要按函数结果过滤时，可以把函数表达式放进生成列，再给生成列建立普通索引。这样既能把复杂表达式集中管理，也能让查询条件有稳定的索引入口。常见场景包括邮箱统一小写、JSON 字段取出字符串、日期截取和状态归一化。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/create-table-generated-columns.html

最小可用原则是：先确定函数表达式和结果类型，再决定生成列是 VIRTUAL 还是 STORED，最后让查询中的表达式、字符集和排序规则与索引定义保持一致。JSON 场景还要显式处理引号和可索引的数据类型。

先把函数表达式落到生成列

假设用户邮箱的大小写不应该影响检索，可以先把 LOWER(email) 固定成生成列，再在这个列上建索引。生成列的定义就是索引语义的源头，不要让每个查询各自拼接一套大小写转换规则。

CREATE TABLE account (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    -- 将邮箱统一成小写，作为可复用的索引值。
    email_key VARCHAR(255)
        GENERATED ALWAYS AS (LOWER(email)) STORED,
    -- 索引绑定生成列，而不是重复书写函数。
    INDEX email_func_idx (email_key)
);

-- 查询表达式与生成列定义保持相同的函数形状。
SELECT id, email
FROM account
WHERE LOWER(email) = 'alice@example.com';

STORED 表示结果在插入或更新时计算并保存；VIRTUAL 则不保存列值，读取时计算。两者都可以建立二级索引，但索引本身仍然需要占用存储空间。生成列表达式应使用允许的确定性函数，不能依赖子查询、变量或存储函数。

根据读写成本选择 VIRTUAL 或 STORED

选择方式可以先按“计算放在哪里”判断：VIRTUAL 把计算推迟到读取时，节省生成列本身的存储；STORED 在写入或更新时计算并保存，适合表达式较重、读取频繁且希望把结果稳定落盘的场景。

ALTER TABLE account
    ADD COLUMN email_key_virtual VARCHAR(255)
        GENERATED ALWAYS AS (LOWER(email)) VIRTUAL,
    ADD INDEX email_virtual_idx (email_key_virtual);

-- STORED 版本会保存计算结果，适合读取量更高的派生值。
ALTER TABLE account
    ADD COLUMN email_key_stored VARCHAR(255)
        GENERATED ALWAYS AS (LOWER(email)) STORED,
    ADD INDEX email_stored_idx (email_key_stored);

真实表里不要同时保留两个仅为对比而存在的派生列。先根据读写比例、行宽和表达式复杂度选一种，再用实际查询计划与写入负担做决定。若只是为了让多个查询复用同一个条件，VIRTUAL 往往更简洁；若计算代价明显且读取很多，STORED 更容易把成本前移。

让查询表达式与索引表达式保持一致

生成列索引解决的是“把某个表达式变成可索引值”，不是让优化器自动证明所有数学等价式。查询中最好复用与定义相同的函数、参数、结果类型和排序规则。尤其不要把大小写转换、字符串截断和隐式类型转换散落在不同业务代码里。

-- 推荐：函数、参数和比较值类型都与索引定义一致。
EXPLAIN SELECT id FROM account
WHERE LOWER(email) = 'alice@example.com';

-- 风险：换成 UPPER 后，不能默认复用 LOWER(email) 的索引。
EXPLAIN SELECT id FROM account
WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';

-- 风险：显式转换或不同排序规则可能改变匹配条件。
EXPLAIN SELECT id FROM account
WHERE LOWER(email) COLLATE utf8mb4_bin = 'alice@example.com';

MySQL 也支持直接把表达式写进函数索引，例如 INDEX ((LOWER(email)))。但生成列方式更容易被应用层显式引用、查看和迁移，适合需要把派生值作为稳定接口的表。无论选哪一种，表达式限制、确定性要求以及索引可见性都要按当前版本文档处理。

处理 JSON 函数的类型边界

JSON 是最容易把“函数写对了”误认为“索引一定能用”的场景。JSON_EXTRACT 得到的是 JSON 值；取字符串时通常需要 JSON_UNQUOTE 去掉 JSON 字符串外层引号。若结果仍被推断为不适合直接建索引的长文本，还应通过 CAST 限定为明确的字符类型。

CREATE TABLE user_profile (
    id BIGINT PRIMARY KEY,
    profile JSON NOT NULL,
    -- 先取出 JSON 字符串，再限定可索引的字符类型。
    profile_name VARCHAR(80)
        GENERATED ALWAYS AS (
            CAST(JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) AS CHAR(80))
        ) STORED,
    -- 索引承接明确的 VARCHAR 结果，而不是未限定的 JSON/LONGTEXT 结果。
    INDEX profile_name_idx (profile_name)
);

-- 查询侧也保持相同的取值和字符串语义。
SELECT id
FROM user_profile
WHERE JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) = 'Alice';

还要检查字符集和排序规则。索引表达式和查询表达式的字符串属性不一致时，优化器可能无法把它们视为同一条件。JSON 路径、JSON_UNQUOTE、CAST 的数据类型和比较字面量应当作为一组契约维护。

用 EXPLAIN 区分结构问题和成本选择

看到函数条件没有走索引时，不要直接重建表。先把问题拆成三层：有没有候选索引、最终选中了什么、优化器有没有识别到生成列表达式。

-- possible_keys 用来观察生成列索引是否进入候选集合。
EXPLAIN SELECT id FROM account
WHERE LOWER(email) = 'alice@example.com';

-- 扩展信息用于观察条件是否被改写为生成列相关条件。
EXPLAIN EXTENDED SELECT id FROM account
WHERE LOWER(email) = 'alice@example.com';
SHOW WARNINGS;

如果只是想确认索引是否具备候选资格，可以做一次受控的索引提示对照；但提示不能修复表达式或类型不匹配。最终方案仍应回到统一的生成列定义、统一的查询写法和可解释的索引结构。

常见问题

生成列和函数索引是不是同一件事？

目标相近，都是让函数结果可被索引。生成列把表达式显式保存为表结构的一部分；函数索引则直接把表达式作为索引键的一部分。需要被查询、查看或迁移的派生值，生成列通常更直观。

VIRTUAL 生成列能不能建索引？

在支持的存储引擎和索引类型下可以建立二级索引，但虚拟列本身不保存列值，仍要评估计算和维护成本。

为什么 JSON_EXTRACT 取到字符串后还要 JSON_UNQUOTE？

因为 JSON 字符串带有 JSON 语义和外层引号，普通 SQL 字符串比较需要先统一字符串表示；再根据索引类型需要决定是否用 CAST 限定字符长度与类型。

EXPLAIN 的 key 为空就代表生成列写错了吗？

不一定。先看 possible_keys：没有候选时优先检查表达式和类型；有候选但没有最终选中，还可能是成本估算选择了别的路径。