DB2數據庫中提高INSERT性能的示例分析

DB2數據庫中提高INSERT性能的示例分析，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

INSERT 處理過程概述

　　首先讓我們快速地看看插入一行時的處理步驟。這些步驟中的每一步都有優化的潛力，對此我們在后面會一一討論。

　　在客戶機準備 語句。對于動態 SQL，在語句執行前就要做這一步，此處的性能是很重要的；對于靜態 SQL，這一步的性能實際上關系不大，因為語句的準備是事先完成的。在客戶機，將要插入的行的各個 列值組裝起來，發送到 DB2 服務器。 DB2 服務器確定將這一行插入到哪一頁中。DB2 在用于該頁的緩沖池中預留一個位置。假如 DB2 選定的是個已有的頁，那么就需要讀磁盤；假如使用一個新頁，則要在表空間(假如是SMS，也就是系統管理存儲的表空間)中為該頁物理地分配空間。插入了新行的每一頁最后都要從緩沖池寫入到磁盤。在目標頁中對該行進行格式化，并獲得該行上的一個X(exclusive，獨占的) 行鎖。將反映該 insert 的一條記錄寫入到日志緩沖區中。

　　最后提交包含該 insert 的事務，假如這時日志緩沖區中的記錄還沒有被寫入日志文檔的話，則將這些記錄寫到日志文檔中。此外，還可能發生很多類型的附加處理，這取決于數據庫配置，例如，索引或觸發器的存在。這種額外的處理對于性能來說也是意義重大的，我們在后面會討論到。

　　INSERT 的替代方案

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

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

　　從游標裝載

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

　　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 語句相比，從游標裝載幾乎能夠提高 20% 的性能。

　　從 CLI 裝載

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

　　任何INSERT能夠改進的地方

　　讓我們看看插入處理的一些必要步驟，連同我們能夠用來優化這些步驟的技巧。

　　1. 語句準備

　　作為一條 SQL 語句，INSERT 語句在執行之前必須由 DB2 進行編譯。這一步驟能夠自動發生(例如在 CLP 中，或在一次 CLI SQLExecDirect 調用中)，也能夠顯式地進行(例如，通過一條 SQL Prepare、CLI SQLPrepare 或 JDBC prepareStatement 語句)。該編譯過程牽涉到授權檢查、優化，連同將語句轉化為可執行格式時所需的其他一些活動。在編譯語句時，語句的訪問計劃被存儲在包緩存中。

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

　　insert into mytable values (1, 'abc')

　　insert into mytable values (2, 'def')

　　等等，換成帶有參數標記的語句，一次準備，重復執行，這樣做是十分可取的：

　　insert into mytable values (?, ?)

　　使用參數標記能夠讓一系列的 insert 的運行速度提高數倍。(在靜態 SQL 程式中使用主機變量也能夠獲得類似的好處。)

　　2. 發送列值到服務器

　　能夠歸為這一類的優化技巧有好幾種。最重要的一種技巧是在每條 insert 語句中包括多行，這樣就能夠避免對于每一行都進行客戶機-服務器通信，同時也減少了 DB2 開銷。可用于多行插入的技巧有：

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

　　在 JDBC 中使用批處理操作。這跟 CLI 中的數組插入相同，基于相同的概念，但是實現細節有所不同。當通過 prepareStatement 方法準備了 insert 語句之后，剩下的步驟是針對每一列調用適當的 setXXXX 方法(例如，setString 或 setInt)，然后是 addBatch。對于要插入的每一行，都要重復這些步驟，然后調用 executeBatch 來執行插入。要查看這方面的例子，請參閱“參考資料”一節中的 JDBC Tutorial。

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

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

　　下面是關于該話題的其他一些建議：

　　假如可能的話，讓客戶機和要存取的數據庫使用相同的代碼頁，以避免在服務器上的轉換代價。數據庫的代碼頁能夠通過運行“get db cfg for ”來確定。

　　在某些情況下，CLI 會自動執行數據類型轉換，但是這樣同時也會帶來看不見的(小小的)性能損耗。因此，盡量使插入值直接處于和相應列對應的格式。

　　將應用程式中和插入相關的配置開銷最小化。例如，當在 CLI 中使用數組插入時，對于整個一組插入，應該盡量確保對于每一列只執行一次 SQLBindParameter，而不是對每一組數組內容都執行一次。對于個體來說，這些調用的代價并不高，但是這些代價是累積的。

　　3. 找到存儲行的地方

