數(shù)棧是云原生—站式數(shù)據(jù)中臺PaaS，我們在github和gitee上有一個有趣的開源項(xiàng)目：FlinkX，F(xiàn)linkX是一個基于Flink的批流統(tǒng)一的數(shù)據(jù)同步工具，既可以采集靜態(tài)的數(shù)據(jù)，也可以采集實(shí)時變化的數(shù)據(jù)，是全域、異構(gòu)、批流一體的數(shù)據(jù)同步引擎。大家喜歡的話請給我們點(diǎn)個star！star！star！

github開源項(xiàng)目：https://github.com/DTStack/flinkx

gitee開源項(xiàng)目：https://gitee.com/dtstack_dev_0/flinkx

MySQL是當(dāng)下最流行的關(guān)系型數(shù)據(jù)庫之一，互聯(lián)網(wǎng)高速發(fā)展的今天，MySQL數(shù)據(jù)庫在電商、金融等諸多行業(yè)的生產(chǎn)系統(tǒng)中被廣泛使用。

在實(shí)際的開發(fā)運(yùn)維過程中，想必大家也常常會碰到慢SQL的困擾。一條性能不好的SQL，往往會帶來過大的性能開銷，進(jìn)而引起整個操作系統(tǒng)資源的過度使用，甚至造成會話堆積，引發(fā)線上故障。

而在SQL調(diào)優(yōu)的場景中，一類比較常見的問題，就是隱式類型轉(zhuǎn)換。那什么是隱式轉(zhuǎn)換呢？

在MySQL中，當(dāng)操作符與不同類型的操作數(shù)一起使用時，會發(fā)生類型轉(zhuǎn)換以使操作數(shù)兼容，此時則會發(fā)生隱式轉(zhuǎn)換。出現(xiàn)隱式轉(zhuǎn)換，往往意味著SQL的執(zhí)行效率將大幅降低。

接下來筆者將結(jié)合幾大常見場景，讓大家實(shí)際體會什么是隱式轉(zhuǎn)換，以及如何去應(yīng)對出現(xiàn)隱式轉(zhuǎn)換的情況，請閱讀以下案例。

一、傳遞數(shù)據(jù)類型和字段類型不一致造成隱式轉(zhuǎn)換

一類比較經(jīng)典的場景就是傳遞數(shù)據(jù)類型和字段類型不一致造成的隱式轉(zhuǎn)換，這種場景也是我們平時最常遇到的。具體可以看下下面這個例子：

1) 待優(yōu)化場景

SQL及執(zhí)行計(jì)劃如下：

select * from dt_t1 where emp_no = 41680;

該表索引如下：

key idx_empno (`emp_no`)

2）場景解析

從執(zhí)行計(jì)劃中Type部分：ALL，全表掃描，而沒有走idx_empno索引, 一般這種情況可能傳遞的數(shù)據(jù)類型和實(shí)際的字段類型不一致，那么我們來看下具體的表結(jié)構(gòu)。

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)

表結(jié)構(gòu)中看到該字段類型為varchar 類型，傳遞字段為整型，造成隱式轉(zhuǎn)換不能走索引。

3）場景優(yōu)化

該SQL可通過簡單改寫來避免出現(xiàn)隱式轉(zhuǎn)換，如下：

select * from dt_t1 where emp_no='41680';

當(dāng)傳入數(shù)據(jù)是與匹配字段一致的varchar類型時，便可以正常使用到索引了，優(yōu)化效果如下：

二、關(guān)聯(lián)字段類型不一致造成隱式轉(zhuǎn)換

除了常量匹配的查詢場景，關(guān)聯(lián)查詢在關(guān)聯(lián)字段不一致的情況下，也會出現(xiàn)隱式轉(zhuǎn)換。

1) 待優(yōu)化場景

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

2）場景解析

從執(zhí)行計(jì)劃中可以看出被驅(qū)動表 b, Extra:Range checked for each record (index map: 0x8)

一般在當(dāng)我們看到Range checked for each record (index map: 0x8) 的時候，可能就是發(fā)生了隱式轉(zhuǎn)換，我們來看下官方文檔是怎么解釋的。

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.

