DB2數(shù)據(jù)庫(kù)中INSERT的效率如何提高，是很多人都在思考的問(wèn)題，本文將為您介紹DB2數(shù)據(jù)庫(kù)中INSERT 處理過(guò)程,及對(duì)應(yīng)優(yōu)化方法，供您參考，希望對(duì)您有所啟迪。

語(yǔ)句準(zhǔn)備

動(dòng)態(tài)

自動(dòng)（例如在 CLP 中，或者在一次 CLI SQLExecDirect 調(diào)用中）
顯式（例如，通過(guò)一條 SQL Prepare、CLI SQLPrepare 或 JDBC prepareStatement 語(yǔ)句）
靜態(tài) 一次編譯

發(fā)送列值到服務(wù)器

在 VALUES 子句中包含多行的內(nèi)容，示例程序 sqllib/samples/cli/tbload.c
在 JDBC 中使用批處理操作
使用 load 將數(shù)據(jù)快速地裝入到一個(gè) staging 表中，然后使用 INSERT ... SELECT 填充主表。
將多條語(yǔ)句組合成一條語(yǔ)句可以通過(guò) Compound SQL 來(lái)實(shí)現(xiàn) ？？

讓客戶機(jī)與要存取的數(shù)據(jù)庫(kù)使用相同的代碼頁(yè)
減少自動(dòng)執(zhí)行數(shù)據(jù)類型轉(zhuǎn)換
將應(yīng)用程序中與插入相關(guān)的設(shè)置開(kāi)銷最小化 ？？

找到存儲(chǔ)行的地方（分配和選擇頁(yè)）

DB2 使用三種算法中的一種來(lái)確定將行插入到哪里。（如果使用了多維群集（Multi-dimensional Clustering，MDC）

缺省模式是，DB2 搜索散布在表的各頁(yè)上的自由空間控制記錄（Free Space Control Records，F(xiàn)SCR），以找到有足夠自由空間存放新行的頁(yè)。DB2 提供了 DB2MAXFSCRSEARCH 注冊(cè)表變量，以便允許將搜索范圍限制為少于缺省的 5 頁(yè)
當(dāng)表是通過(guò) ALTER TABLE 以 APPEND 模式放置時(shí)，就要使用第二種算法。這樣就完全避免了 FSCR 搜索，因?yàn)橹恍韬?jiǎn)單地將行直接放到表的末尾。
當(dāng)表有群集索引（clustering index）時(shí)，就要用到***一種算法。在這種情況下，DB2 試圖將每一行插入到有相似鍵值的一頁(yè)中。如果那一頁(yè)沒(méi)有空間了，DB2 就會(huì)嘗試附近的頁(yè)，如果附近的頁(yè)也沒(méi)有空間，DB2 就進(jìn)行 FSCR 搜索。

如果只考慮插入時(shí)間的優(yōu)化，那么使用 APPEND 模式對(duì)于批量插入是最快的一種方法，但是這種方法的效果遠(yuǎn)不如我們這里討論的很多其他方法那么成效顯著。第二好的方法應(yīng)該是采用缺省算法，但是，如果在***環(huán)境中，更改 DB2MAXFSCRSEARCH 的值影響很小，而在一個(gè) I/O 約束較少的環(huán)境中，這種更改所造成的影響就比較可觀了。 #p#

如果有群集索引，則對(duì) insert 的性能會(huì)有很大的負(fù)面影響，這一點(diǎn)也不驚奇，因?yàn)槭褂萌杭饕哪康木褪峭ㄟ^(guò)在插入時(shí)做額外的工作來(lái)提高查詢（即 select）性能的。如果的確需要群集索引，那么可以通過(guò)確保有足夠的自由空間來(lái)使其對(duì)插入的影響降至最小：使用 ALTER TABLE 增加 PCTFREE，然后使用 REORG 預(yù)留自由空間。不過(guò)，如果允許太多自由空間的存在，則可能導(dǎo)致查詢時(shí)需要讀取額外的頁(yè)，這反而大大違反了使用群集索引的本意。另一種選擇是，在批量插入之 前先刪除群集索引，而后再重新創(chuàng)建群集索引，也許這是***的方法（創(chuàng)建群集索引的開(kāi)銷跟創(chuàng)建常規(guī)索引的開(kāi)銷差不多，都不是很大，只是在插入時(shí)有額外的開(kāi)銷）。

緩沖池、I/O 和頁(yè)清除

