欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間：2025年07月21日 09:13:18   作者：碼上庫利南  
MySQL索引合并通過多索引掃描與結(jié)果集合并優(yōu)化查詢,本文主要介紹了Mysql索引合并的實現(xiàn)示例,具有一定的參考價值,感興趣的可以了解一下

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

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

一、索引合并的類型

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

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

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

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

  • 獲取每個索引掃描得到的主鍵值（或行指針）集合。
  • 計算這些主鍵值集合的交集（即同時出現(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`)
);
-- 假設 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 的交集。
  • 根據(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(...)):

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

工作原理：

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

示例：

SELECT * FROM t WHERE a = 10 OR b = 20;
  • 優(yōu)化器可能分別使用 idx_a 查找 a=10 的行（得到主鍵集合 S1）。
  • 使用 idx_b 查找 b=20 的行（得到主鍵集合 S2）。
  • 計算 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(...)):

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

工作原理：

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

示例：

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 中的主鍵排序。
  • 對兩個有序列表進行歸并排序并去重。
  • 根據(jù)結(jié)果回表取數(shù)據(jù)。
  • EXPLAIN 輸出： type 列顯示 index_merge，Extra 列顯示 Using sort_union(idx_a, idx_b); Using where。

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

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

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

通常不如復合索引高效：

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

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

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

需要確認相關(guān)標志（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)先考慮復合索引：

  • 最佳實踐： 對于經(jīng)常一起出現(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(...) (排序并集)

五、總結(jié)

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

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

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

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

相關(guān)文章

最新評論