"order by newid()“-它是如何工作的？

Question

我知道如果我运行这个查询

select top 100 * from mytable order by newid()

它将从我的表中随机获得100条记录。

但是，我对它的工作原理有点困惑，因为我在select列表中没有看到newid()。有人能解释一下吗？这里的newid()有什么特别之处吗？

Answer 1

我知道NewID()是做什么的，我只是想知道它对随机选择有什么帮助。(1) select语句将从mytable中选择所有内容，(2)对于所选的每一行，添加由NewID()生成的唯一标识符，(3)按该唯一标识符对行进行排序，以及(4)从排序列表中选取前100行？

是。这几乎完全正确(除了它不一定需要对所有行进行排序)。您可以通过查看实际的执行计划来验证这一点。

SELECT TOP 100 * 
FROM master..spt_values 
ORDER BY NEWID()

compute标量操作符为每一行(在我的示例查询中为表中的2506)添加NEWID()列，然后表中的行按该列排序，其中前100行被选中。

SQL Server实际上不需要从第100位向下对整个集合进行排序，因此它使用了TOP N排序运算符，该运算符尝试在内存(for small values of N)中执行整个排序操作。

​

​

Answer 2

一般来说，它是这样工作的：

从mytable中选择所有行is "looped"

• NEWID()对每行执行
• 根据来自NEWID()的随机数对行进行排序
• 100第一行被选择

Answer 3

这里的关键是NEWID函数，它在内存中为每一行生成一个全局唯一标识符(GUID)。根据定义，GUID是唯一且相当随机的；因此，当您使用ORDER By子句按该GUID进行排序时，您将获得表中行的随机排序。取前10%(或您想要的任何百分比)将对表中的行进行随机抽样。

提出了NEWID查询；它很简单，并且非常适合于小表。但是，当您将NEWID查询用于大型表时，它有一个很大的缺点。ORDER BY子句使表中的所有行都复制到tempdb数据库中，并在该数据库中对这些行进行排序。这导致了两个问题:排序操作通常具有与之相关的高成本。排序可能会使用大量磁盘I/O，并且可能会运行很长时间。在最坏的情况下，tempdb可能会用完空间。在最好的情况下，tempdb可能会占用大量磁盘空间，如果没有手动收缩命令，这些空间永远不会被回收。您需要的是一种随机选择行的方法，这种方法不会使用tempdb，并且不会随着表的变大而变慢。这里有一个关于如何做到这一点的新想法：

SELECT * FROM master..spt_values
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10

此查询背后的基本思想是，我们希望为表中的每一行生成一个介于0和99之间的随机数，然后选择随机数小于指定百分比值的所有行。在本例中，我们希望随机选择大约10%的行；因此，我们选择随机数小于10的所有行。