在 MySQL 中，索引主要用于優(yōu)化查詢。在 MySQL 查詢優(yōu)化中涉及到三種處理階段：Index Key、Index Filter 和 Table Filter。

它們描述的是數(shù)據(jù)庫(kù)在使用索引時(shí)，查詢條件的匹配和過濾發(fā)生的過程，這個(gè)分類的背景通常出現(xiàn)在討論索引下推（Index Condition Pushdown, ICP）時(shí)。

以下是對(duì)這三種索引處理階段的詳細(xì)說明：

1. Index Key

定義：索引鍵 (Index Key) 是指索引的關(guān)鍵列，即索引中存儲(chǔ)的字段值。索引鍵是用來幫助定位數(shù)據(jù)行的，查詢通常首先使用索引鍵來篩選匹配最基礎(chǔ)條件的記錄。

使用場(chǎng)景：

當(dāng)查詢條件和索引的定義一致時(shí)，例如：

SELECT * FROM employees WHERE employee_id = 123;

如果 employee_id 有索引，數(shù)據(jù)庫(kù)可以通過索引鍵快速找到符合條件的記錄。

特性：

• 這是索引的基本功能，通過索引鍵直接定位滿足條件的記錄。
• 索引鍵檢索的是精確匹配或者范圍掃描的記錄。

查詢執(zhí)行流程：

通過索引鍵快速定位對(duì)應(yīng)的記錄集合。

2. Index Filter

定義：索引過濾 (Index Filter) 是對(duì)索引存儲(chǔ)的數(shù)據(jù)進(jìn)行進(jìn)一步過濾，用于實(shí)現(xiàn)更復(fù)雜的查詢條件，而無需先通過索引定位所有數(shù)據(jù)然后回表。索引過濾是在存儲(chǔ)引擎層完成的，是索引下推優(yōu)化的關(guān)鍵部分。

使用場(chǎng)景：

查詢條件涉及多個(gè)字段，但不是全部字段都能通過索引鍵直接定位。例如：

SELECT * FROM employees WHERE employee_id > 100 AND salary < 50000;

假設(shè)有索引 (employee_id, salary)：

• 數(shù)據(jù)庫(kù)通過 employee_id > 100 定位部分范圍的記錄；
• 然后在存儲(chǔ)層通過 salary < 50000 進(jìn)一步過濾索引中的記錄，而不是直接將所有匹配 employee_id > 100 的記錄返回到 Server 層。

特性：

• 索引過濾是對(duì)索引本身存儲(chǔ)的數(shù)據(jù)進(jìn)行字段值篩選，而不是直接訪問表。
• 索引下推優(yōu)化后，在存儲(chǔ)引擎層完成這部分過濾，提高了查詢效率。

查詢執(zhí)行流程：

• 基于索引鍵定位候選記錄。
• 在存儲(chǔ)層進(jìn)一步篩選索引中的記錄，減少上層（Server Layer）需要處理的數(shù)據(jù)量。

3. Table Filter

定義：表過濾 (Table Filter) 是指數(shù)據(jù)庫(kù)通過回表查詢數(shù)據(jù)后，再對(duì)返回的表中數(shù)據(jù)進(jìn)行過濾。這通常是針對(duì)查詢條件中涉及的非索引列，或者索引本身無法過濾的情況。

使用場(chǎng)景：

查詢條件涉及非索引字段，例如：

SELECT * FROM employees WHERE employee_id > 100 AND department = 'Engineering';

假設(shè)只有索引 (employee_id)：

• 數(shù)據(jù)庫(kù)通過索引范圍查詢 employee_id > 100；
• 獲取記錄后，需要回表讀取 department 列，并在 Server 層過濾 department = 'Engineering' 的條件。

特性：

• 表過濾發(fā)生在 Server 層（服務(wù)層），需要通過索引定位記錄后，回表查詢?cè)加涗浽龠M(jìn)行過濾。
• 如果查詢條件中非索引列過多，或者數(shù)據(jù)量較大，表過濾會(huì)帶來性能開銷。

查詢執(zhí)行流程：

• 基于索引鍵定位候選記錄。
• 回表查詢?cè)紨?shù)據(jù)。
• 在 Server 層對(duì)數(shù)據(jù)進(jìn)行過濾，符合條件的記錄才會(huì)返回給用戶。

三類過濾物理過程

Index Key 初始階段，通過索引鍵快速定位候選記錄。

Index Filter 在存儲(chǔ)引擎層上對(duì)候選記錄進(jìn)行進(jìn)一步過濾，減少需要回表的記錄數(shù)。

Table Filter 如果查詢涉及非索引列或更復(fù)雜的過濾條件，需要回表查詢，并在服務(wù)器層最終過濾。

索引下推重要點(diǎn)

MySQL 5.6 之前，一旦記錄在索引 Key 查找到，所有復(fù)雜條件的過濾都在 Server 層完成（包括非下推的 Index Filter 和 Table Filter）。

MySQL 5.6 開始支持索引下推 (ICP)，將部分過濾邏輯 (Index Filter) 下推到存儲(chǔ)引擎層，并在回表查詢之前完成過濾，顯著減少了回表次數(shù)和 Server 層的壓力。

示例

假設(shè)有一個(gè)包含索引 (employee_id, salary) 的表，查詢?nèi)缦拢?/p>

SELECT * FROM employees WHERE employee_id > 100 AND salary < 50000 AND department = 'Engineering';

