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

更新時間：2021年08月12日 15:16:52 作者：CodeGo

本文主要介紹了MySQL隱式類型轉換導致索引失效的解決，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下

問題

在工作中發(fā)現，有一個接口只執(zhí)行一條SQL查詢語句，并且SQL明明使用了主鍵列，但是速度很慢。
在MySQL中EXPLAINN后發(fā)現，執(zhí)行時并沒有使用主鍵索引，而是進行了全表掃描。

復現

數據表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;

執(zhí)行下面的查詢語句，發(fā)現雖然 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|

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

隱式轉換

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

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

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

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

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

原因在于，MySQL 中字符串轉浮點型時的轉換規(guī)則，規(guī)則如下：

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在執(zhí)行上面的SQL語句時，會把每一行主鍵列的值轉換成浮點數（在主鍵上執(zhí)行了函數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|