主键排序 [英] Primary Key Sorting

问题描述

表是否按照其主键进行内在排序？如果我在BigInt标识列上有一个包含主键的表，我可以相信查询将始终返回按键排序的数据，或者我是否明确需要添加ORDER BY。性能差异很大。

Is a table intrinsically sorted by it's primary key? If I have a table with the primary key on a BigInt identity column can I trust that queries will always return the data sorted by the key or do I explicitly need to add the "ORDER BY". The performance difference is significant.

推荐答案

数据是通过聚集索引物理存储的，聚簇索引通常是主键但没有将来。

Data is physically stored by clustered index, which is usually the primary key but doesn't have to be.

如果没有ORDER BY子句，SQL中的数据不能保证有订单。当您需要按特定顺序排列数据时，应始终指定ORDER BY子句。如果表已经按这种方式排序，优化器将不会做任何额外的工作，因此将它放在那里是没有害处的。

Data in SQL is not guaranteed to have order without an ORDER BY clause. You should always specify an ORDER BY clause when you need the data to be in a particular order. If the table is already sorted that way, the optimizer won't do any extra work, so there's no harm in having it there.

没有ORDER BY子句，在等待从磁盘读入记录时，RDBMS可能会返回与您的查询匹配的缓存页面。在这种情况下，即使表上有索引，数据也可能不会以索引的顺序进入。 （注意这只是一个例子 - 我不知道甚至认为真实世界的RDBMS会这样做，但它是SQL实现的可接受行为。）

Without an ORDER BY clause, the RDBMS might return cached pages matching your query while it waits for records to be read in from disk. In that case, even if there is an index on the table, data might not come in in the index's order. (Note this is just an example - I don't know or even think that a real-world RDBMS will do this, but it's acceptable behaviour for an SQL implementation.)

编辑

如果您在排序时与不排序时产生性能影响，您可能会对列（或列集）进行排序）没有索引（聚集或其他）。鉴于它是一个时间序列，您可能会根据时间进行排序，但聚集索引位于主要bigint上。 SQL Server不知道两者都以相同的方式增加，因此它必须采取一切措施。

If you have a performance impact when sorting versus when not sorting, you're probably sorting on a column (or set of columns) that doesn't have an index (clustered or otherwise). Given that it's a time series, you might be sorting based on time, but the clustered index is on the primary bigint. SQL Server doesn't know that both increase the same way, so it has to resort everything.

如果时间列和主键列是按顺序相关的（当且仅当另一个增加或保持不变时，一个增加，而是按主键排序。如果它们不以这种方式相关，则将聚集索引从主键移动到您要排序的任何列。

If the time column and the primary key column are a related by order (one increases if and only if the other increases or stays the same), sort by the primary key instead. If they aren't related this way, move the clustered index from the primary key to whatever column(s) you're sorting by.

这篇关于主键排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！