Detailed explanation of Mysql's method of optimizing order by statement

In this article, we will learn about the optimization of the ORDER BY statement. Before that, you need to have a basic understanding of indexes. If you don’t understand, you can first read the index-related articles I wrote before. Now let’s get started.

Two sorting methods in MySQL

1. Directly return ordered data through ordered index sequential scanning

Because the structure of the index is a B+ tree, the data in the index is arranged in a certain order, so if the index can be used in sorting queries, additional sorting operations can be avoided. When EXPLAIN analyzes the query, Extra is displayed as Using index.

2. Filesort sorts the returned data

All operations that do not directly return sorted results through the index are Filesort sorts, which means that additional sorting operations are performed. When EXPLAIN analyzes the query, Extra is displayed as Using filesort.

Core principles of ORDER BY optimization

Minimize additional sorting and return ordered data directly through the index.

ORDER BY optimization practice

The index of the customer table used for the experiment is as follows:

First of all, please note:

MySQL can only use one index for one query. If you want to use indexes on multiple fields, create a composite index.

ORDER BY optimization

1. The queried fields should only include the index fields and primary keys used in this query. The remaining non-index fields and index fields will not use indexes as query fields.

To query only the index fields used for sorting, you can use the index sorting:

explain select store_id,email from customer order by store_id,email;

However, please note that if the sorting field is in multiple indexes, index sorting cannot be used, and only one index can be used for querying at a time:

explain select store_id,email,last_name from customer order by store_id,email,last_name;

Only query the index fields and primary keys used for sorting, and you can use index sorting:

Voice-over: MySQL's default InnoDB engine physically uses a clustered index to search by primary key, so the InnoDB engine requires that the table must have a primary key. Even if the primary key is not explicitly specified, the InnoDB engine will generate a unique implicit primary key, which means that there must be a primary key in the index.

explain select customer_id,store_id,email from customer order by store_id,email;

Querying fields other than the index fields and primary key fields used for sorting will not utilize the index sorting:

explain select store_id,email,last_name from customer order by store_id,email;

explain select * from customer order by store_id,email;

WHERE + ORDER BY optimization

1. The sorting field is in multiple indexes and cannot be sorted using the index

The sorting fields are in multiple indexes (not in the same index), and index sorting cannot be used:

explain select * from customer where last_name='swj' order by last_name,store_id;

Voiceover: When the sorting fields are not in the same index, it is not possible to complete the sorting in a B+ tree, and an additional sorting must be performed.

The sorting field is in an index, and the WHERE condition and ORDER BY use the same index, you can use the index sorting:

explain select * from customer where last_name='swj' order by last_name;

Of course, composite indexes can also use index sorting:

Note that the fields store_id and email are in a composite index.

explain select * from customer where store_id = 5 order by store_id,email;

2. The sorting field order is inconsistent with the index column order, and the index sorting cannot be used

Voice-over: This is for composite indexes. We all know that when using composite indexes, it is necessary to follow the left-most principle. The WHERE clause must have the first column in the index. Although the ORDER BY clause does not have this requirement, it also requires that the sorting field order match the composite index column order. When we usually use composite indexes, we must develop a good habit of writing in the order of composite index columns.

The sorting field order is inconsistent with the index column order, and the index sorting cannot be used:

explain select * from customer where store_id > 5 order by email,store_id;

You should ensure that the sorting field order is consistent with the index column order, so that you can take advantage of the index sorting:

explain select * from customer where store_id > 5 order by store_id,email;

The ORDER BY clause does not require the first column in the index, and the index can still be used for sorting without it. But there is a prerequisite, it is only possible when filtering with equal values, not when querying with ranges:

explain select * from customer where store_id = 5 order by email;

explain select * from customer where store_id > 5 order by email;

Voiceover:

The reason is actually very simple. When performing a range query, the first column a is definitely sorted (ascending by default), but the second field b is not actually sorted. But if field a has the same value, then field b is sorted. Therefore, if it is a range query, only one additional sorting can be performed on b.

3. The ascending and descending order is inconsistent, and the index cannot be used for sorting

ORDER BY sorting fields must either be sorted in ascending order or in descending order; otherwise, index sorting cannot be used.

explain select * from customer where store_id > 5 order by store_id,email;

explain select * from customer where store_id > 5 order by store_id desc,email desc;

explain select * from customer where store_id > 5 order by store_id desc,email asc;

Summarize:

The above optimization can actually be summarized as follows: the WHERE condition and ORDER BY use the same index, the ORDER BY order is the same as the index order, and the ORDER BY fields are in ascending or descending order. Otherwise, additional sorting operations will definitely be required, and Filesort will appear.

Filesort Optimization

The occurrence of Filesort can be reduced by creating appropriate indexes, but in some cases, Filesort cannot be completely eliminated. In this case, the only way is to find ways to speed up the operation of Filesort.

Two sorting algorithms for Filesort:

1. Two-scan algorithm

First, the sorting fields and row pointer information are retrieved according to the conditions, and then sorted in the sort buffer. This sorting algorithm needs to access the data twice: the first time to obtain the sorting field and row pointer information, and the second time to obtain the record based on the row pointer. The second read operation may result in a large number of random I/O operations. The advantage is that the memory overhead is small when sorting.

2. One-Scan Algorithm

All fields of the rows that meet the conditions are retrieved at one time, and then the result set is directly output after sorting in the sort buffer. The memory overhead is relatively large when sorting, but the sorting efficiency is higher than the two-scan algorithm.

According to the characteristics of the two sorting algorithms, appropriately increasing the value of the system variable max_length_for_sort_data can allow MySQL to select a more optimized Filesort sorting algorithm. And when writing SQL statements, only use the required fields instead of SELECT * all fields. This can reduce the use of the sorting area and improve SQL performance.

Summarize

The above is a detailed explanation of the method of optimizing the Mysql order by statement introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!