What should I do if the Mysql index fails? Brief analysis of failure reasons

This article will record for everyone Mysql Index failure, analyze the reasons for Mysql index failure, I hope it will be helpful to everyone!

This article contains Mysql's Where condition query execution process, range query to stop the joint index from matching, table return operation analysis, common index failure scenarios, Extra analysis and other knowledge. [Related recommendations: mysql video tutorial]

Background

A full query appeared in the data table with 60 million data volume, and the sql was reproduced The statement discovery query does not use the index but uses the entire table query to find out the cause of the index failure.

# sql语句
EXPLAIN SELECT count(*) FROM order_recipient_extend_tab WHERE start_date>&#39;1628442000&#39; and start_date<&#39;1631120399&#39; and station_id=&#39;1809&#39; and status=&#39;2&#39;;

The order_recipient_extend_tab table has 60 million data. The query fields for slow query include start_date, station_id, and status. According to the original intention of the index design, the index that actually fails is:

Union index	Field 1	Field 2	Field 3
idx_date_station_driver	start_date	station_id	driver_id

Where conditional query execution process

Understand how Mysql executes where conditional query, and you can quickly and clearly understand the reasons for index failure. The index with high matching degree in this slow query is idx_date_station_driver. Analyze the execution process of where condition query in this slow query.

Mysql's where condition extraction rules can be summarized into three major categories: Index Key (First Key & Last Key), Index Filter, Table Filter .

Index Key

Index Key is used to determine the scope of this sql query on the index tree. A range includes the start and end, Index First Key is used to locate the starting range of the index query, and Index Last Key is used to locate the ending range of the index query.

• Index First Key

  Extraction rules: Starting from the first field of the index, check whether the field exists in the where condition, if it exists and the condition is =, >=, then add the corresponding condition to Index First Key, and continue reading the next field of the index; if it exists and the condition is >, add the corresponding condition to Index First Key, and then terminate Index First Key. Extract; if it does not exist, also terminate the extraction of Index First Key.

• Index Last Key

  is just the opposite of Index First Key. Extraction rules: start from the first field of the index and check whether it exists in the where condition. If If exists and the condition is =,

According to the Index Key extraction rules, the Index Last Key extracted in this slow query is: start_date>'1628442000', and the Index Last Key is: start_date

Index First Key is only used to locate the starting range of the index. Use the Index First Key condition, starting from the root node of the index B tree, and use the binary search method to quickly index to the correct leaf node position. During the Where query process, Index First Key is only judged once.

Index Last Key is used to locate the ending range of the index. Therefore, for each index record read after the starting range, it is necessary to determine whether it has exceeded the range of Index Last Key. If it exceeds, the current The query ends.

Index Filter

In the index range determined by Index Key, not all index records meet the query conditions. For example, in the Index Last Key and Index Last Key ranges, not all index records satisfy station_id = '1809'. At this time you need to use Index Filter.

Index Filter, also known as index pushdown, is used to filter records that do not meet the query conditions in the index query range. For each record in the index range, it needs to be compared with the Index Filter. If it does not meet the Index Filter, it will be discarded directly and continue to read the next record in the index.

Extraction rules of Index Filter: Starting from the first field of the index, check whether it exists in the where condition. If it exists and the condition is only =, skip the first field and continue to check the next field of the index. , the next index column adopts the same extraction rules (explanation: fields with the condition = have been filtered out in the Index Key); if it exists and the condition is >=, >,

According to the extraction rules of Index Filter, the Index Filter extracted in this slow query is: station_id='1809'. In the index query range determined by the Index Key, station_id='1809' needs to be compared when traversing the index records. If this condition is not met, it will be lost directly and the next record in the index will continue to be read.

Table Filter

Table Filter is used to filter out data that cannot be filtered by the index. After the entire row of records is queried in the secondary index through the primary key Returning to the table, it is judged whether the record meets the Table Filter conditions. If it does not meet the conditions, it will be lost and the next record will continue to be judged.

The extraction rules are very simple: all query conditions that do not belong to the index fields are classified into Table Filter. According to the extraction rules of Table Filter, the Table Filter in this query is: status=‘2’.

Summary and supplement

Index Key is used to determine the scope of the index scan; Index Filter is used to filter in the index; Table Filter needs to be returned to the table on the Mysql server to filter.

Index Key and Index Filter occur at the InnoDB storage layer, and Table Filter occurs at the Mysql Server layer.

Before MySQL5.6, there was no distinction between Index Filter and Table Filter. All index records within the range of Index First Key and Index Last Key were returned to the table to read the complete record, and then returned to the MySQL Server layer for processing. filter.

In MySQL 5.6 and later, the Index Filter is separated from the Table Filter. The Index Filter drops to the storage engine layer of InnoDB for filtering, which reduces the interaction overhead of returning tables and returning records to the MySQL Server layer, and improves the execution efficiency of SQL.

Analyze the cause of index failure

The first is count(). At this time, the wildcard * will not expand all columns after optimization, and will actually ignore all The column directly counts the number of rows. So if you only want to collect the number of rows, it is best to use count().

Next analyze the where statement. Assume that this slow query uses the secondary index idx_date_station_driver. According to the execution process of the above where condition query, the Index First Key of the slow query is start_date>'1628442000', and the Index Last Key is: start_date

