Rechercher dans le manuel MySQL
8.2.1.5 Index Condition Pushdown Optimization
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.
Applicability of the Index Condition Pushdown optimization is subject to these conditions:
ICP is used for the
range
,ref
,eq_ref
, andref_or_null
access methods when there is a need to access full table rows.ICP can be used for
InnoDB
andMyISAM
tables, including partitionedInnoDB
andMyISAM
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. ForInnoDB
clustered indexes, the complete record is already read into theInnoDB
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”.)
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.
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.
Suppose that a table contains 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:
MySQL can use the index to scan through people with
zipcode='95054'
. The second part
(lastname LIKE '%etrunia%'
) cannot be used
to limit the number of rows that must be scanned, so without
Index Condition Pushdown, this query must retrieve full table
rows for all people who have
zipcode='95054'
.
With Index Condition Pushdown, MySQL checks the
lastname LIKE '%etrunia%'
part before
reading the full table row. This avoids reading full rows
corresponding to index tuples that match the
zipcode
condition but not the
lastname
condition.
Index Condition Pushdown is enabled by default. It can be
controlled with the
optimizer_switch
system
variable by setting the
index_condition_pushdown
flag:
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-index-condition-pushdown-optimization.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.