SoFunction
Updated on 2025-05-09

Implementation of overriding index and table back operations in MySQL

In MySQL,Overwrite indexandReturn to the tableThese are two concepts closely related to query optimization. Understanding these two concepts will help us better optimize query performance and reduce unnecessary disk IO.

1. Covering Index

Overwrite indexRefers toThe index contains all the data columns required for the query, so there is no need to go back to the table to retrieve data when querying.

  • Index overwrites query, means that all columns involved in the query (including the fields of the query and the fields used for filtering and sorting) are included in the index.
  • When a query only needs the data in the index, MySQL will find the results directly in the index without accessing the actual rows of the data table, which can significantly improve the query efficiency.

For example:

Assume there is a table belowusers：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

If the following composite index is created:

CREATE INDEX idx_name_age ON users(name, age);

At this point, query the following:

SELECT name, age FROM users WHERE name = 'Alice' AND age = 30;

becauseidx_name_ageThe index contains what is required for the querynameandageFields, MySQL can directly find the required data in the index without returning to the table.

2. Return to the table (Lookup)

Return to the tableIt refers to when MySQL uses index to find records. If the index does not contain all the columns of the query, MySQL needs to go back to the original table to retrieve the actual record through the row pointer in the index (usually the primary key or unique index).

  • Return to the tableWhen the index contains only the query conditions or information about some columns, you need to access the data table again to obtain the complete data.
  • The lookup of indexes is fast, but when there are a large number of columns in the data table, returning to the table can cause additional IO overhead.

For example:

If you query:

SELECT name, age, email FROM users WHERE name = 'Alice' AND age = 30;

The index only containsnameandage,Right nowidx_name_ageIndex. MySQL will first find records that meet the criteria through the index, but it does not have an index column.email, therefore, you need to use the table back operation to pass theidFind the data tableemailList.

Overwrite index vs back to table

  • Overwrite index: When the index contains all the fields of the query, the table back operation can be completely avoided, and the query efficiency is high.
  • Return to the table: When the index does not contain all fields of the query, the query will need to return to the table to access the original data table, which will bring additional I/O operations and lead to reduced query performance.

3. Example: Back to table and Overwrite index

Example 1: Use the return table

CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age, email FROM users WHERE name = 'Alice' AND age = 30;
  • QueryednameandageListed in the index, butemailThe column is not in the index.
  • MySQL uses index to find rows that meet the criteria and get them by returning to the table.emailList.

Example 2: Use Overwrite Index

CREATE INDEX idx_name_age_email ON users(name, age, email);
SELECT name, age, email FROM users WHERE name = 'Alice' AND age = 30;
  • name、age、emailAll in the index.
  • MySQL can get all the required data directly from the index without returning to the table.

4. Advantages of using overlay indexes

  • Improve query efficiency: Avoid the additional overhead of backing the table, especially when the table contains a large number of columns, overwriting the index can greatly improve query speed.
  • Reduce I/O operations: The process of accessing tables is avoided during the query process, thereby reducing disk I/O operations.

5. Limits to overwrite indexes

  • Overriding indexes are not effective in all cases. If the number of columns in the query is large and the index does not contain all columns in the query, the table still needs to be returned.
  • The design of the index should take into account the actual needs of the query. Too many columns will increase the size of the index and affect performance.

6. How to optimize

  • Select the right index: Create composite indexes based on fields of commonly used queries. Make sure that common query columns are included in the index and avoid back to tables.
  • Avoid too many replies: When designing indexes, try to make the query operation completely satisfy through the index without having to return to the table.
  • Analyze query execution plan:useEXPLAINCheck the execution plan of the query to see if the overwrite index is used, or if the table back operation has occurred.

7. Summary

  • Overwriting Indexes Avoid the need to return to the table by including all fields required by the query in the index, thereby improving query performance.
  • Returning to the table means that when all data columns are not queried in the index, you need to take out the remaining columns by returning to the data table. Returning to the table will increase I/O overhead.
  • When designing indexes, try to overwrite the columns required by common queries through composite indexes, thereby optimizing query performance.

This is the end of this article about the implementation of overlay index and table back operations in MySQL. For more related contents of overlay index and table back operations, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!