获得随机排序的最佳方法是什么？

ORDER BY NEWID() 将对记录进行随机排序。这里有一个例子

SELECT *
FROM Northwind..Orders 
ORDER BY NEWID()

这是一个老问题，但在我看来，讨论的一个方面是缺失的——性能。 ORDER BY NewId()是一般的答案。当有人喜欢他们时，他们会补充说，您应该真正包裹起来NewID()，CheckSum()您知道，为了性能！

这种方法的问题在于，您仍然可以保证完整的索引扫描，然后是完整的数据排序。如果您处理过任何严重的数据量，这可能会很快变得昂贵。看看这个典型的执行计划，并注意排序如何占用你 96% 的时间......

为了让您了解它是如何扩展的，我将从我使用的数据库中为您提供两个示例。

• TableA - 在 2500 个数据页中有 50,000 行。随机查询在 42 毫秒内生成 145 次读取。
• 表 B - 在 114,000 个数据页中有 120 万行。在此表上运行Order By newid()会生成 53,700 次读取，需要 16 秒。

这个故事的寓意是，如果您有大型表（想想数十亿行）或需要经常运行此查询，则该newid()方法会失败。那么男孩该怎么办呢？

满足 TABLESAMPLE()

在 SQL 2005 中，创建了一个名为的新功能TABLESAMPLE。我只看过一篇讨论它的用途的文章……应该还有更多。MSDN文档在这里。先举个例子：

SELECT Top (20) *
FROM Northwind..Orders TABLESAMPLE(20 PERCENT)
ORDER BY NEWID()

表格样本背后的想法是为您提供大约您要求的子集大小。SQL 为每个数据页编号并选择这些页的 X%。您返回的实际行数可能因所选页面中存在的内容而异。

那么我该如何使用它呢？选择一个超过所需行数的子集大小，然后添加一个Top(). 这个想法是你可以让你的巨大桌子在昂贵的排序之前显得更小。

就我个人而言，我一直在使用它来有效地限制我的桌子的大小。因此，在 1600 毫秒内执行top(20)...TABLESAMPLE(20 PERCENT)查询的百万行表下降到 5600 次读取。还有一个REPEATABLE()选项，您可以在其中传递“种子”以进行页面选择。这应该会导致稳定的样本选择。

无论如何，只是认为应该将其添加到讨论中。希望它可以帮助某人。

Pradeep Adiga 的第一个建议 ,ORDER BY NEWID()很好，我过去曾为此使用过。

使用时要小心RAND()- 在许多情况下，每个语句只执行一次，因此ORDER BY RAND()不会产生任何效果（因为您从 RAND() 中为每一行获得相同的结果）。

例如：

SELECT display_name, RAND() FROM tr_person

从我们的人员表中返回每个名称和一个“随机”数字，这对于每一行都是相同的。每次运行查询时，数字确实会有所不同，但每次对于每一行都是相同的。

为了表明RAND()在一个ORDER BY子句中使用的情况也是如此，我尝试：

SELECT display_name FROM tr_person ORDER BY RAND(), display_name

结果仍然按名称排序，表明较早的排序字段（预期是随机的）没有效果，因此可能总是具有相同的值。

不过，ordering byNEWID()确实有效，因为如果 NEWID() 并不总是重新评估，那么当在一个语句中插入许多新行时，UUID 的目的将被破坏，这些新行具有唯一标识符作为键，所以：

SELECT display_name FROM tr_person ORDER BY NEWID()

确实“随机”排序名称。

其他数据库管理系统

以上对于 MSSQL 是正确的（至少 2005 年和 2008 年，如果我没记错的话 2000 年也是如此）。每次在 MSSQL 下的所有 DBMS NEWID() 中都应评估返回新 UUID 的函数，但值得在文档中和/或通过您自己的测试来验证这一点。其他任意结果函数（如 RAND()）的行为更可能在 DBMS 之间有所不同，因此请再次查看文档。

此外，我还看到在某些情况下会忽略 UUID 值的排序，因为 DB 假定该类型没有有意义的排序。如果您发现这种情况是在 ordering 子句中显式地将 UUID 转换为字符串类型，或者CHECKSUM()在 SQL Server 中围绕它包装一些其他函数（也可能存在小的性能差异，因为排序将在32 位值而不是 128 位值，尽管这样做的好处是否超过了运行CHECKSUM()每个值的成本首先我会让你测试）。

边注

如果您想要任意但有些可重复的排序，请按行本身中一些相对不受控制的数据子集进行排序。例如，其中一个或这些将以任意但可重复的顺序返回名称：

SELECT display_name FROM tr_person ORDER BY CHECKSUM(display_name), display_name -- order by the checksum of some of the row's data
SELECT display_name FROM tr_person ORDER BY SUBSTRING(display_name, LEN(display_name)/2, 128) -- order by part of the name field, but not in any an obviously recognisable order)

任意但可重复的顺序在应用程序中通常不有用，但如果您想在各种顺序的结果上测试一些代码但希望能够以相同的方式重复每次运行多次（以获得平均时间），则在测试中可能很有用多次运行的结果，或测试您对代码所做的修复确实消除了先前由特定输入结果集突出显示的问题或效率低下，或仅用于测试您的代码“稳定”，即每次返回相同的结果如果以给定的顺序发送相同的数据）。

