使用数据库构建高性能队列用于存储订单、通知和任务（转）

2022-08-04 14:39:19

英文原文: http://www.codeproject.com/Articles/110931/Building-High-Performance-Queue-in-Database-for-st

译文：http://www.oschina.net/translate/building-high-performance-queue-in-database-for-storing-orders

译者：几点人, beyondme, 漠天, petert, 赵亮-碧海情天, FGQ

引言

几乎在每个地方都能用到队列。在许多web站点里，比如其中的email和SMS都是使用队列来异步发送通知。电子商务网站都是使用队列来存储订单，处理订单以及实现订单的分发。工厂生产线的自动化系统也是使用队列来按某种顺序运行并发工作任务的。队列是使用很广泛的一种数据结构，它有时可以创建在数据库里，而不是使用类似于MSMQ那样的特定的队列技术创建。使用数据库技术来运行一个高性能且高可扩展性的队列对我们来说是一个巨大的挑战。当每天进入队列和从队列中提取的信息达到数百万行的时候，这个队列就很难维护了。我将向你展示在设计类似队列表时常犯的设计错误以及如何使用简单的数据库功能实现队列的最大性能和强大的可扩展性。

表的读写。在大负载下，入队列和出队列是相互影响而引起锁的竞争、事务死锁、IO超时等等。
当多个接收者试图从同一队列读数据时，它们随机地获取重复项，从而导致重复的处理过程。你需要在队列上实现一些高性能的行锁以至于并发接受器不会接收相同的数据项。
队列表需要以特定的顺序去存储行、以特定的顺序读取行数据，这是一个索引设计的挑战。尽管并不总是先进先出。间或顺序有较高的优先级，无论何时入栈都需要进行处理。
队列表需要以序列化的XML对象或者二进制形式存储，这带来了存储和索引重建的挑战。由于数据表包含文本和/或二进制，所以你不能在队列表上重建索引。因此队列表每天变的越来越慢，最终查询开始时间超时，最后你不得不关闭服务并重建索引。
出队列的过程中，一批行数据被选中、更新，然后重新处理。你需要一个"State"列定义数据项的状体。出队列时，你只选择特定状态的数据项。现在State只是一个包含PENDING、PROCESSING、PROCESSED、ARCHIVED等元素的集合。结果是你不能在"State"列上创建索引，原因是安全性差。队列中可以有成千上万行具有相同的状态。因此任何由扫面索引的出队列操作都会导致CPU和IO资源紧张以及锁竞争。
在出队列过程中，你不只是从表中移出行，原因是容易引起存储残片。进而，你需要重新订单/任务/通知N次以防止他们在第一次尝试中失败。这意味着行数据需要更长的存储周期、索引持续增长和出队列越来越慢。
你不得不归档以队列表中处理过的数据项到不同的表或者数据库，以保持主队列表的精简。这意味着需要移动大量的具有特殊状态的行到另一个数据库。大量的数据移动产生了的存储碎片，而表的高频度碎片整理降低入栈和出栈的性能。
你需要24X7的工作。你不可能关闭服务而归档大量行数据。这意味者在不影响入栈和出栈，你不得不持续的归档行数据。

如果您已实现这样的队列表，你可能已经遇到了以上挑战中的一个或者更多。本文将给你一个关于如何克服这些挑战的一些技巧，以及如何设计和维护一个高性能的队列表。

SQL Server典型队列

下面以常见的队列类型为例，看看在并发负载时的情况.

 1 CREATE TABLE [dbo].[QueueSlow](
 2     [QueueID] [int] IDENTITY(1,1) NOT NULL,
 3     [QueueDateTime] [datetime] NOT NULL,
 4     [Title] [nvarchar](255) NOT NULL,
 5     [Status] [int] NOT NULL,
 6     [TextData] [nvarchar](max) NOT NULL
 7 ) ON [PRIMARY]
 8 GO
 9 CREATE UNIQUE CLUSTERED INDEX [PK_QueueSlow] ON [dbo].[QueueSlow]
