隱式轉(zhuǎn)換引起的sql慢查詢(xún)實(shí)戰(zhàn)記錄

引言

實(shí)在很無(wú)語(yǔ)呀，遇到一個(gè)mysql隱式轉(zhuǎn)換問(wèn)題，問(wèn)了周邊的dba大拿該問(wèn)題，他們居然反問(wèn)我，你連這個(gè)也不知道？白白跟他們混了那么長(zhǎng)   尼瑪，我還真不知道。罪過(guò)罪過(guò)…. 

問(wèn)題是這樣的，一個(gè)字段叫task_id, 本身是varchar字符串類(lèi)型，但是因?yàn)槔舷到y(tǒng)時(shí)間太長(zhǎng)了，我以為是int或者bigint，所以直接在代碼寫(xiě)sql跑數(shù)據(jù)，結(jié)果等了好久就是沒(méi)有反應(yīng)，感覺(jué)要壞事呀。在mysql processlist里看到了該sql語(yǔ)句，直接kill掉。 該字段是有索引的，并且他的sql選擇性很高，索引的價(jià)值也高。 但為什么這么慢？

分析問(wèn)題

通過(guò)explain分析出了結(jié)果，當(dāng)使用整型來(lái)查詢(xún)字符串的字段會(huì)出現(xiàn)無(wú)法走索引的情況，看下面可以知道，key為NULL，沒(méi)走索引，Rows是很大的數(shù)值，基本是全表掃描了。  當(dāng)正常的用字符串查詢(xún)字符串就很正常了，索引沒(méi)問(wèn)題，rows的值為1，這里說(shuō)的是掃描聚簇索引的rows，而不是索引二級(jí)索引。

那么為什么會(huì)出現(xiàn)這問(wèn)題？

下面是mysql官方給出的說(shuō)法， 最后一條很重要，當(dāng)在其他情況下，兩個(gè)參數(shù)都會(huì)統(tǒng)一成 float 來(lái)比較。 居然新版的mysql在優(yōu)化器層面已經(jīng)做了一些調(diào)整規(guī)避這問(wèn)題，但我自己的測(cè)試版本是mysql 5.6，阿里云用的也是5.7，都沒(méi)有解決該問(wèn)題。 看來(lái)是更高版本解決吧，這個(gè)待驗(yàn)證。

看完了官方解說(shuō)，我們知道上面那一句慢查詢(xún)sql，其實(shí)就相當(dāng)于 where to_int(taskid) = 516006380 。當(dāng)然直接用to_int是顯示轉(zhuǎn)換了，但是對(duì)比出來(lái)的效果是一致的。  不管是隱式轉(zhuǎn)換，還是顯示轉(zhuǎn)換，速度能起來(lái)才怪。。。 因?yàn)閙ysql不支持函數(shù)索引。

# xiaorui.cc
 
If both arguments in a comparison operation are strings, they are compared as strings.
If both arguments are integers, they are compared as integers.
Hexadecimal values are treated as binary strings if not compared to a number.
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
In all other cases, the arguments are compared as floating-point (real) numbers.

翻譯為中文就是：

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

總結(jié)

sql查詢(xún)的時(shí)候，字段的類(lèi)型要保持一致，不然會(huì)數(shù)據(jù)字段的隱式轉(zhuǎn)換，繼而出現(xiàn)慢查詢(xún)。 還是那句廢話，多看mysql的慢查詢(xún)?nèi)罩?，有你想要?

好了，以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。