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

文若1年前技术文章955

前言

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财务系统上云

下一篇：数据库性能大揭秘：玩转MySQL监控指标状态变量

相关文章

MS SQL Server 实战 统计与汇总重复记录

MS SQL Server 实战 统计与汇总重复记录

需求在日常的数据管理应用中，统计和汇总重复记录的情况是经常遇到的一个问题，然后我们会根据统计结果进一步对数据进行合理化处理。比如我们有一组题库数据，主要包括题目和选项字段（如单选题目、多选题目和判断题...

MySQL 组复制一致性保证

MySQL 组复制一致性保证

说明本篇文章介绍，MySQL 组复制作为一个分布式系统，如何保证事务一致性？1. 一致性级别MySQL 8.0.14 版本开始，提供组复制变量：group_replication_consistenc...

docker网络介绍

docker网络介绍

一、docker网络介绍安装docker时，会自动创建三个网络。可以使用docker network ls命令列出这些网络Docker内置这三个网络，运行容器时，可以使用--network标志来指定容...

MySQL系统空间上涨

MySQL系统空间上涨

系统空间上涨一、介绍MySQL 的 ibdata1 是一个用来构建 innodb 系统表空间的文件。该文件包含了 undo ，还包含在用户在系统表空间创建的表信息和索引数据。一般如果数据空间均为独立表...

CDP实操--配置KNOX SSO（五）

CDP实操--配置KNOX SSO（五）

1.1配置Atlas的SSO身份验证在Knox SSO的topology里配置Knox与LDAP集成认证如下，并重启Knox服务role=authenticationauthentication.na...

大数据集群监控配置操作指导（二）node_exporter+mysql_exporter部署

大数据集群监控配置操作指导（二）node_exporter+mysql_exporter部署

2.node_exporter监控集群服务器（所有集群服务器）wget https://github.com/prometheus/node_exporter/releases/download/v1...

发表评论    

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

©Copyrights 2016-2022 YUNCHE 浙ICP备2021017017号