varchar(max)、nvarchar(max) 和 varbinary(max) 列會影響選擇查詢嗎？

考慮這張表：

create table Books
(
   Id bigint not null primary key identity(1, 1),
   UniqueToken varchar(100) not null,
   [Text] nvarchar(max) not null
)

假設我們在這個表中有超過 100,000 本書。

現在我們有 10,000 本書數據要插入到這個表中，其中一些是重複的。所以我們需要先過濾重複，然後再插入新書。

檢查重複項的一種方法是：

select UniqueToken
from Books 
where UniqueToken in 
(
   'first unique token',
   'second unique token'
   -- 10,000 items here
)

列的存在Text會影響此查詢的性能嗎？如果是這樣，我們如何優化它？

PS 對於其他一些數據，我有相同的結構。而且表現不佳。一個朋友告訴我，我應該把我的桌子分成兩張桌子，如下所示：

create table BookUniqueTokens 
(
   Id bigint not null primary key identity(1, 1),
   UniqueToken varchar(100)
)

create table Books
(
   Id bigint not null primary key,
   [Text] nvarchar(max)
)

而且我必須只在第一個表上執行我的重複查找算法，然後將數據插入到這兩個表中。他聲稱通過這種方式性能會變得更好，因為表格在物理上是分開的。他聲稱該[Text]列會影響對該列的任何select查詢UniqueToken。

例子

考慮在IN包含 10K 條記錄的數據集的子句中使用 8 個過濾謂詞的查詢。

select UniqueToken
from Books 
where UniqueToken in 
(
   'Unique token 1',
   'Unique token 2',
   'Unique token 3',
   'Unique token 4',
   'Unique token 5',
   'Unique token 6',
   'Unique token 9999',
   'Unique token 5000'
   -- 10,000 items here
);

使用聚集索引掃描，此測試表上沒有其他索引

數據大小為216 字節。

您還應該注意即使有 8 條記錄，OR過濾器也會堆積起來。

此表上發生的讀取：

歸功於statisticsparser。

當您Text在查詢的選擇部分中包含該列時，實際數據大小會發生巨大變化：

select UniqueToken,Text
from Books 
where UniqueToken in 
(
   'Unique token 1',
   'Unique token 2',
   'Unique token 3',
   'Unique token 4',
   'Unique token 5',
   'Unique token 6',
   'Unique token 9999',
   'Unique token 5000'
   -- 10,000 items here
);

同樣，帶有殘差謂詞的聚集索引掃描：

但是使用32KB的數據集。

由於有近 1000 次 lob 邏輯讀取：

現在，當我們創建有問題的兩個表並用相同的 10k 記錄填充它們時

執行相同的選擇而不使用Text. 請記住，我們在使用Books表時有 99 次邏輯讀取。

select UniqueToken
from BookUniqueTokens 
where UniqueToken in 
(
   'Unique token 1',
   'Unique token 2',
   'Unique token 3',
   'Unique token 4',
   'Unique token 5',
   'Unique token 6',
   'Unique token 9999',
   'Unique token 5000'
   -- 10,000 items here
)

讀數BookUniqueTokens 較低，為 67 而不是 99。

我們可以將其追溯到原始Books表中的頁面和新表中沒有Text.

原Books表：

新BookUniqueTokens表

因此，所有頁面 +（2 個成本頁面？）都是從聚集索引中讀取的。

為什麼會有差異，為什麼差異不大？畢竟數據大小差異很大（Lob data <> No Lob data）

Books數據空間

BooksWithText 數據空間

原因是ROW_OVERFLOW_DATA。

當數據大於 8kb 時，數據將作為 ROW_OVERFLOW_DATA 儲存在不同的頁面上。

好的，如果lob數據儲存在不同的頁面上，為什麼這兩個聚集索引的頁面大小不一樣呢？

由於將 24 字節指針添加到聚集索引以跟踪這些頁面中的每一個。畢竟，sql server 需要知道在哪裡可以找到 lob 數據。

