How to use MySQL covering index and table return

Two major categories of indexes

Storage engine used: MySQL 5.7 InnoDB

Clustered index

• * If the table has a primary key, the primary key is the clustered index
• * If the table does not have a primary key, the first NOT NULL and unique (UNIQUE) column will be used as the clustered index by default
• * If none of the above is specified, a hidden row_id will be created as a clustered index by default

The leaf nodes of InnoDB's clustered index store row records (actually a page structure, a page contains multiple rows of data). InnoDB must have at least one clustered index.

It can be seen that using a clustered index query will be very fast because the row record can be directly located.

Normal index

Ordinary indexes are also called secondary indexes, which are indexes other than clustered indexes, that is, non-clustered indexes.

InnoDB's ordinary index leaf nodes store the value of the primary key (clustered index), while MyISAM's ordinary index stores record pointers.

Example

Create a table

mysql> create table user(
  -> id int(10) auto_increment,
  -> name varchar(30),
  -> age tinyint(4),
  -> primary key (id),
  -> index idx_age (age)
  ->)engine=innodb charset=utf8mb4;

The id field is a clustered index, and the age field is a normal index (secondary index)

Filling Data

insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);

mysql> select * from user;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | Zhang San | 30 |
| 2 | Li Si | 20 |
| 3 | Wang Wu | 40 |
| 4 | Liu Ba | 10 |
+----+--------+------+

Index storage structure

id is the primary key, so it is a clustered index, and its leaf nodes store the data of the corresponding row records.

ClusteredIndex

age is a common index (secondary index), a non-clustered index, and its leaf nodes store the values ​​of the clustered index.

Normal index (secondaryIndex)

If the query condition is the primary key (clustered index), you only need to scan the B+ tree once to locate the row record data to be found through the clustered index.

For example: select * from user where id = 1;

Clustered Index Lookup Process

If the query condition is a common index (non-clustered index), the B+ tree needs to be scanned twice. The first scan locates the value of the clustered index through the common index, and then the second scan locates the row record data to be found through the value of the clustered index.
For example: select * from user where age = 30;

1. First locate the primary key value id=1 through the common index age=30

2. Then locate the row record data through the clustered index id=1

The first step of the normal index search process

The second step of the normal index search process

Back to table query

First, locate the clustered index value through the value of the ordinary index, and then locate the row record data through the value of the clustered index. It is necessary to scan the index B+ tree twice, and its performance is lower than scanning the index tree once.

Index Covering

All column data required by SQL can be obtained on one index tree without returning to the table, which is faster.

For example: select id,age from user where age = 10;

How to implement covering index

A common method is to build the queried fields into a joint index.

1. For example: select id,age from user where age = 10;

Explain analysis: Because age is a common index, the age index is used, and the corresponding results can be queried by scanning the B+ tree once, thus achieving a covering index.

2. Implementation: select id,age,name from user where age = 10;

Explain analysis: age is a common index, but the name column is not in the index tree. Therefore, after querying the values ​​of id and age through the age index, it is necessary to go back to the table and query the value of name. The NULL value in the Extra column indicates that a table query has been performed.

In order to achieve index coverage, it is necessary to create a composite index idx_age_name(age,name)

drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);

Explain analysis: At this time, the fields age and name are the composite index idx_age_name. The values ​​of the queried fields id, age, and name are just in the index tree. It is only necessary to scan the composite index B+ tree once. This is how index coverage is achieved. At this time, the Extra field is Using index, which means that index coverage is used.

Which scenarios are suitable for using index covering to optimize SQL?

Optimize count query of the whole table

mysql> create table user(
  -> id int(10) auto_increment,
  -> name varchar(30),
  -> age tinyint(4),
  -> primary key (id),
  ->)engine=innodb charset=utf8mb4;

For example: select count(age) from user;

Using index coverage optimization: Creating an index on the age field

create index idx_age on user(age);

Column query back table optimization

The example used in the previous article to describe the use of index coverage is

For example: select id,age,name from user where age = 10;

Use index coverage: create a composite index idx_age_name(age,name)

Pagination Query

For example: select id,age,name from user order by age limit 100,2;

Because the name field is not an index, a back-table query is required for paging queries. In this case, Extra is Using filesort, and the query performance is poor.

Use index coverage: create a composite index idx_age_name(age,name)

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.