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

This article mainly introduces "what is the meaning of pushing down the MySQL index". In the daily operation, I believe that many people have doubts about what it means to push down the MySQL index. the editor consulted all kinds of data and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the doubt of "what is the meaning of pushing down the MySQL index?" Next, please follow the editor to study!

Catalogue

I. the principle of index push-down optimization

Second, the concrete practice of index push-down

1. Do not use ICP

2. Use ICP

III. Conditions for the use of index pushdown

Index push-down (Index Condition Pushdown, referred to as ICP) is a new feature of the MySQL5.6 version, which can reduce the number of query times back to the table and improve the query efficiency.

I. the principle of index push-down optimization

Let's take a brief look at the general architecture of MySQL:

The MySQL service layer is responsible for SQL syntax parsing, generating execution plans, etc., and calls the storage engine layer to perform data storage and retrieval.

Index push-down actually refers to leaving part of the responsibility of the upper layer (service layer) to the lower layer (engine layer) to deal with.

Let's take a look at the query for MySQL without using ICP:

Storage engine reads index records

Locate and read the complete row record based on the primary key value in the index

The storage engine hands the record to the Server layer to detect whether the record meets the WHERE condition.

In the case of ICP, the query process:

The storage engine reads the index record (not a complete row record)

Determine whether the WHERE condition part can be checked by the columns in the index. If the condition is not satisfied, the next row of index records will be processed.

If the condition is met, use the primary key in the index to locate and read the complete row record (that is, the so-called back table)

The storage engine hands the record to the Server layer, and the Server layer detects whether the record meets the rest of the WHERE condition.

Second, the concrete practice of index push-down

The theory is quite abstract, so let's put it into practice.

Use a user table tuser to create a federated index (name, age).

If there is a need now: retrieve all users whose first word in the table is Zhang and whose age is 10 years old. So, the SQL statement goes like this:

Select * from tuser where name like 'Zhang%' and age=10

If you understand the leftmost matching principle of the index, then you know that this statement can only use Zhang when searching the index tree, and the first record found that meets the criteria id is 1.

So what's the next step?

1. Do not use ICP

Before MySQL 5.6, the storage engine found the primary key id (1,4) of name like 'Zhang%' through the federated index, scanned the tables one by one, declustered the index to find the complete row records, and then the server layer filtered the data according to age=10.

Let's take a look at the diagram:

You can see that we need to go back to the table twice, wasting age, another field of our federated index.

2. Use ICP

After MySQL 5.6, the storage engine finds name like 'Zhang%' according to the (name,age) federated index. Because the federated index contains age columns, the storage engine directly filters by age=10 in the federated index. Return to the table scan one by one according to the filtered data.

Let's take a look at the diagram:

You can see that it only returned to the table once.

In addition, we can also take a look at the execution plan and see the Using index condition in the Extra column, which is the index push-down.

+- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-- + | 1 | SIMPLE | tuser | NULL | range | na_index | na_index | 102 | NULL | 2 | 25.00 | Using index condition | + -+-+ 3. Index push-down conditions of use

Can only be used for range, ref, eq_ref, ref_or_null access methods

Can only be used with InnoDB and MyISAM storage engines and their partition tables

For the InnoDB storage engine, index pushdown applies only to secondary indexes (also known as secondary indexes)

The purpose of index push-down is to reduce the number of returns to the table, that is, to reduce IO operations. For clustered indexes in InnoDB, the data and the index are together, and there is no such thing as going back to the table.

A condition referencing a subquery cannot be pushed down

The condition that references the storage function cannot be pushed down because the storage engine cannot call the storage function.

Related system parameters:

Under index condition, push is enabled by default. You can use the system parameter optimizer_switch to determine whether the controller is enabled or not.

View the default status:

Mysql > select @ @ optimizer_switch\ G * * 1. Row * * @ optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on Subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on1 row in set (0.00 sec)

Switch status:

Set optimizer_switch= "index_condition_pushdown=off"; set optimizer_switch= "index_condition_pushdown=on"; at this point, the study on "what is the meaning of MySQL index push down" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.