关于sql:数栈SQL优化案例隐式转换

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

github开源我的项目：https://github.com/DTStack/fl...

gitee开源我的项目：https://gitee.com/dtstack_dev...

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索引, 个别这种状况可能传递的数据类型和理论的字段类型不统一，那么咱们来看下具体的表构造。

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 a
JOIN 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

共返回 3 行记录,破费 4 ms.

[点击并拖拽以挪动]

能够看到，改写后的SQL，失常应用到索引进行字段关联，这样就达到了咱们预期的成果。
四、总结

隐式转换呈现的场景次要有字段类型不统一、关联字段类型不统一、字符集类型不统一或校对规定不统一等。当呈现隐式转换带来的SQL性能问题时，剖析相应场景隔靴搔痒即可。

除此之外，隐式转换还可能会带来查问后果集不准，字符集不统一也会造成主从同步报错等，因而在理论应用时咱们该当尽量避免。