人妖在线一区,国产日韩欧美一区二区综合在线,国产啪精品视频网站免费,欧美内射深插日本少妇

新聞動(dòng)態(tài)

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

發(fā)布日期:2022-02-08 13:49 | 文章來(lái)源:站長(zhǎng)之家

問(wèn)題

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

復(fù)現(xiàn)

數(shù)據(jù)表DDL如下,使用 user_id 作為主鍵索引:

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

執(zhí)行下面的查詢(xún)語(yǔ)句,發(fā)現(xiàn)雖然 key 顯示使用了主鍵索引,但是 rows顯示掃描了全表,主鍵索引并沒(méi)有起作用:

 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)過(guò)排查發(fā)現(xiàn),數(shù)據(jù)表中 user_id 字段是 VARCHAR 類(lèi)型,SQL語(yǔ)句中 user_id是INT 類(lèi)型。MySQL 在執(zhí)行語(yǔ)句時(shí)會(huì)對(duì)類(lèi)型做轉(zhuǎn)換,應(yīng)該是在類(lèi)型轉(zhuǎn)換后導(dǎo)致主鍵索引失效。

隱式轉(zhuǎn)換

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

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

  • 如果一個(gè)或兩個(gè)參數(shù)均為NULL,則比較結(jié)果為NULL;但是 <=> 相等比較運(yùn)算符除外,對(duì)于NULL <=> NULL,結(jié)果為true,無(wú)需轉(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í)行此操作。為了安全起見(jiàn),在進(jìn)行比較時(shí),請(qǐng)始終使用完整的日期時(shí)間,日期或時(shí)間字符串。例如,要在將BETWEEN與日期或時(shí)間值一起使用時(shí)獲得最佳結(jié)果,請(qǐng)使用CAST()將這些值顯式轉(zhuǎn)換為所需的數(shù)據(jù)類(lèi)型。
  • 一個(gè)或多個(gè)表中的單行子查詢(xún)不視為常量。例如,如果子查詢(xún)返回的整數(shù)要與DATETIME值進(jìn)行比較,則比較將作為兩個(gè)整數(shù)完成,整數(shù)不轉(zhuǎn)換為時(shí)間值。參見(jiàn)上一條,這種情況下請(qǐng)使用CAST()將子查詢(xún)的結(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語(yǔ)句中,字符串與整數(shù)的比較會(huì)被轉(zhuǎn)換成兩個(gè)浮點(diǎn)數(shù)比較,左邊是字符串類(lèi)型 "1" 轉(zhuǎn)換成浮點(diǎn)數(shù)為1.0,右邊 INT類(lèi)型的 1 轉(zhuǎn)換成浮點(diǎn)數(shù) 1.0 。

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

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

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

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

2、以數(shù)字開(kāi)頭的字符串轉(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語(yǔ)句時(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ù)要盡可能與列的類(lèi)型相同,避免隱式轉(zhuǎn)換,或者在傳入的參數(shù)上執(zhí)行轉(zhuǎn)換函數(shù),轉(zhuǎn)換成與索引列相同的類(lèi)型。

參考

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

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

海外服務(wù)器租用

版權(quán)聲明:本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái),僅供學(xué)習(xí)參考,不代表本站立場(chǎng),如有內(nèi)容涉嫌侵權(quán),請(qǐng)聯(lián)系alex-e#qq.com處理。

相關(guān)文章

實(shí)時(shí)開(kāi)通

自選配置、實(shí)時(shí)開(kāi)通

免備案

全球線路精選!

全天候客戶(hù)服務(wù)

7x24全年不間斷在線

專(zhuān)屬顧問(wèn)服務(wù)

1對(duì)1客戶(hù)咨詢(xún)顧問(wèn)

在線
客服

在線客服:7*24小時(shí)在線

客服
熱線

400-630-3752
7*24小時(shí)客服服務(wù)熱線

關(guān)注
微信

關(guān)注官方微信
頂部