Mysql5.6"隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的解決方法

這篇文章主要介紹了MySQL 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的解決方法，具有一定借鑒價(jià)值，需要的朋友可以參考下。希望大家閱讀完這篇文章后大有收獲。下面讓小編帶著大家一起了解一下。

成都創(chuàng)新互聯(lián)公司成立以來(lái)不斷整合自身及行業(yè)資源、不斷突破觀念以使企業(yè)策略得到完善和成熟，建立了一套“以技術(shù)為基點(diǎn)，以客戶(hù)需求中心、市場(chǎng)為導(dǎo)向”的快速反應(yīng)體系。對(duì)公司的主營(yíng)項(xiàng)目，如中高端企業(yè)網(wǎng)站企劃 / 設(shè)計(jì)、行業(yè) / 企業(yè)門(mén)戶(hù)設(shè)計(jì)推廣、行業(yè)門(mén)戶(hù)平臺(tái)運(yùn)營(yíng)、成都app軟件開(kāi)發(fā)、成都做手機(jī)網(wǎng)站、微信網(wǎng)站制作、軟件開(kāi)發(fā)、川西大數(shù)據(jù)中心等實(shí)行標(biāo)準(zhǔn)化操作，讓客戶(hù)可以直觀的預(yù)知到從成都創(chuàng)新互聯(lián)公司可以獲得的服務(wù)效果。

背景

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

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

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

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

分析

1. 從執(zhí)行結(jié)果來(lái)看，使用了單引號(hào)的走了對(duì)應(yīng)的索引。沒(méi)有使用單引號(hào)的沒(méi)有走索引，進(jìn)行了全表掃描。
2. 為什么會(huì)這樣呢？ mysql的優(yōu)化器怎么不直接進(jìn)行類(lèi)型轉(zhuǎn)換呢？

• 在SQL語(yǔ)句中單引號(hào)的引入也就是代表這個(gè)類(lèi)型是字符串?dāng)?shù)據(jù)類(lèi)型CHAR， VARCHAR， BINARY， VARBINARY， BLOB， TEXT， ENUM，和 SET。。
• 不加單引號(hào)也就代表這是一個(gè)字符串之外的類(lèi)型，如int，bigDecimal類(lèi)型等
• 如果給一串有字幕和特殊符號(hào)的字符串不加單引號(hào)，后果就是類(lèi)型轉(zhuǎn)換失敗導(dǎo)致SQl不能執(zhí)行。

如上圖所述：

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

我們先來(lái)看一下一條SQL的執(zhí)行過(guò)程

（網(wǎng)圖）

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

隱式轉(zhuǎn)換

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

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

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

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

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

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

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

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享Mysql 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的解決方法內(nèi)容對(duì)大家有幫助，同時(shí)也希望大家多多支持創(chuàng)新互聯(lián)，關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，遇到問(wèn)題就找創(chuàng)新互聯(lián)，詳細(xì)的解決方法等著你來(lái)學(xué)習(xí)!

標(biāo)題名稱(chēng)：Mysql5.6"隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的解決方法
網(wǎng)頁(yè)鏈接：http://muchs.cn/article34/gcecpe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁(yè)設(shè)計(jì)公司、品牌網(wǎng)站建設(shè)、全網(wǎng)營(yíng)銷(xiāo)推廣、定制開(kāi)發(fā)、網(wǎng)站策劃、企業(yè)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)