Detailed explanation of MySQL covering index

concept

If the index contains all the data that meets the query requirements, it is called a covering index, which means that there is no need to return to the table.

Judgment criteria

When using explain, you can judge by the extra column of the output. For an index covering query, it is displayed as using index. The MySQL query optimizer will determine whether there is an index covering query before executing the query.

Notice

1. Covering indexes are not applicable to any index type. The index must store the value of the column.

2. Hash and full-text indexes do not store values, so MySQL can only use B-TREE

3. Different storage engines implement covering indexes differently

4. Not all storage engines support them

5. If you want to use a covering index, you must pay attention to the SELECT list value to extract the required columns. You cannot use SELECT *, because if you index all fields together, the index file will be too large and the query performance will decrease. You cannot do this just to use a covering index.

If an index contains (or covers) the values ​​of all fields that need to be queried, it is called a 'covering index'. That is, you only need to scan the index without returning to the table.

Advantages of scanning only the index without going back to the table:

1. Index entries are usually much smaller than the data row size, and only the index needs to be read, so MySQL will greatly reduce the amount of data access.

2. Because the index is stored in order of column values, IO-intensive range searches will require much less IO than randomly reading each row of data from disk.

3. Some storage engines such as MyISAM only cache indexes in memory, and rely on the operating system to cache data, so accessing data requires a system call

4. Innodb's clustered index and covering index are particularly useful for Innodb tables. (InnoDB's secondary index stores the primary key value of the row in the leaf node, so if the secondary primary key can cover the query, the secondary query of the primary key index can be avoided)

A covering index must store the values ​​of the index columns, while hash indexes, spatial indexes, and full-text indexes do not store the values ​​of the index columns, so MySQL can only use B-tree indexes as covering indexes.

When an index coverage query is initiated, the using index information can be seen in the extra column of explain.

Pitfalls of covering indexes: The MySQL query optimizer will determine whether there is an index that can cover the query before executing it. Assuming that the index covers the fields in the where condition but not the fields involved in the entire query, MySQL 5.5 and earlier versions will also go back to the table to obtain the data row, even though this row is not needed and will eventually be filtered out.

As shown in the figure above, coverage query cannot be used for the following reasons:

1. No index can cover this index. Because the query selects all columns from the table and there is no index covering all columns.

2.MySQL cannot perform a LIK operation on an index. MySQL can do a LIKE comparison with a leftmost prefix match in the index, but if the LIKE query starts with a wildcard, the storage engine cannot do a comparison match. In this case, MySQL can only extract the value of the data row instead of the index value for comparison

Optimized SQL: Add indexes (artist, title, prod_id), use delayed association (delayed access to columns)

Note: In the first stage of the query, you can use a covering index to find the matching prod_id in the subquery in the from clause, and then query the outer layer to match and obtain all the required values ​​based on the prod_id value.

In 5.5, the API design does not allow MySQL to pass filtering conditions to the storage engine layer (it pulls data from the storage engine to the server layer and then filters according to the conditions). After 5.6, the ICP feature improves the query execution method.

When MySQL cannot use the index for sorting, it will use its own sorting algorithm (quick sort algorithm) to sort the data in memory (sort buffer). If the memory cannot fit, it will divide the data on the disk into blocks, sort each data block, and then merge each block into an ordered result set (actually external sort).

For filesort, MySQL has two sorting algorithms

1. Two passes

The implementation method is to first retrieve the fields to be sorted and the pointer information that can directly locate the relevant row data, and then sort them in the set memory (set by the parameter sort_buffer_size). After the sorting is completed, the required columns are retrieved again through the row pointer information.

Note: This algorithm is used before 4.1. It needs to access data twice. In particular, the second read operation will cause a large number of random I/O operations. On the other hand, the memory overhead is small

2. Single pass algorithm

This algorithm takes out all the required columns at one time, sorts them in memory, and directly outputs the results. Note: This algorithm has been used since MySQL 4.1. It reduces the number of I/O operations and is more efficient, but it also incurs a large memory overhead. If we take out the columns that are not needed, it will greatly waste the memory required for the sorting process. In MySQL 4.1 and later versions, you can control whether MySQL chooses the first or second sorting algorithm by setting the max_length_for_sort_data parameter. When the total size of all large fields retrieved is greater than the max_length_for_sort_data setting, MySQL will choose to use the first sorting algorithm, otherwise it will choose the second one. In order to improve the sorting performance as much as possible, we naturally prefer to use the second sorting algorithm, so it is very necessary to only retrieve the required columns in the Query.

When sorting a join operation, if ORDER BY refers only to columns of the first table, MySQL performs a filesort operation on the table and then performs the join. In this case, EXPLAIN outputs "Using filesort". Otherwise, MySQL must generate a temporary table for the query result set and perform a filesort operation after the join is completed. In this case, EXPLAIN outputs "Using temporary; Using filesort".

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links