Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the coverage index and return table of MySQL, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Two categories of indexes

Storage engine used: MySQL5.7 InnoDB

Clustering index

* if the table has a primary key, the primary key is a clustered index * if the table does not have a primary key, it defaults to the first NOT NULL, and the unique (UNIQUE) column is used as a clustered index * none of the above will create a hidden row_id as a clustered index by default

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

Thus, using a clustered index query is fast because you can locate the row record directly.

General index

A common index is also called a secondary index, an index other than a clustered index, that is, a non-clustered index.

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

Example

Build 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)

Fill data

Insert into user (name,age) values ('Zhang San', 30); insert into user (name,age) values ('Li Si', 20); insert into user (name,age) values ('Wang Wu', 40); insert into user (name,age) values ('Liu Ba', 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 node stores the data of the corresponding row records.

Clustered index (ClusteredIndex)

Age is a general index (secondary index), a non-clustered index, and its leaf nodes store the values of the clustered index.

General Index (secondaryIndex)

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

For example, select * from user where id = 1

Clustered index search process

If the query condition is a normal index (non-clustered index), the B+ tree needs to be scanned twice, the first scan locates the value of the clustered index through the ordinary 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 navigate to the primary key value id=1 2. 0 through the normal index age=30. Then locate the row record data through the clustered index id=1

The first step in the general index search process

The second step of the general index search process

Query back to the table

First, the cluster index value is located by the value of the ordinary index, and then the row record data is located by the value of the cluster index. The index B + tree needs to be scanned twice, and its performance is lower than that of scanning the index tree.

Index overlay

You only need to get all the column data needed by SQL on an index tree, no need to return to the table, and it is faster.

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

How to implement overlay index

The common method is to build the queried field into the federated 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, so the overlay index is realized.

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

Explain analysis: age is a normal index, but the name column is not on the index tree, so after querying the values of id and age through the age index, you need to go back to the table and query the value of name. At this time, the NULL representation of the Extra column is queried back to the table.

In order to achieve index coverage, it is necessary to build 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 combined index idx_age_name, and the values of the queried fields id, age and name are all on the index tree. You only need to scan the combined index B+ tree once. This is to achieve index coverage. In this case, the Extra field indicates that index coverage is used for Using index.

Which scenarios are appropriate to use index overrides to optimize SQL

Full table count query optimization

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

Use index override optimization: create age field indexes

Create index idx_age on user (age)

Column query back to table optimization

The example described earlier in the use of index overrides is

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

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

Paging query

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

Because the name field is not an index, the paging query needs to be queried back to the table. At this time, Extra sorts the Using filesort file, and the query performance is poor.

Use index override: build a composite index idx_age_name (age,name)

After reading the above, do you have any further understanding of the overlay index and return table of MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.