Mysql索引合并的實現(xiàn)示例

更新時間：2025年07月21日 09:13:18 作者：碼上庫利南

MySQL索引合并通過多索引掃描與結果集合并優(yōu)化查詢,本文主要介紹了Mysql索引合并的實現(xiàn)示例,具有一定的參考價值,感興趣的可以了解一下

MySQL 中的索引合并是一種查詢優(yōu)化技術，當單個表查詢的 WHERE 子句中包含多個條件，并且這些條件分別可以用到不同的索引時，MySQL 優(yōu)化器可能會嘗試將這些索引掃描的結果合并起來，以更高效地獲取最終滿足所有條件的行。它本質上是優(yōu)化器在無法找到最優(yōu)的單個復合索引時的一種“折衷”策略。

核心思想：利用多個索引分別篩選數(shù)據，然后將結果集合并（交集、并集或排序后并集）以得到最終結果，避免全表掃描。

一、索引合并的類型

MySQL 主要支持三種索引合并算法：

1.1 Index Merge Intersection Access (Using intersect(...)):

適用場景： WHERE 子句中的多個條件通過 AND 連接，并且每個條件都可以有效地使用一個單獨的索引（這些索引通常是單列索引）。

工作原理：優(yōu)化器對每個可用的索引執(zhí)行范圍掃描或等值查詢掃描。

獲取每個索引掃描得到的主鍵值（或行指針）集合。
計算這些主鍵值集合的交集（即同時出現(xiàn)在所有集合中的主鍵值）。
根據交集得到的主鍵值，回表（如果需要）讀取完整的行數(shù)據。

示例：

CREATE TABLE `t` (
  `id` INT PRIMARY KEY,
  `a` INT,
  `b` INT,
  `c` VARCHAR(100),
  INDEX `idx_a` (`a`),
  INDEX `idx_b` (`b`)
);
-- 假設 idx_a 和 idx_b 都是 B-Tree 索引
SELECT * FROM t WHERE a = 10 AND b = 20;

優(yōu)化器可能分別使用 idx_a 查找 a=10 的行（得到主鍵集合 S1）。
使用 idx_b 查找 b=20 的行（得到主鍵集合 S2）。
計算 S1 和 S2 的交集。
根據交集結果回表取數(shù)據。
EXPLAIN 輸出： type 列顯示 index_merge，Extra 列顯示 Using intersect(idx_a, idx_b); Using where。

1.2 Index Merge Union Access (Using union(...)):

適用場景： WHERE 子句中的多個條件通過 OR 連接，并且每個條件都可以有效地使用一個單獨的索引（這些索引通常是單列索引），并且查詢是 SELECT（非 UPDATE/DELETE），并且沒有使用 FOR UPDATE 或 LOCK IN SHARE MODE。

工作原理：

優(yōu)化器對每個可用的索引執(zhí)行范圍掃描或等值查詢掃描。
獲取每個索引掃描得到的主鍵值（或行指針）集合。
計算這些主鍵值集合的并集（即出現(xiàn)在任意一個集合中的主鍵值）。
對并集結果進行去重。
根據去重后的主鍵值，回表（如果需要）讀取完整的行數(shù)據。

示例：

SELECT * FROM t WHERE a = 10 OR b = 20;

優(yōu)化器可能分別使用 idx_a 查找 a=10 的行（得到主鍵集合 S1）。
使用 idx_b 查找 b=20 的行（得到主鍵集合 S2）。
計算 S1 和 S2 的并集，并去重。
根據去重后的結果回表取數(shù)據。
EXPLAIN 輸出： type 列顯示 index_merge，Extra 列顯示 Using union(idx_a, idx_b); Using where。

1.3 Index Merge Sort-Union Access (Using sort_union(...)):

適用場景： WHERE 子句中的多個條件通過 OR 連接，但是這些條件無法直接使用 Index Merge Union（通常是因為索引掃描返回的是范圍結果，而不僅僅是點查詢的等值結果）。它是 Union 的一種變體，用于處理范圍掃描。

工作原理：

優(yōu)化器對每個可用的索引執(zhí)行范圍掃描。
獲取每個索引掃描得到的主鍵值（或行指針）集合。
對每個集合中的主鍵值分別排序。
將排序后的多個主鍵值列表進行歸并排序，并在歸并過程中進行去重。
根據歸并去重后的主鍵值，回表（如果需要）讀取完整的行數(shù)據。

