Again to the golden three silver four job-hopping season, now a lot of small partners are ready to job-hopping, hand inside the test must be very much, many interview questions, many chances of success in the interview. A few days ago, one of my classmates interviewed several companies and told me that every interview would encounter MySQL back table query and overwrite index, and my answers were not very complete. So today we will explain from beginning to end back to the table query and overwrite index is how to return.
Contents of Study:
What are the scenarios where index overwriting can be used to optimize the benefits of SQL overwriting indexes? Index overwriting notes back to table queries, starting with InnoDB’s index implementation. InnoDB has two main categories of indexes, Clustered Index and Secondary Index.
InnoDB’s clustered index: The leaves of InnoDB’s clustered index store row records, so InnoDB must have only one clustered index.
1. If the table defines a PK(Primary Key), then PK is the clustered index.
2. If the table does NOT define PK, then the first column that is NOT NULL UNIQUE is the clustered index.
3. Otherwise InnoDB will create another hidden ROWID as the clustered index.
This mechanism makes PK-based queries very fast because row records are located directly.
InnoDB’s normal index: The leaf node of InnoDB’s normal index stores the primary key (MyISAM is the stored row header pointer).
Return table query: suppose there is a T table (id PK, name KEY, sex, flag) where ID is the clustered index and name is the common index.
There are four entries in the table:
Clustered index B+ tree index (id is PK, leaf node stores row records) :
B+ tree index (name is KEY, leaf node stores PK value, i.e. id) :
Because ordinary indexes cannot locate row records directly, the query process usually requires two scans of the index tree.
select * from t where name = ‘lisi’;
Here’s how it works:
The pink path in the figure needs to scan the index tree twice, first through the ordinary index to locate the primary key value ID =5, and then through the clustered index to locate the specific row record. This is called a back-table query, which locates the primary key value and then the row record based on the primary key value. Performance is lower than scanning the clustered index tree once.
What is an overwrite index
An index is an overwrite index if it overwrites (contains) the values of all the fields that need to be queried. Because the index already contains the value of the field to be queried, you can directly return the value of the field in the index, without the need to query in the table, avoiding the secondary query of the primary key index, and improving the query efficiency.
Index coverage is an optimization strategy to avoid table-back queries. Specific approach is to establish common index as the index column of the query data (can be a single index, can be an index statements define all the columns to query, namely the united index), so you can direct return in the index data, do not need to travel through the clustered index to locate rows, avoids the back to the table.
In short: you can get all the column data you need for SQL in a single index tree, no need to go back to the table, faster. (This is not hard to understand.)
Which scenarios can use index coverage to optimize SQL?
Scenario 1: Table structure
More than 1.5 million pieces of data, in one simple statement:
Slow query logs are used for 1 second, Explain results are:
First, the statement ORDER BY uses the Using filesort file sorting, query efficiency is low; Secondly, the query field is not on the index, no overwrite index is used, the need to return to the table through the index query, there are also reasons for data distribution. Knowing the reason, the problem is easy to solve.
Solution: Since you only need to query the UID field, adding a federated index can avoid table back and file sorting, use overwrite indexes to speed up the query, and use indexes to complete sorting.
Overwrite index: SQL can return the data required by the query only through the index, rather than through the secondary index to the primary key to query the data.
Let’s Explain again:
Case Summary: When a query statement meets the conditions of overwriting an index, SQL only needs to use the index to return the data required for the query. In this way, the operation of returning to the table after the index is found is avoided, which reduces I/O and improves efficiency.
—- Innodb uses a clustered index to organize data. If a secondary index contains the data needed for a query, there is no need to look it up in the clustered index.
Scene 2:
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.
What are the advantages of overwriting indexes?
Overwriting an index is a very powerful tool that can greatly improve query performance by reading the index without reading the data. It has the following four advantages:
1. Index entries are usually smaller than records, so MySQL accesses less data.
2. Indexes are stored in order of value size, requiring less I/O than randomly accessing records.
Most data engines do a better job of caching indexes. MyISAM, for example, only caches indexes.
4. Overwrite indexes are especially useful for InnoDB tables because InnoDB uses clustered indexes to organize data. If a secondary index contains data needed for a query, there is no need to look it up in the clustered index.
Five, overwrite index notes
Note that not all types of indexes can be overridden. MySQL can only use a b-tree index to create an overwrite index, because a hash index, a spatial index, and a full-text index do not store column values.