当前位置:网站首页>[let's go to Dachang series] what is return table query? How to optimize back to table query?

[let's go to Dachang series] what is return table query? How to optimize back to table query?

2021-10-14 06:23:34 Old fellow iron has dried up the code.

Before mentioning what is a back table query , I have to explain first InnoDB The index of .

InnoDB The index of

InnoDB There are two main categories of indexes , One is clustered index (Clustered Index), One is the general index (Secondary Index)

InnoDB What's the difference between a clustered index and a normal index ? What's the difference ?

InnoDB Clustered index of

Each row of data exists InnoDB On the leaf node of the clustered index , therefore InnoDB There must be and only one clustered index , The following is the generation rule of clustered index :

  • If the table defines PK(Primary Key, Primary key ), that PK It's a clustered index .
  • If the table has no definition PK, Is the first NOT NULL UNIQUE The column of the index is the clustered index .
  • otherwise InnoDB Will create another hidden ROWID As a clustered index .

This mechanism makes it possible to PK The query speed is very fast , Because directly located row records .

InnoDB General index

InnoDB General index of Leaf nodes store primary key values . Want to get row data , You have to scan the index tree in the clustered index .
Be careful , Not store row record header pointer ,MyISAM The index leaf node stores the record pointer .


What is return table query

Here is an example to explain :
Suppose there is a table user(id PK,name,code),id Is clustered index ,code It's a general index . There are several pieces of data in the table .

id name code -----
1 Xiao Ming AQ -----
4 Xiao Chen DR -----
7 Xiaohong CY -----
9 liu FP -----
So the two indexes B+ The tree index is as shown in the figure below :
 Insert picture description here

MySQL Back table query and index coverage in : A million level paging query uses Limit from 90 Seconds to 0.6 Millisecond optimization _ The first 1 A picture
Row records cannot be directly located from a normal index , What is the query process of ordinary index ?

example : select * from user where code = ‘CY’;

The query process usually needs to scan the index tree twice , The execution here is like this :

 Insert picture description here

Such as colored path :

  • In the first pass, locate the primary key value through the ordinary index
  • And then the second time through the clustered index to locate the specific row record .

That's what's called Return to the table for query , That is, first locate the primary key value , Then locate the row record according to the primary key value , The performance is lower than scanning the clustered index tree only once .


How to optimize back to table query

How to solve the problem of low performance ? This involves a concept --------- Overlay index

Overlay index

Overlay index is an optimization strategy to avoid back to table query . Namely Put all the fields to be queried into the general index , In this way, the leaf nodes found in the ordinary index ( That is, the black box in the figure above ) You can already get all the required fields in , No more queries in the clustered index .

How to implement overlay index

It can be divided into two kinds :

The first method is to reduce the query fields and only query the fields in miniature , For example, in a query , We only check id Field ( That is, the primary key field ), In this way, millions of data will not be queried back to the table , There are only 50 The data is queried in the clustered index . As above user surface Optimize sql For not querying name Field .

example : select id,code from user where code = ‘CY’;

The second way is to modify the index created by the table , Add the fields to query , Like above user surface , hold name Also added to the index , Set up (name,code) Union index of two fields .

Definition and precautions of overlay index

If an index covers ( contain ) All the values of the fields that need to be queried , This index is the overlay index . Because the index already contains the value of the field to be queried , Therefore, when querying, you can directly return the field values in the index , You don't need to go to the table again , Avoid the second query of primary key index , It also improves the efficiency of the query .

It should be noted that , Not all types of indexes can be overridden . Because the overlay index must store the column value of the index , And hash index 、 Spatial index and full-text index do not store index column values , Indexes MySQL Only use B-Tree Index to do overlay index .

in addition , When a query is initiated that is overwritten by an index ( Index overlay query ) when , stay explain( Implementation plan ) Of Extra Column can see 【Using Index】 Information about .

Advantages of overriding indexes

1. Index entries are usually much smaller than the size of data rows , Because overwriting the index only needs to read the index , Greatly reduce the amount of data access .

2. Indexes are stored in the order of column values , about IO Intensive range lookups are more efficient than random reading of each row of data from disk IO Many small .

3. Some storage engines, such as MyISAM Only index is cached in memory , Data is cached by operating systems , Therefore, to access the data, you need a system call , Using overlay indexes avoids this .

4. because InnoDB Clustering index of , Overlay index pair InnoDB Database tables under the engine are particularly useful . because InnoDB The secondary index keeps the primary key value of the row in the leaf node , If the secondary index can overwrite the query , This avoids the secondary query of the primary key index .

版权声明
本文为[Old fellow iron has dried up the code.]所创,转载请带上原文链接,感谢
https://chowdera.com/2021/10/20211002145845306u.html

随机推荐