mysql orderby+分页 乱序原因和解决

在 orderby 的字段具有相同的值的时候，使用orderby + 分页可能造成乱序. 而且，相同的查询条件，是否使用limit 返回的查询结果也可能不一样。

select * from xx order by create_time desc limit 0,10

原因

官网对此情况的解释

MySQL sometimes optimizes a query that has a LIMIT ***row_count*** clause and no HAVING clause:

• If you select only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.
• If you combine LIMIT ***row_count*** with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first ro