MySQL back to table

MySQL  Back to the table

 

　　　　 Five flower horse , A thousand furs , Hoo'er is going out for a good drink , With you to sell eternal sorrow .

 

One 、 sketch

Back to the table , As the name suggests, it is to go back to the table , That is, first scan the row where the data is located through the general index , And then through the row primary key ID Fetch data not contained in the index . Therefore, the generation of back table also needs certain conditions , If an index query can get all select Records do not need to be returned to the table , If select There are other non indexed columns in the required columns , There will be a back to table action . In other words, the query based on non primary key index needs to scan one more index tree .

Two 、InnoDB Engines have two main types of indexes

To figure it out, go back to the table , First of all, understand InnoDB Two indexes , Clustered index  （clustered index） And general index （secondary index）.

Clustered index （clustered index）

InnoDB The leaf node of the clustered index stores row records , therefore , InnoDB There must be and only one clustered index .

• If the table has a primary key defined , be Primary Key  It's a clustered index ;
• If the table does not have a primary key defined , Then the first non empty unique index （Not NULL Unique） Columns are clustered indexes ;
• otherwise ,InnoDB Will create a hidden row-id As a clustered index ;

General index （secondary index）

A common index is also called a secondary index , All indexes except clustered indexes are ordinary indexes , That is, non clustered index .

InnoDB Common index leaf node It stores the primary key （ Cluster index ） Value , and MyISAM The normal index of is to store the record pointer .

3、 ... and 、 Back to table example

Data preparation

First create a table   t_back_to_table , In the table id It is a primary key index, that is, a cluster index ,drinker_id For general index .

CREATE TABLE t_back_to_table (

id INT PRIMARY KEY,

drinker_id INT NOT NULL,

drinker_name VARCHAR ( 15 ) NOT NULL,

drinker_feature VARCHAR ( 15 ) NOT NULL,

INDEX ( drinker_id )

) ENGINE = INNODB; 

Do the following again SQL sentence , Insert Four test data .

INSERT INTO t_back_to_table ( id, drinker_id, drinker_name, drinker_feature )

VALUES

( 1, 2, ' guangxi - Yulin ', ' Drink until dawn ' ),

( 2, 1, ' guangxi - Rivers and pools ', ' Baijiu three Jin and half beer ' ),

( 3, 3, ' guangxi - Guigang ', ' Drink until night ' ),

