Since MySQL 5.6, there have been some improvements in the index, such as the index condition pushdown (ICP), strictly speaking, the optimizer level improvement.
If it is simple to understand, that is, the optimizer will try to index the treatment from the Server layer to the storage engine layer. As an example, there is a table containing the combined index idx_cols containing (c1, c2, …, cn) n columns, if there is a range scan on the where1 condition, then the remaining c2, …, cn this n-1 On the index can not be used to extract and filter data, and ICP is to optimize this thing.
We are in the MySQL 5.6 environment to a simple test.
We create the table emp, which contains a primary key, a combined index to illustrate.
Create Table EMP (
EMPNO smallint The (. 5) unsigned Not null AUTO_INCREMENT,
ename VARCHAR (30) Not null,
DEPTNO smallint The (. 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 data, meaning that
Insert the emp values ??(1, ‘zhangsan’, 1, ‘CEO’), (2, ‘lisi’, 2, ‘CFO’), (3, ‘wangwu’, 3, ‘CTO’), (4, Jeanron100 ‘, 3,’ Enginer ‘);
ICP control in the database parameters have an optimizer parameter optimizer_switch to unified management, I think this is the MySQL optimizer from our closest time. You can use the following way to view.
Show variables like ‘optimizer_switch’;
Of course, in the previous version of 5.6, you can not see the index condition pushdown this kind of words. The results in the 5.6 version are as follows:
In the same way as in the case of the following event: index_merge_switch = 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_ed, use_index_extensions = on Here we use two statements to compare the description, through the implementation of the plan to contrast.
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 |
+ —- + ————- + ——- + —— + ———– —- + —— + ——— + —— + —— + ————- +
And if it is turned on, 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’;
> 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 considering.
Recent Comments