使用 MSSQL 的随机函数加快数据抽取速度

介绍

在从数据库中抽取数据的过程中，有时需要随机获取一部分数据。在 SQL Server 中，我们可以使用随机函数 RAND() 来实现这个功能。而在处理大量数据时，随机函数的效率则成为了一个问题。本文将探讨如何使用 MSSQL 的随机函数加快数据抽取速度。

随机函数

RAND()

在 SQL Server 中，我们可以使用随机函数 RAND() 来获取一个随机数，其基本语法为：

SELECT RAND()

返回值为 0 到 1 之间的伪随机数。

NEWID()

另外一个用于生成随机数的函数是 NEWID()，其生成一个唯一标识符 GUID，基本语法为：

SELECT NEWID()

返回一个包含 36 个字符的 GUID。

问题

然而，在处理大量数据时，使用随机函数可以导致性能问题。下面的代码演示了在一张包含 1000000 条数据的表中，使用按照 RAND() 的方式获取 10000 条随机数据所需时间：

SELECT TOP 10000 * 

FROM MyTable
ORDER BY RAND()

执行时间长达十几秒甚至更久，导致查询性能严重下降。

解决方案

使用 CHECKSUM

一种优化方式是使用 CHECKSUM 函数替代 RAND()。CHECKSUM 函数可以根据给定的列或表达式生成一个整数值，基本语法为：

SELECT CHECKSUM(NEWID())

我们可以将 CHECKSUM 函数与 ROW_NUMBER 函数一起使用，以避免使用 RAND()。

WITH CTE AS

(
    SELECT *, ROW_NUMBER() OVER(ORDER BY CHECKSUM(NEWID())) AS RN
    FROM MyTable
)
SELECT *
FROM CTE
WHERE RN <= 10000

在这种情况下，使用 CHECKSUM 的查询效率要比使用 RAND() 高得多。

使用 TABLESAMPLE

另一个解决方案是使用 TABLESAMPLE 关键字。TABLESAMPLE 并不是真正的随机函数，它是从一个表中选取一个样本集，基本语法为：

SELECT * 

FROM MyTable
TABLESAMPLE (10 PERCENT)

这个查询将返回从 MyTable 表中选取 10% 的随机数据。

需要注意的是，在大型表中使用 TABLESAMPLE 可能会导致采样偏差，因此我们需要结合其他方法来进行数据抽取。

使用视图或临时表

如果我们需要多次从同一张表中抽取随机数据，建立一个视图或者临时表可以提高查询效率。

CREATE VIEW RandomView AS 

SELECT TOP 10000 * 
FROM MyTable
ORDER BY CHECKSUM(NEWID())
SELECT * FROM RandomView

这将创建一个名为 RandomView 的视图，其中包含 MyTable 表中的随机 10000 条数据。在之后的查询中，我们可以直接使用该视图。

SELECT * FROM RandomView

结论

在数据抽取过程中，使用随机函数可以方便地获取一部分数据。然而，在处理大量数据时，使用随机函数可能会导致性能问题。我们可以使用 CHECKSUM 函数、TABLESAMPLE 关键字、视图或临时表等方式来优化查询效率。