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 The common index leaf node of is storing 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 .

Because the query method is based on 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 by 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 The clustered index and ordinary index of the engine 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 ）, The leaf node of non clustered index stores the value of clustered 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 .

  • The first scan first locates the value of the cluster index through the ordinary index .
  • The second scan locates the value of the cluster index obtained by the first scan to the row record data to be found .

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 it can be obtained only on an index tree SQL All required column data , No need to return to the table to query .

 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 , As mentioned above, the leaf node of the general index saves the value of the cluster index , So through a scan B+ The tree can query the corresponding results , In this way, the invisible overlay index is realized , 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 Three columns of data .

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

explain analysis ：drinker_id Is a normal index whose leaf node contains only the value of the primary key index , and  drinker_feature  The column is 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 Three columns of 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
 

MySQL More related articles back to the table

  1. MySQL Return to the table for query

    One .MySQL Index type 1. General index : The most basic index , No restrictions 2. unique index (unique index): The value of the index column must be unique , However, it is allowed to be empty 3. primary key : Special unique index , However, it is not allowed to be empty , Generally, the table under construction ...

  2. MySQL Return to the table for query & Index coverage optimization

    Return to the table for query First, the cluster index value is located by the value of the common index , Then through the value of clustering index to locate the row record data Table building example mysql> create table user( -> id int(10) auto_incre ...

  3. understand MySQL Back to the table

    Back to the table is to scan the row where the data is located through the database index , And then through the row primary key id Take out the data not provided in the index , In other words, the query based on non primary key index needs to scan one more index tree . therefore , You can find out... By index first id Field , Through the primary key id Field , The fields in the query row ...

  4. (MYSQL) The principle of return table query , Using joint index to achieve index coverage

    One . What is return table query ? First of all, we have to start from InnoDB The index implementation of ,InnoDB There are two main categories of indexes : Clustered index (clustered index) General index (secondary index) InnoDB Clustered index and normal ...

  5. MySQL Optimize it MRR (Multi-Range Read: Secondary indexes merge back into tables )

    MySQL5.6 Introduced in MRR, To optimize : Secondary index range scan and need to return to the table . Its principle is , Sort multiple secondary indexes that need to be returned to the table according to the primary key , And then go back to the chart together , The original table back to the random IO, Turn it into order IO. file ...

  6. mysql Back table query and index coverage in

    Get to know MySQL Back table query and index coverage in . Return to the table for query To say back to table query , First from InnoDB The index implementation of .InnoDB There are two main categories of indexes , One is clustered index (Clustered Index), One is the general index (Sec ...

  7. MySQL Optimize ： How to avoid returning table query ？ What is index coverage ？ ( turn )

    Database table structure : create table user ( id int primary key, name varchar(20), sex varchar(5), index(name) )engin ...

  8. 【mysql】 Indexes Back to the table Overlay index Index push down

    Index type Index types include primary key index and non primary key index .( Remember , How to store data ) The leaf node of the primary key index stores the whole row of data . stay InnoDB in , Primary key indexes are also called clustered indexes (clustered index). Nondominant ...

  9. MySQL Database back to table and index

    Catalog Back to the table concept 1.stu_info Table cases 2. View the table structure just created 3. Insert test data 4. The analysis process 5. Implementation plan Back to the table concept Draw a conclusion first , According to the following experiment . If I want to get ['liu','25'] This note ...

  10. 【MySQL】 Overwrite index and return table

    Let's take a look at two types of indexes Cluster index ( Also known as clustered index , Primary key index, etc ) General index ( Also called nonclustered index , Secondary index, etc ) Cluster index If the table has a primary key set , Then the primary key is the clustered index If the table does not have a primary key , It defaults to the first NOT NULL, And ...

Random recommendation

  1. Learn something every day GDB 12

    This article is introduced in archlinux In the environment , How to use kernel gdb coordination qemu debug .   1. install qemu   2. compile linux kernel Select the latest kernel version , avoid gcc For the problem of compilation error, the specific steps are as follows: ...

  2. course ： Look for memory leaks (JavaScript)

    This topic leads you through the use of JavaScript The process by which the memory analyzer identifies and fixes simple memory problems . In this tutorial , We create an application that generates a lot of data . We expect the application to release data when navigating to a new page .   explain JavaScr ...

  3. 2、 All kinds of twists and turns , Install cross environment gcc and qt, test c++ and qt Program

    This blog only records my operation process , Keep as a note . Unremitting self-improvement QQ1222698 1. install gcc and qt Take what's on the CD gcc-4.4.4-glibc-2.11.1-multilib-1.0_EasyARM-iMX2 ...

  4. Using neural network algorithm C＃ Handwritten digit recognition

    Welcome to cloud + Community , Get more Tencent mass technology practice dry goods ~ download Demo - 2.77 MB ( Original address ):handwritten_character_recognition.zip Download the source code - 70. ...

  5. Nuclear principal component analysis （KPCA） How to understand ？

    First review the principal component analysis method .PCA The conclusion of the maximum variance derivation is , After projecting the data to the direction of the eigenvector , With a maximum variance . If you first map the data to a new feature space , Do it again PCA What will happen ? For some data , The variance will be better preserved . Nuclear method ...

  6. kafka actual combat kerberos

    more /etc/krb5.conf [logging] default = FILE:/var/log/krb5libs.log kdc = FILE:/var/log/krb5kdc.log a ...

  7. 66.ajax--ajax Request more url terms of settlement

    ajax Request more url terms of settlement The following four methods are what I'm looking for , I have also practiced . There are four request interfaces in the test , Originally needed 13S, The third method is used to reduce to 7S, But still can't reach 2S within . So for reference only , When I find it, it can be reduced to 2S within ...

  8. Spring Cloud Vault Introduce

    https://mp.weixin.qq.com/s?__biz=MzU0MDEwMjgwNA==&mid=2247484838&idx=1&sn=6439ed96133dde ...

  9. The latest version ABP dynamic WebAPI Date to json belt T Solutions for | ABP DateTIme Json format

    ABP dynamic webapi Back to json In the data , Date time band T There's also the problem of milliseconds , In previous versions, you can use the following method to solve : stay XXXAbpWebApiModule Add the following code to : Very old very old version works : pub ...

  10. 2017-2018-1 JAVA The experimental station Week eight homework

    2017-2018-1 JAVA The experimental station Week eight homework See the team blog for details