MySQL隱式類型轉換導致索引失效的解決

Posted on by

問題

在工作中發現，有一個接口隻執行一條SQL查詢語句，並且SQL明明使用瞭主鍵列，但是速度很慢。
在MySQL中EXPLAINN後發現，執行時並沒有使用主鍵索引，而是進行瞭全表掃描。

復現

數據表DDL如下，使用 user_id 作為主鍵索引： 

 CREATE TABLE `user_message` (
   `user_id` varchar(50) NOT NULL COMMENT '用戶ID',
   `msg_id` int(11) NOT NULL COMMENT '消息ID',
   PRIMARY KEY (`user_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

執行下面的查詢語句，發現雖然 key 顯示使用瞭主鍵索引，但是 rows顯示掃描瞭全表，主鍵索引並沒有起作用： 

 EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = 1;
 ​
 id|select_type|table       |partitions|type |possible_keys|key    |key_len|ref|rows |filtered|Extra                   |
 --+-----------+------------+----------+-----+-------------+-------+-------+---+-----+--------+------------------------+
  1|SIMPLE     |user_message|          |index|PRIMARY      |PRIMARY|206    |   |10000|    10.0|Using where; Using index|

經過排查發現，數據表中 user_id 字段是 VARCHAR 類型，SQL語句中 user_id是INT 類型。MySQL 在執行語句時會對類型做轉換，應該是在類型轉換後導致主鍵索引失效。

隱式轉換

MySQL 的官方文檔：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html，介紹瞭 MySQL類型隱式轉換的規則：

當算子兩邊的操作數類型不一致時，MySQL會發生類型轉換以使操作數兼容，這些轉換是隱式發生的。下面描述瞭比較操作的隱式轉換：

  • 如果一個或兩個參數均為NULL，則比較結果為NULL；但是 <=> 相等比較運算符除外，對於NULL <=> NULL，結果為true，無需轉換。
  • 如果比較操作中的兩個參數都是字符串，則將它們作為字符串進行比較。
  • 如果兩個參數都是整數，則將它們作為整數進行比較。
  • 如果不將十六進制值與數字進行比較，則將其視為二進制字符串。
  • 如果參數之一是TIMESTAMP或DATETIME列，而另一個參數是常量，則在執行比較之前，該常量將轉換為時間戳。對於IN() 的參數不執行此操作。為瞭安全起見，在進行比較時，請始終使用完整的日期時間，日期或時間字符串。例如，要在將BETWEEN與日期或時間值一起使用時獲得最佳結果，請使用CAST()將這些值顯式轉換為所需的數據類型。
  • 一個或多個表中的單行子查詢不視為常量。例如，如果子查詢返回的整數要與DATETIME值進行比較，則比較將作為兩個整數完成，整數不轉換為時間值。參見上一條，這種情況下請使用CAST()將子查詢的結果整數值轉換為DATETIME。
  • 如果參數之一是十進制值，則比較取決於另一個參數。如果另一個參數是十進制或整數值，則將參數作為十進制值進行比較；如果另一個參數是浮點值，則將參數作為浮點值進行比較。
  • 在所有其他情況下，將參數作為浮點數（實數）進行比較。例如，將字符串和數字操作數進行比較，將其作為浮點數的比較。

根據上述規則的最後一條，在前面的SQL語句中，字符串與整數的比較會被轉換成兩個浮點數比較，左邊是字符串類型 “1” 轉換成浮點數為1.0，右邊 INT類型的 1 轉換成浮點數 1.0 。

按理說，兩邊都是浮點數，那麼應該能使用索引，為什麼執行時沒有使用到？

原因在於，MySQL 中字符串轉浮點型時的轉換規則，規則如下：

1、不以數字開頭的字符串都將轉換為0： 

 SELECT CAST('abc' AS UNSIGNED)
 ​
 CAST('abc' AS UNSIGNED)|
 -----------------------+
                       0|

2、以數字開頭的字符串轉換時會進行截取，從第一個字符截取到第一個非數字內容為止： 

 SELECT CAST(' 0123abc' AS UNSIGNED)
 ​
 CAST(' 0123abc' AS UNSIGNED)|
 ----------------------------+
                          123|

所以，在 MySQL 裡 “1”、 ” 1″、”1a” 、”01″這樣的字符串轉成數字後都是 1 。

MySQL在執行上面的SQL語句時，會把每一行主鍵列的值轉換成浮點數（在主鍵上執行瞭函數CAST），再與條件參數做比較。在索引列上使用函數，會導致索引失效，所以最後導致瞭全表掃描。

我們隻需要把前面SQL中傳入的參數改為字符串，就可以使用到主鍵索引： 

 EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = '1';
 ​
 id|select_type|table       |partitions|type|possible_keys|key    |key_len|ref  |rows|filtered|Extra      |
 --+-----------+------------+----------+----+-------------+-------+-------+-----+----+--------+-----------+
  1|SIMPLE     |user_message|          |ref |PRIMARY      |PRIMARY|202    |const| 135|   100.0|Using index|

總結

1、條件列是字符串時，如果傳入的條件參數是整數，會先轉換成浮點數，再全表掃描，導致索引失效；
2、條件參數要盡可能與列的類型相同，避免隱式轉換，或者在傳入的參數上執行轉換函數，轉換成與索引列相同的類型。

參考

1、淺析 MySQL 的隱式轉換

到此這篇關於MySQL隱式類型轉換導致索引失效的解決的文章就介紹到這瞭,更多相關MySQL隱式類型轉換導致索引失效內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：