揭秘MySQL範圍查詢最佳化：MRR（多範圍讀取最佳化）如何將隨機IO轉化為順序IO

MySQL中MRR（Multi-Range Read）最佳化是一種重要的查詢最佳化技術，其在處理包含多個範圍條件的查詢時，能夠顯著提升查詢效率。

• 一、MRR最佳化概述

• 二、MRR最佳化的背景

• 三、MRR最佳化的原理

• 四、MRR最佳化的優勢

• 五、磁碟預讀機制

• 六、區域性性原理

• 七、使用場景、條件與監控

• 八、SQL案例解讀

一、MRR最佳化概述

MRR，全稱Multi-Range Read Optimization，直譯為多範圍讀取最佳化，是MySQL中一種用於提高索引查詢效能的技術。MRR透過減少隨機磁碟訪問次數，將隨機IO轉換為順序IO，從而提高資料讀取的效率。它特別適用於包含範圍條件（如BETWEEN、<、>等）的查詢，以及需要透過輔助索引訪問表資料的場景。

二、MRR最佳化的背景

在InnoDB中表資料是透過聚集索引組織的。當基於輔助索引的範圍查詢時，需要先透過輔助索引找到對應的主鍵值，再透過主鍵值回表查詢完整的行資料。這種回表會產生大量的隨機磁碟I/O，尤其是在處理大表時，隨機I/O的效能瓶頸尤為明顯。MRR最佳化正是爲了解決這一問題提出。

三、MRR最佳化的原理

MRR最佳化的核心思想是將多個範圍查詢中的隨機磁碟I/O轉換為順序磁碟I/O，從而提高查詢效能。

1. 掃描輔助索引並收集主鍵值：

• 當執行一個包含範圍條件的查詢時，MySQL最佳化器首先會掃描輔助索引，找到滿足條件的一系列索引元組。
• 對於每個索引元組，MySQL會收集其對應的主鍵值（rowid）。

2. 對主鍵值進行排序：

• 收集到的主鍵值會被放入一個記憶體緩衝區（read_rnd_buffer）中。
• 當緩衝區滿或查詢結束時，MySQL會對緩衝區中的主鍵值進行排序。排序的目的是爲了將隨機訪問轉換為順序訪問。

3. 順序訪問基表：

• 排序後的主鍵值將按照順序被用來訪問基表，檢索出完整的資料行。
• 由於主鍵值是有序的，因此訪問基表時產生的磁碟I/O也變為順序I/O，從而提高了讀取效率。

4. 利用磁碟預讀和快取機制：

• MRR最佳化還充分利用了磁碟的預讀機制。當請求讀取某一頁資料時，磁碟會預測並提前讀取相鄰的幾頁資料到記憶體中。
• 由於MRR將隨機訪問轉換為順序訪問，磁碟預讀機制能夠更好地發揮作用，減少磁碟尋道時間和旋轉延遲。
• 同時，順序訪問也提高了快取的命中率，因為連續訪問的資料頁更有可能在快取中找到。

四、MRR最佳化的優勢

• 提高查詢效能：透過減少隨機磁碟I/O次數和提高快取命中率，MRR最佳化能夠顯著提高查詢效能。
• 減少I/O成本：順序I/O比隨機I/O具有更低的成本，因為順序I/O可以更有效地利用磁碟頻寬和快取資源。
• 適用於多種查詢型別：MRR最佳化不僅適用於範圍查詢（如BETWEEN、<、>等），還適用於等值連線（equi-join）等需要回表訪問的場景。

五、磁碟預讀機制

MRR最佳化充分利用了磁碟預讀機制。當客戶端請求讀取某一頁資料時，磁碟預讀功能會預測並提前讀取相鄰的幾頁資料到記憶體緩衝區中。由於MRR將隨機訪問轉換為順序訪問，磁碟預讀機制能夠更好地發揮作用，減少磁碟尋道時間和旋轉延遲，進一步提升讀取效率。

六、區域性性原理

區域性性原理是MRR最佳化的另一個理論基礎。時間區域性性表明，如果某個資料項被訪問，那麼在不久的將來它可能再次被訪問；空間區域性性表明，一旦某個資料項被訪問，那麼其附近的資料項也可能很快被訪問。MRR透過順序訪問資料，使得資料訪問更加符合區域性性原理，從而提高了快取命中率，減少了磁碟訪問次數。

七、使用場景、條件與監控

MRR最佳化適用於基於範圍掃描和等值連線的操作中尤為有效。但是，並非所有查詢都能從MRR最佳化中受益。如，當查詢完全基於索引元組中的資訊（即使用覆蓋索引）時，MRR最佳化就沒有必要，因為此時無需回表訪問基表資料。

此外，MySQL預設開啟MRR最佳化，但是否真正使用MRR由最佳化器決定。最佳化器會根據查詢的成本（如IO成本、CPU成本等）來決定是否採用MRR最佳化。使用者可以透過調整optimizer_switch系統變數中的mrr和mrr_cost_based標誌來控制MRR最佳化的使用。

1. 配置引數

• optimizer_switch：包含mrr和mrr_cost_based兩個選項，分別用於控制是否啟用MRR最佳化以及是否基於成本決定是否使用MRR。
• read_rnd_buffer_size：設定用於給rowid排序的記憶體緩衝區的大小。這個引數的大小會影響MRR最佳化的效果，需要根據實際情況進行調整。

2. 監控方法

• 使用EXPLAIN語句檢視查詢的執行計劃。如果查詢使用了MRR最佳化，EXPLAIN的輸出會在Extra列中顯示Using MRR。
• 監控查詢的響應時間和I/O開銷。透過比較開啟和關閉MRR最佳化時的查詢效能，可以評估MRR最佳化的效果。

八、SQL案例解讀

一個orders的表結構：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    INDEX idx_customer_date (customer_id, order_date)
) ENGINE=InnoDB;

表中，customer_id和order_date上有一個聯合索引idx_customer_date。想要查詢某個特定客戶在指定日期範圍內的所有訂單，SQL語句：

SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2024-09-25' AND '2024-09-26';

1. 掃描輔助索引：

• MySQL首先會利用輔助索引idx_customer_date來定位滿足customer_id = 123和order_date BETWEEN '20234-09-25' AND '2024-09-26'條件的索引元組。
• 這些索引元組包含了customer_id、order_date以及對應的主鍵值

2. 收集並排序主鍵值：

• MySQL會收集這些索引元組對應的主鍵值，並將它們放入一個記憶體緩衝區（read_rnd_buffer）中。
• 當緩衝區滿或查詢結束時，MySQL會對這些主鍵值進行排序。排序的目的是爲了後續的順序訪問基表。

3. 順序訪問基表：

• 使用排序後的主鍵值，MySQL將順序訪問orders表的基表部分，檢索出完整的訂單資料行。
• 由於主鍵值是有序的，因此訪問基表時產生的磁碟I/O變為順序I/O。

4. 利用磁碟預讀和快取機制：

• 在順序訪問基表的過程中，磁碟預讀機制會預測並提前讀取相鄰的資料頁到記憶體中。
• 這有助於減少磁碟尋道時間和旋轉延遲，並提高快取命中率。

5. 查詢效能提升：

• 相比沒有MRR最佳化的情況，使用MRR可以顯著減少隨機磁碟I/O的次數，從而提高查詢效能。
• 特別是在處理大表時，MRR最佳化的效果更加明顯。