DB2數(shù)據(jù)庫(kù)中提高INSERT性能的示例分析，針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比天心網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式天心網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋天心地區(qū)。費(fèi)用合理售后完善，10余年實(shí)體公司更值得信賴。

INSERT 處理過(guò)程概述

首先讓我們快速地看看插入一行時(shí)的處理步驟。這些步驟中的每一步都有優(yōu)化的潛力，對(duì)此我們?cè)诤竺鏁?huì)一一討論。

在客戶機(jī)準(zhǔn)備 語(yǔ)句。對(duì)于動(dòng)態(tài) SQL，在語(yǔ)句執(zhí)行前就要做這一步，此處的性能是很重要的；對(duì)于靜態(tài) SQL，這一步的性能實(shí)際上關(guān)系不大，因?yàn)檎Z(yǔ)句的準(zhǔn)備是事先完成的。在客戶機(jī)，將要插入的行的各個(gè) 列值組裝起來(lái)，發(fā)送到 DB2 服務(wù)器。 DB2 服務(wù)器確定將這一行插入到哪一頁(yè)中。DB2 在用于該頁(yè)的緩沖池中預(yù)留一個(gè)位置。假如 DB2 選定的是個(gè)已有的頁(yè)，那么就需要讀磁盤；假如使用一個(gè)新頁(yè)，則要在表空間(假如是SMS，也就是系統(tǒng)管理存儲(chǔ)的表空間)中為該頁(yè)物理地分配空間。插入了新行的每一頁(yè)最后都要從緩沖池寫入到磁盤。在目標(biāo)頁(yè)中對(duì)該行進(jìn)行格式化，并獲得該行上的一個(gè)X(exclusive，獨(dú)占的) 行鎖。將反映該 insert 的一條記錄寫入到日志緩沖區(qū)中。

最后提交包含該 insert 的事務(wù)，假如這時(shí)日志緩沖區(qū)中的記錄還沒有被寫入日志文檔的話，則將這些記錄寫到日志文檔中。此外，還可能發(fā)生很多類型的附加處理，這取決于數(shù)據(jù)庫(kù)配置，例如，索引或觸發(fā)器的存在。這種額外的處理對(duì)于性能來(lái)說(shuō)也是意義重大的，我們?cè)诤竺鏁?huì)討論到。

INSERT 的替代方案

在周詳討論 insert 的優(yōu)化之前，讓我們先考慮一下 insert 的兩種替代方案：load 和 import。import 實(shí)用程式實(shí)際上是 SQL INSERT 的一個(gè)前端，但他的某些功能對(duì)于您來(lái)說(shuō)也是有用的。load 也有一些有用的額外功能，但是我們使用 load 而不使用 insert 的主要原因是能夠提高性能。

load 直接格式化數(shù)據(jù)頁(yè)，而避免了由于插入導(dǎo)致的對(duì)每一行進(jìn)行處理的大部分開銷(例如，日志記錄在這里實(shí)際上是消除了)。而且，load 能夠更好地利用多處理器機(jī)器上的并行性。在 V8 load 中有兩個(gè)新功能，他們對(duì)于 load 成為 insert 的替代方案有著特別的功效，這兩個(gè)功能是：從游標(biāo)裝載和從調(diào)用層接口(CLI)應(yīng)用程式裝載。

從游標(biāo)裝載

這種方法可用于應(yīng)用程式的程式代碼(通過(guò) db2Load API)，或用于 DB2 腳本。下面是后一種情況的一個(gè)例子：

declare staffcursor cursor forselect * from staff;

load from staffcursor of cursor insert into myschema.new_staff;

這兩行能夠用下面一行替代：

insert into myschema.new_staff select * from staff

同等效的 INSERT ... SELECT 語(yǔ)句相比，從游標(biāo)裝載幾乎能夠提高 20% 的性能。

從 CLI 裝載

