MySQL Execute Plan--Index Merge特性_ZenDei技術網路在線

Index Merge特性在MySQL 5.5之前版本中，查詢或子查詢被限制在一個表只能使用一個索引(回表查詢除外)。假設表TB1001上C1和C2列分別有單列索引，如對下麵查詢： SELECT * FROM TB1001 WHERE C1='XXX' OR C2='XXX'; 單獨使用任一索引 ...

Index Merge特性

在MySQL 5.5之前版本中，查詢或子查詢被限制在一個表只能使用一個索引(回表查詢除外)。

假設表TB1001上C1和C2列分別有單列索引，如對下麵查詢：

SELECT * FROM TB1001 WHERE C1='XXX' OR C2='XXX';

單獨使用任一索引都無法獲取到所有滿足條件的數據，因此查詢只能使用全表掃描。

在MySQL 5.5版本中引入Index Merge特性，允許：
查詢對一個表上多個索引進行範圍掃描並將多個掃描結果進行合併(UNION/INTERSECT)。

Index Merge三種合併演算法：

1、Index Merge Intersect：對多個結果集求交集
2、Index Merge Union：對多個結果集求UNION集合(無需對結果集排序)
3、Index Merge Sort-Union：對多個結果集先排序再求UNION集合

Index Merge Intersect演算法

當查詢過濾條件(WHERE部分)上使用AND關聯多個不同KEY的過濾條件時，如：

# 表TB1001有主鍵索引PRIMARY KEY(ID)
# 表TB1001有輔助索引IDX_C1(C1) 和輔助索引IDC_C2(C2)

SELECT * FROM TB1001 WHERE C1='XXX' AND C2='XXX';

不使用Index Merge Intersect演算法時執行計劃偽代碼為：

SELECT * FROM TB1001
WHERE ID IN (
SELECT ID FROM TB1001 WHERE C1='XXX')
AND C2='XXX';

使用Index Merge Intersect演算法時執行計劃偽代碼為：

SELECT T2.* FROM (
SELECT ID FROM TB1001 WHERE C1='XXX'
INTERSECT
SELECT ID FROM TB1001 WHERE C2='XXX'
) AS T1
INNER JOIN TB1001 AS T2
ON T1.ID=T2.ID;

操作成本假設1：

假設：
滿足C1='XXX'的記錄有10000行：索引IDX_C1上每個數據頁存放500行索引記錄，滿足條件數據：
    A、"順序存放"在索引IDX_C1上"連續"的20個索引頁中。
    B、"分散存放"在主鍵上"隨機"的2000個數據頁中。
滿足C2='XXX'的記錄有20000行，索引IDX_C2上每個數據頁存放500行索引記錄，滿足條件數據:
    A、"順序存放"在索引IDX_C2上"連續"的40個索引頁中。
    B、"分散存放"在主鍵上"隨機"的4000個數據頁中。
同時滿足C1='XXX' AND C2='XXX'的記錄有200行，滿足條件數據:
    A、"分散存放"在主鍵上"隨機"的40個數據頁中

那麼：
1、不使用Index Merge Intersect演算法需要"順序讀取"20個IDX_C1索引頁+"隨機讀取"2000個主鍵索引數據頁
2、使用Index Merge Intersect演算法需要"順序讀取"20個IDX_C1索引頁+"順序讀取"40個IDX_C2索引頁+"隨機讀取"40個主鍵索引數據頁
針對上面情況，使用Index Merge Intersect演算法能有效降低對主鍵的回表查找次數和隨機讀取次數(從2000次下降至40次)。

操作成本假設2：

假設：
滿足C1='XXX'的記錄有20行：索引IDX_C1上每個數據頁存放500行索引記錄，滿足條件數據：
    A、"順序存放"在索引IDX_C1上"連續"的1個索引頁中。
    B、"分散存放"在主鍵上"隨機"的20個數據頁中。
滿足C2='XXX'的記錄有200000行，索引IDX_C2上每個數據頁存放500行索引記錄，滿足條件數據:
    A、"順序存放"在索引IDX_C2上"連續"的400個索引頁中。
    B、"分散存放"在主鍵上"隨機"的40000個數據頁中。
同時滿足C1='XXX' AND C2='XXX'的記錄有19行，滿足條件數據:
    A、"分散存放"在主鍵上"隨機"的19個數據頁中

那麼：
1、不使用Index Merge Intersect演算法需要"順序讀取"1個IDX_C1索引頁+"隨機讀取"20個主鍵索引數據頁
2、使用Index Merge Intersect演算法需要"順序讀取"1個IDX_C1索引頁+"順序讀取"400個IDX_C2索引頁+"隨機讀取"19個主鍵索引數據頁
針對上面情況，使用Index Merge Intersect演算法需要額外讀取400個IDX_C2索引頁才能降低1次主鍵的回表查詢和隨機讀取，顯然性能更差。

Index Merge Intersect演算法和Index condition Pushdown特性