　　DB2 使用三種算法中的一種來確定將行插入到哪里。(假如使用了多維群集(Multi-dimensional Clustering，MDC)，則另當別論，我們在這里不予討論。)

　　缺省模式是，DB2 搜索散布在表的各頁上的自由空間控制記錄(Free Space Control Records，FSCR)，以找到有足夠自由空間存放新行的頁。顯然，假如每頁上的自由空間都比較少的話，就要浪費很多的搜索時間。為了應付這一點， DB2 提供了 DB2MAXFSCRSEARCH 注冊表變量，以便允許將搜索范圍限制為少于缺省的 5 頁。

　　當表是通過 ALTER TABLE 以 APPEND 模式放置時，就要使用第二種算法。這樣就完全避免了 FSCR 搜索，因為只需簡單地將行直接放到表的末尾。

　　當表有群集索引(clustering index)時，就要用到最后一種算法。在這種情況下，DB2 試圖將每一行插入到有相似鍵值的一頁中。假如那一頁沒有空間了，DB2 就會嘗試附近的頁，假如附近的頁也沒有空間，DB2 就進行 FSCR 搜索。

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

　　假如有群集索引，則對 insert 的性能會有很大的負面影響，這一點也不驚奇，因為使用群集索引的目的就是通過在插入時做額外的工作來提高查詢(即 select)性能的。假如的確需要群集索引，那么能夠通過確保有足夠的自由空間來使其對插入的影響降至最小：使用 ALTER TABLE 增加 PCTFREE，然后使用 REORG 預留自由空間。但是，假如允許太多自由空間的存在，則可能導致查詢時需要讀取額外的頁，這反而大大違反了使用群集索引的本意。另一種選擇是，在批量插入之前先刪除群集索引，而后再重新創建群集索引，也許這是最優的方法(創建群集索引的開銷跟創建常規索引的開銷差不多，都不是很大，只是在插入時有額外的開銷)。

　　4. 緩沖池、I/O 和頁清除

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

　　假如表在系統管理存儲的(System Managed Storage，SMS)表空間中，當需要新頁時，缺省情況下是從文檔系統中分別為每一頁分配空間。但是，假如對數據庫運行了 db2empfa 命令，那么每個 SMS 表空間就會為新頁一次性分配一個區段。我們建議運行 db2empfa 命令，并使用 32 頁的區段。

　　對于數據庫管理的存儲(Database Managed Storage，DMS)表空間，空間是在創建表空間時就預先分配的，但是頁的區段則是在插入處理過程中指派給表的。和 SMS 相比，DMS 對空間的預分配能夠提高大約 20% 的性能 -- 使用 DMS 時，更改區段大小并沒有明顯的效果。

　　假如表上有索引，則對于每個插入的行，都要添加一個條目到每條索引。這需要在緩沖池中存在適當的索引頁。晚些時候我們將討論索引的維護，但是現在只需記住，插入時對緩沖池和 I/O 的考慮也類似地適用于索引頁，對于數據頁也是相同。

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

　　一般來說，對于批量插入，您會希望積極地進行 異步頁清除(asynchronous page cleaning)，這樣在緩沖池中就總有可用于新頁的空余位置。頁清除率，或說總缺頁率，可能導致計時上的很大不同，使得性能比較容易產生誤解。例如，假如使用 100,000 頁的緩沖池，并且不存在頁清除，則批量插入在結束前不會有任何新的或更改過的(“臟的”)頁寫到磁盤上，但是隨后的操作(例如選擇，甚至乎關閉數據庫)都將被大大推遲，因為這時有至多 100,000 個在插入時產生的臟頁要寫到磁盤上。另一方面，假如在同一情況下進行了積極的頁清除，則批量插入過程可能要花更長的時間，但是此后緩沖池中的臟頁要少一些，從而使得隨后的任務執行起來性能更佳。至于那些結果中到底哪個要更好些，我們并不是總能分得清，但是通常來說，將任何臟頁都存儲在緩沖池中是不可能的，所以為了取得最好性能，采取有效的頁清除是有必要的。

　　為了盡可能好地進行頁清除：將 CHNGPGS_THRESH 數據庫配置參數的值從缺省的 60 減少到 5 這么低。這個參數決定緩沖池中臟頁的?值百分比，當臟頁達到這個百分比時，就會啟動頁清除。

