為插入和刪除優化審計表
我正在嘗試確定為審計目的設計表格的最佳方式。基本上,我在一張表中記錄了許多使用者的最後幾個事件。每個使用者的記錄數有限制。新記錄來了,舊記錄走了。
像這樣的東西:
CREATE TABLE Audit ( UserId INT NOT NULL, EventId INT NOT NULL, CreationDate DATETIME NOT NULL, UpdateDate DATETIME NULL, -- Other data fields )
問題是如何處理索引。我正在考慮在
(UserId, EventId)
. 但是由於使用者活動是獨立發生的,這意味著在表中間插入並在表中間刪除。恐怕不好。另一個想法是添加一個人工
AuditId
欄位只是為了讓新記錄越來越多。像這樣:CREATE TABLE Audit ( Id INT, -- Becomes the clustered index -- The same as above )
這樣,新的審計條目將被追加到末尾,但刪除仍將發生在表的中間。它可能比第一個選項更好,但我不確定。
該表將經常使用,基本上每個使用者活動都會被記錄(1 次插入),並且最舊的活動會在同一個事務中被刪除(1 次刪除)。這需要很快。好吧,我希望它在理想情況下是即時的,並且在性能方面不明顯。
我還需要能夠快速檢索特定使用者的記錄集。它可能被非聚集索引覆蓋。
我正在尋求有關設計此表以獲得最佳性能的建議。
編輯:我想我錯過了一些重要的事情。
我試圖追踪的不是瞬時的,而是一段時間內的。系統中有幾個地方需要這個。考慮使用者正在做什麼,可能跨越一段時間的某種活動。如果滿足某些條件,則重用現有活動(刷新、更新)。我只想刪除舊的廢棄活動。例如,一個使用者可能在 2 週內發布了大約 50 個活動,但對於另一個使用者來說,製作多於許多的使用者可能需要一年多的時間。這就是為什麼我不希望所有使用者一起使用一般有序的日誌。
也不清楚我應該如何根據日期時間進行分群(如建議的那樣)。我是在初始創建事件還是更新事件中執行此操作?
通常,為了優化此類表的插入/刪除,您將聚集在 datetime 列上。(我想你正在跟踪這些事件何時發生。)
這樣,插入總是會進入表的末尾,從而最大限度地減少“壞”類型的頁面拆分。刪除或歸檔數據很容易,因為聚集索引將支持範圍刪除,並且這些操作通常不會鎖定您正在執行插入的頁面(除非您出於某種原因升級)。
您可能希望其他索引(例如您所描述的內容)來支持查詢,並且維護這些索引當然會在一定程度上乾擾 DML。
我不確定我是否理解為什麼您只想為任何 UserId/EventId 組合保留一定數量的行。如果一個使用者今天做了一大堆事情,你真的想刪除他們昨天、上周等所做的所有事情,同時為不太活躍的使用者保留舊數據嗎?僅根據時間保留數據(例如保留兩週的歷史記錄)不是更有意義嗎?
我也不確定為什麼刪除絕對需要與插入耦合。不能將刪除推遲到後台程序而不是阻止插入事務嗎?