怎樣提高insert的效能

增刪改查，除了增，其他三樣都是和查有關，你搞的定select，那麼update和delete的效能基本就沒問題，今天我們專門討論一下insert的效能提高。

很多DBA或程式設計師感覺一旦發出insert into，效能只能聽天由命，似乎沒有什麼好的辦法去解決，大家可以順著以下思路去考慮關於insert的效能提升：

1、修改表的屬性為Append ON。

DB2的表屬性Append有開和關兩種狀態，預設情況下是關：

db2inst1@db2v105:~> db2 "select append_mode from syscat.tables where tabname = 'EVENTS'"

 

APPEND_MODE

-----------

N         

 

  1 record(s) selected.

當表的資料被刪除時，DB2的做法是空間並不會立即釋放，而是在原資料上做刪除標記，表示該行的空間可以重用，當在Append OFF發生insert時，DB2會掃描整個表的空閒空間，然後插入新行。修改Append為ON以後，DB2將不再搜尋空閒空間，而是直接把資料插入到表的最後，由於少了檢索的這一步，就可以提高效率，所以從根本上講，預設情況下的insert隱含著會帶有查詢，這種特性適合於那些大批量追加資料的表，主要用於資料遷移場景，Append屬性值為ON以後，需要注意經常reorg table。

db2inst1@db2v105:~> db2 create table t like syscat.events

DB20000I  The SQL command completed successfully.

db2inst1@db2v105:~> db2 alter table t append on

DB20000I  The SQL command completed successfully.

db2inst1@db2v105:~> db2 "select append_mode from syscat.tables where tabname = 'T'"

 

APPEND_MODE

-----------

Y         

 

  1 record(s) selected.

 

db2inst1@db2v105:~> db2 alter table t append off

DB20000I  The SQL command completed successfully.

db2inst1@db2v105:~> db2 "select append_mode from syscat.tables where tabname = 'T'"

 

APPEND_MODE

-----------

N         

 

  1 record(s) selected.

2、儘量使用DMS表空間。

萬一不幸你用的是SMS，可以使用db2empfa工具，它能讓SMS支援多頁檔案的空間分配（multi-page allocation），這將允許SMS 表空間一次增長一個Extend，而不是一頁，從而可以加快那些大型的插入操作和溢位磁碟的排序操作。由於SMS幾乎絕跡，這招insert加速大法可以忽略。

3、增加Log Buffer引數。

資料庫的這個引數有時會被DBA視而不見，它用於指定日誌記錄在寫入磁碟前的緩衝大小。注意：這個引數必須小於或等於dbheap引數，因為log buffer size是dbheap的一部分，這個冷知識瞭解到的人可能不多，因為dbheap通常都是automatic的。

db2inst1@db2v105:~> db2 get db cfg for zuma|grep -i logbufsz

 Log buffer size (4KB)                        (LOGBUFSZ) = 1602

db2inst1@db2v105:~> db2 get db cfg for zuma|grep -i dbheap

 Database heap (4KB)                         (DBHEAP) = AUTOMATIC(3300)

增加緩衝日誌記錄將導致更高效的日誌檔案I/O，這樣可以讓日誌記錄寫入磁碟的頻率更低，一次性寫入更多的日誌記錄，但是很顯然日誌緩衝也不能太大，這其中的原理請大家再翻看一下我以前部落格的內容吧。

4、避免網路開銷。

只要有條件，你的insert發起就最好就發生在伺服器端，或和伺服器在同一個網路安全域中進行，例如有用ETL工具做資料遷移，其實質是insert into，此時ETL工具所在伺服器都要避免要穿過三個路由器五個防火牆這種。

5、使用引數標記。

在insert語句中儘量使用引數標記，以及多行插入，這樣可以避免SQL硬解析。

6、集中提交。

別一個insert就提交1次，資料庫都讓你這樣給玩壞了，觀察著點資料庫快照，看下日誌的空間佔用情況，只要情況允許，就儘量10萬提交一次，50萬提交一次吧，這樣一下效能就會厲害出很多。

db2inst1@db2v105:~> db2 get snapshot for database on zuma|grep space

Log space available to the database (Bytes)     = 102753583

Log space used by the database (Bytes)        = 1592017

Maximum secondary log space used (Bytes)     = 0

Maximum total log space used (Bytes)           = 2359011

用了JDBC的，更要成批提交，如果能用COMPOUND SQL那就儘量用。

7、檢查一下和緩衝、IO有關的引數或設定。

比如NUM_IOSERVERS，NUM_IOCLEANERS，CHNGPGS_THRESH這三個引數，前兩個的意義大家再查一下inforcenter，現在DB2版本都有automatic可用，CHNGPGS_THRESH以前我寫過大篇幅的討論，還有並行IO等，不再浪費唾沫。最最重要的是bufferpool，一定要足夠大，足夠合理才行。

8、檢查關聯的邏輯物件。

要insert的表是否有自增列、觸發器、外來鍵、檢查約束等，這些都是影響效能的一些因素，如果有，能關的關能砍的砍，具體視情況而定吧。

9、解決伺服器和儲存故障。

我遇到過一次insert效能低下的case，上述8招都考慮或用過了，仍然不見效，於是細細的排查了伺服器自身效能，光纖線接入，RAID條帶等，最後發現儲存只有一個控制器在工作，重啟控制器後，那速度，刷刷的，這個讓人很委屈的case感覺特別坑。