10 (
11     [QueueID] ASC
12 )
13 GO
14 CREATE NONCLUSTERED INDEX [IX_QuerySlow] ON [dbo].[QueueSlow]
15 (
16     [QueueDateTime] ASC,
17     [Status] ASC
18 )
19 INCLUDE ( [Title])
20 GO

该队列中使用QueueDateTime排序来模拟先进先出的队列结构 . QueueDateTime不一定非得是对象加入队列的时间，而是其将要被处理的开始时间于是保证了先进先出的次序关系. TextData字段是为了保存进行负载测试的大字段设计的.

表中使用QueueDateTime 的非聚簇索引以加速队列操作速度

首先，插入大约4万行大约500兆数据量的记录，其中每行的负载数大小各不相同.

 1 set nocount on
 2 declare @counter int
 3 set @counter = 1
 4 while @counter < @BatchSize
 5 begin
 6     insert into [QueueSlow] (QueueDateTime, Title, Status, TextData)
 7     select GETDATE(), ‘Item no: ‘ + CONVERT(varchar(10), @counter), 0,
 8         REPLICATE(‘X‘, RAND() * 16000)
 9  
10     set @counter = @counter + 1
11 end

接下来我们将一次性出队10个元素。在出队的时候，它会根据QueueDateTime 和Status = 0进行判断，它将会Status更新为1表示该元素正在被处理。当出队的时候我们并不会从缓存表中删除这行数据，因为我们想保证这些元素在处理失败的时候永远不会丢失。

 1 CREATE procedure [dbo].[DequeueSlow]
 2 AS
 3  
 4 set nocount on
 5  
 6 declare @BatchSize int
 7 set @BatchSize = 10
 8  
 9 declare @Batch table (QueueID int, QueueDateTime datetime, _
10     Title nvarchar(255), TextData nvarchar(max) )
11  
12 begin tran
13  
14 insert into @Batch
15 select Top (@BatchSize) QueueID, QueueDateTime, Title, TextData from QueueSlow
16 WITH (UPDLOCK, HOLDLOCK)
17 where Status = 0
18 order by QueueDateTime ASC
19  
20 declare @ItemsToUpdate int
21 set @ItemsToUpdate = @@ROWCOUNT
22  
23 update QueueSlow
24 SET Status = 1
25 WHERE QueueID IN (select QueueID from @Batch)
26 AND Status = 0
27  
28 if @@ROWCOUNT = @ItemsToUpdate
29 begin
30     commit tran
31     select * from @Batch
32     print ‘SUCCESS‘
33 end
34 else
35 begin
36     rollback tran
37     print ‘FAILED‘
38 end

上面的查询将会从QueueSlow 表中取出10行数据，然后存储在临时表中。紧接着改变选中的记录的状态以保证其他的会话不会再次提取数据。如果能够更新10行记录并且没有被其他的会话占有，那么久可以提交该事务，意味着将会将他们的状态标记为已完成，没有子过程进行调用。反之，其他的会话可以更新，事务将会拒绝被提交以保证事务的一致性

让我测量下IO性能:

1 set statistics IO on
2 exec dequeueslow

输出如下：

 1 Table ‘#3B75D760‘. Scan count 0, logical reads 112, physical reads 0, read-ahead reads 0,
 2     lob logical reads 83, lob physical reads 0, lob read-ahead reads 0.
 3 Table ‘QueueSlow‘. Scan count 1, logical reads 651, physical reads 0, read-ahead reads 0,
 4     lob logical reads 166, lob physical reads 0, lob read-ahead reads 166.
 5 Table ‘QueueSlow‘. Scan count 0, logical reads 906, physical reads 0, read-ahead reads 0,
 6     lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 7 Table ‘#3B75D760‘. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0,
 8     lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 9 Table ‘#3B75D760‘. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0,
10     lob logical reads 464, lob physical reads 0, lob read-ahead reads 0.

概括如下：

Total Logical Read = 1695
Total LOB Logical Read = 675
LOB Logical Read Count = 3

我们将用最快的方式进行标记看性能到底提升了多少。这里我们需要注意的是 LOB Logical Read和 LOB Logical Read 的访问次数得到改善。读三次数据是非常有必要的，因为我们需要重新载入数据。这很清楚的表明SQL Server没有必要读取大对象以满足查询。

