本文精妙地解釋了執行 insert 操作時(shí)所發(fā)生的事情，考察了 insert 的一些替代方案，并研究了影響 insert 性能的一些問(wèn)題，例如鎖、索引維護以及約束管理。
在使用 DB2® Universal Database (UDB) 的時(shí)候，行的插入是我們要執行的最常見(jiàn)、也是最重要的任務(wù)之一。本文是關(guān)于優(yōu)化 insert 、尤其是插入量比較大的 insert 的一個(gè)技巧匯編。談到性能，往往都存在著(zhù)某些權衡，這里也不例外。我們將討論在優(yōu)化 insert 的過(guò)程中可能帶來(lái)的權衡問(wèn)題。例如，您對 insert 采用了某種優(yōu)化技巧，但是這種技巧可能要求在 insert 之后還要進(jìn)行附加的處理，或者可能影響查詢(xún)的性能。我會(huì )提供一些性能測試的結果，以便讓您了解很多優(yōu)化技巧的作用。 附錄 A包含了對這些結果的一個(gè)小結，并且編了號。我將在全文各處以測試編號來(lái)引用結果。在后面的 結束語(yǔ)一節中，總結了大部分有益的技巧，而 附錄 B則列出了本文給出的所有建議。在本文中，我們無(wú)意研究關(guān)于如何實(shí)現這些技巧的細節，但是這方面的信息可以在 DB2 手冊中找到。請閱讀 參考資料一節，以了解更多細節。
  |
|
首先讓我們快速地看看插入一行時(shí)的處理步驟。這些步驟中的每一步都有優(yōu)化的潛力，對此我們在后面會(huì )一一討論。
 |
