What is the difference between MySQL table back query and index coverage?

Back to table query

InnoDB indexes are divided into two categories, one is clustered index (Clustered Index) and the other is non-clustered index (Secondary Index)

Clustered index (clustered index) Index): The leaf node stores the entire row of data. If you find the index, you will find the data. The index is the data. The physical order of the rows in the table is the same as the logical (index) order of the key values. A table can only contain one clustered index. Because the index (directory) can only be sorted in one way.

Non-clustered index (ordinary index, non-clustered index, secondary index): The btree leaf node of the non-clustered index stores the PK (primary key) of the current row of data. For example, MYISAM first caches the index into the memory through key_buffer. When data needs to be accessed (data accessed through the index), the index is searched directly in the memory, and then the corresponding data on the disk is found through the index. This is why the index does not hit the key buffer. The reason for the slow speed.

Why do leaf nodes of non-primary key index structures store primary key values?

Reduces the maintenance work of secondary index when row movement or data page splitting occurs (when data needs to be updated, the secondary index does not need to be modified, only the clustered index needs to be modified, one table only needs to There can be a clustered index, and the others are secondary indexes, so you only need to modify the clustered index, and there is no need to rebuild the secondary index)

When using a non-clustered index, in order to obtain specific Data, we need to return to the clustered index by primary key and query the data. Then call back the table query. The index tree was scanned twice. So the efficiency is relatively low.

Index coverage

Index coverage is a solution to table query. As the name implies, all columns of the query are covered by the index columns used (it can be a single-column index or a joint index, usually a joint index. It is difficult for a single-column index to cover all the columns of the query).

Because the index already contains the value of the field to be queried, it is enough to directly return the field value in the index when querying. There is no need to query the table again, avoiding the secondary re-examination of the primary key index. Query also improves the efficiency of query.

id is a clustered index, name is a non-clustered index:

select name, age from t where name = 'lcc';

You need to query back to the table

Index coverage:

Only query name in SQL field. In this way, the index of name covers all query columns.

select name  from t where name = 'lcc';

Change the index of name to a joint index (name, age), and then execute select name, age from t where name = 'lcc'. This also covers all query columns.
Because covering indexes must store index column values, while hash indexes, spatial indexes, and full-text indexes do not store index column values, so only data using B-Tree indexes can be used as covering indexes.

When performing an index coverage query, you can see the [Using Index] information in the Extra column of explain (execution plan).

Advantages of index covering

• #Index entries are usually much smaller than the size of the data row, because covering the index only requires reading the index, greatly reducing the number of data visits.

• The index is stored in column value order, and the IO-intensive range search will be much smaller than the IO of randomly reading each row of data from the disk.

• Some storage engines such as MyISAM only cache indexes in memory, and the data relies on the operating system to cache. Therefore, accessing data requires a system call. Using covering indexes avoids this. .

• For database tables under the InnoDB engine, covering indexes are very practical because of InnoDB's clustered index. Because InnoDB's secondary index saves the primary key value of the row in the leaf node, if the secondary index can cover the query, it avoids a secondary query of the primary key index.

Which scenarios are suitable for using index coverage to optimize SQL

• When there is no need to query the entire row of records;

• Full table count query optimization;

• Limit paging query;

Under what circumstances do not build an index

• Too few table records

• Tables or fields that are frequently added, deleted or modified (such as user balance)

• Do not create an index for fields that are not used in the Where condition

• Filtering is not suitable for indexing (such as gender)

Index pushdown

Index pushdown optimization was introduced in MySQL 5.6. During the index traversal process, the fields included in the index can be judged first, and records that do not meet the conditions can be directly filtered out to reduce the number of table returns

Create a joint index:

KEY `username` (`name`,`age`) )

Execution:

select * from user2 where name like 'j%' and age=99;

The above query sql complies with the leftmost prefix principle of the index, so the username index will be used

The execution flow of the above SQL in 5.5 is as follows:

• First, the server layer of MySQL calls the storage engine to obtain the first username starting with j.

• After the storage engine finds the first record of username=‘j’, the primary key id is saved in the leaf node of B Tree. At this time, the primary key is removed through the table return operation. The complete data of the record is found in the index and returned to the server layer.

• After the server layer gets the data, it determines whether the age of the record is 99. If age=99, it returns the record to the client. If age!=99, then it Discard the record.

The execution process of the above SQL in 5.6 is as follows:

• The server layer of MySQL first calls the storage engine to locate the first Username starting with j.

• After finding the record, the storage engine is not in a hurry to return to the table, but continues to determine whether the age of this record is equal to 99. If age=99, it will return to the table. If age If it is not equal to 99, the table will not be returned and the next record will be read directly.

The above is the detailed content of What is the difference between MySQL table back query and index coverage?. For more information, please follow other related articles on the PHP Chinese website!