处理默认排序顺序的 SQL 最佳实践

SQL best practice to deal with default sort order

提问by Yada

A lot of SQL code I've read, it seems like the developer assumes that the default sort order always holds. For example when building an HTML select list they would just SELECT id, name FROM tablewithout issuing an ORDER BYclause.

我读过很多 SQL 代码，似乎开发人员假设默认的排序顺序总是成立。例如，在构建 HTML 选择列表时，他们SELECT id, name FROM table不会发出ORDER BY子句。

From my own experience it seems like dbms alway orders data using FIFO if no ORDER BYclause is given and no index. However, the order is not guaranteed. But I have never seen a dbms reordering data if there no change to the table.

根据我自己的经验，如果没有ORDER BY给出子句也没有索引，dbms 似乎总是使用 FIFO 对数据进行排序。但是，不能保证顺序。但是，如果表没有变化，我从未见过 dbms 重新排序数据。

Have you ever experienced a dbms selecting data in a non deterministic order if there is no change to the table?

如果表没有变化，您是否遇到过 dbms 以非确定性顺序选择数据的情况？

Is it best practice to always put an ORDER BY clause?

始终放置 ORDER BY 子句是最佳实践吗？

回答by HLGEM

There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.

没有默认的排序顺序。即使该表具有聚集索引，也不能保证按该顺序获得结果。如果需要特定顺序，则必须使用 order by 子句。

回答by Jim Ferrans

As the other posters mention, if you don't specify a sort order, the SQL standard says the results can be in whatever order the query processor finds most expedient and efficient.

正如其他海报所提到的，如果您不指定排序顺序，SQL 标准说结果可以是查询处理器认为最方便和最有效的任何顺序。

Let's say you do a simple unordered SELECT for all the rows of a CUSTOMER table, which has no indexes and no primary key. It's quite possible, and even likely, that the query processor will do a straight table scan and produce the rows in the order they were originally inserted (giving you the FIFO behavior you saw).

假设您对 CUSTOMER 表的所有行执行简单的无序 SELECT，该表没有索引也没有主键。很有可能，甚至很有可能，查询处理器将执行直接表扫描并按照它们最初插入的顺序生成行（为您提供您看到的 FIFO 行为）。

If you then add an index on the STATE and CITY fields (in that order), and then query for WHERE STATE = 'NY'the query processor may decide it's more efficient to scan the index entries for STATE = 'NY' rather than to do a full table scan. In this case it would probably materialize the rows in STATE, CITY order.

如果随后在 STATE 和 CITY 字段上添加索引（按该顺序），那么查询WHERE STATE = 'NY'处理器的查询可能会决定扫描 STATE = 'NY' 的索引条目比执行全表扫描更有效。在这种情况下，它可能会按 STATE、CITY 顺序实现行。

Even this is not certain. For example if the query processor has gathered statistics that show that nearly all the STATE values in your table are 'NY' (maybe because the database is for an Albany-based equipment rental business), it may decide that the table scan is actually cheaper than the index scan, and you'll see FIFO again.

甚至这也不确定。例如，如果查询处理器收集的统计数据显示您的表中几乎所有 STATE 值都是“NY”（可能是因为该数据库是用于奥尔巴尼的设备租赁业务），它可能会决定表扫描实际上更便宜比索引扫描，你会再次看到 FIFO。

It's a good idea to learn some basics about how your database plans its queries. You can use the EXPLAINstatement to see how your DBMS would execute any given query, and then use this to optimize your query, in some cases by orders of magnitude. This is a fascinating and useful area to learn.

了解一些有关数据库如何计划查询的基础知识是个好主意。您可以使用该EXPLAIN语句来查看您的 DBMS 如何执行任何给定的查询，然后使用它来优化您的查询，在某些情况下按数量级。这是一个有趣且有用的学习领域。

回答by OMG Ponies

If you want the data to come out consistently ordered, yes - you have to use ORDER BY.

如果您希望数据按顺序排列，是的 - 您必须使用ORDER BY.

回答by Ken White

Yes. There is no "default order" without an ORDER BY, and there's no guarantee that you'll get the data back in FIFO/LIFO or any other order.

是的。没有 ORDER BY 就没有“默认顺序”，并且不能保证您会以 FIFO/LIFO 或任何其他顺序取回数据。

As far as the developers using "SELECT id, name FROM table", they're either inept or they don't care what order anything appears in.

至于使用“SELECT id，name FROM table”的开发人员，他们要么无能，要么不在乎任何东西出现的顺序。

回答by marc_s

No serious RDBMS guarantees any order unlessyou specify an explicit ORDER BY.

除非您指定显式 ORDER BY，否则没有严肃的 RDBMS 保证任何顺序。

Anything else is just pure luck or anectodal - if you want order, you have to specify ORDER BY - no way around that.

