解決Mysql 5.6 "隱式轉換"導致的索引失效和資料不準確的問題

mysql影片教學欄位介紹解決Mysql 5.6索引失效和資料不準確問題

相關免費學習推薦：mysql影片教學

背景

• 在一次進行SQl查詢時，我試著對where條件中vachar類型的欄位去掉單引號查詢，這時候發現這篇本來應該很快的語句竟然很慢。這個varchar欄位有一個複合索引。其中的總條數有58989，甚至不加單引號查出來的資料不是我們想要的資料。
• 使用的是mysql 5.6版本，innoDB引擎實際狀況如下

#下面我們來看看執行的結果

##在上面的描述中我們還要注意就是，你的where條件的字串不加單引號必須是全數字。不然就會報錯

還有可能查出來的資料不是我們想要的資料。如下圖

分析

從執行結果來看，使用了單引號的走了對應的索引。沒有使用單引號的沒有走索引，進行了全表掃描。
1. 為什麼會這樣呢？ mysql的優化器怎麼不直接進行型別轉換呢？

在SQL語句中單引號的引入也就是代表這個型別是字串資料型別CHAR， VARCHAR，BINARY，VARBINARY， BLOB， TEXT， ENUM，和 SET。 。
• 不加單引號也就代表這是一個字串之外的類型，如int，bigDecimal類型等
• 如果給一串有字幕和特殊符號的字串不加單引號，後果就是型別轉換失敗導致SQl不能執行。

如上圖所述：

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

我們先來看一條SQL的執行過程

（網圖）

我們先得出結論：如果對索引欄位做函數操作（本例是cast函數做了隱式的轉換），可能會破壞索引值的有序性，因此優化器就決定放棄走樹搜尋功能。 （https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html）
• [外鏈圖片轉存失敗,來源站可能有防盜鏈機制,建議將圖片存檔（）或CONVERT（）轉換索引列，則MySQL可能無法有效使用索引。
• 查出來的資料不準確，也是因為隱式轉換，轉換後導致數值型別不一樣，導致不等變相等。
• 
  
隱含轉換

#1. 產生條件

當運算子與不同類型的操作數一起使用時，會發生類型轉換以使操作數相容。則會發生轉換隱式發生隱含轉換的條件：

兩個參數至少有一個是NULL 時，比較的結果也是NULL，例外是使用 對兩個NULL 做比較時會回傳1，這兩種情況都不需要做型別轉換

兩個參數都是字串，會依照字串來比較，不做型別轉換
1. 兩個參數都是整數，依照整數來比較，不做型別轉換
2. 十六進位的值和非數字做比較時，會被當做二進位字串
3. 有一個參數是TIMESTAMP 或DATETIME，並且另一個參數是常數，常數會被轉換為timestamp
4. 有一個參數是decimal 類型，如果另外一個參數是decimal 或整數，會將整數轉換為decimal 後進行比較，如果另外一個參數是浮點數，則會把decimal 轉換為浮點數進行比較
5. 所有其他情況下，兩個參數都會被轉換為浮點數再進行比較
2. 分析實際遇到的情況

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

• 由於屬於隱式轉換的其他情況，所以對比值都得轉換為浮點數進行比較
• 我們先將查詢條件值轉換為浮點數，再著將表格的記錄數值也得進行轉換，所以這個時候先前已經建立好的索引排序已經不能生效了。因為隱式轉換（函數）已經改變了原來的值，所以說優化器在這裡就直接不選用索引，直接使用全表掃描。
  

2.查詢出不符合的值（或部分符合的值），如上面的查詢結果。這真得看看原始碼了，這也就是MYsql的隱式轉換規則。這裡不就細分析了（因為沒有查到相關的文檔）
由於歷史原因，需要兼容舊的設計，可以使用 MySQL 的類型轉換函數 cast 和 convert，來明確的進行轉換。
總結

• 隱含轉換和函數的使用會導致索引失效和select出的資料不準確
• 隱含轉換的發生條件以及規則
• 隱含轉換導致索引失效的具體原因，由於需要將對比值都要進行型別轉換導致失效。
• 避免發生隱含型別轉換，隱含轉換的型別主要有欄位型別不一致、in 參數包含多個型別、字元集型別或校對規則不一致等

想了解更多程式設計學習，請關注php培訓欄位！

#

以上是解決Mysql 5.6 "隱式轉換"導致的索引失效和資料不準確的問題的詳細內容。更多資訊請關注PHP中文網其他相關文章！