数栈SQL优化案例：隐式转换

Z时代
2024-01-10
分类：综合

database

数栈是云原生—站式数据中台PaaS，我们在github和gitee上有一个有趣的开源项目：FlinkX，FlinkX是一个基于Flink的批流统一的数据同步工具，既可以采集静态的数据，也可以采集实时变化的数据，是全域、异构、批流一体的数据同步引擎。大家喜欢的话请给我们点个star！star！star！

github开源项目：https://github.com/DTStack/flinkx

gitee开源项目：https://gitee.com/dtstack_dev_0/flinkx

MySQL是当下最流行的关系型数据库之一，互联网高速发展的今天，MySQL数据库在电商、金融等诸多行业的生产系统中被广泛使用。

在实际的开发运维过程中，想必大家也常常会碰到慢SQL的困扰。一条性能不好的SQL，往往会带来过大的性能开销，进而引起整个操作系统资源的过度使用，甚至造成会话堆积，引发线上故障。

而在SQL调优的场景中，一类比较常见的问题，就是隐式类型转换。那什么是隐式转换呢？

在MySQL中，当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容，此时则会发生隐式转换。出现隐式转换，往往意味着SQL的执行效率将大幅降低。

接下来笔者将结合几大常见场景，让大家实际体会什么是隐式转换，以及如何去应对出现隐式转换的情况，请阅读以下案例。

一、传递数据类型和字段类型不一致造成隐式转换

一类比较经典的场景就是传递数据类型和字段类型不一致造成的隐式转换，这种场景也是我们平时最常遇到的。具体可以看下下面这个例子：

1) 待优化场景

SQL及执行计划如下：

select * from dt_t1 where emp_no = 41680;

该表索引如下：

key idx_empno (`emp_no`)

2）场景解析

从执行计划中Type部分：ALL，全表扫描，而没有走idx_empno索引, 一般这种情况可能传递的数据类型和实际的字段类型不一致，那么我们来看下具体的表结构。

root@localhost mysql.sock 5.7.28-log :[employees] 14:48:10>desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | varchar(14)   | NO   | MUL | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum("M","F") | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

表结构中看到该字段类型为varchar 类型，传递字段为整型，造成隐式转换不能走索引。

3）场景优化

该SQL可通过简单改写来避免出现隐式转换，如下：

select * from dt_t1 where emp_no="41680";

当传入数据是与匹配字段一致的varchar类型时，便可以正常使用到索引了，优化效果如下：

二、关联字段类型不一致造成隐式转换

除了常量匹配的查询场景，关联查询在关联字段不一致的情况下，也会出现隐式转换。

1) 待优化场景

SELECT  count(*) from t1  as aJOIN  `t2`  b on a.`id` = b.`alipay_order_no` ;

2）场景解析

从执行计划中可以看出被驱动表 b, Extra:Range checked for each record (index map: 0x8)

一般在当我们看到Range checked for each record (index map: 0x8) 的时候，可能就是发生了隐式转换，我们来看下官方文档是怎么解释的。

Range checked for each record (index map: N) (JSON property: message)

MySQL found no good index to use, but found that some of indexes might be used after column values from preceding tables are known. For each row combination in the preceding tables, MySQL checks whether it is possible to use a range or index_merge access method to retrieve rows. This is not very fast, but is faster than performing a join with no index at all. The applicability criteria are as described in Section 8.2.1.2, “Range Optimization”, and Section 8.2.1.3, “Index Merge Optimization”, with the exception that all column values for the preceding table are known and considered to be constants.

Indexes are numbered beginning with 1, in the same order as shown by SHOW INDEX for the table. The index map value N is a bitmask value that indicates which indexes are candidates. For example, a value of 0x19 (binary 11001) means that indexes 1, 4, and 5 will be considered.

查看下表结构：

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alipay_order_no` varchar(45) DEFAULT NULL,
  xxxx
  PRIMARY KEY (`id`),
  KEY `idx_alipay_order_no_temp` (`alipay_order_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2539968 DEFAULT CHARSET=utf8
共返回 1 行记录,花费 5 ms.
 CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL,
  xxxxxx
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8共返回 1 行记录,花费 5 ms.

我们从表结构上面进行观察到该关联字段数据一个是int 类型，一个是varchar 类型。

