散列和堆索引對象儲存表插入和查詢性能

這個問題部分是為了征求意見並確保這個設計是理智的。我在這裡預先說明所需的屬性，然後在基本原理中更詳細地介紹並跟進設計和範例查詢和測試數據。

1. 找到正確的行應該很快。
2. 盡可能避免索引碎片會很好。
3. 一個公平的假設是，某些類型的模式比UPDATE行的頻率高，SELECT而INSERT其他SELECT的多於UPDATE。認為同一個實體會被高頻查詢是不合理的。更有可能是偶爾。
4. 一個公平的假設是，UPDATE呼叫在應用程序更新狀態時占主導地位，偶爾會出現SELECT查詢。如果應用程序更新或插入，而不是在發出呼叫時沒有有效版本（即 etag/ ROWVERSION）時，​​應用程序不會產生影響。如果失敗，它只會讀取最新的 etag 並在開發人員選擇時重試。DELETE可以（也許應該是）設置標誌的軟刪除，這意味著更像是重置而不是刪除。當複位後跟一個upsert時，它只是在以下設計中將該IsDeleted位設置為。0

我正在嘗試設計一個表，該表可以保存由應用程序以二進制、XML 或 JSON 形式序列化的任意對象。不只使用二進制 blob 的原因是一個理想的屬性是能夠操作儲存中的對象。

可以假設儲存了數百萬甚至數億個這樣的對象。不考慮表分區等“企業級”功能是可以的。我更關心“基本設計”以及它是否理智。對我來說，這部分是一個學習練習。

應用程序將根據設計為每個類類型的每個對象擁有一個唯一 ID，並且它使用這兩個來唯一地儲存和定位數據庫中的給定對象。類類型是常見outernamespace.innernamespace.class的，ID 平均約為 50 個字元（可以是隨機的 32 位整數、GUID 或使用者定義的東西）。ID 和類型都是 16 位 Unicode 字元。因為希望快速定位對象，所以唯一 ID 和類型都由應用程序散列為 32 位整數，並以 type 的形式儲存在數據庫中INT。該程式碼處理從散列無符號整數到INT數據庫和返回的映射。

我閱讀了Thomas Kejser Clustered Index vs. Heap的一篇有趣的部落格文章，基於該文章，它看起來像遵循沒有聚集堆的設計並使用堆是要走的路。

但真的是這樣嗎？下面的設計有點簡化，因為我想避免在沒有首先確定它是否有效的情況下進行某些設計。我將為版本添加一列，並可能將其拆分ObjectType到一個單獨的表中，以避免在整個過程中多次儲存類型名稱。我將範例查詢修改為始終返回所有三個有效負載欄位並讓應用程序處理選擇正確的欄位，或者將其修改為僅正確返回具有實際數據的欄位並將類型設置為VARBINARY(MAX)，我不知道這種轉換是否不利績效處罰。

我還有其他問題：

1. 當一個對像被刪除並且我想節省儲存空間但避免索引碎片時，將*Payload欄位設置為 是否有用NULL，否則離開該行？如果我使用MAXon VARBINARYand NVARCHAR，我知道它們將與表格分開儲存，並且將其設置為下表中NULL可能不會節省太多ObjectStore，但我想知道它是否可以在其他地方節省。
2. 有沒有DELETE比IsDeleted. 我不確定在該位上設置或未設置的比率是多少。可以做出的一個假設是，可能可以進行清理操作，從數據庫中物理刪除已刪除的行並對錶進行碎片整理。我將它排除在索引之外，它不在過濾索引中，因為這也適用於 SQL Server Standard。
3. 從 DBA 的角度來看，是否可以這樣安排，以便 DBA 可以相應地修改SELECT、INSERT（此處未顯示）和UPDATE（此處未顯示）查詢，以便系統可以，例如，通過類型進一步優化系統將其中一些儲存到單獨的表中？

5. 我的查詢有什麼問題？我在查詢中還有一個錯誤，在給定的範例中，它應該只返回一個重複行，但我無法弄清楚問題出在哪裡。在最常見的情況下，如果沒有雜湊衝突，查詢看起來就像是使用索引進行的索引查找。 4. 大概這樣的東西也適用於 MySQL 嗎？

呸！那是很多文字。我希望這對其他人也有用。如果這很重要，我計劃在使用此表時使用 ADO.NET（可能使用流式傳輸）。

