indexmerge的補充說明

1. 為什麼需要考慮Intersection

考慮如下查詢:

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

優化器可以考慮使用索引key1或者key2進行REF/Range訪問，如果使用key1，那麼key2=1則作為過濾條件。另外，優化器還會考慮使用Intersection，即同時使用索引key1和key2。這樣做可能的好處是：

(a) 如果兩次索引掃描後做交集，如果最後ROWID很少，則回表次數大大減少

(b) 如果掃描這兩個索引能是覆蓋掃描的話，則無需回表

對比ref/range訪問方式，index merge需要額外多訪問一個索引，ROWID需要做交集，所以需要額外的比較操作。優化器將各自計算ref/range和index merge的成本，然後選擇成本較低作為最終的執行計劃。

2. MySQL優化器的Intersection

前面描述了Intersection的兩個好處，MySQL優化器先使用了一個較為複雜的演算法來預估合併後ROWID數量；另外，如果發現有覆蓋掃描，則無需回表，則成本會大大減少。

另外，因為index merge通常需要訪問兩個以上索引，成本通常不抵，MySQL選擇Intersection的時候，加上了一個額外的要求：

(a) 只有ROR型別的索引使用才能作為Intersection執行計劃的一部分(什麼是ROR)

3. 優化器如何篩選Intersection使用的索引

3.1 演算法說明

這裡分了兩個部分，先使用貪婪演算法在所有的ROR索引中，組合出一組成本最小的做Intersection。如果這個“最小組合”不是覆蓋索引，而且又存在覆蓋索引，那麼再做一次貪婪演算法找到一個成本最小的覆蓋查詢，如果成本更小則選擇之。

3.1.1 找到成本最小的ROR組合

這是一個貪婪演算法，找到未必是全域性最優的結果。這裡簡單描述一下演算法(可以參考get_best_ror_intersect的註釋和實現)：

