MySQL“索引失效”的隱形殺手：隱式類型轉(zhuǎn)換，你了解多少？
今天這篇文章再介紹另外一種非常隱蔽，但又很容易導(dǎo)致索引失效的場(chǎng)景：隱式類型轉(zhuǎn)換。隱式類型轉(zhuǎn)換使用不當(dāng)，輕則導(dǎo)致索引失效，性能急劇下降，重則會(huì)導(dǎo)致SQL語(yǔ)句未查詢到預(yù)期的結(jié)果。
什么是隱式類型轉(zhuǎn)換？
MySQL中的隱式類型轉(zhuǎn)換規(guī)則是在查詢或操作（如比較、函數(shù)調(diào)用等）中，涉及到不同數(shù)據(jù)類型時(shí)發(fā)生的自動(dòng)轉(zhuǎn)換行為。
如果參與操作的表達(dá)式或列的數(shù)據(jù)類型不匹配，MySQL會(huì)根據(jù)數(shù)據(jù)類型的上下文自動(dòng)進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換以適配預(yù)期的數(shù)據(jù)類型。這種行為對(duì)性能和結(jié)果有時(shí)會(huì)有較大的影響，比如索引可能失效或比較結(jié)果出現(xiàn)意外。
隱式類型轉(zhuǎn)換典型案例
在進(jìn)一步介紹隱式類型轉(zhuǎn)換的詳細(xì)規(guī)則之前，我們先來(lái)看兩個(gè)比較典型的案例，這里采用的MySQL 8.0.37版本。
場(chǎng)景一：未獲得預(yù)期數(shù)據(jù)，且索引失效
創(chuàng)建&初始化示例表
表結(jié)構(gòu)及數(shù)據(jù)如下：
-- tb_type_change
CREATETABLE`tb_type_change` (
`col1`varchar(255) NOTNULLDEFAULT'',
`col2`intNOTNULLDEFAULT'0',
KEY`idx_c1` (`col1`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
-- 插入一些數(shù)據(jù)
insertinto tb_type_change values('1234567890123456789',1);
insertinto tb_type_change values('123456789012345678',2);
insertinto tb_type_change values('123456789012345677',3);
insertinto tb_type_change values('12345678901234567',4);
insertinto tb_type_change values('12345678901234568',5);
insertinto tb_type_change values('123456789012345',6);查詢所有數(shù)據(jù)：
mysql> select * from tb_type_change;
+---------------------+------+
| col1 | col2 |
+---------------------+------+
| 1234567890123456789 | 1 |
| 123456789012345678 | 2 |
| 123456789012345677 | 3 |
| 12345678901234567 | 4 |
| 12345678901234568 | 5 |
| 123456789012345 | 6 |
+---------------------+------+未獲得預(yù)期數(shù)據(jù)示例
執(zhí)行如下SQL語(yǔ)句：
mysql> select * from tb_type_change where col1 = 123456789012345678;
+--------------------+------+
| col1 | col2 |
+--------------------+------+
| 123456789012345678 | 2 |
| 123456789012345677 | 3 |
+--------------------+------+通過(guò)上面的查詢可以看到，SQL語(yǔ)句正常執(zhí)行，但查詢的結(jié)果并不是預(yù)期的結(jié)果。查詢條件是123456789012345678，但結(jié)構(gòu)中竟然包含了“123456789012345677”。這就是因?yàn)殡[式類型轉(zhuǎn)換導(dǎo)致未獲得預(yù)期數(shù)據(jù)。
原因分析
在 MySQL 中，當(dāng)查詢條件中的數(shù)據(jù)類型與列的數(shù)據(jù)類型不匹配時(shí)，會(huì)發(fā)生隱式類型轉(zhuǎn)換。隱式類型轉(zhuǎn)換通常會(huì)將查詢條件類型轉(zhuǎn)換為列的類型或反之。如果轉(zhuǎn)換過(guò)程中出現(xiàn)精度丟失或者未精確匹配，就可能導(dǎo)致查詢結(jié)果不符合預(yù)期。
在上述場(chǎng)景中，表 tb_type_change 中列 col1 的類型是 VARCHAR，但查詢條件 123456789012345678 是一個(gè)數(shù)字（BIGINT 型）。MySQL 會(huì)嘗試將 VARCHAR 列的數(shù)據(jù)轉(zhuǎn)換為 BIGINT 類型以進(jìn)行比較。
- BIGINT 的范圍是 -9223372036854775808 到 9223372036854775807，但在比較時(shí)，MySQL 會(huì)將col1的值從字符串轉(zhuǎn)換為數(shù)字。
- 轉(zhuǎn)換過(guò)程中，如果 col1 的字符串值超過(guò) BIGINT 的最大精度范圍，MySQL 會(huì)截?cái)嗷騺G失部分精度，使得原始字符串被轉(zhuǎn)換為近似的 BIGINT 值。
在上述示例中，關(guān)于類型的轉(zhuǎn)換：
- '123456789012345677' 轉(zhuǎn)換為 123456789012345678（數(shù)字，伴隨一定的舍入）。
- '123456789012345678' 轉(zhuǎn)換為 123456789012345678（數(shù)字）。
因此，上述SQL語(yǔ)句查詢出了兩個(gè)結(jié)果。
場(chǎng)景二：隱式類型轉(zhuǎn)換，索引失效
創(chuàng)建&初始化示例表
新創(chuàng)建一個(gè)表以及插入一些數(shù)據(jù)：
CREATE TABLEtest (
col1 VARCHAR(255) NOTNULLDEFAULT'',
col2 INTNOTNULL,
KEY idx_col1 (col1), -- 對(duì)字符串列 col1 建立了索引
KEY idx_col2 (col2) -- 對(duì)整數(shù)列 col2 建立了索引
);
INSERTINTOtest (col1, col2) VALUES
('123', 123),
('456', 456),
('789', 789),
('abc', 111),
('xyz', 222);未走索引，全表掃描示例
執(zhí)行如下SQL語(yǔ)句：
mysql> EXPLAIN SELECT * FROMtestWHERE col1 = 123 \G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: idx_col1
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 20.00
Extra: Usingwhere會(huì)發(fā)現(xiàn)上面的SQL語(yǔ)句，并沒(méi)有走idx_col1索引，而是進(jìn)行了全表掃描。
正常使用索引示例
再執(zhí)行另外一個(gè)SQL語(yǔ)句：
mysql> EXPLAIN SELECT * FROMtestWHERE col2 = '123' \G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: idx_col2
key: idx_col2
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL此時(shí)，針對(duì)col2列的查詢，正確使用了idx_col2索引。
原因分析
在上面的示例中：
- 當(dāng)字段類型為字符串類型，參數(shù)為整型時(shí)，會(huì)導(dǎo)致索引失效；
- 而字段類型為整型，傳入的參數(shù)為字符串類型時(shí)，不會(huì)導(dǎo)致索引失效；
這是因?yàn)樵谧址c數(shù)字進(jìn)行比較時(shí)，MySQL會(huì)將字符串類型轉(zhuǎn)換為數(shù)字進(jìn)行比較，因此當(dāng)字段類型為字符串時(shí)，會(huì)在字段上加函數(shù)，而導(dǎo)致索引失效。
MySQL隱式類型轉(zhuǎn)換規(guī)則
下面我們整理一些在MySQL當(dāng)中常見(jiàn)的隱式類型轉(zhuǎn)換規(guī)則。
字符串與數(shù)字
當(dāng)一個(gè)字符串與一個(gè)數(shù)字進(jìn)行比較時(shí)，字符串會(huì)被轉(zhuǎn)換為一個(gè)數(shù)字。轉(zhuǎn)換是基于字符串的數(shù)值前綴。如果字符串沒(méi)有數(shù)值前綴，則轉(zhuǎn)換結(jié)果為 0。上面的案例中，便是字符串轉(zhuǎn)數(shù)字的場(chǎng)景之一。
示例：
SELECT '6' = 6; -- 返回 1（TRUE），因?yàn)樽址?'6' 被轉(zhuǎn)換為數(shù)字 6。
SELECT '6a' = 6; -- 返回 1（TRUE），因?yàn)樽址?'6a' 在轉(zhuǎn)換時(shí)被認(rèn)定為數(shù)字 6。不同類型的數(shù)值
不同類型的數(shù)值（例如 INT 和 DOUBLE）在比較時(shí)會(huì)轉(zhuǎn)換為精度更高的數(shù)值類型。
示例：
SELECT 5 = 5.0; -- 返回 1（TRUE），整型 5 轉(zhuǎn)換為浮點(diǎn)數(shù) 5.0 進(jìn)行比較。數(shù)值與日期比較
日期格式的數(shù)據(jù)和整型比較時(shí)會(huì)將整型轉(zhuǎn)化為日期格式，但是日期格式的字符串和整型比較會(huì)將日期字符串轉(zhuǎn)化為整型。
SELECT CAST('20230101' as date)=20230101; -- 返回 1（TRUE）
SELECT DATE'2023-01-01' =20230101; -- 返回 1（TRUE）
SELECT '2023-01-01'=20230101; -- 返回 0（FALSE）
SELECT '2023-01-01'=2023; -- 返回 1（TRUE）其他規(guī)則
- 兩個(gè)參數(shù)至少有一個(gè)是NULL時(shí)，比較的結(jié)果也是 NULL，例外是使用<=>對(duì)兩個(gè)NULL做比較時(shí)會(huì)返回 1，這兩種情況都不需要做類型轉(zhuǎn)換；
- 兩個(gè)參數(shù)都是字符串，會(huì)按照字符串來(lái)比較，不做類型轉(zhuǎn)換；
- 兩個(gè)參數(shù)都是整數(shù)，按照整數(shù)來(lái)比較，不做類型轉(zhuǎn)換；
- 十六進(jìn)制的值和非數(shù)字做比較時(shí)，會(huì)被當(dāng)做二進(jìn)制串；
- 有一個(gè)參數(shù)是TIMESTAMP或DATETIME，并且另外一個(gè)參數(shù)是常量，常量會(huì)被轉(zhuǎn)換為TIMESTAMP；
- 有一個(gè)參數(shù)是decimal類型，如果另外一個(gè)參數(shù)是decimal或者整數(shù)，會(huì)將整數(shù)轉(zhuǎn)換為decimal后進(jìn)行比較，如果另外一個(gè)參數(shù)是浮點(diǎn)數(shù)，則會(huì)把decimal轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較；
- 同一類型內(nèi)部的轉(zhuǎn)換。例如，比較TINYINT和BIGINT時(shí)，TINYINT會(huì)被轉(zhuǎn)換為BIGINT。
- 使用BLOB或TEXT類型時(shí)，應(yīng)盡量避免使用不同類型的字面值，因?yàn)檫@可能導(dǎo)致意外的類型轉(zhuǎn)換或比較結(jié)果。
小結(jié)
隱式轉(zhuǎn)換的類型主要有字段類型不一致、IN參數(shù)包含多個(gè)類型、字符集類型或校對(duì)規(guī)則不一致等。數(shù)據(jù)庫(kù)在進(jìn)行隱式轉(zhuǎn)換時(shí)，如果轉(zhuǎn)換無(wú)法正常進(jìn)行或產(chǎn)生了錯(cuò)誤的結(jié)果，可能會(huì)影響查詢的準(zhǔn)確性和性能。因此，在設(shè)計(jì)數(shù)據(jù)庫(kù)和編寫 SQL 查詢時(shí)，需仔細(xì)甄別，最好顯式指定所需的數(shù)據(jù)類型，以避免潛在的問(wèn)題。
