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

文若1年前技术文章956

前言

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监控指标状态变量

相关文章

PostgreSQL 流复制

前言PostgreSQL 流复制（Streaming Replication）是 9.0 提供的一种新的 WAL 传递方法。使用流复制时，每当 Primary 节点 WAL 产生，就会马上传递到 St...

开源大数据集群部署（六）Keytab文件生成

开源大数据集群部署（六）Keytab文件生成

1、 创建keytab文件除了使用明文密码登录之外，Kerberos还可以使用keytab密码文件登陆，现在为testcuser创建它的keytab文件ipa-getkeytab -s ipa.hdp...

爬坑记录------容器内外网络不通，端口无法访问

爬坑记录------容器内外网络不通，端口无法访问

背景：客户需要一套datahub用于分许sparksql血缘，因此需要基于docker搭建，在搭建过程中遇到一个较坑的问题，这里做以记录搭建完成后查看docker容器已经正常启动查看红框中的内容信息，...

Elasticsearch查询优化

1 使用更快的硬件查询性能大部分场景下更多的在于IO能力，很多时候查询速度受限于磁盘IO能力，使用SSD会比旋转类存储介质好得多。如果查询类型属于计算比较多的，则可以考虑使用更快的CPU。2 为文件系...

使用Sqoop将数据从Hive导入MySQL（一）

使用Sqoop将数据从Hive导入MySQL（一）

使用Sqoop将数据从Hive导入MySQL首先查看csv数据类型创建类似的hive表并导入数据CREATE TABLE data (    province STRING,    code INT,...

大数据之数据采集组件选型

大数据之数据采集组件选型

按数据的源头、实际上也是对应的数据采集方式，分别进行分析与技术推荐，数据从源头基本分为以下三大类1、Web页面/移动App/MES/IoT/系统生产： 这些数据是被动接收。建议可采用Apache Ni...

发表评论    

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

©Copyrights 2016-2022 YUNCHE 浙ICP备2021017017号