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.<\/p>\n
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.\u00a0As 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.<\/p>\n
We are in the MySQL 5.6 environment to a simple test.<\/p>\n
We create the table emp, which contains a primary key, a combined index to illustrate.<\/p>\n
Create Table EMP (
\nEMPNO smallint The (. 5) unsigned Not null AUTO_INCREMENT,
\nename VARCHAR (30) Not null,
\nDEPTNO smallint The (. 5) unsigned Not null,
\nJob VARCHAR (30) Not null,
\nPrimary Key (EMPNO),
\nKey idx_emp_info (DEPTNO, Ename)
\n) engine = InnoDB charset = utf8;<\/p>\n
Of course, I also randomly inserted a few data, meaning that<\/p>\n
Insert the emp values ??(1, ‘zhangsan’, 1, ‘CEO’), (2, ‘lisi’, 2, ‘CFO’), (3, ‘wangwu’, 3, ‘CTO’), (4, Jeanron100 ‘, 3,’ Enginer ‘);<\/p>\n
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.\u00a0You can use the following way to view.<\/p>\n
Show variables like ‘optimizer_switch’;<\/p>\n
Of course, in the previous version of 5.6, you can not see the index condition pushdown this kind of words.\u00a0The results in the 5.6 version are as follows:<\/p>\n
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.<\/p>\n
Set optimizer_switch = “index_condition_pushdown = off”<\/p>\n
> Explain select * from emp where deptno between 1 and 100 and ename = ‘jeanron100’;
\n+ —- + ————- + ——- + — — + ————— + —— + ——— + —— + —— +
\n| __-\u00a0+\u00a0| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
\n+ —- + ——– —– + ——- + —— + ————— + —— + —— —
\n|\u00a0+ + + + +\u00a0_—\u00a0+\u00a0| 1 | SIMPLE | emp | ALL | idx_emp_info | NULL | NULL | NULL | 4 |\u00a0Using where\u00a0|
\n+ —- + ————- + ——- + —— + ———– —- + —— + ——— + —— + —— + ————- +<\/p>\n
And if it is turned on, see if ICP is enabled.
\nSet optimizer_switch = “index_condition_pushdown = on”;> explain select * from emp where deptno between 10 and 3000 and ename = ‘jeanron100’;<\/p>\n
> Explain select * from emp where deptno between 1 and 300 and ename = ‘jeanron100’;
\n+ —- + ————- + ——- + — — + ————— + —— + ——— + —— + —— +
\n| __-\u00a0+\u00a0| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
\n+ —- + ——– —– + ——- + —— + ————— + —— + —— —
\n|\u00a0+ + + + +\u00a0_—\u00a0+\u00a0| 1 | SIMPLE | emp | ALL | idx_emp_info | NULL | NULL | NULL | 4 |\u00a0Using where\u00a0|
\n+ —- + ————- + ——- + —— + ———– —- + —— + ——— + —— + —— + ————- +
\n1 row in set (0.00 sec) This place is worth considering.<\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6867"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6867"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6867\/revisions"}],"predecessor-version":[{"id":6868,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6867\/revisions\/6868"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6867"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6867"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6867"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}