在MySQL官方文檔中，Index Merge Intersect演算法可以應用在分別使用主鍵和二級索引的查詢中，如：

SELECT *
FROM innodb_table
WHERE primary_key < 10
AND key_col1 = 20;

在未引入ICP特性的早期MySQL版本中，主鍵上過濾條件(primary_key < 10)不會"下推"到查詢滿足key_col1 = 20條件的過程中，因此可以使用Index Merge Intersect演算法來減少回表查找次數。

在引入ICP特性的MySQL版本中，由於輔助索引的索引記錄中都包含主鍵列數據，因此主鍵上過濾條件(primary_key < 10)可以"下推"到查詢滿足key_col1 = 20條件的過程中，無需再使用Index Merge Intersect演算法。

## 在MySQL 5.7版本中測試
SELECT *
FROM TB001
WHERE C1=10
AND ID<100;
## 執行計劃為：
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TB001
   partitions: NULL
         type: ref
possible_keys: PRIMARY,IDX_C1
          key: IDX_C1
      key_len: 5
          ref: const
         rows: 1
     filtered: 33.33
        Extra: Using where; Using index
## 執行計劃Extra部分沒有INDEX MERGE相關信息

Index Merge Intersect性能問題優化

在部分場景中，使用Index Merge Intersec演算法會帶來嚴重的性能問題，DBA可以通過MySQL參數optimizer_switch來關閉該特性。

對於通過Index Merge Intersec演算法受益的查詢，可以考慮使用組合索引或覆蓋索引來替換單列索引。

如對上面查詢，可以將索引IDX_C1(C1)調整為IDX_C1_C2(C1,C2)，其查詢性能更佳。

Index Merge Union演算法
當查詢過濾條件(WHERE部分)上使用OR關聯多個不同KEY的過濾條件時，如：

# 表TB1001有主鍵索引PRIMARY KEY(ID)
# 表TB1001有輔助索引IDX_C1(C1) 和輔助索引IDC_C2(C2)
SELECT * FROM TB1001 WHERE C1='XXX' OR C2='XXX';

其操作步驟為：

1、使用IDX_C1索引獲取到滿足條件的[C1,ID]記錄，記錄預設按照ID排序
2、使用IDX_C1索引獲取到滿足條件的[C1,ID]記錄，記錄預設按照ID排序
3、將已經按照ID排序的步驟1和步驟2的數據進行合併去重ID。
4、按照ID回表查找並返回

偽代碼為：

SELECT T2.* FROM (
SELECT ID FROM TB1001 WHERE C1='XXX'
UNION
SELECT ID FROM TB1001 WHERE C2='XXX'
) AS T1
INNER JOIN TB1001 AS T2
ON T1.ID=T2.ID

在創建索引IDX_C1(ID)時，其等價為IDX_C1(C1,ID),相同C1值的記錄按ID值排序，因此UNION操作的兩個中見結果集在ID上時有序的。

Index Merge Sort-Union演算法

當查詢過濾條件(WHERE部分)上使用OR關聯多個不同KEY的過濾條件時，如：

# 表TB1001有主鍵索引PRIMARY KEY(ID)
# 表TB1001有輔助索引IDX_C1(C1) 和輔助索引IDC_C2(C2)
SELECT * FROM TB1001 WHERE C1>'XXX' OR C2<'XXX';

其操作步驟為：

1、使用IDX_C1索引獲取到滿足條件的[C1,ID]記錄，再按照ID進行排序
2、使用IDX_C1索引獲取到滿足條件的[C1,ID]記錄，再按照ID進行排序
3、將步驟1和步驟2的已按ID排序後數據進行合併去重ID。
4、按照ID回表查找並返回

偽代碼為：

SELECT T2.* FROM (
SELECT ID FROM TB1001 WHERE C1>'XXX'
ORDER BY ID
UNION
SELECT ID FROM TB1001 WHERE C2>'XXX'
ORDER BY ID
) AS T1
INNER JOIN TB1001 AS T2
ON T1.ID=T2.ID

在創建索引IDX_C1(ID)時，其等價為IDX_C1(C1,ID),對C1列進行範圍查詢返回數據的數據按照C1+ID排序，在ID列上是無序的，因此UNION操作前需先對兩個中間結果集排序。

Index Merge Union相關優化
在禁用Index Merge特性時，可以通過SQL將OR操作改寫為UNION ALL操作，使查詢同時使用多個索引。

如上面使用Index Merge Union演算法的查詢，可以改寫為：

#改寫前：
SELECT * FROM TB1001 WHERE C1='XXX' OR C2='XXX';

# 改寫後
SELECT T2.* FROM (
SELECT ID FROM TB1001 WHERE C1='XXX'
UNION ALL
SELECT ID FROM TB1001 WHERE C2='XXX' AND (C1<>'XXX' OR C1 IS NULL)
) AS T1
INNER JOIN TB1001 AS T2
ON T1.ID=T2.ID

PS: 將IDX_C2(C2)改寫為IDX_C2_C2(C1,C2)能在UNION操作前避免回表查詢。