本文精妙地解釋了執(zhí)行 insert 操作時(shí)所發(fā)生的事情，考察了 insert 的一些替代方案，并研究了影響 insert 性能的一些問題，例如鎖、索引維護(hù)以及約束管理。
在使用 DB2® Universal Database (UDB) 的時(shí)候，行的插入是我們要執(zhí)行的最常見、也是最重要的任務(wù)之一。本文是關(guān)于優(yōu)化 insert 、尤其是插入量比較大的 insert 的一個(gè)技巧匯編。談到性能，往往都存在著某些權(quán)衡，這里也不例外。我們將討論在優(yōu)化 insert 的過程中可能帶來的權(quán)衡問題。例如，您對(duì) insert 采用了某種優(yōu)化技巧，但是這種技巧可能要求在 insert 之后還要進(jìn)行附加的處理，或者可能影響查詢的性能。我會(huì)提供一些性能測(cè)試的結(jié)果，以便讓您了解很多優(yōu)化技巧的作用。 附錄 A包含了對(duì)這些結(jié)果的一個(gè)小結(jié)，并且編了號(hào)。我將在全文各處以測(cè)試編號(hào)來引用結(jié)果。在后面的 結(jié)束語一節(jié)中，總結(jié)了大部分有益的技巧，而 附錄 B則列出了本文給出的所有建議。在本文中，我們無意研究關(guān)于如何實(shí)現(xiàn)這些技巧的細(xì)節(jié)，但是這方面的信息可以在 DB2 手冊(cè)中找到。請(qǐng)閱讀 參考資料一節(jié)，以了解更多細(xì)節(jié)。
  |
|
首先讓我們快速地看看插入一行時(shí)的處理步驟。這些步驟中的每一步都有優(yōu)化的潛力，對(duì)此我們?cè)诤竺鏁?huì)一一討論。
 |
