Shulou(Shulou.com)05/31 Report--

Small series to share with you MySQL Index Condition Pushdown(ICP) what are the restrictions on the use of, I hope you have some gains after reading this article, let us discuss it together!

ICP (index condition pushdown) is an optimization operation of mysql to extract data records from tables by using index (secondary index) tuples and where conditions of sieve fields in indexes. The idea of ICP is that the storage engine checks the pushed index condition of the filter field in the index when accessing the index, and filters out the data record if the data in the index tuple does not meet the pushed index condition. ICP (Optimizer) pushes the processing of index conditions down from the server layer to the storage engine layer as much as possible. The storage engine filters irrelevant data using an index and returns only data that meets the index condition to the server layer. That is to say, data filtering should be done at the storage engine layer as much as possible, instead of returning all data to the server layer and then filtering according to the where condition.

Index Condition Pushdown (ICP) is a new feature in MySQL version 5.6 and is an optimized way to filter data using indexes at the storage engine level.

a When ICP is turned off,index is only an access method for data access. The data obtained by the storage engine through indexing back to the table will be passed to the MySQL Server layer for where conditional filtering.

b When ICP is turned on, if some where conditions can use fields in the index,MySQL Server will push this part down to the engine layer. You can use the where condition of index filtering to filter data at the storage engine layer, instead of passing all the results through index access to MySQL Server for where filtering.

Optimization effect:ICP can reduce the number of times the engine layer accesses the base table and MySQL Server accesses the storage engine, reduce the number of io, and improve query statement performance.

When ICP is enabled, MySQL will filter data using indexes at the storage engine layer to reduce unnecessary table returns. Note that the dotted line using where indicates that if the where condition contains fields that are not indexed, it will still be filtered by MySQL Server layer.

ICP usage restrictions:

1 When sql requires full table access,ICP's optimization strategy can be used for access data methods of type range, ref, eq_ref, ref_or_null.

2 Support InnoDB and MyISAM tables.

ICP can only be used for secondary indexes and cannot be used for primary indexes.

4 Not all where conditions can be filtered with ICP.

If the field of the where condition is not in the index column, you still need to read the records of the whole table to the server side for where filtering.

5 The acceleration effect of ICP depends on the proportion of data filtered out by ICP within the storage engine.

6 Version 5.6 does not support the ICP function of sub-tables, but version 5.7 starts to support it.

7 ICP optimization method is not supported when sql uses overlay indexes.

After reading this article, I believe you have a certain understanding of "MySQL Index Condition Pushdown(ICP)". If you want to know more about it, please pay attention to the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.