Postgre資料庫Insert 、Query效能最佳化詳解

一、前言
以前的系統由於表設計比較複雜（多張表，表與表直接有主從關係），這個是商務邏輯決定的。 插入效率簡直實在無法忍受，必須最佳化。在瞭解了Postgre的Copy,unlogged table 特性
之後，決定一探究竟。

二、測試案例

1.資料表結構:表示一個員工工作績效的表（work_test）：共15個欄位
id,no,name,sex,tel,address,provice,city,post,mobile,department,work,start_time,end_time,score
索引（b-tree的叢集索引或者叫叢集索引）：id,no,name,sex,tel,address,provice,city,post,mobile,department,work

2.測試環境：win7，四核，2GB記憶體；postgre版本9.3；Npgsql串連Postgre資料庫。

三、insert/ transaction/ copy/unlogged table

1.insert 一個10W資料大概需要120s，雖然已經提升“不少”，但是還是不盡如人意。以前用SQLite時發現Transaction可以大幅提升效能，於是在Postgre中試試，發現並沒有明顯變
化。不知何故。

2. copy可以將檔案（csv）中的資料複製進資料庫中，當然資料表的結構和要資料類型要與檔案一一對應。據說可以大幅提升插入效能。
複製代碼 代碼如下:
COPY 'work_test' from 'c:\temp.csv' WITH DELIMITER AS ','

　　
使用Copy後發現插入的效能立馬提升至30s，相當於1s插入3300條記錄。這中間還包含產生csv檔案的時間。

3.unlogged table
unlogged table，網上的文章說可以10倍於insert。使用方法也很簡單:Create unlogged table ...
但是unlogged table 在遇到Postgre伺服器異常重啟後會丟失全部資料，所以如果你的資料不允許丟失，請不要使用。
使用後發現，大概20s，1s插入5000條記錄。

下面是三種插入方式的走勢圖：

說明：x軸表示資料庫中已有的記錄數，單位百萬，每個點是10W.Y軸表示每次插入所耗時間，單位秒。
藍色線：insert;之所以後面比較穩定是因為電腦沒有運行其他程式。所以說，電腦工作狀態對Postgre效率有一定的影響。
粉紅色：copy;
黃色線：copy+unlogged
 雖然建了索引，並且表中的資料一直累加進來，對於後續插入資料效能並沒有任何影響，這個結果就是我想看到的。

四、查詢測試
按name欄位搜尋：
複製代碼 代碼如下:
select * from work_test where name='1'




說明：x軸表示資料庫中已有的記錄數，單位百萬，每個點是10W.Y軸表示每次查詢所耗時間，單位毫秒。

藍色線：insert;
粉紅色：copy;
黃色線：copy+unlogged

由於三種插入方式結果都是一樣的，所以對比並沒有意義，這裡主要看查詢耗時。平均下來：500ms,並且隨著Row count的增加，查詢效率並沒有降低。這主要得益於良好的索引。
另外發現：條件越多，查詢效率越高，因為掃描的行數在減少，後面的圖就不貼出來了。