MySQL中應用or、in與union all在查詢敕令下的效力比較

MySQL中應用or、in與union all在查詢敕令下的效力比較。本站提示廣大學習愛好者：（MySQL中應用or、in與union all在查詢敕令下的效力比較）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL中應用or、in與union all在查詢敕令下的效力比較正文

OR、in和union all 查詢效力究竟哪一個快？
網上許多的聲響都是說union all 快於 or、in，由於or、in會招致全表掃描，他們給出了許多的實例。
但真的union all真的快於or、in？

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

這條語句履行成果481條，履行時光為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')

這條語句的履行成果時光為0.186s

SIMPLE  employees  ALL  300141  Using where

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

這條語句的履行成果和in的成果差不多

豈非是網上的說法有誤？豈非和索引有關？在firstname上樹立了一個索引

從新履行

union的履行履行籌劃以下，履行時光為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的履行籌劃以下，履行時光也為0.004s

SIMPLE  employees  range  index_firstname  index_firstname  44  481  Using where

or的履行籌劃以下，履行時光也為0.004s

SIMPLE  employees  range  index_firstname  index_firstname  44  481  Using where

感到機能差不多啊。然則留意履行籌劃中的type，ref要好過range哦(ref為非獨一性索引掃描，range為索引規模掃描）
忽然感到似乎和網上說的差不多了，然則第一個語句走了兩個ref掃描 會不會效力比走一次range的掃描低啊。

要不我再嘗嘗主鍵，這個是獨一的，會不會和網上的後果一向呢？

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

union的履行籌劃以下

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的履行籌劃以下

SIMPLE  employees  range  PRIMARY  PRIMARY  4  2  Using where

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

or的履行籌劃以下

SIMPLE  employees  range  PRIMARY  PRIMARY  4  2  Using where

感到成果和第二個試驗照樣差不多。

上面本文就采取實例來商量在現實的查詢敕令下它們之間的效力比較畢竟若何。
1：創立表，拔出數據、數據量為1萬萬【要不後果不顯著】。

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

 該表只要兩個字段 ID為主鍵【索引頁相似】，一個是通俗的字段。（偷懶就用簡略的表構造呢）
向BT表中拔出1萬萬條數據
這裡我寫了一個簡略的存儲進程【所以你的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應用了結合停止完整掃描請應用1 。
   非索引列及VNAME應用了全表掃描請應用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; 

 就不寫正文呢，挺簡略的。
存儲進程是最好設置下innob的相干參數【重要和日記、寫緩存相干如許能加速拔出】，俺沒有設置拔出1萬萬條數據插了6分鐘。
部門數據以下：1萬萬數據相似

2：實戰
    2.1 :分離在索引列上應用 or、in、union all
           我們創立的表只要主鍵索引，所以只能用ID做查詢呢。我們查 ID 為 98，85220，9888589的三個數據各個耗時以下：

時光都為0.00，怎樣會如許呢，呵呵一切查詢都是在毫秒級別。
我應用其他的對象--EMS SQL Manager  for mysql
查詢顯示時光為
93 ms， 94ms，93 ms，時光相差了若干簡直可以疏忽。
然後我們在看看各自的履行籌劃

這裡要留意的字段type 與ref字段
我們發明union all 的所用的 type【type為顯示銜接應用了何品種型】 為ref 而or和in為range【ref銜接類型優於range，相差不了若干】，而查詢行數都一樣【看rows字段都是為3】。
從全部的進程來看，在索引列應用常數or及in和union all查詢相差不了若干。
但為何在有的龐雜查詢中，再索引列應用or及in 比union all 速度慢許多呢，這能夠是你的查詢寫的不敷公道，讓mysql廢棄索引而停止全表掃描。
2.2：在非索引列中應用 or、in及union all。
    我們查 VNAME 為 M98，M85220，M9888589的三個數據各個耗時以下：

我們發明為啥union all查詢時光簡直為 or 和in的三倍。
這是為何呢，我們先不說，先看看三個的查詢籌劃。

這裡我們發明籌劃簡直一樣。
但我們要留意掃描的此時關於 or及in 來講 只對表掃描一次即rows是列為9664782。
而關於union all 來講對表掃描了三次即rows的和為9664782*3。
這也是為何我們看到union all 為簡直為三倍的緣由。
備注： 假如應用存儲進程應用第二sql該履行籌劃一切的type列 為 all，其實這個是我最想演示的，但如今曾經快寫終了了才發明成績將功補過呢。

3:總結
     3.1：不要科學union all 就比 or及in 快，要聯合現實情形剖析究竟應用哪一種情形。
     3.2：關於索引列來最好應用union all，因龐雜的查詢【包括運算等】將使or、in廢棄索引而全表掃描，除非你能肯定or、in會應用索引。
    3.3：關於只要非索引字段來講你就老誠實實的用or 或許in，由於 非索引字段原來要全表掃描而union all 只成倍增長表掃描的次數。
    3.4：關於及有索引字段【索引字段有用】又包括非索引字段來時，按理你也應用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' 

     該兩個查詢速度相差若干 重要取決於 索引列查詢時長，如索引列查詢時光太長的話，那你也用or或許in取代吧。
   3.5： 以上重要針對的是單表，而多表結合查詢來講，斟酌的處所就比擬多了，好比銜接方法，查詢表數據量散布、索引等，再聯合單表的戰略選擇適合的症結字。