一旦像前面討論的那樣指定了頁(yè)，那么在將行添加到該頁(yè)之前，該頁(yè)必須已經(jīng)在緩沖池中。對(duì)于批量插入，大部分頁(yè)都是***指派給表的，因此讓我們關(guān)注一下對(duì)新頁(yè)的處理。

SMS表空間中，當(dāng)需要新頁(yè)時(shí)，缺省情況下是從文件系統(tǒng)中分別為每一頁(yè)分配空間。但是，如果對(duì)數(shù)據(jù)庫(kù)運(yùn)行了 db2empfa 命令，那么每個(gè) SMS 表空間就會(huì)為新頁(yè)一次性分配一個(gè)區(qū)段。

DMS 時(shí)，更改區(qū)段大小并沒(méi)有明顯的效果。

如果表上有索引，則對(duì)于每個(gè)插入的行，都要添加一個(gè)條目到每條索引。這要求在緩沖池中存在適當(dāng)?shù)乃饕?yè)。

隨著插入的進(jìn)行，越來(lái)越多的頁(yè)中將填入被插入的行，但是，DB2 不要求在 insert 或 Commit 后將任何新插入的或更新后的數(shù)據(jù)或索引寫(xiě)入到磁盤(pán)。（這是由于 DB2 的 writeahead 日志記錄算法。但是有一個(gè)例外，這將在關(guān)于日志記錄的小節(jié)中論述到。）然而，這些頁(yè)需要在某一時(shí)刻寫(xiě)到磁盤(pán)上，這個(gè)時(shí)刻可能會(huì)在數(shù)據(jù)庫(kù)關(guān)閉時(shí)才會(huì)輪到。

一般來(lái)說(shuō)，對(duì)于批量插入，您會(huì)希望積極地進(jìn)行 異步頁(yè)清除（asynchronous page cleaning），這樣在緩沖池中就總有可用于新頁(yè)的空余位置。

如果在同一情況下進(jìn)行了積極的頁(yè)清除，則批量插入過(guò)程可能要花更長(zhǎng)的時(shí)間，但是此后緩沖池中的臟頁(yè)要少一些，從 而使得隨后的任務(wù)執(zhí)行起來(lái)性能更佳。至于那些結(jié)果中到底哪個(gè)要更好些，我們并不是總能分得清，但是通常來(lái)說(shuō)，將所有臟頁(yè)都存儲(chǔ)在緩沖池中是不可能的，所以 為了取得***性能，采取有效的頁(yè)清除是有必要的。

為了盡可能好地進(jìn)行頁(yè)清除：將 CHNGPGS_THRESH 數(shù)據(jù)庫(kù)配置參數(shù)的值從缺省的 60 減少到 5 這么低。這個(gè)參數(shù)決定緩沖池中臟頁(yè)的閾值百分比，當(dāng)臟頁(yè)達(dá)到這個(gè)百分比時(shí)，就會(huì)啟動(dòng)頁(yè)清除。#p#

嘗試啟用注冊(cè)表變量 DB2_USE_ALTERNATE_PAGE_CLEANING（在 DB2 V8 FixPak 4 中***提供）。通過(guò)將這個(gè)變量設(shè)置成 ON，可以為頁(yè)清除提供一種比缺省方法（基于 CHNGPGS_THRESH 和 LSN 間隙觸發(fā)器）更積極的方法。

至于 I/O 本身，當(dāng)需要建立索引時(shí)，可以通過(guò)使用盡可能大的緩沖池來(lái)將 I/O 活動(dòng)減至最少。如果不存在索引，則使用較大的緩沖池幫助不大，而只是推遲了 I/O。也就是說(shuō)，它允許所有新頁(yè)暫時(shí)安放在緩沖池中，但是最終仍需要將這些頁(yè)寫(xiě)到磁盤(pán)上。

當(dāng)發(fā)生將頁(yè)寫(xiě)到磁盤(pán)的 I/O 時(shí)，通過(guò)一些常規(guī)的 I/O 調(diào)優(yōu)步驟可以加快這一過(guò)程，例如：

將表空間分布在多個(gè)容器（這些容器映射到不同磁盤(pán)）。

盡可能使用最快的硬件和存儲(chǔ)管理配置，這包括磁盤(pán)和通道速度、寫(xiě)緩存以及并行寫(xiě)等因素。

避免 RAID5（除非是與像 Shark 這樣有效的存儲(chǔ)設(shè)備一起使

鎖