這種方法顯然只限于調(diào)用層接口(CLI)應(yīng)用程式，但是他很快。這種技巧很類似于數(shù)組插入，DB2 附帶了這樣的示例，使用 load 時(shí)的速度是使用經(jīng)過(guò)完全優(yōu)化的數(shù)組插入時(shí)的兩倍，幾乎要比未經(jīng)優(yōu)化的數(shù)組插入快 10 倍。

任何INSERT能夠改進(jìn)的地方

讓我們看看插入處理的一些必要步驟，連同我們能夠用來(lái)優(yōu)化這些步驟的技巧。

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

作為一條 SQL 語(yǔ)句，INSERT 語(yǔ)句在執(zhí)行之前必須由 DB2 進(jìn)行編譯。這一步驟能夠自動(dòng)發(fā)生(例如在 CLP 中，或在一次 CLI SQLExecDirect 調(diào)用中)，也能夠顯式地進(jìn)行(例如，通過(guò)一條 SQL Prepare、CLI SQLPrepare 或 JDBC prepareStatement 語(yǔ)句)。該編譯過(guò)程牽涉到授權(quán)檢查、優(yōu)化，連同將語(yǔ)句轉(zhuǎn)化為可執(zhí)行格式時(shí)所需的其他一些活動(dòng)。在編譯語(yǔ)句時(shí)，語(yǔ)句的訪問(wèn)計(jì)劃被存儲(chǔ)在包緩存中。

假如重復(fù)地執(zhí)行相同的 INSERT 語(yǔ)句，則該語(yǔ)句的訪問(wèn)計(jì)劃(通常)會(huì)進(jìn)入到包緩存中，這樣就免除了編譯的開銷。然而，假如 insert 語(yǔ)句對(duì)于每一行有不同的值，那么每一條語(yǔ)句都將被看成是惟一的，必須單獨(dú)地進(jìn)行編譯。因此，將像下面這樣的重復(fù)語(yǔ)句：

insert into mytable values (1, 'abc')

insert into mytable values (2, 'def')

等等，換成帶有參數(shù)標(biāo)記的語(yǔ)句，一次準(zhǔn)備，重復(fù)執(zhí)行，這樣做是十分可取的：

insert into mytable values (?, ?)

使用參數(shù)標(biāo)記能夠讓一系列的 insert 的運(yùn)行速度提高數(shù)倍。(在靜態(tài) SQL 程式中使用主機(jī)變量也能夠獲得類似的好處。)

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

能夠歸為這一類的優(yōu)化技巧有好幾種。最重要的一種技巧是在每條 insert 語(yǔ)句中包括多行，這樣就能夠避免對(duì)于每一行都進(jìn)行客戶機(jī)-服務(wù)器通信，同時(shí)也減少了 DB2 開銷?？捎糜诙嘈胁迦氲募记捎校?/p>

在 VALUES 子句中包含多行的內(nèi)容。例如，下面的語(yǔ)句將插入三行：INSERT INTO mytable VALUES (1, 'abc'), (2, 'def'), (3, 'ghi') 在 CLI 中使用數(shù)組插入(array insert)。這需要準(zhǔn)備一條帶參數(shù)標(biāo)記的 INSERT 語(yǔ)句，定義一個(gè)用于存儲(chǔ)要插入的值的數(shù)組，將該數(shù)組綁定到參數(shù)標(biāo)記，連同對(duì)于每個(gè)數(shù)組中的一組內(nèi)容執(zhí)行一次 insert。而且，示例程式 sqllib/samples/cli/tbload.c 提供了數(shù)組插入的基本框架(但是執(zhí)行的是 CLI LOAD)。從不使用數(shù)組改為使用包含 100 行的數(shù)組，能夠?qū)r(shí)間縮短大約 2.5 倍。所以應(yīng)該盡可能地使用包含至少 100 行的數(shù)組。

