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

What this article shares to you is about how to carry on the simple test under the condition of MySQL index, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Since MySQL 5.6, there have been some improvements in indexing, such as index conditional push (Index condition pushdown,ICP), which is strictly an optimizer-level improvement.

To put it simply, the optimizer will push index condition processing down from the Server layer to the storage engine layer as much as possible. For example, there is a table that contains a composite index idx_cols that contains (C1 ~ c2, … , cn) n columns, if there is a where condition for range scanning on C1, then the remaining c2,... The index on cn cannot be used to extract and filter data, and ICP optimizes this.

Let's briefly test it in the environment of MySQL 5.6.

Let's create the table emp, which contains a primary key and a composite index to illustrate.

Create table emp (

Empno smallint (5) unsigned not null auto_increment

Ename varchar (30) not null

Deptno smallint (5) unsigned not null

Job varchar (30) not null

Primary key (empno)

Key idx_emp_info (deptno,ename)

) engine=InnoDB charset=utf8

Of course, I also randomly inserted a few pieces of data, meaning.

Insert into emp values (1), (2), (2), (2), (2), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3)

The control of ICP is uniformly managed by an optimizer parameter optimizer_switch in the database parameters, and I think this is the time when the MySQL optimizer is closest to us. You can view it in the following way.

Show variables like 'optimizer_switch'

Of course, you didn't see the word index condition pushdown in versions prior to 5.6. The results seen in version 5.6 are as follows:

# mysqladmin var | grep optimizer_switch 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,subquery_materialization_cost_based=on,use_index_extensions=on Let's compare it with two statements and compare it with the execution plan.

Set optimizer_switch = "index_condition_pushdown=off"

> explain select * from emp where deptno between 1 and 100 and ename = 'jeanron100'

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | emp | ALL | idx_emp_info | NULL | NULL | NULL | 4 | Using where |

+-- +

If it is enabled, see if ICP is enabled.

Set optimizer_switch = "index_condition_pushdown=on"; > explain select * from emp where deptno between 10 and 3000 and ename = 'jeanron100'

+-+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | emp | range | idx_emp_info | idx_emp_info | 94 | NULL | 1 | Using index condition |

+-+-

1 row in set (0.00 sec) if you observe carefully, you will find that the two statements are still different, that is, the scope of the range scan is different, if you still use the original statement, the result is still limited.

> explain select * from emp where deptno between 1 and 300 and ename = 'jeanron100'

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | emp | ALL | idx_emp_info | NULL | NULL | NULL | 4 | Using where |

+-- +

1 row in set (0. 00 sec) this place is worth deliberating.

The above is how to carry out the simple test under the condition of MySQL index. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.