tsql - 按性能随机排序

Question

按随机顺序获取top n行的最佳方法是什么？
我使用如下查询:

Select top(10) field1,field2 .. fieldn
from Table1
order by checksum(newid())

上面查询中的问题是，随着表大小的增加，它将保持越来越慢的速度。它将始终执行完整的聚集索引扫描，以按随机顺序查找 top(10)行。

还有其他更好的方法吗？

Answer 1

我已经对此进行了测试，并在更改查询时获得了更好的性能。

我在测试中使用的表的DDL。

CREATE TABLE [dbo].[TestTable]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Col1] [nvarchar](100) NOT NULL,
    [Col2] [nvarchar](38) NOT NULL,
    [Col3] [datetime] NULL,
    [Col4] [nvarchar](50) NULL,
    [Col5] [int] NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
 (
    [ID] ASC
 )
)

GO

CREATE NONCLUSTERED INDEX [IX_TestTable_Col5] ON [dbo].[TestTable] 
(
    [Col5] ASC
)

该表有722888行。

第一个查询:

select top 10
  T.ID,
  T.Col1,
  T.Col2,
  T.Col3,
  T.Col5,
  T.Col5
from TestTable as T
order by newid()

首次查询的统计信息:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 13 ms.

(10 row(s) affected)
Table 'TestTable'. Scan count 1, logical reads 12492, physical reads 14, read-ahead reads 6437, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 859 ms,  elapsed time = 1700 ms.

执行计划第一个查询:

第二个查询:

select 
  T.ID,
  T.Col1,
  T.Col2,
  T.Col3,
  T.Col5,
  T.Col5
from TestTable as T
  inner join (select top 10 ID
              from TestTable
              order by newid()) as C
    on T.ID = C.ID

第二次查询的统计信息:

SQL Server parse and compile time: 
   CPU time = 125 ms, elapsed time = 183 ms.

(10 row(s) affected)
Table 'TestTable'. Scan count 1, logical reads 1291, physical reads 10, read-ahead reads 399, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 516 ms,  elapsed time = 706 ms.

执行计划第二查询:


摘要:

第二个查询是使用 Col5上的索引按 newid()排序行，然后执行10次聚集索引搜索以获取输出值。

之所以能够获得性能提升，是因为 Col5上的索引比集群键窄，并且导致更少的读取。

感谢 Martin Smith的 pointing that out。