在 JDBC 中使用批處理操作。這跟 CLI 中的數(shù)組插入相同，基于相同的概念，但是實(shí)現(xiàn)細(xì)節(jié)有所不同。當(dāng)通過(guò) prepareStatement 方法準(zhǔn)備了 insert 語(yǔ)句之后，剩下的步驟是針對(duì)每一列調(diào)用適當(dāng)?shù)?setXXXX 方法(例如，setString 或 setInt)，然后是 addBatch。對(duì)于要插入的每一行，都要重復(fù)這些步驟，然后調(diào)用 executeBatch 來(lái)執(zhí)行插入。要查看這方面的例子，請(qǐng)參閱“參考資料”一節(jié)中的 JDBC Tutorial。

使用 load 將數(shù)據(jù)快速地裝入到一個(gè) staging 表中，然后使用 INSERT ... SELECT 填充主表。(通過(guò)這種方法節(jié)省下來(lái)的代價(jià)源于 load 的速度很快，再加上 INSERT ... SELECT 是在 DB2 內(nèi)(在服務(wù)器上)傳輸數(shù)據(jù)的，從而消除了通信上的代價(jià)。一般情況下我們不會(huì)使用這種方法，除非在 INSERT ... SELECT 中還要另外做 load 無(wú)法完成的處理。

假如不可能在一條 insert 語(yǔ)句中傳遞多行，那么最好是將多條 insert 語(yǔ)句組成一組，將他們一起從客戶機(jī)傳遞到服務(wù)器。(但是，這意味著每條 insert 都包含不同的值，都需要準(zhǔn)備，因而其性能實(shí)際上要比使用參數(shù)標(biāo)記情況下的性能更差一些。)將多條語(yǔ)句組合成一條語(yǔ)句能夠通過(guò) Compound SQL 來(lái)實(shí)現(xiàn)。在 SQL 中，復(fù)合語(yǔ)句是通過(guò) BEGIN ATOMIC 或 BEGIN COMPOUND 語(yǔ)句創(chuàng)建的。在 CLI 中，復(fù)合語(yǔ)句能夠通過(guò) SQLExecDirect 和 SQLExecute 調(diào)用來(lái)建立。對(duì)于 DB2 V8 FixPak 4，另一種生成復(fù)合語(yǔ)句的方法是在(對(duì)一條預(yù)處理語(yǔ)句)發(fā)出多個(gè) SQLExecute 調(diào)用之前配置語(yǔ)句屬性 SQL_ATTR_CHAINING_BEGIN，并在調(diào)用之后配置語(yǔ)句屬性 SQL_ATTR_CHAINING_END。

下面是關(guān)于該話題的其他一些建議：

假如可能的話，讓客戶機(jī)和要存取的數(shù)據(jù)庫(kù)使用相同的代碼頁(yè)，以避免在服務(wù)器上的轉(zhuǎn)換代價(jià)。數(shù)據(jù)庫(kù)的代碼頁(yè)能夠通過(guò)運(yùn)行“get db cfg for ”來(lái)確定。

在某些情況下，CLI 會(huì)自動(dòng)執(zhí)行數(shù)據(jù)類型轉(zhuǎn)換，但是這樣同時(shí)也會(huì)帶來(lái)看不見的(小小的)性能損耗。因此，盡量使插入值直接處于和相應(yīng)列對(duì)應(yīng)的格式。

將應(yīng)用程式中和插入相關(guān)的配置開銷最小化。例如，當(dāng)在 CLI 中使用數(shù)組插入時(shí)，對(duì)于整個(gè)一組插入，應(yīng)該盡量確保對(duì)于每一列只執(zhí)行一次 SQLBindParameter，而不是對(duì)每一組數(shù)組內(nèi)容都執(zhí)行一次。對(duì)于個(gè)體來(lái)說(shuō)，這些調(diào)用的代價(jià)并不高，但是這些代價(jià)是累積的。

3. 找到存儲(chǔ)行的地方

DB2 使用三種算法中的一種來(lái)確定將行插入到哪里。(假如使用了多維群集(Multi-dimensional Clustering，MDC)，則另當(dāng)別論，我們?cè)谶@里不予討論。)

