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

This article introduces how to optimize the order by sentence in Mysql, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Two sorting methods in MySQL

1. Return ordered data directly by sequential scanning of ordered index

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

2.Filesort sort, sort the returned data

All operations that do not return sort results directly through the index are Filesort sorting, that is, additional sorting operations are performed. When EXPLAIN parses the query, the Extra is displayed as Using filesort.

Core principles of ORDER BY Optimization

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

ORDER BY optimization practice

The index of the customer table used for the experiment:

First of all, we should pay attention to:

MySQL can only use one index at a time, and if you want to use an index on multiple fields, create a composite index.

ORDER BY optimization

1. The fields of the query should contain only the index fields and primary keys used in this query, and the rest of the non-index fields and index fields will not be used as query fields.

Query only the index fields used for sorting, and you can use the index to sort:

Explain select store_id,email from customer order by store_id,email

Note, however, that the sort field is in multiple indexes and cannot be sorted using the index, and the query can only use one index at a time:

Explain select store_id,email,last_name from customer order by store_id,email,last_name

Query only the index fields and primary keys used for sorting, and you can use the index to sort:

Voiceover: MySQL's default InnoDB engine physically uses a clustered index to search by the 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, that is, there must be a primary key in the index.

Explain select customer_id,store_id,email from customer order by store_id,email

Queries for fields other than index fields and primary keys that are used for sorting do not use 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 sort field is in more than one index and cannot be sorted by index

The sort field is in multiple indexes (not in the same index) and cannot be sorted by index:

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

Voiceover: when the sort field is not in the same index, it is not enough to complete the sort in a B+ tree, so an additional sort must be performed.

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

Explain select * from customer where last_name='swj' order by last_name

Of course, composite indexes can also use index sorting:

Note that the field store_id,email is in a composite index

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

two。 The order of sort fields is not consistent with the order of index columns, and cannot be sorted by index

Voiceover: this is for a combined index. We all know that using a combined index must follow the leftmost principle. The WHERE clause must have the first column in the index. Although the ORDER BY clause does not require this, it also requires that the order of the sorted fields match the order of the combined index columns. When using combinatorial indexes, we must form the good habit of writing in the order of combinatorial index columns.

The order of the sort fields is not consistent with the order of the index columns, and cannot be sorted by index:

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

You should ensure that the order of the sort fields is the same as the order of the index columns, so that you can sort with the index:

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 no index sorting is still available. However, there is a prerequisite, which can only be used when equivalent filtering, but not when querying a range:

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 also very simple: when querying a range, the first column a must be sorted (the default is ascending order), while the second field b is not sorted. But if the a field has the same value, the b field is sorted. So if it's a range query, you can only do an extra sort of b.

3. The ascending and descending order is inconsistent and cannot be sorted by index.

The ORDER BY sort field sorts either in positive order or in reverse order, or it cannot be sorted by index.

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

Summary:

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

Filesort optimization

You can reduce the occurrence of Filesort by creating appropriate indexes, but in some cases, you can't make Filesort disappear completely, so you have to find a way to speed up the operation of Filesort.

Two sorting algorithms for Filesort:

1. Double scan algorithm

First, the sort field and row pointer information are taken out according to the conditions, and then sorted in the sort area sort buffer. This sorting algorithm needs to access the data twice, the first time to get the sort field and row pointer information, the second time to get the record according to the row pointer, and the second read operation may lead to a large number of random Imax O operations. The advantage is that there is less memory overhead when sorting.

two。 Single scan algorithm

All the fields of the rows that meet the criteria are fetched at once, and then sorted in the sort area sort buffer, the result set is output directly. When sorting, the memory cost is relatively high, but the sorting efficiency is higher than the two-scan algorithm.

According to the characteristics of the two sorting algorithms, properly increasing the value of the system variable max_length_for_sort_data can make MySQL choose a more optimized Filesort sorting algorithm. And when writing SQL statements, use only the fields you need, rather than all the fields of SELECT *, which can reduce the use of the sort area and improve SQL performance.

On how to optimize the order by statement in Mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.