sql-server - SQL 大表选择随机行策略

Question

我想从一个非常大的表(1000 万条记录)中选择一个随机行。因此，最常见的策略(例如 RAND() 和 NEWID())似乎并不实用。

我尝试了以下策略，想知道这是否是最理想的方法。

1. 创建一个名为“RandomSort”的新字段作为 UniqueIdentified

2. 每小时/每天结束时将对整个表执行Update RandomSort = NewID()

3. 每次需要查询时，我都可以按RandomSort排序Top 10

它确实完成了工作(比 ORDER BY NewID 更好)，但不确定这是否是迄今为止的最佳实践？

Answer 1

添加标识列“rowid”(int 或 bigint，具体取决于表大小)并在其上创建唯一的非聚集索引。

以下查询使用 NEWID() 函数返回表中大约百分之一的行:

SELECT * FROM MyTable
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), rowID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)

rowId 列包含在 CHECKSUM 表达式中，以便 NEWID() 每行计算一次，以实现按行采样。表达式 CAST(CHECKSUM(NEWID(), rowid) & 0x7fffffff AS float/CAST(0x7fffffff AS int) 计算结果为 0 到 1 之间的随机浮点值。

事实上，您可以使用表中的任何列索引列(我相信)。

如果您只想选择一个随机行:

SELECT TOP 1 * FROM table 
WHERE rowid >= RAND(CHECKSUM(NEWID())) * (SELECT MAX(rowid) FROM table) 

如果 rowid 列已建立索引，则此操作会在恒定时间内起作用。注意:这假设 rowid 均匀分布在 0..MAX(rowid) 范围内，因此建议添加标识列。如果您的数据集具有其他分布，您的结果将会出现偏差(即某些行将比其他行更频繁地被选取)。