|
在詳細討論 insert 的優(yōu)化之前，讓我們先考慮一下 insert 的兩種替代方案：load 和 import。import 實(shí)用程序實(shí)際上是 SQL INSERT 的一個(gè)前端，但它的某些功能對于您來(lái)說(shuō)也是有用的。load 也有一些有用的額外功能，但是我們使用 load 而不使用 insert 的主要原因是可以提高性能。這里我們不會(huì )進(jìn)一步討論 import，不過(guò)讀者可以參閱后面的 參考資料一節，在那里可以找到指向 Data Movement Utilities Guide 的鏈接，該指南討論了 import 與 load 之間的不同之處。
load 直接格式化數據頁(yè)，而避免了由于插入導致的對每一行進(jìn)行處理的大部分開(kāi)銷(xiāo)（例如，日志記錄在這里實(shí)際上是消除了）。而且，load 可以更好地利用多處理器機器上的并行性。在 V8 load 中有兩個(gè)新功能，它們對于 load 成為 insert 的替代方案有著(zhù)特別的功效，這兩個(gè)功能是：從游標裝載和從調用層接口（CLI）應用程序裝載。
這種方法可用于應用程序的程序代碼（通過(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
附錄 A中的 test 6 和 9 表明，同等效的 INSERT ... SELECT 語(yǔ)句相比，從游標裝載幾乎可以提高 20% 的性能。
這種方法顯然只限于調用層接口（CLI）應用程序，但是它非?？?。這種技巧非常類(lèi)似于數組插入（ 后面會(huì )討論），DB2 附帶了這樣的示例，請查看 sqllib/samples/cli/tbload.c。通過(guò)查看附錄 A 中的 test 79我們可以看到，使用 load 時(shí)的速度是使用經(jīng)過(guò)完全優(yōu)化的數組插入（test 71）時(shí)的兩倍，幾乎要比未經(jīng)優(yōu)化的數組插入（比如 test 69）快 10 倍。
|
|
讓我們看看插入處理的一些必要步驟，以及我們可以用來(lái)優(yōu)化這些步驟的技巧。
作為一條 SQL 語(yǔ)句，INSERT 語(yǔ)句在執行之前必須由 DB2 進(jìn)行編譯。這一步驟可以自動(dòng)發(fā)生（例如在 CLP 中，或者在一次 CLI SQLExecDirect 調用中），也可以顯式地進(jìn)行（例如，通過(guò)一條 SQL Prepare、CLI SQLPrepare 或 JDBC prepareStatement 語(yǔ)句）。該編譯過(guò)程牽涉到授權檢查、優(yōu)化，以及將語(yǔ)句轉化為可執行格式時(shí)所需的其他一些活動(dòng)。在編譯語(yǔ)句時(shí)，語(yǔ)句的訪(fǎng)問(wèn)計劃被存儲在包緩存中。
如果重復地執行相同的 INSERT 語(yǔ)句，則該語(yǔ)句的訪(fǎng)問(wèn)計劃（通常）會(huì )進(jìn)入到包緩存中，這樣就免除了編譯的開(kāi)銷(xiāo)。然而，如果 insert 語(yǔ)句對于每一行有不同的值，那么每一條語(yǔ)句都將被看成是惟一的，必須單獨地進(jìn)行編譯。因此，將像下面這樣的重復語(yǔ)句： insert into mytable values (1, 'abc')
insert into mytable values (2, 'def')
等等，
換成帶有參數標記的語(yǔ)句，一次準備，重復執行，這樣做是十分可取的： insert into mytable values (?, ?)
將 test 1與 test 2相比， 61-64與 65-68相比，我們可以看到，使用參數標記可以讓一系列的 insert 的運行速度提高數倍。（在靜態(tài) SQL 程序中使用主機變量也可以獲得類(lèi)似的好處。）
可以歸為這一類(lèi)的優(yōu)化技巧有好幾種。最重要的一種技巧是在每條 insert 語(yǔ)句中包括多行，這樣就可以避免對于每一行都進(jìn)行客戶(hù)機-服務(wù)器通信，同時(shí)也減少了 DB2 開(kāi)銷(xiāo)?？捎糜诙嘈胁迦氲募记捎校?ul>
如果不可能在一條 insert 語(yǔ)句中傳遞多行，那么最好是將多條 insert 語(yǔ)句組成一組，將它們一起從客戶(hù)機傳遞到服務(wù)器。（不過(guò)，這意味著(zhù)每條 insert 都包含不同的值，都需要準備，因而其性能實(shí)際上要比使用參數標記情況下的性能更差一些，前面“語(yǔ)句準備”一節已對此作了討論。）將多條語(yǔ)句組合成一條語(yǔ)句可以通過(guò) Compound SQL 來(lái)實(shí)現：
下面是關(guān)于該話(huà)題的其他一些建議：
DB2 使用三種算法中的一種來(lái)確定將行插入到哪里。（如果使用了多維群集（Multi-dimensional Clustering，MDC），則另當別論，我們在這里不予討論。）要了解關(guān)于插入算法方面的細節，請參閱 DB2 V8 Administration Guide: Performance中提到的相關(guān)內容。
缺省模式是，DB2 搜索散布在表的各頁(yè)上的自由空間控制記錄（Free Space Control Records，FSCR），以找到有足夠自由空間存放新行的頁(yè)。顯然，如果每頁(yè)上的自由空間都比較少的話(huà)，就要浪費很多的搜索時(shí)間。為了應付這一點(diǎn)，DB2 提供了 DB2MAXFSCRSEARCH 注冊表變量，以便允許將搜索范圍限制為少于缺省的 5 頁(yè)。
當表是通過(guò) ALTER TABLE 以 APPEND 模式放置時(shí)，就要使用第二種算法。這樣就完全避免了 FSCR 搜索，因為只需簡(jiǎn)單地將行直接放到表的末尾。
當表有群集索引（clustering index）時(shí)，就要用到最后一種算法。在這種情況下，DB2 試圖將每一行插入到有相似鍵值的一頁(yè)中。如果那一頁(yè)沒(méi)有空間了，DB2 就會(huì )嘗試附近的頁(yè)，如果附近的頁(yè)也沒(méi)有空間，DB2 就進(jìn)行 FSCR 搜索。
如果只考慮插入時(shí)間的優(yōu)化，那么使用 APPEND 模式對于批量插入是最快的一種方法，但是這種方法的效果遠不如我們這里討論的很多其他方法那么成效顯著(zhù)。請參考 test 22 和 test 23。第二好的方法應該是采用缺省算法，但是，如果在最佳環(huán)境中，更改 DB2MAXFSCRSEARCH 的值影響很小，而在一個(gè) I/O 約束較少的環(huán)境中，這種更改所造成的影響就比較可觀(guān)了。
如果有群集索引，則對 insert 的性能會(huì )有很大的負面影響（test 32 和 38 表明幾乎有 20% 的開(kāi)銷(xiāo)），這一點(diǎn)也不驚奇，因為使用群集索引的目的就是通過(guò)在插入時(shí)做額外的工作來(lái)提高查詢(xún)（即 select）性能的。如果的確需要群集索引，那么可以通過(guò)確保有足夠的自由空間來(lái)使其對插入的影響降至最?。菏褂?ALTER TABLE 增加 PCTFREE，然后使用 REORG 預留自由空間。不過(guò)，如果允許太多自由空間的存在，則可能導致查詢(xún)時(shí)需要讀取額外的頁(yè)，這反而大大違反了使用群集索引的本意。另一種選擇是，在批量插入之前先刪除群集索引，而后再重新創(chuàng )建群集索引，也許這是最優(yōu)的方法（創(chuàng )建群集索引的開(kāi)銷(xiāo)跟創(chuàng )建常規索引的開(kāi)銷(xiāo)差不多，都不是很大，只是在插入時(shí)有額外的開(kāi)銷(xiāo)）。
每一條 insert 在執行時(shí)，都是先將新行存儲在一個(gè)頁(yè)中，并最終將那個(gè)頁(yè)寫(xiě)到磁盤(pán)上。一旦像前面討論的那樣指定了頁(yè)，那么在將行添加到該頁(yè)之前，該頁(yè)必須已經(jīng)在緩沖池中。對于批量插入，大部分頁(yè)都是最新指派給表的，因此讓我們關(guān)注一下對新頁(yè)的處理。
如果表在系統管理存儲的（System Managed Storage，SMS）表空間中，當需要新頁(yè)時(shí)，缺省情況下是從文件系統中分別為每一頁(yè)分配空間。但是，如果對數據庫運行了 db2empfa 命令，那么每個(gè) SMS 表空間就會(huì )為新頁(yè)一次性分配一個(gè)區段。test 11 和 test 82 表明，與區段大小為缺省的 32 頁(yè)的情況相比，運行 db2empfa 命令可以使對 SMS 表空間的插入快到兩倍，因為在 test 82 中對于一個(gè)頁(yè)有 32 次分配，而 test 11 中是 32 個(gè)頁(yè)一次分配。test 11 和 test 83-85 表明，如果區段大小小于 32 頁(yè)，則性能會(huì )逐步下降，因為要進(jìn)行額外的分配，但是讓區段大于 32 這樣的建議也未必有幫助。我們建議運行 db2empfa 命令，并使用 32 頁(yè)的區段。
對于數據庫管理的存儲（Database Managed Storage，DMS）表空間，空間是在創(chuàng )建表空間時(shí)就預先分配的，但是頁(yè)的區段則是在插入處理過(guò)程中指派給表的。與 SMS 相比，DMS 對空間的預分配可以提高大約 20% 的性能 -- 請參考 test 11 和 test 81。test 81 使用 DMS 文件表空間，而如果使用了 DMS raw 表空間的話(huà)，還可以多得到一點(diǎn)好處。使用 DMS 時(shí)，更改區段大小并沒(méi)有明顯的效果。
如果表上有索引，則對于每個(gè)插入的行，都要添加一個(gè)條目到每條索引。這要求在緩沖池中存在適當的索引頁(yè)。晚些時(shí)候我們將討論索引的維護，但是現在只需記住，插入時(shí)對緩沖池和 I/O 的考慮也類(lèi)似地適用于索引頁(yè)，對于數據頁(yè)也是一樣。
隨著(zhù)插入的進(jìn)行，越來(lái)越多的頁(yè)中將填入被插入的行，但是，DB2 不要求在 insert 或 Commit 后將任何新插入的或更新后的數據或索引寫(xiě)入到磁盤(pán)。（這是由于 DB2 的 writeahead 日志記錄算法。但是有一個(gè)例外，這將在關(guān)于日志記錄的小節中論述到。）然而，這些頁(yè)需要在某一時(shí)刻寫(xiě)到磁盤(pán)上，這個(gè)時(shí)刻可能會(huì )在數據庫關(guān)閉時(shí)才會(huì )輪到。
一般來(lái)說(shuō)，對于批量插入，您會(huì )希望積極地進(jìn)行 異步頁(yè)清除（asynchronous page cleaning），這樣在緩沖池中就總有可用于新頁(yè)的空余位置。頁(yè)清除率，或者說(shuō)總缺頁(yè)率，可能導致計時(shí)上的很大不同，使得性能比較容易產(chǎn)生誤解。例如，如果使用 100,000 頁(yè)的緩沖池，并且不存在頁(yè)清除，則批量插入在結束前不會(huì )有任何新的或更改過(guò)的（“臟的”）頁(yè)寫(xiě)到磁盤(pán)上，但是隨后的操作（例如選擇，甚至乎關(guān)閉數據庫）都將被大大推遲，因為這時(shí)有至多 100,000 個(gè)在插入時(shí)產(chǎn)生的臟頁(yè)要寫(xiě)到磁盤(pán)上。另一方面，如果在同一情況下進(jìn)行了積極的頁(yè)清除，則批量插入過(guò)程可能要花更長(cháng)的時(shí)間，但是此后緩沖池中的臟頁(yè)要少一些，從而使得隨后的任務(wù)執行起來(lái)性能更佳。至于那些結果中到底哪個(gè)要更好些，我們并不是總能分得清，但是通常來(lái)說(shuō)，將所有臟頁(yè)都存儲在緩沖池中是不可能的，所以為了取得最佳性能，采取有效的頁(yè)清除是有必要的。
為了盡可能好地進(jìn)行頁(yè)清除：
至于 I/O 本身，當需要建立索引時(shí)，可以通過(guò)使用盡可能大的緩沖池來(lái)將 I/O 活動(dòng)減至最少。（請參閱后面的“ 索引維護”一節。）如果不存在索引，則使用較大的緩沖池幫助不大，而只是推遲了 I/O。也就是說(shuō)，它允許所有新頁(yè)暫時(shí)安放在緩沖池中，但是最終仍需要將這些頁(yè)寫(xiě)到磁盤(pán)上。
當發(fā)生將頁(yè)寫(xiě)到磁盤(pán)的 I/O 時(shí)，通過(guò)一些常規的 I/O 調優(yōu)步驟可以加快這一過(guò)程，例如：
缺省情況下，每一個(gè)插入的行之上都有一個(gè) X 鎖，這個(gè)鎖是在該行創(chuàng )建時(shí)就開(kāi)始有的，一直到 insert 被提交。有兩個(gè)跟 insert 和鎖相關(guān)的性能問(wèn)題：
對于經(jīng)過(guò)良好優(yōu)化的批量插入，由獲得每一行之上的一個(gè) X 鎖以及后來(lái)釋放該鎖引起的 CPU 開(kāi)銷(xiāo)是比較可觀(guān)的。對于每個(gè)新行之上的鎖，惟一可以替代的是表鎖（DB2 中沒(méi)有頁(yè)鎖）。test 11 和 test 101 表明，當使用表鎖時(shí)，耗時(shí)減少了 3%。有 3 種情況可以導致表鎖的使用，在討論表鎖的缺點(diǎn)之前，我們先用一點(diǎn)時(shí)間看看這 3 種情況：
當然，所有這些的缺點(diǎn)就在于并發(fā)的影響：如果表上有一個(gè) X 鎖，那么其他應用程序除非使用了隔離級別 UR（未提交的讀），否則都不能訪(fǎng)問(wèn)該表。如果知道獨占訪(fǎng)問(wèn)不會(huì )導致問(wèn)題，那么就應該盡量使用表鎖。但是，即使您堅持使用行鎖，也應記住，在批量插入期間，表中可能存在數千個(gè)有 X 鎖的新行，所以就可能與其他使用該表的應用程序產(chǎn)生沖突。通過(guò)一些方法可以將這些沖突減至最少：
注意
缺省情況下，每條 insert 都會(huì )被記錄下來(lái)，以用于恢復。日志記錄首先被寫(xiě)到內存中的日志緩沖池，然后再寫(xiě)到日志文件，通常是在日志緩沖池已滿(mǎn)或者發(fā)生了一次提交時(shí)寫(xiě)到日志文件的。對批量插入的日志記錄的優(yōu)化實(shí)際上就是最小化日志記錄寫(xiě)的次數，以及使寫(xiě)的速度盡可能快。
這里首先考慮的是日志緩沖池的大小，這由數據庫配置參數 LOGBUFSZ 來(lái)控制。該參數缺省值為 8 頁(yè)或 32 K，這與大多數批量插入所需的理想日志緩沖池大小相比要小些。舉個(gè)例子，對于一個(gè)批量插入，假設對于每一行的日志內容有 200 字節，則在插入了 160 行之后，日志緩沖池就將被填滿(mǎn)。如果要插入 1000 行，因為日志緩沖池將被填滿(mǎn)幾次，再加上提交，所以大概有 6 次日志寫(xiě)。如果將 LOGBUFSZ 的值增加到 64 頁(yè)（256K）或者更大，緩沖池就不會(huì )被填滿(mǎn)，這樣的話(huà)對于該批量插入就只有一次日志寫(xiě)（在提交時(shí)）。test 104 和 test 105 表明，通過(guò)使用更大的 LOGBUFSZ 可以獲得大約 13% 的性能提升。較大日志緩沖池的不利之處是，緊急事故恢復所花的時(shí)間可能要稍微長(cháng)一點(diǎn)。
減少日志寫(xiě)的另一種可能性是對新行要插入到的那個(gè)表使用“ALTER TABLE <name> ACTIVATE NOT LOGGED INITIALLY”(NLI)。如果這樣做了，那么在該工作單元內不會(huì )記錄任何 insert 操作，但是這里存在兩個(gè)與 NLI 有關(guān)的重要問(wèn)題：
至于提高日志寫(xiě)的速度，有下面一些可能性：
提交迫使將日志記錄寫(xiě)到磁盤(pán)上，以保證提交的插入肯定會(huì )存在于數據庫中，并且釋放新行上的鎖。這些都是有價(jià)值的活動(dòng)，但是因為 Commit 總是要牽涉到同步 I/O（對于日志），而 insert 則不會(huì )，所以 Commit 的開(kāi)銷(xiāo)很容易高于 insert 的開(kāi)銷(xiāo)。因此，在進(jìn)行批量插入時(shí)，每一行都提交一次的做法對于性能來(lái)說(shuō)是很糟糕的，所以應確保不使用自動(dòng)提交（對于 CLI 和 CLP 來(lái)說(shuō)缺省情況正是如此）。建議大約每 1000 行提交一次：test 61-78 表明，當每 1000 行而不是一兩行提交一次時(shí)，性能可以提高大概 10 倍。不過(guò)，一次提交多于 1000 行只能節省少量的時(shí)間，但是一旦出現失敗，恢復起來(lái)所花的時(shí)間要更多。
對上述方法的一種修正：如果 MINCOMMIT 數據庫配置參數的值大于 1 （缺省值），則 DB2 就不必對每次 commit 都進(jìn)行一次同步 I/O，而是等待，并試圖與一組事件一起共享日志 I/O。對于某些環(huán)境來(lái)講，這樣做是有好處，但是對于批量插入常常沒(méi)有作用，甚至有負作用，因此，如果要執行的關(guān)鍵任務(wù)是批量插入，就應該讓 MINCOMMIT 的值保持為 1。
|
|
對于一次 insert，有幾種類(lèi)型的處理將自動(dòng)發(fā)生。如果您的主要目標只是減少插入時(shí)間，那么最簡(jiǎn)單的方法是避免所有這些處理的開(kāi)銷(xiāo)，但是如果從總體上考慮的話(huà)，這樣做未必值得。讓我們依次進(jìn)行討論。
對于插入的每一行，必須添加一個(gè)條目到表上的每個(gè)索引中（包括任何主鍵索引）。這一過(guò)程主要有兩方面的代價(jià)：
更壞的場(chǎng)景是，在索引維護期間有大量的隨機 I/O。假設要插入 10,000 行，在索引的緩沖池中有 5000 頁(yè)，并且要插入的各行的鍵值隨機分布在整個(gè)鍵范圍內。那么，有 10,000 個(gè)這么多的葉子頁(yè)（可能還有些非葉子頁(yè)）需要進(jìn)入緩沖池，以便對它們進(jìn)行搜索和/或更新，對于一個(gè)給定的葉子頁(yè)，它預先已經(jīng)在緩沖池中的概率只有 10%。對于每次的 insert，需要讀磁盤(pán)的概率如此之高，使得這種場(chǎng)景往往性能很差。
對于逐行插入，將新行添加到已有的索引中比起創(chuàng )建一個(gè)新索引來(lái)代價(jià)要高得多。如果是插入到一個(gè)空表，應該總是在進(jìn)行了列插入之后創(chuàng )建索引。（注意，如果使用了 load，則應該 預先創(chuàng )建索引。）如果要插入到一個(gè)已經(jīng)填充過(guò)的表，那么在列插入之前刪除索引，并在列插入之后重新創(chuàng )建索引，這種方法可能是最快的，但是只有在要插入相當多的行 -- 大概大于表的 10-20% 的時(shí)候，才能這么說(shuō)。如果為索引表空間使用較大的緩沖池，并且盡可能地將不同 insert 排序，以便鍵值是排好序的，而不是隨機的，就可以幫助加快索引維護。
附錄 A 中的 test 31-37給出的結果表明，insert 的耗時(shí)是如何隨著(zhù)索引的數目以及創(chuàng )建索引的時(shí)機而變化的?？偠灾?，與沒(méi)有索引相比，有 4 條索引的情況下 insert 可能要多耗費數倍的時(shí)間，而如果在 insert 之后才創(chuàng )建索引，就可以將總耗時(shí)（insert 耗時(shí)加上創(chuàng )建索引的耗時(shí)）縮短 1/4 到幾乎 1/2。
如果關(guān)鍵目標是將 insert 的性能最優(yōu)化，那么增加索引的 PCTFREE 時(shí)，就可能減少在隨機插入索引條目時(shí)出現頁(yè)拆分（page split）的次數。所以這樣做時(shí)要小心，不過(guò)，太多的自由空間意味著(zhù)大量的索引頁(yè)，這對查詢(xún)的性能乃至 insert 處理本身都會(huì )產(chǎn)生負面影響。
這一類(lèi)的開(kāi)銷(xiāo)包括檢查約束驗證和外鍵約束（參照完整性（RI））驗證。檢查約束開(kāi)銷(xiāo)很低（請參閱附錄 A 中的 test 11-13），這很大程度上是因為不需要對每一行使用 I/O（因為要檢查的值是在行內，只是要進(jìn)行一些計算而已）。
如果有外鍵，則插入一行就是另外一回事了。對于每個(gè)外鍵，都必須在父表中進(jìn)行一次查找，以確保有父行存在。雖然這種查找是通過(guò)主鍵索引進(jìn)行的，但是這仍然要占用 CPU 循環(huán)來(lái)進(jìn)行搜索，而且可能還要占用 I/O 將索引頁(yè)讀入緩沖池。test 11、14 和 15 表明，當有兩個(gè)外鍵時(shí)，insert 的耗時(shí)要翻一番。
在填充表之后使用 CREATE INDEX 比起通過(guò)一條一條的 INSERT 語(yǔ)句建立索引來(lái)代價(jià)要小些，同樣，使用 ALTER TABLE 創(chuàng )建外鍵（即進(jìn)行驗證）作為批量操作，比起在每次 insert 期間的驗證所增加的代價(jià)的總和來(lái)，要小一些。比較 test 14 和 test 16、test 15 和 test 17，我們可以看到，在 insert 之后創(chuàng )建外鍵可以將總耗時(shí)減少大約 40%。
如果可能的話(huà)，在大量插入行到一個(gè)表之前，應該先使用 ALTER TABLE 刪除表上的所有約束，在插入之后再重新創(chuàng )建這些約束（仍是使用 ALTER TABLE），只有在插入的行不到該表中所有行的 10-20% 時(shí)才可不必這樣做。
有些應用程序本身也會(huì )做一些檢查，以確保表之間的關(guān)系是有效的。也就是說(shuō)，在插入一個(gè)子行之前，應用程序會(huì )讀一個(gè)父行，以確保父行存在。如果這種檢查得以正確執行，那么在數據庫中定義外鍵約束將增加額外的開(kāi)銷(xiāo)。但是，至少有三個(gè)原因可以說(shuō)明為什么在數據庫中定義這些約束要更好些：
如果應用程序本身的檢查不能少，那么最好的替代方案是在 DB2 中定義外鍵，但是在 CREATE TABLE 或 ALTER TABLE 中帶上 NOT ENFORCED 子句，這樣就避免了檢查開(kāi)銷(xiāo)，而優(yōu)化器又能使用關(guān)系知識。
如果在一個(gè)表上定義了一個(gè)或多個(gè)進(jìn)行 Insert 操作的觸發(fā)器，那么每次的 insert 都將引起觸發(fā)器定義中的動(dòng)作的執行。由于那些被觸發(fā)的動(dòng)作通常是一條或多條 INSERT、UPDATE 或 DELETE 語(yǔ)句，因此，在數據量很大的 insert 中，觸發(fā)器的開(kāi)銷(xiāo)會(huì )很大。附錄 A 中的 test 18-21表明，增加觸發(fā)器會(huì )導致 insert 的性能減慢數倍。索引和約束可以臨時(shí)刪除，但是應用程序知識卻必須知道何時(shí)避免觸發(fā)器的執行是可接受的。也就是說(shuō)，知道何時(shí)不會(huì )導致數據完整性問(wèn)題。如果避免觸發(fā)器的執行是可接受的，您可以通過(guò) 參考資料中列出的文章里所描述的技術(shù)來(lái)臨時(shí)禁用觸發(fā)器。
這兩種方法可以讓 DB2 自動(dòng)生成整型列值，這通常是在 insert 期間進(jìn)行的。應該清楚的主要性能問(wèn)題是，由于可恢復性的原因，生成的值必須做日志記錄。為了減少日志記錄的開(kāi)銷(xiāo)，可以將這些值預存（緩存）起來(lái)，每當緩存用完時(shí)，才寫(xiě)一條日志記錄。缺省情況下是緩存 20 個(gè)值。
附錄 A 中 test 41-51的結果表明，如果沒(méi)有緩沖的話(huà)，耗時(shí)會(huì )非常大（幾乎要比缺省情況慢 9 倍），而如果使用比缺省情況更大的緩存，則可以縮減大半的時(shí)間，并且將使用 Identity（標識）或 Sequence（序列） 的時(shí)間減至不到原先的 20%。如果您要在使用 Identity 還是 Sequence 之間作選擇的話(huà)，那么我告訴您使用 Identity 要好出幾個(gè)百分點(diǎn)。
當插入行到一個(gè)表，并且該表用“generated as”子句定義了一個(gè)或多個(gè)列，例如： CREATE TABLE t1 (c1 CHAR(5), c2 CHAR(5) generated always as (UPPER(c1))) ，這時(shí)，為建立生成的值而進(jìn)行的函數調用將導致附加的開(kāi)銷(xiāo)。然而，這種開(kāi)銷(xiāo)非常小，不至于影響您使用這種功能的決心。
“refresh immediate”物化查詢(xún)表（MQT）的重新生成
MQT 可用于通過(guò)預先計算聚合值來(lái)增強查詢(xún)性能。例如：
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í)將重新計算 MQT 中的聚合，因此通常來(lái)講，對帶有 refresh immediate MQT 的表執行列插入不大可取。不過(guò)，DB2 會(huì )盡其所能優(yōu)化重新計算，例如掃描總結表，而不是整個(gè)基本表。我們建議對于涉及 MQT 的 insert 運行 Explain，以便清楚幕后情況。
|
|
當插入一行到一個(gè)分區表（使用 DB2 V8 的 Data Partitioning Feature (DPF)）時(shí)，首先要散列（hash）該行的分區鍵值以確定必須將該行插入到哪個(gè)分區，然后 DB2 將該行發(fā)送到那個(gè)分區。這種一次一行的處理方式比較慢，前面我們已看到，在 CLI 中，這種方式與數組插入方式比起來(lái)的確很慢，而且因為還需要將每一行從協(xié)調分區發(fā)送到目標分區，使得情況更加糟糕。
為了把開(kāi)銷(xiāo)降至最低，可以使用緩沖插入（buffered insert）。如果使用了這種方法，定向于某個(gè)給定分區的散列行首先會(huì )進(jìn)入一個(gè)緩沖區中，然后成組地發(fā)送到那個(gè)分區，而不是一次發(fā)送一行。您可以通過(guò) Prep 或 Bind 命令的“INSERT BUF”選項來(lái)引起緩沖插入。要了解關(guān)于緩沖插入的細節，請參閱 Application Development Guide 的參考資料：Programming Client Applications。在開(kāi)發(fā)者園地也有關(guān)于基于 Java（只包括 SQLJ）的緩沖插入的文章，請參閱后面的“參考資料”。
在 DPF 環(huán)境中，如果對于重復的批量插入要求絕對最大的性能，那么您可能需要研究?jì)蓚€(gè)相關(guān)的 API。第一個(gè) API 是 sqlugtpi，它讓?xiě)贸绦蚩梢垣@得一個(gè)表的分區信息。之后就可以使用這種信息，再結合 sqlugrpn API 來(lái)找到一行所屬的分區號。您可以使用這些 API 將屬于某個(gè)給定分區的所有數據組到一起，然后連接到那個(gè)分區，這樣就不需要在分區之間傳輸數據，對于每個(gè)分區都重復這么做。這種方法可以取得非?？斓男阅?，但是要花一定的精力來(lái)確保這種方法在有多個(gè)數據類(lèi)型、代碼頁(yè)等等的情況下也能十分有效。
DPF 插入可能引起的另一個(gè)問(wèn)題是，要插入到的那個(gè)表（子表）上可能有外鍵約束。假設父表和子表有不同的分區鍵。那么每個(gè)子行的父親一般會(huì )在一個(gè)不同的分區上，因此，對于大多數插入的行，對其父親的驗證檢查就需要從子分區跨越到父分區。對此的解決辦法是，讓父表和子表的分區鍵相同（這對于查詢(xún)性能來(lái)說(shuō)不是最好的選擇），或者，如果使用了多個(gè)邏輯分區，就可以將 DB2 注冊表變量設置為 YES。
通過(guò)使用 staging 表可以為某些場(chǎng)景下的 insert 提高性能。通常的用法是，不是批量插入行到一個(gè)表中，而是使用 LOAD 命令將行裝載到一個(gè) staging 表中；然后，就可以使用 INSERT ... SELECT 將行插入到主表。不管 LOAD 還是 INSERT ... SELECT，都比常規的插入要快得多，即使將這兩步加起來(lái)也常常要比常規插入快些。不過(guò)，單單就性能而言，在 V8 中還是使用 load 直接將表裝載到主表要快些，因為在 V8 中的 load 不像 V7 中那樣有并發(fā)限制。
除了數據的消息傳遞以外，致使您在 V8 中仍想使用 staging 表的主要原因是，這樣可以將批量插入拆散成能夠并行運行的更小的塊。在一個(gè)有多個(gè)處理器的系統上，每條插入將在一個(gè) DB2 代理中運行，并且不會(huì )占用多于一個(gè)的處理器，即使將 DBMINTRA_PARALLEL 參數被設為 ON 也是如此。例如，如果要在一臺 8-way 的機器上插入 1M 的行，一般的插入過(guò)程通常對 CPU 的利用不會(huì )多于 12% （100 / 8）。（另一方面，load 則會(huì )自動(dòng)使用百分比大得多的 CPU，這也是它比 insert 更可取的另一個(gè)原因。）相反，您可以將 1M 的行裝載到一個(gè) staging 表中，然后運行 8 條并發(fā)的 insert ... Select 語(yǔ)句，8 條 Select 語(yǔ)句中都有謂詞，每條語(yǔ)句從 staging 表中檢索大約 1/8 的惟一的行子集。
最后，您可以通過(guò)一個(gè)多線(xiàn)程應用程序運行并發(fā)的插入，其中每個(gè)線(xiàn)程做它自己的插入。
對于一條 INSERT ... SELECT，只要能使選擇更快，就可以減少整條語(yǔ)句的耗時(shí)，但是這超出了本文的范圍，不適合進(jìn)行詳細的討論。下面列出了一些可能性。注意，這些只適用于選擇部分。而同樣的這些因素對于插入部分一般沒(méi)什么幫助。
這些類(lèi)型的列是惟一的，它們不會(huì )緩存在緩沖池中。因此，任何包括一個(gè)或多個(gè)這種列的 insert 都會(huì )使得這些列被直接寫(xiě)到磁盤(pán)上，用 DB2 術(shù)語(yǔ)來(lái)說(shuō)就是“直接寫(xiě)（direct write”）。您可以想像，這會(huì )使 LOB/LONG 的 insert 比“一般” 的 insert 要慢得多： test 91中使用了一個(gè) CLOB 列，這種情況比基線(xiàn)測試（test 11，有一個(gè) CHAR 列）要慢 9 倍以上。有這樣一些優(yōu)化的可能性：
對于沒(méi)有約束的簡(jiǎn)單插入，將優(yōu)化級別從缺省值（5）改為 1 的測試雖然將優(yōu)化器的算法變得更廉價(jià)，但是并沒(méi)有對性能產(chǎn)生很大的變化。如果要經(jīng)常準備插入語(yǔ)句，插入牽涉到約束，或者有選擇部分，那么使用較低的優(yōu)化級別可能會(huì )有好處。相反，如果插入部分很小，而選擇部分比較復雜，那么將優(yōu)化級別從 5 增至更大將帶來(lái)好處。
利用源表（source table）插入/更新目標表（MERGE 語(yǔ)句）
一個(gè)相當常見(jiàn)的數據庫任務(wù)就是利用一個(gè)源表更新一個(gè)目標表。舉個(gè)特定的例子，比如取源表中的每一行，如果該行不在目標表中，那么就將該行插入到目標表，否則就更新目標行。在 V8 中可以使用 MERGE 語(yǔ)句獨立完成上述任務(wù)，而不必多次執行不同的語(yǔ)句，從而性能也就更好一些。
|
|
當您試圖監視和調優(yōu) insert 時(shí)，基本任務(wù)跟大多數其他的性能分析沒(méi)什么不同：找出瓶頸所在，然后直接處理瓶頸。欲確定瓶頸，首先就是利用操作系統工具查看 CPU、I/O、內存和網(wǎng)絡(luò )消耗。這樣應該就可以讓您排除某些方面，而將注意力放在一兩個(gè)方面。對操作系統實(shí)用程序的深入討論超出了本文的范圍。
盡量不要被非必要的問(wèn)題轉移了視線(xiàn)。例如，如果 CPU 利用率是 100%，那么這時(shí)減少 I/O 很可能無(wú)法提高性能，而當以后 CPU 瓶頸已經(jīng)解除時(shí)，這樣的更改本來(lái)可能非常有用，但是您可能會(huì )因為上一次的失敗而不再作這樣的更改了。
應該盡量讓?xiě)贸绦蛟诓僮髌陂g的不同時(shí)刻報告插入的速率。例如，如果知道在運行后的第 10 分鐘與第 5 分鐘時(shí)各自的每秒插入次數是否相同，是很有用的。通常，當 insert 開(kāi)始的時(shí)候，有一小段較慢的啟動(dòng)時(shí)間，然后當緩沖池填充了內容并且沒(méi)有數據頁(yè)的 I/O 時(shí)，就有一段速度比較快的時(shí)期。接著(zhù)，當開(kāi)始將數據頁(yè)往外寫(xiě)的時(shí)候，速率又會(huì )慢下來(lái)，如果頁(yè)清除或者 I/O 子系統不是最優(yōu)的，則更是如此。
對于非常大的批量插入，通常在某一時(shí)刻插入的速率會(huì )趨于平穩。如果不是這樣，那么通常是因為要在插入期間創(chuàng )建索引，使得需要創(chuàng )建越來(lái)越多的索引頁(yè)，并且可能還要進(jìn)行隨機的 I/O 操作，讀取已有的索引頁(yè)以便更新它們。如果的確是上述情況，那么使用更大的緩沖池是最好的解決辦法，但是為索引頁(yè)增加更多的自由空間也有所幫助。
現在讓我們看看可以幫助您監視和調優(yōu) insert 的關(guān)鍵 DB2 實(shí)用程序：Snapshots（快照監視）、Event Monitoring（事件監視）和 Explain。要了解關(guān)于快照監視和事件監視的更多信息，請參閱 System Monitor Guide and Reference；至于 Explain，請參閱 Administration Guide: Performance。
快照監視可以提供大量信息片斷來(lái)描述在插入的處理期間發(fā)生了什么事情。您可以使用以下步驟獲得所有可以得到的信息（或者也可以選擇獲得信息的子集）：
大多數與 insert 相關(guān)的信息都可以在數據庫快照中找到，并且大部分的這些信息也都會(huì )在適當的緩沖池、應用程序和表空間快照中以更大的粒度提供。
以下是數據庫快照中最相關(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 |
請注意連續快照中的“Rows inserted”，看看在批量插入期間插入的速率是否有變化。大多數其他的值都反映了 I/O 量和頁(yè)清除的效力。至于后者，理想情況下您可以看到，所有數據寫(xiě)都是同步的，而所有緩沖池寫(xiě)時(shí)間都是異步的（就像上面輸出的那樣）；如果不是這樣，嘗試降低 CHNGPGS_THRESH 和/或增加 NUM_IOCLEANERS。
通常在動(dòng)態(tài) SQL 快照中可以找到關(guān)于 insert 的附加信息?？纯纯偤臅r(shí)，并將其與用戶(hù)和系統 CPU 時(shí)間相比較，這樣做是十分有用的。耗時(shí)與 CPU 之間的差值大部分在于 I/O 部分，所以，哪個(gè)地方占去了大部分的時(shí)間以及哪個(gè)地方需要調優(yōu)也就很清楚了。
以下是一個(gè) 100,000 行的 CLI 數組插入的動(dòng)態(tài) SQL 快照條目的一個(gè)子集。注意，盡管應用程序只發(fā)送那個(gè)數量的 1/10 那么多的數組，“Number of executions”仍是對于每一行都有一個(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(?, ?, ?, ?, ?) |
快照輸出中的其他信息：
當事件在服務(wù)器上發(fā)生時(shí)，通過(guò) DB2 事件監視器可以獲得關(guān)于事件的性能信息。為了分析 insert 的性能，需要為語(yǔ)句創(chuàng )建一個(gè)事件監視器，并在 insert 執行期間激活該事件監視器。雖然有點(diǎn)過(guò)分，但是事件監視器的輸出會(huì )顯示每條 insert 語(yǔ)句的耗時(shí)。對于 OLTP 型的應用程序，對語(yǔ)句運行事件監視的開(kāi)銷(xiāo)相當高，其輸出也十分冗長(cháng)，所以應注意不要讓事件監視運行太長(cháng)的時(shí)間。即使是幾秒鐘也會(huì )產(chǎn)生數兆的輸出。您可以將監視器信息寫(xiě)入到一個(gè)表中，以便于對結果的分析，例如性能趨勢。
下面是一個(gè)濃縮的示例語(yǔ)句事件，在一系列的 10,000 個(gè) CLI 數組插入中每 10 行對應一次這樣的語(yǔ)句事件。每個(gè)數組只有一個(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 的性能不像預期的那么好，則有可能是因為有“隱藏”的處理發(fā)生。前面已經(jīng)討論過(guò)，這種處理可能以不同的形式出現，例如索引維護、約束驗證或者觸發(fā)器執行。對 insert 運行某種形式的 Explain （例如 Visual Explain，或者 Explain 語(yǔ)句加上 db2exfmt），就可以揭示大多數額外的處理（除了索引維護）。如果額外的處理可能是性能問(wèn)題的起因，那么可以消除這種額外的處理。
作為一個(gè)簡(jiǎn)單的例子，下面的圖（由 db2exfmt 產(chǎn)生）展示了“Insert into test1 values (?, ?, ...)”語(yǔ)句的訪(fǎng)問(wèn)計劃。您可以猜出這里的額外處理是什么嗎？回答就在下面。
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 對于 INSERT ... SELECT 語(yǔ)句也十分有用。insert 本身是非?？斓?，但是 SELECT 可能存在一個(gè)訪(fǎng)問(wèn)計劃問(wèn)題，這個(gè)問(wèn)題會(huì )拖慢整個(gè)語(yǔ)句。通過(guò) Explain 就可以揭示這一切。
答案：
上述訪(fǎng)問(wèn)計劃是針對將行插入到擁有外鍵關(guān)系的子表的 insert 的。其中有一個(gè) insert（步驟 5）與索引掃描（步驟 7）之間的嵌套循環(huán)連接（NLJOIN，步驟 4 ）。索引掃描實(shí)際上是對父表進(jìn)行主鍵查找，并完成外鍵約束驗證。在這種情況下，來(lái)自額外處理的開(kāi)銷(xiāo)相對來(lái)說(shuō)就比較?。核饕龗呙璧某杀竟烙嬛挥?2.60196 timerons（timerons 是（主要）結合了 CPU 和 I/O 代價(jià)的成本單位），而 insert 本身的成本是 25.5248 timerons。例如，如果有一個(gè)觸發(fā)器的話(huà)，那么就會(huì )在訪(fǎng)問(wèn)計劃中的一個(gè)或多個(gè) insert、Update 或 Delete 條目中反映出來(lái)。
順便提一下，當您在訪(fǎng)問(wèn)計劃中看到“GENROW”時(shí)，其實(shí)就是一個(gè)“generate row”步驟。這代表用于后續步驟的臨時(shí)行的創(chuàng )建，這里不需要擔心。
|
|
在本文中，我們給出了多種提高 insert 性能的方法。請參閱 附錄 B以了解這些方法的完整清單。下面是最重要的一部分方法，每種方法在某些情況下可以使性能快上兩倍：
我們希望本文可以讓您很好地了解在 DB2 insert 處理期間所發(fā)生的事情，以及如何監視和提高其性能。
|
|
該附錄舉例說(shuō)明了在本文中討論的那些優(yōu)化技術(shù)可以帶來(lái)的一些好處（以及對非優(yōu)化方法的沖擊）。每次測試都至少運行 2 次，并且結果一致，但是該結果跟您在自己環(huán)境中看到的結果不一定完全吻合。特別地，這些結果是在一個(gè) I/O 強度比理想情況下更大的一個(gè)系統上得到的，因為用戶(hù)表空間和日志被放在相同的文件系統中，并且是在相同的兩個(gè)磁盤(pán)上。因此，能減少 CPU 開(kāi)銷(xiāo)的改進(jìn)策略通常還有比這里提到的更多的好處。
下面幾條適用于所有這些測試，如有例外則會(huì )另外注明：
| test # | 插入方法/注解 | # 行數 | 耗時(shí)（秒） |
| 1 | CLI -- 對每一行執行 SQLExecDirect | 10,000 | 180.63 |
| 2 | CLI -- 預處理語(yǔ)句，但是每次只插入 1 行 | 10,000 | 92.05 |
| 3 | CLI -- 預處理語(yǔ)句，并且是數組插入（每次插入 10 行） | 10,000 | 12.85 |
test 1-3 中自動(dòng)提交是處于啟用狀態(tài)的，因而對每一次 insert 都有一次 Commit。也就是說(shuō)，在 test 1 和 test 2 中，對于每一行都有一次 Commit，在 test 3 中對于每 10 行有一次 Commit。至于 Commit 的性能影響，請參考 test 61-78。
| test # | 插入方法/注解 | # 行數 | 耗時(shí)（秒） |
| 6 | 帶日志記錄的 INSERT ... SELECT | 100,000 | 15.66 |
| 7 | 帶 NOT LOGGED INITIALLY 選項的 INSERT ... SELECT | 100,000 | 16.43 |
| 8 | 與 test 7 一樣，但是 CHNGPGS_THRESH = 5 | 100,000 | 11.80 |
| 9 | 從游標裝載（與 test 6-8 相同的 Select） | 100,000 | 12.95 |
test 6-9 計算了利用一個(gè)表填充另一個(gè)表的時(shí)間，包括 Commit 的時(shí)間。test 7 表明，使用 NOT LOGGED INITIALLY (NLI) 實(shí)際上會(huì )導致性能下降，因為需要在 Commit 時(shí)將新頁(yè)寫(xiě)到磁盤(pán)上：Commit 占了大半的時(shí)間。然而，當 test 8 中采用了更積極的頁(yè)清除時(shí)，性能就大大提高了，這主要是因為 Commit 的時(shí)間縮短了超過(guò) 5 秒鐘。test 9 表明，通過(guò)使用 Load 而不是 insert ... Select，性能提高了 17%，并且沒(méi)有 test 6 中那樣的 NLI 風(fēng)險。
| test # | 插入方法/注解 | # 行數 | 耗時(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 |
顯然，檢查約束的使用對性能有一點(diǎn)小小的影響，但是外鍵和觸發(fā)器的影響卻非常大。在 test 18 和 test 19 中，各自的觸發(fā)器都將一行插入到基本表所在的相同表空間內的一個(gè)不同的表中，這暴露了數據庫和日志磁盤(pán)上的 I/O 瓶頸。在 test 20 和 test 21 中，各自的觸發(fā)器在一個(gè)只有一行的表中增加了一個(gè)“insert count”列；雖然這里不像 test 18 和 test 19 那樣有其他的行，但是更新開(kāi)銷(xiāo)和日志記錄仍導致 insert 運行起來(lái)比沒(méi)有觸發(fā)器的情況下慢很多。雖然通過(guò)標準數據庫 I/O 調優(yōu)可以改進(jìn) test 18-21，但關(guān)鍵是在大規模插入期間應盡可能避免存在約束。
| test # | 插入方法/注解 | # 行數 | 索引個(gè)數 | 耗時(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 相同，但是索引被群集起來(lái) | 100,000 | 1 | 62.93 |
在 test 32-35 中，索引是在插入之前創(chuàng )建的，而在 test 36-37 中索引是在插入之后創(chuàng )建的（并且，對于后一次測試，耗時(shí)包括 insert 的時(shí)間加上 CREATE INDEX 語(yǔ)句的時(shí)間）。
| test # | 插入方法/注解 | # 行數 | 耗時(shí)（秒） |
| 41 | CLI -- 與 test 31 相同（沒(méi)有 Identity 或 Sequence） | 100,000 | 31.51 |
| 42 | CLI -- 與 test 41 相同，但是有 Identity 列，無(wú)緩存 | 100,000 | 896.61 |
| 43 | CLI -- 與 test 41 相同，但是有 Identity 列，緩存 5 | 100,000 | 212.52 |
| 44 | CLI -- 與 test 41 相同，但是有 Identity 列，緩存 20（缺?。?/td> | 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，無(wú)緩存 | 100,000 | 896.92 |
| 48 | CLI -- 與 test 41 相同，但是有 Sequence，緩存 5 | 100,000 | 212.58 |
| 49 | CLI -- 與 test 41 相同，但是有 Sequence，緩存 20（缺?。?/td> | 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 表明，如果緩存很小或者沒(méi)有緩存的話(huà)，使用 Identity 或 Sequence 對性能有很大的負面影響，但是通過(guò)使用較大的緩存可以使這種開(kāi)銷(xiāo)幾乎可以忽略不計。
表 6. 展示使用大型數組和每次提交很多 insert 的效果
| test # | 插入方法/注解 | # 行數 | 耗時(shí)（秒） |
| 1 | CLI -- 對每一行執行 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 行）；數組大小為 10，每 10 行一次 Commit | 10,000 | 118.18 |
| 70 | CLI -- 與 test 69 相同，但是數組大小為 10，每 100 行一次 Commit | 10,000 | 56.71 |
| 71 | CLI -- 與 test 69 相同，但是數組大小為 10，每 1000 行一次 Commit | 10,000 | 30.09 |
| 72 | CLI -- 與 test 69 相同，但是數組大小為 100，每 100 行一次 Commit | 10,000 | 50.65 |
| 73 | CLI -- 與 test 69 相同，但是數組大小為 100，每 1000 行一次 Commit | 10,000 | 24.27 |
| 74 | CLI -- 與 test 69 相同，但是數組大小為 1000，每 1000 行一次 Commit | 10,000 | 23.43 |
| 75 | CLI -- 與 test 69 相同，但是數組大小為 2，每 2 行一次 Commit | 10,000 | 471.82 |
| 76 | CLI -- 與 test 69 相同，但是數組大小為 2，每 10 行一次 Commit | 10,000 | 155.21 |
| 77 | CLI -- 與 test 69 相同，但是數組大小為 2，每 100 行一次 Commit | 10,000 | 74.82 |
| 78 | CLI -- 與 test 69 相同，但是數組大小為 2，每 1000 行一次 Commit | 10,000 | 48.51 |
| 79 | CLI -- 通過(guò) SQLSetStmtAttr（數組大小為 10）使用 Load | 10,000 | 13.68 |
前面的測試展示了使用大型數組和每次提交大量的行所帶來(lái)的好處，其中后者尤為重要。
| test # | 插入方法/注解 | # 行數 | 耗時(shí)（秒） |
| 11 | CLI -- SMS，運行了 db2empfa（同上） | 100,000 | 31.51 |
| 81 | 與 test 11 相同，但是使用 DMS 文件表空間 | 100,000 | 25.52 |
| 82 | 與 test 11 相同，但是 db2empfa 沒(méi)有運行（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 |
前面的測試表明，對于 SMS，運行 db2empfa 是取得良好的 insert 性能的關(guān)鍵，而使用小于 32 頁(yè)的區段是有害的。DMS 比 SMS 更好一些。
| test # | 插入方法/注解 | # 行數 | 耗時(shí)（秒） |
| 11 | CLI -- （同上） | 100,000 | 31.51 |
| 91 | 與 test 11 相同，但是 CHAR(10) 列現在是 CLOB(10) | 100,000 | 286.49 |
前面的比較展示了使用 CLOB 列（雖然非常短）帶來(lái)的巨大影響。我們還對 test 91 的變種做過(guò)嘗試，但是結果仍然十分接近，對于 CLOB 列不管選擇作不作日志記錄，或者是否進(jìn)行壓縮，都影響不大。
| test # | 插入方法/注解 | # 行數 | 耗時(shí)（秒） |
| 11 | CLI -- （同上） | 100,000 | 31.51 |
| 101 | 與 test 11 相同，但是使用了 LOCKSIZE TABLE | 100,000 | 30.58 |
上述比較表明，使用表鎖而不是行鎖可以節省大約 3% 的耗時(shí)。
| test # | 插入方法/注解 | # 行數 | 耗時(shí)（秒） |
| 104 | 與 test 71 相同，但是每次提交 10K 的行（LOGBUFSZ=8） | 100,000 | 28.53 |
| 105 | 與 test 104 相同，但是 LOGBUFSZ = 256 | 100,000 | 24.91 |
test 105 表明，如果將 LOGBUFSZ 升至足夠高，以免在 Insert 期間因為日志緩沖區被填滿(mǎn)而被迫將日志寫(xiě)到磁盤(pán)，這樣做可以提高大約 13% 的性能。
| test # | 插入方法/注解 | # 行數 | 耗時(shí)（秒） |
| 22 | CLI -- （同上） | 1,000,000 | 282.02 |
| 111 | 與 test 22 相同，但是使用了所有優(yōu)化（見(jiàn)下面） | 1,000,000 | 160.45 |
| 1(*） | CLI -- （與 test 1 相同，但是增加到了 1,000,000 行） | 1,000,000 | ~18000.00 |
test 111 使用了前面試過(guò)的所有優(yōu)化技術(shù)來(lái)替代基線(xiàn)：使用 DMS 而不是 SMS，使用 1000 行的數組（而不是 10 行），每 10000 行提交一次（而不是 1000 行），使用 LOCKSIZE TABLE，使 APPEND 的狀態(tài)為 ON，并且 LOGBUFSZ 設為 256。注意，與最初的基線(xiàn)（test 1）相比，test 22 已經(jīng)做了一些優(yōu)化。這里將 test 1 擴展到了 1,000,000 行，以表明僅僅更改少量因素就可能造成性能上的巨大差異。
|
|
下面是在本文前面詳細討論過(guò)的一些建議。請記住，這些建議的影響程度千差萬(wàn)別，有些建議對于除 Insert 之外的其他任務(wù)還可能有負面影響。
聯(lián)系客服
