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

This article mainly explains "what is the implementation principle of order by in MySQL". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what is the implementation principle of order by in MySQL"?

Full-field sorting

MySQL allocates a piece of memory to each thread for sorting, called sort_buffer.

The execution process of the statement:

(1) initialize sort_buffer and determine the fields to be put into select

(2) find a row that meets the conditions, take out the fields of select and store them in sort_buffer.

(3) keep searching until the conditions are not met

(4) sort the data in sort_buffer according to the sorted fields

Sort_buffer_size can set the size of the sort_buffer. If the amount of data to be sorted is less than sort_buffer_size, the sort is done in memory. However, if the amount of sorting data is too large to be stored in it, temporary disk files have to be used to assist sorting.

Rowid sorting

Max_length_for_sort_data is a parameter in MySQL that specifically controls the length of row data used for sorting. What it means is that if the length of a single line exceeds this value, MySQL thinks the single line is too large and needs a different algorithm.

Statement execution process:

(1) initialize sort_buffer to determine the fields to be placed in id and to be sorted

(2) find a row that meets the conditions, take out these two fields and store them in sort_buffer

(3) keep searching until the conditions are not met

(4) sort the data in sort_buffer according to the sorted fields

(5) iterate through the sorting results and retrieve the fields of select according to the value table of id

If MySQL thinks the memory is large enough, it will use full-field sorting, otherwise it will use rowid sorting. For InnoDB tables, rowid sorting will require that returning to the table will cause a large number of random reads to the disk, so it will not be preferred.

At this point, I believe you have a deeper understanding of "what is the principle of the implementation of order by in MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.