MySQL 函数索引提取表达式结果的设计方法

当 WHERE 条件不是直接比较列，而是先经过 LOWER()、DATE() 或 JSON 提取表达式时，普通列索引未必能直接复用。MySQL 函数索引的设计重点不是“把函数套在索引外面”，而是先固定表达式和结果类型，再把这个结果变成可查找的索引键。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

先把表达式结果变成可查找的索引键

函数索引适合“输入列确定、函数确定、输出类型稳定”的场景。例如系统统一按小写邮箱查找，就可以直接索引 LOWER(email)。MySQL 的函数索引语法需要额外一层括号，用来区分表达式和普通索引列。

CREATE TABLE account (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL
) ENGINE = InnoDB;

-- 把统一后的邮箱结果直接作为函数索引键
CREATE INDEX idx_account_email_lower
  ON account ((LOWER(email)));

-- 查询侧重复同一个表达式，便于优化器匹配索引
SELECT id, email
FROM account
WHERE LOWER(email) = 'dev@example.com';

这里的关键不是函数名称，而是表达式的可重复性。若业务还要按原始邮箱排序、按不同字符集比较，应该先确认排序规则和大小写语义，再决定是否值得维护这条索引。

JSON 提取更适合用生成列固定结果类型

JSON 场景通常不仅要“取出一个值”，还要决定它究竟按字符串、整数还是日期比较。直接函数索引可以缩短表结构，但生成列更容易复用、检查和解释，也能把类型转换写在一个稳定位置。

ALTER TABLE orders
  ADD COLUMN tenant_id_idx BIGINT
    GENERATED ALWAYS AS (
      -- 去掉 JSON 字符串引号，再固定为数值类型
      CAST(JSON_UNQUOTE(JSON_EXTRACT(profile, '$.tenant_id')) AS UNSIGNED)
    ) STORED,
  ADD INDEX idx_orders_tenant (tenant_id_idx);

-- 查询时使用同样的提取逻辑，也可以直接引用命名生成列
SELECT id, created_at
FROM orders
WHERE tenant_id_idx = 10086;

对 JSON 字符串，JSON_UNQUOTE 很重要；否则索引定义得到的值可能带引号，优化器在字符串比较时不一定能按预期复用。若值可能缺失，要提前决定 NULL 如何参与筛选，并让应用层不要把“缺失”和“0”混成同一个业务状态。

直接函数索引和生成列索引怎么选

生成列可以是 VIRTUAL 或 STORED。前者不额外存储列值，但读取时仍需计算；后者把结果存下来，写入和更新成本更高，而且生成列值与索引都会占空间。只有在表达式计算昂贵、查询收益明确时，才值得选择 STORED。

用 EXPLAIN 检查表达式是否真的匹配

索引建成功不等于查询一定使用。检查时先确认表定义，再看执行计划中的 possible_keys、key 和估算行数：

-- 先确认表达式、数据类型和索引名称没有漂移
SHOW CREATE TABLE orders;

-- 用实际查询检查优化器是否考虑目标索引
EXPLAIN SELECT id, created_at
FROM orders
WHERE tenant_id_idx = 10086;

如果查询直接写表达式，表达式本身和结果类型要保持一致。比如生成列定义为 f1 + 1，查询写成 1 + f1 或拿字符串去比较，优化器可能不再把它当作同一个索引表达式。遇到计划不稳定时，先修正表达式和类型，再考虑索引提示。

上线前的四项边界清单

1. 确认函数是确定性的，不依赖当前时间、连接用户、变量或子查询。
2. 确认输入列的字符集、排序规则、NULL 行为和 JSON 缺失字段已经写进设计。
3. 确认 DDL 的表达式结果不会截断、溢出或被隐式转换成意外类型。
4. 用真实查询跑 EXPLAIN，同时评估 INSERT、UPDATE 增加的索引维护成本。

延伸问答

函数索引为什么要写两层括号？

一层括号表示索引列列表，内层括号才表示一个表达式。少写一层时，MySQL 会按普通列索引语法解析，容易直接报语法错误。

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

JSON 字符串值可能保留 JSON 的引号表示。先去掉引号，再按业务需要 CAST，索引键和查询比较的类型更明确。

生成列一定要使用 STORED 吗？

不一定。表达式轻量且更关注节省存储时可考虑 VIRTUAL；需要把复杂结果作为稳定缓存并频繁查找时，再评估 STORED。

EXPLAIN 没有选择函数索引怎么办？

先核对表达式是否逐字一致、结果类型是否一致、比较运算符是否适用，再查看选择性和统计信息；不要只因为索引名字出现在表结构里就认定它有效。