没有安全带—期待用 ORDER BY 排序

作者:陈梦译 来源:ITPOW(原创) 日期:2009-4-23

我在创建软件时学到的最有意义的一课是:每一项技术实现都取决于人们如何使用、理解、解释它。我已经发现糟糕的技术解决方案带来的问题,有时大多数有趣的“错误”点是由于人类操作员和他/她如何使用、理解、解释软件特性。这些特性中有些简单易懂,但有些对我来说有点难体验了。

偶尔有人问我关于 SQL Server 中查询时排序问题。某些情况下,SQL Server 不得不打破事先未声明却又固定的行为(TOP 100 Percent... ORDER BY)。在其他情况下,我发现很难通过逻辑来解释。今天的问题是做一做没有指明 ORDER BY 但仍按排好的序返回行。一些人说:“我没有指定 ORDER BY,因为我知道它将按排好的序返回行,并且每次都是这样的!每次!我保证!”他们这样说让我对 SQL 语言那些不起眼的角落产生了兴趣。

现在,我们讨论下关于一个 top-level 的查询中 ORDER BY 子句时指定 ANSI SQL 的 order。通常的结论是:取决于开发,数据库开发员发现一个查询计划总是返回相同的结果。那么,那么我是不是可以说每个人从现在到宇宙的尽头获得的都是相同的查询计划。

CISCAN

查询计划会变化吗?现在,使用相同的电脑配置(CPU、内存等),也不要在表中插入数据,也许你会幸运地获得相同的计划……也许,当然,当你升级到 SQL Server 下一个版本时,也许服务变了,新的优化被添加了,或者有人在聚集索引中添加了列使它变得比以前慢了。也有这些都可能改变计划,即使你没有改变什么。

令人惊讶的是,如果我添加一个“ORDER BY col1”到这个查询中,我会发现查询计划总是相同的。看来没有投入就没有保障啊,虽然也许只是多键入几个字符。

ciorderby

有人问“哪里说了,系统不会按排好的序返回行?”,现在这类问题的“人类”部分的问题产生了。经过多次试验后,你脑海中的结论已经很难撤销了,你总认为任何情况下查询会返回排好序的行。那么,我今天要给你看看不同于你结论的例子。

我在我个人的计算机上运行 SQL Server 2008 开发者版本,双核、8G,要完成这个测试,你也要用一台让 SQL Server 至少有两个 CPU 可用的机器。

CREATE TABLE [dbo].[orderingtest1](
    [col1] [int] NULL,
    [col2] [int] NULL,
    [col3] [int] NULL,
    [col4] [int] NULL,
    [col5] [binary](4000) NULL
) ON [PRIMARY];
create clustered index i1 on orderingtest1(col1)
 
DECLARE @i INT=0;
set nocount on
begin transaction
WHILE @i<20000
BEGIN
INSERT INTO [orderingtest1](col1, col2, col3, col4) VALUES (rand()*1000, rand()*1000, rand()*1000, rand()*1000);
SET @i+=1;
END;
commit transaction

我创建了一个条,并添加了 20,000 条记录。就当作是你网站清单表。

让我们运行一个产生计划的查询,它为我返回了特定的顺序。

select * from orderingtest1 where col2 = 5

output1

哇,它是按 col1 排序的!让我们再加些记录。

DECLARE @i INT=0;
set nocount on
begin transaction
WHILE @i<20000
BEGIN
INSERT INTO [orderingtest1](col1, col2, col3, col4) VALUES (rand()*1000, rand()*1000, rand()*1000, rand()*1000);
SET @i+=1;
END;
commit transaction

又加了 20,000 条记录,我的查询仍然很好。这里只有一个索引,能发生什么?

我们再加 20,000 条记录进去,再查询看看:

select * from orderingtest1 where col2 = 5

parallel

哇!我们有一个新奇的平行查询计划。它使用多线程来扫描索引,还带有过滤条件的。大数据集时,这很快。

(你也许需要运行 DBCC FREEPROCCACHE,如果你没有获得这样的查询,因为数据是随机的,你也可以试试另一个字段 col2 的值。确保你的 SQL 中是使用了两个以上的 CPU,不停地添加行,你总会获得我上面的查询计划的。)

unordered1

My God,它不是排序的!

难的是,SQL Server 不会告诉我们什么情况下查询计划会改变。所有的查询空间都很大,让你去深思。如果有足够的参数发生了变化,SQL Server 优化器就会改变计划,即使是简单的查询。也可能你运气好,查询计划不会变,或者你添加 ORDER BY 不考虑这个问题。有趣的是,当有足够的记录时,添加了 ORDER BY,SQL Server 也同样像这样产生计划:

parallelorder

但是,如果你运行查询,你会发现你的查询结果确实是排好序的……Exchange 操作员可以在一个保护排序的模型中运行,但是也需要你要求他使用 ORDER BY 子句。

properties

因此,添加 ORDER BY,让一切都有序。(我曾看到两个家伙提到这点,其中一个说“一个让查询结果有序的方法?不错!”)

我做的例子是非常简单的—一个带聚集索引的表,使用一个只有一个例的简单查询。仅有两种查询计划要考虑,其中一个没有排序保障。

对于复杂的查询,优化器可能会改变计划,使产生很多种情况,可能多达上千种,或者更多,每一种都可能被选中。对这些查询,如果你没有指定排序,那么他们的排序可能各不相同。因此,我希望你可以去尝试一下这些东西—我发现示例帮助了我,希望也能帮助你。

因此,我今天的建议是:

如果你需要查询结果有序,使用 ORDER BY,它挺简单的。否则的话,就像是没有系安全带驾驶一辆汽车。

Conor Cunningham

Architect, SQL Server Core Engine

相关文章