mysql中的order by 带limit与不带limit结果是不一样的。
mysql> select table_name from tables order by table_rows;
+------------------------------------------------------+
| table_name |
+------------------------------------------------------+
| x$wait_classes_global_by_avg_latency |
| x$wait_classes_global_by_latency |
| x$waits_by_host_by_latency |
| x$waits_by_user_by_latency |
| x$waits_global_by_latency |
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
mysql> select table_name from tables order by table_rows limit 5;
+---------------------------------------+
| table_name |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
+---------------------------------------+
5 rows in set (1.87 sec)
如果需要加limit与不带limit的顺序保持一致，需要
select table_name from tables order by table_rows,table_name limit 5;这种方式。
如果ORDER BY列中的多个行具有相同的值，则服务器可以按任何顺序自由返回这些行，并且可能会根据整体执行计划的不同而不同。 换句话说，这些行的排序顺序相对于无序列是不确定的。
https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html
