Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of the Mysql coverage index, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Concept

If the index that contains all the data that meets the needs of the query becomes an overlay index (Covering Index), that is, there is no need to return to the table.

criterion

Using explain, you can tell by the output extra column. For an index overlay query, it is shown that the using index,MySQL query optimizer determines whether there is an index override query before executing the query.

Be careful

1. The overlay index is not applicable to any index type, and 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. And different storage engines implement overlay indexes differently.

4. Not all storage engines support them

5. If you want to use an overlay index, be sure to pay attention to the SELECT list value to take out the required columns, not SELECT *, because indexing all fields together will cause the index file to be too large and the query performance to be degraded. You can't do this in order to take advantage of the overlay index.

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

The advantage of scanning only the index without returning to the table:

1. Index entries are usually much smaller than the data row size, and if you only need to read the index, mysql will greatly reduce data access.

two。 Because indexes are stored in the order of column values, there is much less IO for IO-intensive range lookups than for randomly reading each row of data from disk.

3. Some storage engines, such as myisam, only cache indexes in memory, while data is cached by the operating system, so a system call is needed to access the data.

4.innodb 's clustered index, override index is particularly useful for innodb tables. (innodb's secondary index holds the primary key value of the row in the leaf node, so if the secondary primary key can override the query, you can avoid a secondary query on the primary key index.)

Override indexes must store the values of index columns, while hash indexes, spatial indexes, and full-text indexes do not store the values of index columns, so mysql can only use B-tree indexes as override indexes.

When you initiate an index override query, you can see the information of using index in the extra column of explain

Overwrite index pit: the mysql query optimizer determines whether an index can be overwritten before executing the query, assuming that the index overrides the fields in the where condition, but not the fields involved in the entire query. Mysql5.5 and previous versions will also go back to the table to get the data row, although this row is not needed and will eventually be filtered out.

As shown in the above figure, an override query cannot be used because:

1. No index can overwrite this index. Because the query selects all the columns from the table, and no index overwrites all the columns.

2.mysql cannot perform LIke operations in an index. Mysql can do like comparisons with leftmost prefixes in the index, but if it is a like query that begins 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 index (artist,title,prod_id), using deferred association (delayed access to columns)

Description: in the first phase of the query, you can use the overlay index, find the matching prod_id in the subquery in the from clause, and then match all the required values in the outer query based on the prod_id value.

At 5.5, the API design did not allow mysql to pass filtering conditions to the storage engine layer (pulling data from the storage engine to the server layer, filtering according to the conditions). Since then, the query execution has been improved due to the ICP feature.

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 be loaded, it will divide the data on the disk into blocks, sort each data block, and then merge each block into an ordered result set (in fact, external sort).

There are two sorting algorithms for filesort,MySQL

1. Two-pass scan algorithm (Two passes)

The way to achieve this is to first take out the fields to be sorted and the pointer information that can be directly located to the relevant row data, then sort them in the set memory (set by the parameter sort_buffer_size), and then retrieve the required Columns again through the row pointer information after sorting.

Note: this algorithm is the algorithm used before 4.1, it requires two accesses to the data, especially the second read operation will result in a large number of random Imax O operations. On the other hand, the memory overhead is small.

2. One scan algorithm (single pass)

The algorithm takes out all the required Columns at once, sorts the results in memory and outputs the results directly.

Note: this algorithm has been used since MySQL version 4.1. It reduces the number of Imax O and is more efficient, but the memory cost is also higher. If we take out the Columns that we don't need, we will waste a lot of memory needed for the sorting process. In versions later than MySQL 4.1, you can control whether MySQL chooses the first sorting algorithm or the second by setting the max_length_for_sort_data parameter. When the total size of all large fields fetched is larger than the max_length_for_sort_data setting, MySQL chooses the first sorting algorithm, and vice versa, the second. In order to improve sorting performance as much as possible, we naturally prefer to use the second sorting algorithm, so it is necessary to take out only the required Columns in Query.

When sorting join operations, if ORDER BY only references the columns of the first table, MySQL filesort the table and then perform join processing, in this case, EXPLAIN outputs "Using filesort"; otherwise, MySQL must generate a temporary table from the result set of the query, and perform the filesort operation after the join is completed, at which point, EXPLAIN outputs "Using temporary;Using filesort"

The above is all the contents of the article "sample Analysis of Mysql coverage Index". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.