After extracting the Index First Key, locating the index starting range on the index B tree is the process of index matching . Use the binary search method on the index B tree to quickly locate the start range that meets the query conditions. Leaf nodes. Through the above Where condition query execution process, we know the where condition of the slow query (start_date>'1628442000' and start_date, only Matches the first field of index <code>idx_date_station_driver(start_date, station_id, driver_id), that is, only matches idx_date_station_driver(start_date), station_id='1809' precise query does not affect the match On the index, it plays a role in the Index Filter, that is, the index push-down process. In fact, this is because the range query causes the joint index to stop matching .

Range query causes the joint index to stop matching

Why does the range query cause the joint index to stop matching? This involves the leftmost prefix matching principle. Assuming that a joint index index(a, b) is established, a will be sorted first, and if a is equal, b will be sorted, as shown in the figure below. On this index tree, a is globally ordered, while b is in a globally unordered and locally ordered state. From a global perspective, the value of b is 1, 2, 1, 4, 1, 2, and only b=2 query conditions cannot directly use this index; from a local perspective, when the value of a is determined , b is an ordered state, a=2 && b=4 can use this index. Therefore, the fundamental reason why the range query causes the joint index to stop matching is that the ordered state of the non-first field on the index tree depends on the equality of the previous field, and range query destroys the local ordered state of the next index field, Causes the index to stop matching.

Range query stops the joint index from matching, and cannot filter out data with station_id not equal to '1809' when the index matches, resulting in Mysql scanning range on the index Index First Key and Index Last Key are completely determined by start_timestamp_of_date time. The start_timestamp_of_date range query can filter out 73% of the data volume, while the station_id='1809' precise query can filter out 99% of the data volume.

Query conditions	Data volume	Percentage
All data	63.67 million	100%
start_timestamp_of_date>'1628442000' and start_timestamp_of_date	17.42 million	27.35%
station_id='1809'	80,000	0.16%

Overhead of table return operation

Since the status field is not on the index idx_date_station_driver field, it is necessary to return the table to query the index filtered data, which is judged at the Mysql service layer Whether the data meets the query conditions.

Mysql's optimizer will first estimate the cost of indexing with a high matching degree when executing a sql statement. If the cost of indexing is greater than searching the entire table, then Mysql will choose a full table scan. This conclusion may be counter-intuitive. In our impression, indexes are used to improve query efficiency. There are mainly two factors involved here:

• When the query condition or the field being searched is not on the field of the secondary index, the table return operation will be performed, which will be: secondary index primary key index.

• The performance of disk random I/O is lower than sequential I/O. Table return queries are random I/O on the primary key index, and full table scans are sequential I/O on the primary key index.

#Do experiments to analyze whether the cost of table return operations is the direct cause of index failure?

Remove the status='0' query condition and explain to see if the query uses the index idx_date_station_driver. The result is as shown in the figure below. The overhead of the table return operation is reduced, and the index does not become invalid.

Summary

Combined with the above analysis, the reason for the index failure is summarized: the range query causes the joint index to stop matching, and the index matches the filtered data Not enough, causing the MySQL optimizer to estimate that the table return operation cost of Table Filter is greater than that of the full table query, so the full table query was chosen. The range query causing the joint index to stop matching is the culprit of index failure, and the cost of table return operations is the direct cause of index failure.

Optimize Index

The culprit of the slow query index failure is that the range query causes the joint index to stop matching. You only need to adjust the fields of the range query to the fields of the precise query. Later, the

joint indexidx_date_station_driver(start_date, station_id, driver_id) will be modified to idx_station_date_driver(station_id, start_date, driver_id). The optimized results are shown in the figure below.

Expansion

Common scenarios of index failure

• Violates the leftmost prefix matching principle. For example, there is an index index(a,b), but the query condition only has the b field.

• Perform any operation on the index column, including calculations, functions, type conversions, etc.

• Range queries cause the union index to stop matching.

• Reduce the use of select*. To avoid unnecessary table return operation overhead, try to use covering indexes.

• Use not equal to (!=, ) and use the or operation.

• The string index without single quotes is invalid.

• like starts with wildcard '�c'. Note that like ‘abc%’ can be indexed.

• order by violates the leftmost matching principle and includes non-index field sorting, which will result in file sorting.

• group by violates the leftmost matching principle and contains non-index field grouping, which will result in the generation of a temporary table.

Explain analysis

The analysis of slow query is inseparable from the mysql explain statement. explain mainly focuses on two fields: Type and Extra.

Type represents the way to access data, and Extra represents the way to filter and organize data. Listed here for easy search.

##ExtraALLFull table scanUsing indexUse covering index, no need to return the table, no need for Mysql service layer filteringindexFull scan of the index treeUsing whereGet data from the storage engine layer, and use where query conditions in the Mysql service layer Filter data. #rangeIndex tree range scanUsing where; Using indexIndex range scan. Index scans are similar to full table scans, but they occur at different levels. refNon-unique index scan, such as non-unique index and non-unique prefix of unique indexUsing index conditionUse index pushdown to make full use of query index fields to filter data at the storage engine layereq_refUnique index scan, such as unique index and primary key indexUsing temporaryTemporary table stores results, used for sorting and grouping queriesconstConvert queries into constantsUsing filesortFile sorting, used for sortingNULLNo need to access tables or indexesNULLReply to the table

Type

For more programming-related knowledge, please visit:

Introduction to Programming! !

The above is the detailed content of What should I do if the Mysql index fails? Brief analysis of failure reasons. For more information, please follow other related articles on the PHP Chinese website!