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

This article mainly introduces several filesort algorithms in MySQL, which can be used for reference by interested friends. I hope you can learn a lot after reading this article.

I. introduction of filesort algorithm

MySQL has two filesort algorithms: two-pass and single-pass.

(1) two-pass

This algorithm is used when the sum of column lengths exceeds max_length_for_sort_data bytes. The principle is that rows are read according to where filter criteria, and the sort fields and row pointers of each row are stored in the sort buffer (sort buffer). If the sort buffer size is not enough, run a quick sort operation in memory to store the sort results in a temporary file

Point to the sorted block with a pointer, and then continue to read the data until all rows are read. This is the first time to read the record. When the records are read for the first time, they can be sorted by index or scanned by table, and can be read sequentially. But the second time the record is read, although the sort fields are ordered, the row pointers stored in the row cache are ordered

However, the physical records pointed to need to be read randomly, so this algorithm may bring a lot of random reads, resulting in low efficiency.

The advantage is that the amount of sorted data is small and can be completed in memory.

The disadvantage is that the record needs to be read twice, and a lot of random IO may be generated during the second read, which may be costly.

(2) single-pass

MySQL generally uses this algorithm. The principle is to read all the fields involved in SQL into the sort buffer (sort buffer) according to the filter conditions, and then sort them according to the sort fields.

If the sort buffer is not enough, the temporary sort result is written to a temporary file, and finally the temporary sort file is merged to return the sorted result set directly.

The advantage is that there is no need to read the record twice, and compared with two-pass, it can reduce the cost of Icano.

The disadvantage is that because all fields are to be read, the sort buffer may not be enough, and additional temporary files are needed to assist in sorting, resulting in additional Ibig O costs.

two。 Parameter setting and optimization related to sorting

The relevant parameters are as follows:

Max_length_for_sort_data: if the sum of the column lengths (including selecting columns, sorting) exceeds max_length_for_sort_data bytes, then the two-pass algorithm is used.

If you sort the BLOB and Text fields and also use the two-pass algorithm, setting this value too high will cause the system I max_length_for_sort_data O to rise and CPU to decrease, so it is recommended that you do not set max_length_for_sort_data

Is set too high.

Max_sort_length: if you sort the BLOB and TEXT fields, only the first max_sort_length bytes are sorted.

The optimization directions that can be considered are as follows:

(1)。 Increase sort_buffer_size

In general, you can use the default single-pass algorithm. You can consider increasing the sort_buffer_size to reduce the disk Imando O.

It is important to note that the sum of the field lengths does not exceed max_length_for_sort_data, only query the required columns, and pay attention to the length and type of the columns. MySQL currently reads and calculates the length of the column according to the maximum degree defined, so when designing the table structure, do not set the field of type varchar too large, although for the type varchar, it is actually on physical disk.

The storage can be compact, but the maximum defined length is allocated when sorting, and sometimes the temporary files produced by the sorting phase are even larger than the original table. MySQL version 5. 7 makes some optimizations in this respect.

(2)。 For the two-pass algorithm, consider increasing the read_rnd_buffer_size, but since this global variable takes effect on all connections, it is recommended that you only set it at the session level to speed up some special large operations.

(3)。 At the operating system level, optimize the reading and writing of temporary files.

Thank you for reading this article carefully. I hope the article "several filesort algorithms in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.