其他任何事情都只是纯粹的运气或轶事 - 如果你想要订单，你必须指定 ORDER BY - 没有办法解决这个问题。

回答by simo.3792

In my experience with SQL, most of the time I do not specify a ORDER BYin SQL, because the record sets are displayed in a "client-side" grid type control etc. where dynamic sorting is supported - in this case ordering by SQL is needlessas it will be checked client side anyway.

根据我使用 SQL 的经验，大多数时候我不会在 SQL 中指定ORDER BY，因为记录集显示在支持动态排序的“客户端”网格类型控件等中 - 在这种情况下按 SQL 排序是不必要的，因为无论如何它都会在客户端进行检查。

This is also done client side because the same query might be used to display the data in different places in different orders.

这也在客户端完成，因为相同的查询可能用于以不同的顺序在不同的地方显示数据。

Therefore it is only best practice to put in an ORDER BY, when

因此，最好的做法是放入 ORDER BY，当

• The order of the data ISimportant; and
• The sorting is more efficient at the DB level.

• 数据的顺序IS重要; 和
• 排序在 DB 级别更有效。

i.e. if the front end developer is going to be "re-sorting" it anyway, then there is no point, as it unlikely to save overall processing time.

即如果前端开发人员无论如何都要“重新排序”它，那么没有意义，因为它不太可能节省整体处理时间。

回答by boydc7

If you want the data ordered, the only way to guarantee anything (with every major RDBMS system that I'm aware of, definitely Sql Server and Oracle) is to include an ORDER BY clause. FIFO has absolutely nothing to do with the order data is returned without an ORDER BY clause, and there isn't a concept of any kind of DEFAULT sort order. The so called DEFAULT sort order is basically however the engine gets the data, which could be in literally any order based on indexes, cached data, simultaneous executing queries, load on the server, etc., etc.

如果您想要对数据进行排序，那么保证任何事情的唯一方法（对于我所知道的每个主要 RDBMS 系统，绝对是 Sql Server 和 Oracle）是包含一个 ORDER BY 子句。FIFO 与没有 ORDER BY 子句返回数据的顺序完全无关，并且没有任何类型的 DEFAULT 排序顺序的概念。所谓的默认排序顺序基本上是引擎获取数据，它可以是基于索引、缓存数据、同时执行的查询、服务器上的负载等的任何顺序。

This other stackoverflow threadis basically covering the same concept in relation to Sql Server, AlexK blogged a repoto demonstrate the behavior.

另一个 stackoverflow 线程基本上涵盖了与 Sql Server 相关的相同概念，AlexK 在博客上写了一个 repo来演示该行为。

回答by Remus Rusanu

Even a simple query like SELECT ... FROM tablecan return data in various order. I know this to be true in theory, I know this to be true in practice, and I have seen plenty of cases when the order changes between subsequent executions, even when no data change occurs in the table.

即使是像这样的简单查询SELECT ... FROM table也可以按各种顺序返回数据。我知道这在理论上是正确的，我知道在实践中也是如此，并且我已经看到了很多情况，即使在表中没有发生任何数据更改的情况下，后续执行之间的顺序也会发生变化。

A typical example of order changes between executions is when the query is executed using a parallel plan. Since parallel operators return data as the underlying threads produce it, the order of the rows in the result varies between each run. This situation makes even the simple SELECT in your example return wildly different results each time is run.

执行之间顺序更改的典型示例是使用并行计划执行查询时。由于并行运算符在底层线程生成数据时返回数据，因此每次运行时结果中的行顺序会有所不同。这种情况使得即使是示例中的简单 SELECT 每次运行时也会返回截然不同的结果。

回答by hoggar

I'm writing this in case if someone would like to use this as I did.

如果有人想像我一样使用它，我正在写这个。

Well, I'm getting satisfactory default sort order, let's say for log tables, with sort on Index. For example I'm usually interested in last rows of log table (LIFO) so I make DateTime DESC as order. I also tried for fun to add Index on the other field (integer) beside Primary key and it worked.

好吧，我得到了令人满意的默认排序顺序，比如说对于日志表，对索引进行排序。例如，我通常对日志表 (LIFO) 的最后几行感兴趣，所以我将 DateTime DESC 作为顺序。为了好玩，我还尝试在主键旁边的另一个字段（整数）上添加索引并且它起作用了。

CREATE TABLE [dbo].[tableA]([DateTime] [datetime] NOT NULL,
CONSTRAINT [PK_tableA] 
PRIMARY KEY CLUSTERED ([DateTime] DESC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

Or in SSMS ...

或者在 SSMS ...

回答by Mike Atlas

Perhaps the writers of those SQL queries you're reading don't care about the order of the data returned. The best practice is to use it where you need to ensure the order of the results returned!

也许您正在阅读的那些 SQL 查询的作者并不关心返回数据的顺序。最佳实践是在需要确保返回结果顺序的地方使用它！