隨時間推移預測查詢性能的合理方法

我有一個 SQL Server 2012 數據庫，用於保存已處理文件中的數據。我們從一個文件夾中讀取數據，用 python 處理它並將結果保存到數據庫中。

我們在 ETL 過程中做的第一件事就是檢查文件是否已經被處理。我們簡單地做一個：

SELECT id FROM table1 WHERE basename = <basename>

如果有結果我們跳過文件，如果沒有結果我們處理文件。現在這個查詢需要大約 250 毫秒和大約 5 百萬條記錄。basename我們已經在列上有一個非聚集索引。

我們將看到每月添加大約 100-200k 條記錄。我們分批獲取文件。所以我們可能會看到 2k 個文件，然後 2 小時後又看到了 2k 個文件。有些日子我們會得到 10k 個文件，有些日子我們可能只會得到 4k 個文件。

保持所有其他變數相同，除了在表中插入 15-2000 萬條記錄並查看會發生什麼情況之外，還有一個經驗法則可以預測我們何時可能遇到此查詢的性能（查詢時間超過 1 秒）問題？

DDL 表：

CREATE TABLE [dbo].[raw_records](
[id] [int] IDENTITY(1,1) NOT NULL,
[basename] [varchar](512) NULL,
[filename] [varchar](1024) NULL,
[file_size] [int] NULL,
[machine] [varchar](10) NULL,
[insert_timestamp] [datetime] NULL,
[raw_xml] [xml] NULL,
[process_status] [varchar](2048) NULL,

PRIMARY KEY CLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

指數：

CREATE NONCLUSTERED INDEX [basename_index] ON [dbo].[raw_records]
(
   [basename] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

這張表是在我開始處理它之前很久就創建的，所以我假設有人剛剛做了filename1024 的最大長度來保持“足夠”。當然是多變的。

產生的文件在文件名本身（例如system1_metadata_timestamp.xml）一個“系統”不能產生（或永遠不應該）產生具有相同時間戳的文件時具有時間戳和唯一資訊。

select max(len(basename)), max(len(filename)) from dbo.raw_records;

返回：basename- 143，filename- 168。將最大值更改為 260 可能是件好事。

process_status可能也不需要那麼長，但我覺得猜測是合理的，因為該列用於保存來自處理階段的錯誤消息。我對其進行了查詢，最多有 600 個字元。不過，我們通常不會查詢該列。它只是用於調試的更多資訊。

我正在通過應用程序清理諸如此類的東西。在某些地方，我無法擺脫它，但在其他地方，不幸的是，我對此無能為力（例如，需要實際檢索 XML 列以從中提取數據）。這個問題只是源於看到有問題的查詢的性能並且不希望它遠離我。這是為每個文件完成的第一件事，所以如果這不起作用，其他任何事情也不會。

在存在良好索引的情況下，只要記憶體中有索引空間，定位匹配行所需的時間應該大致成對數比例。

我會創建索引UNIQUE，因為基本名稱必須是唯一的，否則您的工作流程無效，並且它使索引更有效。

CREATE UNIQUE INDEX IX_raw_records_basename
ON dbo.raw_records (basename);

檢查查詢的執行計劃以確保正在使用索引。

確保您有足夠的記憶體空間用於索引，並且假設並發不會成為一個大問題，那麼您應該適合大量的行。

我會重新考慮basename和filename列的長度，因為查詢優化器在計算執行查詢需要分配多少記憶體時將使用該長度。例如，如果該basename列永遠不會包含超過 20 個字元，但您將其定義為 512 個字元，則授​​予的記憶體SELECT basename FROM dbo.raw_records;將是實際需要的 25.6 倍。列長度實際上比大多數人意識到的要重要得多。

您還可以將查詢更改為SELECT 1 FROM table1 WHERE basename = <basename>您甚至不需要的方式，id因為您要做的就是驗證它的存在。只做你真正需要的。看起來您在問題中顯示的索引可以正常工作。

此外，如果空間是一個問題，您可能需要考慮索引和表的數據壓縮。這應該允許索引適合較小的記憶體佔用。評估DATA_COMPRESSION = ROW與DATA_COMPRESSION = PAGE查看哪種壓縮方法最適合您的要求。

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