MySQL 函数触发隐式转换应对策略
MySQL 中，当 SQL 索引字段使用了函数的话，会出现隐式转换的问题，导致索引失效，从而导致 SQL 执行效率变慢。本篇文章介绍 MySQL 不同版本此类问题的应对策略。
1. 环境介绍
以下是本篇文章，测试需要准备的表结构和环境信息。
数据库版本：
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
示例 SQL：
select instance_id, instance_name, status from task_queue where DATE_FORMAT(create_time, '%Y-%m-%d') = '2023-09-10'
2. MySQL 5.7
MySQL 5.7 版本新增了一个虚拟列的新特性，可以通过该特性让示例 SQL 可以用到索引。
2.1 执行计划
先看看 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 字段使用了函数，导致 SQL 隐式转换，从而导致索引失效。
2.2 虚拟列优化
接下来创建一个虚拟列，该列是基于 create_time 计算创建而来。
alter table task_queue add column v_create_time datetime GENERATED ALWAYS AS (DATE_FORMAT(create_time, '%Y-%m-%d'));
然后为该列添加一个索引：
alter table task_queue add 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 进行任何修改，只是调整了表结构。
2.3 原理解析
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 字段支持创建二级索引，这也是能解决函数计算造成的
推荐阅读：
3. MySQL 8.0
MySQL 8.0 提供了一个函数索引新特性，相比 5.7 的虚拟列，可以更方便的解决隐式转换问题。
3.1 执行计划
以下是示例 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 字段的索引。
3.2 函数索引优化
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 版本的虚拟列更新便捷。
3.3 原理解析
MySQL 8.0 的函数索引，比较好理解，就是根据用户指定的计算规则，维护一个二级索引，来应对更复杂的查询。
以下是函数索引的使用限制。
主键不支持函数索引，因为主键以实际列进行存储，而函数索引是作为虚拟列存在的
在有主键的情况下，唯一索引支持函数索引；但在无主键的情况下，被提升为主键的唯一索引不支持
外键不支持函数索引
空间索引和全文索引不支持函数索引
函数索引不能直接使用列前缀，可以通过SUBSTRING()和CAST()来替代
在删除列之前，要先删除相关的函数索引
推荐阅读：