这个技巧也可以用来从函数中获得更多的任意结果，这些函数不允许在它们的体内进行像 NEWID() 这样的非确定性调用。同样，这在现实世界中可能不会经常有用，但如果您希望函数返回随机的东西并且“随机”已经足够好（但请注意记住确定的规则）当评估用户定义的函数时，即通常每行只有一次，或者您的结果可能不是您期望/要求的）。

表现

正如 EBarr 指出的那样，上述任何一项都可能存在性能问题。对于多于几行，您几乎可以保证在请求的行数以正确的顺序读回之前看到输出假脱机到 tempdb，这意味着即使您正在寻找前 10 名，您也可能会找到一个完整的索引扫描（或更糟糕的是，表扫描）伴随着对 tempdb 的大量写入发生。因此，与大多数事情一样，在将其用于生产之前，用真实数据进行基准测试是非常重要的。

许多表都有一个相对密集（缺失值很少）索引的数字 ID 列。

这使我们能够确定现有值的范围，并使用该范围内随机生成的 ID 值选择行。当要返回的行数相对较少，并且 ID 值的范围很密集（因此生成缺失值的机会足够小）时，这种方法效果最好。

为了说明，以下代码从 Stack Overflow 用户表中选择 100 个不同的随机用户，该表有 8,123,937 行。

第一步是确定 ID 值的范围，由于索引的有效操作：

DECLARE 
    @MinID integer,
    @Range integer,
    @Rows bigint = 100;

--- Find the range of values
SELECT
    @MinID = MIN(U.Id),
    @Range = 1 + MAX(U.Id) - MIN(U.Id)
FROM dbo.Users AS U;

该计划从索引的每一端读取一行。

现在我们在范围内生成 100 个不同的随机 ID（在 users 表中匹配行）并返回这些行：

WITH Random (ID) AS
(
    -- Find @Rows distinct random user IDs that exist
    SELECT DISTINCT TOP (@Rows)
        Random.ID
    FROM dbo.Users AS U
    CROSS APPLY
    (
        -- Random ID
        VALUES (@MinID + (CONVERT(integer, CRYPT_GEN_RANDOM(4)) % @Range))
    ) AS Random (ID)
    WHERE EXISTS
    (
        SELECT 1
        FROM dbo.Users AS U2
            -- Ensure the row continues to exist
            WITH (REPEATABLEREAD)
        WHERE U2.Id = Random.ID
    )
)
SELECT
    U3.Id,
    U3.DisplayName,
    U3.CreationDate
FROM Random AS R
JOIN dbo.Users AS U3
    ON U3.Id = R.ID
-- QO model hint required to get a non-blocking flow distinct
OPTION (MAXDOP 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

该计划表明，在这种情况下，需要 601 个随机数才能找到 100 个匹配的行。它非常快：

表“用户”。扫描计数 1，逻辑读取 1937，物理读取 2，预读读取 408
表“工作台”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0
表“工作文件”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0

 SQL Server 执行时间：
   CPU 时间 = 0 毫秒，经过时间 = 9 毫秒。

在 Stack Exchange 数据资源管理器上试一试。

正如我在本文中所解释的，为了对 SQL 结果集进行洗牌，您需要使用特定于数据库的函数调用。

请注意，使用 RANDOM 函数对大型结果集进行排序可能会非常慢，因此请确保对小型结果集进行排序。

如果您必须对大型结果集进行洗牌并在之后对其进行限制，那么最好使用 SQL ServerTABLESAMPLE中的SQL Server而不是 ORDER BY 子句中的随机函数。

因此，假设我们有以下数据库表：

以及表中的以下行song：

| id | artist                          | title                              |
|----|---------------------------------|------------------------------------|
| 1  | Miyagi & Эндшпиль ft. Рем Дигга | I Got Love                         |
| 2  | HAIM                            | Don't Save Me (Cyril Hahn Remix)   |
| 3  | 2Pac ft. DMX                    | Rise Of A Champion (GalilHD Remix) |
| 4  | Ed Sheeran & Passenger          | No Diggity (Kygo Remix)            |
| 5  | JP Cooper ft. Mali-Koa          | All This Love                      |

在 SQL Server 上，您需要使用该NEWID函数，如下例所示：

SELECT
    CONCAT(CONCAT(artist, ' - '), title) AS song
FROM song
ORDER BY NEWID()

在 SQL Server 上运行上述 SQL 查询时，我们将得到以下结果集：

| song                                              |
|---------------------------------------------------|
| Miyagi & Эндшпиль ft. Рем Дигга - I Got Love      |
| JP Cooper ft. Mali-Koa - All This Love            |
| HAIM - Don't Save Me (Cyril Hahn Remix)           |
| Ed Sheeran & Passenger - No Diggity (Kygo Remix)  |
| 2Pac ft. DMX - Rise Of A Champion (GalilHD Remix) |

NEWID请注意，由于ORDER BY 子句使用的函数调用，歌曲是按随机顺序列出的。

这是一个旧线程，但最近遇到了这个；所以更新一种对我有用并提供良好性能的方法。这假设您的表有一个 IDENTITY 或类似的列：

DECLARE @r decimal(8,6) = rand()
SELECT @r

SELECT  TOP 100 *
FROM    TableA
ORDER BY ID % @r