Shulou(Shulou.com)06/03 Report--

This article mainly introduces the example analysis of back table and index coverage in MySQL, which is very detailed and has certain reference value. Interested friends must read it!

Index type

Clustered index: the leaf node stores row records, and each table must have at least one clustered index. Using clustered index queries is fast because you can locate row records directly

General index: secondary index, index other than clustered index, that is, non-clustered index. The normal index leaf node stores the value of the primary key (clustered index).

Clustering index recursive rules:

If the table has a primary key set, the primary key is a clustered index

If the table does not have a primary key, the first NOT NULL is defaulted and the unique (UNIQUE) column is used as a clustered index

If there is none of the above, a hidden row_id will be created by default as a clustered index

Index structure

Id is the primary key, so it is a clustered index, and its leaf node stores the data of the corresponding row records.

Non-clustered index (Non-ClusteredIndex)

Clustered index query

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

Non-clustered index query

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 through the normal index age=30

two。 Then locate the row record data through the clustered index id=1

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

Use id,age,name query:

Select id,age,name, salary 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 Using where representation of the Extra column is queried back to the table.

Type: all, indicating full table scan

Union index of added tables: CREATE INDEX idx_user_name_age_salary ON mydb.user (name, age, salary)

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.

Paging query (non-utilizing index):

After adding the index, you can use the index to find quickly.

The above is all the contents of the article "sample Analysis of Table and Index overrides in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.