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

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

一、索引合并的類型

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

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

適用場(chǎng)景： WHERE 子句中的多個(gè)條件通過(guò) AND 連接，并且每個(gè)條件都可以有效地使用一個(gè)單獨(dú)的索引（這些索引通常是單列索引）。

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

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

示例：

CREATE TABLE `t` (
  `id` INT PRIMARY KEY,
  `a` INT,
  `b` INT,
  `c` VARCHAR(100),
  INDEX `idx_a` (`a`),
  INDEX `idx_b` (`b`)
);
-- 假設(shè) 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）。
• 計(jì)算 S1 和 S2 的交集。
• 根據(jù)交集結(jié)果回表取數(shù)據(jù)。
• EXPLAIN 輸出： type 列顯示 index_merge，Extra 列顯示 Using intersect(idx_a, idx_b); Using where。

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

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

工作原理：

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

示例：

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

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

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

適用場(chǎng)景： WHERE 子句中的多個(gè)條件通過(guò) OR 連接，但是這些條件無(wú)法直接使用 Index Merge Union（通常是因?yàn)樗饕龗呙璺祷氐氖欠秶Y(jié)果，而不僅僅是點(diǎn)查詢的等值結(jié)果）。它是 Union 的一種變體，用于處理范圍掃描。

工作原理：

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

示例：

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

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

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

• 避免全表掃描： 當(dāng)沒(méi)有單個(gè)復(fù)合索引可以覆蓋所有查詢條件時(shí)，索引合并提供了利用現(xiàn)有多個(gè)單列索引的可能性，避免代價(jià)高昂的全表掃描。
• 利用現(xiàn)有索引： 如果表上已經(jīng)存在多個(gè)單列索引，優(yōu)化器可以嘗試?yán)盟鼈儯灰欢ㄐ枰獮樘囟ú樵儎?chuàng)建新的復(fù)合索引（盡管復(fù)合索引通常更好）。
• 處理復(fù)雜 OR 條件： 對(duì)于 OR 連接的復(fù)雜條件，索引合并（特別是 sort_union）提供了一種優(yōu)化的執(zhí)行路徑。

三、索引合并的缺點(diǎn)與注意事項(xiàng)

通常不如復(fù)合索引高效：

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

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

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

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

需要確認(rèn)相關(guān)標(biāo)志（index_merge, index_merge_intersection, index_merge_union, index_merge_sort_union）是開啟的 (on)。

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

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

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

四、如何識(shí)別索引合并

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

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

五、總結(jié)

MySQL 的索引合并（Index Merge）是一種在特定查詢條件下（涉及多個(gè)索引列且條件由 AND 或 OR 連接），優(yōu)化器利用多個(gè)獨(dú)立索引分別掃描數(shù)據(jù)，然后對(duì)結(jié)果集進(jìn)行交集、并集或排序后并集操作，最終定位目標(biāo)行的優(yōu)化策略。

• intersect 處理 AND 條件。
• union / sort_union 處理 OR 條件（sort_union 處理范圍掃描）。

雖然索引合并提供了一種避免全表掃描的途徑，但它通常伴隨著額外的掃描、合并和回表開銷。創(chuàng)建合適的復(fù)合索引（Composite Index）通常是解決這類查詢性能問(wèn)題的首選和更優(yōu)方案，因?yàn)樗芨苯?、高效地定位?shù)據(jù)。

到此這篇關(guān)于Mysql索引合并的實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)Mysql索引合并內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！