缺省模式是，DB2 搜索散布在表的各頁(yè)上的自由空間控制記錄(Free Space Control Records，F(xiàn)SCR)，以找到有足夠自由空間存放新行的頁(yè)。顯然，假如每頁(yè)上的自由空間都比較少的話，就要浪費(fèi)很多的搜索時(shí)間。為了應(yīng)付這一點(diǎn)， 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è)沒有空間了，DB2 就會(huì)嘗試附近的頁(yè)，假如附近的頁(yè)也沒有空間，DB2 就進(jìn)行 FSCR 搜索。

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

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

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

每一條 insert 在執(zhí)行時(shí)，都是先將新行存儲(chǔ)在一個(gè)頁(yè)中，并最終將那個(gè)頁(yè)寫到磁盤上。一旦像前面討論的那樣指定了頁(yè)，那么在將行添加到該頁(yè)之前，該頁(yè)必須已在緩沖池中。對(duì)于批量插入，大部分頁(yè)都是最新指派給表的，因此讓我們關(guān)注一下對(duì)新頁(yè)的處理。

假如表在系統(tǒng)管理存儲(chǔ)的(System Managed Storage，SMS)表空間中，當(dāng)需要新頁(yè)時(shí)，缺省情況下是從文檔系統(tǒng)中分別為每一頁(yè)分配空間。但是，假如對(duì)數(shù)據(jù)庫(kù)運(yùn)行了 db2empfa 命令，那么每個(gè) SMS 表空間就會(huì)為新頁(yè)一次性分配一個(gè)區(qū)段。我們建議運(yùn)行 db2empfa 命令，并使用 32 頁(yè)的區(qū)段。

對(duì)于數(shù)據(jù)庫(kù)管理的存儲(chǔ)(Database Managed Storage，DMS)表空間，空間是在創(chuàng)建表空間時(shí)就預(yù)先分配的，但是頁(yè)的區(qū)段則是在插入處理過(guò)程中指派給表的。和 SMS 相比，DMS 對(duì)空間的預(yù)分配能夠提高大約 20% 的性能 -- 使用 DMS 時(shí)，更改區(qū)段大小并沒有明顯的效果。

假如表上有索引，則對(duì)于每個(gè)插入的行，都要添加一個(gè)條目到每條索引。這需要在緩沖池中存在適當(dāng)?shù)乃饕?yè)。晚些時(shí)候我們將討論索引的維護(hù)，但是現(xiàn)在只需記住，插入時(shí)對(duì)緩沖池和 I/O 的考慮也類似地適用于索引頁(yè)，對(duì)于數(shù)據(jù)頁(yè)也是相同。

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

一般來(lái)說(shuō)，對(duì)于批量插入，您會(huì)希望積極地進(jìn)行 異步頁(yè)清除(asynchronous page cleaning)，這樣在緩沖池中就總有可用于新頁(yè)的空余位置。頁(yè)清除率，或說(shuō)總?cè)表?yè)率，可能導(dǎo)致計(jì)時(shí)上的很大不同，使得性能比較容易產(chǎn)生誤解。例如，假如使用 100,000 頁(yè)的緩沖池，并且不存在頁(yè)清除，則批量插入在結(jié)束前不會(huì)有任何新的或更改過(guò)的(“臟的”)頁(yè)寫到磁盤上，但是隨后的操作(例如選擇，甚至乎關(guān)閉數(shù)據(jù)庫(kù))都將被大大推遲，因?yàn)檫@時(shí)有至多 100,000 個(gè)在插入時(shí)產(chǎn)生的臟頁(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è)清除。

