OR、in和union all 查詢效率到底哪個(gè)快？
網(wǎng)上很多的聲音都是說(shuō)union all 快于 or、in，因?yàn)閛r、in會(huì)導(dǎo)致全表掃描，他們給出了很多的實(shí)例。
但真的union all真的快于or、in？

EXPLAIN SELECT * from employees where employees.first_NAME ='Georgi' UNION ALL SELECT * from employees where employees.first_NAME ='Bezalel'

這條語(yǔ)句執(zhí)行結(jié)果481條，執(zhí)行時(shí)間為0.35s

PRIMARY employees ALL 300141 Using where

UNION employees ALL 300141 Using where

 UNION RESULT <union1,2> ALL 

explain SELECT * FROM employees WHERE employees.first_name IN ('Georgi','Bezalel')

這條語(yǔ)句的執(zhí)行結(jié)果時(shí)間為0.186s

SIMPLE  employees  ALL  300141  Using where

explain SELECT * FROM employees WHERE employees.first_name ='Georgi' or employees.first_name='Bezalel'

這條語(yǔ)句的執(zhí)行結(jié)果和in的結(jié)果差不多

難道是網(wǎng)上的說(shuō)法有誤？難道和索引有關(guān)？在firstname上建立了一個(gè)索引

重新執(zhí)行

union的執(zhí)行執(zhí)行計(jì)劃如下，執(zhí)行時(shí)間為0.004s

PRIMARY  employees  ref  index_firstname  index_firstname  44  const  253  Using where
UNION  employees  ref  index_firstname  index_firstname  44  const  228  Using where
UNION RESULT  <union1,2>  ALL  

in的執(zhí)行計(jì)劃如下，執(zhí)行時(shí)間也為0.004s

SIMPLE  employees  range  index_firstname  index_firstname  44  481  Using where

or的執(zhí)行計(jì)劃如下，執(zhí)行時(shí)間也為0.004s

SIMPLE  employees  range  index_firstname  index_firstname  44  481  Using where

感覺(jué)性能差不多啊。但是注意執(zhí)行計(jì)劃中的type，ref要好于range哦(ref為非唯一性索引掃描，range為索引范圍掃描）
突然感覺(jué)好像和網(wǎng)上說(shuō)的差不多了，但是第一個(gè)語(yǔ)句走了兩個(gè)ref掃描 會(huì)不會(huì)效率比走一次range的掃描低啊。

要不我再試試主鍵，這個(gè)是唯一的，會(huì)不會(huì)和網(wǎng)上的效果一直呢？

EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO=100001 UNION ALL SELECT * FROM employees WHERE employees.EMP_NO=101100

union的執(zhí)行計(jì)劃如下

PRIMARY  employees  const  PRIMARY  PRIMARY  4  const  1  
UNION  employees  const  PRIMARY  PRIMARY  4  const  1  
UNION RESULT  <union1,2>  ALL  

EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO IN (100001 ,101100)

in的執(zhí)行計(jì)劃如下

SIMPLE  employees  range  PRIMARY  PRIMARY  4  2  Using where

EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO=100001 OR emp_no=101100

or的執(zhí)行計(jì)劃如下

SIMPLE  employees  range  PRIMARY  PRIMARY  4  2  Using where

感覺(jué)結(jié)果和第二個(gè)實(shí)驗(yàn)還是差不多。

下面本文就采用實(shí)例來(lái)探討在實(shí)際的查詢命令下它們之間的效率對(duì)比究竟如何。
1：創(chuàng)建表，插入數(shù)據(jù)、數(shù)據(jù)量為1千萬(wàn)【要不效果不明顯】。

drop table if EXISTS BT; 
create table BT( 
  ID int(10) NOT NUll, 
  VName varchar(20) DEFAULT '' NOT NULL, 
  PRIMARY key( ID ) 
)ENGINE=INNODB; 

 該表只有兩個(gè)字段 ID為主鍵【索引頁(yè)類似】，一個(gè)是普通的字段。（偷懶就用簡(jiǎn)單的表結(jié)構(gòu)呢）
向BT表中插入1千萬(wàn)條數(shù)據(jù)
這里我寫了一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程【所以你的mysql版本至少大于5.0，俺的版本為5.1】，代碼如下。
注意：最好

  INSERT INTO BT ( ID,VNAME ) VALUES( i, CONCAT( 'M', i ) );---1

    修改為

  INSERT INTO BT ( ID,VNAME ) VALUES( i, CONCAT( 'M', i, 'TT' ) );---2

   修改原因在
   非索引列及VNAME使用了聯(lián)合進(jìn)行完全掃描請(qǐng)使用1 。
   非索引列及VNAME使用了全表掃描請(qǐng)使用2 。
 

DROP PROCEDURE IF EXISTS test_proc; 
CREATE PROCEDURE test_proc() 
BEGIN 
declare i int default 0; 
set autocommit = 0; 
while i<10000000 do 
INSERT INTO BT ( ID,VNAME ) VALUES( i, CONCAT( 'M', i ) ); 
set i = i+1; 
if i%2000 = 0 then 
commit; 
end if; 
end while; 
END; 

 就不寫注釋呢，挺簡(jiǎn)單的。
存儲(chǔ)過(guò)程是最好設(shè)置下innob的相關(guān)參數(shù)【主要和日志、寫緩存相關(guān)這樣能加快插入】，俺沒(méi)有設(shè)置插入1千萬(wàn)條數(shù)據(jù)插了6分鐘。
部分?jǐn)?shù)據(jù)如下：1千萬(wàn)數(shù)據(jù)類似

