在数据库中构建高性能队列以存储订单、通知、任务

引言

我们到处都有队列。大多数网站都有用于异步发送电子邮件和短信等通知的队列。电子商务网站有用于存储、处理和分发订单的队列。工厂装配线自动化系统有用于以特定顺序并行运行任务的队列。队列是一种广泛使用的数据结构，有时必须在数据库中创建，而不是使用 MSMQ 等专用队列技术。使用数据库技术运行高性能和高度可扩展的队列是一个巨大的挑战，当队列每天开始有数百万行入队和出队时，很难维护。让我向您展示在设计类似队列的表时犯的一些常见设计错误，以及如何从使用简单数据库功能实现的队列中获得最大性能和可扩展性。

首先让我们确定您在此类队列表中所面临的挑战

• 该表既可读又可写。因此，在高负载下，入队和出队会相互影响，导致锁争用、事务死锁、IO 超时等。
• 当多个接收器尝试从同一个队列读取时，它们会随机获得重复的项目，从而导致重复处理。您需要对队列实现某种高性能行锁，以确保并发接收器永远不会选中相同的项目。
• 队列表需要以特定顺序存储行并以特定顺序读取，这是一个索引设计挑战。它不总是先进先出。有时订单具有更高的优先级，无论何时入队都需要处理。
• 队列表需要以 XML 或二进制形式存储序列化对象，这成为存储和索引重建的挑战。您无法重建队列表上的索引，因为它包含文本和/或二进制字段。因此，表每天都会变得越来越慢，最终查询开始超时，直到您停机并重建索引。
• 在出队期间，会选择一批行，更新它们，然后返回进行处理。您有一个“State”列，用于定义项目的状态。在出队期间，您选择特定状态的项目。现在 State 只有一小组值，例如 PENDING、PROCESSING、PROCESSED、ARCHIVED。因此，您无法在“State”列上创建索引，因为这无法提供足够的选择性。可能有数千行具有相同的状态。结果，任何出队操作都会导致聚簇索引扫描，这既耗费 CPU 又耗费 IO，并产生锁争用。
• 在出队期间，您不能只从表中删除行，因为这会导致表碎片。此外，如果订单/作业/通知第一次尝试失败，您需要重试 N 次。这意味着行存储时间更长，索引不断增长，出队速度日益减慢。
• 您必须将已处理的项目从队列表存档到不同的表或数据库，以使主队列表保持较小。这意味着将大量具有特定状态的行移动到另一个数据库。这种大量数据删除会使表高度碎片化，导致队列/出队性能不佳。
• 您有 24x7 全天候业务。您没有维护窗口可以停机并存档大量行。这意味着您必须持续存档行，而不会影响生产队列-出队流量。

如果您已实现此类队列表，您可能已遇到上述一个或多个挑战。让我给您一些克服这些挑战以及如何设计和维护高性能队列表的提示。

在 SQL Server 中构建典型队列

让我们以一个典型的队列表为例，我们将看看它在并发负载下的表现如何。

CREATE TABLE [dbo].[QueueSlow](
    [QueueID] [int] IDENTITY(1,1) NOT NULL,
    [QueueDateTime] [datetime] NOT NULL,
    [Title] [nvarchar](255) NOT NULL,
    [Status] [int] NOT NULL,
    [TextData] [nvarchar](max) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PK_QueueSlow] ON [dbo].[QueueSlow]
(
    [QueueID] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX_QuerySlow] ON [dbo].[QueueSlow]
(
    [QueueDateTime] ASC,
    [Status] ASC
)
INCLUDE ( [Title])
GO

在此队列表中，项目使用 QueueDateTime 作为排序顺序出队，以模拟先进先出或优先级队列。QueueDateTime 不一定是项目入队的时间。它是项目应该处理的时间。因此，时间最早的项目会优先被选中。TextData 是用于存储有效负载的大字符字段。

该表在 QueueDateTime 上有一个非聚簇索引，以便在出队期间使用 QueueDateTime 进行排序更快。

首先，让我们用大约 40K 行、500MB 数据填充此表，其中每行具有不同大小的有效负载。

set nocount on
declare @counter int
set @counter = 1
while @counter < @BatchSize
begin
    insert into [QueueSlow] (QueueDateTime, Title, Status, TextData)
    select GETDATE(), 'Item no: ' + CONVERT(varchar(10), @counter), 0,
        REPLICATE('X', RAND() * 16000)

    set @counter = @counter + 1
end

然后我们将一次出队 10 个项目。在出队期间，它将根据 QueueDateTime 和 Status = 0 选取前 10 个项目，然后它将 Status 更新为 1 以将项目标记为正在处理。我们不会在出队期间从队列表中删除行，因为我们希望确保如果接收它们的应用程序未能处理它们，项目不会永远丢失。

CREATE procedure [dbo].[DequeueSlow]
AS

set nocount on

declare @BatchSize int
set @BatchSize = 10

declare @Batch table (QueueID int, QueueDateTime datetime, _
	Title nvarchar(255), TextData nvarchar(max) )

begin tran

