mysql sorting difference

Let’s talk about the MySQL sorting mode in detail from the shallower to the deeper, how it affects MySQL’s selection of different sorting modes and how to optimize sorting.

Recommended courses: MySQL Tutorial.

# Sorting is a basic function in databases, and MySQL is no exception.

Users can sort the specified result set through the Order by statement. In fact, not only the Order by statement, but also the Group by statement and the Distinct statement will implicitly use sorting. This article will first briefly introduce how SQL uses indexes to avoid sorting costs, and then introduce the internal principles of MySQL to implement sorting.

Solution to everyone’s following questions:

Where does MySQL use sorting, and how to judge that MySQL uses sorting;

MySQL has several sorting modes, and by what method? MySQL chooses different sorting modes;

What is the relationship between MySQL sorting and read_rnd_buffer_size, and under what circumstances can increasing read_rnd_buffer_size optimize sorting;

How to judge whether MySQL uses disk for sorting, and how to avoid or optimize it Disk sorting;

How variable-length field (varchar) data is stored in memory during sorting, and what improvements are there in 5.7;

In this case, what improvements are there in the sorting mode;

What exactly is sort_merge_pass? What problem does the status value indicate if it is too large? How can it be solved?

If MySQL uses sorting, what methods can be used to analyze and optimize it to make the sorting faster?

2. Sorting

When we view the MySQL execution plan through explain, we often see Using filesort displayed in the Extra column.

For SQL that cannot use indexes to avoid sorting, the database has to implement the sorting function itself to meet user needs. At this time, "Using filesort" will appear in the SQL execution plan. It should be noted here that filesort does not mean This is file sorting. In fact, it may also be memory sorting. This is mainly determined by the sort_buffer_size parameter and the result set size.

In fact, this situation shows that MySQL uses sorting. Using filesort often appears in order by, group by, distinct, join, etc.

There are three main ways to implement sorting internally in MySQL, regular sorting, optimized sorting and priority queue sorting.

CREATE TABLE t1(id int, col1 varchar(64), col2 varchar(64), col3 varchar(64), PRIMARY KEY(id),key(col1,col2));
SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;

Please look at the differences between these three sorts:

a. Conventional sorting

(1) .Get records that meet the WHERE condition from table t1

(2). For each record, take out the primary key sort key (id, col2) of the record and put it into the sort buffer

(3 ). If the sort buffer can store all (id, col2) pairs that meet the conditions, sort them; otherwise, when the sort buffer is full, sort them and solidify them into a temporary file. (The sorting algorithm uses the quick sort algorithm)

(4). If temporary files are generated during sorting, you need to use the merge sort algorithm to ensure that the records in the temporary files are in order

( 5). Execute the above process in a loop until all records that meet the conditions are involved in sorting

(6). Scan the sorted (id, col2) pairs, and use the id to get the columns that need to be returned by SELECT ( col1,col2,col3)

(7). Return the obtained result set to the user.

From the above process, whether to use file sorting mainly depends on whether the sort buffer can accommodate the (id, col2) pair that needs to be sorted. The size of this buffer is controlled by the sort_buffer_size parameter. In addition, a sorting requires two IOs, one is to retrieve (id, col2), and the second is to retrieve (col1, col2, col3). Since the returned result set is sorted by col2, the ids are out of order. A large amount of random IO will be generated when fishing for (col1, col2, col3) with the id. An optimization for the second MySQL itself is to sort the IDs before retrieving them and put them into a buffer. The size of this buffer is controlled by the parameter read_rnd_buffer_size, and then retrieve records in an orderly manner, converting random IO into sequential IO.

b. Optimized sorting

The conventional sorting method requires two additional IOs in addition to the sorting itself. Compared with conventional sorting, the optimized sorting method reduces the second IO. The main difference is that the sort buffer is not (id, col2), but (col1, col2, col3). Since the sort buffer contains all the fields required for the query, it can be returned directly after the sorting is completed without the need to retrieve the data again. The cost of this method is that the number of (col1, col2, col3) that can be stored in a sort buffer of the same size is smaller than (id, col2). If the sort buffer is not large enough, temporary files may need to be written, causing additional IO. Of course, MySQL provides the parameter max_length_for_sort_data. Only when the sorted tuple is smaller than max_length_for_sort_data, the optimized sorting method can be used. Otherwise, the conventional sorting method can only be used.

c. Priority queue sorting

In order to get the final sorting result, no matter what, we need to sort all the records that meet the conditions before returning. So compared to optimizing the sorting method, is there still room for optimization? Version 5.6 has optimized the Order by limit M, N statement at the spatial level and added a new sorting method - priority queue, which is implemented using heap sorting. The characteristics of the heap sort algorithm can just solve the sorting problem of limit M, N. Although all elements are still required to participate in the sorting, only M N tuples of sort buffer space are needed. For scenarios where M and N are small, it is basically not necessary. This may cause the problem of requiring temporary files for merge sorting due to insufficient sort buffer. For ascending order, a large top heap is used, and the elements in the final heap form the smallest N elements. For descending order, a small top heap is used, and the elements in the final heap form the largest N elements.

The above is the detailed content of mysql sorting difference. For more information, please follow other related articles on the PHP Chinese website!