來源

---

回答您的問題

他聲稱

$$ Text $$列會影響 UniqueToken 列上的任何選擇查詢。

和

Text 列的存在是否會影響此查詢的性能？如果是這樣，我們如何優化它？

如果儲存的數據實際上是 Lob Data，並且使用了答案中提供的 Query

由於 24 字節指針，它確實帶來了一些成本。

根據執行次數/分鐘不會太高，我會說這可以忽略不計，即使有 10 萬條記錄也是如此。

Text請記住，僅當使用包含的索引（例如聚集索引）時才會發生這種成本。

但是，如果使用聚集索引掃描，並且lob數據不超過8kb呢？

如果數據不超過 8kb，並且您沒有索引UniqueToken，則成本可能會更大。即使沒有選擇Text列。

當 Text 僅 137 個字元長（所有記錄）時，邏輯讀取 10k 條記錄：

表’Books2’。掃描計數 1，邏輯讀取 419

由於所有這些額外數據都在聚集索引頁上。

UniqueToken同樣， （不包括列）上的索引Text將解決此問題。

正如@David Browne - Microsoft 所指出的，您還可以將數據儲存在行外，以便在不選擇此文本列時不會在聚集索引上添加此成本。

此外，如果您確實希望將文本儲存在行外，則可以在不使用單獨表格的情況下強制執行該操作。只需使用 sp_tableoption 設置 ’large value types out of row’ 選項。docs.microsoft.com/en-us/sql/relational-databases

TL; 博士

根據給定的查詢，UniqueToken不包含的索引TEXT應該可以解決您的麻煩。此外，我會使用臨時表或表類型來進行過濾而不是IN語句。

編輯：

是的，UniqueToken 上有一個非聚集索引

您的範例查詢沒有觸及該Text列，根據查詢，這應該是一個覆蓋索引。

如果我們在之前使用的三個表上進行測試（UniqueToken+ Lob 數據，Solely UniqueToken，UniqueToken+ nvarchar(max) 列中的 137 個字元數據）

CREATE INDEX [IX_Books_UniqueToken] ON Books(UniqueToken);
CREATE INDEX [IX_BookUniqueTokens_UniqueToken]  ON BookUniqueTokens(UniqueToken);
CREATE INDEX [IX_Books2_UniqueToken] ON Books2(UniqueToken);

這三個表的讀取保持不變，因為使用了非聚集索引。

Table 'Books'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'BookUniqueTokens'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Books2'. Scan count 8, logical reads 16, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

額外細節

@David Browne - 微軟

此外，如果您確實希望將文本儲存在行外，則可以在不使用單獨表格的情況下強制執行該操作。只需使用 sp_tableoption 設置 ’large value types out of row’ 選項。docs.microsoft.com/en-us/sql/relational-databases/

請記住，您必須重建索引才能對已填充的數據生效。

通過@Erik Darling

在

• MAX 數據類型做什麼？

過濾 Lob 數據很糟糕。

• 記憶體授予和數據大小

當使用更大的數據類型時，您的記憶體授予可能會超出上限，從而影響性能。

從技術上講，任何在數據頁上佔用更多空間以致數據需要更多數據頁的任何東西都會降低性能，即使數量如此之小以至於無法輕鬆測量也是如此。但是更多的數據頁意味著需要更多的操作來讀取更多的頁面，並且需要更多的記憶體來保存更多的數據頁，等等。

因此，如果您正在掃描堆或索引，那麼即使沒有選擇列，NVARCHAR(MAX)列的存在*也會影響性能。*例如，如果您每行有 5000 - 7000 字節，那麼在問題中顯示的架構中，這將儲存在行中，從而需要更多數據頁。然而 8100 字節（大約）或更多將保證數據儲存在行外，只有一個指向 LOB 頁面的指針，所以不會那麼糟糕。