insert into @Batch
select Top (@BatchSize) QueueID, QueueDateTime, Title, TextData from QueueSlow
WITH (UPDLOCK, HOLDLOCK)
where Status = 0
order by QueueDateTime ASC

declare @ItemsToUpdate int
set @ItemsToUpdate = @@ROWCOUNT

update QueueSlow
SET Status = 1
WHERE QueueID IN (select QueueID from @Batch)
AND Status = 0

if @@ROWCOUNT = @ItemsToUpdate
begin
    commit tran
    select * from @Batch
    print 'SUCCESS'
end
else
begin
    rollback tran
    print 'FAILED'
end

以上查询首先从 QueueSlow 表中选择 10 行，然后将其存储在一个临时表变量中。然后它更改所选行的状态，以确保没有其他人再次获取这些行。如果它能够成功更新相同的 10 行，并且在此期间没有其他人获取它们，那么它会提交事务，这会使这些项目标记为已完成，并且不会在后续调用中被选中。但是，如果它无法使用正确的状态更新它选中的相同 10 行，则在此期间其他人已获取它们，并且它会中止以保持一致性。

让我们测量 IO 性能

set statistics IO on
exec dequeueslow 

输出结果如下

Table '#3B75D760'. Scan count 0, logical reads 112, physical reads 0, read-ahead reads 0,
	lob logical reads 83, lob physical reads 0, lob read-ahead reads 0.
Table 'QueueSlow'. Scan count 1, logical reads 651, physical reads 0, read-ahead reads 0,
	lob logical reads 166, lob physical reads 0, lob read-ahead reads 166.
Table 'QueueSlow'. Scan count 0, logical reads 906, physical reads 0, read-ahead reads 0,
	lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#3B75D760'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0,
	lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#3B75D760'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0,
	lob logical reads 464, lob physical reads 0, lob read-ahead reads 0.

让我们总结一下

• 总逻辑读取 = 1695
• 总 LOB 逻辑读取 = 675
• LOB 逻辑读取计数 = 3

我们将把这些与更快的解决方案进行比较，看看我们获得了多少改进。需要注意的是高 LOB 逻辑读取以及执行 LOB 逻辑读取的次数。没有理由读取大对象三次，因为我们只需要加载它们一次。这清楚地表明 SQL Server 不必要地读取大对象以满足查询。

随着入队和出队发生，并且行从表中移出以进行存档，表会日复一日地碎片化。您无法在线重建聚簇索引以消除碎片，因为它有一个 varchar(max) 字段。所以，您别无选择，只能停机，然后重建索引。停机总是昂贵的，即使您没有 24x7 系统。

构建更快的队列

首先，您需要减少高逻辑读取。为此，您需要将 QueueSlow 表拆分为两个表 – QueueMeta 和 QueueData。QueueMeta 只包含参与 WHERE 子句的字段。它是一个轻量级表，只用于保存搜索查询中使用的字段。因此，SQL Server 能够将多行放入一个 8K 页面中，并且遍历此表将比遍历 QueueSlow 表更快，因为 QueueSlow 表中每个页面只包含少量行，有时由于有效负载较大，单行跨多个页面。

其次，您将能够在线重建 QueueMeta 表上的索引，同时生产流量仍在访问该表。这样，QueueMeta 表的性能不会下降，您也不再需要停机。

CREATE TABLE [dbo].[QueueMeta](
    [QueueID] [int] IDENTITY(1,1) NOT NULL,
    [QueueDateTime] [datetime] NOT NULL,
    [Title] [nvarchar](255) NOT NULL,
    [Status] [int] NOT NULL,
 CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED
(
    [QueueID] ASC
)
GO
ALTER TABLE [dbo].[QueueMeta] ADD  CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED
(
    [QueueID] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX_QueueDateTime] ON [dbo].[QueueMeta]
(
    [QueueDateTime] ASC,
    [Status] ASC
)
INCLUDE ( [Title]) 

此表包含搜索查询中出现的所有字段。所有其他与有效负载相关的字段都已移动到 QueueData 表。

CREATE TABLE [dbo].[QueueData](
    [QueueID] [int] NOT NULL,
    [TextData] [nvarchar](max) NOT NULL
) ON [PRIMARY]

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_QueueData] ON [dbo].[QueueData]
(
    [QueueID] ASC
)
GO

此表上没有聚簇索引。

Dequeue 存储过程略有修改，首先在 QueueMeta 表上执行查询，然后从 QueueData 表选择有效负载。

CREATE procedure [dbo].[Dequeue]
AS

set nocount on

declare @BatchSize int
set @BatchSize = 10

declare @Batch table (QueueID int, QueueDateTime datetime, Title nvarchar(255))

begin tran

insert into @Batch
select Top (@BatchSize) QueueID, QueueDateTime, Title from QueueMeta
WITH (UPDLOCK, HOLDLOCK)
where Status = 0
order by QueueDateTime ASC

declare @ItemsToUpdate int
set @ItemsToUpdate = @@ROWCOUNT

update QueueMeta
SET Status = 1
WHERE QueueID IN (select QueueID from @Batch)
AND Status = 0