為獲得和釋放鎖而產(chǎn)生的 CPU 開(kāi)銷。
由獲得每一行之上的一個(gè) X 鎖以及后來(lái)釋放該鎖引起的 CPU 開(kāi)銷是比較可觀的。對(duì)于每個(gè)新行之上的鎖，惟一可以替代的是表鎖（DB2 中沒(méi)有頁(yè)鎖）。當(dāng)使用表鎖時(shí)，耗時(shí)減少了 3%。

運(yùn)行 ALTER TABLE LOCKSIZE TABLE。這將導(dǎo)致 DB2 為隨后使用該表的所有 SQL 語(yǔ)句使用一個(gè)表鎖，直到 locksize 參數(shù)改回到 ROW。
運(yùn)行 LOCK TABLE IN EXCLUSIVE MODE。這將導(dǎo)致表上立即上了一個(gè) X 鎖。注意，在下一次提交（或回滾）的時(shí)候，這個(gè)表將被釋放，因此，如果您要運(yùn)行一個(gè)測(cè)試，測(cè)試中每 N 行提交一次，那么就需要在每次提交之后重復(fù)執(zhí)行 LOCK TABLE。
使用缺省鎖，但是讓 LOCKLIST 和 MAXLOCKS 數(shù)據(jù)庫(kù)配置參數(shù)的值比較小。當(dāng)獲得少量的行鎖時(shí)，行鎖就會(huì)自動(dòng)地逐漸升級(jí)為表鎖。

可能由于鎖沖突而導(dǎo)致的并發(fā)問(wèn)題。

對(duì)于 V8 FixPak 4，或許也可以通過(guò) DB2_EVALUNCOMMITTED 注冊(cè)表變量來(lái)減少鎖沖突：如果將該變量設(shè)置為 YES，那么在很多情況下，只能獲得那些符合某個(gè)謂詞的行上的鎖，而并不是獲得被檢查的所有行上的鎖。#p#

發(fā)出一個(gè) COMMIT 命令以釋放鎖，因此如果更頻繁地提交的話就足以減輕鎖沖突的負(fù)擔(dān)。

日志記錄

缺省情況下，每條 insert 都會(huì)被記錄下來(lái)，以用于恢復(fù)。日志記錄首先被寫(xiě)到內(nèi)存中的日志緩沖池，然后再寫(xiě)到日志文件，通常是在日志緩沖池已滿或者發(fā)生了一次提交時(shí)寫(xiě)到日志文件的。 對(duì)批量插入的日志記錄的優(yōu)化實(shí)際上就是最小化日志記錄寫(xiě)的次數(shù)，以及使寫(xiě)的速度盡可能快。

這里首先考慮的是日志緩沖池的大小，這由數(shù)據(jù)庫(kù)配置參數(shù) LOGBUFSZ 來(lái)控制。該參數(shù)缺省值為 8 頁(yè)或 32 K，這與大多數(shù)批量插入所需的理想日志緩沖池大小相比要小些。

減少日志寫(xiě)的另一種可能性是對(duì)新行要插入到的那個(gè)表使用“ALTER TABLE ACTIVATE NOT LOGGED INITIALLY”(NLI)。如果這樣做了，那么在該工作單元內(nèi)不會(huì)記錄任何 insert 操作，但是這里存在兩個(gè)與 NLI 有關(guān)的重要問(wèn)題：

如果有一條語(yǔ)句失敗，那么這個(gè)表將被標(biāo)記為不可訪問(wèn)的，并且需要被刪除掉。這與其他恢復(fù)問(wèn)題（請(qǐng)參閱 SQL Reference 關(guān)于 Create Table 的討論）一起使得 NLI 在很多情況下不能成為可行的方法。
在工作單元***進(jìn)行的提交，必須等到在此工作單元內(nèi)涉及的所有臟頁(yè)都被寫(xiě)到磁盤(pán)之后才能完成。這意味著這種提交 要占用大量的時(shí)間。如果沒(méi)有積極地進(jìn)行頁(yè)清除，那么在使用 NLI 的情況下，Insert 加上提交所耗費(fèi)的總時(shí)間要更長(zhǎng)一些。將 NLI 與積極的頁(yè)清除一起使用的時(shí)候，可以大大減少耗時(shí)。如果使用 NLI，就要瞪大眼睛盯緊提交操作所耗費(fèi)的時(shí)間。

至于提高日志寫(xiě)的速度，有下面一些可能性：

將日志與新行所要插入到的表分別放在不同的磁盤(pán)上。

