MySQL索引下推技術

MySQL索引下推技術

索引下推整個思路如下：

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

1. 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.

2. Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

When Index Condition Pushdown is used, the scan proceeds like this instead:

1. Get the next row's index tuple (but not the full table row).

2. 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.

3. If the condition is satisfied, use the index tuple to locate and read the full table row.

4. Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

舉例如下：

Suppose that we have a table containing information about people and their addresses and that the table has an index defined as INDEX (zipcode, lastname, firstname). If we know a person's zipcode value but are not sure about the last name, we can search like this:

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

people表中（zipcode，lastname，firstname）構成一個索引。

如果沒有使用索引下推技術，則MySQL會通過zipcode='95054'從儲存引擎中查詢對應的元祖，返回到MySQL服務端，然後MySQL服務端基於lastname LIKE '%etrunia%'和address LIKE '%Main Street%'來判斷元祖是否符合條件。

如果使用了索引下推技術，則MYSQL首先會返回符合zipcode='95054'的索引，然後根據lastname LIKE '%etrunia%'和address LIKE '%Main Street%'來判斷索引是否符合條件。如果符合條件，則根據該索引來定位對應的元祖，如果不符合，則直接reject掉。

本文永久更新連結地址：