類型轉換對 MySQL 索引選擇的影響：隱式轉換導致索引失效的原理與避坑指南

核心現象

表 indextest(id PK, name VARCHAR(10) INDEX, age TINYINT INDEX, create_time TIMESTAMP INDEX)。
查詢 WHERE name = '111222'（字符串）走索引 idx_name；
查詢 WHERE name = 111222（數字）**全表掃描**，索引失效。

根因：MySQL 隱式類型轉換規則

當比較運算子兩側類型不一致時，MySQL 按以下優先級進行**隱式轉換**（參考文檔 Type Conversion in Expression Evaluation）：

1. 任一為 NULL → 結果 NULL（<=> 除外）。
2. 雙方均為字符串 → 字符串比較，**無轉換**。
3. 雙方均為整數 → 整數比較，**無轉換**。
4. 十六進制值與非數字比較 → 視為二進制串；與數字比較 → 按數字規則。
5. 任一為 TIMESTAMP/DATETIME，另為常量 → 常量轉 TIMESTAMP。
6. 任一為 DECIMAL：另為 DECIMAL/整數 → 轉 DECIMAL；另為浮點數 → 轉浮點數。
7. 所有其他情況 → 雙方均轉為浮點數 (DOUBLE) 比較。

為什麼 VARCHAR = 數字 會走全表掃描？

屬於第 7 類：**列值（字符串）與參數（數字）雙雙轉為浮點數比較**。

• 索引存儲的是**原始字符串**，按字典序有序。
• 轉為浮點數後，'111222'、'111222abc'、' 111222'、'11122.2e1' 等**無限多字符串**的浮點數值均等於 111222。
• 優化器無法在索引上定位「所有轉換後等於 111222 的字符串」，只能**放棄索引，全表掃描逐行轉換比較**。

反向驗證：數字列 = 字符串 仍能走索引

EXPLAIN SELECT name FROM indextest WHERE age = '30';
-- type=ref, key=idx_age, rows=1

原因：MySQL 將字符串 '30' **常量折疊**為確定整數 30，再在索引上精確定位，**列值無需轉換**，索引有效。

其他導致索引失效的隱式轉換場景

最佳實踐：杜絕隱式轉換

1. 應用層參數類型與列定義嚴格一致：DAO/ORM 映射時確保 VARCHAR 傳字符串，INT 傳整數。
2. SQL 中常量加引號：WHERE name = '111222' 而非 WHERE name = 111222。
3. 避免在索引列上使用函數/運算：將計算移至常量側（如日期範圍查詢）。
4. 顯式 CAST 而非依賴隱式轉換：WHERE CAST(col AS UNSIGNED) = 123 明確語義，雖同樣失效索引，但語義清晰、可被靜態分析工具識別。
5. 啟用 sql_mode=STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION：嚴格模式下部分隱式轉換會報警/報錯。

驗證工具：EXPLAIN + Optimizer Trace

EXPLAIN SELECT * FROM indextest WHERE name = 111222;
-- 觀察 type=ALL, key=NULL

SET SESSION optimizer_trace='enabled=on';
SELECT * FROM indextest WHERE name = 111222;
SELECT * FROM information_schema.optimizer_trace;
-- 可見 "condition_processing" 階段的 "constant_propagation" 與 "type_conversion" 細節

結語

MySQL 隱式類型轉換是**索引失效的隱形殺手**，尤其「字符串列比較數字」觸發雙向浮點數轉換，徹底摧毀 B+Tree 有序性。強制應用層類型一致、SQL 常量加引號、避免索引列函數運算、善用 EXPLAIN 與 Optimizer Trace 驗證，是每位開發者、DBA 的必修功課。記住：**索引列原樣參與比較，才是索引生效的前提**。