如何在DB2中提高Insert性能
INSERT處理過程概述
首先讓我們快速地看看insert一行時的處理步驟。這些步驟中的每一步都有優化的潛力，對此我們在后面會一一討論。
1、在客戶機準備語句。對于動態SQL，在語句執行前就要做這一步，此處的性能是很重要的;對于靜態SQL，這一步的性能實際上關系不大，因為語句的準備是事先完成的。
2、在客戶機，將要插入的行的各個列值組裝起來，發送到DB2服務器。
3、DB2服務器確定將這一行插入到哪一頁中。
4、DB2在用于該頁的緩沖池中預留一個位置。如果DB2選定的是一個已有的頁，那么就需要讀磁盤;如果使用一個新頁，則要在表空間(如果是SMS，也就是系統管理存儲的表空間)中為該頁物理地分配空間。插入了新行的每一頁最后都要從緩沖池寫入到磁盤。
5、在目標頁中對該行進行格式化，并獲得該行上的一個X(exclusive，獨占的)行鎖。
6、將反映該insert的一條記錄寫入到日志緩沖區中。
7、最后提交包含該insert的事務，如果這時日志緩沖區中的記錄還沒有被寫入日志文件的話，則將這些記錄寫到日志文件中。
此外，還可能發生很多類型的附加處理，這取決于數據庫配置，例如，索引或觸發器的存在。這種額外的處理對于性能來說也是意義重大的，我們在后面會討論到。
INSERT的替代方案
在詳細討論insert的優化之前，讓我們先考慮一下insert的兩種替代方案：load和import。import實用程序實際上是SQLINSERT的一個前端，但它的某些功能對于您來說也是有用的。load也有一些有用的額外功能，但是我們使用load而不使用insert的主要原因是可以提高性能。
load直接格式化數據頁，而避免了由于插入導致的對每一行進行處理的大部分開銷(例如，日志記錄在這里實際上是消除了)。而且，load可以更好地利用多處理器機器上的并行性。在V8load中有兩個新功能，它們對于load成為insert的替代方案有著特別的功效，這兩個功能是：從游標裝載和從調用層接口(CLI)應用程序裝載。
從游標裝載
這種方法可用于應用程序的程序代碼(通過db2LoadAPI)，或用于DB2腳本。下面是后一種情況的一個例子：
declarestaffcursorcursorforselect*fromstaff;
loadfromstaffcursorofcursorinsertintomyschema.new_staff;
這兩行可以用下面一行替代：
insertintomyschema.new_staffselect*fromstaff
同等效的INSERT...SELECT語句相比，從游標裝載幾乎可以提高20%的性能。
從CLI裝載
這種方法顯然只限于調用層接口(CLI)應用程序，但是它非常快。這種技巧非常類似于數組插入，DB2附帶了這樣的示例，使用load時的速度是使用經過完全優化的數組插入時的兩倍，幾乎要比未經優化的數組插入快10倍。
所有INSERT可以改進的地方
讓我們看看插入處理的一些必要步驟，以及我們可以用來優化這些步驟的技巧。
1.語句準備
作為一條SQL語句，INSERT語句在執行之前必須由DB2進行編譯。這一步驟可以自動發生(例如在CLP中，或者在一次CLISQLExecDirect調用中)，也可以顯式地進行(例如，通過一條SQLPrepare、CLISQLPrepare或JDBCprepareStatement語句)。該編譯過程牽涉到授權檢查、優化，以及將語句轉化為可執行格式時所需的其他一些活動。在編譯語句時，語句的訪問計劃被存儲在包緩存中。
如果重復地執行相同的INSERT語句，則該語句的訪問計劃(通常)會進入到包緩存中，這樣就免除了編譯的開銷。然而，如果insert語句對于每一行有不同的值，那么每一條語句都將被看成是惟一的，必須單獨地進行編譯。因此，將像下面這樣的重復語句：
insertintomytablevalues(1,'abc')
insertintomytablevalues(2,'def')
換成帶有參數標記的語句，一次準備，重復執行，這樣做是十分可取的：
insertintomytablevalues(?,?)
使用參數標記可以讓一系列的insert的運行速度提高數倍。(在靜態SQL程序中使用主機變量也可以獲得類似的好處。)
2.發送列值到服務器
可以歸為這一類的優化技巧有好幾種。最重要的一種技巧是在每條insert語句中包括多行，這樣就可以避免對于每一行都進行客戶機-服務器通信，同時也減少了DB2開銷。可用于多行插入的技巧有：
在VALUES子句中包含多行的內容。例如，下面的語句將插入三行：INSERTINTOmytableVALUES(1,'abc'),(2,'def'),(3,'ghi')
在CLI中使用數組插入(arrayinsert)。這需要準備一條帶參數標記的INSERT語句，定義一個用于存儲要插入的值的數組，將該數組綁定到參數標記，以及對于每個數組中的一組內容執行一次insert。而且，示例程序sqllib/samples/cli/tbload.c提供了數組插入的基本框架(但是執行的是CLILOAD)。從不使用數組改為使用包含100行的數組，可以將時間縮短大約2.5倍。所以應該盡可能地使用包含至少100行的數組。
在JDBC中使用批處理操作。這跟CLI中的數組插入一樣，基于相同的概念，但是實現細節有所不同。當通過prepareStatement方法準備了insert語句之后，剩下的步驟是針對每一列調用適當的setXXXX方法(例如，setString或setInt)，然后是addBatch。對于要插入的每一行，都要重復這些步驟，然后調用executeBatch來執行插入。要查看這方面的例子，請參閱“參考資料”一節中的JDBCTutorial。
使用load將數據快速地裝入到一個staging表中，然后使用INSERT...SELECT填充主表。(通過這種方法節省下來的代價源于load的速度非常快，再加上INSERT...SELECT是在DB2內(在服務器上)傳輸數據的，從而消除了通信上的代價。一般情況下我們不會使用這種方法，除非在INSERT...SELECT中還要另外做load無法完成的處理。
如果不可能在一條insert語句中傳遞多行，那么最好是將多條insert語句組成一組，將它們一起從客戶機傳遞到服務器。(不過，這意味著每條insert都包含不同的值，都需要準備，因而其性能實際上要比使用參數標記情況下的性能更差一些。)將多條語句組合成一條語句可以通過CompoundSQL來實現：
在SQL中，復合語句是通過BEGINATOMIC或BEGINCOMPOUND語句創建的。
在CLI中，復合語句可以通過SQLExecDirect和SQLExecute調用來建立。對于DB2V8FixPak4，另一種生成復合語句的方法是在(對一條預處理語句)發出多個SQLExecute調用之前設置語句屬性SQL_ATTR_CHAINING_BEGIN，并在調用之后設置語句屬性SQL_ATTR_CHAINING_END。
下面是關于該話題的其他一些建議：
如果可能的話，讓客戶機與要存取的數據庫使用相同的代碼頁，以避免在服務器上的轉換代價。數據庫的代碼頁可以通過運行“getdbcfgfor”來確定。
在某些情況下，CLI會自動執行數據類型轉換，但是這樣同時也會帶來看不見的(小小的)性能損耗。因此，盡量使插入值直接處于與相應列對應的格式。
將應用程序中與插入相關的設置開銷最小化。例如，當在CLI中使用數組插入時，對于整個一組插入，應該盡量保證對于每一列只執行一次SQLBindParameter，而不是對每一組數組內容都執行一次。對于個體來說，這些調用的代價并不高，但是這些代價是累積的。#p#
3.找到存儲行的地方
DB2使用三種算法中的一種來確定將行插入到哪里。(如果使用了多維群集(Multi-dimensionalClustering，MDC)，則另當別論，我們在這里不予討論。)
缺省模式是，DB2搜索散布在表的各頁上的自由空間控制記錄(FreeSpaceControlRecords，FSCR)，以找到有足夠自由空間存放新行的頁。顯然，如果每頁上的自由空間都比較少的話，就要浪費很多的搜索時間。為了應付這一點，DB2提供了DB2MAXFSCRSEARCH注冊表變量，以便允許將搜索范圍限制為少于缺省的5頁。
當表是通過ALTERTABLE以APPEND模式放置時，就要使用第二種算法。這樣就完全避免了FSCR搜索，因為只需簡單地將行直接放到表的末尾。
當表有群集索引(clusteringindex)時，就要用到最后一種算法。在這種情況下，DB2試圖將每一行插入到有相似鍵值的一頁中。如果那一頁沒有空間了，DB2就會嘗試附近的頁，如果附近的頁也沒有空間，DB2就進行FSCR搜索。
如果只考慮插入時間的優化，那么使用APPEND模式對于批量插入是最快的一種方法，但是這種方法的效果遠不如我們這里討論的很多其他方法那么成效顯著。第二好的方法應該是采用缺省算法，但是，如果在最佳環境中，更改DB2MAXFSCRSEARCH的值影響很小，而在一個I/O約束較少的環境中，這種更改所造成的影響就比較可觀了。
如果有群集索引，則對insert的性能會有很大的負面影響，這一點也不驚奇，因為使用群集索引的目的就是通過在插入時做額外的工作來提高查詢(即select)性能的。如果的確需要群集索引，那么可以通過確保有足夠的自由空間來使其對插入的影響降至最小：使用ALTERTABLE增加PCTFREE，然后使用REORG預留自由空間。不過，如果允許太多自由空間的存在，則可能導致查詢時需要讀取額外的頁，這反而大大違反了使用群集索引的本意。另一種選擇是，在批量插入之前先刪除群集索引，而后再重新創建群集索引，也許這是最優的方法(創建群集索引的開銷跟創建常規索引的開銷差不多，都不是很大，只是在插入時有額外的開銷)。
4.緩沖池、I/O和頁清除
每一條insert在執行時，都是先將新行存儲在一個頁中，并最終將那個頁寫到磁盤上。一旦像前面討論的那樣指定了頁，那么在將行添加到該頁之前，該頁必須已經在緩沖池中。對于批量插入，大部分頁都是最新指派給表的，因此讓我們關注一下對新頁的處理。
如果表在系統管理存儲的(SystemManagedStorage，SMS)表空間中，當需要新頁時，缺省情況下是從文件系統中分別為每一頁分配空間。但是，如果對數據庫運行了db2empfa命令，那么每個SMS表空間就會為新頁一次性分配一個區段。我們建議運行db2empfa命令，并使用32頁的區段。
對于數據庫管理的存儲(DatabaseManagedStorage，DMS)表空間，空間是在創建表空間時就預先分配的，但是頁的區段則是在插入處理過程中指派給表的。與SMS相比，DMS對空間的預分配可以提高大約20%的性能--使用DMS時，更改區段大小并沒有明顯的效果。
如果表上有索引，則對于每個插入的行，都要添加一個條目到每條索引。這要求在緩沖池中存在適當的索引頁。晚些時候我們將討論索引的維護，但是現在只需記住，插入時對緩沖池和I/O的考慮也類似地適用于索引頁，對于數據頁也是一樣。
隨著插入的進行，越來越多的頁中將填入被插入的行，但是，DB2不要求在insert或Commit后將任何新插入的或更新后的數據或索引寫入到磁盤。(這是由于DB2的writeahead日志記錄算法。但是有一個例外，這將在關于日志記錄的小節中論述到。)然而，這些頁需要在某一時刻寫到磁盤上，這個時刻可能會在數據庫關閉時才會輪到。
一般來說，對于批量插入，您會希望積極地進行異步頁清除(asynchronouspagecleaning)，這樣在緩沖池中就總有可用于新頁的空余位置。頁清除率，或者說總缺頁率，可能導致計時上的很大不同，使得性能比較容易產生誤解。例如，如果使用100,000頁的緩沖池，并且不存在頁清除，則批量插入在結束前不會有任何新的或更改過的(“臟的”)頁寫到磁盤上，但是隨后的操作(例如選擇，甚至乎關閉數據庫)都將被大大推遲，因為這時有至多100,000個在插入時產生的臟頁要寫到磁盤上。另一方面，如果在同一情況下進行了積極的頁清除，則批量插入過程可能要花更長的時間，但是此后緩沖池中的臟頁要少一些，從而使得隨后的任務執行起來性能更佳。至于那些結果中到底哪個要更好些，我們并不是總能分得清，但是通常來說，將所有臟頁都存儲在緩沖池中是不可能的，所以為了取得最佳性能，采取有效的頁清除是有必要的。
為了盡可能好地進行頁清除：
將CHNGPGS_THRESH數據庫配置參數的值從缺省的60減少到5這么低。這個參數決定緩沖池中臟頁的閾值百分比，當臟頁達到這個百分比時，就會啟動頁清除。
嘗試啟用注冊表變量DB2_USE_ALTERNATE_PAGE_CLEANING(在DB2V8FixPak4中最新提供)。通過將這個變量設置成ON，可以為頁清除提供一種比缺省方法(基于CHNGPGS_THRESH和LSN間隙觸發器)更積極的方法。我沒有評測過其效果。請參閱FixPak4ReleaseNotes以了解這方面的信息。
確保NUM_IOCLEANERS數據庫配置參數的值至少等于數據庫中物理存儲設備的數量。
至于I/O本身，當需要建立索引時，可以通過使用盡可能大的緩沖池來將I/O活動減至最少。如果不存在索引，則使用較大的緩沖池幫助不大，而只是推遲了I/O。也就是說，它允許所有新頁暫時安放在緩沖池中，但是最終仍需要將這些頁寫到磁盤上。
當發生將頁寫到磁盤的I/O時，通過一些常規的I/O調優步驟可以加快這一過程，例如：
將表空間分布在多個容器(這些容器映射到不同磁盤)。
盡可能使用最快的硬件和存儲管理配置，這包括磁盤和通道速度、寫緩存以及并行寫等因素。
避免RAID5(除非是與像Shark這樣有效的存儲設備一起使用)。#p#
5.鎖
缺省情況下，每一個插入的行之上都有一個X鎖，這個鎖是在該行創建時就開始有的，一直到insert被提交。有兩個跟insert和鎖相關的性能問題：
◆為獲得和釋放鎖而產生的CPU開銷。
◆可能由于鎖沖突而導致的并發問題。
對于經過良好優化的批量插入，由獲得每一行之上的一個X鎖以及后來釋放該鎖引起的CPU開銷是比較可觀的。對于每個新行之上的鎖，惟一可以替代的是表鎖(DB2中沒有頁鎖)。當使用表鎖時，耗時減少了3%。有3種情況可以導致表鎖的使用，在討論表鎖的缺點之前，我們先用一點時間看看這3種情況：
運行ALTERTABLELOCKSIZETABLE。這將導致DB2為隨后使用該表的所有SQL語句使用一個表鎖，直到locksize參數改回到ROW。
運行LOCKTABLEINEXCLUSIVEMODE。這將導致表上立即上了一個X鎖。注意，在下一次提交(或回滾)的時候，這個表將被釋放，因此，如果您要運行一個測試，測試中每N行提交一次，那么就需要在每次提交之后重復執行LOCKTABLE。
使用缺省鎖，但是讓LOCKLIST和MAXLOCKS數據庫配置參數的值比較小。當獲得少量的行鎖時，行鎖就會自動地逐漸升級為表鎖。
當然，所有這些的缺點就在于并發的影響：如果表上有一個X鎖，那么其他應用程序除非使用了隔離級別UR(未提交的讀)，否則都不能訪問該表。如果知道獨占訪問不會導致問題，那么就應該盡量使用表鎖。但是，即使您堅持使用行鎖，也應記住，在批量插入期間，表中可能存在數千個有X鎖的新行，所以就可能與其他使用該表的應用程序產生沖突。通過一些方法可以將這些沖突減至最少：
確保鎖的升級不會無故發生。您可能需要加大LOCKLIST和/或MAXLOCKS的值，以允許插入應用程序有足夠的鎖。
對于其他的應用程序，使用隔離級別UR。
對于V8FixPak4，或許也可以通過DB2_EVALUNCOMMITTED注冊表變量來減少鎖沖突：如果將該變量設置為YES，那么在很多情況下，只能獲得那些符合某個謂詞的行上的鎖，而并不是獲得被檢查的所有行上的鎖。
發出一個COMMIT命令以釋放鎖，因此如果更頻繁地提交的話就足以減輕鎖沖突的負擔。
注意
在V7中，存在涉及insert和鍵鎖的并發問題，但是在V8中，由于提供了type-2索引，這些問題實際上已經不見了。如果要遷移到V8中來，那么應該確保使用帶CONVERT關鍵字的REORGINDEXES命令，以便將索引從type-1轉換為type-2。
在V7中，插入過程中可能使用W或NW鎖，但是在V8中只有在使用了type-1索引或者隔離級別為RR的情況下才會出現這兩種鎖。因此，應盡可能避免這兩種情況。
一條insert所據有的鎖(通常是一個X鎖)通常不會受隔離級別的影響。例如，使用隔離級別UR不會阻止從插入的行上獲得鎖。然而，如果使用了INSERT...SELECT，則隔離級別將影響從SELECT獲得的鎖。#p#
6.日志記錄
缺省情況下，每條insert都會被記錄下來，以用于恢復。日志記錄首先被寫到內存中的日志緩沖池，然后再寫到日志文件，通常是在日志緩沖池已滿或者發生了一次提交時寫到日志文件的。對批量插入的日志記錄的優化實際上就是最小化日志記錄寫的次數，以及使寫的速度盡可能快。
這里首先考慮的是日志緩沖池的大小，這由數據庫配置參數LOGBUFSZ來控制。該參數缺省值為8頁或32K，這與大多數批量插入所需的理想日志緩沖池大小相比要小些。舉個例子，對于一個批量插入，假設對于每一行的日志內容有200字節，則在插入了160行之后，日志緩沖池就將被填滿。如果要插入1000行，因為日志緩沖池將被填滿幾次，再加上提交，所以大概有6次日志寫。如果將LOGBUFSZ的值增加到64頁(256K)或者更大，緩沖池就不會被填滿，這樣的話對于該批量插入就只有一次日志寫(在提交時)。通過使用更大的LOGBUFSZ可以獲得大約13%的性能提升。較大日志緩沖池的不利之處是，緊急事故恢復所花的時間可能要稍微長一點。
減少日志寫的另一種可能性是對新行要插入到的那個表使用“ALTERTABLEACTIVATENOTLOGGEDINITIALLY”(NLI)。如果這樣做了，那么在該工作單元內不會記錄任何insert操作，但是這里存在兩個與NLI有關的重要問題：
如果有一條語句失敗，那么這個表將被標記為不可訪問的，并且需要被刪除掉。這與其他恢復問題(請參閱SQLReference關于CreateTable的討論)一起使得NLI在很多情況下不能成為可行的方法。
在工作單元最后進行的提交，必須等到在此工作單元內涉及的所有臟頁都被寫到磁盤之后才能完成。這意味著這種提交要占用大量的時間。如果沒有積極地進行頁清除，那么在使用NLI的情況下，Insert加上提交所耗費的總時間要更長一些。將NLI與積極的頁清除一起使用的時候，可以大大減少耗時。如果使用NLI，就要瞪大眼睛盯緊提交操作所耗費的時間。
至于提高日志寫的速度，有下面一些可能性：
將日志與新行所要插入到的表分別放在不同的磁盤上。
在操作系統層將日志分放到多個磁盤。
考慮為日志使用原始設備(rawdevice)，但是要注意，這樣管理起來要更困難些。
避免使用RAID5，因為它不適合于寫密集型(write-intensive)活動。
7.提交
提交迫使將日志記錄寫到磁盤上，以保證提交的插入肯定會存在于數據庫中，并且釋放新行上的鎖。這些都是有價值的活動，但是因為Commit總是要牽涉到同步I/O(對于日志)，而insert則不會，所以Commit的開銷很容易高于insert的開銷。因此，在進行批量插入時，每一行都提交一次的做法對于性能來說是很糟糕的，所以應確保不使用自動提交(對于CLI和CLP來說缺省情況正是如此)。建議大約每1000行提交一次：當每1000行而不是一兩行提交一次時，性能可以提高大概10倍。不過，一次提交多于1000行只能節省少量的時間，但是一旦出現失敗，恢復起來所花的時間要更多。
對上述方法的一種修正：如果MINCOMMIT數據庫配置參數的值大于1(缺省值)，則DB2就不必對每次commit都進行一次同步I/O，而是等待，并試圖與一組事件一起共享日志I/O。對于某些環境來講，這樣做是有好處，但是對于批量插入常常沒有作用，甚至有負作用，因此，如果要執行的關鍵任務是批量插入，就應該讓MINCOMMIT的值保持為1。
可以選擇性地進行改進的地方
對于一次insert，有幾種類型的處理將自動發生。如果您的主要目標只是減少插入時間，那么最簡單的方法是避免所有這些處理的開銷，但是如果從總體上考慮的話，這樣做未必值得。讓我們依次進行討論。
索引維護
對于插入的每一行，必須添加一個條目到表上的每個索引中(包括任何主鍵索引)。這一過程主要有兩方面的代價：
遍歷每個索引樹，在樹的每一層搜索一個頁，以確定新條目必須存儲在哪里(索引條目總是按鍵順序存儲的)，這一過程所引起的CPU開銷;
將所有搜索到的頁讀入緩沖池，并最終將每個更新后的頁寫到磁盤上的I/O開銷。
更壞的場景是，在索引維護期間有大量的隨機I/O。假設要插入10,000行，在索引的緩沖池中有5000頁，并且要插入的各行的鍵值隨機分布在整個鍵范圍內。那么，有10,000個這么多的葉子頁(可能還有些非葉子頁)需要進入緩沖池，以便對它們進行搜索和/或更新，對于一個給定的葉子頁，它預先已經在緩沖池中的概率只有10%。對于每次的insert，需要讀磁盤的概率如此之高，使得這種場景往往性能很差。
對于逐行插入，將新行添加到已有的索引中比起創建一個新索引來代價要高得多。如果是插入到一個空表，應該總是在進行了列插入之后創建索引。(注意，如果使用了load，則應該預先創建索引。)如果要插入到一個已經填充過的表，那么在列插入之前刪除索引，并在列插入之后重新創建索引，這種方法可能是最快的，但是只有在要插入相當多的行--大概大于表的10-20%的時候，才能這么說。如果為索引表空間使用較大的緩沖池，并且盡可能地將不同insert排序，以便鍵值是排好序的，而不是隨機的，就可以幫助加快索引維護。
【編輯推薦】
