摘要：在 MySQL 中，写一条带函数的 WHERE 条件导致全表扫描，几乎是开发中最为常见的性能陷阱之一。这篇文章从索引失效的根因入手，深入拆解函数索引的实现机制，对比显式虚拟列与隐式虚拟列这两种技术路径，再结合 JSON 业务场景，给出可落地的索引优化方案。读完之后，函数索引的底层逻辑和最佳实践，应该就能心中有数了。
一、普通索引为何挡不住函数条件？
普通索引的本质，是基于原始列值构建一个有序结构。比如，对 register_date 列建索引，内部存储的就是原始的日期值，并按照日期顺序排列：
2021-01-01 → 2021-01-02 → … → 2021-02-01。
但当查询条件写成：
SELECT * FROM UserWHERE DATE_FORMAT(register_date, '%Y-%m') = '2021-01';
这里 DATE_FORMAT() 函数把寄存日期转换成了 2021-01 格式的字符串。可问题是，普通索引存储和排序的依据是原始日期，而不是格式化后的结果。优化器没办法直接利用这个索引来快速定位，只能逐行读取数据、计算后再进行比较——结果就是“索引失效，全表扫描”。
不少人会想当然地以为，只要在 register_date 上建了索引，所有 SQL 就都能用上它。但索引的核心在于排序，索引 idx_register_date 只对 register_date 的数据做了排序，可它没给 DATE_FORMAT(register_date) 排过序，所以上面的 SQL 自然没法用二级索引 idx_register_date。
所以，数据库规范里那句“函数写在等式右边”，其实就是为了避免这种尴尬。可以把 SQL 改成：
SELECT * FROM UserWHERE register_date BETWEEN '2021-01-01' AND '2021-01-31';
这样一来，register_date 列保持原始值，索引就能命中，查询效率自然就上来了。
二、函数索引：从根源解决“函数导致索引失效”
函数索引的思路其实很简单：既然查询条件用了函数表达式，那干脆直接为这个表达式的计算结果建索引，并按这个结果排序。从 MySQL 8.0.13 开始，可以用简洁语法直接创建函数索引：
CREATE INDEX idx_date_format ON User (DATE_FORMAT(register_date, '%Y-%m'));
这个索引内部存储的是格式化后的值（比如 2021-01），并按此顺序排列。当再次执行 WHERE DATE_FORMAT(register_date, '%Y-%m') = '2021-01' 时，优化器可以直接匹配到这个索引，根本不用全表扫描。
从底层来看，MySQL 会自动创建一个隐藏的生成列（隐式虚拟列）来存放表达式结果，再对这个隐藏列建索引。整个过程中，用户不需要手动维护什么，表结构对上层应用完全透明。
三、显式虚拟列：手动实现函数索引（5.7 兼容方案）
在 MySQL 5.7 中，虽然不支持直接创建函数索引，但可以通过虚拟列（Generated Column）+ 普通索引的组合来实现同样的效果。虚拟列的值由表达式自动生成，不用手动维护，而且默认不占用磁盘空间（VIRTUAL 类型）。
1. 创建虚拟列并建立索引
ALTER TABLE UserADD COLUMN reg_month VARCHAR(7)GENERATED ALWAYS AS (DATE_FORMAT(register_date, '%Y-%m')) VIRTUAL;CREATE INDEX idx_reg_month ON User(reg_month);
现在 reg_month 成了一个真实存在的列，可以在查询中直接用了：
SELECT * FROM User WHERE reg_month = '2021-01';
2. 虚拟列类型详解与语法简化
MySQL 提供了两种虚拟列类型，用于不同的场景：
- VIRTUAL（默认）：不占用磁盘空间，只存储计算规则；每次查询时根据表达式实时计算值。适合计算成本低、查询频率不高的场景。本文中的
cellphone列就是这种类型，所以可以说“不占用任何存储空间”。 - STORED：占用磁盘空间，把表达式结果物理存储到表里；当原始数据发生修改时自动更新。适合计算成本高、频繁查询的场景，可以避免重复计算的开销。
实际写 DDL 时，很多关键字可以省略，让语句更简洁。下面三种写法效果完全一样：
-- 完整写法（关键字齐全）reg_month VARCHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(register_date, '%Y-%m')) VIRTUAL;-- 省略 GENERATED ALWAYSreg_month VARCHAR(7) AS (DATE_FORMAT(register_date, '%Y-%m')) VIRTUAL;-- 最简写法（连 VIRTUAL 也省略，因为它是默认值）reg_month VARCHAR(7) AS (DATE_FORMAT(register_date, '%Y-%m'));
需要特别注意：AS (表达式) 是虚拟列的核心标识，绝对不能省略。GENERATED ALWAYS 只是语义上的装饰，可以不留。日常开发中，推荐用最简写法，保持 DDL 清晰易读。
3. 显式虚拟列与函数索引本质一致
二者都是把表达式的计算结果固化下来，并以此为基础构建有序索引，从而让函数查询能利用索引执行。唯一的区别在于：
- 显式虚拟列：列对用户可见，可以直接引用，适合需要反复使用或作为查询条件的表达式。
- 函数索引：列对用户隐藏，使用起来更简洁，但没办法在 SELECT 中直接引用这个虚拟列。
四、隐式虚拟列：函数索引背后的隐藏列
当执行 CREATE INDEX idx ON User (loginInfo->>"$.cellphone") 这样的函数索引语句时，MySQL 会自动在底层创建一个对用户不可见的虚拟列，这就是隐式虚拟列。
它的特性如下：
- 完全隐藏：通过
DESC或SELECT *都看不到，它只为索引服务。 - 自动维护：写入数据时，表达式结果自动计算并存储到隐藏列，不需要任何额外操作。
- 等价于显式虚拟列索引：查询优化器能识别带有相同表达式的 WHERE 条件，直接使用这个索引。
用一个比喻来理解：显式虚拟列相当于自己给数据贴上一个可见的标签，而隐式虚拟列则是系统悄悄贴上的隐形标签，只有系统自己需要时才会用到。
五、实战场景：为 JSON 字段建立高效的查询路径
在爬虫数据、订单快照这些以 JSON 存储半结构化数据的场景里，虚拟列或函数索引的价值尤其突出。
痛点：查询JSON内部字段只能全表扫描
假设 UserLogin 表存储 JSON 格式的登录信息：
CREATE TABLE UserLogin ( userId BIGINT, loginInfo JSON, cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"), PRIMARY KEY(userId), UNIQUE KEY idx_cellphone(cellphone));
如果没有虚拟列，直接查询 JSON 内部字段只能写成：
SELECT * FROM UserLoginWHERE loginInfo->>"$.cellphone" = '13918888888';
这个写法每次都要解析 JSON、提取字段值，而且没法用索引，数据量稍微大一点就会引发严重的性能问题。
方案1：显式虚拟列 + 索引
ALTER TABLE UserLoginADD COLUMN cellphone VARCHAR(255)GENERATED ALWAYS AS (loginInfo->>"$.cellphone") VIRTUAL;CREATE UNIQUE INDEX idx_cellphone ON UserLogin(cellphone);
之后就可以直接查询 cellphone 列并且命中索引：
SELECT * FROM UserLogin WHERE cellphone = '13918888888';
方案2：直接函数索引（8.0.13+）
CREATE INDEX idx_cellphone ON UserLogin ( (CAST(loginInfo->>"$.cellphone" AS CHAR(255))) );
这两种方案都能让 JSON 字段查询享受到与传统列相同的索引性能，同时避免全表扫描带来的资源浪费。
六、总结
- 普通索引失效的根因：索引基于原始列值排序，没法匹配函数处理后的结果，优化器只能放弃索引。
- 函数索引：对表达式计算结果建立索引，直接解决函数条件无法使用索引的问题。MySQL 8.0.13+ 支持简洁语法，底层自动创建隐式虚拟列。
- 显式虚拟列 + 索引：MySQL 5.7 时期的替代方案，但今天依然流行，因为虚拟列对用户可见，可读性和复用性更佳。
- 隐式与显式本质相同：都是把表达式值物化并建立有序结构，区别只在于用户是否能看见这个中间列。
- JSON 高性能查询：虚拟列或函数索引是处理 JSON 字段查询优化的利器，能有效避免全表扫描，是各类半结构化数据存储方案的关键优化手段。
掌握函数索引与虚拟列的原理和用法，能帮助开发者在面对复杂表达式查询时，自如地做出最优的索引设计，显著提升数据库读写性能。
