Summary of knowledge points about covering index in MySQL

If an index contains (or covers) the values ​​of all fields that need to be queried, it is called a ' covering index '.

Covering index is a very powerful tool that can greatly improve query performance. It only needs to read the index instead of reading the data. It has the following advantages:

1. Index entries are usually smaller than records, so MySQL accesses less data.

2. Indexes are stored by value size, which requires less I/O than random access records.

3. The data engine can cache indexes better, for example, MyISAM only caches indexes.

4. Covering indexes are particularly useful for InnoDB because InnoDB uses clustered indexes to organize data. If the secondary index contains the data required for the query, there is no need to search in the clustered index.

limit:

1. Covering indexes are not applicable to any index type. The index must store the values ​​of the columns.

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

3. Different storage engines implement covering indexes differently, and not all storage engines support covering indexes.

4. If you want to use a covering index, be sure to select the required columns from the SELECT list value. Do not use SELECT *, because if all fields are indexed together, the index file will be too large and query performance will deteriorate.

Knowledge point expansion:

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".

The above is the detailed content of the summary of the knowledge points about covering indexes in MySQL. For more information about what is covering index in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!