解決Mysql 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的問題

mysql視頻教程欄目介紹解決Mysql 5.6索引失效和數(shù)據(jù)不準(zhǔn)確問題

解決Mysql 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的問題

相關(guān)免費(fèi)學(xué)習(xí)推薦:mysql視頻教程

背景

  • 在一次進(jìn)行SQl查詢時,我試著對where條件中vachar類型的字段去掉單引號查詢,這個時候發(fā)現(xiàn)這條本應(yīng)該很快的語句竟然很慢。這個varchar字段有一個復(fù)合索引。其中的總條數(shù)有58989,甚至不加單引號查出來的數(shù)據(jù)不是我們想要的數(shù)據(jù)。
  • 使用的是mysql 5.6版本,innoDB引擎 實(shí)際情況如下

下面我們來看一下執(zhí)行的結(jié)果

解決Mysql 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的問題

在上面的描述中我們還得注意就是,你的where條件的字符串不加單引號必須是全數(shù)字。不然就會報錯

解決Mysql 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的問題

還有可能查出來的數(shù)據(jù)不是我們想要的數(shù)據(jù)。如下圖

解決Mysql 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的問題

分析

  1. 從執(zhí)行結(jié)果來看,使用了單引號的走了對應(yīng)的索引。沒有使用單引號的沒有走索引,進(jìn)行了全表掃描。
  2. 為什么會這樣呢? mysql的優(yōu)化器怎么不直接進(jìn)行類型轉(zhuǎn)換呢?
  • 在SQL語句中單引號的引入也就是代表這個類型是字符串?dāng)?shù)據(jù)類型CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM,和 SET。。
  • 不加單引號也就代表這是一個字符串之外的類型,如int,bigDecimal類型等
  • 如果給一串有字幕和特殊符號的字符串不加單引號,后果就是類型轉(zhuǎn)換失敗導(dǎo)致SQl不能執(zhí)行。

如上圖所述:

1054?-?Unknown?column?'000w1993521'?in?'where?clause',?Time:?0.008000s

我們先來看一下一條SQL的執(zhí)行過程

解決Mysql 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的問題

(網(wǎng)圖)

  • 我們先得出結(jié)論:如果對索引字段做函數(shù)操作(本例是cast函數(shù)做了隱式的轉(zhuǎn)換),可能會破壞索引值的有序性,因此優(yōu)化器就決定放棄走樹搜索功能。(https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html)
  • [外鏈圖片轉(zhuǎn)存失敗,源站可能有防盜鏈機(jī)制,建議將圖片保存下來直接上傳(img-l5AwT0xu-1607244327891)(http://note.youdao.com/yws/res/23689/CE6F785994E6476D816B23787CE65217)]
  • 意思也就是:請注意,如果您使用BINARY,CAST()或CONVERT()轉(zhuǎn)換索引列,則MySQL可能無法有效使用索引。
  • 查出來的數(shù)據(jù)不準(zhǔn)確,也是因為隱式轉(zhuǎn)換,轉(zhuǎn)換后導(dǎo)致數(shù)值類型不一樣,導(dǎo)致不等變?yōu)橄嗟取?

隱式轉(zhuǎn)換

1. 產(chǎn)生條件
當(dāng)操作符與不同類型的操作數(shù)一起使用時,會發(fā)生類型轉(zhuǎn)換以使操作數(shù)兼容。則會發(fā)生轉(zhuǎn)換隱式
發(fā)生隱式轉(zhuǎn)換的條件:

  1. 兩個參數(shù)至少有一個是 NULL 時,比較的結(jié)果也是 NULL,例外是使用 對兩個 NULL 做比較時會返回 1,這兩種情況都不需要做類型轉(zhuǎn)換
  2. 兩個參數(shù)都是字符串,會按照字符串來比較,不做類型轉(zhuǎn)換
  3. 兩個參數(shù)都是整數(shù),按照整數(shù)來比較,不做類型轉(zhuǎn)換
  4. 十六進(jìn)制的值和非數(shù)字做比較時,會被當(dāng)做二進(jìn)制串
  5. 有一個參數(shù)是 TIMESTAMP 或 DATETIME,并且另外一個參數(shù)是常量,常量會被轉(zhuǎn)換為 timestamp
  6. 有一個參數(shù)是 decimal 類型,如果另外一個參數(shù)是 decimal 或者整數(shù),會將整數(shù)轉(zhuǎn)換為 decimal 后進(jìn)行比較,如果另外一個參數(shù)是浮點(diǎn)數(shù),則會把 decimal 轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較
  7. 所有其他情況下,兩個參數(shù)都會被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較

2. 分析實(shí)際遇到的情況

1.那我們也就清楚了,上面我提出的例子是整數(shù)和字符串的比較,那就屬于其他情況了。那我們就先來分析一下索引失效的原因

  • 由于屬于隱式轉(zhuǎn)換的其他情況,所以對比值都得轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較
  • 我們先將查詢條件值進(jìn)行轉(zhuǎn)換為浮點(diǎn)數(shù),再著將表的記錄值也得進(jìn)行轉(zhuǎn)換,所以這個時候此前已經(jīng)創(chuàng)建好的索引排序已經(jīng)不能生效了。因為隱式轉(zhuǎn)換(函數(shù))已經(jīng)改變了原來的值,所以說優(yōu)化器在這里就直接不選用索引,直接使用全表掃描。

2.查詢出不匹配的值(或者說是部分匹配的值),如上面的查詢結(jié)果。這真得看看源碼了,這也就是MYsql的隱式轉(zhuǎn)換規(guī)則。這里不就細(xì)分析了(因為沒有查到相關(guān)的文檔)
由于歷史原因,需要兼容舊的設(shè)計,可以使用 MySQL 的類型轉(zhuǎn)換函數(shù) cast 和 convert,來明確的進(jìn)行轉(zhuǎn)換。
總結(jié)

  • 隱式轉(zhuǎn)換和函數(shù)的使用會導(dǎo)致索引失效和select出的數(shù)據(jù)不準(zhǔn)確
  • 隱式轉(zhuǎn)換的發(fā)生條件以及規(guī)則
  • 隱式轉(zhuǎn)換導(dǎo)致索引失效的具體原因,由于需要將對比值都要進(jìn)行類型轉(zhuǎn)換導(dǎo)致失效。
  • 避免發(fā)生隱式類型轉(zhuǎn)換,隱式轉(zhuǎn)換的類型主要有字段類型不一致、in 參數(shù)包含多個類型、字符集類型或校對規(guī)則不一致等

想了解更多編程學(xué)習(xí),敬請關(guān)注mysql視頻教程欄目!

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊6 分享