❝

Life is too short to have a dog

❞

The background,

MySQL can be said to be a relatively easy to use but also error prone database language. When you confidently type the next line of SQL to make a heroic turn without looking at the result, an unexpected execution result will hit your face with a “pia~ pia~” ring.

Some time ago, when I was writing a bug, I encountered a requirement: query a set of data in ascending order of weight, and the new data must appear at the end of the list by time. Seeing this requirement, the idle fish didn’t even think about it, he just wrote an order by, and then confidently looked back without taking a bug away. Two days later, the product told me that the execution results were not as expected. With the mentality of “you must be lying to me, watch me hit you in the face”, I execute SQL:

SELECT * FROM member_tool_config WHERE isv_id IS NULL and user_id IS NULL ORDER BY weight ASC;
Copy the code

Yi? The wind must be noisy today, affecting the results of SQL execution…… Forget it. Still look for bugs.

2, “order by” causes the disorder

After some investigation, the culprit was found to be Order BY. [1] Order by: order by: order by: order by

❝

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.

❞

Notice the expression is free to return those rows in any order. When there are multiple rows with the same value, MySQL will return the result set “in any order”. Of course, it will not be so bold and bold. The official also said later that the final implementation situation may be different according to the implementation plan, which means that the final result is unstable.

Third, how to solve

Since the official document also says that the execution results are largely affected by the execution plan, it means that when using order BY, we need to clarify the scope of the query and refine the query conditions, so that MySQL can better understand our requirements during the execution. In the example above, in addition to the weight of use, you can add the creation time as one of the conditions for sorting. The SQL is as follows:

SELECT * FROM member_tool_config WHERE isv_id IS NULL and user_id IS NULL ORDER BY weight,gmt_create ASC;
Copy the code

Four,

Although the problem was solved and I checked the official document, I always felt that there was something missing. If which big guy has better explanation can communicate together. Finally, thanks to the product manager for letting the idle fish feel the “fun” of MySQL while writing bugs.

Reference

[1]

LIMIT the Query Optimization: https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

This article was typeset using MDNICE