经常产生出队和入队，使得许多的行被移除表进行归档，数据表逐渐变成碎片，你不能在线够重建聚族索引来消除碎片引文它有varchar(max)字段。因此，你不得不选择停止服务器重建索引，停止服务器是非常耗资源的

创建一个更快速的队列

第一，你必须降低比较过的逻辑读取。所以你只能将QueueSlow进行拆分为两张表-QueueMeta ,QueueData。QueueData只包含参与where子句的字段。他是一张很小的表只用于保存查询。使得SQL Server必须修正几行数据占据8K的页然后再运行，这样的表会比表QueueSlow运行的更快。

第二，你能够在线重建QueueMeta 的索引，当其事务还在进行的时候。这样的话，QueueMeta表的性能将被不会降低，你再也不用担心停止服务器进行索引重建了。

 1 CREATE TABLE [dbo].[QueueMeta](
 2     [QueueID] [int] IDENTITY(1,1) NOT NULL,
 3     [QueueDateTime] [datetime] NOT NULL,
 4     [Title] [nvarchar](255) NOT NULL,
 5     [Status] [int] NOT NULL,
 6  CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED
 7 (
 8     [QueueID] ASC
 9 )
10 GO
11 ALTER TABLE [dbo].[QueueMeta] ADD  CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED
12 (
13     [QueueID] ASC
14 )
15 GO
16 CREATE NONCLUSTERED INDEX [IX_QueueDateTime] ON [dbo].[QueueMeta]
17 (
18     [QueueDateTime] ASC,
19     [Status] ASC
20 )
21 INCLUDE ( [Title])

这个表保留了出现在搜索查询中的所有的字段。其他所有和有效负载相关的字段被移到了QueueData表。

 1 CREATE TABLE [dbo].[QueueData](
 2     [QueueID] [int] NOT NULL,
 3     [TextData] [nvarchar](max) NOT NULL
 4 ) ON [PRIMARY]
 5  
 6 GO
 7 CREATE UNIQUE NONCLUSTERED INDEX [IX_QueueData] ON [dbo].[QueueData]
 8 (
 9     [QueueID] ASC
10 )
11 GO

在这个表中没有聚类索引项。“Dequeue”过程先被简单的修改后去执行QueueMeta表上的查询操作，然后从QueueData表中选择有效负载。

 1 CREATE procedure [dbo].[Dequeue]
 2 AS
 3  
 4 set nocount on
 5  
 6 declare @BatchSize int
 7 set @BatchSize = 10
 8  
 9 declare @Batch table (QueueID int, QueueDateTime datetime, Title nvarchar(255))
10  
11 begin tran
12  
13 insert into @Batch
14 select Top (@BatchSize) QueueID, QueueDateTime, Title from QueueMeta
15 WITH (UPDLOCK, HOLDLOCK)
16 where Status = 0
17 order by QueueDateTime ASC
18  
19 declare @ItemsToUpdate int
20 set @ItemsToUpdate = @@ROWCOUNT
21  
22 update QueueMeta
23 SET Status = 1
24 WHERE QueueID IN (select QueueID from @Batch)
25 AND Status = 0
26  
27 if @@ROWCOUNT = @ItemsToUpdate
28 begin
29     commit tran
30     select b.*, q.TextData from @Batch b
31     inner join QueueData q on q.QueueID = b.QueueID
32     print ‘SUCCESS‘
33 end
34 else
35 begin
36     rollback tran
37     print ‘FAILED‘
38 end

当把在QueueSlow提取的相同数据填充到QueueMeta和QueueData表中，然后重建两个表的索引并作对比，分析发现有明显的提高：

Total Logical Read = 1546 (vs 1695)
Total LOB Read = 380 (vs 675)
LOB Read Count = 1 (vs 3)

你会看到逻辑读的次数低于149；LOB读取低于295；LOB读取计数为1，以上数据正式我们期待的。

负载下的性能比较

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

让我们来分析这些重要的计数器，看看有哪些改进：

页面分割/秒 – 更快的解决方案有比较低的页面分割，和较慢的解决方案相比几乎没有。这是因为在插入的过程中，有时候一行无法放下而部分填充，因此需要分割成新的一页。你可以冲这里了解更多有关页面分割的信息。
事务/秒 – 我们从金钱中获得更多价值。每秒越多的事务，就会有越多的队列操作随着出队列发生，它显示出了快速队列操作的性能相比于慢的是更好的选项。
锁超时/秒 - 这说明有多少个查询在等待某个对象上的锁，并最终放弃了，因为它没有及时得到该锁。该值越高，表示数据库读取性能越差。你要尽量保持该值接近零。上述结果表明锁定超时在快速队列的数目小于缓慢队列。
批量请求/秒 - 显示每秒执行SELECT查询多少次。它显示了这两种解决方案都执行的相同次数的SELECT操作，由于更快的解决方案是从多个表读取的。所以，与较慢的解解方案相比，出队列的存储过程没有显著优化。

这不仅仅是有更好的性能，最大的好处是你可以在QueueMeta表在线运行INDEX DEFRAG，从而阻止Queue逐渐放缓。

在SQL Server 2005,2008里实现的最快队列

SQL Server 2005给UPDATE、INSERT和DELETE语句引入了OUTPUT子句。这么做就可以让你通过一个查询就可以得到由insert,update和delete更改的哪些数据行。你不需要先查询一些数据行，然后对它们进行加锁，接着再更新这些数据行，最后才返回这些数据行。你只需一条语句就可以完成这些功能-更新并返回这些数据行。

下面从队列获取信息的过程是修改过的：

 1 alter procedure [dbo].[DequeueFast]
 2 AS
 3  
 4 set nocount on
 5  
 6 declare @BatchSize int
 7 set @BatchSize = 100
 8  
 9 update top(@BatchSize) QueueMeta WITH (UPDLOCK, READPAST)
10 SET Status = 1
11 OUTPUT inserted.QueueID, inserted.QueueDateTime, inserted.Title, qd.TextData
12 FROM QueueMeta qm
13 INNER JOIN QueueData qd
14     ON qm.QueueID = qd.QueueID
15 WHERE Status = 0

一行UPDATE语句就可以做到我们到现在为止看到的Dequeue存储过程所做的一切。同时IO统计状态也得到了很大的改善：

1 Table ‘QueueMeta‘. Scan count 1, logical reads 522, physical reads 0,
2   read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
3 Table ‘QueueData‘. Scan count 0, logical reads 31, physical reads 0,
4   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服务的系统，而且也没有非工作高峰时段，那么你就需要接连不断地运行小批量归档，这样的小批量归档之间稍有时延。不过，在从队列提取消息的时候，你不能删除队列表中的消息数据，因为删除操作是费时费力的操作。如果你删除队列中的数据，那么从队列提取消息的过程将会慢很多。不过，你可以通过另一个后台任务来删除队列里已经处理过的消息数据，这么做就不会降低从队列提取消息的性能。此外，如果要求实现的是一个可靠性很高的队列，那么你就不能在从队列中提取消息的时候删除消息数据。如果处理队列消息数据的进程由于某种原因而失效，而且重新运行消息数据插入后仍然不能把这些消息数据插入到队列表里，那么这些消息数据就会永远丢失了。有时候，你需要密切关注消息队列，确保已经被提取的消息在某个时间段内得到处理。如果没有得到处理的话，那么就需要把这些消息放在队列的最前端，这样才能保证处理进程能提取到这些消息。正是由于以上这些原因，最好在从队列提取消息的时候保持这些消息数据不变，只是更改这些消息数据行的状态。

结论

你的订单处理系统、任务执行系统或通知系统的性能和可靠性，取决于你如何设计你的队列。由于这些直接影响客户满意度并最终直戳你的底线，所以当你要建立你的队列时，花足够的时间做出正确的设计决策是很重要的。否则，随着时间的推移，它会成为一个债务，在你付出了失去业务和很高的资源消耗的代价之后最终仍然不得不重新设计队列。

使用数据库构建高性能队列用于存储订单、通知和任务（转）,布布扣,bubuko.com