摘要在 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.13CREATE INDEX idx_cellphone ON UserLogin ( (CAST(loginInfo-$.cellphone AS CHAR(255))) );两种方案都能让 JSON 字段查询享受到与传统列相同的索引性能同时避免全表扫描带来的资源浪费。六、总结普通索引失效的根因索引基于原始列值排序无法匹配函数处理后的结果优化器只能放弃索引。函数索引对表达式计算结果建立索引直接解决函数条件无法使用索引的问题。MySQL 8.0.13 支持简洁语法底层自动创建隐式虚拟列。显式虚拟列 索引MySQL 5.7 时期的替代方案但今天依然流行因为虚拟列对用户可见可读性及复用性更佳。隐式与显式本质相同都是将表达式值物化并建立有序结构区别仅在于用户是否能看到这一中间列。JSON 高性能查询虚拟列/函数索引是处理 JSON 字段查询优化的利器能有效避免全表扫描是各类半结构化数据存储方案的关键优化手段。掌握函数索引与虚拟列的原理与用法能够帮助开发者在面对复杂表达式查询时自如地做出最优的索引设计显著提升数据库读写性能。