最近一周接連處理了2個由于int向varchar轉換無法使用索引，從而引發(fā)的慢查詢。
CREATE?TABLE?`appstat_day_prototype_201305`?( `day_key`?date?NOT?NULL?DEFAULT?'1900-01-01', `appkey`?varchar(20)?NOT?NULL?DEFAULT?'', `user_total`?bigint(20)?NOT?NULL?DEFAULT?'0', `user_activity`?bigint(20)?NOT?NULL?DEFAULT?'0', `times_total`?bigint(20)?NOT?NULL?DEFAULT?'0', `times_activity`?bigint(20)?NOT?NULL?DEFAULT?'0', `incr_login_daily`?bigint(20)?NOT?NULL?DEFAULT?'0', `unbind_total`?bigint(20)?NOT?NULL?DEFAULT?'0', `unbind_activitys`?bigint(20)?NOT?NULL?DEFAULT?'0', PRIMARY?KEY?(`appkey`,`day_key`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8 mysql>?explain?SELECT?*?from?appstat_day_prototype_201305?where?appkey?=?xxxxx?and?day_key?between?'2013-05-23'?and?'2013-05-30'; +----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+ |?id?|?select_type?|?table?|?type?|?possible_keys?|?key?|?key_len?|?ref?|?rows?|?Extra?| +----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+ |?1?|?SIMPLE?|?appstat_day_prototype_201305?|?ALL?|?PRIMARY?|?NULL?|?NULL?|?NULL?|?19285787?|?Using?where?| +----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+ 1?row?in?set?(0.00?sec) mysql>?explain?SELECT?*?from?appstat_day_prototype_201305?where?appkey?=?'xxxxx'?and?day_key?between?'2013-05-23'?and?'2013-05-30'; +----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+ |?id?|?select_type?|?table?|?type?|?possible_keys?|?key?|?key_len?|?ref?|?rows?|?Extra?| +----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+ |?1?|?SIMPLE?|?appstat_day_prototype_201305?|?range?|?PRIMARY?|?PRIMARY?|?65?|?NULL?|?1?|?Using?where?| +----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+ 1?row?in?set?(0.00?sec)
從上面可以很明顯的看到由于appkey是varchar，而在where條件中不加”，會引發(fā)全表查詢，加了就可以用到索引，這掃描的行數可是天差地別，對于服務器的壓力和響應時間自然也是天差地別的。
我們再看另外一個例子：
***************************?1.?row?*************************** Table:?poll_joined_151 Create?Table:?CREATE?TABLE?`poll_joined_151`?( `poll_id`?bigint(11)?NOT?NULL, `uid`?bigint(11)?NOT?NULL, `item_id`?varchar(60)?NOT?NULL, `add_time`?int(11)?NOT?NULL?DEFAULT?'0', `anonymous`?tinyint(1)?NOT?NULL?DEFAULT?'0', `sub_item`?varchar(1200)?NOT?NULL?DEFAULT?'', KEY?`idx_poll_id_uid_add_time`?(`poll_id`,`uid`,`add_time`), KEY?`idx_anonymous_id_addtime`?(`anonymous`,`poll_id`,`add_time`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8 SELECT?*?FROM?poll_joined_151?WHERE?poll_id?=?'2348993'?AND?anonymous?=0?ORDER?BY?add_time?DESC?LIMIT?0?,?3 ***************************?1.?row?*************************** id:?1 select_type:?SIMPLE table:?poll_joined_151 type:?ref possible_keys:?idx_poll_id_uid_add_time,idx_anonymous_id_addtime key:?idx_anonymous_id_addtime key_len:?9 ref:?const,const rows:?30240 Extra:?Using?where
從上面的例子看，雖然poll_id的類型為bigint，但是SQL中添加了”，但是這個語句仍然用到了索引，雖然掃描行數也不少，但是能用到索引就是好SQL。
那么一個小小的”為什么會有這么大的影響呢？根本原因是因為MySQL在對文本類型和數字類型進行比較的時候會進行隱式的索引。
以下是5.5官方手冊的說明：
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. 有一個參數是?TIMESTAMP?或?DATETIME，并且另外一個參數是常量，常量會被轉換為?timestamp 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. 有一個參數是?decimal?類型，如果另外一個參數是?decimal?或者整數，會將整數轉換為?decimal?后進行比較，如果另外一個參數是浮點數，則會把?decimal?轉換為浮點數進行比較 In?all?other?cases,?the?arguments?are?compared?as?floating-point?(real)?numbers.所有其他情況下，兩個參數都會被轉換為浮點數再進行比較
根據以上的說明，當where條件之后的值的類型和表結構不一致的時候，MySQL會做隱式的類型轉換，都將其轉換為浮點數在比較。
對于第一種情況：
比如where string = 1；
需要將索引中的字符串轉換成浮點數，但是由于’1′,’ 1′,’1a’都會比轉化成1,故MySQL無法使用索引只能進行全表掃描，故造成了慢查詢的產生。
mysql>?SELECT?CAST('?1'?AS?SIGNED)=1; +-------------------------+ |?CAST('?1'?AS?SIGNED)=1?| +-------------------------+ |?1?| +-------------------------+ 1?row?in?set?(0.00?sec) mysql>?SELECT?CAST('?1a'?AS?SIGNED)=1; +--------------------------+ |?CAST('?1a'?AS?SIGNED)=1?| +--------------------------+ |?1?| +--------------------------+ 1?row?in?set,?1?warning?(0.00?sec) mysql>?SELECT?CAST('1'?AS?SIGNED)=1; +-----------------------+ |?CAST('1'?AS?SIGNED)=1?| +-----------------------+ |?1?| +-----------------------+ 1?row?in?set?(0.00?sec)
同時需要注意一點，由于都會轉換成浮點數進行比較，而浮點數只有53bit，故當超過最大值的時候，比較會出現問題。
對于第二種情況：
由于索引建立在int的基礎上，而將純數字的字符串可以百分百轉換成數字，故可以使用到索引，雖然也會進行一定的轉換，消耗一定的資源，但是最終仍然使用了索引，不會產生慢查詢。
mysql>?select?CAST(?'30'?as?SIGNED)?=?30; +----------------------------+ |?CAST(?'30'?as?SIGNED)?=?30?| +----------------------------+ |?1?| +----------------------------+ 1?row?in?set?(0.00?sec)
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
