nvarchar(max) 在 SQL 中對於 Entity Framework 開發者的危害

Tim Corey

10m 27s

在SQL中處理nvarchar時，開發者經常忽視這種資料型別如何影響性能——特別是在使用Entity Framework的C#開發中。 在一個名為"SQL中nvarchar(max)對Entity Framework開發者的危害"的10分鐘專注影片中，Tim Corey探索了將nvarchar(max)作為SQL Server資料庫中字串欄位的預設值的影響。

本文是Tim影片的詳細解釋，只使用他的展示和推理，並附有範例和性能比較。 如果您在沒有了解其工作原理的情況下依賴nvarchar(max)，這會成為一個驚醒。

了解問題：Entity Framework中的預設行為

Tim首先描述了一個常見的Entity Framework情景，C#開發者定義了一個具有FirstName和LastName等欄位的模型。 當透過遷移在SQL Server中自動建立表時，生成的模式將這些字串欄位預設設置為nvarchar(max)。

正如Tim所解釋的，這是因為Entity Framework不知道要指定的合適字串大小，因此選擇安全路徑——預設賦予最大長度。 這意味著每個nvarchar欄位允許最多2^31–1個字元，最大儲存大小為吉字節。

這一決定看似便利，但隱藏著危險的性能成本。

兩個表的範例設置：nvarchar(max)對比固定長度

為了凸顯問題，Tim建立了兩個相同的表：

• Users：first 和 last names使用nvarchar(50)。

• UsersToTheMax：相同欄位使用nvarchar(max)。

在2:39，Tim解釋了如何使用Dapper將兩個表都填滿了100萬個相同的行，以確保只有nvarchar資料型別不同。

此設置允許他在固定長度Unicode欄位和可變長度最大欄位之間進行一致的比較。

比較查詢和執行計劃

Tim在兩個表上使用以下SQL查詢：

SELECT * FROM dbo.Users ORDER BY LastName;
SELECT * FROM dbo.UsersToTheMax ORDER BY LastName;

SELECT * FROM dbo.Users ORDER BY LastName;
SELECT * FROM dbo.UsersToTheMax ORDER BY LastName;

在3:34，他啟用了實際執行計劃，以分析SQL Server在執行這些查詢時內部做了什麼。

注意：這個測試不涉及跨機器的總執行時間——Tim強調在同一台伺服器上使用相同的資料比較查詢，以隔離nvarchar(max)對性能的影響。

令人震驚的結果

執行計劃顯示出了一個重大差異：

• nvarchar(50)的查詢只使用批處理成本的2%。

• nvarchar(max)的查詢卻使用了驚人的98%成本。

如Tim所言，這意味著最大查詢在SQL Server處理上代價是50倍之多，即使欄位資料輸入相同而且相對較小。

在CPU時間方面：

• 排序nvarchar(50)需要107ms。

• 排序nvarchar(max)需要339ms。

但最大的不同在於特定的並行操作上：

• 固定長度：0.43s

• 最大長度：22.17s

這比率超過慢了50倍，即使資料相同。

記憶體消耗差異

Tim深入研究了記憶體分配——SQL Server為每個查詢分配的記憶體大小：

• nvarchar(50)查詢：340MB

• nvarchar(max)查詢：641MB

這已經是一個警訊，但在測試未快取欄位時，影響更加顯著：

• FirstName的固定長度：357MB

• FirstName的最大長度：8.5GB

這種增加是因為SQL Server在定義為max時無法知道nvarchar值可能有多大，所以它保留了一個更大的記憶區塊來容納最大大小。

為什麼nvarchar(max)如此昂貴？

在9:15，Tim解釋了其背後的原因。 nvarchar(max)資料型別：

• 支援最多2^31–1個Unicode字元，佔用最多2GB的儲存空間。

• 如果不能適合表內，SQL Server需要將值儲存在行外，使用指標而不是直接在行記憶體儲。

• 無法以固定長度欄位的同樣方式建立索引。

因此：

• 您不能為nvarchar(max)欄位建立索引，這意味著SQL Server必須在沒有最佳化的情況下對整個資料集進行排序或篩選。

• 這會影響如ORDER BY、WHERE、或JOIN等nvarchar(max)欄位上的操作。

這一行為只因選擇錯誤的字元資料長度而帶來了顯著的記憶體使用、CPU負載和性能減慢。

Tim的最終建議

正如Tim在結尾所說：

"在您的Entity Framework查詢中，確保您指定所有字串的大小。"

總是使用最大字元數定義您的字串屬性，例如nvarchar(100)或nvarchar(255)，取決於預期資料。 這一小改變確保：

• 優化的儲存空間

• 支援索引

• 減少查詢成本

• 性能一致性提高

通過設置適當的長度，您將使您的資料庫架構更有效率，避免懶惰預設設置的陷阱。

結論

Tim Corey的影片傳達了一個關鍵的教訓：在SQL中將nvarchar(max)作為字串欄位的預設長度會嚴重削弱性能——而您甚至未察覺。 即便是像名稱或地址這樣的普通Unicode文字輸入，SQL Server仍會分配過量的記憶體、跳過索引並增加CPU成本。

結論？ 了解nvarchar資料型別，除非您確實需要用於儲存大型文件或可變長度內容的欄位，否則避免使用max。

通過指定字串大小，您不僅節省了位元和記憶體，還使您的Entity Framework和SQL程式碼更高效、更具擴展性和更健壯。依循Tim的指導方針，您確保您的應用程式不會因設計緩慢。

對於任何在.NET中從事資料庫工作的開發者來說，這是一個應成為您標準工具包一部分的最佳實踐。 查看Tim的頻道以獲取更多SQL相關影片。