示例：

SELECT * FROM t WHERE a < 10 OR b < 20;
-- 或者
SELECT * FROM t WHERE a < 10 OR b = 20; -- 一個范圍，一個等值

優(yōu)化器使用 idx_a 掃描 a < 10（得到主鍵集合 S1）。
使用 idx_b 掃描 b < 20（或 b = 20）（得到主鍵集合 S2）。
分別對 S1 和 S2 中的主鍵排序。
對兩個有序列表進行歸并排序并去重。
根據結果回表取數(shù)據。
EXPLAIN 輸出： type 列顯示 index_merge，Extra 列顯示 Using sort_union(idx_a, idx_b); Using where。

二、索引合并的優(yōu)點

避免全表掃描：當沒有單個復合索引可以覆蓋所有查詢條件時，索引合并提供了利用現(xiàn)有多個單列索引的可能性，避免代價高昂的全表掃描。
利用現(xiàn)有索引：如果表上已經存在多個單列索引，優(yōu)化器可以嘗試利用它們，而不一定需要為特定查詢創(chuàng)建新的復合索引（盡管復合索引通常更好）。
處理復雜 OR 條件：對于 OR 連接的復雜條件，索引合并（特別是 sort_union）提供了一種優(yōu)化的執(zhí)行路徑。

三、索引合并的缺點與注意事項

通常不如復合索引高效：

額外開銷：索引合并需要進行多個獨立的索引掃描、結果集的合并操作（交集、并集、排序歸并去重），這些操作本身就有開銷。
多次回表：合并操作是基于主鍵值進行的，最終得到主鍵集后，還需要根據這些主鍵值回表讀取完整的行數(shù)據（如果查詢需要的數(shù)據不在索引中）。而一個設計良好的復合索引可能直接覆蓋查詢（避免回表）或者按最有效的順序定位數(shù)據。
優(yōu)化器成本估算可能不準：合并多個索引的成本估算比使用單個復合索引更復雜，優(yōu)化器可能錯誤地選擇了索引合并，而實際上全表掃描或強制使用某個單索引可能更快（反之亦然）。

不是所有條件組合都適用：

只有特定的 AND/OR 結構且每個條件都能獨立使用索引時才可能觸發(fā)。
索引列類型、查詢條件的具體形式（等值、范圍、函數(shù)、隱式轉換）都會影響優(yōu)化器是否選擇索引合并。
配置影響：索引合并是否啟用受系統(tǒng)變量 optimizer_switch 控制。例如：

-- 查看當前設置
SELECT @@optimizer_switch;
-- 關閉所有索引合并優(yōu)化
SET optimizer_switch = 'index_merge=off';
-- 關閉特定類型的索引合并 (e.g., intersection)
SET optimizer_switch = 'index_merge_intersection=off';

需要確認相關標志（index_merge, index_merge_intersection, index_merge_union, index_merge_sort_union）是開啟的 (on)。

統(tǒng)計信息準確性：優(yōu)化器是否選擇索引合并以及選擇哪種合并算法，高度依賴于表的統(tǒng)計信息（如索引的基數(shù) cardinality）。過時的統(tǒng)計信息可能導致優(yōu)化器做出錯誤的選擇。

替代方案 - 優(yōu)先考慮復合索引：

最佳實踐：對于經常一起出現(xiàn)在 WHERE 子句中的列，尤其是通過 AND 連接的列，創(chuàng)建合適的復合索引通常是性能最優(yōu)的選擇。復合索引直接按索引順序定位滿足所有條件的行，避免了多索引掃描和合并的開銷，也更容易避免回表（如果索引覆蓋查詢）。
示例：對于 SELECT * FROM t WHERE a = 10 AND b = 20;，創(chuàng)建 INDEX idx_a_b (a, b) 或 INDEX idx_b_a (b, a) 通常會比依賴 idx_a 和 idx_b 的索引合并快得多。

四、如何識別索引合并

使用 EXPLAIN 或 EXPLAIN FORMAT=JSON 查看查詢的執(zhí)行計劃：

type 列：顯示為 index_merge。
key 列：列出實際使用的索引，多個索引用逗號分隔（如 idx_a, idx_b）。
Extra 列：明確指出使用的合并算法：
- Using intersect(...) (交集)
- Using union(...) (并集)
- Using sort_union(...) (排序并集)