用了函数就无法使用索引？MySQL函数索引值得你拥有
MySQL中的索引，就像图书馆里的索引卡片，帮我们快速定位到想要的信息。但是，如果你对这些卡片动了点“手脚”，比如用个函数来“改造”一下索引字段，那么这些卡片可能就不再那么有效了，查找起来就得费劲多了。
咱们先来看个例子。假设你有个名为 students 的表，里面有个 age 字段，你给它建了个索引，方便快速查找某个年龄的学生。但是，有一天你心血来潮，想查找年龄的平方大于某个值的学生，于是你写了个 SQL，大概是这样的：
SELECT * FROM studentsWHERE SQRT(age) > 10;
哎呀，这里你用了个 SQRT 函数来处理 age 字段。MySQL 一看，这索引字段被 “改造” 了，它就不敢再信任原来的索引卡片了，只能老老实实地去逐行扫描数据，效率自然就慢了。
这个问题，不同版本的 MySQL 有不同的应对策略。在早些版本中，MySQL 可能比较“固执”，一遇到这种情况就直接放弃索引，哪怕你后面还有其他条件可以用到索引。这就像图书馆员看到被涂鸦的索引卡片，直接扔到一边，不管后面还有没有其他有用的信息。
但是，随着 MySQL 版本的升级，它变得越来越“聪明”了。新版本可能会尝试优化这种情况，比如专门 “动手脚” 的卡片，制作定制化索引卡片，满足用户个性化需求，依然能够快速帮你找书。本篇文章将为您介绍 MySQL 不同版本此类问题的应对策略。
环境介绍
以下是本篇文章，测试需要准备的表结构和环境信息。
数据库版本：
MySQL 5.7.37
MySQL 8.0.32
测试表结构：
CREATE TABLE `task_queue` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `instance_id` varchar(50) NOT NULL COMMENT '实例ID', `instance_name` varchar(50) NOT NULL COMMENT '实例名', `create_time` datetime NOT NULL COMMENT '任务创建时间', `end_time` datetime NOT NULL COMMENT '任务结束时间', `instance_type` varchar(10) NOT NULL COMMENT '数据库类型：MySQL、SQLServer', `status` varchar(3) NOT NULL DEFAULT '未完成' COMMENT '任务状态：完成 & 未完成 & 异常', PRIMARY KEY (`id`) USING BTREE, KEY `idx_create_time` (`create_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
制造测试数据：
造数工具下载地址：
https://github.com/Percona-Lab/mysql_random_data_load/releases
# 解压后会得到一个二进制程序tar -zxvf mysql_random_data_load_0.1.12_Linux_x86_64.tar.gz# 验证mysql_random_data_load --help# 造数据命令模版，按需修改配置mysql_random_data_load -h127.0.0.1 -u'用户' -p'密码' --max-threads=线程数 库名 表名 造数多少行
为 task_queue 表制造 10000 行测试数据。
mysql_random_data_load -h127.0.0.1 -u'root' -p'abc123' --max-threads=2 test task_queue 10000
示例 SQL：
select instance_id, instance_name, status from task_queue where DATE_FORMAT(create_time, '%Y-%m-%d') = '2023-09-10'
MySQL 5.7 版本
MySQL 5.7 版本新增了一个虚拟列的新特性，通过该特性让示例 SQL 用到索引。
执行计划
先看看 SQL 在 5.7 版本的执行计划：
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "13.20" }, "table": { "table_name": "task_queue", "access_type": "ALL", "rows_examined_per_scan": 61, "rows_produced_per_join": 61, "filtered": "100.00", "cost_info": { "read_cost": "1.00", "eval_cost": "12.20", "prefix_cost": "13.20", "data_read_per_join": "28K" }, "used_columns": [ "instance_id", "instance_name", "create_time", "status" ], "attached_condition": "(date_format(`op_service_db_bak`.`task_queue`.`create_time`,'%Y-%m-%d') = '2023-11-11')" } }}由于 create_time 字段使用了函数，导致 idx_create_time 索引失效。
虚拟列优化
接下来创建一个虚拟列，该列是基于 create_time 计算创建而来。
alter table task_queueadd column v_create_time datetimeGENERATED ALWAYS AS (DATE_FORMAT(create_time, '%Y-%m-%d'));
然后为该列添加一个索引：
alter table task_queueadd index idx_v_create_time(v_create_time);
此时的表结构如下：
CREATE TABLE `task_queue` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `instance_id` varchar(50) NOT NULL COMMENT '实例ID', `instance_name` varchar(50) NOT NULL COMMENT '实例名', `create_time` datetime NOT NULL COMMENT '任务创建时间', `end_time` datetime NOT NULL COMMENT '任务结束时间', `instance_type` varchar(10) NOT NULL COMMENT '数据库类型：MySQL、SQLServer', `status` varchar(3) NOT NULL DEFAULT '未完成' COMMENT '任务状态：完成 & 未完成 & 异常', `v_create_time` datetime GENERATED ALWAYS AS (date_format(`create_time`,'%Y-%m-%d')) VIRTUAL, PRIMARY KEY (`id`) USING BTREE, KEY `idx_create_time` (`create_time`), KEY `idx_v_create_time` (`v_create_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
再次执行 SQL 查看执行计划：
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "5.20" }, "table": { "table_name": "task_queue", "access_type": "ref", "possible_keys": [ "idx_v_create_time" ], "key": "idx_v_create_time", "used_key_parts": [ "v_create_time" ], "key_length": "6", "ref": [ "const" ], "rows_examined_per_scan": 11, "rows_produced_per_join": 11, "filtered": "100.00", "cost_info": { "read_cost": "3.00", "eval_cost": "2.20", "prefix_cost": "5.20", "data_read_per_join": "5K" }, "used_columns": [ "instance_id", "instance_name", "create_time", "status", "v_create_time" ] } }}通过执行计划可以看到，通过创建虚拟列，让SQL 使用上索引，且未对原 SQL 进行任何修改，只是调整了表结构。
原理解析
Generated Column 是 MySQL 5.7 引入的新特性，就是数据库中的一个字段，是由其它字段计算而得。我们以官方文档中的例子给予说明。
例如，知道直角三角形的两条直角边，要求斜边的长度。很明显，斜边的长度可以通过两条直角边计算而得，那么，这时候就可以在数据库中只存放直角边，斜边使用 Generated Column 如下所示：
CREATE TABLE triangle ( sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)));INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
mysql> SELECT * FROM triangle;+-------+-------+--------------------+| sidea | sideb | sidec |+-------+-------+--------------------+| 1 | 1 | 1.4142135623730951 || 3 | 4 | 5 || 6 | 8 | 10 |+-------+-------+--------------------+
Generated Column 的创建语法如下：
col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string']
需要注意的是 Generated Column 有两种模式，分别是 VIRTUAL 和 STORED，默认模式为 VIRTUAL。
VIRTUAL 是将字段的计算逻辑存储在数据字典中，既元数据。
STORED 是会将计算而得的数据，存储到磁盘中，会得到持久化存储。
Generated Column 字段支持创建二级索引，这也是能解决函数计算造成的索引失效的原因。
推荐阅读：
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
MySQL 8.0
MySQL 8.0 提供了一个函数索引新特性，相比 5.7 的虚拟列，可以更方便的解决函数导致索引失效问题。
执行计划
以下是示例 SQL 在 8.0 版本中的执行计划：
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "6.35" }, "table": { "table_name": "task_queue", "access_type": "ALL", "rows_examined_per_scan": 61, "rows_produced_per_join": 61, "filtered": "100.00", "cost_info": { "read_cost": "0.25", "eval_cost": "6.10", "prefix_cost": "6.35", "data_read_per_join": "28K" }, "used_columns": [ "instance_id", "instance_name", "create_time", "status" ], "attached_condition": "(date_format(`test`.`task_queue`.`create_time`,'%Y-%m-%d') = '2023-11-11')" } }}与 MySQL 5.7 版本相同，由于使用函数，无法使用 create_time 字段的索引。
函数索引优化
MySQL 8.0 函数索引创建方式非常简单，如下：
alter table task_queue add index idx_func_create_time((DATE_FORMAT(create_time, '%Y-%m-%d')));
通过如下 SQL 可以查看函数索引：
mysql>select table_name,index_name,seq_in_index,column_name,is_visible,expression from information_schema.statistics where table_name='task_queue';+------------+----------------------+--------------+-------------+------------+-------------------------------------------------+| TABLE_NAME | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | IS_VISIBLE | EXPRESSION |+------------+----------------------+--------------+-------------+------------+-------------------------------------------------+| task_queue | idx_create_time | 1 | create_time | YES | NULL || task_queue | idx_func_create_time | 1 | NULL | YES | date_format(`create_time`,_utf8mb4\'%Y-%m-%d\') || task_queue | PRIMARY | 1 | id | YES | NULL |+------------+----------------------+--------------+-------------+------------+-------------------------------------------------+
再次执行示例 SQL 查看执行计划：
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "0.35" }, "table": { "table_name": "task_queue", "access_type": "ref", "possible_keys": [ "idx_func_create_time" ], "key": "idx_func_create_time", "used_key_parts": [ "date_format(`create_time`,_utf8mb4'%Y-%m-%d')" ], "key_length": "43", "ref": [ "const" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "0.25", "eval_cost": "0.10", "prefix_cost": "0.35", "data_read_per_join": "520" }, "used_columns": [ "instance_id", "instance_name", "create_time", "status", "date_format(`create_time`,_utf8mb4'%Y-%m-%d')" ] } }}通过执行计划可以看到，创建函数索引可以让 SQL 重新使用索引，且未对原 SQL 和表字段进行任何修改，只是添加一个索引。相比于 MySQL 5.7 版本的虚拟列，函数索引更新便捷。
原理解析
MySQL 8.0 的函数索引，比较好理解，就是根据用户指定的计算规则，维护一个二级索引，来应对更特殊的查询场景。
以下是函数索引的使用限制。
主键不支持函数索引，因为主键以实际列进行存储，而函数索引是作为虚拟列存在的。
在有主键的情况下，唯一索引支持函数索引；但在无主键的情况下，被提升为主键的唯一索引不支持。
外键不支持函数索引。
空间索引和全文索引不支持函数索引。
函数索引不能直接使用列前缀，可以通过 SUBSTRING() 和 CAST() 来替代/
在删除列之前，要先删除相关的函数索引。