在操作系統(tǒng)層將日志分放到多個(gè)磁盤(pán)。

考慮為日志使用原始設(shè)備（raw device），但是要注意，這樣管理起來(lái)要更困難些。

避免使用 RAID 5，因?yàn)樗贿m合于寫(xiě)密集型（write-intensive）活動(dòng)#p#

提交

提交迫使將日志記錄寫(xiě)到磁盤(pán)上，以保證提交的插入肯定會(huì)存在于數(shù)據(jù)庫(kù)中，并且釋放新行上的鎖。這些都是有 價(jià)值的活動(dòng)，但是因?yàn)?Commit 總是要牽涉到同步 I/O（對(duì)于日志），而 insert 則不會(huì)，所以 Commit 的開(kāi)銷很容易高于 insert 的開(kāi)銷。因此，在進(jìn)行批量插入時(shí)，每一行都提交一次的做法對(duì)于性能來(lái)說(shuō)是很糟糕的，所以應(yīng)確保不使用自動(dòng)提交（對(duì)于 CLI 和 CLP 來(lái)說(shuō)缺省情況正是如此）。建議大約每 1000 行提交一次：當(dāng)每 1000 行而不是一兩行提交一次時(shí)，性能可以提高大概 10 倍。不過(guò)，一次提交多于 1000 行只能節(jié)省少量的時(shí)間，但是一旦出現(xiàn)失敗，恢復(fù)起來(lái)所花的時(shí)間要更多。

對(duì)上述方法的一種修正：如果 MINCOMMIT 數(shù)據(jù)庫(kù)配置參數(shù)的值大于 1 （缺省值），則 DB2 就不必對(duì)每次 commit 都進(jìn)行一次同步 I/O，而是等待，并試圖與一組事件一起共享日志 I/O。對(duì)于某些環(huán)境來(lái)講，這樣做是有好處，但是對(duì)于批量插入常常沒(méi)有作用，甚至有負(fù)作用，因此，如果要執(zhí)行的關(guān)鍵任務(wù)是批量插入，就應(yīng)該讓 MINCOMMIT 的值保持為 1。

索引維護(hù)

對(duì)于插入的每一行，必須添加一個(gè)條目到表上的每個(gè)索引中（包括任何主鍵索引）。這一過(guò)程主要有兩方面的代價(jià)：

遍歷每個(gè)索引樹(shù)，在樹(shù)的每一層搜索一個(gè)頁(yè)，以確定新條目必須存儲(chǔ)在哪里（索引條目總是按鍵順序存儲(chǔ)的），這一過(guò)程所引起的 CPU 開(kāi)銷；

將所有搜索到的頁(yè)讀入緩沖池，并最終將每個(gè)更新后的頁(yè)寫(xiě)到磁盤(pán)上的 I/O 開(kāi)銷。

更壞的場(chǎng)景是，在索引維護(hù)期間有大量的隨機(jī) I/O。假設(shè)要插入 10,000 行，在索引的緩沖池中有 5000 頁(yè)，并且要插入的各行的鍵值隨機(jī)分布在整個(gè)鍵范圍內(nèi)。那么，有 10,000 個(gè)這么多的葉子頁(yè)（可能還有些非葉子頁(yè)）需要進(jìn)入緩沖池，以便對(duì)它們進(jìn)行搜索和/或更新，對(duì)于一個(gè)給定的葉子頁(yè)，它預(yù)先已經(jīng)在緩沖池中的概率只有 10%。對(duì)于每次的 insert，需要讀磁盤(pán)的概率如此之高，使得這種場(chǎng)景往往性能很差。

對(duì)于逐行插入，將新行添加到已有的索引中比起創(chuàng)建一個(gè)新索引來(lái)代價(jià)要高得多。如果是插入到一個(gè)空表，應(yīng)該總是在進(jìn)行了列插入之后創(chuàng)建索 引。（注意，如果使用了 load，則應(yīng)該 預(yù)先創(chuàng)建索引。）如果要插入到一個(gè)已經(jīng)填充過(guò)的表，那么在列插入之前刪除索引，并在列插入之后重新創(chuàng)建索引，這種方法可能是最快的，但是只有在要插入相當(dāng) 多的行 -- 大概大于表的 10-20% 的時(shí)候，才能這么說(shuō)。如果為索引表空間使用較大的緩沖池，并且盡可能地將不同 insert 排序，以便鍵值是排好序的，而不是隨機(jī)的，就可以幫助加快索引維護(hù)。