MySQL 函数索引与虚拟列深度解析
摘要：在 MySQL 中，写一条带函数的 WHERE 条件导致全表扫描，是开发中极为常见的性能陷阱。本文将从索引失效的根因出发，深入剖析函数索引的实现机制，对比显式虚拟列与隐式虚拟列两种技术路径，并结合 JSON 业务场景，给出可落地的索引优化方案。读完本文，你将彻底掌握函数索引的底层逻辑与最佳实践。
一、普通索引为何挡不住函数条件？
普通索引基于原始列值构建有序结构。例如，对register_date列建立的索引，内部存储的是原始日期值，并按日期顺序排列：2021-01-01 → 2021-01-02 → … → 2021-02-01。
当查询条件写成：
SELECT * FROM User
WHERE DATE_FORMAT(register_date, '%Y-%m') = '2021-01';DATE_FORMAT()函数将寄存器日期转换为2021-01格式的字符串。然而，普通索引存储和排序的依据是原始日期，而不是格式化后的结果。优化器无法直接利用该索引快速定位，只能逐行读取数据、计算后再进行比较，即“索引失效，全表扫描”。
数据库规范要求“函数写在等式右边”，正是为了避免这种情况。可将 SQL 改写为：
SELECT * FROM User
WHERE 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 User
ADD 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 ALWAYS
reg_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 UserLogin
WHERE loginInfo->>"$.cellphone" = '13918888888';该写法每次需要解析 JSON、提取字段值，且无法利用索引，数据量稍大便会导致严重性能问题。
方案1：显式虚拟列 + 索引
ALTER TABLE UserLogin
ADD 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 字段查询优化的利器，能有效避免全表扫描，是各类半结构化数据存储方案的关键优化手段。
掌握函数索引与虚拟列的原理与用法，能够帮助开发者在面对复杂表达式查询时，自如地做出最优的索引设计，显著提升数据库读写性能。