1. Introduction
What does index push-down do? How much of a performance boost to query statements? Before we get to the bottom of these questions, let’s take a look at the official documentation.
Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.
Index push-down is an optimized technique used by MySQL to retrieve rows from tables using indexes. Without index push-down, the storage engine retrieves rows from the table based on the index and returns the results to MySQL’s Server layer, which then filters the returned results based on the Where condition. With index push-down, the storage engine first retrieves the result based on the index, and then filters the result based on the unindexable columns in the index before retrieving the row record. Index push-down reduces the number of accesses to the base table and the number of accesses to the storage engine by the Server layer.
Even with an explanation, it is difficult to understand. Select * from table T where id, a, B, and C are null; select * from table T where id, A, B, and C are null; select * from table T where id, B, and C are null; Select * from t where a = ‘XXX’ and b like ‘%xx%’ and c = ‘XXX’; select * from t where a = ‘XXX’ and B like ‘%xx%’ and c = ‘XXX’;
If you do not use the index push-down technique, the index that does not meet b like ‘%xx%’ and C =” XXX “will also be queried, and the corresponding row will be retrieved back to the table, and the retrieval result will be returned to the Server layer. B like ‘%xx%’ and c=’ XXX ‘
If index push-down is used, the storage engine will filter out index records that satisfy b like ‘%xx%’ and C =” XXX “, so that the Server layer does not need to filter the results based on B like ‘%xx%’ and C =” XXX “.
To paraphrase a great man, practice is the sole criterion for testing truth.
2. The actual combat
2.1 Importing Data
The actual combat data can be downloaded from test_DB, and then imported into the database. After the import, you can see the following table in the database
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
Copy the code2.2 Creating An Index
Create a federated index idx_first_name_LAST_name_birthday on the first_name, last_name, and birth_date columns in the employees table
2.3 Viewing the Execution plan
2.3.1 Enabling index push-down
Index push-down is enabled by default. Take a look at the following query execution plan
Using index condition is given in the Extra column of the execution plan, indicating that the index push-down function is used in the query.
The current query can only use the first_NAME field in the union index. Before retrieving row records from the table, the index push-down technology filters out unqualified index records based on last_name like ‘%ch%’ and birthd_date > ‘1952-05-15’.
Add another query condition to the WHERE condition to see the effect
The Extra column of the execution plan shows the Using index condition, indicating that the query uses the index push-down function. Indicates that the Server layer filters the results returned by the storage engine using the condition hire_date > ‘1986-01-01’. From this, we can know that the index push-down technique filters the fields that cannot be used in the joint index and cannot be used in the non-joint index.
2.3.2 Disabling index push-down
Set optimizer_switch = ‘index_condition_pushdown=off’; Command to disable index push-down, and then look at the query execution plan
Using WHERE is shown in the Extra column of the execution plan. It can be seen that filtering takes place in the Server layer, not in the storage engine layer, when index push-down is disabled
2.4 key_len field
The key_len field in the query execution plan indicates the length of the index to be used. The result given in 2.3 is that the query only uses the first_name field in the union index, which can be calculated. Before we verify, let’s take a look at the table definition:
mysql> show create table employees\G;
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL.
`birth_date` date NOT NULL.
`first_name` varchar(14) NOT NULL.
`last_name` varchar(16) NOT NULL.
`gender` enum('M'.'F') NOT NULL.
`hire_date` date NOT NULL.
PRIMARY KEY (`emp_no`),
KEY `idx_first_name_last_name_birthday` (`first_name`.`last_name`.`birth_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Copy the codeThree important pieces of information emerge from the table definition:
- The data type defined for each field
- Whether each field can be NULL
- The character encoding corresponding to the table
You should know that different character sets take up different numbers of bytes:
| Character set | Occupied bytes |
|---|---|
| latin1 | 1 |
| ucs2 | 2 |
| Utf8mb3 utf8, | 3 |
| utf8mb4 | 4 |
Given these conditions, we can calculate the length of key_len in the execution plan = 14 + 2 + 0(not null, 1 if allowed) = 16.
2.4 Performance Comparison
Having said that, you probably still don’t know how much index push-down improves query performance, so read on
2.4.1 Enabling Monitoring
set profiling = 1;
Copy the code2.4.2 Executing a Query Statement
select * from employees where first_name = 'Sanjiv' and last_name like '%cha%' and birth_date = '1952-05-15';
Copy the code2.4.3 Disabling the index push-down function
set optimizer_switch='index_condition_pushdown=off';
Copy the code2.4.4 Executing a Query Statement
select * from employees where first_name = 'Sanjiv' and last_name like '%cha%' and birth_date = '1952-05-15';
Copy the code2.4.5 Viewing monitoring Results
You can see that enabling index push-down improves performance twice as much as disabling index push-down
3. Summary
In this article, you will learn what index push-down is and demonstrate the difference in query execution plan with index push-down enabled and disabled, as well as performance with index push-down enabled and disabled. This is the end of the study, looking forward to meeting you next time.