索引下推優(yōu)化官方文檔說明 (Section 8.2.1.5)：https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

1. 什么是索引下推？

\qquad 索引條件下推 (Index Condition Pushdown，ICP) 是 MySQL 在使用索引從表中檢索行時的一種優(yōu)化方法。在沒有 ICP 的情況下，存儲引擎遍歷索引以定位基表中的行，并將它們返回給 MySQL 服務(wù)器，由服務(wù)器對行進(jìn)行 WHERE 條件的評估。在啟用 ICP 的情況下，并且如果 WHERE 條件的某些部分可以僅使用索引列進(jìn)行評估，則 MySQL 服務(wù)器將此部分 WHERE 條件下推給存儲引擎。ICP 可以減少存儲引擎訪問基表的次數(shù)和 MySQL 服務(wù)器訪問存儲引擎的次數(shù)。

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

2. 索引下推優(yōu)化有什么約束條件？

\qquad 索引條件下推優(yōu)化的適用性受以下條件的約束：

• 當(dāng)需要訪問完整的表行時 (SELECT *) 時，ICP 被用于 range、ref、eq_ref 和 ref_or_null 訪問方法。
• ICP 可以用于 InnoDB 和 MyISAM 表，包括分區(qū)的 InnoDB 和 MyISAM 表。
• 對于 InnoDB 表，ICP 僅用于二級索引，此時 ICP 的目標(biāo)是減少完整行讀取的次數(shù)，從而減少 I/O 操作；對于 InnoDB 聚簇索引，完整記錄已經(jīng)讀入 InnoDB 緩沖區(qū)，在這種情況下使用 ICP 不會減少 I/O。
• 創(chuàng)建在虛擬生成列的二級索引不支持 ICP (InnoDB 支持在虛擬生成列上創(chuàng)建二級索引)。
• 涉及子查詢的條件無法被下推。
• 涉及存儲函數(shù)的條件無法被下推 (存儲引擎無法調(diào)用存儲函數(shù))。
• 觸發(fā)條件無法下推 (關(guān)于觸發(fā)條件的信息，參見 8.2.2.3 節(jié)，“使用 EXISTS 策略優(yōu)化子查詢”)。

Applicability of the Index Condition Pushdown optimization is subject to these conditions:

• ICP is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows.
• ICP can be used for InnoDB and MyISAM tables, including partitioned InnoDB and MyISAM tables.
• For InnoDB tables, ICP is used only for secondary indexes. The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O.
• ICP is not supported with secondary indexes created on virtual generated columns. InnoDB supports secondary indexes on virtual generated columns.
• Conditions that refer to subqueries cannot be pushed down.
• Conditions that refer to stored functions cannot be pushed down. Storage engines cannot invoke stored functions.
• Triggered conditions cannot be pushed down. (For information about triggered conditions, see Section 8.2.2.3, “Optimizing Subqueries with the EXISTS Strategy”.)

3. 索引下推優(yōu)化如何工作？

\qquad 為了理解 (索引下推) 優(yōu)化如何工作，首先考慮當(dāng) ICP 未使用時索引的掃描過程：

• 獲取下一行，首先通過讀取索引元組，然后使用索引元組來定位并讀取完整的表行 (根據(jù)索引元祖保存的主鍵回表)。
• 對應(yīng)用于該表的 WHERE 條件部分進(jìn)行測試，根據(jù)測試結(jié)果接受或拒絕該行。

\qquad 使用 ICP，掃描流程則如下所示：

• 獲取下一行的索引元組 (但不獲取完整的表行)。
• 對應(yīng)用于該表且僅使用索引列即可進(jìn)行檢驗的 WHERE 條件部分進(jìn)行測試，如果條件不滿足，則繼續(xù)下一行的索引元組。
• 如果條件滿足，則使用索引元組來定位并讀取完整的表行 (回表，但是由于索引下推過濾了索引元祖故減少了回表的數(shù)量)。
• 對適用于該表的剩余 WHERE 條件部分進(jìn)行測試，根據(jù)測試結(jié)果接受或拒絕該行。

To understand how this optimization works, first consider how an index scan proceeds when Index Condition Pushdown is not used:

• Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.
• Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

Using Index Condition Pushdown, the scan proceeds like this instead:

• Get the next row’s index tuple (but not the full table row).
• Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row.
• If the condition is satisfied, use the index tuple to locate and read the full table row.
• Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

4. 如何判斷 SQL 是否會走索引下推優(yōu)化？

\qquad 當(dāng)使用索引條件下推時，EXPLAIN 的輸出在 Extra 列會顯示 “Using index condition”，它不顯示 " Using index" 是因為當(dāng)必須讀取整個表行時 ICP 不適用。

EXPLAIN output shows Using index condition in the Extra column when Index Condition Pushdown is used. It does not show Using index because that does not apply when full table rows must be read.

\qquad 總之就是，explain + sql 的打印的表信息的 Extra 列顯示 “Using index condition” 時，則使用了索引下推。

5. 索引下推的官方例子？

\qquad 假設(shè)某個表包含有關(guān)人員及其地址的信息，并且該表具有定義為 INDEX (zipcode, lastname, firstname) 的索引。如果我們知道一個人的郵政編碼值但不確定姓氏，我們可以像這樣搜索：

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

\qquad MySQL 可以使用索引掃描 zipcode=‘95054’ 的人，第二部分 (姓氏 LIKE ‘%etrunia%’) 不能用于限制必須掃描的行數(shù)，因此如果沒有索引條件下推，此查詢必須檢索 zipcode=‘95054’ 的所有用戶的全表行。
\qquad 使用索引條件下推，MySQL 在讀取整個表行之前檢查姓氏 LIKE ‘%etrunia%’ 部分，這樣可以避免讀取與郵政編碼條件匹配但與姓氏條件不匹配的索引元組對應(yīng)的整行。
\qquad 索引下推優(yōu)化默認(rèn)開啟，它可以通過使用 optimizer_switch 系統(tǒng)變量設(shè)置 index_condition_pushdown 的標(biāo)識控制 (是否啟用)：文章來源地址http://www.zghlxwxcb.cn/news/detail-564381.html

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

到了這里，關(guān)于MySQL 參考文檔：SQL 語句優(yōu)化（SELECT 語句優(yōu)化）之索引條件下推（索引下推）優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！