2：實(shí)戰(zhàn)
    2.1 :分別在索引列上使用 or、in、union all
           我們創(chuàng)建的表只有主鍵索引，所以只能用ID做查詢呢。我們查 ID 為 98，85220，9888589的三個(gè)數(shù)據(jù)各個(gè)耗時(shí)如下：

時(shí)間都為0.00，怎么會(huì)這樣呢，呵呵所有查詢都是在毫秒級(jí)別。
我使用其他的工具--EMS SQL Manager  for mysql
查詢顯示時(shí)間為
93 ms， 94ms，93 ms，時(shí)間相差了多少幾乎可以忽略。
然后我們?cè)诳纯锤髯缘膱?zhí)行計(jì)劃

這里要注意的字段type 與ref字段
我們發(fā)現(xiàn)union all 的所用的 type【type為顯示連接使用了何種類型】 為ref 而or和in為range【ref連接類型優(yōu)于range，相差不了多少】，而查詢行數(shù)都一樣【看rows字段都是為3】。
從整個(gè)的過(guò)程來(lái)看，在索引列使用常數(shù)or及in和union all查詢相差不了多少。
但為什么在有的復(fù)雜查詢中，再索引列使用or及in 比union all 速度慢很多呢，這可能是你的查詢寫的不夠合理，讓mysql放棄索引而進(jìn)行全表掃描。
2.2：在非索引列中使用 or、in及union all。
    我們查 VNAME 為 M98，M85220，M9888589的三個(gè)數(shù)據(jù)各個(gè)耗時(shí)如下：

我們發(fā)現(xiàn)為啥union all查詢時(shí)間幾乎為 or 和in的三倍。
這是為什么呢，我們先不說(shuō)，先看看三個(gè)的查詢計(jì)劃。

這里我們發(fā)現(xiàn)計(jì)劃幾乎一樣。
但我們要注意掃描的此時(shí)對(duì)于 or及in 來(lái)說(shuō) 只對(duì)表掃描一次即rows是列為9664782。
而對(duì)于union all 來(lái)說(shuō)對(duì)表掃描了三次即rows的和為9664782*3。
這也是為什么我們看到union all 為幾乎為三倍的原因。
備注： 如果使用存儲(chǔ)過(guò)程使用第二sql該執(zhí)行計(jì)劃所有的type列 為 all，其實(shí)這個(gè)是我最想演示的，但現(xiàn)在已經(jīng)快寫完畢了才發(fā)現(xiàn)問(wèn)題將錯(cuò)就錯(cuò)呢。

3:總結(jié)
     3.1：不要迷信union all 就比 or及in 快，要結(jié)合實(shí)際情況分析到底使用哪種情況。
     3.2：對(duì)于索引列來(lái)最好使用union all，因復(fù)雜的查詢【包含運(yùn)算等】將使or、in放棄索引而全表掃描，除非你能確定or、in會(huì)使用索引。
    3.3：對(duì)于只有非索引字段來(lái)說(shuō)你就老老實(shí)實(shí)的用or 或者in，因?yàn)?非索引字段本來(lái)要全表掃描而union all 只成倍增加表掃描的次數(shù)。
    3.4：對(duì)于及有索引字段【索引字段有效】又包含非索引字段來(lái)時(shí)，按理你也使用or 、in或者union all 都可以，
       但是我推薦使用or、in。
      如以下查詢：

select * from bt where bt.VName = 'M98' or bt.id ='9888589' 
 
select * from bt where bt.VName = 'M98' 
UNION ALL 
select * from bt where bt.id = '9888589' 

     該兩個(gè)查詢速度相差多少 主要取決于 索引列查詢時(shí)長(zhǎng)，如索引列查詢時(shí)間太長(zhǎng)的話，那你也用or或者in代替吧。
   3.5： 以上主要針對(duì)的是單表，而多表聯(lián)合查詢來(lái)說(shuō)，考慮的地方就比較多了，比如連接方式，查詢表數(shù)據(jù)量分布、索引等，再結(jié)合單表的策略選擇合適的關(guān)鍵字。 

最新評(píng)論