MySQL Overlay Index

It is common for everyone to wear a suitable index based on the where condition of the query, but this is only one aspect of index optimization. A well-designed index should take into account the entire query, not just the Where condition part. Indexes are really an efficient way to find data, but MySQL can also use indexes to get data directly from columns, so that you no longer need to read rows of data. If the index's leaf node already contains the data to be queried, then what is the need to return to the table query? If an index contains (or overrides) the value of all fields that need to be queried, we call it "overwrite index".

Overwriting indexes is a very useful tool that can greatly improve performance. Consider the benefits of a query that requires only an index without returning a table:

Index entries are usually much smaller than the data row size, so only the index is read, so MySQL greatly reduces the amount of data access. This is important for the load on the cache, because in this case most of the time is spent on the copy of the data. Overriding indexes are also helpful for I/O intensive applications, where indexes are smaller than data and are easier to put into memory (this is especially true for MyISAM, because MyISAM can compress indexes to become smaller).

Because indexes are stored in the Order of column values (at least within a single page), the IO-intensive range query is much smaller than the IO that is randomly read from disk. For some storage engines, such as MyISAM, it is even possible to make the indexes fully ordered by optimize commands, which allows simple range queries to be accessed using fully sequential indexes.

Some storage engines, such as MyISAM, only cache indexes in memory, and data relies on the operating system to cache, so access to data requires a system call. This can lead to serious performance problems, especially in scenarios where system calls account for the maximum overhead in data access.

Because of the clustered index of InnoDB, the overlay index is particularly useful for the InnoDB table. InnoDB's Level Two index holds the row's primary key value in the leaf node, so if a level two primary key can overwrite the query, you can avoid two queries on the primary key index. In all of these scenarios, the cost of satisfying a query in an index is generally much smaller than the query.

Not all types of indexes can become overwrite indexes, overwriting indexes must store the values of indexed columns, while hash indexes, spatial indexes, and full-text indexes do not store the values of the columns, so MySQL can only use the B-tree index to overwrite the index. Different storage engines implement different ways of overwriting indexes, and not all coverage indexes support overwriting indexes.

When a query that launches an index overlay (also called an index overwrite query) is initiated, the information "Using index" can be seen in the extra column of explain.

Overwriting index queries also has many pitfalls that can lead to optimizations that cannot be implemented. The MySQL query optimizer will determine if there is an index that can be malformed before executing the query. Suppose the index overrides the field in the Where condition, but not the field involved in the entire query. If the query condition is false, MySQL5.5 and earlier versions will always get the rows back to the table, and it does not require this line and will always be filtered out.

Have the following query:

SELECT * from Produs WHERE actor= "Bob" and the title like "%a%"

There are two reasons why an index cannot overwrite this query:

1 No index can overwrite this query. Because the query selects all columns from the table, none of the indexes overwrite all the columns. In theory, however, MySQL has a shortcut that can be exploited: the columns in the Where condition can be overwritten by an index, so MySQL can use the index to find the corresponding actor and check if the title matches, filtering and then reading the required data rows.

2 MySQL cannot perform the like operation in the index. This is the underlying storage engine API limit, which allows simple comparison operations (for example, equal to, greater than, not equal to) in the index in MySQL5.5 and earlier versions. MySQL can make a like comparison of the leftmost prefix in the index, because the operation can be converted into a simple comparison operation, but if it is a like query beginning with a wildcard, the storage engine cannot compare matches. In this case, the MySQL service function extracts the values of the data rows instead of the index values for comparison.

There are ways to solve the two problems mentioned above, you need to rewrite the query and cleverly design the index. Expand the index to overwrite three data columns (artist,title,prod_id) and then rewrite the query as follows.

SELECT * from the Products JOIN (select prod_id from Products WHERE actor= "BOB" and the title like "%a%") as T1 on (t1.pro_id = Pro DUCTS.PROD_ID).

We call this a deferred association (deferred join) because it delays access to the column. In the first stage of the query, MySQL can use the overwrite index, find the matching prod_id in the subquery from the FROM clause, and then get all the required column values in the out-of-office query match based on this prod_id value. Although it is not possible to overwrite an entire query with an index, it is always better to overwrite the index completely.

　　

The optimization effect depends on the number of rows returned in the Where condition match, assuming that the Products table has 100w rows of data, let's take a look at the performance of the above two queries on three different datasets, each containing a 100W row:

First DataSet: Bob starred in the 3W work, with the 2W section heading containing a

Second data set: Bob ..... 3W ...,............ 40 parts .....

The third set of data: ..... ..... Part 50 ..... Part 10 ....

Data set original query optimization after query

1 5 times per second 5 times per second

2 7 times per second 35 times per second

3 2,400 times per second 2000 times per second

The following is an analysis of the results:

In instance 1, the query returns a large result set, so the effect of the optimization is not visible. Most of the time is spent reading and sending data.

In instance 2, the index filter, especially the second conditional filter only returns a very small result set, the optimization effect is very obvious: the performance of the data set improved 5 times times, the efficiency of the optimized query is mainly due to read 40 rows of complete data, rather than the original query 3W rows.

In Example 3, the results show that the sub-query efficiency is not high but decreases. Because the result set that meets the first condition in index filtering is already small, the cost of a subquery is higher than extracting the complete row directly from the table.

In most storage engines, an overlay index can only overwrite queries that are only accessed in the middle of the index. However, InnoDB can be further optimized. Recall that the leaf nodes of the InnoDB level two index contain the value of the primary key, which means that the InnoDB two-level index can effectively overwrite the query with these additional primary keys.

　　

　　

MySQL Overlay Index