MySQL sorting feature details

Source of the article ： Learn through http://www.bdgxy.com/

1、 Problem scenario

A new transaction export function is launched , The logic is simple ： According to the query conditions , Export the corresponding data . Because of the large amount of data , Paging query is adopted when querying the database , Each query 1000 Data .

Self test normal , The test environment is normal , Derived from operational feedback after the launch Data has duplicate records .

Originally, I thought it was a business logic problem , again Review Read the code again , The cause of the problem is still not found . Finally, I had to SQL Statements are taken out and executed separately , Derived data , It turns out that SQL Statement query results are out of order .

2、 Cause analysis

Query statement with create_time Sort in reverse order , adopt limit paging , Under normal circumstances, there will be no problems . But when the business concurrency is large , Lead to create_time When there are a large number of the same values , Then based on the limit paging , There will be a disorder problem .

The scene is ： With create_time Sort , When create_time The same value exists , adopt limit Pagination , Cause paging data out of order .

such as , Inquire about 1000 Data , One of them create_time The recorded values are ”2021-10-28 12:12:12“, When these data are created at the same time , Part appears on the first page , A part appears on the second page , When querying the data on the second page , The data already checked on the first page may appear .

in other words , The data will bounce back and forth , It will appear on the first page , It will appear on the second page , This results in a partial duplication of the exported data , Part of it is missing .

Look at the Mysql 5.7 and 8.0 Official documents of , Described below ：

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

Overview of the above ： In the use of ORDER BY When sorting columns , If the corresponding （ORDER BY The column of ） There are multiple rows of the same data in the column ,（Mysql） The server will return these lines in any order , And may return in different ways according to the overall execution plan .

In a nutshell ：ORDER BY Query data , If ORDER BY There are multiple rows of the same data in the column ,Mysql Will return randomly . This will result in the use of sorting , But there will also be disorder .

3、 Solution

For the above problems , The basic solution is ： avoid ORDER BY Duplicate values for column . therefore , Other dimensions can be added , such as ID And so on .

select * from tb_order order by create_time ,id desc;

such , stay create_time Phase at the same time , Will be based on id Sort , and id It must be different , There will be no more of these problems .

4、 Expand knowledge

Actually , The above is in Mysql The official website of has made it clear , And there are examples . The following is a brief summary of the contents and examples of the official website .

4.1 limit Query optimization

If we only query part of a result set , Then don't query all the data , Then discard the unwanted data , But through limit Conditions to limit .

Not in use having When the conditions ,Mysql It may be true limit Condition optimization ：

• If you only query a few pieces of data , It is recommended to use limit, such Mysql The index may be used , And usually Mysql It's a full table scan ;
• If you will limit row_count and order by Use a combination of ,Mysql You'll find the first row_count Stop sorting immediately after the result set , Instead of sorting the entire result set . If the operation is based on the index at this time , It will be faster . If you have to sort files , Find row_count Before the result set , Some or all of the qualified results will be sorted . But when you find row_count After the results , The rest will not be sorted . One manifestation of this feature is that we mentioned earlier with limit And without limit When making a query , The order of results returned may be different .
• If you will limit row_count and distinct Use a combination of ,Mysql It will be found in row_count Stop immediately after the only row in the result set .
• In some cases , You can read the index in order （ Or sort the index ）, Then calculate the summary until the index changes to achieve group by. under these circumstances ,limit row_count No unnecessary... Will be calculated group by value .
• once MySQL The required number of rows were sent to the client , Will abort the query , Unless you use SQL_CALC_FOUND_ROWS. under these circumstances , have access to SELECT FOUND_ROWS() Search lines .
• LIMIT 0 Will quickly return an empty collection , It can usually be used to check SQL The effectiveness of the . It can also be used to get the type of result set in the application . stay Mysql The client , have access to --column-type-info To display the result column type .
• If you use temporary tables to parse queries ,Mysql Will use limit row_count To calculate how much space is needed .
• If order by Unused index , And there are limit Conditions , Then the optimizer may avoid using merge files , And use memory filesort Operation to sort rows in memory .
  

I understand limit Some characteristics of , Let's return to the focus of this article ,limit row_count and order by Combined with the use of features .

4.2 limit And order by Use a combination of

As mentioned in Article 2 above ,limit row_count and order by One of the characteristics of combined rendering is that the order in which the results are returned is uncertain . One factor that affects the implementation of the plan is limit, So with limit And without limit Execute the same query statement , The order in which the results are returned may be different .

In the following example , according to category Column to sort the query , and id and rating It's uncertain ：

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

When the query statement contains limit when , It may affect category Data with the same value ：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

among id by 3 and 4 The resulting position has changed .

In practice , Maintaining the order of query results is often very important , At this point, you need to introduce other columns to ensure the order of the results .

When the above example introduces id after , The query statement and results are as follows ：

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+
​
mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

It can be seen that , When added id Sort Columns , Even if category identical , There will be no disorder . This is consistent with our initial solution .

5、 Summary

Originally, through an accidental pit in practice , Talk about the Mysql Yes limit Optimization of query statements , At the same time, it provides a solution , That is, it meets the business needs , It also avoids the error of business logic .

A lot of friends are using order by and limit Statement to query , But if you don't know Mysql These optimization features , It's probably in the pit , It's just that the amount of data doesn't trigger rendering .

This is about Mysql This is the end of the article on the details of sorting features , More about Mysql Please search rookie tutorial for sorting feature content www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！