MySQL 函数触发隐式转换应对策略

文若3个月前技术文章116

前言

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 字段支持创建二级索引,这也是能解决函数计算造成的

推荐阅读:13.1.18.7 CREATE TABLE and Generated Columns

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 的函数索引,比较好理解,就是根据用户指定的计算规则,维护一个二级索引,来应对更复杂的查询。

以下是函数索引的使用限制。

  1. 主键不支持函数索引,因为主键以实际列进行存储,而函数索引是作为虚拟列存在的

  2. 在有主键的情况下,唯一索引支持函数索引;但在无主键的情况下,被提升为主键的唯一索引不支持

  3. 外键不支持函数索引

  4. 空间索引和全文索引不支持函数索引

  5. 函数索引不能直接使用列前缀,可以通过SUBSTRING()和CAST()来替代

  6. 在删除列之前,要先删除相关的函数索引

推荐阅读:Functional Key Parts


返回列表

上一篇:技术实践分享|用友NC财务系统上云

没有最新的文章了...

相关文章

kubernetes集群清理

清理如果你在集群中使用了一次性服务器进行测试,则可以关闭这些服务器,而无需进一步清理。你可以使用 kubectl config delete-cluster 删除对集群的本地引用。但是,如果要更干净地...

linux时间同步工具Chrony的配置和使用

linux时间同步工具Chrony的配置和使用

一、chrony简介       chronony是网络时间协议(NTP)的通用实现。它可以同步系统时钟与NTP服务器,chronyd包含两个程序,chronyd是一个守护进程,chronyc是一个命...

Prometheus集成pushgateway监控k8s集群

Prometheus集成pushgateway监控k8s集群

Prometheus部署环境介绍本文的k8s环境是通过二进制方式搭建的v1.20.13版本清单准备注意集群版本的坑,自己先到Github上下载对应的版本。注意: 集群版本在v1.21.x之前需要注意下...

企业级大数据安全架构(十一)

企业级大数据安全架构(十一)

一、Kerberos接入dophinscheduler建议将dophinscheduler集成到Ambari安装部署,在Ambari上面开启kerberos1.安装准备编译从GitHub获取dolph...

performance_schema

performance_schema

performance_schema一、数据库支持情况1、performance_schema被视为存储引擎,如果该引擎可用,则应该 INFORMATION_SCHEMA.ENGINES 表或 sho...

系统RDSCPU打满问题分析报告

系统RDSCPU打满问题分析报告

1. 问题概述在2023年9月01日09点13分,玳数运维组侧接收到业务侧反馈系统响应缓慢,与此同时运维群内新系统RDS 发出CPU打满的告警,告警通知如下: 2. 问题分析a. 数据库会话管理核查玳...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。