当发生这种场景的时候我们应该如何优化呢？

我们还回来看看下具体的执行计划，该驱动表为a,被驱动表b; 关联条件：a.id = b.alipay_order_no ; 当a 表的字段id 当为常数传递给b.alipay_order_no 的时候，发生column_type 不一致，无法使用索引，那么我们让a.id 传递的字段类型和b.alipay_order_no 保持一致，就可以使用索引了？

3）场景优化

我们可以对驱动表的关联字段进行显式的类型转换，让其与被驱动表关联字段类型一致。改写后SQL如下：

SELECT COUNT(*)
FROM `t1`  o
join `t2`  og  ON `o`.`def8`= `og`.`group_id`WHERE  o.`def1`= "DG21424956"

2）场景解析

从这个执行计划中我们可以看出第二列表og 中含有using join buffer (Block Nested Loop) ，TYpe=ALL .

一般这种情况下：using join buffer (Block Nested Loop) ，发生的情况是 a. 关联字段没有索引 b.发生隐式转换等

看下具体表结构：

create table t1(
     .....  
   `group_id` varchar(20) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `group_id` (`group_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
create table t2(
     .....  
    `def8` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tr_def1` (`def8`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

我们从表结构中可以看出关联字段都存在索引，但字符集是不一样的，t1 utf8，t2 utf8mb4.

3）场景优化

SQL改写思路和上例类似，我们对驱动表的关联字段进行字符集转换，如下：

SELECT COUNT(*)   FROM `t1`  o
left join `t2` og  ON CONVERT(  o.`def8`  USING utf8 ) = `og`.`group_id`WHERE  o.`def1`= "DG21424956

转换成一致的字符集之后，便可以通过索引进行关联了

三、校验规则不一致造成隐式转换

那么，只要保证操作符两侧数据类型以及字符集一致，就不会出现隐式转换吗？

答案是否定的，因为字符集还有一个很重要的属性，就是校验规则，当校验规则不一致的时候，也是会出现隐式转换行为的。具体看下面这个例子：

1) 待优化场景

SELECT *
FROM `t1`WHERE `uuid` in (SELECT uuid  FROM t2 WHERE project_create_at!= "0000-00-00 00:00:00")

该SQL执行计划如下：

2）场景解析

两张表的表结构如下：

CREATE TABLE `t1` (
   `id` int(11) NOT NULL AUTO_INCREMENT,  `
   uuid` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT "" COMMENT "UUID",
   xxxxxx
 PRIMARY KEY (`id`),
UNIQUE KEY `uuid_idx` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=2343994 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `t2` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `uuid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT "" COMMENT "项目uuid",
PRIMARY KEY (`id`),) ENGINE=InnoDB AUTO_INCREMENT=5408 DEFAULT CHARSET=utf8

我们从表结构看出，t1表作为被驱动表uuid是存在唯一索引的，并且关联字段数据类型以及字符集也都是一致的，但是校验规则的不同导致了这个场景无法使用到索引。

3）场景优化

我们可以通过如下改写，对驱动表关联字段的校验规则进行显示定义，让其与被驱动表一致

explain extended
select b.*
from (select  uuid COLLATE utf8_unicode_ci as uuid
from t1 where project_create_at != "0000-00-00 00:00:00") a, t2 b
where a.uuid = b.uuid
+--------------+-----------------------+--------------------+----------------+-----------------------+-------------------+---------------+----------------+-----------------------+
| id           | select_type           | table              | type           | key                   | key_len           | ref           | rows           | Extra                 |
+--------------+-----------------------+--------------------+----------------+-----------------------+-------------------+---------------+----------------+-----------------------+
| 1            | PRIMARY               | <derived2>         | ALL            |                       |                   |               | 51             |                       |
| 1            | PRIMARY               | b                  | eq_ref         | uuid_idx              | 386               | a.uuid        | 1              |                       |
| 2            | DERIVED               | volunteer_patients | range          | idx-project-create-at | 6                 |               | 51             | Using index condition |
+--------------+-----------------------+--------------------+----------------+-----------------------+-------------------+---------------+----------------+-----------------------+共返回 3 行记录,花费 4 ms.

可以看到，改写后的SQL，正常使用到索引进行字段关联，这样就达到了我们预期的效果。