MySQL Execute Plan--Index Merge特性-有解無憂

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操作前避免回表查詢，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/87438.html

標籤：MySQL

上一篇：SQL中的事務ACID

下一篇：MySQL必知存盤引擎