Index coverage, index pushdown, index diving, index merging for MySQL query performance optimization

Index coverage

What is index coverage

The data columns of select can only be obtained from the index without reading the data rows, in other words, the query columns must be covered by the built index.

How to achieve index coverage?

The most common method is: build the field to be queried into a joint index (if there is only one field, a normal index is also acceptable).

For example, create the following table:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `surname` varchar(2) NOT NULL DEFAULT '' COMMENT 'surname',
  `name` varchar(10) NOT NULL DEFAULT '' COMMENT 'name',
  `full_name` varchar(12) GENERATED ALWAYS AS (concat(`surname`,`name`)) STORED COMMENT 'full name',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  `sex` varchar(2) DEFAULT NULL COMMENT 'sex',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age_sex` (`age`,`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=1144780 DEFAULT CHARSET=utf8mb4


When querying all fields, you need to use the index, but you also need to go back to the table to get all the row data.

When the query only has age and sex, the Extra of explain is Using index, indicating that index coverage is used.

Index pushdown

What are all pushdowns?

Index Condition Pushdown (ICP for short) is a new feature of MySQL 5.6, which can reduce the number of queries back to the table and improve query efficiency.

What are the functions of index pushdown?

There are two main functions:

  1. Reduce the number of queries back to the table
  2. Reduce the amount of data transfer between the storage engine and the MySQL Server layer

Index pushdown configuration

View the configuration of index pushdown:

show variables like '%optimizer_switch%';

If index_condition_pushdown=on is displayed in the output result, it means that index pushdown is enabled.

Enable index pushdown

Enable index pushdown:

set optimizer_switch="index_condition_pushdown=on";

Turn off index pushdown

Turn off index pushdown:

set optimizer_switch="index_condition_pushdown=off";

Principle of index pushdown

Create a combat data table:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `surname` varchar(2) NOT NULL DEFAULT '' COMMENT 'surname',
  `name` varchar(10) NOT NULL DEFAULT '' COMMENT 'name',
  `full_name` varchar(12) GENERATED ALWAYS AS (concat(`surname`,`name`)) STORED COMMENT 'full name',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  `sex` varchar(2) DEFAULT NULL COMMENT 'sex',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age_sex` (`age`,`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4

Create a joint index on the age and sex columns.

In the case where index pushdown is not used, the query process is as follows:

  1. The storage engine finds the primary key ID of the eligible row according to the age index field in the where condition
  2. Then query back to the table twice, and find the entire row of records on the primary key index according to the primary key ID
  3. Return the data to the MySQL Server layer, and then filter out a row of records that meet the requirements according to the sex condition in where
  4. return to client

In the case of using index pushdown, the query process is as follows:

  1. The storage engine finds the qualified row records according to the age index field in the where condition, and then uses the sex condition to filter out the qualified primary key ID
  2. Then query back to the table twice, and find the entire row of records on the primary key index according to the primary key ID
  3. Return the data to the MySQL Server layer
  4. return to client

    The Extra column in the execution plan shows Using index condition, indicating that the optimization logic of index pushdown is used.

Application scope of index pushdown

  1. Queries for InnoDB engine and MyISAM engine
  2. Applicable to range queries whose execution plan is range, ref, eq_ref, ref_or_null
  3. For InnoDB tables, only for nonclustered indexes. The goal of index pushdown is to reduce the number of full row reads, thereby reducing I/O operations. For InnoDB clustered indexes, complete records are already read into InnoDB buffers. Using index pushdown in this case does not reduce I/O.
  4. Subqueries cannot use index pushdown
  5. Stored procedures cannot use index pushdown

Index Dive

What is an index dive?

Let’s look at the actual operation first.
Create table:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `surname` varchar(2) NOT NULL DEFAULT '' COMMENT 'surname',
  `name` varchar(10) NOT NULL DEFAULT '' COMMENT 'name',
  `full_name` varchar(12) GENERATED ALWAYS AS (concat(`surname`,`name`)) STORED COMMENT 'full name',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  `sex` varchar(2) DEFAULT NULL COMMENT 'sex',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age_sex` (`age`,`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=151 DEFAULT CHARSET=utf8mb4

Write 128 lines of data into it:

select count(id) from user;


Execute the in operation:


It is also an in query, when the number of parameters is different, the rows and types are different.

The number of in parameters determines the way of index diving or index statistics.

What does index diving do?

Is it not good to use the index dive method to estimate the number of scanned rows?

In fact, this is a cost-based consideration. The estimated cost of index diving is relatively high, which is suitable for small data volume. Index statistics are less costly to estimate and are suitable for large amounts of data.

In general, there are not too many parameters in the in condition of our where statement, which is suitable for using index diving to estimate the number of scanned rows.

It is recommended that students who are still using versions prior to MySQL 5.7.3 manually modify the configuration parameters of index diving and change them to appropriate values.

Index dive configuration

Query configuration:

show variables like '%eq_range_index_dive_limit%';


The function of eq_range_index_dive_limit configuration is:
When the number of parameters in the in condition of the where statement is less than this value, MySQL uses the index dive method to estimate the number of scanned rows, which is very accurate.

When the number of parameters in the in condition of the where statement is greater than or equal to this value, MySQL uses another method Index statistics (Index statistics) to estimate the number of scanned rows, with large errors.

Change setting:

set eq_range_index_dive_limit=200;

Index merge

What is index merge?

When there are multiple indexes in a single table, and the where of an SQL statement contains multiple index fields, each index may return a result set when executing the SQL statement, and MySQL will calculate the intersection or union, or the intersection and sum A combination of unions. That is to say, multiple indexes can be used in one query.

We create a table:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `surname` varchar(2) NOT NULL DEFAULT '' COMMENT 'surname',
  `name` varchar(10) NOT NULL DEFAULT '' COMMENT 'name',
  `full_name` varchar(12) GENERATED ALWAYS AS (concat(`surname`,`name`)) STORED COMMENT 'full name',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  `sex` varchar(2) DEFAULT NULL COMMENT 'sex',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age_sex` (`age`,`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=151 DEFAULT CHARSET=utf8mb4

Create a joint index on the age and sex columns, and an index on the name.

When executing a query:

When using explain, index_merge will be displayed in the column of type, and the column of key is all used indexes.

Index merging contains three algorithms, which are shown in explain:

  1. using intersect
    index merge intersection access algorithm (index merge intersection access algorithm).
    Query for each index used, query the set of primary key values, and then merge to find the intersection, that is, the AND operation.

  2. using union
    index merge union access algorithm (index merge union access algorithm)
    It is easy to see that it is similar to the above algorithm, but it uses the or connection condition to find the union.
    The execution process is similar to that of index merge intersect. It still queries the ordered set of primary keys and then performs a union.

  3. using sort_union
    index merge sort sort-union access algorithm (index merge sort union access algorithm)
    Get the primary key set according to the index query, sort each primary key set, and then find the union.

What is the function of index merge?

Index merging can use multiple indexes to improve query speed.