扫描大型表以删除记录而不中断正在进行的插入的更好方法

Question

我有一个包含~50M行的表(UrlLog(。少数应用程序实例每分钟在此表中总共插入 4000-5000 个新行。每天晚上都会运行一个作业，删除此行中主键未被其他两个表中的任何行引用的每条记录(这两个表包含大约 50M (OutLog( 和 150M (InLog( 行(。

我在清理过程运行时选择、删除和插入干扰并导致访问此表的任何操作超时时遇到一些问题。

以下是表格，基本上：

CREATE TABLE UrlLog (
Id BIGINT NOT NULL PRIMARY KEY,
Hash UNIQUEIDENTIFIER NOT NULL,
Protocol TINYINT NOT NULL,
DomainId SMALLINT NOT NULL,
Path NVARCHAR(4000) NOT NULL,
Query NVARCHAR(4000) NOT NULL,
UNIQUE INDEX IX_UrlLog_Hash NONCLUSTERED (Hash)
)
CREATE TABLE InLog (
Id BIGINT NOT NULL PRIMARY KEY,
UrlId BIGINT NOT NULL,
Timestamp INT NOT NULL,
ResponseTime REAL NOT NULL,
IpAddress INT NOT NULL,
ErrorId INT NOT NULL,
Flags TINYINT NOT NULL,
INDEX IX_InLog_UrlId NONCLUSTERED (UrlId)
)
CREATE TABLE OutLog (
Id BIGINT NOT NULL PRIMARY KEY,
UrlId BIGINT NOT NULL,
ApiId SMALLINT NOT NULL,
Timestamp INT NOT NULL,
ResponseTime REAL NOT NULL,
HttpStatus TINYINT NOT NULL,
ErrorId INT NOT NULL,
INDEX IX_OutLog_UrlId NONCLUSTERED (UrlId)
)

插入通过以下过程进行。每个应用程序实例每分钟刷新一次缓冲记录。每分钟有 800-1200 个新行发送到此插入过程，并且它们是批量提交的 - 目前一次有 400 行通过表值参数发送到存储过程。发送到此过程的大多数行都是新的，并导致表插入。

主键 ID 在应用程序中生成，而不是在数据库中自动递增。ID 将返回到应用程序，以便它可以缓存这些日志值并关联将来的重复项，而无需再次查询数据库。每分钟大约有一半的 UrlLog 行是新的，大约一半已经在应用程序内存中。我们还假设哈希是无冲突的。在极少数情况下发生冲突，具有不正确的 URL 关联是可以接受的。

CREATE TYPE [dbo].[UrlInsertTableType] AS TABLE (
Id BIGINT NOT NULL,
Hash UNIQUEIDENTIFIER NOT NULL,
Protocol TINYINT NOT NULL,
DomainId SMALLINT NOT NULL,
Path NVARCHAR(4000) NOT NULL,
Query NVARCHAR(4000) NOT NULL)
CREATE PROCEDURE [dbo].[LogUrls]
@Urls [dbo].[UrlInsertTableType] READONLY
AS
SET NOCOUNT ON
DECLARE @FINAL_ID BIGINT
DECLARE @ID BIGINT
DECLARE @HASH UNIQUEIDENTIFIER
DECLARE @PROTOCOL TINYINT
DECLARE @DOMAINID SMALLINT
DECLARE @PATH NVARCHAR(4000)
DECLARE @QUERY NVARCHAR(4000)
CREATE TABLE #UrlInsertTemp (
Id BIGINT NOT NULL,
Hash UNIQUEIDENTIFIER NOT NULL
)
BEGIN TRAN
DECLARE CUR CURSOR LOCAL FAST_FORWARD FOR
SELECT Id, Hash, Protocol, DomainId, Path, Query FROM @Urls
OPEN CUR
WHILE 1=1
BEGIN
FETCH NEXT FROM CUR INTO @ID, @HASH, @PROTOCOL, @DOMAINID, @PATH, @QUERY
IF @@FETCH_STATUS = -1 BREAK
SET @FINAL_ID = NULL
SELECT @FINAL_ID = Id FROM Url WHERE Hash=@HASH
IF @FINAL_ID IS NULL BEGIN
INSERT INTO Url (Id, Hash, Protocol, DomainId, Path, Query)
VALUES (@ID, @HASH, @PROTOCOL, @DOMAINID, @PATH, @QUERY)
SELECT @FINAL_ID = @ID
END
INSERT INTO #UrlInsertTemp (Id, Hash) VALUES (@FINAL_ID, @HASH)
END
CLOSE CUR
DEALLOCATE CUR
COMMIT
SELECT Id, Hash AS [Key] FROM #UrlInsertTemp

删除操作通过以下过程进行。应用程序代码在循环中调用该过程，直到它达到最大 Url.Id。

CREATE PROCEDURE [dbo].[DeleteUrls]
@LastId BIGINT
AS
SET NOCOUNT ON
DECLARE @ID BIGINT
DECLARE @FOUND BIGINT
BEGIN TRAN
DECLARE CUR CURSOR LOCAL FAST_FORWARD FOR
SELECT TOP 200 Id FROM UrlLog WHERE Id > @LastId ORDER BY Id
OPEN CUR
WHILE 1=1
BEGIN
FETCH NEXT FROM CUR INTO @ID
IF @@FETCH_STATUS = -1 BREAK
SELECT @FOUND = Id FROM InLog WHERE UrlId=@ID
IF @FOUND IS NULL BEGIN
SELECT @FOUND = Id FROM OutLog WHERE UrlId=@ID
IF @FOUND IS NULL BEGIN
DELETE FROM UrlLog WHERE Id=@ID
END
END
END
CLOSE CUR
DEALLOCATE CUR
COMMIT
SELECT @ID

应用的调用伪代码：

topId = GetLargestUrlLogId()
lastId = 0
while (lastId < topId)
lastId = executeDeleteUrlProcedure(lastId)

关于如何更改删除或插入行的方式以帮助它们一起玩得更好，有什么建议吗？

Answer 1

我有几个想法给你，过去对我有用。 我发现在处理如此高度事务性的问题时，"科学"中有很多"艺术"，对某人有用的东西对其他人不起作用。 希望其中一个想法能解决您的问题。

• 尝试创建需要删除的内容的队列表。 使用 SELECT 和 WITH(NOLOCK( 填充一个只有 1 列的表，该表是您要清理的表的素键。 然后尝试加入"块"进行删除(我取得了一些成功(。 也可以尝试简单地删除 TOP 1 并加入表并循环(出于某种原因，这里的成功要好得多。 我的发现表明，与其说是删除导致了争用，不如说是对删除内容的查找导致了更大的问题。 这些方法很奇怪，因为它是很多小交易，这是我们一直被教导的错误！

• 在开始清理之前，请更改清洁时日志进入"保留"表的位置。 清理完成后，将日志记录重定向回并导入"保留"数据。

• 完全暂停日志记录。 做1个大删除，恢复清理。 这可能不是一种选择。

• 轮换日志记录。 每天都把它放到一张新桌子上。 使用视图合并表以进行读取。 要删除数据，只需删除最旧的表即可。

• 考虑是否有其他因素可以简单地提高您的整体绩效？ 也许将数据库事务模型更改为简单或更改隔离级别？