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類型時，便可以正常使用到索引了，優化效果如下：

關聯字段類型不一緻造成隐式轉換

除了常量比對的查詢場景，關聯查詢在關聯字段不一緻的情況下，也會出現隐式轉換。

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

從執行計劃中可以看出被驅動表 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 保持一緻，就可以使用索引了？

我們可以對驅動表的關聯字段進行顯式的類型轉換，讓其與被驅動表關聯字段類型一緻。改寫後SQL如下：

SELECT  count(*)
from `t1`a
 JOIN `t2` b on CAST( a.`id` AS CHAR ) = b.`alipay_order_no`           

進行改寫後就可以正常利用索引進行關聯了，執行計劃如下：

字元集不一緻造成隐式轉換

前面的兩種場景都是操作符兩側資料類型不同的情況，事實上，資料類型相同也可能會出現隐式轉換，比如下面這個字元集不一緻導緻隐式轉換的例子：

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

從這個執行計劃中我們可以看出第二清單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.

SQL改寫思路和上例類似，我們對驅動表的關聯字段進行字元集轉換，如下：

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

轉換成一緻的字元集之後，便可以通過索引進行關聯了

校驗規則不一緻造成隐式轉換

那麼，隻要保證操作符兩側資料類型以及字元集一緻，就不會出現隐式轉換嗎？

答案是否定的，因為字元集還有一個很重要的屬性，就是校驗規則，當校驗規則不一緻的時候，也是會出現隐式轉換行為的。具體看下面這個例子：

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

該SQL執行計劃如下：

兩張表的表結構如下：

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是存在唯一索引的，并且關聯字段資料類型以及字元集也都是一緻的，但是校驗規則的不同導緻了這個場景無法使用到索引。

我們可以通過如下改寫，對驅動表關聯字段的校驗規則進行顯示定義，讓其與被驅動表一緻

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性能問題時，分析相應場景對症下藥即可。

除此之外，隐式轉換還可能會帶來查詢結果集不準，字元集不一緻也會造成主從同步報錯等，是以在實際使用時我們應當盡量避免。