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

What are the characteristics of ICP in MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

I. brief introduction of ICP

ICP: the full name is Index Condition Pushdown, which is an optimization strategy introduced by MySQL 5.6. To put it simply, it should be pushed to the Innodb engine layer under the condition that MySQL should be filtered. But this strategy and we usually use the index is actually different, we usually use the index generally refers to the use of the index for positioning and access, but here is a filtering operation. Strictly speaking, there is not much difference in filtering between the MySQL layer and the Innodb layer, but because the filtering occurs in the Innodb layer, and the table return and row locking operation (for update) has not been performed yet, the advantages are as follows:

Reduced table return operations

Primary key locking (for update) is reduced after returning to the table, but locking does not change for query indexes.

Reduced data returned to MySQL layer

If Using index condition appears in the execution plan, the push-down operation has been performed. If you want to disable the ICP feature, simply set it as follows:

Set optimizer_switch='index_condition_pushdown=off'

Push-down filtering requires that the conditions for push-down and Innodb layer positioning are included in the same composite index at the same time. For example, the index contains (a, b) three columns. If it is (where push * and c like'%%'), then push-down can be completed.

Of course, there are many other restrictions that are not listed here. You can refer to the official manual for yourself:

8.2.1.5 Index Condition Pushdown Optimization

II. Analysis of WHERE conditions

To facilitate the discussion, we use the following columns:

Mysql > show create table bgicp\ gateway * 1. Row * * Table: bgicpCreate Table: CREATE TABLE `bgicp` (`id` int (11) NOT NULL, `a1` int (11) DEFAULT NULL, `a2` varchar (20) DEFAULT NULL, `a3` varchar (20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a1` (`a1`) `a2`) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql > select * from bgicp +-- + | id | A1 | a2 | a3 | +-- + | 1 | 1 | G1 | G1 | | 2 | 2 | G2 | G2 | | 3 | 2 | g3 | | 4 | 4 | G4 | G4 | 5 | 5 | g5 | g5 | 6 | | | 6 | G6 | G6 | | 7 | 6 | G7 | G7 | | 8 | 6 | G7 | G8 | 9 | 9 | G9 | G9 | | 10 | G10 | G10 | +-+ mysql > desc select * from bgicp where a1room6 and a2 like'% 7' | +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-- + | 1 | SIMPLE | bgicp | NULL | ref | A1 | A1 | 5 | const | 3 | 11.11 | Using index condition | + -- +

The where conditions here are stored in st_select_lex.m_where_cond. The following picture shows this relationship:

The following part is the proof, you don't have to pay attention:

Here, we can actually do some verification through gdb, and the breakpoint can be placed on st_select_lex::prepare, as follows:

And symbol:

(gdb) p ((Item_cond *) m_where_cond)-> functype () $43 = Item_func::COND_AND_FUNC (gdb) p ((Item_cond_and *) m_where_cond)-> list- > elements$64 = 2 (this is the number of elements in AND, here is 2, as shown above)

= symbol:

(gdb) p ((Item_cond_and *) m_where_cond)-> list- > first- > info$47 = (void *) 0x7ffe7c007690 (gdb) p ((Item_cond*) ((void *) 0x7ffe7c007690))-> functype () $48 = Item_func::EQ_FUNC

A = 6 condition:

(gdb) p ((Item_func_eq*) ((void *) 0x7ffe7c007690))-> args [1] $49 = (Item *) 0x7ffe7c006888 (gdb) p ((Item_int *) $49)-> value$50 = 6 (this is the value 6) (gdb) p ((void *) 0x7ffe7c007690)-> args [0] $59 = (Item *) 0x7ffe7c007570 (gdb) p ((Item_field *) $59)-> field_name$60 = 0x7ffe7c0067c0 "A1" (this is the field A1)

Like symbol:

(gdb) p ((Item_cond_and *) m_where_cond)-> list- > first- > next- > info$51 = (void *) 0x7ffe7c006b60 (gdb) p ((Item_cond*) ((void *) 0x7ffe7c006b60))-> functype () $52 = Item_func::LIKE_FUNC

A2 like'% 7' condition:

(gdb) p ((Item_func_like *) ((void *) 0x7ffe7c006b60))-> args [1] $54 = (Item *) 0x7ffe7c006aa8 (gdb) p ((Item_string *) $54)-> str_value$55 = {m_ptr = 0x7ffe7c006aa0 "% 7", m_length = 2, m_charset = 0x2e3bcc0, m_alloced_length = 0 M_is_alloced = false} (this is the value% 7) (gdb *) p ((Item_func_like *) ((void *) 0x7ffe7c006b60))-> args [0] $57 = (Item_field *) 0x7ffe7c007830 (gdb) p ((Item_field *) $56)-> field_name$58 = 0x7ffe7c0069e0 "a2" (this is field a2)

In this way, we can also look at the specific conditions of the push down. let's take a look at it.

3. Push down under ICP condition

In fact, after the whole push is completed, as shown in the following figure, the condition (a2 like'% 7') will also be pushed down:

The following is the proof of the push-down, which can be ignored:

When the condition is pushed, the ha_innobase::idx_cond_push function is called to push down. Let's take a look at what conditions are pushed down to the Innodb layer in the above statement. We still use the above debug method to do this, and the breakpoint can be set on ha_innobase::idx_cond_push. The specific viewing methods are as follows:

(gdb) p ((Item_cond *) pushed_idx_cond)-> functype () $67 = Item_func::LIKE_FUNC (this is a like operation) (gdb) p ((Item_func_like *) ((void *) $66))-> args [1] $68 = (Item *) 0x7ffe7c006aa8 (gdb) p ((Item_string *) $68)-> str_value$69 = {m_ptr = 0x7ffe7c006aa0 "% 7", m_length = 2, m_charset = 0x2e3bcc0, m_alloced_length = 0 M_is_alloced = false} (this is the string'% 7') (gdb) p ((Item_func_like *) ((void *) $66))-> args [0] $70 = (Item *) 0x7ffe7c007830 (gdb) p ((Item_field *) $70)-> field_name$71 = 0x7ffe7c99618f "a2" (this is field a2) IV. Use of push under ICP condition

As shown in the figure above, once the condition (a2 like'% 7') has been pushed, the Innodb layer can use it. The process is roughly as follows:

Innodb scans a piece of data (Innodb layer): in this case, a row of data is obtained based on the condition "A1: 6".

Add row locks to index records (Innodb layer): for example, for update statements require row locks.

Filter the data according to the push-down condition (Innodb layer): in this case, the data is filtered according to the condition "a2 like'% 7'".

Perform the table return operation and add row locks (Innodb layer) to the primary key records after returning to the table: for example, the for update statement needs to lock the primary key after returning to the table.

Return the data to the MySQL layer and filter it (MySQL layer): here, other conditions are filtered by where. If the conditions are not met, the row of records will be unlocked in advance (RR,RC is OK). Refer to frame 1 at the end of the stack.

This is the process of using ICP, and we can find that for this process, the advantages we initially summarized are reflected, and it does have the opportunity to improve query efficiency.

5. Other push-down opportunities

In fact, the push-down operation is not always as described in the example, but let's sum it up.

The following is the calling part of the source code, which can be ignored:

This process will be done through row_search_mvcc- > row_search_idx_cond_check- > innobase_index_cond. If we do not enable ICP or do not have the condition for ICP push down, then we will not make a judgment directly, that is, the row_search_idx_cond_check function will return directly.

It is also relatively simple for the function innobase_index_cond, but it will first make a judgment on the end position of the type=range mode as follows:

Other

Stack frame 1:

# 0lock _ rec_unlock (trx=0x7fffd7803b10, block=0x7fff44598370, rec=0x7fff44fd40fc "\ 200", lock_mode=LOCK_X) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:4365#1 0x0000000001bbb70a in row_unlock_for_mysql (prebuilt=0x7ffe70af2ec0 Has_latches_on_recs=0) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/row/row0mysql.cc:3278#2 0x0000000001a52954 in ha_innobase::unlock_row (this=0x7ffe70af20f0) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9237#3 0x00000000014e3379 in rr_unlock_row (tab=0x7ffe70b02460) at / mysqldata/percona-server-locks-detail-5.7.22/sql/records.cc : 821004 0x0000000001582226 in evaluate_join_record (join=0x7ffe70afe778 Qep_tab=0x7ffe70b02460) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1705#5 0x0000000001581372 in sub_select (join=0x7ffe70afe778, qep_tab=0x7ffe70b02460, end_of_records=false) read the above content Have you mastered what are the features of ICP in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow 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.