• Index Key: 索引通過 employee_id > 100 進(jìn)行范圍掃描，獲取候選記錄。
• Index Filter（索引下推實(shí)現(xiàn)）: 在存儲(chǔ)層進(jìn)一步通過 salary < 50000 過濾出滿足條件的記錄，減少回表的次數(shù)。
• Table Filter: 回表查詢后，對(duì) department = 'Engineering' 的條件進(jìn)行過濾，最終返回結(jié)果。

索引下推

索引下推（Index Condition Pushdown, ICP）是數(shù)據(jù)庫(kù)查詢優(yōu)化的一種技術(shù)。它主要用于提升數(shù)據(jù)庫(kù)查詢性能，尤其是順序掃描大表或使用索引進(jìn)行過濾時(shí)。索引下推在 MySQL 5.6 引入，是針對(duì)索引的查詢優(yōu)化。

簡(jiǎn)單解釋

索引下推的核心思想是把一部分查詢條件“下推”到存儲(chǔ)層的索引掃描過程，而無需每次都把數(shù)據(jù)從存儲(chǔ)層讀到服務(wù)層做判斷。這樣可以減少需要訪問的數(shù)據(jù)行數(shù)，從而優(yōu)化查詢速度。

傳統(tǒng)索引掃描

在沒有索引下推時(shí)，當(dāng)查詢涉及多個(gè)篩選條件（WHERE 子句）時(shí)，數(shù)據(jù)庫(kù)先通過索引查找到滿足部分條件的記錄，但并不會(huì)馬上應(yīng)用所有的條件過濾。它會(huì)將索引匹配到的記錄獲取到服務(wù)層（Server Layer）后再檢查剩余的條件是否符合，然后決定結(jié)果是否返回給用戶。

這種做法在數(shù)據(jù)量大或涉及復(fù)雜條件時(shí)，可能會(huì)導(dǎo)致服務(wù)層不得不處理大量不必要的數(shù)據(jù)記錄，從而性能不佳。

有索引下推的查詢流程

索引下推允許直接在存儲(chǔ)引擎層應(yīng)用更多的篩選條件，而不需要將所有的篩選工作都依賴上層來完成。存儲(chǔ)層在掃描索引時(shí)，直接應(yīng)用部分條件來過濾記錄，減少向服務(wù)層返回的記錄數(shù)量。

舉例說明：

假如有一個(gè)表 products，帶有索引 (category_id, price)，查詢語(yǔ)句如下：

SELECT * FROM products WHERE category_id = 10 AND price < 100;

沒有索引下推：

• 存儲(chǔ)層通過索引 (category_id, price) 找到所有 category_id = 10 的記錄。
• 然后將這些記錄返回給服務(wù)層。
• 服務(wù)層對(duì)這些記錄再進(jìn)行過濾，看 price < 100 的記錄是否符合條件。
• 在這個(gè)過程中，可能會(huì)發(fā)送大量數(shù)據(jù)到服務(wù)層處理，增大系統(tǒng)開銷。

有索引下推：

• 存儲(chǔ)層通過索引 (category_id, price)，不僅用于定位 category_id = 10 的記錄，還直接在存儲(chǔ)層檢查 price < 100 條件。
• 只有完全滿足條件的記錄才會(huì)返回給服務(wù)層。
• 服務(wù)層需要處理的數(shù)據(jù)量顯著減少，查詢效率提升。

優(yōu)勢(shì)

• 降低IO開銷：因?yàn)榇鎯?chǔ)層生成的滿足條件的記錄更少，處理的數(shù)據(jù)量減少了。
• 更快的查詢速度：減少服務(wù)層進(jìn)行二次篩選的壓力。
• 無需修改查詢語(yǔ)句：索引下推是存儲(chǔ)引擎的優(yōu)化機(jī)制，無需用戶對(duì) SQL 語(yǔ)句進(jìn)行額外調(diào)整。

使用注意

• 是否能夠啟用索引下推，取決于存儲(chǔ)引擎以及索引類型。
• 在 MySQL 中，只有 InnoDB 存儲(chǔ)引擎支持索引下推。
• 索引下推并不總是顯著提升查詢性能，其實(shí)際效果依賴于查詢復(fù)雜度、數(shù)據(jù)分布、索引選擇等因素。

如何驗(yàn)證索引下推

你可以通過 EXPLAIN 命令檢查查詢計(jì)劃，如果查詢使用了索引下推，會(huì)看到關(guān)鍵字 Using index condition，例如：

EXPLAIN SELECT * FROM products WHERE category_id = 10 AND price < 100;

輸出可能包括：

Extra: Using index condition

如果沒有 "Using index condition"，則說明沒有啟用索引下推。

總之，索引下推是數(shù)據(jù)庫(kù)引擎的一項(xiàng)重要優(yōu)化技術(shù)，它通過讓存儲(chǔ)層承擔(dān)更多的篩選工作，顯著提升了查詢性能，特別是在使用復(fù)合索引的場(chǎng)景中。

小結(jié)

索引下推利用了 Index Filter 在存儲(chǔ)層完成過濾的能力，減少了回表次數(shù)和 Server 層處理數(shù)據(jù)的壓力，從而優(yōu)化了查詢性能。在實(shí)際使用索引時(shí)，通過合理的覆蓋索引設(shè)計(jì)，可進(jìn)一步減少回表，提高效率。

到此這篇關(guān)于MySQL查詢優(yōu)化的三種處理階段(Index Key、Index Filter和Table Filter)詳解的文章就介紹到這了,更多相關(guān)MySQL查詢優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論