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

This article focuses on "what are the characteristics of Mysql sorting". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the characteristics of Mysql sorting"?

1. Problem scenario

A new online transaction export function, the logic is very simple: according to the query conditions, export the corresponding data. Because of the large amount of data, paging query is used when querying the database, with 1000 pieces of data at a time.

The self-test is normal, the test environment is normal, and there are duplicate records of the data exported by the operation feedback after the launch.

Originally thought that the business logic problem, re-Review the code, still can not find the cause of the problem. Finally, we have to take out the SQL statement and execute it separately to export the data. by comparison, it is found that it is caused by the disordered query results of the SQL statement.

2. Cause analysis.

Query statements are sorted in reverse order by create_time and paginated by limit, so there is no problem under normal circumstances. However, when the business concurrency is relatively large, resulting in a large number of the same value of create_time, and then paging based on limit, there will be the problem of disorder.

The scenario is: sort by create_time, when create_time has the same value, paging through limit, resulting in paging data out of order.

For example, if you query 1000 pieces of data, among which there is a batch of create_time records with values of "2021-10-28 12:12:12", when the data with the same creation time appears on the first page and partly on the second page, when querying the data on the second page, there may be data that has been checked on the first page.

In other words, the data bounces back and forth, one moment on the first page and the other on the second page, causing the exported data to be partially duplicated and partially missing.

Viewed the official documents of Mysql 5.7and 8.0, which are described as follows:

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.

Summary of the above: when sorting columns using ORDER BY, if there are multiple rows of the same data in the corresponding (ORDER BY's column) column, the (Mysql) server will return these rows in any order and may return them differently depending on the overall execution plan.

To put it simply: the data queried by ORDER BY, if there are multiple rows of the same data in the ORDER BY column, Mysql will return it randomly. This can lead to disorder even though sorting is used.

3. Solution

To solve the above problems, the basic solution is to avoid duplicating the values of the ORDER BY column. Therefore, you can add other dimensions, such as ID and other permutations.

Select * from tb_order order by create_time, id desc

In this way, when the create_time is the same, it will be sorted by id, but the id will definitely be different, and the above problem will not occur again.

4. Expand knowledge

In fact, the above content has been clearly stated on the official website of Mysql, and examples are also given. The following is a simple summary of the contents and examples of the official website.

4.1 limit query optimization

If we are only querying part of a result set, then instead of querying all the data and then discarding the unwanted data, we should restrict it by limit conditions.

When having conditions are not used, Mysql may optimize limit conditions:

If only a few pieces of data are queried, it is recommended to use limit, so that Mysql may use indexes, while Mysql is usually a full table scan

If you use limit row_count with order by, Mysql stops sorting as soon as the first row_count result set is found, instead of sorting the entire result set. If the operation is based on the index at this time, it will be faster. If file sorting is necessary, some or all eligible results are sorted before the row_count result set is found. However, when the row_count result is found, the rest will not be sorted. One manifestation of this feature is that the order of results returned may be different when querying with and without limit as we mentioned earlier.

If you use limit row_count with distinct, Mysql stops as soon as it finds the unique row of the row_count result set.

In some cases, group by can be achieved by reading the index sequentially (or sorting the index) and then calculating the summary until the index changes. In this case, limit row_count does not calculate any unnecessary group by values.

Once MySQL sends the required number of rows to the client, the query is aborted unless SQL_CALC_FOUND_ROWS is used. In this case, you can use SELECT FOUND_ROWS () to retrieve the number of rows.

LIMIT 0 quickly returns an empty collection, which can usually be used to check the validity of SQL. It can also be used to obtain the type of result set in the application. In the Mysql client, you can use-- column-type-info to display the result column type.

If you use a temporary table to parse the query, Mysql uses limit row_count to calculate how much space is needed.

If order by does not use indexes and limit conditions exist, the optimizer may avoid using merge files and use in-memory filesort operations to sort rows in memory.

Now that you've learned about some of the features of limit, let's go back to the focus of this article, using limit row_count in conjunction with order by.

4.2 limit in conjunction with order by

As mentioned in the second above, one of the features of the combination of limit row_count and order by is that the order in which the results are returned is uncertain. One factor that affects the execution plan is limit, so if you execute the same query with and without limit, the order in which the results are returned may be different.

In the following example, the query is sorted by the category column, while id and rating are indeterminate:

Mysql > SELECT * FROM ratings ORDER BY category + 3 | 5.0 | | 7 | 3 | 2.7 | +-+

When a query statement contains limit, it may affect data with the same threshold value:

Mysql > SELECT * FROM ratings ORDER BY category LIMIT 5 + -. -+

The position of the result with id of 3 and 4 changed.

In practice, it is often very important to maintain the order of query results, so it is necessary to introduce other columns to ensure the order of results.

When id is introduced into the above example, the query statement and result are as follows:

Mysql > SELECT * FROM ratings ORDER BY category, id + | 3 | 5.0 | | 7 | 3 | 2.7 | +-+ mysql > SELECT * FROM ratings ORDER BY category Id LIMIT 5 + -. -+

As you can see, when the sort of id column is added, even if the category is the same, there is no disorder problem. This is in line with our original solution.

At this point, I believe you have a deeper understanding of "what are the characteristics of Mysql sorting". 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.