MySQL 函数索引完整实验：表达式匹配与邮箱归一化

索引建在 LOWER(email) 上，查询写成 LOWER(TRIM(email))，就不能仅凭“最后都在查小写邮箱”认定它们匹配同一索引。函数索引保存的是一段明确表达式的结果，优化器不会替应用证明任意两段归一化逻辑等价。

本文把业务规则限定为一个简单约定：示例邮箱使用 ASCII，登录标识按统一小写比较。这里不宣称所有邮件系统都应忽略大小写，也不把更复杂的 Unicode 规范化混进实验。

为表达式准备可以命中的数据

使用 MySQL 8.4、InnoDB。原邮箱列选择区分大小写的排序规则，使“原值比较”和“小写结果比较”能够明确分开。

CREATE DATABASE functional_key_lab
    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;
USE functional_key_lab;

CREATE TABLE member_account (
    id BIGINT NOT NULL PRIMARY KEY,
    email VARCHAR(200) CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_as_cs NOT NULL,
    display_name VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 6000;
INSERT INTO member_account(id, email, display_name)
WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT n, CONCAT('User', LPAD(n, 5, '0'), '@example.test'),
       CONCAT('member-', n)
FROM seq;

先保存没有表达式索引的计划：

EXPLAIN
SELECT id, display_name FROM member_account
WHERE LOWER(email) = 'user00420@example.test';

现在建立函数索引。函数键部分外面需要额外括号；这不是给普通列名随意多加一层装饰。

CREATE INDEX idx_email_lower
ON member_account ((LOWER(email)));
ANALYZE TABLE member_account;

EXPLAIN
SELECT id, display_name FROM member_account
WHERE LOWER(email) = 'user00420@example.test';

查看实际选择的键和读取方式。示例有一个选择性很窄的等值条件，但计划仍以实际实例输出为准。小表或查询返回过多记录时，优化器可以选择其他成本更低的路径，不能把“必须显示这个索引”写成脱离数据的验收要求。

这里的索引让计算结果拥有访问入口，不是让每次查询先对整张表做 LOWER 再神奇加速。判断收益时，应看候选范围和实际读取行数，而不只是最终返回一行。

三种看起来相近的条件，职责并不相同

EXPLAIN
SELECT id FROM member_account
WHERE email = 'user00420@example.test';

EXPLAIN
SELECT id FROM member_account
WHERE LOWER(TRIM(email)) = 'user00420@example.test';

EXPLAIN
SELECT id FROM member_account
WHERE LOWER(email) = LOWER('User00420@Example.Test');

第一条查原值，既没使用同一表达式，比较语义也与示例的区分大小写列有关。第二条增加了 TRIM，处理的是“先去空白再小写”的不同规则。第三条在常量一侧归一化，与前两种改动不是一回事，应单独观察实际计划。

不要为了让一条 SQL 走索引，偷偷删除 TRIM。若历史数据确实含首尾空格，去掉它可能改变结果；正确做法是确定归一化协议、清理历史数据，再让索引和查询共同使用同一规则。

这也是表达式索引常见的维护成本：规则藏在多个 DAO 的 SQL 字符串里，某个接口多包一个函数，就可能失去预期路径。类型转换、排序规则以及表达式结果类型也会影响匹配，不能只比较函数名称。

用可见生成列，把协议从查询字符串中提出来

另一个可维护方案是定义命名生成列，让业务查询显式引用它。在实验中先删除函数索引，再增加生成列索引，避免两套等价入口干扰观察：

DROP INDEX idx_email_lower ON member_account;

ALTER TABLE member_account
ADD COLUMN email_normalized VARCHAR(200)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs
    GENERATED ALWAYS AS (LOWER(email)) VIRTUAL,
ADD INDEX idx_email_normalized (email_normalized);

SELECT id, email, email_normalized
FROM member_account WHERE id = 420;

EXPLAIN
SELECT id, display_name FROM member_account
WHERE email_normalized = 'user00420@example.test';

查询字段名变得明确，应用不用在每处重复表达式。生成列仍受自己的表达式与类型约束影响，不是任意可写的冗余字段；一般不应该让 ORM 对它发送普通业务赋值。

函数索引在内部也与隐藏的虚拟生成列机制相关，但是否把规范化列显式暴露给应用，是一个接口与维护选择。少量固定 SQL 可以使用函数索引；多个模块共享同一业务标识时，命名字段往往更容易统一契约和排查。

这里选择 VIRTUAL，不表示索引没有存储成本。被索引的计算结果仍需要进入索引记录，写入时也要计算和维护。VIRTUAL 省掉的是把结果作为普通表行中的存储值重复保留，不是免掉所有计算与索引空间。

查询优化与唯一性要求要分开决定

普通函数索引或普通生成列索引只提供查找入口，不拒绝两条大小写不同、归一化后相同的邮箱。若登录标识必须唯一，需要明确的唯一约束。

先扫描历史重复：

SELECT email_normalized, COUNT(*) AS duplicate_count
FROM member_account
GROUP BY email_normalized
HAVING COUNT(*) > 1;

本例种子数据没有重复，可以建立唯一键，再去掉普通索引：

CREATE UNIQUE INDEX uk_email_normalized
ON member_account (email_normalized);
DROP INDEX idx_email_normalized ON member_account;

-- 预期失败：与id=420的归一化标识相同。
INSERT INTO member_account(id, email, display_name)
VALUES (6000, 'USER00420@EXAMPLE.TEST', 'duplicate');

若扫描发现重复，不应直接删除“看起来多出来”的账号。它们可能对应不同用户，需要按注册和合并规则处理。数据库负责阻止未来冲突，历史身份处置不能靠索引创建错误自动决定。

新增唯一约束前，也要让所有写入入口采用同一业务规则。预检查完成以后如果仍有旧实例继续写入冲突标识，建索引依然会失败。上线应包含旧版本兼容窗口、历史修正、约束创建与最终验证，不能把一次 GROUP BY 的空结果当作永久保证。

更新入口也必须遵守同一归一化协议

修改邮箱时应更新原始 email，让数据库按定义重新生成规范化结果，而不是把生成列当作另一份可独立编辑的数据。两个字段若允许各自赋值，就又回到了应用双写容易不一致的问题。

唯一索引保护的也只是表达式定义的相等关系。本例 LOWER 不负责清除空白：一个前面多出空格的字符串，可能仍成为另一个规范化结果。它是否应被拒绝，是输入校验和业务标识规则需要明确的决定，不能指望索引猜测人的意图。

因此，产品若随后改变规则，要求忽略首尾空白，必须把这次变化当成数据契约迁移：先找归一化后会合并的历史记录，再决定身份合并与冲突处理，最后切换表达式和查询。直接把函数改成 LOWER(TRIM(email))，既可能让唯一索引重建失败，也可能使原本分别可登录的账号发生碰撞。

失败的表达式不是再加一层括号就能修好

MySQL 对函数键部分的表达式有明确限制，依赖非确定性函数、子查询或不受支持结果类型的表达式，不能被随意索引。比如想按“距离当前时间有多少天”创建随时钟变化的键，就已经超出固定行数据表达式的稳定含义。

另一类问题是返回 TEXT 或 BLOB 这类不适合作为该函数键部分直接使用的结果。需要显式类型设计时，CAST 的长度、字符集和比较语义都应与业务一致。随手截短字符串只是为了让 DDL 成功，可能把不同标识压成同一结果；索引建成了，数据规则却坏了。

因此，DDL 报错应先阅读具体限制、还原表达式结果类型，再决定生成列类型或归一化存储方案。不能把每一种错误都当成优化器“不够聪明”，然后无限叠加提示强制执行。

验证最终接口，而不只验证示例谓词

完成实验后，用实际应用发出的参数类型和 SQL 再检查一次。某些框架会加额外 CAST、COLLATE 或动态条件，手写查询命中索引，并不证明最终请求具备相同表达式。

保留至少三类样本：正常大小写混合标识、应拒绝的归一化重复、历史带空白或异常字符的记录。分别核对查找结果、错误反馈和执行计划。性能调整不能改变原有身份规则，数据清理也不能悄悄混进“加一个索引”的发布里。

查询成本还包括取回列。只查 id 的实验与取整份大对象的接口，回表工作可能不同。若条件命中范围已经很窄，再考虑行宽、缓存和响应序列化，不要把所有耗时都归因于函数索引。

最后保存 SHOW CREATE TABLE，确认归一化表达式、结果类型和唯一键都是当前生效的定义。清理实验库：

DROP DATABASE functional_key_lab;