DROP TABLE ObjectStore;
CREATE TABLE ObjectStore
(
   -- These are for the book keeping. The application calculates
   -- these hashes, which are unsigned 32 integers mapped to
   -- the *Id fields. The mapping is done in the code. The
   -- *Name columns contain the corresponding clear name fields.
   --
   -- If there are duplicates
   ObjectId INT NOT NULL,
   ObjectIdName NVARCHAR(512) NOT NULL,
   ObjectType INT NOT NULL,
   ObjectTypeName NVARCHAR(512) NOT NULL,

   -- The usage of the payload records is exclusive in that
   -- only one is populated at any given time and two others
   -- are NULL. When all three are returned, the application
   -- knows how to handle the situation.   
   PayLoadBinary VARBINARY(MAX) NULL,
   PayloadXml XML NULL,
   PayLoadJson NVARCHAR(MAX) NULL,

   -- Informational field, no other use.
   ModifiedOn DATETIME2(3) NOT NULL,

   -- If this particular object has been deleted from the database
   -- or not. The objects can be inserted, deleted and reinserted.
   -- Would it be beneficial to set the Payload* columns to NULL
   -- to save space but still to avoid index fragmentation?
   IsDeleted BIT NOT NULL

   -- The following would in principle be the primary key, but hashing can produce
   -- collisions.
   -- CONSTRAINT PK_ObjectStore PRIMARY KEY NONCLUSTERED (ObjectId, ObjectType)        
);
CREATE NONCLUSTERED INDEX IX_ObjectStore ON ObjectStore(ObjectId, ObjectType) INCLUDE(IsDeleted, PayLoadBinary, PayLoadJson, PayloadXml);

SELECT
   PayLoadBinary,
   PayloadXml,
   PayLoadJson
FROM
   ObjectStore
WHERE
   ObjectId = @objectId
   AND ObjectType = @objectType
   AND IsDeleted = 0
   AND ObjectIdName = @objectIdName
   AND ObjectTypeName = @objectTypeName;

一些帶有模擬碰撞的測試數據

INSERT INTO ObjectStore
(
   ObjectId,
   ObjectIdName,
   ObjectType,
   ObjectTypeName,
   PayLoadBinary,
   PayloadXml,
   PayLoadJson,
   ModifiedOn,
   IsDeleted
)
VALUES
(
   1,
   N'First',
   1,
   N'FirstType',
   NULL,
   NULL,
   N'{ "id": First, "field1": "Red", "field2": 1.0, "objects": ["birch", "pine"] }',
   GETUTCDATE(),
   0
);

-- Simulate a collision.
INSERT INTO ObjectStore
(
   ObjectId,
   ObjectIdName,
   ObjectType,
   ObjectTypeName,
   PayLoadBinary,
   PayloadXml,
   PayLoadJson,
   ModifiedOn,
   IsDeleted
)
VALUES
(
   1,
   N'First',
   1,
   N'NonFirstType',
   NULL,
   NULL,
   N'{ "id": First, "field1": "Green", "field2": 1.2, "objects": ["pine", "birch"] }',
   GETUTCDATE(),
   0
);

INSERT INTO ObjectStore
(
   ObjectId,
   ObjectIdName,
   ObjectType,
   ObjectTypeName,
   PayLoadBinary,
   PayloadXml,
   PayLoadJson,
   ModifiedOn,
   IsDeleted
)
VALUES
(
   2,
   N'Second',
   2,
   N'SecondType',
   NULL,
   NULL,
   N'{ "id": First, "field1": "Green", "field2": 2.0, "objects": ["oak", "juniper"] }',
   GETUTCDATE(),
   0
);

DECLARE @objectId   AS INT = 1;
DECLARE @objectIdName AS NVARCHAR(512) = N'First';
DECLARE @objectType AS INT = 1;
DECLARE @objectTypeName AS NVARCHAR(512) = N'FirstType';

<編輯：相關的 SO 文章何時應將主鍵聲明為非聚集的？. 在這裡，可以預期會同時發生大量的INSERT操作UPDATE。我不確定硬體或模式，但對前進的道路有一個大致的感覺是我在這裡研究的。

（以下評論適用於 MySQL；有些可能適用於其他引擎。）

• UUID 由於其隨機性而減慢了速度。
• 不要使用 Unicode；使用 utf8。（更好的是，CHARACTER SET utf8mb4）
• InnoDB 通過設計使索引碎片保持在較低水平。
• 經驗法則：在具有數百萬行的表中，通過 的“點查詢”PRIMARY KEY預計需要與一個磁碟命中一樣長的時間。InnoDB 使用以PRIMARY KEYBTree 組織的“集群”。
• 規範化你的類名。短整數比中等大小的字元串更有效，特別是因為有很多重複。
• 雜湊索引不可用。它們對於掃描毫無用處，對於點查詢也不比 BTrees 好多少。（競爭產品比 MySQL 更擅長提供很少有用的功能，例如雜湊和點陣圖索引。）
• 獲取記錄是任何操作中的主要成本。相比之下，函式呼叫、表達式求值、字元集、排序規則、轉換VARBINARY等都是微不足道的。
• 數據儲存在塊中，因此釋放行可能會導致釋放塊。不再擔心碎片化。您還沒有說明您是否可能刪除 10% 的行（不是什麼大問題）或 90%（可能是一個問題）。
• 將 DBA 與開發人員分開是愚蠢的。
• 範常式式碼中大約 10% 的 SQL 行會導致 MySQL 中的語法錯誤。SQL 實現非常不兼容。如果你試圖讓程式碼在引擎之間工作，你將不得不省略一些性能特性。
• “對象”和“關係數據庫”互不相同。

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