嘗試啟用注冊(cè)表變量 DB2_USE_ALTERNATE_PAGE_CLEANING(在 DB2 V8 FixPak 4 中最新提供)。通過(guò)將這個(gè)變量配置成 ON，能夠?yàn)轫?yè)清除提供一種比缺省方法(基于 CHNGPGS_THRESH 和 LSN 間隙觸發(fā)器)更積極的方法。我沒有評(píng)測(cè)過(guò)其效果。請(qǐng)參閱 FixPak 4 Release Notes 以了解這方面的信息。確保 NUM_IOCLEANERS 數(shù)據(jù)庫(kù)配置參數(shù)的值至少等于數(shù)據(jù)庫(kù)中物理存儲(chǔ)設(shè)備的數(shù)量。

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

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

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

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

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

5. 鎖

缺省情況下，每一個(gè)插入的行之上都有一個(gè) X 鎖，這個(gè)鎖是在該行創(chuàng)建時(shí)就開始有的，一直到 insert 被提交。有兩個(gè)跟 insert 和鎖相關(guān)的性能問(wèn)題：

為獲得和釋放鎖而產(chǎn)生的 CPU 開銷。

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

對(duì)于經(jīng)過(guò)良好優(yōu)化的批量插入，由獲得每一行之上的一個(gè) X 鎖連同后來(lái)釋放該鎖引起的 CPU 開銷是比較可觀的。對(duì)于每個(gè)新行之上的鎖，惟一能夠替代的是表鎖(DB2 中沒有頁(yè)鎖)。當(dāng)使用表鎖時(shí)，耗時(shí)減少了 3%。有 3 種情況能夠?qū)е卤礞i的使用，在討論表鎖的缺點(diǎn)之前，我們先用一點(diǎn)時(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āng)然，任何這些的缺點(diǎn)就在于并發(fā)的影響：假如表上有一個(gè) X 鎖，那么其他應(yīng)用程式除非使用了隔離級(jí)別 UR(未提交的讀)，否則都不能訪問(wèn)該表。假如知道獨(dú)占訪問(wèn)不會(huì)導(dǎo)致問(wèn)題，那么就應(yīng)該盡量使用表鎖。但是，即使您堅(jiān)持使用行鎖，也應(yīng)記住，在批量插入期間，表中可能存在數(shù)千個(gè)有 X 鎖的新行，所以就可能和其他使用該表的應(yīng)用程式產(chǎn)生沖突。通過(guò)一些方法能夠?qū)⑦@些沖突減至最少：

確保鎖的升級(jí)不會(huì)無(wú)故發(fā)生。您可能需要加大 LOCKLIST 和/或 MAXLOCKS 的值，以允許插入應(yīng)用程式有足夠的鎖。對(duì)于其他的應(yīng)用程式，使用隔離級(jí)別 UR。

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

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

注意

在 V7 中，存在涉及 insert 和鍵鎖的并發(fā)問(wèn)題，但是在 V8 中，由于提供了 type-2 索引，這些問(wèn)題實(shí)際上已不見了。假如要遷移到 V8 中來(lái)，那么應(yīng)該確保使用帶 CONVERT 關(guān)鍵字的 REORG INDEXES 命令，以便將索引從 type-1 轉(zhuǎn)換為 type-2。

在 V7 中，插入過(guò)程中可能使用 W 或 NW 鎖，但是在 V8 中只有在使用了 type-1 索引或隔離級(jí)別為 RR 的情況下才會(huì)出現(xiàn)這兩種鎖。因此，應(yīng)盡可能避免這兩種情況。

一條 insert 所據(jù)有的鎖(通常是個(gè) X 鎖)通常不會(huì)受隔離級(jí)別的影響。例如，使用隔離級(jí)別 UR 不會(huì)阻止從插入的行上獲得鎖。然而，假如使用了 INSERT ... SELECT，則隔離級(jí)別將影響從 SELECT 獲得的鎖。

6. 日志記錄

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

