利用数据库存储订单、通知和任务，构建高性能队列
原文地址：http://www.codeproject.com/Articles/110931/Building-High-Performance-Queue-in-Database-for-st
作者：chszs，转载需注明。博客主页：http://blog.csdn.net/chszs
引言
到处都有队列。很多Web网站，经常可以看到使用队列来异步发送通知，比如email和SMS。电子商务网站常常使用队列来存储订单、处理订单以及实现订单的分发。工厂生产线的自动化系统也经常使用队列并行处理任务。队列是一种广泛使用的数据结构，有时它必须创建到数据库里，而不是使用专门的队列技术（比如MSMQ）。使用数据库技术来运行一个高性能且高可扩展性的队列对我们来说是一个巨大的挑战。而且当每天进出队列的记录达到数百万行时，队列的维护是很困难的。下面我将向你展示在设计类似队列表时常犯的设计错误以及如何使用简单的数据库功能实现队列的最大性能和扩展性。
首先我们需要清楚在设计队列表时会遇到哪些挑战：
1）表的读写。
由于入队列和出队列是相互影响的，在高负载下可能会导致锁竞争、事务死锁、IO超时等等。
2）当多个接收者试图从同一队列读数据时，它们会随机地获取重复项，因而导致重复的处理过程。
你需要在队列上实现一些高性能的行锁定，以便让并发接受器不会接收相同的数据项。
3）队列表需要以某种顺序存储行以及以某种顺序读取行，这使得设计索引很棘手。
队列表并不总是遵守先进先出的，有时候顺序中的消息带有更高的优先级，无论这个消息是否入队列都要先处理它。
4）队列表需要以XML或二进制的形式序列化对象，这使得存储和重建索引很麻烦。
你不能在队列表中重建索引，因为它包含了文本或二进制字段。因此，每过一天，数据表会变得越来越慢，最后查询会超时，你不得不关闭服务并重建索引。
5）出队列的过程中，一批行数据被选中、被更新，然后返回数据。你需要一个"State"(状态)列来定义数据项的状态。出队列时，你只需选择某个状态的数据项。现在状态只有几种类型：PENDING(待定)、PROCESSING(处理中)、PROCESSED(已处理)、ARCHIVED(存档)。你不能在状态列上创建索引，因为不能提供足够的选择性，具有相同的状态的数据行有成千上万。因此，任何出队列操作都会导致集群的索引被重新扫描，这属于CPU和IO密集型操作，会产生锁竞争。
6）在出队列的过程中，你不能仅仅移除队列表的相关行，因为这很容易导致数据表产生存储碎片。而且，你还需要重新处理订单/任务/通知做N次操作以防止这些操作在第一次中失败。这意味着存储行数据需要更长的时间、索引会持续增长以及出队列越来越慢。
7）你必须归从入队表把处理过的数据项归档到不同的数据表或数据库，以保持主队列表的精简。这意味着需要移动大量的带有特定状态的数据行到另一个数据库。如此大的数据移动会频繁产生存储碎片，以至于降低入队列和出队列的性能。
8）你有24×7不间断的业务。你不能停止服务再归档大量的行数据。这意味者你必须在不影响入栈和出栈通信的情况下持续的归档行数据。
如果您已实现这样的队列表，你可能已经遇到了以上一个或多个的麻烦。本文将教你一些如何应对这些挑战的技巧，以及如何设计和维护一个高性能的队列表。
在SQL Server创建一个典型的队列
下面我们创建一个典型的队列表为例，看看它在并发负载时的是怎样工作的。
CREATETABLE [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
CREATEUNIQUE CLUSTERED INDEX [PK_QueueSlow] ON [dbo].[QueueSlow]
(
[QueueID] ASC
)
GO
CREATENONCLUSTERED INDEX [IX_QuerySlow] ON [dbo].[QueueSlow]
(
[QueueDateTime] ASC,
[Status] ASC
)
INCLUDE( [Title])
GO
在队列表中，出队列使用了QueueDateTime作为排序顺序，以此模拟先进先出的算法或队列的优先级。QueueDateTime不一定是对象入队列的时间，而是对象被处理的开始时间。因此，时间最早的数据行会先被选出。TextData字段是一个很大的字符串字段，它存储有效载荷。
在此表中使用QueueDateTime字段作为非聚集索引，目的是在出队列期间使得使用QueueDateTime字段排序更快。
首先，我们先对这个表填充数据，插入4万行约500兆的数据，其中每行的有效载荷的大小互不相同。
setnocount 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，表示该数据行正在被处理。在出队列的期间，我们不会从队列表中删除这些数据行，因为我们要确保这些数据行在终端接收失败时永远不会丢失。
CREATEprocedure [dbo].[DequeueSlow]
AS
setnocount on
declare@BatchSize int
set@BatchSize = 10
declare@Batch table (QueueID int, QueueDateTime datetime, _
Title nvarchar(255), TextDatanvarchar(max) )
begintran
insertinto @Batch
selectTop (@BatchSize) QueueID, QueueDateTime, Title, TextData from QueueSlow
WITH(UPDLOCK, HOLDLOCK)
whereStatus = 0
orderby QueueDateTime ASC
declare@ItemsToUpdate int
set@ItemsToUpdate = @@ROWCOUNT
updateQueueSlow
SETStatus = 1
WHEREQueueID IN (select QueueID from @Batch)
ANDStatus = 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性能：
setstatistics IO on
execdequeueslow
输出如下：
Table'#3B75D760'. Scan count 0, logical reads 112, physical reads 0, read-aheadreads 0,
lob logical reads 83, lob physical reads0, lob read-ahead reads 0.
Table'QueueSlow'. Scan count 1, logical reads 651, physical reads 0, read-aheadreads 0,
lob logical reads 166, lob physicalreads 0, lob read-ahead reads 166.
Table'QueueSlow'. Scan count 0, logical reads 906, physical reads 0, read-aheadreads 0,
lob logical reads 0, lob physical reads0, lob read-ahead reads 0.
Table'#3B75D760'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads0,
lob logical reads 0, lob physical reads0, lob read-ahead reads 0.
Table'#3B75D760'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads0,
lob logical reads 464, lob physicalreads 0, lob read-ahead reads 0.
概括如下：
1）逻辑读总数（Total Logical Read）= 1695
2）LOB逻辑读总数（Total LOB LogicalRead）= 675
3）LOB逻辑读计数（LOB Logical ReadCount）= 3
我们用更快的解决方案进行比较，看应该怎样提升性能。这里我们要注意， LOB Logical Read很高，以及 LOB Logical Read的次数被执行了。读入大对象没有理由只载入一次，这清楚的说明SQL Server没有必要读取大对象以满足查询。
由于出队和入队经常发生，有很多行会被移出表进行归档，数据表会不断产生碎片。你不能在线重建聚集索引来消除碎片，因为它包含了varchar(max)字段。因此，你不得不选择停止服务重建索引，停止服务的代价是非常高的，即使你不需要提供24×7的服务。
创建一个更快的队列
首先你必须减少高逻辑读。要做到这一点，你需要把QueueSlow表拆分为两张表——QueueMeta表和QueueData表。QueueData表只包含where子句中包含的字段。他是一张很小的表只用于保存搜索查询。这样，SQL Server就能够把数行记录放入8K的页并运行，这样的表运行更快，因为原先的QueueSlow表可能每页只包含少量的行记录，甚至可能会出现某个行记录跨越多页的情况，以至于很慢。
其次，你可以在线重建QueueMeta表的索引，即使是其事务还在进行的时候。这样的话，QueueMeta表的性能不会降低，你再也无需担心要停止服务进行索引重建了。
CREATETABLE [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
ALTERTABLE [dbo].[QueueMeta] ADD CONSTRAINT[PK_Queue] PRIMARY KEY CLUSTERED
(
[QueueID] ASC
)
GO
CREATENONCLUSTERED INDEX [IX_QueueDateTime] ON [dbo].[QueueMeta]
(
[QueueDateTime] ASC,
[Status] ASC
)
INCLUDE( [Title])
这个表保留了出现在搜索查询中所需的所有字段。其它所有与有效负载相关的字段都移到了QueueData表。
CREATETABLE [dbo].[QueueData](
[QueueID] [int] NOT NULL,
[TextData] [nvarchar](max) NOT NULL
)ON [PRIMARY]
GO
CREATEUNIQUE NONCLUSTERED INDEX [IX_QueueData] ON [dbo].[QueueData]
(
[QueueID] ASC
)
GO
在这个表中没有聚类索引项。
出队列过程会首先在QueueMeta表执行查询，并作轻微修改，然后从QueueData表中选取有效负载。
CREATEprocedure [dbo].[Dequeue]
AS
setnocount on
declare@BatchSize int
set@BatchSize = 10
declare@Batch table (QueueID int, QueueDateTime datetime, Title nvarchar(255))
begintran
insertinto @Batch
selectTop (@BatchSize) QueueID, QueueDateTime, Title from QueueMeta
WITH(UPDLOCK, HOLDLOCK)
whereStatus = 0
orderby QueueDateTime ASC
declare@ItemsToUpdate int
set@ItemsToUpdate = @@ROWCOUNT
updateQueueMeta
SETStatus = 1
WHEREQueueID IN (select QueueID from @Batch)
ANDStatus = 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表时，重建两个表的索引并作对比，分析发现性能有明显的提高：
1）逻辑读总数（Total Logical Read）= 1546 (vs 1695)
2）LOB逻辑读总数（Total LOBLogical Read）= 380 (vs 675)
3）LOB逻辑读计数（LOBLogical Read Count）= 1 (vs 3)
你可以看到，逻辑读的次数减少了149次；LOB读的次数减少了295次；LOB读计数为1，这样的性能正是我们所期待的。
负载下的性能比较
当我模拟并发入队列和出队列，并测量性能计数器，结果如下所示：
让我们来分析这些重要的计数器，看看有哪些改进：
1）页面分割/秒
上面快的解决方案有比较低的页面分割，而上面慢的解决方案几乎没有页面分割。这是因为在插入期间，有时页面的剩余空间无法存放完某一行记录而只能部分填充，因此需要分割成新的一页。
可以从这里了解更多有关页面分割的信息。
2）事务/秒
我们从投资上获得了更多的价值。每秒有更多的事务处理，就意味着有更多的入队列操作随着出队列发生。它显示出上面快的解决方案的队列操作的性能更好。
3）锁超时/秒
它说明有多少个查询在等待某个对象解锁并最终放弃，因为它没有及时得到该锁。这个值越高，表示从数据库获取的性能越差。你必须尽量保持该值接近零。上述结果表明锁超时在上面快的解决方案的数值更小。
4）批处理请求/秒
它显示了每秒执行SELECT查询有多少次。它显示了这两种解决方案都执行的相同次数的SELECT操作，尽管上面快的解决方案是从多个表中执行SELECT查询的。所以，与上面慢的解解方案相比，出队列的存储过程没有显著优化。
这不仅仅是有更好的性能，最大的好处是你可以在线在QueueMeta表上运行INDEX DEFRAG，从而防止了Queue性能的逐渐下降。
在SQL Server 2005,2008里实现最快的队列
SQL Server 2005在UPDATE、INSERT和DELETE等语句中引入了OUTPUT子句。它允许获取通过单个查询，获取insert、update和delete更改了哪些数据行。这样你不需要先查询一些数据行，然后对它们进行加锁，接着再更新这些数据行，最后才返回这些数据行。你只需一条语句就可以完成这些功能——更新并返回这些数据行。
下面从队列获取信息的过程是修改过的：
alterprocedure [dbo].[DequeueFast]
AS
setnocount on
declare@BatchSize int
set@BatchSize = 100
updatetop(@BatchSize) QueueMeta WITH (UPDLOCK, READPAST)
SETStatus = 1
OUTPUTinserted.QueueID, inserted.QueueDateTime, inserted.Title, qd.TextData
FROMQueueMeta qm
INNERJOIN QueueData qd
ON qm.QueueID = qd.QueueID
WHEREStatus = 0
一行UPDATE语句就可以做到我们所看到的Dequeue存储过程所做的一切。同时IO统计状态也得到了很大的改善：
Table'QueueMeta'. Scan count 1, logical reads 522, physical reads 0,
read-ahead reads 0, lob logical reads 0, lobphysical 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, lobphysical reads 0, lob read-ahead reads 0.
1）逻辑读总数（Total Logical Read）= 553
2）LOB逻辑读总数（Total LOB LogicalRead）= 56
与之前快的解决方案相比，在非IO密集型的场景，这种方案的性能提升了接近3倍。
这里我还使用了专门的锁策略——READPAST。这种策略是这样的：如果查询发现一些行已经加了锁，那么无需等待这些行的解锁。它会立刻忽略这些数据行。这里由于我们没有先SELECT再UPDATE，所以无需使用HOLDLOCK。这是性能更佳的一个原因。
队列表的归档策略
当你向队列表中插入记录时，队列表的大小会一直在增长。你必须确保队列表维持合理的大小，这样队列表的备份和重建索引就不会耗时太长。
有两种方法可以归档行数据：昼夜不停地进行小批量归档或者在非高峰访问时段进行大批量归档。如果你的系统是提供24×7的不间断服务的，而且也没有非高峰访问时段，那么你就需要连续不断地运行小批量归档，在这样的小批量归档周期之间有一些时延。然而，在出队列期间，你不能从队列表中删除相应的记录行，因为删除操作是开销较大的操作，会导致出队列变慢。取而代之的是，你可以通过另一个后台任务来删除队列中已经处理过的记录行，这么做就不会影响出队列的性能。此外，在可靠的队列上，也不能在出队列期间删除相应行记录。因为如果处理队列消息的进程可能会因为某些原因而执行失败，而且又不能重新让它们入队列，那么这些消息数据就会永远丢失。有时候，你需要密切关注消息队列，确保已经被提取的消息在某个时间段内得到处理。如果没有得到处理，那么就需要把这些消息放在队列的最前端再次发送，让处理进程能处理到这些消息。正是由于以上这些原因，才会在出队列时保持这些记录行不变，只更改这些记录行的状态。
结论
订单处理系统、任务执行系统或通知系统的性能和可靠性，取决于你怎样设计你的队列。由于这些直接影响客户满意度并最终直戳你的底线，所以你应该花足够的时间对队列设计做正确的决策。否则，随着时间的推移，它会成为一个债务，让你付出失去业务和昂贵的资源消耗之后最终仍然不得不重新设计队列。
