MySQL 知识点：函数索引（Functional Index）

MySQL 技术文档：函数索引（Functional Index）

1. 概述

在 MySQL 8.0.13 之前，索引必须关联到表的列或列的前缀。如果查询条件对列使用了函数（如WHERE UPPER(name) = 'TOM'），即使name字段有索引，该索引也会失效。

函数索引允许在表达式或函数的结果上建立索引，从而使原本无法利用索引的复杂查询实现高性能。

2. 语法结构

创建函数索引的语法与普通索引类似，区别在于索引部分被包含在双括号内。

2.1 创建表时定义

CREATETABLEusers(idINTPRIMARYKEY,first_nameVARCHAR(50),last_nameVARCHAR(50),-- 创建函数索引：存储全名的长度INDEXidx_name_len((LENGTH(CONCAT(first_name,last_name)))));

2.2 在现有表上添加

ALTERTABLEt_erp_doc_credentialADDUNIQUEINDEXuk_active_code((IF(del_flag=1,credential_code,NULL)));

3. 实现原理

MySQL 内部将函数索引实现为隐藏的虚拟生成列（Virtual Generated Column）：

1. 当数据插入或更新时，MySQL 自动计算表达式的结果。
2. 计算后的结果被存储在 B+ 树索引结构中。
3. 优化器在解析 SQL 时，如果发现WHERE条件中的表达式与函数索引定义的表达式完全匹配，则直接引用该索引。

4. 关键应用场景

4.1 逻辑删除下的唯一性约束

这是最经典的应用。在包含逻辑删除字段（如del_flag）的表中，要求“未删除的数据唯一，已删除的数据可重复”。

• 表达式：UNIQUE INDEX ((IF(del_flag = 1, code, NULL)))
• 原理：MySQL 唯一索引允许存在多个NULL值。

4.2 大写/小写不敏感查询

如果数据库字符集区分大小写，但业务需要快速进行不区分大小写的搜索：

• 表达式：INDEX ((UPPER(user_email)))

4.3 JSON 字段检索

针对 JSON 类型的某个 key 进行高频查询：

• 表达式：INDEX ((CAST(json_col->>'$.user_name' AS CHAR(30))))

5. 局限性与注意事项

5.1 语法严格匹配

查询 SQL 中的表达式必须与索引定义的表达式完全一致。

• 有效：WHERE UPPER(name) = 'A'匹配INDEX ((UPPER(name)))
• 无效：WHERE name = 'a'无法直接利用INDEX ((UPPER(name)))

5.2 性能开销

• 写入性能：每次INSERT或UPDATE都会触发表达式计算，略微增加 CPU 负担。
• 存储空间：虽然不增加可见列，但索引本身会在磁盘（.ibd文件）中占用空间。

5.3 算法限制

• 函数索引不支持ALGORITHM=INPLACE。添加该索引时通常会触发COPY算法（锁表），在生产环境大型表操作时需谨慎。

5.4 维护规范

• 函数索引中的函数必须是确定性函数（Deterministic）。例如，不能使用NOW()或RAND()，因为它们的值随时间改变。
• 主键不能是函数索引。

6. 性能验证

使用EXPLAIN命令观察Extra列。如果看到Using index或key命中，说明函数索引生效。

EXPLAINSELECT*FROMusersWHERELENGTH(CONCAT(first_name,last_name))>10;

7. 总结

函数索引是 MySQL 8.0 迈向现代数据库的重要一步。它不仅减少了冗余物理列的创建（原本需要手动创建生成列再加索引），还为复杂的业务逻辑优化提供了极大的灵活性。

建议：在处理逻辑删除唯一性、JSON 搜索或历史遗留的复杂查询优化时，优先考虑函数索引。