初始：R是所有可用的ROR索引查詢；S是空集；
R中的記錄是按照需要掃描索引的大小排序(E(#records_matched) * key_record_length)
  S= first(R); R= R-first(S);
  min_cost= cost(S); min_scan= make_scan(S);
  while (R is not empty)
  {
    firstR= R - first(R);
    if (!selectivity(S + firstR < selectivity(S)))
      continue;
    S= S + first(R);
    if (cost(S) < min_cost)
    {min_cost= cost(S);min_scan= make_scan(S);}
  }
  return min_scan;

演算法說明：每次從所有ROR中取出掃描成本最低的索引，判斷加入該索引後成本是否會下降。如果成本下降，則將本ROR加入結果集；如果成本不會下降，那麼忽略；

除此，MySQL還做了一個判斷，如果新增ROR索引之後，會計算其選擇度(selectivity)，只有當新增ROR索引會降低整體區分度的時候，這個索引才會被加入其中。這部分計算的目的，一方面是保證新增索引後一定會降低選擇度，這通常都是滿足的，只要新增的索引條件不是S集合的子集，一般都是滿足的；另一方面，會順便計算出新增索引後的選擇度，這樣就可以計算，多個索引合併後返回的記錄數大約是多少。下面會單獨介紹MySQL如何預估，兩個條件交集命中的記錄數。

3.1.2 計算兩個索引交集命中的記錄數

這個問題的抽象如下：有如下條件key1_p1=c1 and key1_p1=c2 and key2_p1=c3 and key2_p2=c4，現在已知key1_p1=c1 and key1_p1=c2的選擇度是X，key2_p1=c3 and key2_p2=c4的選擇度是Y，問，總體選擇度是多少？

如果key1和key2是完全獨立的，沒有任何欄位重複，那麼按照均勻計算，交集後，總體選擇度為X*Y，這部分是較為容易理解的。

如果key1和key2不是獨立的，問題就較為複雜了，例如，key1_p1 = c1 和 key2_p1=c3 是兩個一樣的重複的條件，即索引key1和key2的某個欄位相同。那麼，如果按照上面的公式計算就非常不準確了。MySQL計算的辦法，是逐個新增：

假設有集合A={key1_p1 = c1, key1_p1=c2}，對應的選擇度記為P(A)，如果有索引條件：key2_p1=c3 and key2_p2=c4，MySQL先將key2_p1=c3加入集合A，並計算選擇度；然後把key2_p2=c4加入集合A，並計算選擇度。進一步抽象，有集合A，已知選擇度為P(A)，現有索引條件key2對應的兩個AND條件為b 1

和b 2

，現在演示如何逐個將b 1

和b 2

加入集合A並計算其選擇度。

已知集合A，其選擇度為P(A)；索引條件b 1

and b 2

；並記 B 1 ={b 1 }，B 2 ={b 2 }；

；

記R為該表總記錄數，R(b 1 )

表示條件b 1

對應的記錄數，可以通過函式records_in_range計算；

P(X|Y)

表示Y條件發生時的條件概率，這裡假設都是均勻分佈，選擇度就是概率。且有P(X|Y) = P(X)*P(Y|X)；

那麼，將集合B 1

合併到集合A之後，選擇度計算為：

(1) 如果A，B 1

不獨立，即對應條件b 1

屬於集合A，那麼，P(B 1 |A)=1

。那麼選擇度不變，仍然是P(A)

;

(2) 如果A，B 1

獨立，對應條件b 1
不屬於集合A，那麼有

這時就可以把條件b 1

併入集合A，對應的選擇度如上式。繼續，考慮把條件b 2

加入合計A。

同樣的，如果B 2

和A∩B 1

不獨立，即B 2

是{x|x∈A或者x∈B 1 }

的子集，那麼

如果兩者獨立，繼續計算：

MySQL將使用上面的方法計算多個條件合併的時候的選擇度。 MySQL通過records_in_range來計算R(b 1 andb 2 )

。

MySQL在實現的時候，略有不同的地方是，為了儘可能少的避免records_in_range的呼叫次數，如果連續的多個條件都是同時獨立或者同時都不獨立，那麼則會將這多個條件作為一個整理來計算。

3.1.3 找到成本最小覆蓋索引組合

如果前面找到ROR組合不是覆蓋查詢，而且又存多個索引組合的覆蓋索引的話，MySQL還會再做一次貪婪查詢，嘗試找到最優的覆蓋索引組合，如果成本比之前的”最小成本”更小，則選擇這組索引。

這部分實現參考函式get_best_covering_ror_intersect，沒有特別需要說明的。

4. 成本的計算

如果上面計算好了選擇度，Intersection的成本計算就很簡單了。每次新增一個索引到index merge中的時候，先計算各個索引讀取的成本(參考)，如果不是覆蓋掃描則需要額外加上，根據ROWID取出記錄的成本(參考)。

5. Intersection的案例

CREATE TABLE `tmp_index_merge` (
  `id` int(11) NOT NULL,
  `key1_part1` int(11) NOT NULL,
  `key1_part2` int(11) NOT NULL,
  `key2_part1` int(11) NOT NULL,
  `key2_part2` int(11) NOT NULL,
  `key2_part3` int(11) NOT NULL,
  `key3_part1` int(11) NOT NULL DEFAULT `4`,
  PRIMARY KEY (`id`),
  KEY `ind2` (`key2_part1`,`key2_part2`,`key2_part3`),
  KEY `ind1` (`key1_part1`,`key1_part2`,`id`),
  KEY `ind3` (`key3_part1`,`id`)
) ENGINE=InnoDB

for i in `seq 1 5000` ; do mysql -vvv -uroot test 
-e `insert into tmp_index_merge values (60000*rand(),5000*rand(),
5000*rand(),5000*rand(),5000*rand(),5000*rand(),2877)`; done

for i in `seq 1 5000` ; do mysql -vvv -uroot test 
-e `insert into tmp_index_merge values (600000*rand(),4333,1657,
5000*rand(),5000*rand(),5000*rand(),5000*rand())`; done

explain select count(*) from tmp_index_merge where 
(key1_part1 = 4333 and key1_part2 = 1657) and (key3_part1 = 2877)G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tmp_index_merge
         type: index_merge
possible_keys: ind1,ind3
          key: ind3,ind1
      key_len: 4,8
          ref: NULL
         rows: 3622
        Extra: Using intersect(ind3,ind1); Using where; Using index

如果不滿足ROR的條件，例如將上面案例的ind3索引的ID欄位去掉，則不會再考慮使用Intersection。

alter table tmp_index_merge drop index ind3,add KEY `ind3` (`key3_part1`);
Query OK, 14137 rows affected (1.15 sec)
Records: 14137  Duplicates: 0  Warnings: 0

root@test 04:32:58>explain select * from tmp_index_merge where 
(key1_part1 = 4333 and key1_part2 = 1657) and (key3_part1 = 2877)G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tmp_index_merge
         type: ref
possible_keys: ind1,ind3
          key: ind1
      key_len: 8
          ref: const,const
         rows: 3408
        Extra: Using where

6. 最後

Intersection這類執行計劃，因為需要滿足ROR條件，所以較為少見。理想情況是，覆蓋但非ROR成本也可能會很低，但是MySQL不考慮這點。另外，較新版本開始支援Index Condition Pushdown，這會大大降低選擇ref/range的執行成本，Intersection的優勢會大大下降。

到此，MySQL index merge調研就告一段落了。