WHRER條件裡的資料型別必須和欄位資料型別一致

首先看案例：

表中欄位FPHONE_IMEI是varchar型別的，主鍵也建立在FPHONE_IMEI 欄位上，原則上只要where條件中用到了這個欄位，就會走索引，這也是建立索引的目的，可事實是這樣子麼。首先看下面這條查詢，初看，唯一值查詢走索引應該很快，實際上呢，從執行計劃可以看出，沒有使用到索引，而是全表掃描，所有這條查詢語句效能好不到哪裡。

看看實際的執行時間：

1.11s，超過一秒了我們定義為慢SQL，因此這條語句是有效能問題的。

再看下面這條查詢語句，就和預想的一樣，走主鍵索引，查詢很快，也是我們希望看到的結果。

看看執行時間：

0.01s,這個時間才是我們需要的。

從寫法上來看，上述兩條語句差不多，為何效能有如此大的差距呢，問題就在差不多上面，注意看仔細咯，看第一條語句，where條件中給定的值沒有引號，而FPHONE_IMEI 欄位是字串的，也就是兩邊型別不匹配，第二條語句型別是匹配的，因此沒有效能問題，所以導致上述差異的原因是型別不匹配。那問題來了：

1.為何型別不匹配會導致效能如何大差異呢

2.是不是型別不匹配就一定會導致效能問題呢。

請看下文：

mysql在做比較時，如果發現型別不匹配，會發生自動隱式型別轉換，是的，mysql是會自動隱式型別轉換，既然是轉換，那轉換的規則是怎樣的，規則如下：

1.如果一個或兩個引數都是空的，比較的結果為null。null與null比較，結果為null，無需進行轉換。

2.如果兩個比較的兩個引數都是string，按string比較，不做型別轉換。

3.如果兩個比較的兩個引數都是integer，按integer比較，不做型別轉換。

4.十六進位制的值和非數字做比較時，會被當做二進位制串來比較。

5.有一個引數是 TIMESTAMP 或 DATETIME，並且另外一個引數是常量，常量會被轉換為 timestamp。

6.有一個引數是 decimal 型別，如果另外一個引數是 decimal 或者整數，會將整數轉換為 decimal 後進行比較，如果另外一個引數是浮點數，則會把 decimal 轉換為浮點數進行比較

7.所有其他情況下，兩個引數都會被轉換為浮點數再進行比較。

 

說完轉換的規則，再說下索引，索引的作用是為了讓mysql根據查詢條件快速定位到相應的記錄，從而減少IO次數，達到快速返回結果的目的。但是如果mysql發現查詢條件無法滿足快速定位到記錄的時候，會放棄索引，而走全表掃描，因為它覺得全表掃描比走索引更優，當然這一系列操作都是mysql優化器決定。

 

再接著上面的兩條SQL語句說，從上述的規則來看，第一條SQL符合第七條規則，因此都需要轉換為浮點數再進行比較。那就看看mysql對具體的數值是如何進行轉換的:

我們可以看到，一個float數值可以由多個字串轉換而來，比如上面的1，發生轉換的可以有 1,'1','1xxx','  1'，對於’1xxx’這樣的，會發生截斷，非數字會丟棄。因此如果一個索引建立在string型別上，如果這個欄位和一個int型別的值比較，這時兩者同樣是轉換為float，但是因為索引是string的，而string轉換為同一個float的情況很多，同一個float值對應的string可能有很多，這就有點類似%string%的情況，mysql認為這種情況無法通過訪問索引來快速的定位記錄，索引的效果是訪問少而準，從而索引失效，進而走全表掃描，既然是全表掃描，這個效能是好不到哪裡去的，這也是第一條語句出現的問題，字串隱式轉換為float型別，從而導致索引失效，第一條語句出現的warnings而正是因為發生了隱式轉換導致欄位截斷而出現的,這也證實了隱式轉換的發生。而對於兩邊都是string的話，不發生型別轉換，而是直接比較，從而可以通過走索引進行快速查詢。

反過來又會怎樣呢，另外一種情況，當一個索引建立在int型別上，如果這個欄位和一個字串比較，會是怎麼樣的情況呢，同樣會發生型別轉換，兩邊都轉換為float型別，但是int型別轉換到float是唯一的，不會像上面情況一樣，不會存在可能很多記錄轉換為同一個值的情況，同時字串轉換為唯一的int型別值，因此是可以用到索引定位到具體記錄的（如果記錄存在），從而可以達到走索引的效果，快速響應查詢。看下面例子進一步說明：

表中x欄位是int型別，建立有索引，

第一條查詢語句，都是int型別比較，和預想一樣，走索引。

第二條查詢語句，和純數字字串比較，和我們上面說的轉換第二種情況一樣，同樣走索引。

第三條查詢語句，和混合字串比較，同樣我們上面說的轉換第二種情況一樣，同樣走索引，只是字串發生了截斷。

上述這個例子和我們說的第二種情況一致，發生型別轉換還是走索引。

我們再回到最前面提出的問題：

1.       為何型別不匹配會導致效能如何大差異

2.       是不是型別不匹配就一定會導致效能問題

相信上面的解讀已經很好的回答了這兩個問題。對1問題，型別不匹配，會導致發生隱式型別轉換，根據轉換的結果可能導致效能影響。對2問題，並不是發生型別轉換就一定導致效能問題，這個要看具體轉換的欄位，所以也不是一定的。但是我們在拼寫SQL語句的時候，不能只看到不一定，而是要做到一定不要，不要將型別不匹配的欄位進行比較，要保證WHRER條件裡的資料型別必須和欄位資料型別一致。

 

總結： where查詢條件中，務必保證提供的資料型別和表中欄位型別一致，而不是讓mysql發生隱式型別轉換，隱式轉換不一定按我們預想的來轉換，在一些轉換條件下會產生效能影響，而且往往這種情況下效能問題比較難以發現和定位

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22418990/viewspace-1302080/，如需轉載，請註明出處，否則將追究法律責任。