問題

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

復(fù)現(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í)行語句時(shí)會(huì)對(duì)類型做轉(zhuǎn)換，應(yīng)該是在類型轉(zhuǎn)換后導(dǎo)致主鍵索引失效。

隱式轉(zhuǎn)換

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

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

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

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

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

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

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

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

2、以數(shù)字開頭的字符串轉(zhuǎn)換時(shí)會(huì)進(jìn)行截取，從第一個(gè)字符截取到第一個(gè)非數(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語句時(shí)，會(huì)把每一行主鍵列的值轉(zhuǎn)換成浮點(diǎn)數(shù)（在主鍵上執(zhí)行了函數(shù)CAST），再與條件參數(shù)做比較。在索引列上使用函數(shù)，會(huì)導(dǎo)致索引失效，所以最后導(dǎo)致了全表掃描。

我們只需要把前面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ù)是整數(shù)，會(huì)先轉(zhuǎn)換成浮點(diǎn)數(shù)，再全表掃描，導(dǎo)致索引失效；
2、條件參數(shù)要盡可能與列的類型相同，避免隱式轉(zhuǎn)換，或者在傳入的參數(shù)上執(zhí)行轉(zhuǎn)換函數(shù)，轉(zhuǎn)換成與索引列相同的類型。

參考

1、淺析 MySQL 的隱式轉(zhuǎn)換

到此這篇關(guān)于MySQL隱式類型轉(zhuǎn)換導(dǎo)致索引失效的解決的文章就介紹到這了,更多相關(guān)MySQL隱式類型轉(zhuǎn)換導(dǎo)致索引失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論