Quick Fixes for Inefficient SQL? Left a tail:
select id,name where name=’shenjian’
select id,name_,sex_ where name=’shenjian’
Why is the retrieval process completely different when one more attribute is queried?
What is a back table query?
What is index coverage?
How to implement index coverage?
In what scenarios can index coverage be used to optimize SQL?
These, this is what I want to share today.
Voice-over: This experiment is based on mysql5.6-InnoDB.
What is a back table query?
This starts with InnoDB’s index implementation. InnoDB has two categories of indexes:
-
Clustered Index
-
Secondary index
** What are the differences between InnoDB clustered indexes and normal indexes? 支那
The leaf node of InnoDB’s clustered index stores row records, so InnoDB must have only one clustered index:
(1) If the table defines PK, PK is the clustered index;
Select * from table where PK is not defined; select * from table where PK is not defined;
Otherwise, InnoDB creates a hidden row-ID as the clustered index;
Voice-over: So PK queries are very fast and directly locate row records.
The leaf node of InnoDB’s normal index stores primary key values.
Voice-over: Note that instead of storing the row record header pointer, MyISAM’s index leaf node stores the record pointer.
For example, we might as well have a table:
t(id PK, name KEY, sex, flag);
Voice-over: ID is the clustered index, and name is the plain index.
There are four entries in the table:
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
The two B + tree indexes are shown in the figure above:
(1) Id is PK, clustered index, leaf node stores row records;
(2) Name is KEY, a common index, and the leaf node stores THE PK value, that is, ID;
Since row records cannot be located directly from a normal index, what is the query process for a normal index?
Typically, you need to scan the index tree twice.
Such as:
select * from t where name=’lisi’;
How is it implemented?
If the path is pink, it needs to scan the index tree twice:
(1) First locate the primary key value id=5 through the common index;
(2) locate the row record by clustering index;
This is known as a back-table query, which locates the primary key first and then the row record, and has lower performance than a sweep of the index tree.
Second, what is index coverage (Covering index)****?
This concept is not found on the MySQL website.
Are you serious about your studies?
Borrow sqL-server official website statement.
If the Extra field of the output result of EXPLAIN is Using index, index overwriting can be triggered.
You can obtain all columns of SQL data in a single index tree without returning to the table.
How to achieve index coverage?
The common method is to create a joint index for the field to be queried.
Again, Quick Fixes for Inefficient SQL? Examples from:
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
First SQL statement:
select id,name from user where name=’shenjian’;
The index leaf node stores the primary key ID. The id and name can be obtained through the index tree of name without returning to the table, which is in line with index coverage and high efficiency.
Voice-over, Extra:Using index.
Second SQL statement:
select id,name_,sex_ from user where name=’shenjian’;
The name index can be matched. The index leaf node stores the primary key ID, but the sex field can only be obtained by querying back to the table, which does not meet the index coverage. Therefore, the efficiency of obtaining the sex field through the ID value scanning code aggregation index will be reduced.
Voice-over, Extra:Using index condition.
If the single-column index (name) is upgraded to the federated index (name, sex), it is different.
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;
You can see:
select id,name … where name=’shenjian’;
_select id,name,_sex … where name=’shenjian’;
Are able to hit index overwrites without needing to go back to the table.
Voice-over, Extra:Using index.
Which scenarios can use index coverage to optimize SQL?
Scenario 1: Full table count query optimization
The original table as follows:
User (PK ID, name, sex);
Direct:
select count(name) from user;
Cannot use index overwrite.
Add index:
alter table user add key(name);
You can use index overrides to improve performance.
Scenario 2: Column query back to table optimization
select id,name,sex … where name=’shenjian’;
This example will not be repeated, but the single column index (name) can be upgraded to the federated index (name, sex) to avoid the table back.
Scenario 3: Paging query
select id,name,sex … order by name limit 500,100;
A single column index (name) can also be upgraded to a federated index (Name, sex) to avoid back tables.
InnoDB aggregate index normal index, back table, index overwrite.
If you do not know the meaning of Using index in explain results, please read the article “How to use tools to quickly locate inefficient SQL”.
The architect’s Path– Share landing technology
Related recommendations:
Buffer Pool, get it once and for all!
Change Buffer, Get it This time!
Database Indexes, What are they Really Made of? Dry goods