但是，在您的情況下，由於您提到在 上具有非聚集索引，那麼如果有一列具有 5000-7000 字節（導致每行 1 頁UniqueToken），那麼它實際上應該不那麼重要（甚至根本不重要）NVARCHAR(MAX)) 因為查詢應該查看其中只有IdandUniqueToken列的索引。而且，該操作應該進行查找而不是掃描，因此不會讀取索引中的所有數據頁。

最後的考慮：除非你有非常舊的硬體（意思是，沒有 RAM 和/或其他程序佔用磁碟/CPU/RAM，在這種情況下，大多數查詢都會受到影響，而不僅僅是這個），那麼 100,000 行並不是很多行. 事實上，它甚至沒有接近很多行。100 萬行甚至不會是很多行，在這裡會產生巨大的影響。

因此，假設您的查詢確實使用了非聚集索引，那麼我認為我們應該在該NVARCHAR(MAX)列之外的某個地方尋找問題。這並不是說有時將一個表分成兩個表不是最佳選擇，只是懷疑它是否會在提供的資訊中有所幫助。

我希望改進的三個地方是：

1. **顯式模式名稱：**這是次要的，但始終使用其模式名稱作為基於模式的對象的前綴。所以你應該使用dbo.Books而不是僅僅使用Books. 這不僅在使用多個架構並且不同使用者具有不同的預設架構的情況下有所幫助，而且還減少了在架構未明確說明並且 SQL Server 需要檢查幾個位置時發生的一些鎖定。
2. IN清單：這些很方便，但不以可擴展性著稱。IN列表擴展為OR列表中每個項目的條件。意義：

where UniqueToken in 
(
   'first unique token',
   'second unique token'
   -- 10,000 items here
)

變成：

where UniqueToken = 'first unique token'
OR UniqueToken = 'second unique token'
-- 10,000 items here (9,998 more OR conditions)

當您向列表中添加更多項目時，您會獲得更多OR條件。

與其動態建構IN列表，不如創建一個本地臨時表並創建INSERT語句列表。此外，將它們全部包裝在一個事務中，以避免每個事務會發生的事務成本INSERT（因此將 10,000 個事務減少到 1 個）：

CREATE TABLE #UniqueTokens
(
 UniqueToken VARCHAR(100) NOT NULL
             COLLATE Latin1_General_100_BIN2
             PRIMARY KEY
);

BEGIN TRAN;

..dynamically generated INSERT INTO #UniqueTokens (UniqueToken) VALUES ('...');

COMMIT TRAN;

現在您已經載入了該列表，您可以按如下方式使用它來獲取相同的重複標記集：

SELECT bk.[UniqueToken]
FROM   dbo.Books bk
INNER JOIN #UniqueTokens tmp
       ON tmp.[UniqueToken] = bk.[UniqueToken];

或者，假設您想知道可以載入 10,000 個新條目中的哪一個，您真的想要非重複標記的列表，以便您可以插入它們，對嗎？在這種情況下，您將執行以下操作：

SELECT tmp.[UniqueToken]
FROM   #UniqueTokens tmp
WHERE  NOT EXISTS(SELECT *
                 FROM   dbo.Books bk
                 WHERE  bk.[UniqueToken] = tmp.[UniqueToken]);

3. **字元串比較：**如果沒有特別需要對 進行不區分大小寫和/或不區分重音的比較UniqueToken，並假設您創建此表的數據庫（不使用該列的COLLATE子句[UniqueToken]）沒有二進制預設排序規則，那麼您可以UniqueToken通過使用二進制比較來提高值匹配的性能。非二進制比較需要為每個值創建一個排序鍵，並且該排序鍵基於特定文化的語言規則（即Latin1_General, French, Hebrew,Syriac等）。如果值只需要完全相同，那將是很多額外的處理。因此，請執行以下操作：

4. 刪除非聚集索引UniqueToken

5. 將列更改UniqueToken為VARCHAR(100) NOT NULL COLLATE Latin1_General_100_BIN2（就像上面顯示的臨時表一樣）

6. 重新創建非聚集索引UniqueToken

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