数据库如何实现SQL“ORDER BY x”？
我想知道这些在幕后是如何工作的，特别是对于大型结果集。例如，数据库是否可能将原始结果写入磁盘，然后进行外部排序？
我还想知道这如何与 LIMIT...OFFSET 一起使用。如果数据库无法利用现有索引，那么数据库似乎必须对整个事物进行排序并提取原始结果集的子集。
I'm wondering how these work under the hood, especially for large result sets. For example, would the DB likely write the raw result to disk and then do an external sort?
I'm also wondering how this works with LIMIT...OFFSET. If the DB can't utilize an existing index it seems like the DB would have to sort the whole thing and pluck the subset of the raw result set.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
索引是有序的；如果有合适的索引，就会使用它。否则，他们将需要进行排序，正如您所想的那样。查询的执行计划（您可以使用例如
EXPLAIN或通过客户端菜单获取它的具体方法因情况而异；这DBMS）可能包含有关如何对查询进行排序的提示。请参阅：
Indexes are ordered; if there's a suitable index, that will be used. Otherwise, they'll need to sort, as you suppose. The execution plan for a query (which you can get with e.g.
EXPLAINor via client menus; the exact method of getting it varies with the DBMS) may contain hints as to how a query will be sorted.See:
mySQL 在 此链接
Oracle展示了自己的Order By算法过程这里
基本上，如果你有一个索引，它已订购。但是当你不排序时，就会发生
O(n log n)mySQL shows their own Order By optimization on this link
Oracle shows theor own Order By algorithm procedure here
Basically, If you have an index, it is ordered. But when you don't sorting occurs which is
O(n log n)你几乎有权利这样做。如果没有准备或预先计划任何内容（即索引或之前准备或缓存的数据），那么是的，必须读取为了生成正确的顺序而必须考虑的所有数据，并且如果数据量达到无法适应可用/分配的内存，则需要进行磁盘缓存。
这是一个不平凡的性能问题，每个 RDBMS 都会有巧妙的方法来解决和优化它，这样您就可以使用他们的产品，而不是那种劣质的“Brand X”山寨产品。
You pretty much have the right of it. If nothing has been prepared or pre-planned (i.e. indexes, or data otherwise previously prepared or cached), then yes, all the data that must be considered in order to generate the proper order must be read, and if the amount of data to be worked over cannot fit in available/allocated memory, then disk caching will need to be done.
It's a non-trivial performance issue, and every RDBMS will have clever ways to address and optimize it, so that you'll use their product and not that shoddy "Brand X" knock-off.