( 4, 4, ' guangxi - Liuzhou ', ' Drink and don't eat ' );

NO Back to the table case 

Use primary key index id, Query out id by 3  The data of .

EXPLAIN SELECT * FROM t_back_to_table WHERE id = 3;

perform EXPLAIN SELECT * FROM t_back_to_table WHERE id = 3, This article SQL Statement does not need to return to the table .

the reason being that According to the query method of the primary key , Just search ID This tree B+ Trees , The leaf node on the tree stores row records , According to this unique index ,MySQL You can determine the search record .

Back to the table case 

Use drinker_id This index is used to query drinker_id = 3 The return table will be involved in the record of .

SELECT * FROM t_back_to_table WHERE drinker_id = 3;

because adopt drinker_id This common index query method , You need to search drinker_id Tree index ( The primary key is recorded on the index tree ID Value ), And then we get the primary key ID The value of is 3, Until then ID Index tree search once . Although the index is used in this process , But in fact, the bottom layer makes two index queries , This process is called back to table .

Summary table

• Compared with , Query based on non primary key index needs to scan an index tree , First locate the primary key value , Repositioning row records , Its performance is lower than scanning the index tree .
• Primary key query should be used as much as possible in application , There are only four pieces of data in the table , If there's a lot of data , It can be clearly seen that using the primary key query is more efficient .
• Using a clustered index （ Primary key or first unique index ） You don't go back to your watch , The normal index will return to the table .

Four 、 Index storage structure

InnoDB engine Both the clustered index and the ordinary index are B+Tree  Storage structure , Only leaf nodes store data .

• new B+ The tree structure does not store record data in all nodes , Instead, it is stored only in the lowest leaf node , All non leaf nodes in the upper layer only store index information , This structure allows a single node to store more index values , increase Degree  Value , Increase the chance of hitting the target record .
• This structure will store some redundant data in the upper non leaf nodes , But such shortcomings are tolerable , Because redundant data is index data , Not a big burden on memory .

Cluster index

id It's the primary key , So clustering index , The leaf node stores the data of the corresponding row record .

Clustered index storage structure

If the query condition is primary key （ Cluster index ）, It only needs to be scanned once B+ The tree can locate the row record data to be searched through clustering index .

Such as ：

SELECT * FROM t_back_to_table WHERE id = 1;

Lookup process ：

Clustering index search process  

General index

drinker_id  It's a general index （ Secondary indexes ）, Nonclustered index Of The leaf node stores the value of the cluster index , The primary key ID Value .

Common index storage structure

If the query condition is normal index （ Nonclustered index ）, It needs to be scanned twice B+ Trees .

• First scan First Locate the value of the clustered index through the normal index .
• The second scan passed From the first scan The value of the clustered data record to locate .

Such as ：

SELECT * FROM t_back_to_table WHERE drinker_id = 1;

（1） First step , First locate the primary key value through the normal index id=1;

（2） The second step , Return to the table for query , Then locate the row record data through the located primary key value, that is, the clustered index .

Ordinary index lookup process

5、 ... and 、 How to prevent back to the table

Now that we know there's a back watch , We must try our best to prevent the slightest possible change . The most common way to prevent back to the table is index overwrite , Beat the index through the index .

Index overlay

Why can we use the index to beat the index to prevent back to the table ？ Because of its It only needs to be on an index tree to get SQL All required column data , There is no need to return the form Inquire about .

 for example ：SELECT * FROM t_back_to_table WHERE drinker_id = 1;

How to implement overlay index ？

The common method is to query the field , Build to Federated index .

Explanatory SQL Of explain Output result of Extra Field is Using index Indicates that an index override has been triggered .

No Overlay index case1

Continue using the previously created  t_back_to_table surface , Through the general index drinker_id Inquire about id and  drinker_id Column .

EXPLAIN SELECT id, drinker_id FROM t_back_to_table WHERE drinker_id = 1;

explain analysis ： Why is the overlay index not created Extra The field is still Using index, because drinker_id It's a general index , Using the drinker_id Indexes , It's mentioned above General index The leaf node of is saved The value of the clustered index , therefore Through a scan B+ The tree can query the corresponding results , That's it Invisible Overlay index , That is, there is no artificial joint index .（drinker_id The index contains the value of the primary key index ）

No Overlay index case2

Continue using the previously created  t_back_to_table surface , Through the general index drinker_id Inquire about id、drinker_id and drinker_feature 3、 ... and Column data .

EXPLAIN SELECT id, drinker_id, drinker_feature FROM t_back_to_table WHERE drinker_id = 1;

explain analysis ：drinker_id It's a general index On its leaf node, only Contains the value of the primary key index , and  drinker_feature  Column and Not on the index tree , So pass drinker_id  The index is looking up id and drinker_id After the value of , According to the primary key id Back to table query , obtain  drinker_feature  Value . At this time Extra Column NULL Indicates that a return table query has been performed .

Overlay index case

To achieve index coverage , You need to build a composite index  idx_drinker_id_drinker_feature(drinker_id,drinker_feature)

# Delete index  drinker_id

DROP INDEX drinker_id ON t_back_to_table;

# Set up composite index 

CREATE INDEX idx_drinker_id_drinker_feature on t_back_to_table(`drinker_id`,`drinker_feature`);

Continue using the previously created  t_back_to_table surface , By overriding index  idx_drinker_id_drinker_feature  Inquire about id、drinker_id and drinker_feature 3、 ... and Column data .

EXPLAIN SELECT id, drinker_id, drinker_feature FROM t_back_to_table WHERE drinker_id = 1;

explain analysis ： Now the field drinker_id and drinker_feature It's a composite index idx_drinker_id_drinker_feature, Fields to query id、drinker_id and drinker_feature The values of are just on the index tree , Just scan the composite index once B+ A tree will do , This is the implementation of index coverage , At this time Extra Field is Using index Indicates that index overlay is used .

6、 ... and 、 Index coverage optimization SQL scene

It is suitable to use index coverage to optimize SQL The scene is like a full table count Inquire about 、 Column query back to table and paging query, etc .

Full table count Query optimization

# First of all to delete  t_back_to_table  Combined index in table 

DROP INDEX idx_drinker_id_drinker_feature ON t_back_to_table;

EXPLAIN SELECT COUNT(drinker_id) FROM t_back_to_table

explain analysis ： At this time Extra Field is Null Indicates that index overrides are not used .

Use index overlay optimization , establish drinker_id Field index .

# establish  drinker_id  Field index 

CREATE INDEX idx_drinker_id on t_back_to_table(drinker_id);

EXPLAIN SELECT COUNT(drinker_id) FROM t_back_to_table

explain analysis ： At this time Extra Field is Using index Indicates that index overlay is used .

Column query return table optimization

The example of index coverage described above is column query back to table optimization .

for example ：

SELECT id, drinker_id, drinker_feature FROM t_back_to_table WHERE drinker_id = 1;

Use index overlay ： Build a composite index idx_drinker_id_drinker_feature on t_back_to_table(`drinker_id`,`drinker_feature`) that will do .

Paging query optimization

# First of all to delete  t_back_to_table  The index in the table  idx_drinker_id

DROP INDEX idx_drinker_id ON t_back_to_table;

EXPLAIN SELECT id, drinker_id, drinker_name, drinker_feature FROM t_back_to_table ORDER BY drinker_id limit 200, 10;

explain analysis ： because  drinker_id Field is not index , So in paging query, you need to query back to the table , here Extra by U sing filesort File sorting , Poor query performance .

Use index overlay ： Build a composite index  idx_drinker_id_drinker_name_drinker_feature

# Set up composite index  idx_drinker_id_drinker_name_drinker_feature (`drinker_id`,`drinker_name`,`drinker_feature`)

CREATE INDEX idx_drinker_id_drinker_name_drinker_feature on t_back_to_table(`drinker_id`,`drinker_name`,`drinker_feature`);

Again on the basis of drinker_id Paging query ：

EXPLAIN SELECT id, drinker_id, drinker_name, drinker_feature FROM t_back_to_table ORDER BY drinker_id limit 200, 10;

explain analysis ： At this time Extra Field is Using index Indicates that index overlay is used .

 

 

 

 

Five flower horse

　　　　 A thousand furs

　　　　　　　　　　　　 Hoo'er is going out for a good drink

With you to sell eternal sorrow