這里首先考慮的是日志緩沖池的大小，這由數(shù)據(jù)庫(kù)配置參數(shù) LOGBUFSZ 來(lái)控制。該參數(shù)缺省值為 8 頁(yè)或 32 K，這和大多數(shù)批量插入所需的理想日志緩沖池大小相比要小些。舉個(gè)例子，對(duì)于一個(gè)批量插入，假設(shè)對(duì)于每一行的日志內(nèi)容有 200 字節(jié)，則在插入了 160 行之后，日志緩沖池就將被填滿。假如要插入 1000 行，因?yàn)槿罩揪彌_池將被填滿幾次，再加上提交，所以大概有 6 次日志寫。假如將 LOGBUFSZ 的值增加到 64 頁(yè)(256K)或更大，緩沖池就不會(huì)被填滿，這樣的話對(duì)于該批量插入就只有一次日志寫(在提交時(shí))。通過(guò)使用更大的 LOGBUFSZ 能夠獲得大約 13% 的性能提升。較大日志緩沖池的不利之處是，緊急事故恢復(fù)所花的時(shí)間可能要稍微長(zhǎng)一點(diǎn)。

減少日志寫的另一種可能性是對(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è)都被寫到磁盤之后才能完成。這意味著這種提交要占用大量的時(shí)間。假如沒有積極地進(jìn)行頁(yè)清除，那么在使用 NLI 的情況下，Insert 加上提交所耗費(fèi)的總時(shí)間要更長(zhǎng)一些。將 NLI 和積極的頁(yè)清除一起使用的時(shí)候，能夠大大減少耗時(shí)。假如使用 NLI，就要瞪大眼睛盯緊提交操作所耗費(fèi)的時(shí)間。

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

將日志和新行所要插入到的表分別放在不同的磁盤上。

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

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

避免使用 RAID 5，因?yàn)樗贿m合于寫密集型(write-intensive)活動(dòng)。

7. 提交

提交迫使將日志記錄寫到磁盤上，以確保提交的插入肯定會(huì)存在于數(shù)據(jù)庫(kù)中，并且釋放新行上的鎖。這些都是有價(jià)值的活動(dòng)，但是因?yàn)?Commit 總是要牽涉到同步 I/O(對(duì)于日志)，而 insert 則不會(huì)，所以 Commit 的開銷很容易高于 insert 的開銷。因此，在進(jìn)行批量插入時(shí)，每一行都提交一次的做法對(duì)于性能來(lái)說(shuō)是很糟糕的，所以應(yīng)確保不使用自動(dòng)提交(對(duì)于 CLI 和 CLP 來(lái)說(shuō)缺省情況正是如此)。建議大約每 1000 行提交一次：當(dāng)每 1000 行而不是一兩行提交一次時(shí)，性能能夠提高大概 10 倍。但是，一次提交多于 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ì)于批量插入常常沒有作用，甚至有負(fù)作用，因此，假如要執(zhí)行的關(guān)鍵任務(wù)是批量插入，就應(yīng)該讓 MINCOMMIT 的值保持為 1。

能夠選擇性地進(jìn)行改進(jìn)的地方

對(duì)于一次 insert，有幾種類型的處理將自動(dòng)發(fā)生。假如您的主要目標(biāo)只是減少插入時(shí)間，那么最簡(jiǎn)單的方法是避免任何這些處理的開銷，但是假如從總體上考慮的話，這樣做未必值得。讓我們依次進(jìn)行討論。

索引維護(hù)

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

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

將任何搜索到的頁(yè)讀入緩沖池，并最終將每個(gè)更新后的頁(yè)寫到磁盤上的 I/O 開銷。

更壞的場(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ù)先已在緩沖池中的概率只有 10%。對(duì)于每次的 insert，需要讀磁盤的概率如此之高，使得這種場(chǎng)景往往性能很差。

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

關(guān)于DB2數(shù)據(jù)庫(kù)中提高INSERT性能的示例分析問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關(guān)知識(shí)。