tsql – 按性能划分的随机顺序

通过随机顺序获得前n行的最佳方法是什么？
我使用的查询如下：

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

上述查询中的问题是随着表大小的增加它会继续变慢.它将始终执行完整的聚簇索引扫描,以按随机顺序查找前(10)行.

还有其他更好的方法吗？

我已经测试了这个并且在更改查询方面获得了更好的性

我在测试中使用的表的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()对行进行排序,然后执行Clustered Index Seek 10次以获取输出值.

性能提升是因为Col5上的索引比群集密钥窄,导致读取次数减少.

感谢Martin Smith pointing that out.