萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 索引失效實測:數值類型加引號 vs 字符類型不加引號的隱式轉換陷阱

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');

測試 1:INT 列傳字符串參數

EXPLAIN SELECT * FROM test_idx WHERE i = 1;
EXPLAIN SELECT * FROM test_idx WHERE i = '1';

結果:兩條查詢均顯示 type=ref、key=idx_i、rows=1。

結論:數值類型列,參數加單引號字符串,MySQL 內部自動轉換為數值,**索引不失效**。

測試 2:VARCHAR 列傳數值參數

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 將列值隱式轉換為數值比較,**導致索引失效**。

測試 3:IN 列表混合類型(INT 列)

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 ❌ 混合類型導致索引失效

測試 4:IN 列表混合類型(VARCHAR 列)

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、<、> 等)兩側類型不一致時,遵循以下轉換規則:

  1. 若一側為數值,另一側為字符串 → **字符串轉數值**
  2. 轉換發生在**列值**上時,**索引失效**;發生在**參數常量**上,**索引生效**
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 = 123WHERE 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(或保持字符串也可)🟢 低

排查工具與習慣

  • 開發階段:所有 SQL 必須 EXPLAIN,檢查 type 不為 ALL/index,key 不為 NULL
  • 代碼審查:檢查 Mapper/DAO 參數類型與表結構一致性
  • 線上監控:pt-query-digest 分析慢查詢,關注 Rows_examined / Rows_sent 比值
  • 配置嚴格模式:sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 可部分暴露類型問題
copyright © 萬盛學電腦網 all rights reserved