查看下表結(jié)構(gòu)：

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 行記錄,花費(fèi) 5 ms. CREATE TABLE `t1` ( ?`id` bigint(20) NOT NULL, ?xxxxxx ?PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 共返回 1 行記錄,花費(fèi) 5 ms.

我們從表結(jié)構(gòu)上面進(jìn)行觀察到該關(guān)聯(lián)字段數(shù)據(jù) 一個是int 類型，一個是varchar 類型。

當(dāng)發(fā)生這種場景的時候我們應(yīng)該如何優(yōu)化呢？

我們還回來看看下具體的執(zhí)行計(jì)劃，該驅(qū)動表為a,被驅(qū)動表b; 關(guān)聯(lián)條件：a.id = b.alipay_order_no ; 當(dāng)a 表的字段id 當(dāng)為常數(shù)傳遞給b.alipay_order_no 的時候，發(fā)生column_type 不一致，無法使用索引，那么我們讓a.id 傳遞的 字段類型和b.alipay_order_no 保持一致，就可以使用索引了？

3）場景優(yōu)化

我們可以對驅(qū)動表的關(guān)聯(lián)字段進(jìn)行顯式的類型轉(zhuǎn)換，讓其與被驅(qū)動表關(guān)聯(lián)字段類型一致。改寫后SQL如下：

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

2）場景解析

從這個執(zhí)行計(jì)劃中我們可以看出第二列表og 中含有using join buffer (Block Nested Loop) ，TYpe=ALL .

一般這種情況下：using join buffer (Block Nested Loop) ，發(fā)生的情況是 a. 關(guān)聯(lián)字段沒有索引 b.發(fā)生隱式轉(zhuǎn)換 等

看下具體表結(jié)構(gòu)：

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

我們從表結(jié)構(gòu)中可以看出關(guān)聯(lián)字段都存在索引，但字符集是不一樣的，t1 utf8，t2 utf8mb4.

3）場景優(yōu)化

SQL改寫思路和上例類似，我們對驅(qū)動表的關(guān)聯(lián)字段進(jìn)行字符集轉(zhuǎn)換，如下：

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

轉(zhuǎn)換成一致的字符集之后，便可以通過索引進(jìn)行關(guān)聯(lián)了

三、校驗(yàn)規(guī)則不一致造成隱式轉(zhuǎn)換

那么，只要保證操作符兩側(cè)數(shù)據(jù)類型以及字符集一致，就不會出現(xiàn)隱式轉(zhuǎn)換嗎？

答案是否定的，因?yàn)樽址€有一個很重要的屬性，就是校驗(yàn)規(guī)則，當(dāng)校驗(yàn)規(guī)則不一致的時候，也是會出現(xiàn)隱式轉(zhuǎn)換行為的。具體看下面這個例子：

1) 待優(yōu)化場景

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

該SQL執(zhí)行計(jì)劃如下：

2）場景解析

兩張表的表結(jié)構(gòu)如下：

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 '項(xiàng)目uuid', PRIMARY KEY (`id`), ) ENGINE=InnoDB AUTO_INCREMENT=5408 DEFAULT CHARSET=utf8

我們從表結(jié)構(gòu)看出，t1表作為被驅(qū)動表uuid是存在唯一索引的，并且關(guān)聯(lián)字段數(shù)據(jù)類型以及字符集也都是一致的，但是校驗(yàn)規(guī)則的不同導(dǎo)致了這個場景無法使用到索引。

3）場景優(yōu)化

我們可以通過如下改寫，對驅(qū)動表關(guān)聯(lián)字段的校驗(yàn)規(guī)則進(jìn)行顯示定義，讓其與被驅(qū)動表一致

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 行記錄,花費(fèi) 4 ms.

可以看到，改寫后的SQL，正常使用到索引進(jìn)行字段關(guān)聯(lián)，這樣就達(dá)到了我們預(yù)期的效果。

四、總結(jié)

隱式轉(zhuǎn)換出現(xiàn)的場景主要有字段類型不一致、關(guān)聯(lián)字段類型不一致、字符集類型不一致或校對規(guī)則不一致等。當(dāng)出現(xiàn)隱式轉(zhuǎn)換帶來的SQL性能問題時，分析相應(yīng)場景對癥下藥即可。

除此之外，隱式轉(zhuǎn)換還可能會帶來查詢結(jié)果集不準(zhǔn)，字符集不一致也會造成主從同步報(bào)錯等，因此在實(shí)際使用時我們應(yīng)當(dāng)盡量避免。