|
在詳細(xì)討論 insert 的優(yōu)化之前，讓我們先考慮一下 insert 的兩種替代方案：load 和 import。import 實(shí)用程序?qū)嶋H上是 SQL INSERT 的一個(gè)前端，但它的某些功能對(duì)于您來說也是有用的。load 也有一些有用的額外功能，但是我們使用 load 而不使用 insert 的主要原因是可以提高性能。這里我們不會(huì)進(jìn)一步討論 import，不過讀者可以參閱后面的 參考資料一節(jié)，在那里可以找到指向 Data Movement Utilities Guide 的鏈接，該指南討論了 import 與 load 之間的不同之處。
load 直接格式化數(shù)據(jù)頁，而避免了由于插入導(dǎo)致的對(duì)每一行進(jìn)行處理的大部分開銷（例如，日志記錄在這里實(shí)際上是消除了）。而且，load 可以更好地利用多處理器機(jī)器上的并行性。在 V8 load 中有兩個(gè)新功能，它們對(duì)于 load 成為 insert 的替代方案有著特別的功效，這兩個(gè)功能是：從游標(biāo)裝載和從調(diào)用層接口（CLI）應(yīng)用程序裝載。
這種方法可用于應(yīng)用程序的程序代碼（通過 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
附錄 A中的 test 6 和 9 表明，同等效的 INSERT ... SELECT 語句相比，從游標(biāo)裝載幾乎可以提高 20% 的性能。
這種方法顯然只限于調(diào)用層接口（CLI）應(yīng)用程序，但是它非?？?。這種技巧非常類似于數(shù)組插入（ 后面會(huì)討論），DB2 附帶了這樣的示例，請(qǐng)查看 sqllib/samples/cli/tbload.c。通過查看附錄 A 中的 test 79我們可以看到，使用 load 時(shí)的速度是使用經(jīng)過完全優(yōu)化的數(shù)組插入（test 71）時(shí)的兩倍，幾乎要比未經(jīng)優(yōu)化的數(shù)組插入（比如 test 69）快 10 倍。
|
|
讓我們看看插入處理的一些必要步驟，以及我們可以用來優(yōu)化這些步驟的技巧。
作為一條 SQL 語句，INSERT 語句在執(zhí)行之前必須由 DB2 進(jìn)行編譯。這一步驟可以自動(dòng)發(fā)生（例如在 CLP 中，或者在一次 CLI SQLExecDirect 調(diào)用中），也可以顯式地進(jìn)行（例如，通過一條 SQL Prepare、CLI SQLPrepare 或 JDBC prepareStatement 語句）。該編譯過程牽涉到授權(quán)檢查、優(yōu)化，以及將語句轉(zhuǎn)化為可執(zhí)行格式時(shí)所需的其他一些活動(dòng)。在編譯語句時(shí)，語句的訪問計(jì)劃被存儲(chǔ)在包緩存中。
如果重復(fù)地執(zhí)行相同的 INSERT 語句，則該語句的訪問計(jì)劃（通常）會(huì)進(jìn)入到包緩存中，這樣就免除了編譯的開銷。然而，如果 insert 語句對(duì)于每一行有不同的值，那么每一條語句都將被看成是惟一的，必須單獨(dú)地進(jìn)行編譯。因此，將像下面這樣的重復(fù)語句： insert into mytable values (1, 'abc')
insert into mytable values (2, 'def')
等等，
換成帶有參數(shù)標(biāo)記的語句，一次準(zhǔn)備，重復(fù)執(zhí)行，這樣做是十分可取的： insert into mytable values (?, ?)
將 test 1與 test 2相比， 61-64與 65-68相比，我們可以看到，使用參數(shù)標(biāo)記可以讓一系列的 insert 的運(yùn)行速度提高數(shù)倍。（在靜態(tài) SQL 程序中使用主機(jī)變量也可以獲得類似的好處。）
可以歸為這一類的優(yōu)化技巧有好幾種。最重要的一種技巧是在每條 insert 語句中包括多行，這樣就可以避免對(duì)于每一行都進(jìn)行客戶機(jī)-服務(wù)器通信，同時(shí)也減少了 DB2 開銷?？捎糜诙嘈胁迦氲募记捎校?ul>
如果不可能在一條 insert 語句中傳遞多行，那么最好是將多條 insert 語句組成一組，將它們一起從客戶機(jī)傳遞到服務(wù)器。（不過，這意味著每條 insert 都包含不同的值，都需要準(zhǔn)備，因而其性能實(shí)際上要比使用參數(shù)標(biāo)記情況下的性能更差一些，前面“語句準(zhǔn)備”一節(jié)已對(duì)此作了討論。）將多條語句組合成一條語句可以通過 Compound SQL 來實(shí)現(xiàn)：
下面是關(guān)于該話題的其他一些建議：
DB2 使用三種算法中的一種來確定將行插入到哪里。（如果使用了多維群集（Multi-dimensional Clustering，MDC），則另當(dāng)別論，我們?cè)谶@里不予討論。）要了解關(guān)于插入算法方面的細(xì)節(jié)，請(qǐng)參閱 DB2 V8 Administration Guide: Performance中提到的相關(guān)內(nèi)容。
缺省模式是，DB2 搜索散布在表的各頁上的自由空間控制記錄（Free Space Control Records，F(xiàn)SCR），以找到有足夠自由空間存放新行的頁。顯然，如果每頁上的自由空間都比較少的話，就要浪費(fèi)很多的搜索時(shí)間。為了應(yīng)付這一點(diǎn)，DB2 提供了 DB2MAXFSCRSEARCH 注冊(cè)表變量，以便允許將搜索范圍限制為少于缺省的 5 頁。
當(dāng)表是通過 ALTER TABLE 以 APPEND 模式放置時(shí)，就要使用第二種算法。這樣就完全避免了 FSCR 搜索，因?yàn)橹恍韬?jiǎn)單地將行直接放到表的末尾。
當(dāng)表有群集索引（clustering index）時(shí)，就要用到最后一種算法。在這種情況下，DB2 試圖將每一行插入到有相似鍵值的一頁中。如果那一頁沒有空間了，DB2 就會(huì)嘗試附近的頁，如果附近的頁也沒有空間，DB2 就進(jìn)行 FSCR 搜索。
如果只考慮插入時(shí)間的優(yōu)化，那么使用 APPEND 模式對(duì)于批量插入是最快的一種方法，但是這種方法的效果遠(yuǎn)不如我們這里討論的很多其他方法那么成效顯著。請(qǐng)參考 test 22 和 test 23。第二好的方法應(yīng)該是采用缺省算法，但是，如果在最佳環(huán)境中，更改 DB2MAXFSCRSEARCH 的值影響很小，而在一個(gè) I/O 約束較少的環(huán)境中，這種更改所造成的影響就比較可觀了。
如果有群集索引，則對(duì) insert 的性能會(huì)有很大的負(fù)面影響（test 32 和 38 表明幾乎有 20% 的開銷），這一點(diǎn)也不驚奇，因?yàn)槭褂萌杭饕哪康木褪峭ㄟ^在插入時(shí)做額外的工作來提高查詢（即 select）性能的。如果的確需要群集索引，那么可以通過確保有足夠的自由空間來使其對(duì)插入的影響降至最小：使用 ALTER TABLE 增加 PCTFREE，然后使用 REORG 預(yù)留自由空間。不過，如果允許太多自由空間的存在，則可能導(dǎo)致查詢時(shí)需要讀取額外的頁，這反而大大違反了使用群集索引的本意。另一種選擇是，在批量插入之前先刪除群集索引，而后再重新創(chuàng)建群集索引，也許這是最優(yōu)的方法（創(chuàng)建群集索引的開銷跟創(chuàng)建常規(guī)索引的開銷差不多，都不是很大，只是在插入時(shí)有額外的開銷）。
每一條 insert 在執(zhí)行時(shí)，都是先將新行存儲(chǔ)在一個(gè)頁中，并最終將那個(gè)頁寫到磁盤上。一旦像前面討論的那樣指定了頁，那么在將行添加到該頁之前，該頁必須已經(jīng)在緩沖池中。對(duì)于批量插入，大部分頁都是最新指派給表的，因此讓我們關(guān)注一下對(duì)新頁的處理。
如果表在系統(tǒng)管理存儲(chǔ)的（System Managed Storage，SMS）表空間中，當(dāng)需要新頁時(shí)，缺省情況下是從文件系統(tǒng)中分別為每一頁分配空間。但是，如果對(duì)數(shù)據(jù)庫運(yùn)行了 db2empfa 命令，那么每個(gè) SMS 表空間就會(huì)為新頁一次性分配一個(gè)區(qū)段。test 11 和 test 82 表明，與區(qū)段大小為缺省的 32 頁的情況相比，運(yùn)行 db2empfa 命令可以使對(duì) SMS 表空間的插入快到兩倍，因?yàn)樵?test 82 中對(duì)于一個(gè)頁有 32 次分配，而 test 11 中是 32 個(gè)頁一次分配。test 11 和 test 83-85 表明，如果區(qū)段大小小于 32 頁，則性能會(huì)逐步下降，因?yàn)橐M(jìn)行額外的分配，但是讓區(qū)段大于 32 這樣的建議也未必有幫助。我們建議運(yùn)行 db2empfa 命令，并使用 32 頁的區(qū)段。
對(duì)于數(shù)據(jù)庫管理的存儲(chǔ)（Database Managed Storage，DMS）表空間，空間是在創(chuàng)建表空間時(shí)就預(yù)先分配的，但是頁的區(qū)段則是在插入處理過程中指派給表的。與 SMS 相比，DMS 對(duì)空間的預(yù)分配可以提高大約 20% 的性能 -- 請(qǐng)參考 test 11 和 test 81。test 81 使用 DMS 文件表空間，而如果使用了 DMS raw 表空間的話，還可以多得到一點(diǎn)好處。使用 DMS 時(shí)，更改區(qū)段大小并沒有明顯的效果。
如果表上有索引，則對(duì)于每個(gè)插入的行，都要添加一個(gè)條目到每條索引。這要求在緩沖池中存在適當(dāng)?shù)乃饕?。晚些時(shí)候我們將討論索引的維護(hù)，但是現(xiàn)在只需記住，插入時(shí)對(duì)緩沖池和 I/O 的考慮也類似地適用于索引頁，對(duì)于數(shù)據(jù)頁也是一樣。
隨著插入的進(jìn)行，越來越多的頁中將填入被插入的行，但是，DB2 不要求在 insert 或 Commit 后將任何新插入的或更新后的數(shù)據(jù)或索引寫入到磁盤。（這是由于 DB2 的 writeahead 日志記錄算法。但是有一個(gè)例外，這將在關(guān)于日志記錄的小節(jié)中論述到。）然而，這些頁需要在某一時(shí)刻寫到磁盤上，這個(gè)時(shí)刻可能會(huì)在數(shù)據(jù)庫關(guān)閉時(shí)才會(huì)輪到。
一般來說，對(duì)于批量插入，您會(huì)希望積極地進(jìn)行 異步頁清除（asynchronous page cleaning），這樣在緩沖池中就總有可用于新頁的空余位置。頁清除率，或者說總?cè)表撀剩赡軐?dǎo)致計(jì)時(shí)上的很大不同，使得性能比較容易產(chǎn)生誤解。例如，如果使用 100,000 頁的緩沖池，并且不存在頁清除，則批量插入在結(jié)束前不會(huì)有任何新的或更改過的（“臟的”）頁寫到磁盤上，但是隨后的操作（例如選擇，甚至乎關(guān)閉數(shù)據(jù)庫）都將被大大推遲，因?yàn)檫@時(shí)有至多 100,000 個(gè)在插入時(shí)產(chǎn)生的臟頁要寫到磁盤上。另一方面，如果在同一情況下進(jìn)行了積極的頁清除，則批量插入過程可能要花更長(zhǎng)的時(shí)間，但是此后緩沖池中的臟頁要少一些，從而使得隨后的任務(wù)執(zhí)行起來性能更佳。至于那些結(jié)果中到底哪個(gè)要更好些，我們并不是總能分得清，但是通常來說，將所有臟頁都存儲(chǔ)在緩沖池中是不可能的，所以為了取得最佳性能，采取有效的頁清除是有必要的。
為了盡可能好地進(jìn)行頁清除：
至于 I/O 本身，當(dāng)需要建立索引時(shí)，可以通過使用盡可能大的緩沖池來將 I/O 活動(dòng)減至最少。（請(qǐng)參閱后面的“ 索引維護(hù)”一節(jié)。）如果不存在索引，則使用較大的緩沖池幫助不大，而只是推遲了 I/O。也就是說，它允許所有新頁暫時(shí)安放在緩沖池中，但是最終仍需要將這些頁寫到磁盤上。
當(dāng)發(fā)生將頁寫到磁盤的 I/O 時(shí)，通過一些常規(guī)的 I/O 調(diào)優(yōu)步驟可以加快這一過程，例如：
缺省情況下，每一個(gè)插入的行之上都有一個(gè) X 鎖，這個(gè)鎖是在該行創(chuàng)建時(shí)就開始有的，一直到 insert 被提交。有兩個(gè)跟 insert 和鎖相關(guān)的性能問題：
對(duì)于經(jīng)過良好優(yōu)化的批量插入，由獲得每一行之上的一個(gè) X 鎖以及后來釋放該鎖引起的 CPU 開銷是比較可觀的。對(duì)于每個(gè)新行之上的鎖，惟一可以替代的是表鎖（DB2 中沒有頁鎖）。test 11 和 test 101 表明，當(dāng)使用表鎖時(shí)，耗時(shí)減少了 3%。有 3 種情況可以導(dǎo)致表鎖的使用，在討論表鎖的缺點(diǎn)之前，我們先用一點(diǎn)時(shí)間看看這 3 種情況：
當(dāng)然，所有這些的缺點(diǎn)就在于并發(fā)的影響：如果表上有一個(gè) X 鎖，那么其他應(yīng)用程序除非使用了隔離級(jí)別 UR（未提交的讀），否則都不能訪問該表。如果知道獨(dú)占訪問不會(huì)導(dǎo)致問題，那么就應(yīng)該盡量使用表鎖。但是，即使您堅(jiān)持使用行鎖，也應(yīng)記住，在批量插入期間，表中可能存在數(shù)千個(gè)有 X 鎖的新行，所以就可能與其他使用該表的應(yīng)用程序產(chǎn)生沖突。通過一些方法可以將這些沖突減至最少：
注意
缺省情況下，每條 insert 都會(huì)被記錄下來，以用于恢復(fù)。日志記錄首先被寫到內(nèi)存中的日志緩沖池，然后再寫到日志文件，通常是在日志緩沖池已滿或者發(fā)生了一次提交時(shí)寫到日志文件的。對(duì)批量插入的日志記錄的優(yōu)化實(shí)際上就是最小化日志記錄寫的次數(shù)，以及使寫的速度盡可能快。
這里首先考慮的是日志緩沖池的大小，這由數(shù)據(jù)庫配置參數(shù) LOGBUFSZ 來控制。該參數(shù)缺省值為 8 頁或 32 K，這與大多數(shù)批量插入所需的理想日志緩沖池大小相比要小些。舉個(gè)例子，對(duì)于一個(gè)批量插入，假設(shè)對(duì)于每一行的日志內(nèi)容有 200 字節(jié)，則在插入了 160 行之后，日志緩沖池就將被填滿。如果要插入 1000 行，因?yàn)槿罩揪彌_池將被填滿幾次，再加上提交，所以大概有 6 次日志寫。如果將 LOGBUFSZ 的值增加到 64 頁（256K）或者更大，緩沖池就不會(huì)被填滿，這樣的話對(duì)于該批量插入就只有一次日志寫（在提交時(shí)）。test 104 和 test 105 表明，通過使用更大的 LOGBUFSZ 可以獲得大約 13% 的性能提升。較大日志緩沖池的不利之處是，緊急事故恢復(fù)所花的時(shí)間可能要稍微長(zhǎng)一點(diǎn)。
減少日志寫的另一種可能性是對(duì)新行要插入到的那個(gè)表使用“ALTER TABLE <name> ACTIVATE NOT LOGGED INITIALLY”(NLI)。如果這樣做了，那么在該工作單元內(nèi)不會(huì)記錄任何 insert 操作，但是這里存在兩個(gè)與 NLI 有關(guān)的重要問題：
至于提高日志寫的速度，有下面一些可能性：
提交迫使將日志記錄寫到磁盤上，以保證提交的插入肯定會(huì)存在于數(shù)據(jù)庫中，并且釋放新行上的鎖。這些都是有價(jià)值的活動(dòng)，但是因?yàn)?Commit 總是要牽涉到同步 I/O（對(duì)于日志），而 insert 則不會(huì)，所以 Commit 的開銷很容易高于 insert 的開銷。因此，在進(jìn)行批量插入時(shí)，每一行都提交一次的做法對(duì)于性能來說是很糟糕的，所以應(yīng)確保不使用自動(dòng)提交（對(duì)于 CLI 和 CLP 來說缺省情況正是如此）。建議大約每 1000 行提交一次：test 61-78 表明，當(dāng)每 1000 行而不是一兩行提交一次時(shí)，性能可以提高大概 10 倍。不過，一次提交多于 1000 行只能節(jié)省少量的時(shí)間，但是一旦出現(xiàn)失敗，恢復(fù)起來所花的時(shí)間要更多。
對(duì)上述方法的一種修正：如果 MINCOMMIT 數(shù)據(jù)庫配置參數(shù)的值大于 1 （缺省值），則 DB2 就不必對(duì)每次 commit 都進(jìn)行一次同步 I/O，而是等待，并試圖與一組事件一起共享日志 I/O。對(duì)于某些環(huán)境來講，這樣做是有好處，但是對(duì)于批量插入常常沒有作用，甚至有負(fù)作用，因此，如果要執(zhí)行的關(guān)鍵任務(wù)是批量插入，就應(yīng)該讓 MINCOMMIT 的值保持為 1。
|
|
對(duì)于一次 insert，有幾種類型的處理將自動(dòng)發(fā)生。如果您的主要目標(biāo)只是減少插入時(shí)間，那么最簡(jiǎn)單的方法是避免所有這些處理的開銷，但是如果從總體上考慮的話，這樣做未必值得。讓我們依次進(jìn)行討論。
對(duì)于插入的每一行，必須添加一個(gè)條目到表上的每個(gè)索引中（包括任何主鍵索引）。這一過程主要有兩方面的代價(jià)：
更壞的場(chǎng)景是，在索引維護(hù)期間有大量的隨機(jī) I/O。假設(shè)要插入 10,000 行，在索引的緩沖池中有 5000 頁，并且要插入的各行的鍵值隨機(jī)分布在整個(gè)鍵范圍內(nèi)。那么，有 10,000 個(gè)這么多的葉子頁（可能還有些非葉子頁）需要進(jìn)入緩沖池，以便對(duì)它們進(jìn)行搜索和/或更新，對(duì)于一個(gè)給定的葉子頁，它預(yù)先已經(jīng)在緩沖池中的概率只有 10%。對(duì)于每次的 insert，需要讀磁盤的概率如此之高，使得這種場(chǎng)景往往性能很差。
對(duì)于逐行插入，將新行添加到已有的索引中比起創(chuàng)建一個(gè)新索引來代價(jià)要高得多。如果是插入到一個(gè)空表，應(yīng)該總是在進(jìn)行了列插入之后創(chuàng)建索引。（注意，如果使用了 load，則應(yīng)該 預(yù)先創(chuàng)建索引。）如果要插入到一個(gè)已經(jīng)填充過的表，那么在列插入之前刪除索引，并在列插入之后重新創(chuàng)建索引，這種方法可能是最快的，但是只有在要插入相當(dāng)多的行 -- 大概大于表的 10-20% 的時(shí)候，才能這么說。如果為索引表空間使用較大的緩沖池，并且盡可能地將不同 insert 排序，以便鍵值是排好序的，而不是隨機(jī)的，就可以幫助加快索引維護(hù)。
附錄 A 中的 test 31-37給出的結(jié)果表明，insert 的耗時(shí)是如何隨著索引的數(shù)目以及創(chuàng)建索引的時(shí)機(jī)而變化的?？偠灾c沒有索引相比，有 4 條索引的情況下 insert 可能要多耗費(fèi)數(shù)倍的時(shí)間，而如果在 insert 之后才創(chuàng)建索引，就可以將總耗時(shí)（insert 耗時(shí)加上創(chuàng)建索引的耗時(shí)）縮短 1/4 到幾乎 1/2。
如果關(guān)鍵目標(biāo)是將 insert 的性能最優(yōu)化，那么增加索引的 PCTFREE 時(shí)，就可能減少在隨機(jī)插入索引條目時(shí)出現(xiàn)頁拆分（page split）的次數(shù)。所以這樣做時(shí)要小心，不過，太多的自由空間意味著大量的索引頁，這對(duì)查詢的性能乃至 insert 處理本身都會(huì)產(chǎn)生負(fù)面影響。
這一類的開銷包括檢查約束驗(yàn)證和外鍵約束（參照完整性（RI））驗(yàn)證。檢查約束開銷很低（請(qǐng)參閱附錄 A 中的 test 11-13），這很大程度上是因?yàn)椴恍枰獙?duì)每一行使用 I/O（因?yàn)橐獧z查的值是在行內(nèi)，只是要進(jìn)行一些計(jì)算而已）。
如果有外鍵，則插入一行就是另外一回事了。對(duì)于每個(gè)外鍵，都必須在父表中進(jìn)行一次查找，以確保有父行存在。雖然這種查找是通過主鍵索引進(jìn)行的，但是這仍然要占用 CPU 循環(huán)來進(jìn)行搜索，而且可能還要占用 I/O 將索引頁讀入緩沖池。test 11、14 和 15 表明，當(dāng)有兩個(gè)外鍵時(shí)，insert 的耗時(shí)要翻一番。
在填充表之后使用 CREATE INDEX 比起通過一條一條的 INSERT 語句建立索引來代價(jià)要小些，同樣，使用 ALTER TABLE 創(chuàng)建外鍵（即進(jìn)行驗(yàn)證）作為批量操作，比起在每次 insert 期間的驗(yàn)證所增加的代價(jià)的總和來，要小一些。比較 test 14 和 test 16、test 15 和 test 17，我們可以看到，在 insert 之后創(chuàng)建外鍵可以將總耗時(shí)減少大約 40%。
如果可能的話，在大量插入行到一個(gè)表之前，應(yīng)該先使用 ALTER TABLE 刪除表上的所有約束，在插入之后再重新創(chuàng)建這些約束（仍是使用 ALTER TABLE），只有在插入的行不到該表中所有行的 10-20% 時(shí)才可不必這樣做。
有些應(yīng)用程序本身也會(huì)做一些檢查，以確保表之間的關(guān)系是有效的。也就是說，在插入一個(gè)子行之前，應(yīng)用程序會(huì)讀一個(gè)父行，以確保父行存在。如果這種檢查得以正確執(zhí)行，那么在數(shù)據(jù)庫中定義外鍵約束將增加額外的開銷。但是，至少有三個(gè)原因可以說明為什么在數(shù)據(jù)庫中定義這些約束要更好些：
如果應(yīng)用程序本身的檢查不能少，那么最好的替代方案是在 DB2 中定義外鍵，但是在 CREATE TABLE 或 ALTER TABLE 中帶上 NOT ENFORCED 子句，這樣就避免了檢查開銷，而優(yōu)化器又能使用關(guān)系知識(shí)。
如果在一個(gè)表上定義了一個(gè)或多個(gè)進(jìn)行 Insert 操作的觸發(fā)器，那么每次的 insert 都將引起觸發(fā)器定義中的動(dòng)作的執(zhí)行。由于那些被觸發(fā)的動(dòng)作通常是一條或多條 INSERT、UPDATE 或 DELETE 語句，因此，在數(shù)據(jù)量很大的 insert 中，觸發(fā)器的開銷會(huì)很大。附錄 A 中的 test 18-21表明，增加觸發(fā)器會(huì)導(dǎo)致 insert 的性能減慢數(shù)倍。索引和約束可以臨時(shí)刪除，但是應(yīng)用程序知識(shí)卻必須知道何時(shí)避免觸發(fā)器的執(zhí)行是可接受的。也就是說，知道何時(shí)不會(huì)導(dǎo)致數(shù)據(jù)完整性問題。如果避免觸發(fā)器的執(zhí)行是可接受的，您可以通過 參考資料中列出的文章里所描述的技術(shù)來臨時(shí)禁用觸發(fā)器。
這兩種方法可以讓 DB2 自動(dòng)生成整型列值，這通常是在 insert 期間進(jìn)行的。應(yīng)該清楚的主要性能問題是，由于可恢復(fù)性的原因，生成的值必須做日志記錄。為了減少日志記錄的開銷，可以將這些值預(yù)存（緩存）起來，每當(dāng)緩存用完時(shí)，才寫一條日志記錄。缺省情況下是緩存 20 個(gè)值。
附錄 A 中 test 41-51的結(jié)果表明，如果沒有緩沖的話，耗時(shí)會(huì)非常大（幾乎要比缺省情況慢 9 倍），而如果使用比缺省情況更大的緩存，則可以縮減大半的時(shí)間，并且將使用 Identity（標(biāo)識(shí)）或 Sequence（序列） 的時(shí)間減至不到原先的 20%。如果您要在使用 Identity 還是 Sequence 之間作選擇的話，那么我告訴您使用 Identity 要好出幾個(gè)百分點(diǎn)。
當(dāng)插入行到一個(gè)表，并且該表用“generated as”子句定義了一個(gè)或多個(gè)列，例如： CREATE TABLE t1 (c1 CHAR(5), c2 CHAR(5) generated always as (UPPER(c1))) ，這時(shí)，為建立生成的值而進(jìn)行的函數(shù)調(diào)用將導(dǎo)致附加的開銷。然而，這種開銷非常小，不至于影響您使用這種功能的決心。
“refresh immediate”物化查詢表（MQT）的重新生成
MQT 可用于通過預(yù)先計(jì)算聚合值來增強(qiáng)查詢性能。例如：
create table staffsum as
( select count(salary) as salcount, sum(salary) as salsum, dept from staff group by dept )
data initially deferred refresh immediate
如果 MQT 被定義為“refresh immediate”，則在每次 insert 時(shí)將重新計(jì)算 MQT 中的聚合，因此通常來講，對(duì)帶有 refresh immediate MQT 的表執(zhí)行列插入不大可取。不過，DB2 會(huì)盡其所能優(yōu)化重新計(jì)算，例如掃描總結(jié)表，而不是整個(gè)基本表。我們建議對(duì)于涉及 MQT 的 insert 運(yùn)行 Explain，以便清楚幕后情況。
|
|
當(dāng)插入一行到一個(gè)分區(qū)表（使用 DB2 V8 的 Data Partitioning Feature (DPF)）時(shí)，首先要散列（hash）該行的分區(qū)鍵值以確定必須將該行插入到哪個(gè)分區(qū)，然后 DB2 將該行發(fā)送到那個(gè)分區(qū)。這種一次一行的處理方式比較慢，前面我們已看到，在 CLI 中，這種方式與數(shù)組插入方式比起來的確很慢，而且因?yàn)檫€需要將每一行從協(xié)調(diào)分區(qū)發(fā)送到目標(biāo)分區(qū)，使得情況更加糟糕。
為了把開銷降至最低，可以使用緩沖插入（buffered insert）。如果使用了這種方法，定向于某個(gè)給定分區(qū)的散列行首先會(huì)進(jìn)入一個(gè)緩沖區(qū)中，然后成組地發(fā)送到那個(gè)分區(qū)，而不是一次發(fā)送一行。您可以通過 Prep 或 Bind 命令的“INSERT BUF”選項(xiàng)來引起緩沖插入。要了解關(guān)于緩沖插入的細(xì)節(jié)，請(qǐng)參閱 Application Development Guide 的參考資料：Programming Client Applications。在開發(fā)者園地也有關(guān)于基于 Java（只包括 SQLJ）的緩沖插入的文章，請(qǐng)參閱后面的“參考資料”。
在 DPF 環(huán)境中，如果對(duì)于重復(fù)的批量插入要求絕對(duì)最大的性能，那么您可能需要研究?jī)蓚€(gè)相關(guān)的 API。第一個(gè) API 是 sqlugtpi，它讓應(yīng)用程序可以獲得一個(gè)表的分區(qū)信息。之后就可以使用這種信息，再結(jié)合 sqlugrpn API 來找到一行所屬的分區(qū)號(hào)。您可以使用這些 API 將屬于某個(gè)給定分區(qū)的所有數(shù)據(jù)組到一起，然后連接到那個(gè)分區(qū)，這樣就不需要在分區(qū)之間傳輸數(shù)據(jù)，對(duì)于每個(gè)分區(qū)都重復(fù)這么做。這種方法可以取得非常快的性能，但是要花一定的精力來確保這種方法在有多個(gè)數(shù)據(jù)類型、代碼頁等等的情況下也能十分有效。
DPF 插入可能引起的另一個(gè)問題是，要插入到的那個(gè)表（子表）上可能有外鍵約束。假設(shè)父表和子表有不同的分區(qū)鍵。那么每個(gè)子行的父親一般會(huì)在一個(gè)不同的分區(qū)上，因此，對(duì)于大多數(shù)插入的行，對(duì)其父親的驗(yàn)證檢查就需要從子分區(qū)跨越到父分區(qū)。對(duì)此的解決辦法是，讓父表和子表的分區(qū)鍵相同（這對(duì)于查詢性能來說不是最好的選擇），或者，如果使用了多個(gè)邏輯分區(qū)，就可以將 DB2 注冊(cè)表變量設(shè)置為 YES。
通過使用 staging 表可以為某些場(chǎng)景下的 insert 提高性能。通常的用法是，不是批量插入行到一個(gè)表中，而是使用 LOAD 命令將行裝載到一個(gè) staging 表中；然后，就可以使用 INSERT ... SELECT 將行插入到主表。不管 LOAD 還是 INSERT ... SELECT，都比常規(guī)的插入要快得多，即使將這兩步加起來也常常要比常規(guī)插入快些。不過，單單就性能而言，在 V8 中還是使用 load 直接將表裝載到主表要快些，因?yàn)樵?V8 中的 load 不像 V7 中那樣有并發(fā)限制。
除了數(shù)據(jù)的消息傳遞以外，致使您在 V8 中仍想使用 staging 表的主要原因是，這樣可以將批量插入拆散成能夠并行運(yùn)行的更小的塊。在一個(gè)有多個(gè)處理器的系統(tǒng)上，每條插入將在一個(gè) DB2 代理中運(yùn)行，并且不會(huì)占用多于一個(gè)的處理器，即使將 DBMINTRA_PARALLEL 參數(shù)被設(shè)為 ON 也是如此。例如，如果要在一臺(tái) 8-way 的機(jī)器上插入 1M 的行，一般的插入過程通常對(duì) CPU 的利用不會(huì)多于 12% （100 / 8）。（另一方面，load 則會(huì)自動(dòng)使用百分比大得多的 CPU，這也是它比 insert 更可取的另一個(gè)原因。）相反，您可以將 1M 的行裝載到一個(gè) staging 表中，然后運(yùn)行 8 條并發(fā)的 insert ... Select 語句，8 條 Select 語句中都有謂詞，每條語句從 staging 表中檢索大約 1/8 的惟一的行子集。
最后，您可以通過一個(gè)多線程應(yīng)用程序運(yùn)行并發(fā)的插入，其中每個(gè)線程做它自己的插入。
對(duì)于一條 INSERT ... SELECT，只要能使選擇更快，就可以減少整條語句的耗時(shí)，但是這超出了本文的范圍，不適合進(jìn)行詳細(xì)的討論。下面列出了一些可能性。注意，這些只適用于選擇部分。而同樣的這些因素對(duì)于插入部分一般沒什么幫助。
這些類型的列是惟一的，它們不會(huì)緩存在緩沖池中。因此，任何包括一個(gè)或多個(gè)這種列的 insert 都會(huì)使得這些列被直接寫到磁盤上，用 DB2 術(shù)語來說就是“直接寫（direct write”）。您可以想像，這會(huì)使 LOB/LONG 的 insert 比“一般” 的 insert 要慢得多： test 91中使用了一個(gè) CLOB 列，這種情況比基線測(cè)試（test 11，有一個(gè) CHAR 列）要慢 9 倍以上。有這樣一些優(yōu)化的可能性：
對(duì)于沒有約束的簡(jiǎn)單插入，將優(yōu)化級(jí)別從缺省值（5）改為 1 的測(cè)試雖然將優(yōu)化器的算法變得更廉價(jià)，但是并沒有對(duì)性能產(chǎn)生很大的變化。如果要經(jīng)常準(zhǔn)備插入語句，插入牽涉到約束，或者有選擇部分，那么使用較低的優(yōu)化級(jí)別可能會(huì)有好處。相反，如果插入部分很小，而選擇部分比較復(fù)雜，那么將優(yōu)化級(jí)別從 5 增至更大將帶來好處。
利用源表（source table）插入/更新目標(biāo)表（MERGE 語句）
一個(gè)相當(dāng)常見的數(shù)據(jù)庫任務(wù)就是利用一個(gè)源表更新一個(gè)目標(biāo)表。舉個(gè)特定的例子，比如取源表中的每一行，如果該行不在目標(biāo)表中，那么就將該行插入到目標(biāo)表，否則就更新目標(biāo)行。在 V8 中可以使用 MERGE 語句獨(dú)立完成上述任務(wù)，而不必多次執(zhí)行不同的語句，從而性能也就更好一些。
|
|
當(dāng)您試圖監(jiān)視和調(diào)優(yōu) insert 時(shí)，基本任務(wù)跟大多數(shù)其他的性能分析沒什么不同：找出瓶頸所在，然后直接處理瓶頸。欲確定瓶頸，首先就是利用操作系統(tǒng)工具查看 CPU、I/O、內(nèi)存和網(wǎng)絡(luò)消耗。這樣應(yīng)該就可以讓您排除某些方面，而將注意力放在一兩個(gè)方面。對(duì)操作系統(tǒng)實(shí)用程序的深入討論超出了本文的范圍。
盡量不要被非必要的問題轉(zhuǎn)移了視線。例如，如果 CPU 利用率是 100%，那么這時(shí)減少 I/O 很可能無法提高性能，而當(dāng)以后 CPU 瓶頸已經(jīng)解除時(shí)，這樣的更改本來可能非常有用，但是您可能會(huì)因?yàn)樯弦淮蔚氖《辉僮鬟@樣的更改了。
應(yīng)該盡量讓應(yīng)用程序在操作期間的不同時(shí)刻報(bào)告插入的速率。例如，如果知道在運(yùn)行后的第 10 分鐘與第 5 分鐘時(shí)各自的每秒插入次數(shù)是否相同，是很有用的。通常，當(dāng) insert 開始的時(shí)候，有一小段較慢的啟動(dòng)時(shí)間，然后當(dāng)緩沖池填充了內(nèi)容并且沒有數(shù)據(jù)頁的 I/O 時(shí)，就有一段速度比較快的時(shí)期。接著，當(dāng)開始將數(shù)據(jù)頁往外寫的時(shí)候，速率又會(huì)慢下來，如果頁清除或者 I/O 子系統(tǒng)不是最優(yōu)的，則更是如此。
對(duì)于非常大的批量插入，通常在某一時(shí)刻插入的速率會(huì)趨于平穩(wěn)。如果不是這樣，那么通常是因?yàn)橐诓迦肫陂g創(chuàng)建索引，使得需要?jiǎng)?chuàng)建越來越多的索引頁，并且可能還要進(jìn)行隨機(jī)的 I/O 操作，讀取已有的索引頁以便更新它們。如果的確是上述情況，那么使用更大的緩沖池是最好的解決辦法，但是為索引頁增加更多的自由空間也有所幫助。
現(xiàn)在讓我們看看可以幫助您監(jiān)視和調(diào)優(yōu) insert 的關(guān)鍵 DB2 實(shí)用程序：Snapshots（快照監(jiān)視）、Event Monitoring（事件監(jiān)視）和 Explain。要了解關(guān)于快照監(jiān)視和事件監(jiān)視的更多信息，請(qǐng)參閱 System Monitor Guide and Reference；至于 Explain，請(qǐng)參閱 Administration Guide: Performance。
快照監(jiān)視可以提供大量信息片斷來描述在插入的處理期間發(fā)生了什么事情。您可以使用以下步驟獲得所有可以得到的信息（或者也可以選擇獲得信息的子集）：
大多數(shù)與 insert 相關(guān)的信息都可以在數(shù)據(jù)庫快照中找到，并且大部分的這些信息也都會(huì)在適當(dāng)?shù)木彌_池、應(yīng)用程序和表空間快照中以更大的粒度提供。
以下是數(shù)據(jù)庫快照中最相關(guān)的幾行：
Buffer pool data writes = 500 Asynchronous pool data page writes = 500 Buffer pool index writes = 0 Asynchronous pool index page writes = 0 Total buffer pool write time (ms) = 25000 Total elapsed asynchronous write time = 25000 LSN Gap cleaner triggers = 21 Dirty page steal cleaner triggers = 0 Dirty page threshold cleaner triggers = 0 Update/Insert/Delete statements executed = 100000 Rows inserted = 100000 |
請(qǐng)注意連續(xù)快照中的“Rows inserted”，看看在批量插入期間插入的速率是否有變化。大多數(shù)其他的值都反映了 I/O 量和頁清除的效力。至于后者，理想情況下您可以看到，所有數(shù)據(jù)寫都是同步的，而所有緩沖池寫時(shí)間都是異步的（就像上面輸出的那樣）；如果不是這樣，嘗試降低 CHNGPGS_THRESH 和/或增加 NUM_IOCLEANERS。
通常在動(dòng)態(tài) SQL 快照中可以找到關(guān)于 insert 的附加信息?？纯纯偤臅r(shí)，并將其與用戶和系統(tǒng) CPU 時(shí)間相比較，這樣做是十分有用的。耗時(shí)與 CPU 之間的差值大部分在于 I/O 部分，所以，哪個(gè)地方占去了大部分的時(shí)間以及哪個(gè)地方需要調(diào)優(yōu)也就很清楚了。
以下是一個(gè) 100,000 行的 CLI 數(shù)組插入的動(dòng)態(tài) SQL 快照條目的一個(gè)子集。注意，盡管應(yīng)用程序只發(fā)送那個(gè)數(shù)量的 1/10 那么多的數(shù)組，“Number of executions”仍是對(duì)于每一行都有一個(gè)。
Number of executions = 100000 Number of compilations = 1 Rows written = 100000 Buffer pool data logical reads = 102120 Total execution time (sec.ms) = 13.830543 Total user cpu time (sec.ms) = 10.290000 Total system cpu time (sec.ms) = 0.130000 Statement text = INSERT into test1 values(?, ?, ?, ?, ?) |
快照輸出中的其他信息：
當(dāng)事件在服務(wù)器上發(fā)生時(shí)，通過 DB2 事件監(jiān)視器可以獲得關(guān)于事件的性能信息。為了分析 insert 的性能，需要為語句創(chuàng)建一個(gè)事件監(jiān)視器，并在 insert 執(zhí)行期間激活該事件監(jiān)視器。雖然有點(diǎn)過分，但是事件監(jiān)視器的輸出會(huì)顯示每條 insert 語句的耗時(shí)。對(duì)于 OLTP 型的應(yīng)用程序，對(duì)語句運(yùn)行事件監(jiān)視的開銷相當(dāng)高，其輸出也十分冗長(zhǎng)，所以應(yīng)注意不要讓事件監(jiān)視運(yùn)行太長(zhǎng)的時(shí)間。即使是幾秒鐘也會(huì)產(chǎn)生數(shù)兆的輸出。您可以將監(jiān)視器信息寫入到一個(gè)表中，以便于對(duì)結(jié)果的分析，例如性能趨勢(shì)。
下面是一個(gè)濃縮的示例語句事件，在一系列的 10,000 個(gè) CLI 數(shù)組插入中每 10 行對(duì)應(yīng)一次這樣的語句事件。每個(gè)數(shù)組只有一個(gè)事件，在這里就是每 10 行有一個(gè)事件。
17) Statement Event ... Appl Handle: 9 Appl Id: *LOCAL.wilkins.0953D9033443 ------------------------------------------- Type : Dynamic Operation: Execute Section : 4 Creator : NULLID Package : SYSSH200 Text : INSERT into test1 values(?, ?) ------------------------------------------- Start Time: 01-28-2004 22:34:43.918444 Stop Time: 01-28-2004 22:34:43.919763 Exec Time: 0.001319 seconds Number of Agents created: 1 User CPU: 0.000000 seconds System CPU: 0.000000 seconds Fetch Count: 0 Rows read: 0 Rows written: 10 Internal rows deleted: 0 Internal rows updated: 0 Internal rows inserted: 0 Bufferpool data logical reads: 10 SQLCA: sqlcode: 0 sqlstate: 00000 |
如果 insert 的性能不像預(yù)期的那么好，則有可能是因?yàn)橛?#8220;隱藏”的處理發(fā)生。前面已經(jīng)討論過，這種處理可能以不同的形式出現(xiàn)，例如索引維護(hù)、約束驗(yàn)證或者觸發(fā)器執(zhí)行。對(duì) insert 運(yùn)行某種形式的 Explain （例如 Visual Explain，或者 Explain 語句加上 db2exfmt），就可以揭示大多數(shù)額外的處理（除了索引維護(hù)）。如果額外的處理可能是性能問題的起因，那么可以消除這種額外的處理。
作為一個(gè)簡(jiǎn)單的例子，下面的圖（由 db2exfmt 產(chǎn)生）展示了“Insert into test1 values (?, ?, ...)”語句的訪問計(jì)劃。您可以猜出這里的額外處理是什么嗎？回答就在下面。
Rows RETURN ( 1) Cost I/O | 0.333333 TBSCAN ( 2) 28.2956 1 /----+---\ 0.04 1 FILTER TABFNC: SYSIBM ( 3) GENROW 28.2266 1 | 1 NLJOIN ( 4) 28.1268 1 /---------+--------\ 1 1 INSERT IXSCAN ( 5) ( 7) 25.5248 2.60196 1 0 /---+--\\ | 1 116 120 TBSCAN TABLE: WILKINS INDEX: SYSIBM ( 6) test1 SQL0401290925513 0.0048 0 | 1 TABFNC: SYSIBM GENROW |
在我給出答案之前：Explain 對(duì)于 INSERT ... SELECT 語句也十分有用。insert 本身是非?？斓?，但是 SELECT 可能存在一個(gè)訪問計(jì)劃問題，這個(gè)問題會(huì)拖慢整個(gè)語句。通過 Explain 就可以揭示這一切。
答案：
上述訪問計(jì)劃是針對(duì)將行插入到擁有外鍵關(guān)系的子表的 insert 的。其中有一個(gè) insert（步驟 5）與索引掃描（步驟 7）之間的嵌套循環(huán)連接（NLJOIN，步驟 4 ）。索引掃描實(shí)際上是對(duì)父表進(jìn)行主鍵查找，并完成外鍵約束驗(yàn)證。在這種情況下，來自額外處理的開銷相對(duì)來說就比較小：索引掃描的成本估計(jì)只有 2.60196 timerons（timerons 是（主要）結(jié)合了 CPU 和 I/O 代價(jià)的成本單位），而 insert 本身的成本是 25.5248 timerons。例如，如果有一個(gè)觸發(fā)器的話，那么就會(huì)在訪問計(jì)劃中的一個(gè)或多個(gè) insert、Update 或 Delete 條目中反映出來。
順便提一下，當(dāng)您在訪問計(jì)劃中看到“GENROW”時(shí)，其實(shí)就是一個(gè)“generate row”步驟。這代表用于后續(xù)步驟的臨時(shí)行的創(chuàng)建，這里不需要擔(dān)心。
|
|
在本文中，我們給出了多種提高 insert 性能的方法。請(qǐng)參閱 附錄 B以了解這些方法的完整清單。下面是最重要的一部分方法，每種方法在某些情況下可以使性能快上兩倍：
我們希望本文可以讓您很好地了解在 DB2 insert 處理期間所發(fā)生的事情，以及如何監(jiān)視和提高其性能。
|
|
該附錄舉例說明了在本文中討論的那些優(yōu)化技術(shù)可以帶來的一些好處（以及對(duì)非優(yōu)化方法的沖擊）。每次測(cè)試都至少運(yùn)行 2 次，并且結(jié)果一致，但是該結(jié)果跟您在自己環(huán)境中看到的結(jié)果不一定完全吻合。特別地，這些結(jié)果是在一個(gè) I/O 強(qiáng)度比理想情況下更大的一個(gè)系統(tǒng)上得到的，因?yàn)橛脩舯砜臻g和日志被放在相同的文件系統(tǒng)中，并且是在相同的兩個(gè)磁盤上。因此，能減少 CPU 開銷的改進(jìn)策略通常還有比這里提到的更多的好處。
下面幾條適用于所有這些測(cè)試，如有例外則會(huì)另外注明：
| test # | 插入方法/注解 | # 行數(shù) | 耗時(shí)（秒） |
| 1 | CLI -- 對(duì)每一行執(zhí)行 SQLExecDirect | 10,000 | 180.63 |
| 2 | CLI -- 預(yù)處理語句，但是每次只插入 1 行 | 10,000 | 92.05 |
| 3 | CLI -- 預(yù)處理語句，并且是數(shù)組插入（每次插入 10 行） | 10,000 | 12.85 |
test 1-3 中自動(dòng)提交是處于啟用狀態(tài)的，因而對(duì)每一次 insert 都有一次 Commit。也就是說，在 test 1 和 test 2 中，對(duì)于每一行都有一次 Commit，在 test 3 中對(duì)于每 10 行有一次 Commit。至于 Commit 的性能影響，請(qǐng)參考 test 61-78。
表 2. 用一個(gè)表的內(nèi)容填充另一個(gè)表
| test # | 插入方法/注解 | # 行數(shù) | 耗時(shí)（秒） |
| 6 | 帶日志記錄的 INSERT ... SELECT | 100,000 | 15.66 |
| 7 | 帶 NOT LOGGED INITIALLY 選項(xiàng)的 INSERT ... SELECT | 100,000 | 16.43 |
| 8 | 與 test 7 一樣，但是 CHNGPGS_THRESH = 5 | 100,000 | 11.80 |
| 9 | 從游標(biāo)裝載（與 test 6-8 相同的 Select） | 100,000 | 12.95 |
test 6-9 計(jì)算了利用一個(gè)表填充另一個(gè)表的時(shí)間，包括 Commit 的時(shí)間。test 7 表明，使用 NOT LOGGED INITIALLY (NLI) 實(shí)際上會(huì)導(dǎo)致性能下降，因?yàn)樾枰?Commit 時(shí)將新頁寫到磁盤上：Commit 占了大半的時(shí)間。然而，當(dāng) test 8 中采用了更積極的頁清除時(shí)，性能就大大提高了，這主要是因?yàn)?Commit 的時(shí)間縮短了超過 5 秒鐘。test 9 表明，通過使用 Load 而不是 insert ... Select，性能提高了 17%，并且沒有 test 6 中那樣的 NLI 風(fēng)險(xiǎn)。
| test # | 插入方法/注解 | # 行數(shù) | 耗時(shí)（秒） |
| 11 | CLI -- 與 test 3 相同，但是有 100K 的行，提交 1000 次 | 100,000 | 31.51 |
| 12 | CLI -- 與 test 11 相同，但是有 1 個(gè)檢查約束 | 100,000 | 33.65 |
| 13 | CLI -- 與 test 11 相同，但是有 2 個(gè)檢查約束 | 100,000 | 36.63 |
| 14 | CLI -- 與 test 11 相同，但是有 1 個(gè)外鍵（FK） | 100,000 | 55.37 |
| 15 | CLI -- 與 test 11 相同，但是有 2 個(gè)外鍵（FK） | 100,000 | 72.71 |
| 16 | CLI -- 與 test 14 相同，但是 Insert 之后添加了 FK | 100,000 | 32.84 |
| 17 | CLI -- 與 test 15 相同，但是 Insert 之后添加了 FK | 100,000 | 38.89 |
| 18 | CLI -- 與 test 11 相同，但是有 1 個(gè) 進(jìn)行 Insert 操作的觸發(fā)器 | 100,000 | 67.57 |
| 19 | CLI -- 與 test 11 相同，但是有 2 個(gè) 進(jìn)行 Insert 操作的觸發(fā)器 | 100,000 | 175.95 |
| 20 | CLI -- 與 test 11 相同，但是有 1 個(gè) 進(jìn)行 Update 操作的觸發(fā)器 | 100,000 | 54.71 |
| 21 | CLI -- 與 test 11 相同，但是有 2 個(gè) 進(jìn)行 Update 操作的觸發(fā)器 | 100,000 | 150.18 |
| 22 | CLI -- 與 test 11 相同，但是有 1M 的行 | 100,000 | 282.02 |
| 23 | CLI -- 與 test 22 相同，但是 APPEND 的狀態(tài)是 ON | 100,000 | 281.64 |
顯然，檢查約束的使用對(duì)性能有一點(diǎn)小小的影響，但是外鍵和觸發(fā)器的影響卻非常大。在 test 18 和 test 19 中，各自的觸發(fā)器都將一行插入到基本表所在的相同表空間內(nèi)的一個(gè)不同的表中，這暴露了數(shù)據(jù)庫和日志磁盤上的 I/O 瓶頸。在 test 20 和 test 21 中，各自的觸發(fā)器在一個(gè)只有一行的表中增加了一個(gè)“insert count”列；雖然這里不像 test 18 和 test 19 那樣有其他的行，但是更新開銷和日志記錄仍導(dǎo)致 insert 運(yùn)行起來比沒有觸發(fā)器的情況下慢很多。雖然通過標(biāo)準(zhǔn)數(shù)據(jù)庫 I/O 調(diào)優(yōu)可以改進(jìn) test 18-21，但關(guān)鍵是在大規(guī)模插入期間應(yīng)盡可能避免存在約束。
| test # | 插入方法/注解 | # 行數(shù) | 索引個(gè)數(shù) | 耗時(shí)（秒） |
| 31 | CLI -- 與 test 3 相同，但是有 100K 行，提交 1000 次 | 100,000 | 0 | 31.51 |
| 32 | CLI -- 與 test 31 相同，但是有 1 個(gè)索引 | 100,000 | 1 | 53.73 |
| 33 | CLI -- 與 test 31 相同，但是有 2 個(gè)索引 | 100,000 | 2 | 83.26 |
| 34 | CLI -- 與 test 31 相同，但是有 3 個(gè)索引 | 100,000 | 3 | 108.21 |
| 35 | CLI -- 與 test 31 相同，但是有 4 個(gè)索引 | 100,000 | 4 | 141.63 |
| 36 | CLI -- 與 test 35 相同，但是在插入之后創(chuàng)建索引 | 100,000 | 4 (*） | 73.75 |
| 37 | CLI -- 與 test 32 相同，但是在插入之后創(chuàng)建索引 | 100,000 | 1 | 39.44 |
| 38 | CLI -- 與 test 32 相同，但是索引被群集起來 | 100,000 | 1 | 62.93 |
在 test 32-35 中，索引是在插入之前創(chuàng)建的，而在 test 36-37 中索引是在插入之后創(chuàng)建的（并且，對(duì)于后一次測(cè)試，耗時(shí)包括 insert 的時(shí)間加上 CREATE INDEX 語句的時(shí)間）。
| test # | 插入方法/注解 | # 行數(shù) | 耗時(shí)（秒） |
| 41 | CLI -- 與 test 31 相同（沒有 Identity 或 Sequence） | 100,000 | 31.51 |
| 42 | CLI -- 與 test 41 相同，但是有 Identity 列，無緩存 | 100,000 | 896.61 |
| 43 | CLI -- 與 test 41 相同，但是有 Identity 列，緩存 5 | 100,000 | 212.52 |
| 44 | CLI -- 與 test 41 相同，但是有 Identity 列，緩存 20（缺省） | 100,000 | 99.06 |
| 45 | CLI -- 與 test 41 相同，但是有 Identity 列，緩存 100 | 100,000 | 61.62 |
| 46 | CLI -- 與 test 41 相同，但是有 Identity 列，緩存 1000 | 100,000 | 37.51 |
| 47 | CLI -- 與 test 41 相同，但是有 Sequence，無緩存 | 100,000 | 896.92 |
| 48 | CLI -- 與 test 41 相同，但是有 Sequence，緩存 5 | 100,000 | 212.58 |
| 49 | CLI -- 與 test 41 相同，但是有 Sequence，緩存 20（缺省） | 100,000 | 101.87 |
| 50 | CLI -- 與 test 41 相同，但是有 Sequence，緩存 100 | 100,000 | 66.55 |
| 51 | CLI -- 與 test 41 相同，但是有 Sequence，緩存 1000 | 100,000 | 39.55 |
test 41-51 表明，如果緩存很小或者沒有緩存的話，使用 Identity 或 Sequence 對(duì)性能有很大的負(fù)面影響，但是通過使用較大的緩存可以使這種開銷幾乎可以忽略不計(jì)。
表 6. 展示使用大型數(shù)組和每次提交很多 insert 的效果
| test # | 插入方法/注解 | # 行數(shù) | 耗時(shí)（秒） |
| 1 | CLI -- 對(duì)每一行執(zhí)行 SQLExecDirect（同上） | 10,000 | 183.55 |
| 61 | CLI -- 與 test 1 相同，每 5 行一次 Commit | 10,000 | 118.41 |
| 62 | CLI -- 與 test 1 相同，每 10 行一次 Commit | 10,000 | 114.23 |
| 63 | CLI -- 與 test 1 相同，每 100 行一次 Commit | 10,000 | 103.43 |
| 64 | CLI -- 與 test 1 相同，每 1000 行一次 Commit | 10,000 | 102.86 |
| 2 | CLI -- 預(yù)處理語句，但是每次插入 1 行（同上） | 10,000 | 92.05 |
| 65 | CLI -- 與 test 2 相同，每 5 行一次 Commit | 10,000 | 22.34 |
| 66 | CLI -- 與 test 2 相同，每 10 行一次 Commit | 10,000 | 20.78 |
| 67 | CLI -- 與 test 2 相同，每 100 行一次 Commit | 10,000 | 10.11 |
| 68 | CLI -- 與 test 2 相同，每 1000 行一次 Commit | 10,000 | 7.58 |
| 69 | CLI -- 與 test 3 相同（10x 行）；數(shù)組大小為 10，每 10 行一次 Commit | 10,000 | 118.18 |
| 70 | CLI -- 與 test 69 相同，但是數(shù)組大小為 10，每 100 行一次 Commit | 10,000 | 56.71 |
| 71 | CLI -- 與 test 69 相同，但是數(shù)組大小為 10，每 1000 行一次 Commit | 10,000 | 30.09 |
| 72 | CLI -- 與 test 69 相同，但是數(shù)組大小為 100，每 100 行一次 Commit | 10,000 | 50.65 |
| 73 | CLI -- 與 test 69 相同，但是數(shù)組大小為 100，每 1000 行一次 Commit | 10,000 | 24.27 |
| 74 | CLI -- 與 test 69 相同，但是數(shù)組大小為 1000，每 1000 行一次 Commit | 10,000 | 23.43 |
| 75 | CLI -- 與 test 69 相同，但是數(shù)組大小為 2，每 2 行一次 Commit | 10,000 | 471.82 |
| 76 | CLI -- 與 test 69 相同，但是數(shù)組大小為 2，每 10 行一次 Commit | 10,000 | 155.21 |
| 77 | CLI -- 與 test 69 相同，但是數(shù)組大小為 2，每 100 行一次 Commit | 10,000 | 74.82 |
| 78 | CLI -- 與 test 69 相同，但是數(shù)組大小為 2，每 1000 行一次 Commit | 10,000 | 48.51 |
| 79 | CLI -- 通過 SQLSetStmtAttr（數(shù)組大小為 10）使用 Load | 10,000 | 13.68 |
前面的測(cè)試展示了使用大型數(shù)組和每次提交大量的行所帶來的好處，其中后者尤為重要。
| test # | 插入方法/注解 | # 行數(shù) | 耗時(shí)（秒） |
| 11 | CLI -- SMS，運(yùn)行了 db2empfa（同上） | 100,000 | 31.51 |
| 81 | 與 test 11 相同，但是使用 DMS 文件表空間 | 100,000 | 25.52 |
| 82 | 與 test 11 相同，但是 db2empfa 沒有運(yùn)行（SMS） | 100,000 | 62.87 |
| 83 | 與 test 11 相同，但是 extentsize = 4 （并且 prefetchsize = 4） | 100,000 | 38.37 |
| 83 | 與 test 11 相同，但是 extentsize = 4 （并且 prefetchsize = 4） | 100,000 | 38.37 |
| 84 | 與 test 11 相同，但是 extentsize = 8 （并且 prefetchsize = 8） | 100,000 | 34.61 |
| 85 | 與 test 11 相同，但是 extentsize = 16 （并且 prefetchsize = 16） | 100,000 | 31.75 |
前面的測(cè)試表明，對(duì)于 SMS，運(yùn)行 db2empfa 是取得良好的 insert 性能的關(guān)鍵，而使用小于 32 頁的區(qū)段是有害的。DMS 比 SMS 更好一些。
| test # | 插入方法/注解 | # 行數(shù) | 耗時(shí)（秒） |
| 11 | CLI -- （同上） | 100,000 | 31.51 |
| 91 | 與 test 11 相同，但是 CHAR(10) 列現(xiàn)在是 CLOB(10) | 100,000 | 286.49 |
前面的比較展示了使用 CLOB 列（雖然非常短）帶來的巨大影響。我們還對(duì) test 91 的變種做過嘗試，但是結(jié)果仍然十分接近，對(duì)于 CLOB 列不管選擇作不作日志記錄，或者是否進(jìn)行壓縮，都影響不大。
| test # | 插入方法/注解 | # 行數(shù) | 耗時(shí)（秒） |
| 11 | CLI -- （同上） | 100,000 | 31.51 |
| 101 | 與 test 11 相同，但是使用了 LOCKSIZE TABLE | 100,000 | 30.58 |
上述比較表明，使用表鎖而不是行鎖可以節(jié)省大約 3% 的耗時(shí)。
| test # | 插入方法/注解 | # 行數(shù) | 耗時(shí)（秒） |
| 104 | 與 test 71 相同，但是每次提交 10K 的行（LOGBUFSZ=8） | 100,000 | 28.53 |
| 105 | 與 test 104 相同，但是 LOGBUFSZ = 256 | 100,000 | 24.91 |
test 105 表明，如果將 LOGBUFSZ 升至足夠高，以免在 Insert 期間因?yàn)槿罩揪彌_區(qū)被填滿而被迫將日志寫到磁盤，這樣做可以提高大約 13% 的性能。
| test # | 插入方法/注解 | # 行數(shù) | 耗時(shí)（秒） |
| 22 | CLI -- （同上） | 1,000,000 | 282.02 |
| 111 | 與 test 22 相同，但是使用了所有優(yōu)化（見下面） | 1,000,000 | 160.45 |
| 1(*） | CLI -- （與 test 1 相同，但是增加到了 1,000,000 行） | 1,000,000 | ~18000.00 |
test 111 使用了前面試過的所有優(yōu)化技術(shù)來替代基線：使用 DMS 而不是 SMS，使用 1000 行的數(shù)組（而不是 10 行），每 10000 行提交一次（而不是 1000 行），使用 LOCKSIZE TABLE，使 APPEND 的狀態(tài)為 ON，并且 LOGBUFSZ 設(shè)為 256。注意，與最初的基線（test 1）相比，test 22 已經(jīng)做了一些優(yōu)化。這里將 test 1 擴(kuò)展到了 1,000,000 行，以表明僅僅更改少量因素就可能造成性能上的巨大差異。
|
|
附錄 B -- 對(duì)于批量插入的優(yōu)化建議清單
下面是在本文前面詳細(xì)討論過的一些建議。請(qǐng)記住，這些建議的影響程度千差萬別，有些建議對(duì)于除 Insert 之外的其他任務(wù)還可能有負(fù)面影響。
聯(lián)系客服