if @@ROWCOUNT = @ItemsToUpdate
begin
    commit tran
    select b.*, q.TextData from @Batch b
    inner join QueueData q on q.QueueID = b.QueueID
    print 'SUCCESS'
end
else
begin
    rollback tran
    print 'FAILED'
end

当我用 QueueSlow 中的完全相同的数据填充 QueueMeta 和 QueueData 并在两个表上重建索引并进行比较时，我可以看到一些显著的改进

• 总逻辑读取 = 1546（对比 1695）
• 总 LOB 读取 = 380（对比 675）
• LOB 读取计数 = 1（对比 3）

在这里您看到逻辑读取次数减少了 149 次，LOB 读取减少了 295 次。LOB 读取计数只有 1 次，这正是我们所期望的。

比较负载下的性能

当我模拟并发入队和出队并测量性能计数器时，结果如下所示

慢队列	快队列

让我们研究重要的计数器并查看改进

• 页面拆分/秒 – 更快的解决方案具有更低的页面拆分，与较慢的解决方案相比几乎没有。这是因为在插入期间，有时一行无法适应部分填充的页面，因此需要拆分成新页面。您可以从此处阅读有关页面拆分的更多信息。
• 每秒事务数 – 我们物超所值。每秒事务数越多，出队进行时发生的队列操作就越多。它表明快速队列操作的性能优于较慢的选项。
• 锁超时/秒 – 它显示有多少查询等待某个对象的锁并最终放弃，因为它没有及时获得锁。值越高，您从数据库获得的性能越差。您必须尝试使其接近零。以上结果显示，快速队列中的锁超时次数少于较慢的解决方案。
• 批处理请求/秒 – 显示每秒执行了多少 SELECT 查询。它显示两种解决方案执行的 SELECT 数相同，尽管更快的解决方案是从多个表读取的。因此，较慢解决方案上的出队存储过程不是显著最优的。

它不仅性能更好，最大的好处是您可以在 QueueMeta 表上在线运行 INDEX DEFRAG ，从而防止队列逐渐变慢。

SQL Server 2005、2008 中最快的队列

SQL Server 2005 在 UPDATE、INSERT 和 DELETE 语句中引入了 OUTPUT 子句。它允许您从单个查询中获取受 insert、update、delete 影响的行。您不必先选择一些行，然后锁定它们，然后更新它们，然后返回它们。您可以一气呵成——更新并返回。

这是更改后的出队过程

alter procedure [dbo].[DequeueFast]
AS

set nocount on

declare @BatchSize int
set @BatchSize = 100

update top(@BatchSize) QueueMeta WITH (UPDLOCK, READPAST)
SET Status = 1
OUTPUT inserted.QueueID, inserted.QueueDateTime, inserted.Title, qd.TextData
FROM QueueMeta qm
INNER JOIN QueueData qd
    ON qm.QueueID = qd.QueueID
WHERE Status = 0

这条单行 UPDATE 语句完成了我们迄今为止所见的所有 Dequeue 过程所做的一切。IO 统计数据也令人印象深刻

Table 'QueueMeta'. Scan count 1, logical reads 522, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'QueueData'. Scan count 0, logical reads 31, physical reads 0,
  read-ahead reads 0, lob logical reads 56, lob physical reads 0, lob read-ahead reads 0.

• 总逻辑读取 = 553
• LOB 逻辑读取 = 56

它至少比更快的解决方案的 IO 密集度低 3 倍。

我在这里使用了特殊的锁定策略 – READPAST。这表示如果它发现某些行被锁定，它不会等待行解锁。它只会忽略它们。由于我们没有执行 SELECT 然后执行 UPDATE，因此这里无需使用 HOLDLOCK。这是性能更好的原因之一。

队列表的存档策略

当您将记录排队到队列表中时，它会不断增长。您必须确保队列表保持在合理的大小，以便备份和索引重建不会花费太长时间。有两种存档行的方法——全天候连续小批量存档或在非高峰期大批量存档。如果您有 24x7 系统，并且没有非高峰期，那么您需要以较小的批量连续存档，并带有一些延迟。但是，您不应该在出队期间从队列表中删除行，因为删除是一个昂贵的操作。它会使出队变慢。相反，您应该在另一个后台作业中删除队列中已处理的项目，这样就不会影响出队性能。此外，在可靠的队列中，您不能在出队期间删除行。如果处理项目的进程由于某种原因失败，并且没有再次将它们排队以进行重试，那么项目将永远丢失。有时您需要密切关注队列，以确保在一定时间内处理已选取进行处理的项目。如果不是，那么需要将它们发送到队列的前面，以便再次选取进行处理。由于所有这些原因，最好在出队期间将项目保留在队列中，只需更新它们的状态即可。

结论

您的订单处理系统、任务执行系统或通知系统的性能和可靠性取决于您设计队列的好坏。由于这些直接影响客户满意度并最终影响您的底线，因此在构建队列时花足够的时间做出正确的设计决策非常重要。否则，随着时间的推移，它会成为一种负担，您最终将不得不以失去业务和高资源投入为代价重新设计队列。

历史

• 2010 年 9 月 18 日：初次发布