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

This article shows you how to solve the slow sorting speed in MySQL, the content is concise and easy to understand, and it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

I. specific phenomena

There is a function that displays a batch of list data in pages according to the weight values obtained by the algorithm, and the larger the weight value, the higher the weight value. The R & D students reported that the query speed was slow and the ranking was unstable.

The specific phenomenon of sorting instability, there are many records have the same weight value, a record (assuming id=100) appears on the first page, turn to the second page may also have it (which page is controlled by the limit).

Page 1 data

Page 2 data

A main table A, which connects two tables B and C on the left, and sorts them according to the weight field of C. The specific SQL is as follows

Second, problem analysis

Look at the execution plan (EXPLAIN) of the SQL statement and find the words Using filesort.

Quickly search the MySQL documentation. The first item is sorting optimization.

There is a sentence in the document that says, "if the index does not satisfy the ORDERBY clause, MySQL will perform a file sort (filesort) operation to read and sort the data rows. File sorting constitutes an additional sorting phase in query execution."

Obviously, using indexes to achieve ordering is more efficient than using filesort. Filesort is not always sorted by disk, and it is done in memory when the amount of data is small. The reason why the speed is not fast enough has been found.

Heap sequence or quick sort may appear in memory during filesort. Which sort method to use is determined by the optimizer. The basic principles are as follows

Quick sorting algorithm: mass sorting

Heap sorting algorithm: the amount of sorting is small.

Quick sort and heap sort are unstable sorting algorithms, and the order of duplicate values can not be guaranteed. The reason for the instability of Order by sorting is also located.

Learn how filesort works.

(1) read all records that meet the conditions according to the index of the table or the full table scan.

(2) for each row, a pair of values are stored in the buffer (sort sequence, row record pointer), one is the value of the sorted index column, that is, the column value used by order by, and the row pointer to the row data, the size of the buffer is the size of sort_buffer_size.

(3) when the buffer is full, run a quick sort (qsort) to sort the data in the buffer, store the sorted data to a temporary file, and save a pointer to the storage block, of course, if the buffer is not satisfied, the temporary file will not be rebuilt.

(4) repeat the above steps until all the lines have been read and the corresponding ordered temporary files are established.

(5) sort the block level, this is similar to the merge sorting algorithm, only through the pointers of two temporary files to constantly exchange data, and finally achieve the two files, are orderly.

(6) repeat 5 until all data are sorted.

(7) each row of data is read into memory by sequential reading (here, the data is not read in a row), and the data is fetched and transmitted to the client. The read cache size is specified by read_rnd_buffer_size.

Third, how to optimize

1. Use the index to achieve the purpose of sorting (optimization for examples)

For the example at the beginning of the article, the optimization principle is Use of Indexes to Satisfy ORDER BY (let ORDER BY use the index), which not only improves query efficiency, but also ensures stability (the order of index B+ leaf nodes is unique and certain).

MySQL's documentation lists a number of specific case, sorting out the main ones as follows.

There is a sentence in the MySQL document that says, "this query joins many tables, and not all the columns in the ORDER BY come from the first non-constant table used to retrieve rows." SQL that satisfies this type cannot make use of index sorting This is the example at the beginning of the article. In addition, using an alias may invalidate index sorting if it conflicts with the column name of the table.

See some articles written to the following statement ORDER BY can not use the index

This statement is clearly inconsistent with the official MySQL documentation. I think whether the index can be used in this statement or not has something to do with the phase in which the database engine decides whether to retrieve or not based on the cost.

2. Optimize filesort

If you really can't take advantage of the index, you can find a way to optimize filesort sorting.

If the result set is too large for memory, filesort will use temporary disk files as needed. Disk io speed you know! MySQL officially suggests that you can increase the sort cache parameter sort_buffer_size,MySQL 8.0 and optimize the cache utilization, which is not wasteful at all. Previous versions of MySQL can turn to DBA.

Typical SQL statements that can be optimized in this way are as follows

The above content is how to solve the slow sorting speed in MySQL. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.