Shulou(Shulou.com)06/01 Report--

MRR "Multi-Range Read" has a preliminary understanding of splitting the where condition to reduce finding too much useless data through the index; after querying the primary key ID of the leaf node of the index page, it does not read the data directly, but sorts the primary key ID that meets the condition, and then carries on the data search.

MySQL 5.6 begins to support Multi-Range Read (MRR) optimization. The goal is to reduce random access to the disk and to convert random access into more sequential data access, which can greatly improve the performance of IO-bound-type SQL query statements. MRR optimization can be applied to rangeref,eq_ref-type queries

Benefits of MRR optimization:

A) MRR makes data access more sequential. When querying the secondary index, first sort according to the primary key according to the query results, and look up the bookmarks according to the order of the primary key sort.

B) reduce the number of page replacements in the buffer pool

C) batch processing of query operations for key values

For the scope query and JOIN query operations of the InnoDB and MyISAM storage engines, MRR works as follows

A) the secondary index key values obtained by the query are stored in a cache, where the data in the cache is sorted according to the secondary index key values

B) sort the keys in the cache according to RowID

C) access the actual data files according to the sort order of the RowID

Index Condition Pushdown (ICP) is an optimization in which MySQL uses the index to fetch data from the table. If ICP is disabled, the engine layer looks for data rows in the base table through the index, then returns them to the MySQL Server layer, and then filters the WHERE conditions for those data rows. ICP is enabled, and if some of the WHERE conditions can use the fields in the index, MySQL Server will push that part down to the engine layer. The storage engine evaluates by using index entries and then pushing index conditions, and uses this index to read satisfied rows from the table. ICP can reduce the number of times the engine layer accesses the base table and the MySQL Server accesses the storage engine.

ICP "Index Condition Pushdown" has a preliminary understanding of further filtering other where conditions through an auxiliary index query, as long as the data for the where condition is available in that index.

Remarks personally understand that both optimization premises rely on indexes, while ICP relies on federated indexes.

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.