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掉。

 

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注