　　嘗試啟用注冊表變量 DB2_USE_ALTERNATE_PAGE_CLEANING(在 DB2 V8 FixPak 4 中最新提供)。通過將這個變量配置成 ON，能夠為頁清除提供一種比缺省方法(基于 CHNGPGS_THRESH 和 LSN 間隙觸發器)更積極的方法。我沒有評測過其效果。請參閱 FixPak 4 Release Notes 以了解這方面的信息。確保 NUM_IOCLEANERS 數據庫配置參數的值至少等于數據庫中物理存儲設備的數量。

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

　　當發生將頁寫到磁盤的 I/O 時，通過一些常規的 I/O 調優步驟能夠加快這一過程，例如：

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

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

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

　　5. 鎖

　　缺省情況下，每一個插入的行之上都有一個 X 鎖，這個鎖是在該行創建時就開始有的，一直到 insert 被提交。有兩個跟 insert 和鎖相關的性能問題：

　　為獲得和釋放鎖而產生的 CPU 開銷。

　　可能由于鎖沖突而導致的并發問題。

　　對于經過良好優化的批量插入，由獲得每一行之上的一個 X 鎖連同后來釋放該鎖引起的 CPU 開銷是比較可觀的。對于每個新行之上的鎖，惟一能夠替代的是表鎖(DB2 中沒有頁鎖)。當使用表鎖時，耗時減少了 3%。有 3 種情況能夠導致表鎖的使用，在討論表鎖的缺點之前，我們先用一點時間看看這 3 種情況：

　　運行 ALTER TABLE LOCKSIZE TABLE。這將導致 DB2 為隨后使用該表的任何 SQL 語句使用一個表鎖，直到 locksize 參數改回到 ROW。

　　運行 LOCK TABLE IN EXCLUSIVE MODE。這將導致表上立即上了一個 X 鎖。注意，在下一次提交(或回滾)的時候，這個表將被釋放，因此，假如您要運行一個測試，測試中每 N 行提交一次，那么就需要在每次提交之后重復執行 LOCK TABLE。

　　使用缺省鎖，但是讓 LOCKLIST 和 MAXLOCKS 數據庫配置參數的值比較小。當獲得少量的行鎖時，行鎖就會自動地逐漸升級為表鎖。

　　當然，任何這些的缺點就在于并發的影響：假如表上有一個 X 鎖，那么其他應用程式除非使用了隔離級別 UR(未提交的讀)，否則都不能訪問該表。假如知道獨占訪問不會導致問題，那么就應該盡量使用表鎖。但是，即使您堅持使用行鎖，也應記住，在批量插入期間，表中可能存在數千個有 X 鎖的新行，所以就可能和其他使用該表的應用程式產生沖突。通過一些方法能夠將這些沖突減至最少：

　　確保鎖的升級不會無故發生。您可能需要加大 LOCKLIST 和/或 MAXLOCKS 的值，以允許插入應用程式有足夠的鎖。對于其他的應用程式，使用隔離級別 UR。

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

　　發出一個 COMMIT 命令以釋放鎖，因此假如更頻繁地提交的話就足以減輕鎖沖突的負擔。

　　注意

　　在 V7 中，存在涉及 insert 和鍵鎖的并發問題，但是在 V8 中，由于提供了 type-2 索引，這些問題實際上已不見了。假如要遷移到 V8 中來，那么應該確保使用帶 CONVERT 關鍵字的 REORG INDEXES 命令，以便將索引從 type-1 轉換為 type-2。

　　在 V7 中，插入過程中可能使用 W 或 NW 鎖，但是在 V8 中只有在使用了 type-1 索引或隔離級別為 RR 的情況下才會出現這兩種鎖。因此，應盡可能避免這兩種情況。

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

　　6. 日志記錄

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

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

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

　　假如有一條語句失敗，那么這個表將被標記為不可訪問的，并且需要被刪除掉。這和其他恢復問題(請參閱 SQL Reference 關于 Create Table 的討論)一起使得 NLI 在很多情況下不能成為可行的方法。

　　在工作單元最后進行的提交，必須等到在此工作單元內涉及的任何臟頁都被寫到磁盤之后才能完成。這意味著這種提交要占用大量的時間。假如沒有積極地進行頁清除，那么在使用 NLI 的情況下，Insert 加上提交所耗費的總時間要更長一些。將 NLI 和積極的頁清除一起使用的時候，能夠大大減少耗時。假如使用 NLI，就要瞪大眼睛盯緊提交操作所耗費的時間。

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

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

　　在操作系統層將日志分放到多個磁盤。

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

　　避免使用 RAID 5，因為他不適合于寫密集型(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 排序，以便鍵值是排好序的，而不是隨機的，就能夠幫助加快索引維護。

關于DB2數據庫中提高INSERT性能的示例分析問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。