Shulou(Shulou.com)06/01 Report--

This article will explain in detail what is the difference between mysql sorting. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

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

The user can achieve the purpose of sorting 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 begins with a brief introduction to how SQL uses indexes to avoid sorting costs, and then introduces MySQL's internals for sorting.

Solve the following questions:

Where MySQL uses sorting, how to determine MySQL uses sorting;

MySQL has several sort modes. What methods can MySQL use to select different sort modes?

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

How to determine MySQL uses disk to sort, how to avoid or optimize disk sorting;

Sort variable length field (varchar) data in memory is how to store, 5.7 what improvements;

What improvements have been made to the sorting mode in the case;

What sort_merge_pass is exactly, what problem does this state value indicate if it is too large, and what method can be used to solve it;

MySQL uses sorting, then what can be analyzed and optimized to make sorting faster?

II. Sorting

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

For SQL that cannot avoid sorting by using indexes, 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 that filesort does not mean 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 is a case of MySQL using sorting. Using filesort often occurs in order by, group by, distinct, join, etc.

MySQL internal implementation of sorting there are three main ways, regular sorting, optimization 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;

See the difference between these three categories:

a. General sorting

(1). Get records satisfying WHERE condition from table t1

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

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

(4). If temporary files are generated during sorting, merge sorting algorithm is needed to ensure that records in temporary files are orderly.

(5). Repeat the above process until all records satisfying the conditions participate in sorting.

(6). Scan the ordered (id,col2) pairs and use id to retrieve the columns (col1,col2,col3) that SELECT needs to return.

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

From the above flow, whether to use file sorting mainly depends on whether the sort buffer can accommodate the (id,col2) pair to be sorted. The size of this buffer is controlled by the sort_buffer_size parameter. In addition, a sort requires two IOs, one is to catch (id,col2), and the second is to catch (col1,col2,col3). Since the returned result set is sorted by col2, the id is out of order, and a large number of random IOs will be generated when catching (col1,col2,col3) through the out of order id. For the second MySQL itself, an optimization, that is, before fishing, first sort the id and put it into the buffer. The size of this buffer is controlled by the parameter read_rnd_buffer_size, and then search the records in an orderly manner, turning random IO into sequential IO.

b. Optimize sequencing

Conventional sorting requires two additional IOs in addition to the sort itself. The optimized sort reduces the number of second IOs compared to conventional sort. 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 by the query, it can be returned directly after sorting, without the need for secondary data retrieval. The cost of this approach is that the number of (col1,col2,col3) that can be stored in a sort buffer of the same size is less than (id,col2). If the sort buffer is not large enough, it may lead to the need to write temporary files, resulting in additional IO. Of course MySQL provides the parameter max_length_for_sort_data. Only when the sort tuple is less than max_length_for_sort_data can the optimized sort mode be used, otherwise only the conventional sort mode can be used.

c. Priority queue ordering

In order to get the final sorted result, we need to sort all the records that satisfy the condition before returning anyway. So is there room for optimization relative to the optimal sorting method? 5.6 Version for Order by limit M, N statement, optimized at the spatial level, adding a new sorting method-priority queue, this method is implemented by heap sorting. Heap sort algorithm features just can solve the problem of limit M, N such sort, although still need all elements to participate in sorting, but only need M+N tuple sort buffer space, for M, N is very small scene, basically will not cause temporary files to merge sorting problem because the sort buffer is not enough. For ascending order, the elements in the final heap make up the smallest N elements, and for descending order, the elements in the final heap make up the largest N elements.

What is the difference between mysql sorting to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.