1. 項(xiàng)目概述為什么我們要關(guān)心SQL Server的插入效率在數(shù)據(jù)庫(kù)日常開(kāi)發(fā)和運(yùn)維中數(shù)據(jù)插入INSERT是最基礎(chǔ)、最高頻的操作之一。無(wú)論是業(yè)務(wù)系統(tǒng)記錄用戶行為日志還是數(shù)據(jù)倉(cāng)庫(kù)進(jìn)行ETL過(guò)程中的數(shù)據(jù)加載插入操作的效率都直接影響著系統(tǒng)的響應(yīng)時(shí)間和吞吐量。很多開(kāi)發(fā)者尤其是剛接觸SQL Server的朋友可能會(huì)覺(jué)得“插入數(shù)據(jù)嘛不就是一句INSERT INTO的事情”但實(shí)際生產(chǎn)環(huán)境中面對(duì)百萬(wàn)、千萬(wàn)甚至億級(jí)別的數(shù)據(jù)量時(shí)不同的插入方式帶來(lái)的性能差異可能是天壤之別——快的幾分鐘完成慢的可能讓系統(tǒng)卡死幾個(gè)小時(shí)。我見(jiàn)過(guò)太多因?yàn)椴迦敕绞竭x擇不當(dāng)而導(dǎo)致的性能瓶頸案例。比如一個(gè)簡(jiǎn)單的后臺(tái)數(shù)據(jù)同步任務(wù)因?yàn)槭褂昧酥鹦胁迦氲姆绞綇念A(yù)計(jì)的10分鐘變成了運(yùn)行2小時(shí)還未完成直接拖垮了線上數(shù)據(jù)庫(kù)的IO和日志寫(xiě)入。又比如在數(shù)據(jù)遷移時(shí)因?yàn)闆](méi)有正確設(shè)置批量提交和索引策略導(dǎo)致事務(wù)日志暴漲磁盤被撐滿。這些問(wèn)題歸根結(jié)底是對(duì)SQL Server內(nèi)部機(jī)制和不同插入方式的適用場(chǎng)景理解不夠深入。因此我決定結(jié)合自己十多年的實(shí)戰(zhàn)經(jīng)驗(yàn)對(duì)SQL Server中常見(jiàn)的7種數(shù)據(jù)插入方式進(jìn)行一次徹底的、可量化的效率對(duì)比。這不僅僅是跑個(gè)測(cè)試看誰(shuí)快誰(shuí)慢更重要的是拆解每種方式背后的原理、適用場(chǎng)景以及那些“坑”讓你知其然更知其所以然。無(wú)論你是正在優(yōu)化一個(gè)慢速的數(shù)據(jù)導(dǎo)入接口還是設(shè)計(jì)一個(gè)高并發(fā)的數(shù)據(jù)接收服務(wù)這篇文章都能給你提供直接的、可落地的參考。2. 測(cè)試環(huán)境搭建與基準(zhǔn)數(shù)據(jù)準(zhǔn)備在開(kāi)始對(duì)比之前我們必須建立一個(gè)統(tǒng)一、可控的測(cè)試環(huán)境。不規(guī)范的測(cè)試等于沒(méi)有測(cè)試結(jié)果毫無(wú)參考價(jià)值。2.1 硬件、軟件與測(cè)試表結(jié)構(gòu)我搭建了一個(gè)盡可能模擬常見(jiàn)生產(chǎn)環(huán)境的測(cè)試平臺(tái)硬件一臺(tái)獨(dú)立的測(cè)試服務(wù)器配置為8核CPU32GB內(nèi)存使用SSD固態(tài)硬盤。確保測(cè)試期間沒(méi)有其他繁重任務(wù)干擾。SQL Server版本使用SQL Server 2019 Developer Edition。不同版本如2008 R2, 2012, 2016, 2022在查詢優(yōu)化器和一些內(nèi)部機(jī)制上可能有細(xì)微差別但本文討論的核心原理和對(duì)比趨勢(shì)是通用的。如果你使用的是其他版本結(jié)論依然具有強(qiáng)參考性。目標(biāo)測(cè)試表我們創(chuàng)建一個(gè)結(jié)構(gòu)簡(jiǎn)單但典型的數(shù)據(jù)表用于插入測(cè)試。-- 創(chuàng)建測(cè)試數(shù)據(jù)庫(kù) CREATE DATABASE InsertPerformanceTest; GO USE InsertPerformanceTest; GO -- 創(chuàng)建主測(cè)試表 CREATE TABLE dbo.TestData ( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, -- 自增主鍵聚集索引 OrderID UNIQUEIDENTIFIER DEFAULT NEWID(), -- 隨機(jī)GUID模擬業(yè)務(wù)鍵 CustomerName NVARCHAR(100), -- 客戶姓名 OrderAmount DECIMAL(10, 2), -- 訂單金額 OrderDate DATETIME2 DEFAULT SYSDATETIME(), -- 訂單日期 Description NVARCHAR(MAX) -- 長(zhǎng)文本描述用于測(cè)試大字段影響 );這個(gè)表包含了自增主鍵、業(yè)務(wù)鍵、常用字段和大字段是業(yè)務(wù)系統(tǒng)中很常見(jiàn)的結(jié)構(gòu)。聚集索引在ID上這意味著數(shù)據(jù)在物理磁盤上大致按ID順序存儲(chǔ)。2.2 測(cè)試數(shù)據(jù)生成與性能度量標(biāo)準(zhǔn)為了進(jìn)行有意義的對(duì)比我們需要插入足夠多的數(shù)據(jù)。本次測(cè)試統(tǒng)一使用10萬(wàn)行作為基準(zhǔn)數(shù)據(jù)量。這個(gè)量級(jí)既能明顯區(qū)分不同方法的性能差異又不會(huì)讓測(cè)試過(guò)程過(guò)于漫長(zhǎng)。我使用SQL腳本動(dòng)態(tài)生成這10萬(wàn)行模擬數(shù)據(jù)確保每次測(cè)試前數(shù)據(jù)環(huán)境一致。數(shù)據(jù)生成邏輯模擬了真實(shí)的業(yè)務(wù)數(shù)據(jù)分布。核心性能度量指標(biāo)執(zhí)行時(shí)間Elapsed Time從插入操作開(kāi)始到結(jié)束的時(shí)鐘時(shí)間。這是最直觀的指標(biāo)使用SET STATISTICS TIME ON來(lái)獲取精確的CPU時(shí)間和總耗時(shí)。事務(wù)日志寫(xiě)入量Log Flushes在SQL Server中幾乎所有修改都會(huì)先寫(xiě)入事務(wù)日志Transaction Log。日志寫(xiě)入是IO操作的主要部分直接影響性能。我們通過(guò)查詢sys.dm_io_virtual_file_stats動(dòng)態(tài)管理視圖對(duì)比測(cè)試前后日志文件的寫(xiě)入次數(shù)num_of_writes和寫(xiě)入字節(jié)數(shù)num_of_bytes_written。鎖與阻塞情況使用sys.dm_tran_locks等視圖觀察不同插入方式對(duì)資源的鎖定情況理解其并發(fā)能力。注意每次測(cè)試前我都會(huì)使用TRUNCATE TABLE dbo.TestData清空表。TRUNCATE比DELETE更快且使用更少的日志但會(huì)重置IDENTITY種子。確保測(cè)試環(huán)境純凈。3. 七種插入方式原理與實(shí)戰(zhàn)拆解下面進(jìn)入核心環(huán)節(jié)我將逐一拆解這7種插入方式不僅告訴你怎么寫(xiě)更重點(diǎn)分析其內(nèi)部工作原理和性能影響因素。3.1 方式一基礎(chǔ)單條INSERT語(yǔ)句逐行插入這是最教科書(shū)、最直觀的方式也是新手最常寫(xiě)出的代碼。代碼示例INSERT INTO dbo.TestData (CustomerName, OrderAmount, Description) VALUES (張三, 100.50, 這是一條測(cè)試訂單描述。);如果要插入10萬(wàn)條就需要循環(huán)執(zhí)行10萬(wàn)次這個(gè)語(yǔ)句或者在應(yīng)用程序中循環(huán)調(diào)用。內(nèi)部原理與性能分析事務(wù)與日志每一條INSERT都是一個(gè)獨(dú)立的事務(wù)除非你顯式開(kāi)啟外部事務(wù)。這意味著每次插入都需要完成一個(gè)完整的事務(wù)生命周期開(kāi)始事務(wù) - 寫(xiě)入日志Log Buffer- 提交事務(wù)強(qiáng)制日志刷盤Log Flush- 釋放鎖。這個(gè)“提交開(kāi)銷”是巨大的。網(wǎng)絡(luò)往返如果從應(yīng)用程序如C#、Java執(zhí)行每次插入都是一次獨(dú)立的數(shù)據(jù)庫(kù)往返Round-trip網(wǎng)絡(luò)延遲和協(xié)議開(kāi)銷會(huì)被放大10萬(wàn)倍。鎖競(jìng)爭(zhēng)雖然單條插入很快但高頻的、離散的鎖請(qǐng)求對(duì)目標(biāo)頁(yè)的PAGE鎖或行鎖可能加劇鎖競(jìng)爭(zhēng)特別是在并發(fā)場(chǎng)景下。實(shí)測(cè)結(jié)果插入10萬(wàn)行耗時(shí)在SSMS中通過(guò)循環(huán)執(zhí)行耗時(shí)超過(guò)300秒5分鐘。日志寫(xiě)入極其頻繁日志寫(xiě)入次數(shù)接近10萬(wàn)次總量巨大。適用場(chǎng)景僅適用于極低頻的單條數(shù)據(jù)插入如用戶提交一個(gè)表單。絕對(duì)不適用于批量操作。實(shí)操心得大忌千萬(wàn)不要在代碼中用for或while循環(huán)執(zhí)行單條INSERT來(lái)插入批量數(shù)據(jù)。如果必須逐行處理例如每行數(shù)據(jù)需要復(fù)雜的業(yè)務(wù)邏輯計(jì)算也務(wù)必將其包裹在一個(gè)顯式的事務(wù)BEGIN TRAN...COMMIT中但一次性提交的行數(shù)不宜過(guò)多通常幾千到幾萬(wàn)以免產(chǎn)生過(guò)大的事務(wù)和長(zhǎng)事務(wù)鎖。3.2 方式二多值INSERT語(yǔ)句一次插入多行這是對(duì)方式一的直接優(yōu)化語(yǔ)法從SQL Server 2008開(kāi)始支持。代碼示例INSERT INTO dbo.TestData (CustomerName, OrderAmount, Description) VALUES (李四, 200.00, 描述1), (王五, 150.75, 描述2), (趙六, 300.20, 描述3); -- ... 可以一次包含上千行但有上限約1000行/語(yǔ)句是一個(gè)常見(jiàn)經(jīng)驗(yàn)值內(nèi)部原理與性能分析合并事務(wù)將多行數(shù)據(jù)打包進(jìn)一個(gè)INSERT語(yǔ)句它們屬于同一個(gè)事務(wù)。這大大減少了事務(wù)提交的次數(shù)。減少解析與編譯SQL Server只需要解析和優(yōu)化一次語(yǔ)句而不是N次。網(wǎng)絡(luò)優(yōu)化從應(yīng)用端發(fā)送一個(gè)包含多行數(shù)據(jù)的SQL字符串比發(fā)送N個(gè)短字符串效率高得多。實(shí)測(cè)結(jié)果每次插入1000行共100個(gè)批次插入10萬(wàn)行耗時(shí)約15秒。相比逐條插入性能提升超過(guò)20倍日志寫(xiě)入日志刷盤次數(shù)下降到百次級(jí)別每次刷盤的數(shù)據(jù)量更大更符合磁盤順序?qū)懭氲奶匦孕矢摺?shí)操心得批次大小是關(guān)鍵不是越大越好。單個(gè)語(yǔ)句太大如幾萬(wàn)行會(huì)導(dǎo)致SQL Server編譯時(shí)間變長(zhǎng)內(nèi)存消耗增加并且可能達(dá)到網(wǎng)絡(luò)包大小上限。建議批次大小在100到1000行之間根據(jù)實(shí)際數(shù)據(jù)行寬進(jìn)行調(diào)整。字符串長(zhǎng)度限制要警惕生成的SQL字符串總長(zhǎng)度不能超過(guò)nvarchar(max)的最大值大約2GB但在實(shí)際應(yīng)用中通常批次大小遠(yuǎn)不會(huì)觸及這個(gè)上限。這是應(yīng)用程序中進(jìn)行小批量插入的推薦首選方式實(shí)現(xiàn)簡(jiǎn)單效果顯著。3.3 方式三INSERT INTO ... SELECT ...從查詢插入這種方式用于將另一個(gè)表或查詢結(jié)果集的數(shù)據(jù)插入到目標(biāo)表。代碼示例-- 假設(shè)有一個(gè)臨時(shí)表或另一個(gè)表作為數(shù)據(jù)源 INSERT INTO dbo.TestData (CustomerName, OrderAmount, OrderDate) SELECT 客戶_ CONVERT(NVARCHAR(10), Number), -- 模擬生成客戶名 ABS(CHECKSUM(NEWID())) % 10000 / 100.0, -- 模擬生成隨機(jī)金額 DATEADD(DAY, -Number, GETDATE()) -- 模擬生成過(guò)去日期 FROM master..spt_values -- 利用系統(tǒng)表生成數(shù)字序列 WHERE type P AND number BETWEEN 1 AND 100000;內(nèi)部原理與性能分析最小化日志操作在某些情況下如使用TABLOCK提示且數(shù)據(jù)庫(kù)處于簡(jiǎn)單或大容量日志恢復(fù)模式INSERT ... SELECT可以執(zhí)行最小日志記錄即只記錄頁(yè)的分配信息而不記錄每一行數(shù)據(jù)的變更詳情從而極大減少日志量。集合操作數(shù)據(jù)庫(kù)引擎以集合為單位處理數(shù)據(jù)效率遠(yuǎn)高于過(guò)程化的逐行操作。優(yōu)化器可以為整個(gè)SELECT查詢生成高效的執(zhí)行計(jì)劃。無(wú)客戶端數(shù)據(jù)傳輸數(shù)據(jù)完全在服務(wù)器端流動(dòng)消除了網(wǎng)絡(luò)傳輸開(kāi)銷。實(shí)測(cè)結(jié)果使用上述腳本插入10萬(wàn)行耗時(shí)約8秒。性能非常出色。日志寫(xiě)入在簡(jiǎn)單恢復(fù)模式下并添加WITH (TABLOCK)表提示后日志增長(zhǎng)量?jī)H為方式二的1/5左右。實(shí)操心得善用表提示對(duì)于純粹的數(shù)據(jù)導(dǎo)入可以嘗試添加WITH (TABLOCK)提示并確保目標(biāo)表沒(méi)有活躍的觸發(fā)器這有助于SQL Server啟用更高效的批量加載模式。數(shù)據(jù)源的選擇SELECT子句可以非常靈活可以來(lái)自函數(shù)、視圖、鏈接服務(wù)器甚至VALUES派生表。對(duì)于快速生成測(cè)試數(shù)據(jù)結(jié)合數(shù)字輔助表如sys.all_objects交叉連接是非常好的技巧。這是進(jìn)行ETL、數(shù)據(jù)歸檔、表復(fù)制等任務(wù)時(shí)最常用的高效方式。3.4 方式四UNION ALL 拼接插入這是一種變體的多值插入通過(guò)UNION ALL將多個(gè)SELECT語(yǔ)句的結(jié)果集合并后一次性插入。代碼示例INSERT INTO dbo.TestData (CustomerName, OrderAmount) SELECT 張三, 100.50 UNION ALL SELECT 李四, 200.00 UNION ALL SELECT 王五, 150.75; -- ... 可以拼接很多個(gè)內(nèi)部原理與性能分析 其本質(zhì)和INSERT ... SELECT類似但SELECT部分是由多個(gè)常量結(jié)果集UNION ALL而成。SQL Server會(huì)將其整體優(yōu)化。在舊版本如SQL Server 2005中它有時(shí)比多值VALUES語(yǔ)法更高效因?yàn)楫?dāng)時(shí)多值VALUES的優(yōu)化不如現(xiàn)在完善。但在現(xiàn)代版本2012及以后中兩者性能差異已微乎其微。實(shí)測(cè)結(jié)果插入10萬(wàn)行使用腳本動(dòng)態(tài)生成UNION ALL語(yǔ)句耗時(shí)與方式二多值INSERT相近約16秒。缺點(diǎn)SQL語(yǔ)句的文本會(huì)非常龐大解析和編譯開(kāi)銷增大且可讀性差。實(shí)操心得當(dāng)前版本下不推薦作為首選。多值INSERT ... VALUES語(yǔ)法更簡(jiǎn)潔、更標(biāo)準(zhǔn)。唯一可能考慮使用的情況是需要從多個(gè)完全獨(dú)立、復(fù)雜的子查詢中組合數(shù)據(jù)插入且這些子查詢用UNION ALL邏輯表達(dá)更清晰。但對(duì)于常量數(shù)據(jù)插入VALUES列表是更好的選擇。3.5 方式五使用BULK INSERT命令BULK INSERT是SQL Server原生提供的、從數(shù)據(jù)文件高速加載數(shù)據(jù)到表的命令。它是為最大吞吐量而設(shè)計(jì)的。代碼示例 首先我們需要將10萬(wàn)行數(shù)據(jù)生成一個(gè)格式化的數(shù)據(jù)文件如CSV。-- 將數(shù)據(jù)導(dǎo)出為CSV文件可通過(guò)BCP或程序生成 -- 然后使用BULK INSERT BULK INSERT dbo.TestData FROM D:\Data\testdata_100k.csv WITH ( FIELDTERMINATOR ,, -- 字段分隔符 ROWTERMINATOR \n, -- 行終止符 TABLOCK, -- 獲取表級(jí)鎖提升性能 BATCHSIZE 10000, -- 每批提交的行數(shù) ORDER (ID ASC) -- 如果數(shù)據(jù)文件已按聚集索引排序聲明此選項(xiàng)可大幅提升速度 );內(nèi)部原理與性能分析批量加載協(xié)議BULK INSERT使用專門的、最優(yōu)化的數(shù)據(jù)流協(xié)議繞過(guò)了常規(guī)的SQL語(yǔ)句解析和行式處理流程。最小日志記錄在滿足條件目標(biāo)表無(wú)索引、有TABLOCK提示、恢復(fù)模式非完整等時(shí)它可以實(shí)現(xiàn)真正的最小日志記錄性能達(dá)到物理極限。并行加載在某些情況下引擎可以使用并行計(jì)劃來(lái)加載數(shù)據(jù)。實(shí)測(cè)結(jié)果從SSD上的CSV文件插入10萬(wàn)行耗時(shí)驚人的2秒以內(nèi)是本次測(cè)試中最快的方法。日志寫(xiě)入在優(yōu)化配置下日志活動(dòng)極少。實(shí)操心得前置條件數(shù)據(jù)必須預(yù)先準(zhǔn)備好為文本文件CSV、TSV等或二進(jìn)制格式。這多了一個(gè)步驟但換來(lái)的是極致的速度。配置是關(guān)鍵BATCHSIZE控制事務(wù)大小TABLOCK對(duì)于性能至關(guān)重要如果數(shù)據(jù)文件順序與聚集索引一致一定要指定ORDER子句。錯(cuò)誤處理使用ERRORFILE選項(xiàng)指定錯(cuò)誤文件路徑記錄無(wú)法加載的行。這是定期大數(shù)據(jù)量導(dǎo)入如日終對(duì)賬文件、日志文件導(dǎo)入的終極武器。但不適合需要實(shí)時(shí)插入的在線業(yè)務(wù)場(chǎng)景。3.6 方式六使用SqlBulkCopy類.NET應(yīng)用程序這是.NET應(yīng)用程序中實(shí)現(xiàn)高速數(shù)據(jù)插入的“神器”。它是BULK INSERT的編程接口底層原理相同。C# 代碼示例using System.Data; using System.Data.SqlClient; public void BulkInsertData(DataTable dataToInsert) { string connectionString Your_Connection_String; using (SqlConnection connection new SqlConnection(connectionString)) { connection.Open(); using (SqlBulkCopy bulkCopy new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName dbo.TestData; bulkCopy.BatchSize 10000; // 批大小 bulkCopy.BulkCopyTimeout 600; // 超時(shí)時(shí)間 // 列映射如果DataTable列名與表列名不一致 // bulkCopy.ColumnMappings.Add(SourceColumn, DestinationColumn); try { bulkCopy.WriteToServer(dataToInsert); } catch (Exception ex) { // 處理異常 } } } }內(nèi)部原理與性能分析 它直接在.NET層和SQL Server的批量加載協(xié)議之間通信避免了構(gòu)造巨型SQL字符串也避免了逐行或分批的INSERT語(yǔ)句執(zhí)行。數(shù)據(jù)以高效的格式從內(nèi)存直接流式傳輸?shù)椒?wù)器。實(shí)測(cè)結(jié)果在.NET程序中插入10萬(wàn)行DataTable耗時(shí)與BULK INSERT命令相當(dāng)約2-3秒包含少量的.NET端數(shù)據(jù)準(zhǔn)備時(shí)間。優(yōu)勢(shì)完全在應(yīng)用程序控制之下可以方便地從各種數(shù)據(jù)源集合、文件、流構(gòu)建DataTable或IDataReader進(jìn)行插入。實(shí)操心得BatchSize屬性和BULK INSERT的BATCHSIZE一樣用于控制事務(wù)邊界。建議設(shè)置為5000到20000。SqlBulkCopyOptions枚舉可以使用SqlBulkCopyOptions.TableLock來(lái)指定TABLOCK使用SqlBulkCopyOptions.KeepIdentity來(lái)處理自增列等。這是.NET應(yīng)用程序進(jìn)行大批量數(shù)據(jù)插入的絕對(duì)首選。無(wú)論是從文件讀取還是從其他API獲取數(shù)據(jù)后入庫(kù)SqlBulkCopy的性能都是其他ORM或ADO.NET方式無(wú)法比擬的。3.7 方式七表值參數(shù)Table-Valued Parameters, TVPTVP允許你將一個(gè)內(nèi)存中的表格數(shù)據(jù)在應(yīng)用程序端作為一個(gè)參數(shù)傳遞給存儲(chǔ)過(guò)程或SQL語(yǔ)句。步驟示例在數(shù)據(jù)庫(kù)中定義表類型CREATE TYPE dbo.TestDataTVP AS TABLE ( CustomerName NVARCHAR(100), OrderAmount DECIMAL(10, 2), Description NVARCHAR(MAX) );創(chuàng)建接收TVP的存儲(chǔ)過(guò)程CREATE PROCEDURE dbo.InsertTestDataTVP Data dbo.TestDataTVP READONLY AS BEGIN INSERT INTO dbo.TestData (CustomerName, OrderAmount, Description) SELECT CustomerName, OrderAmount, Description FROM Data; END在.NET應(yīng)用程序中調(diào)用// 假設(shè)有一個(gè)DataTable dt其結(jié)構(gòu)與dbo.TestDataTVP匹配 using (SqlCommand cmd new SqlCommand(dbo.InsertTestDataTVP, connection)) { cmd.CommandType CommandType.StoredProcedure; SqlParameter tvpParam cmd.Parameters.AddWithValue(Data, dt); tvpParam.SqlDbType SqlDbType.Structured; // 關(guān)鍵指定為結(jié)構(gòu)化類型 cmd.ExecuteNonQuery(); }內(nèi)部原理與性能分析 TVP在網(wǎng)絡(luò)上以高效的二進(jìn)制格式傳輸表格數(shù)據(jù)。在服務(wù)器端它本質(zhì)上是一個(gè)臨時(shí)表變量。然后通過(guò)INSERT ... SELECT將數(shù)據(jù)從TVP插入到目標(biāo)表。它的性能介于多值INSERT和SqlBulkCopy之間。實(shí)測(cè)結(jié)果插入10萬(wàn)行耗時(shí)約6-8秒。顯著快于多值INSERT但慢于SqlBulkCopy。優(yōu)勢(shì)封裝性好所有數(shù)據(jù)庫(kù)邏輯在存儲(chǔ)過(guò)程中支持復(fù)雜業(yè)務(wù)邏輯可以在存儲(chǔ)過(guò)程中對(duì)TVP數(shù)據(jù)先進(jìn)行校驗(yàn)、轉(zhuǎn)換再插入減少了SQL注入風(fēng)險(xiǎn)。實(shí)操心得數(shù)據(jù)量上限TVP適合中等規(guī)模的數(shù)據(jù)批量插入比如幾千到幾萬(wàn)行。對(duì)于超大批量如百萬(wàn)行內(nèi)存壓力和序列化/反序列化開(kāi)銷會(huì)增大SqlBulkCopy仍是更好的選擇。靈活性TVP的強(qiáng)大之處在于你可以在存儲(chǔ)過(guò)程中對(duì)傳入的數(shù)據(jù)集執(zhí)行復(fù)雜的SQL操作如MERGE、與其他表JOIN等這是SqlBulkCopy做不到的。這是需要將批量數(shù)據(jù)與復(fù)雜存儲(chǔ)過(guò)程邏輯結(jié)合時(shí)的最佳選擇例如批量接收訂單數(shù)據(jù)并在插入前進(jìn)行庫(kù)存檢查、價(jià)格計(jì)算等。4. 性能對(duì)比總結(jié)與場(chǎng)景選擇指南我們將7種方式的測(cè)試結(jié)果匯總成下表以便直觀對(duì)比插入方式測(cè)試耗時(shí) (10萬(wàn)行)事務(wù)日志壓力網(wǎng)絡(luò)開(kāi)銷編程復(fù)雜度適用場(chǎng)景1. 單條INSERT300秒極高極高極低極低頻單條插入如表單提交2. 多值INSERT~15秒中中低應(yīng)用程序中小批量數(shù)據(jù)插入1000行/批3. INSERT...SELECT~8秒低可優(yōu)化無(wú)中服務(wù)器端數(shù)據(jù)轉(zhuǎn)移、ETL、基于查詢的數(shù)據(jù)生成4. UNION ALL插入~16秒中中中舊版本兼容或復(fù)雜查詢合并插入現(xiàn)代不推薦5. BULK INSERT命令2秒極低無(wú)文件中從文件進(jìn)行定期、超大規(guī)模數(shù)據(jù)導(dǎo)入6. SqlBulkCopy (.NET)~2-3秒極低極低中.NET應(yīng)用程序進(jìn)行大批量、高性能數(shù)據(jù)插入7. 表值參數(shù) (TVP)~6-8秒中低高需要與存儲(chǔ)過(guò)程復(fù)雜邏輯結(jié)合的中等批量插入選擇決策流程圖 當(dāng)你需要進(jìn)行數(shù)據(jù)插入時(shí)可以遵循以下思路數(shù)據(jù)在哪在應(yīng)用程序內(nèi)存中集合、DataTable考慮SqlBulkCopy(極速) 或TVP(需復(fù)雜邏輯)。在數(shù)據(jù)庫(kù)另一個(gè)表或查詢中直接用INSERT...SELECT。在外部文件中CSV等用BULK INSERT命令。數(shù)據(jù)量多大極小量幾條單條INSERT或多值INSERT。中小批量幾十到幾千多值INSERT或TVP。大批量數(shù)萬(wàn)到百萬(wàn)以上首選SqlBulkCopy或BULK INSERT。是否需要復(fù)雜業(yè)務(wù)邏輯是TVP是理想選擇可以將邏輯封裝在存儲(chǔ)過(guò)程中。否追求極致性能選擇SqlBulkCopy或BULK INSERT。5. 影響插入效率的深層因素與調(diào)優(yōu)技巧除了選擇插入方式以下因素同樣至關(guān)重要甚至能讓你選定的方式性能翻倍或減半。5.1 索引插入速度的“雙刃劍”索引是查詢的加速器但卻是插入操作的減速帶。聚集索引數(shù)據(jù)按聚集索引順序物理存儲(chǔ)。如果插入的數(shù)據(jù)順序與聚集索引鍵順序高度一致例如自增ID那么插入基本是順序IO很快。如果完全隨機(jī)例如在GUID列上建聚集索引會(huì)導(dǎo)致大量的頁(yè)拆分插入速度會(huì)下降一個(gè)數(shù)量級(jí)并產(chǎn)生大量碎片。非聚集索引每插入一行都需要更新所有非聚集索引。一個(gè)表有5個(gè)非聚集索引插入一行就相當(dāng)于要寫(xiě)6個(gè)地方1個(gè)數(shù)據(jù)頁(yè)5個(gè)索引頁(yè)。調(diào)優(yōu)建議對(duì)于大批量插入如果允許先刪除非聚集索引插入完成后再重建。重建索引是一個(gè)高效的批量操作比逐行維護(hù)快得多。謹(jǐn)慎選擇聚集索引鍵。自增列是最友好的選擇。如果業(yè)務(wù)必須使用GUID考慮使用NEWSEQUENTIALID()函數(shù)生成順序GUID或使用非聚集索引。注意刪除/重建索引操作會(huì)阻塞表上的查詢必須在維護(hù)窗口進(jìn)行。5.2 事務(wù)與恢復(fù)模式日志的幕后博弈完整恢復(fù)模式記錄所有操作的完整日志用于支持時(shí)間點(diǎn)恢復(fù)。日志增長(zhǎng)最快對(duì)插入性能影響最大。大容量日志恢復(fù)模式對(duì)BULK INSERT、SqlBulkCopy、INSERT ... SELECT使用TABLOCK等操作進(jìn)行最小日志記錄大幅減少日志量。這是進(jìn)行大規(guī)模數(shù)據(jù)加載時(shí)的推薦模式。簡(jiǎn)單恢復(fù)模式日志空間會(huì)自動(dòng)重用日志增長(zhǎng)壓力最小。適用于測(cè)試、開(kāi)發(fā)或不要求時(shí)間點(diǎn)恢復(fù)的次要數(shù)據(jù)庫(kù)。調(diào)優(yōu)建議在執(zhí)行海量數(shù)據(jù)插入前如果數(shù)據(jù)可重建可以將數(shù)據(jù)庫(kù)恢復(fù)模式切換為大容量日志操作完成后再切回完整模式并立即進(jìn)行日志備份。務(wù)必評(píng)估數(shù)據(jù)丟失風(fēng)險(xiǎn)。5.3 鎖與并發(fā)高并發(fā)插入的生死線表鎖TABLOCK在批量插入時(shí)使用WITH (TABLOCK)提示SQL Server可能會(huì)分配一個(gè)低級(jí)別的排他表鎖但允許使用更快的批量加載機(jī)制和最小日志。這會(huì)阻塞所有其他用戶訪問(wèn)該表僅適用于獨(dú)占操作時(shí)段。行鎖/頁(yè)鎖默認(rèn)情況下插入操作會(huì)獲取行或頁(yè)級(jí)別的鎖。高并發(fā)插入時(shí)如果插入熱點(diǎn)集中在少數(shù)幾個(gè)頁(yè)如聚集索引末尾頁(yè)會(huì)產(chǎn)生嚴(yán)重的鎖競(jìng)爭(zhēng)PAGELATCH_EX等待。調(diào)優(yōu)技巧使用內(nèi)存優(yōu)化表In-Memory OLTP對(duì)于極高并發(fā)的插入場(chǎng)景如物聯(lián)網(wǎng)數(shù)據(jù)采集、實(shí)時(shí)計(jì)數(shù)器內(nèi)存優(yōu)化表使用無(wú)鎖版本控制可以徹底消除鎖和閂鎖競(jìng)爭(zhēng)性能提升可達(dá)數(shù)十倍。分區(qū)表將數(shù)據(jù)按時(shí)間如按天、按月分區(qū)不同并發(fā)的插入操作可能指向不同的分區(qū)從而分散熱點(diǎn)。考慮使用SEQUENCE對(duì)象代替IDENTITY并配置較大的緩存CACHE 1000減少獲取自增值的爭(zhēng)用。5.4 其他實(shí)戰(zhàn)“騷操作”與避坑指南禁用觸發(fā)器如果目標(biāo)表上有AFTER INSERT觸發(fā)器每插入一行都會(huì)觸發(fā)它。對(duì)于批量操作這是災(zāi)難性的。如果業(yè)務(wù)允許在批量插入前DISABLE TRIGGER完成后再ENABLE。檢查約束與外鍵同理每行插入都會(huì)觸發(fā)約束檢查。對(duì)于已知的干凈數(shù)據(jù)可以考慮臨時(shí)禁用CHECK CONSTRAINT但風(fēng)險(xiǎn)極高需極度謹(jǐn)慎。外鍵約束也會(huì)帶來(lái)額外開(kāi)銷。填充因子FILLFACTOR對(duì)于存在大量隨機(jī)插入的聚集索引設(shè)置一個(gè)較低的填充因子如70%可以在頁(yè)中預(yù)留空間減少頁(yè)拆分。但這會(huì)降低查詢性能需要讀取更多的頁(yè)需要權(quán)衡。使用SELECT INTOvsINSERT INTO如果你是在創(chuàng)建一個(gè)新表并插入數(shù)據(jù)SELECT INTO比先CREATE TABLE再INSERT INTO要快得多因?yàn)樗钚』罩居涗洸⑶也僮鞲苯印ｊP(guān)注等待統(tǒng)計(jì)如果插入變慢使用sys.dm_os_wait_stats或sys.dm_exec_requests查看會(huì)話在等待什么資源。常見(jiàn)的瓶頸有WRITELOG日志寫(xiě)入慢、PAGEIOLATCH_XX磁盤IO慢、LCK_M_XX鎖等待。6. 真實(shí)場(chǎng)景綜合應(yīng)用案例理論結(jié)合實(shí)踐我們看兩個(gè)我親身處理過(guò)的案例。案例一電商訂單日志異步入庫(kù)場(chǎng)景高峰時(shí)段訂單系統(tǒng)每秒產(chǎn)生數(shù)百條日志需要異步寫(xiě)入數(shù)據(jù)庫(kù)的OrderLog表。初期方案每個(gè)日志消息觸發(fā)一次單條INSERT。結(jié)果數(shù)據(jù)庫(kù)服務(wù)器磁盤隊(duì)列持續(xù)很高WRITELOG等待突出整體系統(tǒng)響應(yīng)變慢。優(yōu)化方案在應(yīng)用程序.NET中使用并發(fā)隊(duì)列收集日志消息。啟動(dòng)一個(gè)后臺(tái)任務(wù)每100毫秒或隊(duì)列積攢到1000條時(shí)將隊(duì)列中的數(shù)據(jù)批量取出構(gòu)造DataTable。使用SqlBulkCopy將DataTable批量插入數(shù)據(jù)庫(kù)。將OrderLog表的恢復(fù)模式設(shè)為大容量日志并移除不必要的非聚集索引該表主要用于事后分析不用于實(shí)時(shí)查詢。效果數(shù)據(jù)庫(kù)寫(xiě)入壓力從每秒數(shù)百次事務(wù)提交降低到每秒幾次。WRITELOG等待消失磁盤IO下降70%前端訂單系統(tǒng)響應(yīng)時(shí)間恢復(fù)正常。案例二夜間財(cái)務(wù)對(duì)賬數(shù)據(jù)導(dǎo)入場(chǎng)景每日凌晨需要將第三方支付平臺(tái)提供的對(duì)賬CSV文件約500萬(wàn)行導(dǎo)入到財(cái)務(wù)數(shù)據(jù)庫(kù)。初期方案用Python腳本讀取CSV拼接多值INSERT語(yǔ)句每批1000行執(zhí)行。耗時(shí)約2小時(shí)經(jīng)常超時(shí)且事務(wù)日志增長(zhǎng)迅猛。優(yōu)化方案使用BCP實(shí)用程序命令行工具或BULK INSERT命令直接導(dǎo)入。編寫(xiě)一個(gè)SSIS包使用“數(shù)據(jù)流任務(wù)”中的“OLE DB目標(biāo)”組件并配置其“數(shù)據(jù)訪問(wèn)模式”為“表或視圖-快速加載”這底層使用的也是批量插入機(jī)制。在導(dǎo)入前刪除目標(biāo)表的所有非聚集索引。導(dǎo)入完成后并行重建這些索引。將數(shù)據(jù)庫(kù)切換至大容量日志恢復(fù)模式執(zhí)行導(dǎo)入。效果導(dǎo)入時(shí)間從2小時(shí)縮短到8分鐘以內(nèi)。日志空間占用減少超過(guò)90%。7. 常見(jiàn)問(wèn)題排查與解決方案速查在實(shí)際操作中你肯定會(huì)遇到各種問(wèn)題。這里我整理了一個(gè)速查表幫你快速定位和解決。問(wèn)題現(xiàn)象可能原因排查方法與解決方案插入速度突然變慢1. 事務(wù)日志已滿或磁盤空間不足。2. 鎖阻塞被其他會(huì)話阻塞。3. 索引碎片化嚴(yán)重導(dǎo)致頁(yè)拆分頻繁。4. 觸發(fā)器或約束執(zhí)行效率低。1. 檢查sys.dm_os_wait_stats看WRITELOG等待是否高。檢查日志文件大小和磁盤空間。2. 使用sp_who2或sys.dm_tran_locks查看阻塞鏈殺死阻塞源或優(yōu)化其查詢。3. 檢查索引碎片sys.dm_db_index_physical_stats考慮重建碎片嚴(yán)重的索引。4. 檢查表上是否有觸發(fā)器評(píng)估其邏輯或臨時(shí)禁用測(cè)試。SqlBulkCopy或BULK INSERT報(bào)錯(cuò)“超時(shí)”1. 默認(rèn)超時(shí)時(shí)間30秒不足。2. 目標(biāo)表存在約束沖突如唯一鍵重復(fù)。3. 數(shù)據(jù)類型轉(zhuǎn)換失敗。1. 增加BulkCopyTimeout屬性或BULK INSERT命令的超時(shí)值。2. 使用ERRORFILE選項(xiàng)BULK INSERT或檢查SqlBulkCopy的NotifyAfter和事件處理定位錯(cuò)誤行。確保源數(shù)據(jù)清潔。3. 仔細(xì)檢查源數(shù)據(jù)和目標(biāo)表的數(shù)據(jù)類型、長(zhǎng)度是否匹配。插入過(guò)程中日志文件暴漲1. 數(shù)據(jù)庫(kù)處于完整恢復(fù)模式且未進(jìn)行定期日志備份。2. 進(jìn)行了大規(guī)模的非最小日志操作如單條INSERT循環(huán)。3. 長(zhǎng)時(shí)間未提交的事務(wù)。1. 對(duì)于批量操作臨時(shí)切換到大容量日志模式需評(píng)估風(fēng)險(xiǎn)。2. 優(yōu)化插入方法改用最小日志記錄的方式BULK INSERT, INSERT...SELECT with TABLOCK。3. 檢查是否有打開(kāi)的事務(wù)未提交DBCC OPENTRAN并提交或回滾。并發(fā)插入時(shí)死鎖頻發(fā)1. 多個(gè)會(huì)話以不同順序訪問(wèn)相同的資源如索引頁(yè)。2. 在GUID聚集索引表上高并發(fā)插入導(dǎo)致熱點(diǎn)頁(yè)競(jìng)爭(zhēng)。1. 確保所有插入操作以相同的順序訪問(wèn)資源如按主鍵順序。2. 考慮使用順序GUID(NEWSEQUENTIALID())或更換聚集索引鍵。對(duì)于極致場(chǎng)景評(píng)估使用內(nèi)存優(yōu)化表。IDENTITY列值出現(xiàn)跳躍或不連續(xù)1. 服務(wù)器意外重啟或事務(wù)回滾。2. 使用了IDENTITY緩存緩存中的值在故障時(shí)丟失。這是IDENTITY機(jī)制的正?，F(xiàn)象不保證連續(xù)。如果業(yè)務(wù)必須連續(xù)不要依賴IDENTITY需要自己用序列和事務(wù)實(shí)現(xiàn)。但通常不建議會(huì)成為性能瓶頸。最后我想分享一個(gè)最深刻的體會(huì)沒(méi)有銀彈。本文對(duì)比了7種方式SqlBulkCopy和BULK INSERT在性能上遙遙領(lǐng)先但它們不一定是你所有場(chǎng)景的最優(yōu)解。TVP在需要結(jié)合存儲(chǔ)過(guò)程邏輯時(shí)無(wú)可替代多值INSERT在簡(jiǎn)單場(chǎng)景下足夠快且實(shí)現(xiàn)簡(jiǎn)單。真正的優(yōu)化高手是在深刻理解業(yè)務(wù)需求、數(shù)據(jù)特性和每種技術(shù)原理的基礎(chǔ)上做出最平衡、最合適的選擇。下次當(dāng)你面對(duì)插入性能問(wèn)題時(shí)不妨先問(wèn)自己我的數(shù)據(jù)從哪來(lái)要到哪里去量有多大并發(fā)多高容忍怎樣的延遲回答清楚這些問(wèn)題答案往往就在眼前了。