MySQL Series (12)--index push-down optimization

The index condition push (ICP) is an optimization of how MySQL uses an index to retrieve rows from a table. Without an ICP, the storage Engine traverses the index to find rows in the base table and returns them to the MySQL server, which evaluates the conditions of the where row. When ICP is enabled, the MySQL server pushes this section if the where only evaluates some of the conditions using the columns in the index. The Where condition to the storage engine. The storage engine then evaluates the pushed index condition by using an index entry and reads rows from the table only if the condition is met. The ICP reduces the number of times the storage engine must access the base table and the number of times that the MySQL server must access the storage engine.

The applicability of push optimization under exponential conditions is limited by the following conditions:

• When the ICP is used for range, ref, EQ_REF, and ref_or_null access methods, it is necessary to access the complete table row.

• The ICP can be used for tables InnoDB and MyISAM tables, including partitioned InnoDB and MyISAM tables.

• For InnoDB tables, the ICP is only used for level two indexes. The objective of the ICP is to reduce the number of full-line reads, thereby reducing I/O operations. For the InnoDB clustered index, the full record has been read into the InnoDB buffer. Using an ICP in this case does not degrade I/O.

• The two-level index created on the virtual build column does not support ICP. The InnoDB supports a level two index on a virtual build column.

• The criteria for referencing a subquery cannot be pushed down.

• A condition referencing a stored function cannot be pushed down. The storage engine cannot invoke stored functions.

• The trigger condition cannot be pushed down. (For information about triggering conditions, see section 8.2.2.4, "Optimizing subqueries using exists policies.") ）

To understand how this optimization works, first consider the progress of index scans when pushing without using index conditions:

Gets the next line, reading the index tuple first, and then using the index tuple to locate and read the entire table row.

Test where the conditions section applies to this table. Accept or reject the row based on the test results.

Using the index condition push, the scan will proceed like this:

Gets the index tuple for the next row (but not the full table row).

Test where the condition section applies to this table, and can only be checked using indexed columns. If the condition is not met, continue to the index tuple for the next row.

If the condition is met, use an index tuple to find and read the entire table row.

Test the remaining portion of the condition that applies to this table. Accept or reject the row based on the test results.

Explain when using index condition push, the output displays the using index condition in the extra column. It is not shown, Using index because this does not apply when the full table row must be read.

Suppose a table contains information about people and their addresses, and the indexes for that table are defined as index (ZipCode, LastName, FirstName). If we know a person's zipcode value but are not sure of the surname, we can search this way:

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

MySQL can use the index to scan people zipcode= ' 95054 '. The second section (LastName like '%etrunia% ') cannot be used to limit the number of rows that must be scanned, so if there is no index Condition pushdown, this query must retrieve the full table row zipcode= ' 95054 ' for all owned people.

Using the index condition push, MySQL lastname like '%etrunia% ' checks this section before reading the entire table row. This avoids reading the complete row corresponding to the index tuple, which matches the zipcode condition but does not conform to the LastName condition.

By default, index conditions are pushed down. You can optimizer_switch to control system variables by setting the INDEX_CONDITION_PUSHDOWN flag:

SET optimizer_switch = ‘index_condition_pushdown=off‘;SET optimizer_switch = ‘index_condition_pushdown=on‘;

Summarize

1. For where constant + like queries you can try to use a federated index

// name和stu_id有联合索引explain select * from course where name = ‘ww‘ and stu_id like ‘%4%‘;

2. For where constant + ORDER by index column you can try to use a federated index;

// name和stu_id有联合索引explain select * from course where  name = ‘ww‘ order by stu_id;

The using index condition is used in all of the above. The first is to filter like fuzzy matches, and the second is to sort the federated indexes.

Frequently used scenarios for index push-down:

• For level Two indexes
• The column for select does not use the overwrite index
• Multi-Criteria query (where multi-criteria, where + order by) + Federated Index

Reference

Index Condition pushdown optimization

MySQL Series (12)--index push-down optimization