MySql默认排序机制

1. 使用mysql查询时，不加ORDER BY是否能实现按顺序返回

不能。

详细解释：

MySQL :: Re: What is The Default Sort Order of SELECTS with no ORDER BY Clause?

* Do not depend on order when ORDER BY is missing.
* Always specify ORDER BY if you want a particular order -- in some situations the engine can eliminate the ORDER BY because of how it does some other step.
* GROUP BY forces ORDER BY. (This is a violation of the standard. It can be avoided by using ORDER BY NULL.)

SELECT * FROM tbl -- this will do a "table scan". If the table has never had any DELETEs/REPLACEs/UPDATEs, the records will happen to be in the insertion order, hence what you observed.

If you had done the same statement with an InnoDB table, they would have been delivered in PRIMARY KEY order, not INSERT order. Again, this is an artifact of the underlying implementation, not something to depend on.

简单翻译一下

1. 不用ORDER BY无法实现按顺序返回

2. 如果需要查询结果按一定顺序返回，就要使用ORDER BY

3. GROUP BY强制ORDER BY

机翻结果

*当order BY缺失时，不要依赖于order。
*总是指定ORDER BY，如果你想要一个特定的顺序——在某些情况下，引擎可以消除ORDER BY，因为它如何做一些其他步骤。
*按部队分组。这是违反标准的。它可以通过使用ORDER by NULL来避免。)

SELECT * FROM table——这将执行“表扫描”。如果表从未进行过任何delete / replace /UPDATEs操作，则记录恰好是按插入顺序排列的，因此会出现您所看到的情况。

如果您对一个InnoDB表执行相同的语句，它们将以PRIMARY KEY顺序传递，而不是INSERT顺序。同样，这是底层实现的工件，而不是需要依赖的东西。

2. 使用mysql查询时不加ORDER BY，会随机返回结果，但为什么结果看起来总是一样的

1. 查询结果的顺序由查询规划器决定构建结果集的方式决定，以相同方式进行查询很有可能得到类似的结果，因为每次都是按最优解去规划的

2. 数据变化、索引变化等其他综合因素都会导致查询结果变化

详细解释: 

sql server - The order of a SQL Select statement without Order By clause - Stack Overflow

No, that behavior cannot be relied on. The order is determined by the way the query planner has decided to build up the result set. simple queries like select * from foo_table are likely to be returned in the order they are stored on disk, which may be in primary key order or the order they were created, or some other random order. more complex queries, such as select * from foo where bar < 10 may instead be returned in order of a different column, based on an index read, or by the table order, for a table scan. even more elaborate queries, with multipe where conditions, group by clauses, unions, will be in whatever order the planner decides is most efficient to generate.

The order could even change between two identical queries just because of data that has changed between those queries. a "where" clause may be satisfied with an index scan in one query, but later inserts could make that condition less selective, and the planner could decide to perform a subsequent query using a table scan.

---

To put a finer point on it. RDBMS systems have the mandate to give you exactly what you asked for, as efficiently as possible. That efficiency can take many forms, including minimizing IO (both to disk as well as over the network to send data to you), minimizing CPU and keeping the size of its working set small (using methods that require minimal temporary storage).

without an ORDER BY clause, you will have not asked exactly for a particular order, and so the RDBMS will give you those rows in some order that (maybe) corresponds with some coincidental aspect of the query, based on whichever algorithm the RDBMS expects to produce the data the fastest.

If you care about efficiency, but not order, skip the ORDER BY clause. If you care about the order but not efficiency, use the ORDER BY clause.

Since you actually care about BOTH use ORDER BY and then carefully tune your query and database so that it is efficient.

机翻结果

不，这种行为是靠不住的。顺序由查询规划器决定构建结果集的方式决定。像select * from foo_table这样的简单查询很可能会按照它们存储在磁盘上的顺序返回，这可能是主键顺序，也可能是它们创建的顺序，或者其他一些随机顺序。更复杂的查询，比如select * from foo where bar < 10，可能会根据索引读取的顺序，或者根据表扫描的顺序，按照不同列的顺序返回。甚至更复杂的查询，如multiple where条件、按子句分组、联合，将以规划器认为最有效的顺序生成。

两个相同查询之间的顺序甚至可能发生变化，因为这些查询之间的数据发生了变化。在一次查询中，where子句可以通过索引扫描得到满足，但是随后的插入可能会使该条件的选择性降低，并且计划器可以决定使用表扫描执行后续查询。

说得更清楚一点。RDBMS系统的任务是尽可能高效地准确地满足您的要求。这种效率可以有多种形式，包括最小化IO(对磁盘以及通过网络向您发送数据)，最小化CPU并保持其工作集的大小较小(使用需要最小临时存储的方法)。

如果没有ORDER BY子句，您就不会确切地要求特定的顺序，因此RDBMS将根据RDBMS期望最快生成数据的算法，以某种顺序(可能)与查询的某些巧合方面相对应的顺序向您提供这些行。

如果您关心的是效率，而不是顺序，请跳过order BY子句。如果您关心顺序而不关心效率，请使用order BY子句。

由于您实际上关心两者，因此请使用ORDER BY，然后仔细调优您的查询和数据库，以使其高效。