MySQL (ICP) index push-down comparison with Oracle

Source: Internet
Author: User

MySQL (ICP) index push-down comparison with Oracle

The first time I saw this term, I thought of it together with Oracle FPD-filter push-down, but later I found that they were just the same thing, when an index contains all the access fields, you can filter out the conditions based on the leading column and filter out other conditions. For example
Create table testicp (a int, B INT, C NAME );
Alter table testticp add key (A, B );


SELECT * from testicp where a = 1 and B <10
If no ICP is used, the result set is returned through the = 1 condition, and then the B <10 condition is filtered out after the table is returned, in this case, the additional result set of B <10 is not allowed to pass back to the table, which increases the discrete read pressure. If you know ORACLE, you must remember the concept of CLUSTER_FACTOR, it is used to describe the degree of order of the index relative to the data in the table. The maximum value is the number of rows in the table, and the minimum value is the number of blocks in the table. The smaller the value, the more similar the index and table data, that is, this column in the table is relatively ordered. If the larger the value is, the more time-consuming the operation is to return to the table (the more severe the discrete read ), although not familiar with MYSQL, this will definitely be affected.

Therefore, it is necessary to filter out unnecessary data as soon as possible. This may not be a problem in ORACLE, but MYSQL only introduced ICP when it knew 5.6.

Let's take a look at the ORACLE execution plan.
Script:

Create table testicp (a int, B INT, C varchar2 (20 ));
Declare
I number (10 );
Begin
For I in 1000
Loop
Insert into TESTICP
Values (I, I, 'gaopeng ');
End loop;
End;
SELECT * from testicp where a = 1 and B <10;


--------------------------------------------------------------------------------
Plan hash value: 446810821
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU
--------------------------------------------------------------------------------
| 0 | select statement | 1 | 38 | 3 (0
| 1 | table access by index rowid | TESTICP | 1 | 38 | 3 (0
| * 2 | index range scan | TESTICP_INDEX | 1 | 2 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("A" = 1 AND "B" <10)


When adding A ticket, we only need to see access ("A" = 1 AND "B" = 1) to know that the index is accessed through "A" = 1 AND "B" = 1.
If it is filter B = 1, we can understand it as filtering after accessing the index.
SQL> explain plan for select * from testicp where a = 1 and c = 'gtest ';
Explained


SQL> select * from table (dbms_xplan.display );
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 446810821
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU
--------------------------------------------------------------------------------
| 0 | select statement | 1 | 38 | 3 (0
| * 1 | table access by index rowid | TESTICP | 1 | 38 | 3 (0
| * 2 | index range scan | TESTICP_INDEX | 1 | 2 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("C" = 'gtest ')
2-access ("A" = 1)
Note
-----
-Dynamic sampling used for this statement (level = 2)
19 rows selected


If we change to and c = 'gtest'
You can see filter ("C" = 'gtest'), which is called filter. It is filtered after the index is returned to the table.

However, all these things that ORACLE takes for granted are implemented only when MYSQL reaches MYSQL 5.6. We use MYSQL to execute the following script:

Create table testicp (a int, B INT, C varchar (20 ));
Delimiter //
Create procedure myproc3 ()
Begin
Declare num int;
Set num = 1;
While num <= 1000 do
Insert into testicp values (num, num, 'gaopeng ');
Set num = num + 1;
End while;
End //
Call myproc3 ()//
Delimiter;
Alter table testicp add key (a, B );

Explain select * from testicp where a = 1 and B <10;
Mysql> explain select * from testicp where a = 1 and B <10;
+ ---- + ------------- + --------- + ------- + --------------- + ------ + --------- + ------ + ----------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + --------- + ------- + --------------- + ------ + --------- + ------ + ----------------------- +
| 1 | SIMPLE | testicp | range | A | 10 | NULL | 1 | Using index condition |
+ ---- + ------------- + --------- + ------- + --------------- + ------ + --------- + ------ + ----------------------- +

The keyword Using index condition is used to describe this parameter.
Optimizer_switch = 'index _ condition_pushdown = On'
If optimizer_switch = 'index _ condition_pushdown = off' is set, let's take a look.

Set optimizer_switch = 'index _ condition_pushdown = off'
Mysql> explain select * from testicp where a = 1 and B <10;
+ ---- + ------------- + --------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + --------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
| 1 | SIMPLE | testicp | range | A | 10 | NULL | 1 | Using where |
+ ---- + ------------- + --------- + ------- + --------------- + ------ + --------- + ------ + ------------- +
1 row in set (0.01 sec)

We can see that the Using where is used, which means that no icp is used.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.