How does MySQL sort by default when using the select statement without order by?

Hello everyone , I'm Fei Yun

Fear of the infinite truth , There is further joy in the near future

Record a MySQL Query sorting problem , One SQL Statement not added order by, Then, according to what rules are the query results sorted ？ Inquired some information on the Internet , Share below ：

•MyISAM surface

MySQL Select The default sort is displayed in physical storage order （ No additional sorting ）. in other words SELECT * FROM tbl – Will produce “ Table scan ”. If the table is not deleted 、 Replace 、 update operation , The records are displayed in the order of insertion .

•InnoDB surface

Same thing , Will be arranged in the order of the primary key , It needs to be emphasized again , It's just a hidden rule , In fact, it may not be completely reliable .

Reference resources :MySQL There are also hidden rules – Select Sentence without order by How to sort ？^[1]

---

If there is no definition  order by:

The returned data is not necessarily sorted by primary key , The results can be returned in any order - It may also change over time .

There is no... In the relational database “ Natural order ” Or something like that （ At least as far as I know ）. The only way to get an explicit sort is to specify a reliable sort  order by Clause , source when-no-order-by-is-specified-what-order-does-a-query-choose-for-your-record^[2].

For the same batch of data , At some point the order is the same , Over time , The data will change , So when making a query ,MySQL Will try to do it as fast as possible （MySQL The practical method is not necessarily fast ） Return the data .

Due to access to primary key 、 Indexing is faster in most cases （ stay Cache in ） Therefore, the returned data may be based on the primary key 、 The sequential output of the index , It's not really sorted here , This is mainly due to the primary key 、 The index itself is sorted into memory , So continuous output may be some kind of sequence . In some cases, the data that consumes the shortest seek time of the hard disk will be returned first . If you only query a single table , It is regular under special circumstances .

---

The following content analysis source ：what-is-the-default-order-of-records-for-a-select-statement-in-mysql^[3]

MySQL in SELECT What is the default recording order of statements ？

There is an answer with high recognition and praise ：

Read the answer roughly , Republish a previously answered SQL Server Type of question .

stay SQL In the world , Order is not an inherent property of a set of data . therefore , Unless you use order by Clause to query your data , Otherwise you can't get from RDBMS Ensure that your data will be returned in a specific order - Even return... In a consistent order .

Then answer your question ：

•MySQL Sort records as needed , But there is no guarantee of consistency • If you intend to rely on this order for any operation , Must be used order by Specify the order you want . Otherwise, doing anything else is preparing for an unpopular accident .

This is all. SQL Properties of , Not just MySQL.SQL-92 The relevant text in the specification is ：

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

If not specified <order by Clause >, be Q The order of rows depends on the underlying implementation .

---

The content of this article is the above contents , In summary : If not specified in use order by, So it basically depends on the underlying implementation , The specific sorting rules are uncertain , So the order of sorting is not fixed , May change over time .

In practice , If there is a query list to show the functions and requirements of data , Before development, we must first determine the rules of data sorting , This can avoid the problem of different sorting results of subsequent data queries .

If the MySQL SELECT The default recording order of statements is for those who know or have research partners , Welcome to leave a message to share your understanding .

If the article helps you , Please support me , Thank you for your generosity ～

References

[1] MySQL There are also hidden rules – Select Sentence without order by How to sort ？: https://www.cnblogs.com/lushilin/p/6094373.html
[2] when-no-order-by-is-specified-what-order-does-a-query-choose-for-your-record: https://stackoverflow.com/questions/20050341/when-no-order-by-is-specified-what-order-does-a-query-choose-for-your-record
[3] what-is-the-default-order-of-records-for-a-select-statement-in-mysql: https://dba.stackexchange.com/questions/6051/what-is-the-default-order-of-records-for-a-select-statement-in-mysql

Recommended reading

MySQL Database design specification , Worth reading

MySQL Database of the Internet commonly used sub database sub table scheme

【 A person seems to be thinking logically , But in fact, most of the time is just memory retrieval .】

See you next good day~