blocks|key|776611|text|确切的方法取决于您使用的产品，但通常一个功能齐全的DBMS有多种排序算法可供其使用。有些在磁盘上工作，随着时间的推移优化空间，有些在内存中工作，优化速度。如果你对血淋淋的细节感兴趣，请查看可用的开源软件的源代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|776612|您不太可能通过自己进行排序或使用其他库来获得更好的结果，尽管可能存在病态情况，例如某些操作系统的qsort()在某些数据分布方面存在问题。如果必须的话，可以尝试一下，但更喜欢使用DBMS来管理数据，因为这是他们擅长的。|offset|length|style|CODE|776613|entityMap^0|0|1C|7|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

The exact method depends on the product you are using, but normally a fully-featured DBMS has multiple sort algorithms at its disposal. Some work on disk, optimizing for space over time, some work in memory, optimizing for speed. Check the source code of the available open source ones, if you are interested in the gory details.

It's unlikely that you are going to get better results by doing the sorting yourself or using some other library, although there can be pathological cases such as some operating system's <code>qsort()</code> having problems with certain data distributions. Try it out if you must, but prefer using a DBMS to manage your data, because that's what they are good at.

blocks|key|776714|text|几乎可以肯定的是，对数据库中的数据进行排序会更有效。数据库是为处理大数据量而设计的。并且有各种可用于数据库的优化，而这些优化对于中间层则不可用。如果您计划在中间层编写一个超级高效的排序例程，该例程利用数据库没有的有关数据的信息(例如，将数据分派到由数十台中间层机器组成的集群中，这样排序就不会溢出到磁盘，从而利用这样一个事实，即您的数据大多被排序，以选择一种通常效率不是特别高的算法)，那么您可能可以超越数据库的排序速度。但这种情况往往很少见。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|776715|例如，根据查询，数据库优化器可以选择按顺序返回数据而不执行排序的查询计划。例如，数据库知道索引中的数据是排序的，因此它可以选择执行索引扫描来按顺序返回数据，而不必物化和排序整个结果集。如果它必须物化整个结果，那么它只需要排序所依据的列和某种行标识符(即Oracle中的ROWID+)，而不是像简单的中间层实现那样对整行数据进行排序。例如，如果您有一个关于(+col1，col2)的复合索引，并且您决定按上部(+col2+)、下部(Col1)进行排序，那么数据库可以从索引中读取col1&col2值，对行标识符进行排序，然后从表中获取数据。当然，数据库不必这样做--优化器将考虑执行排序的成本与从表或从各种索引获取数据的成本。数据库可能会得出这样的结论:最有效的方法是进行表扫描，将整个行读入内存，然后对其进行排序。它可能会得出结论，利用索引会导致更多的I/O来获取数据，但通过减少或消除排序成本来弥补这一点。|776716|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

It will almost certainly be more efficient to sort the data in the database. Databases are designed to deal with large data volumes. And there are various optimizations available to the database that would not be available to the middle tier. If you plan on writing a hyper-efficient sort routine in the middle tier that takes advantage of information that you have about your data that the database doesn't (i.e. farming the data out to a cluster of dozens of middle tier machines so that the sort never spills to disk, taking advantage of the fact that your data is mostly ordered to choose an algorithm that wouldn't normally be particularly efficient), you can probably beat the database's sort speed. But that tends to be rare.

Depending on the query, for example, the database optimizer may choose a query plan that returns the data in order without performing a sort. For example, the database knows that the data in an index is sorted so it may choose to do an index scan to return the data in order without ever having to materialize and sort the entire result set. If it does have to materialized the entire result, it only needs the columns you are sorting by and some sort of row identifier (i.e. a ROWID in Oracle) rather than sorting an entire row of data like a naive middle tier implementation is likely to do. For example, if you have a composite index on (col1, col2) and you decide to sort on UPPER(col2), LOWER(col1), the database could read the col1 &amp; col2 values from the index, sort the row identifiers, and then go fetch the data from the table. Of course, the database doesn't have to do this-- the optimizer will take into account the cost of doing a sort against the cost of fetching the data from the table or from the various indexes. The database may well conclude that the most efficient approach is to do a table scan, read the entire row into memory, and sort it. It may conclude that leveraging an index results in more I/O to fetch the data but makes up for it by reducing or eliminating the sort costs.

blocks|key|776790|text|除非排序是基于索引的，否则如果您使用数据库排序，您可以保证您将等待整个结果集在数据库中解析和排序，然后才能看到结果集的任何一行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|776791|如果你自己排序，数据可能会被增量地流式传输(更适合于网络受限的环境)，并且可能会逐渐对应用程序有用，即使排序操作消耗了相同的总时间，也会减少执行延迟。|776792|根据部署场景的不同，与排序相关的额外成本应该在哪里支付，这可能会产生很大的不同。在我使用的场景中，中间层是一次性和可伸缩的，而数据层向外扩展的成本更高。如果它的CPU成本相同，但数据库CPU的运营成本是它的5倍或10倍，那么在数据库之外做这件事实际上就更便宜了。|776793|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Unless sort is index based if you use database sort you are guaranteeing you will wait for entire result set to be resolved and sorted in the database before you see even a single row of the result set.

If you sort it yourself data may be incrementally streamed (better for network constrained environment) and perhaps incrementally useful to application reducing execution delay even if sorting operation consumes the same amount of total time.

Depending on deployment scenario it might make a big difference where the extra costs associated with sorting should be paid out. In scenarios I work with middle tier is disposable and scalable while data tier is more expensive to scale out. If it costs the same CPU but database CPU costs 5x or 10x in terms of operational cost it becomes cheaper in real terms to do it outside the database.

I don't quite understand how a SQL command would sort a large resultset. Is it done in memory on the fly (i.e. when a query is perfomed)? 

Is is going to be faster to sort using ORDER BY in SQL rather than sort say a linked list of objects containing the results in a language like Java (assuming a fast built-in sort, probably using quicksort)?

How Expensive is SQL ORDER BY?

数据库优化

数据库

大数据

Java

我不太理解SQL命令如何对大型结果集进行排序。它是在内存中动态完成的吗(即当查询执行时)？在SQL中使用ORDER BY进行排序是否会比在Java等语言中使用包含结果的对象链表进行排序更快(假设使用快速内置排序，可能使用快速排序)？

问SQL ORDER BY有多贵？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL ORDER BY有多贵？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL ORDER BY有多贵？
EN