MySQL隱式類型轉(zhuǎn)換導致索引失效的解決

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

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

問題

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

復現(xiàn)

數(shù)據(jù)表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ā)現(xiàn)雖然 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|

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

隱式轉(zhuǎn)換

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

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

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

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

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

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

1、不以數(shù)字開頭的字符串都將轉(zhuǎn)換為0：

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

2、以數(shù)字開頭的字符串轉(zhuǎn)換時會進行截取，從第一個字符截取到第一個非數(shù)字內(nèi)容為止：

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

所以，在 MySQL 里 "1"、 " 1"、"1a" 、"01"這樣的字符串轉(zhuǎn)成數(shù)字后都是 1 。

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

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

 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|

總結(jié)

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