場景和環(huán)境
redhat6.5 + 64位 + 12核心 + 16g
表數(shù)量 600w
mysql 5.0
問題描述
在使用in過程中，同事寫了一個簡單的in條件查詢(字段是普通索引，varchar)，由于拼裝sql的時候，沒有使用引號，導致出現(xiàn)大量慢查詢
問題SQL
select?count(*)?total?from?member_phone?where?phone?in(1521xxx541,15845xxx412)
問題SQL和糾正過的寫法對比
執(zhí)行時間
mysql>?select?count(*)?total?from?member_phone?where?phone?in(1521xxx541,15845xxx412); +-------+ |?total?| +-------+ |?????1?|? +-------+ 1?row?in?set?(2.76?sec) mysql>?select?count(*)?total?from?member_phone?where?phone?in('1521xxx541','15845xxx412'); +-------+ |?total?| +-------+ |?????1?|? +-------+ 1?row?in?set?(0.01?sec) mysql>?select?count(*)?total?from?member_phone?where?(phone='1521xxx541'?or?phone='15845xxx412'); +-------+ |?total?| +-------+ |?????1?|? +-------+ 1?row?in?set?(0.00?sec)
EXPLAIN
mysql>?explain?select?count(*)?total?from?member_phone?where?phone?in(1521xxx541,15845xxx412)?G; ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE ????????table:?member_phone ?????????type:?index possible_keys:?phone ??????????key:?phone ??????key_len:?18 ??????????ref:?NULL ?????????rows:?6307075 ????????Extra:?Using?where;?Using?index 1?row?in?set?(0.00?sec) mysql>?explain?select?count(*)?total?from?member_phone?where?phone?in('1521xxx541','15845xxx412')?G; ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE ????????table:?member_phone ?????????type:?range possible_keys:?phone ??????????key:?phone ??????key_len:?18 ??????????ref:?NULL ?????????rows:?2 ????????Extra:?Using?where;?Using?index 1?row?in?set?(0.00?sec) mysql>?explain?select?count(*)?total?from?member_phone?where?(phone='1521xxx541'?or?phone='15845xxx412')?G; ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE ????????table:?member_phone ?????????type:?range possible_keys:?phone ??????????key:?phone ??????key_len:?18 ??????????ref:?NULL ?????????rows:?2 ????????Extra:?Using?where;?Using?index 1?row?in?set?(0.01?sec)
總結
在三個類型的sql中，效率從高到低分別是 or,in 添加了引號, in不加引號。在explain中看到不加引號時,顯示的用上了索引phone，type 變成了 index ,和全表掃描差不多了，只不過MySQL掃描時按索引的次序進行而不是行。
提醒
在where多個or,in中條件個數(shù)比較多,或者多個in 條件時，實際性能都比較差的。以上測試我個人僅在MySQL5.0中測試，高版本官方不知是否優(yōu)化過。
? 版權聲明
文章版權歸作者所有，未經(jīng)允許請勿轉載。
THE END
