mysql中IN，OR，BETWEEN性能比較

經常會有朋友問關於在mysql中IN，OR，BETWEEN那個性能更好，我想有很多朋友會用它們三但確實不知道IN，OR，BETWEEN性能那個更好吧，下面我來總結一下關於IN，OR，BETWEEN性能。

微博上看到@金山 提到了一個MySQL中的一個性能問題，

 

 代碼如下 復制代碼 select id from table where id > 100 and id < 200 和 select id from table where id = 101 or id = 103 or id = 104 or id = 105 or id = ...

 
哪個更快？

 
這裡的查詢條件有三種：between，or 和 in。這裡id列是索引列，如果不是的話，三個查詢都是全表掃描，性能差距應該不大。

 
1  准備環境

 代碼如下 復制代碼

mysql> show create table tinG
*************************** 1. row ***************************
       Table: tin
Create Table: CREATE TABLE `tin` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=5002 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create procedure init_tinG
*************************** 1. row ***************************
           Procedure: init_tin
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `init_tin`(cnt int)
begin
declare i int default 0;
repeat
insert into tin(c2) values(repeat('a', 100));
set i:= i+1;
until i > cnt
end repeat;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> call init_tin(5000)G

 
2  查看執行計劃
為了簡單起見，這裡並沒有選擇[100,200]這個區間進行查詢，而是只選擇了[100,104]這個區間。 查詢語句為：

 代碼如下 復制代碼

 
SELECT * FROM tin where c1 >= 100 and c1 <= 104;
SELECT * FROM tin where c1 in (100, 101, 102, 103, 104);
SELECT * FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 = 104;

 
首先查看explain輸出，會發現三個語句的explain輸出是一樣的：

 代碼如下 復制代碼

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tin
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

 
MySQL5.6在information_schema中增加了optimizer_trace表，用於跟蹤語句生成的執行計劃的具體步驟，包含各種關鍵的優化步驟。 分別看下三種不同語句的執行代價：

1.

 代碼如下 復制代碼 SELECT * FROM tin where c1 >=100 and c1 <=104;
"chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 5,
                      "ranges": [
                        "100 <= c1 <= 104"
                      ]
                    },
                    "rows_for_plan": 5,
                    "cost_for_plan": 2.0188,
                    "chosen": true
                    }

2.

 代碼如下 復制代碼 SELECT * FROM tin where c1 in (100, 101, 102, 103, 104);
"chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 5,
                      "ranges": [
                        "100 <= c1 <= 100",
                        "101 <= c1 <= 101",
                        "102 <= c1 <= 102",
                        "103 <= c1 <= 103",
                        "104 <= c1 <= 104"
                      ]
                    },
                    "rows_for_plan": 5,
                    "cost_for_plan": 6.0188,
                    "chosen": true
                  }

3.

 代碼如下 復制代碼 SELECT * FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 =104;
"chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 5,
                      "ranges": [
                        "100 <= c1 <= 100",
                        "101 <= c1 <= 101",
                        "102 <= c1 <= 102",
                        "103 <= c1 <= 103",
                        "104 <= c1 <= 104"
                      ]
                    },
                    "rows_for_plan": 5,
                    "cost_for_plan": 6.0188,
                    "chosen": true
                  }

 
從上面可以看出執行代價最小的語句為SELECT * FROM tin WHERE c1 >= 100 and c1 <=104，代價為2.0118，其他兩個計劃的代價 為6.0118。

 
3  計劃分析
看了上面的代價結果，是不是就理所當然的任務第一個語句的代價真的是最小呢？這就需要知道MySQL代價計算的方法， 一個計劃的代價體現在硬件上就是I/O+CPU，I/O就是將所需的物理頁載入內存的時間，CPU則是數據計算所消耗的時間， 有些語句是I/O密集的，有些語句是CPU運算密集的。

 
為什麼MySQL計算出來的代價會差別這麼大呢？ MySQL在計算上面三個語句的代價時，I/O代價的計算是由range的個數n_ranges和最終的結果集的行數total_rows得出來的， 語句1的n_ranges=1，語句2和語句3的n_ranges=5，totol_rows都為5，故語句1的在I/O上的代價明顯小於語句2和語句3（具體的函數 參見ha_innobase::read_time）。至於CPU的代價，由於返回的行數一致，故CPU的代價一致，CPU的代價主要體現在獲取數據後，進行WHERE 條件的匹配操作。

 
這只是MySQL的對於上面三個語句的代價模型，而實際上，上面三個語句所進行的I/O操作其實是一致的，因為數據范圍是一樣的。所以，僅憑 MySQL給出的代價結果還是不能立刻判斷出語句1就肯定好。

 
既然I/O操作的代價可以考慮是一致的，那麼只能來看三條語句執行時的區別了。語句2和語句3的range個數都為5個，而且range的范圍都是一致的， 這其實是MySQL的優化結果，IN和OR都被優化成了相同的結果。只有語句1只有1個range。MySQL執行時是遍歷每個range，而每個range遍歷時其實 是兩種操作，read_first和read_next，read_first是根據每個range的start key定位到相應的位置，read_next則是根據上次BTREE讀到的位置， 繼續往後讀，read_next是以end key為結束。

 
對於語句1，只有一個range，故需要1次read_first和5次read_next（最後一次read_next不符合end_key，返回結束）,對於語句2和語句3， 有5個range，每個range需要1此read_first和一次read_next,總共需要5此read_first和5次read_next。從數據獲取的次數來看，語句2和語句3基本是語句1的調用次數的兩倍。

 
除了獲取數據調用次數的區別外，在獲取數據之後，還需要進行數據合法性的驗證，即匹配WHERE條件，對於語句1的WHERE條件十分簡單，匹配 上下界限即可，即對於每返回的一行數據需要兩次驗證，時間復雜度為常量O（2）。 而對於語句2和語句3，則需要對IN或OR中的每個條件進行驗證，知道找到某一匹配項為止，時間復雜度為O（n）。 但是MySQL對於IN做了相應的優化，即將IN中的常量全部存儲在一個數組裡面，而且這個數組是排好序的，故匹配的時候是二分查找， 時間復雜度為O（lgn）。

 
在忽略I/O的情況下，僅僅從CPU的耗時來看，語句1應該是最少的，其次是IN，最差的就是OR了。

 
先就分析到這吧，具體的執行時間的數據我就不測試了，主要是想通過測試了解MySQL內部的優化流程。可能單獨測試的時候語句執行效率 差別不是很大。好了，收拾行李，明天回家，年前最後一篇。