SQL 查詢中參數類型與列定義不匹配,會觸發 MySQL 隱式類型轉換,導致索引失效、全表掃描。本文通過實測 EXPLAIN 執行計劃,驗證 INT 列傳字符串、VARCHAR 列傳數值、IN 列表混合類型等場景的索引使用情況。
CREATE TABLE test_idx (
i INT(10) NOT NULL,
s VARCHAR(10) NOT NULL,
KEY idx_i (i),
KEY idx_s (s)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test_idx (i, s) VALUES
(1,'1'),(2,'2'),(3,'3'),(4,'4'),(5,'5'),
(6,'6'),(7,'7'),(8,'8'),(9,'9');
EXPLAIN SELECT * FROM test_idx WHERE i = 1;
EXPLAIN SELECT * FROM test_idx WHERE i = '1';
結果:兩條查詢均顯示 type=ref、key=idx_i、rows=1。
結論:數值類型列,參數加單引號字符串,MySQL 內部自動轉換為數值,**索引不失效**。
EXPLAIN SELECT * FROM test_idx WHERE s = 1;
EXPLAIN SELECT * FROM test_idx WHERE s = '1';
結果:
s = '1':type=ref、key=idx_s、rows=1 ✅s = 1:type=ALL、key=NULL、rows=9 ❌ 全表掃描,索引失效結論:字符類型列,參數**不加引號**傳數值,MySQL 將列值隱式轉換為數值比較,**導致索引失效**。
EXPLAIN SELECT * FROM test_idx WHERE i IN (1,2);
EXPLAIN SELECT * FROM test_idx WHERE i IN (1,'2');
結果:
IN (1,2):type=range、key=idx_i ✅IN (1,'2'):type=ALL、key=NULL ❌ 混合類型導致索引失效EXPLAIN SELECT * FROM test_idx WHERE s IN ('1','2');
EXPLAIN SELECT * FROM test_idx WHERE s IN (1,'2');
結果:
IN ('1','2'):type=range、key=idx_s ✅IN (1,'2'):type=ALL、key=NULL ❌ 混合類型導致索引失效MySQL 比較操作符(=、IN、<、> 等)兩側類型不一致時,遵循以下轉換規則:
WHERE varchar_col = 123
-- 等價於 WHERE CAST(varchar_col AS UNSIGNED) = 123
-- 列上加函數 → 索引失效
WHERE int_col = '123'
-- 等價於 WHERE int_col = CAST('123' AS UNSIGNED)
-- 常量轉換 → 索引生效
| 場景 | 錯誤寫法 | 正確寫法 | 風險等級 |
|---|---|---|---|
| VARCHAR/CHAR 列查詢 | WHERE col = 123 | WHERE col = '123' | 🔴 高(全表掃描) |
| IN 列表參數 | WHERE col IN (1, '2', 3) | WHERE col IN ('1', '2', '3') | 🔴 高 |
| JOIN 關聯字段類型不一致 | t1.int_col = t2.varchar_col | 統一類型或 CAST | 🔴 高 |
| 應用層框架自動綁定 | MyBatis #{id} 傳 Integer 對應 VARCHAR | 映射文件 jdbcType=VARCHAR 或傳 String | 🟡 中 |
| 數值列傳字符串 | WHERE int_col = '123' | WHERE int_col = 123(或保持字符串也可) | 🟢 低 |
type 不為 ALL/index,key 不為 NULLpt-query-digest 分析慢查詢,關注 Rows_examined / Rows_sent 比值sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 可部分暴露類型問題