PostgreSQL 應用程序設計的效能要訣(一)

原文作者簡介:

Josh Berkus 在 2002年加入 PostgreSQL 的核心開發成員. 現在他在 Sun Microsystems 的開放源碼資料庫團隊中擔任領導者.
擁有 11 年的資料庫經驗, 同時也曾經工作於其它社群專案包括: OpenOffice.org, Microsoft SQL Server, Oracle PL/SQL, and (shudder) COM+...


更新:2007-03-25
對映章節:PostgreSQL Application Performance Tips

內容:
這是一篇由 Josh Berkus 撰寫的好文章, 阿益翻譯分享給大家參考:

為 PostgreSQL 性能的應用程序設計

查詢撰寫規則
所有資料庫管理系統(DBMSes), "往返"(round-trip)時間是很寶貴的。這是因為它取得 Query 回來時必須通過語言分析器(language parser)、驅動器, 穿透網路介面, 資料庫分析器(database parser), 計劃器, 執行器, 反譯分析器, 再返回穿透網路介面, 通過驅動資料處理器, 和對 Client 應用程序的時間。
DBMSes 總是在時間上跟 CPU 爭取進程來處理這個循環週期, 並且由於各式各樣的原因每次在"往返"(round-trip) 上, PostgreSQL 是很嚴苛在意時間和系統資源。

更進一步, PostgreSQL 有著重要特點一切全是事務交易(per-transaction), 每筆事務交易處理都包括日誌記錄的產出和各種需要被設置的可見性規則在內。當您認為您可以不使用事務交易處理下的單一唯讀(singleton read-only) SELECT 的宣告, 實際上每一個宣告對 PostgreSQL 都是在進行事務交易處理。在沒有一種明確事務交易處理時, 宣告也是一種隱藏事務交易處理。

補充這, PostgreSQL 是唯一不輸給 Oracle 在處理大型複雜的查詢式(queries)上, 並且有能力輕易地處理複雜多宣告(multi-statement)式的事務交易在併發同作時的衝突處理。我們並且支持游標(cursors), 包括可捲動式(scrollable)與非捲動式(non-scrollable)。

Tip 1: 永遠不使用多數的小型查詢在當一個大型查詢能執行任務時.
它是共同在 MySQL 應用裡討論後加入的應用代碼; 那是由於手工查詢當 ID 來自父級記錄及之後迴路經由子級記錄 ID。這可能導致來自每個用戶元件螢幕連續性上百或數以萬計的查詢。每筆這種查詢往返時間大約花費 2-6 milleseconds, 看起來似乎不重大但直到您把它增加到 1000 筆次查詢, 到時您丟失 3-5 秒對往返時間。比較之下, 檢索所有那些紀錄在一次唯一查詢時只消耗少於不到 100 milleseconds, 時間節省達 80%.

Tip 2: 群組化多數的小查詢 UPDATES, INSERTS 或 DELETEs 到一個大型的宣告, 或者沒有的話, 大型事務.
首先, 在 MySQL 的早期的版本缺乏子查詢(subselects)導致應用開發者設計他們的資料修改宣告(DML)在相似情況下加入在中介軟體(middleware)中。這對 PostgreSQL 同樣是一種壞方法為。反之, 您若想要利用子查詢並且加入您的 UPDATE、INSERT 及 DELETE 宣告設法修改成批次(batches)在一個單一宣告中。這可減少往返時間與事務交易之上。

在一些案例中, 無論如何, 單一查詢不能寫入所有資料到您要的查詢並且您必須使用一串連續宣告。在這種情況下, 您想要保證包裹您的一系列 DML 宣告在一種清楚的事務交易。 (例如. BEGIN; UPDATE; UPDATE; UPDATE; COMMIT;). 這是縮減事務交易成本支出且節省執行時間高達 50%。

Tip 3: 連續性的 INSERTS 考慮用塊裝堆疊式(bulk)導入.

PostgreSQL 提供一個塊裝堆疊式的機制稱為 COPY, 採取定位分隔(tab-delimited)製表或 CSV 檔從文件或導管(pipe)來輸入。
COPY 能夠被使用在代替上百或數以萬計的 INSERTS, 它能削減執行時間達 75% 以上。


延伸閱讀:
PostgreSQL 應用程序設計的效能要訣(二)