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

What is the principle of sorting in MySQL? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

In programming, we use the group by keyword in many scenarios. For example, when reading data in paging, in order to avoid repeatedly scanning records, it is necessary to use group by.

For example, we use the following DDL to create a table:

CREATE TABLE `city` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'key ID', `city` varchar (16) NOT NULL COMMENT' city', `name` varchar (16) NOT NULL COMMENT 'name', `age`int (11) NOT NULL COMMENT 'age', `addr`varchar (128i) DEFAULT NULL COMMENT 'address', PRIMARY KEY (`id`), KEY `city` (`city`) ENGINE=InnoDB DEFAULT CHARSET=utf8

And we will execute the following query statement

SELECT city, `name`, age FROM user_info WHERE city=' Shanghai 'ORDER BY `name` LIMIT 1000; sort all fields

Because the above table-building statement has already created an index on the city field, when we use the EXPLAIN command, we will get the following result:

The "Using filesort" in the Extra field above indicates that sorting is needed, and MySQL allocates a piece of memory for each thread for sorting, which is called sort_buffer. Let's take a look at the structure diagram of index (city).

The execution process is as follows:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Initialize sort_buffer and make sure to put in the three fields of city name age

Obtain * city=' Shanghai 'records from the city index, that is, id_x

Get the corresponding record from the primary key index, and put the value of name city age into sort_buffer

Take down a record that meets the conditions and repeat the operation of 3 / 4 until the conditions are not met.

Quickly sort the data in sort_buffer according to name

Take out the first 1000 pieces of data and return them.

For the time being, we call this sorting process "full-field sorting", as follows:

Sort by name in the figure may be in memory or sort using disk files, depending on the memory and sort_buffer_size required for sorting. Sort_buffer_size is the memory size opened up by MySQL for sorting. When the memory required is less than sort_buffer_size, the sorting is completed directly in memory. If the memory required is larger than sort_buffer_size, extra disk space is needed to assist sorting.

Rowid sorting

The above algorithm may have some problems when the amount of data is relatively large. Because when sorting, all the return fields are stored, which increases the pressure on the sorting space (sort_buffer).

SET max_length_for_sort_data=16

Max_length_for_sort_data is a parameter that MySQL restricts the size of sorted rows. This means that if the size of the sort row exceeds this value, another sorting algorithm will be selected. The size of the above three name city age fields is 36, which is greater than 16. In the new algorithm, only name (sort field) and id will participate in the sorting in sort_buffer. The process is as follows

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Initialize sort_buffer and make sure to put in the two fields of name id

Obtain * city=' Shanghai 'records from the city index, that is, id_x

Get the corresponding record from the primary key index, and put the value of name id into sort_buffer

Take down a record that meets the conditions and repeat the operation of 3 / 4 until the conditions are not met.

Quickly sort the data in sort_buffer according to name

Take out the first 1000 pieces of data, then take out the name city age 3 fields of the corresponding record according to id and return the result.

This sort process, which we call rowid sorting, is as follows:

Full field sort VS rowid sort

Judging from the above two processes, if there is enough memory, MySQL will store all the fields in the returned value in the sort space. When MySQL memory is too small, rowid sorting will be considered. However, judging from the above process, the rowid sort will return the table again before returning the result. Therefore, MySQL believes that when there is enough memory, full-field sorting will be preferred.

The above scenario is that after the city field is filtered, the name field is not ordered. In fact, we can avoid the sorting of name fields through federated indexes.

Alter table user_info add index idx_city_user (city, name)

Let's take a look at the diagram of the federated index:

As you can see from the flow chart above, when we take out the record of city=' Shanghai', the fields of name are also in order. The process is as follows

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Get * city=' Shanghai 'records id_x from the (city, name) index

Get the corresponding record in the primary key index and return the value of name city age directly as part of the result set

Take down a record that meets the condition and repeat the operation of 2 / 3 until it does not meet the condition or reaches 1000

From the point of view of the federated index, we don't have to sort, so can we return the results directly through the index? That is, do not return to the table operation. The answer is yes, and that is to overwrite the index.

Alter table user_info add index idx_city_user_age (city, name, age)

When the query statement is executed, not only are the fields in the name ordered, but all the fields in the result set are already included in the index, as follows:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Get * city=' Shanghai 'records from the (city, name,age) index, and directly return the value of name city age as part of the result set.

Take down a qualified record and repeat the operation of 1 / 2 until it does not meet the condition or reaches 1000.

This is the answer to the question about the principle of sorting in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about 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.