mysql--Optimizing ORDER BY statements

In this article we will look at the optimization of the order BY statement, before which you need to have a basic understanding of the index, and young men and young men who do not know it can start by looking at the index related articles I wrote earlier. Now let's get started.

Two ways to sort MySQL

1. direct return of ordered data via sequential index sequence scan

Because the structure of the index is a B + tree, the data in the index is arranged in a certain order, so if you can use the index in a sorted query, you can avoid additional sorting operations. When explain parses a query, the extra is displayed as a using index.

2.Filesort Sort, sort the returned data

All operations that do not return the sort results directly through the index are filesort sorted, which means that additional sorting is performed. Explain when you parse a query, extra is displayed as a using Filesort.

Core principles of ORDER by optimization

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

ORDER by Optimization Combat

Index of the Customer table for the experiment:

The first thing to note:

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

ORDER by optimization

1. The queried field should contain only the index fields and primary keys used by this query, and the remaining non-indexed fields and indexed fields will not use the index as query fields.

Query only the indexed fields used for sorting, which can be sorted by index:

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

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

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

Querying only the index fields and primary keys used for sorting can be sorted by index:

Voice-over: MySQL default InnoDB engine uses a clustered index in this way, search by primary key, so the InnoDB engine requires 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, the index must have a primary key.

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

Queries for fields other than the index field and primary key used for sorting are not sorted by index:

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. sort fields in multiple indexes, cannot be sorted by index

Sort fields cannot be sorted by index in multiple indexes (not in the same index):

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

VoiceOver: When sorting fields are not in the same index, sorting in a B + tree cannot be satisfied, additional sorting must be done

The sort field is in an index, and the Where condition and order by use the same index , which can be sorted by index:

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

Of course the combined index can also be sorted by index:

Note the field Store_id,email in a combined index

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

2. The sort field order is inconsistent with the index column order and cannot be sorted by index

VoiceOver: This is for composite indexes, and we all know that using composite indexes is necessary to follow the leftmost principle , where clauses must have the first column in the index, although the ORDER BY clause does not have this requirement, but also requires that the sort field order and the combined Index column order match. We usually use the combination index, we must form a combination of indexed column in the order of the good habit of writing.

The sort field order is inconsistent with the index column order and cannot be sorted by index:

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

You should make sure that the sort field order is consistent with the index column order, which can be sorted by index:

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

The ORDER BY clause does not require that the first column in the index must be indexed, and the index cannot still be sorted. However, there is a precondition, only when the equivalent filter can be, the scope of the query can not :

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

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

Narrator:

The reason is also very simple, when the scope of the query, the first column A must be sorted (the default is ascending), and the second field B is not a sort of. But if the a field has the same value, then the B field is sorted. So if it's a range query, you can only do an extra sort of B.

3. the order is inconsistent and cannot be sorted by index

The order by sort field is either fully ordered or sorted in reverse order or 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;

Summarize:

The above optimizations can be summarized as follows:where conditions and order by use the same index, and the order by is the same in sequence and index order, and the order by field is ascending or descending . Otherwise, an additional sort operation is required, and a filesort will appear.

Filesort optimization

By creating an appropriate index to reduce the appearance of filesort, but in some cases, can not completely let filesort disappear, at this time can only find ways to speed up filesort operation.

Two sorting algorithms for Filesort:

1. Two-scan algorithm

The sort field and row pointer information are first removed according to the criteria, and then sorted in sort buffer in the sorting area. This sort algorithm requires access to two data, the first to get the sort field and row pointer information, the second to get records based on row pointers, and the second read operation may result in a large number of I/O operations. The advantage is that the memory overhead is low when sorting.

2. One-time scanning algorithm

Once all the fields of the row that meet the criteria are fetched, and then the result set is output directly after sorting in sort buffer in the sorting area. When sorting, the memory overhead is relatively large, but the sorting efficiency is higher than the two scan algorithm.

According to the characteristics of the two sorting algorithms, it is possible to increase the value of the system variable Max_length_for_sort_data , allowing MySQL to choose a more optimized filesort sorting algorithm. And when writing SQL statements, use only the fields you want, not all of the select * Fields , which can reduce the use of the sorting area and improve SQL performance.

Reference

"In layman's MySQL"

Recommended Reading
mysql--analysis of SQL execution plans through explain
mysql--Index Base
mysql--Index Optimization Combat
Data structure behind the database index
What is the impact of database index selection?

Coderfocus
Public Number:


Source: http://songwenjie.cnblogs.com/
Statement: This article for Bo Master Learning sentiment Summary, the level is limited, if improper, welcome correction. If you think it's good, just click on the "recommend" button below, thanks for your support. Please specify the source with reference.