SQL 並發插入和自動查找活動標誌記錄

什麼是即時找到 OLTP 表的活動標誌的正確數據庫設計：將許多行插入並行、多執行緒環境，同時允許優化，並防止競爭條件、鎖定、死鎖等？注意：此表只有插入，沒有刪除或更新。

活動標誌由商店發送訂單文件（ max(LastModified) 列）確定。由於客戶修改和技術問題，有時商店可以稍後發送舊文件。所以最後發送的文件可能並不總是最新的並且有一個更早的

$$ LastModified $$日期。 我們應該使用：

(1) 帶活動標誌的一桌設計，

(2) 還是高插入環境下的兩張表設計（事務表和目前指針表）？

我們在一周的時間內收到大約 5000 次插入/秒和 1 億次插入。消耗大約 200GB 的數據，50 個核心中的 SQL2016，200GB 的 RAM。

方法一：帶目前標誌的一表法

create table dbo.CustomerOrder
(
   CustomerOrderId bigint primary key identity(1,1),
   CustomerId int,
   CurrentFlag bit,
   FileLastModified datetime,  -- actual shop submittal date
   Productsku varchar(25),  
   Quantity int,
)

方法二：兩表法，指向原始表的指針行

create table dbo.CustomerOrderCurrent
(
   CustomerOrderCurrentKey bigint primary key identity(1,1),
   CustomerId int,
   CustomerOrderId bigint foreign key references  CustomerOrder(CustomerOrderId) -- refers to historical table
)

基於“什麼是正確的數據庫設計”這個問題：

有一個“CurrentFlag”列會稍微“去規範化”模式設計，以便快速辨識客戶的目前訂單。

我曾在使用這種方法的數據庫上工作過，但這是一種性能權衡，需要“程式碼”（例如觸發器）來維護該列。這是並發問題可能會蔓延的地方，因為插入時會進行額外的工作。

但是，通過在索引本身中“包含”“CustomerOrderID”的降序覆蓋索引，可以在不更改架構的情況下實現相同的結果。

此僅索引解決方案需要進行測試，以確保它在“一周時間跨度內的 1 億次插入”負載下具有性能，尤其是在查詢數據時（請參閱程式碼範例的末尾）。

覆蓋索引範常式式碼：

--Create a test table for the example based on given schema
USE [testDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CustomerOrder](
   [CustomerOrderId] [bigint] IDENTITY(1,1) NOT NULL,
   [CustomerId] [int] NOT NULL,
   [FileLastModified] [datetime] NOT NULL,
   [Productsku] [varchar](25) NOT NULL,
   [Quantity] [int] NOT NULL,
CONSTRAINT [PK_CustomerOrder] PRIMARY KEY CLUSTERED 
(
   [CustomerOrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Create the covering index
CREATE NONCLUSTERED INDEX IX_ActiveCustomerOrder 
   ON dbo.CustomerOrder (CustomerID ASC, FileLastModified DESC) INCLUDE (CustomerOrderID);

-- Insert dummy data (with dates out of order)
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity]) 
      VALUES (100, '17-Oct-2017', 'Prod1',20);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity]) 
      VALUES (100, '15-Oct-2017', 'Prod1',30);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity]) 
      VALUES (100, '18-Oct-2017', 'Prod1',41);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity]) 
      VALUES (100, '14-Oct-2017', 'Prod1',42);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity]) 
      VALUES (100, '13-Oct-2017', 'Prod1',43);


-- Fetch the active order for a single customer using an index hint
SELECT TOP(1) CustomerID, CustomerOrderId, [FileLastModified] 
FROM dbo.CustomerOrder WITH (INDEX=IX_ActiveCustomerOrder)
   WHERE CustomerID = 100; 

-- Fetch active orders for all customers
SELECT x.CustomerID, x.CustomerOrderId, x.[FileLastModified]
FROM (
SELECT CustomerID, 
      CustomerOrderId, 
      FileLastModified, 
     (ROW_NUMBER() OVER(PARTITION BY CustomerID 
                   ORDER BY FileLastModified DESC)) AS RowID 
 FROM dbo.CustomerOrder WITH (INDEX=IX_ActiveCustomerOrder)
) x
WHERE x.rowid = 1;

引用自：https://dba.stackexchange.com/questions/188282