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

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 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`a
 JOIN `t2` b on CAST(a.`id` AS CHAR) = b.`alipay_order_no`

进行改写后就能够失常利用索引进行关联了，执行打算如下：

后面的两种场景都是操作符两侧数据类型不同的状况，事实上，数据类型雷同也可能会呈现隐式转换，比方上面这个字符集不统一导致隐式转换的例子：

1) 待优化场景

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，失常应用到索引进行字段关联，这样就达到了咱们预期的成果。

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

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

更多技术相干文章，请关注公众号“云掣 YUNCHE”
也可到官网进行查看 https://www.dtstack.com/dtsmart/

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

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

关联字段类型不统一造成隐式转换

字符集不统一造成隐式转换

校验规定不统一造成隐式转换

总结