從資料庫來調校：資料處理存取篇

資料庫系統效能出現問題時，追蹤到最後，常會發現是因為某個SQL指令未加以優化而造成。水能載舟亦能覆舟，如何在效能優化的考量下達成資料存取的需求？

資料可攜性設計考量的迷思

在一些最佳實踐方案的設計建議，或是J2EE架構設計藍圖中，對於資料處理的邏輯設計都會強調可攜性（Portability）考量的重要。可攜性考量的主要觀點，在於將資料處理邏輯實作在商業邏輯層，而不以資料庫特有之機制來處理（像是採用Stored Procedure、Trigger等），以免未來在更換資料庫系統時，保有系統移植的平順度，不會因此而需要重新撰寫資料庫端的邏輯處理作業。這樣的思維立意甚佳，也有不少將之奉為圭臬，將資料庫提供的強大功能棄之不用，而依賴在不易處理複雜資料處理作業的持久層（Persistence Layer）軟體框架上。

透過持久層進行資料庫存取，美其名是表揚物件封裝的精神，符合軟體框架設計的理念，實則有如隔靴搔癢，無法有效掌握資料存取的方式。持久層以未被資料庫系統認定的較佳方式存取資料庫，理所當然在效能表現上必然不會比直接以撰寫SQL指令來得佳。若是需要進行多個資料表關連的查詢時，這樣的問題更會被突顯出來。

就實務上而言，資料處理效能的最佳化，勢必是直接使用資料庫系統所提供自身的執行環境及程式語言，才能將資料處理作業效率提到最高。這樣的設計少了應用伺服器與資料庫之間的資料傳輸時間，降低因連線中斷造成交易作業異常的風險。

找出問題SQL進行改善

在資料庫系統端，最常見造成效能表現瓶頸的原因是起因於SQL指令。目前許多便利的開發工具都提供了SQL指令產生器，該功能方便地幫開發人員快速產生SQL語法，但幾乎都是非最佳化後的結果，若直接拿來使用，常常就會造成效能問題。利用資料庫所提供的管理介面，像是Oracle Enterprise Manager、SQL Server Management Studio都可以找出成本較高的指令，再來進行語法的調整及優化。

針對SQL命令的改善作法，可以搭配資料庫系統所提供的分析工具來先了解主要造成執行效能問題的環節，像是Oracle的Explain Plan，或是MS SQL Server的執行計畫，讓我們可以分析得出SQL命令預計在執行過程中，所進行的處理步驟及可能花費的時間及成本。從這裡得出的分析結果，可以快速找出改善的點，最常見的便是出現Full Table Scan的項目，勢必得建立適當的索引來加速處理。

實務上會避免一些SQL撰寫用法，像是：

● 避免用LIKE方式查詢，會造成資料庫的Full Table Scan，成本甚鉅。

● 進行多個資料表關連查詢時，在SQL指令中WHERE後的資料表名稱順序，建議資料量由大至小來依列排放。

● 查詢條件中避免直接使用函式或運算結果當成是過濾條件，在指令處理上會多一些處理程序而造成成本較高。

● 盡可能不要在同一個SQL指令中，使用多層次查詢（sub-query），若必要時，以增加條件來減少次查詢中回傳資料筆數。

批次處理程式的設計原則

在許多的應用實例上，會設計批次處理的程式來進行資料存取，例如定期報表的產生、未付款訂單的系統取消作業等。而批次作業的特性在於，每次執行時處理的資料量較大，處理上必須考量到資源使用及釋放的時間點，同時是否會影響線上網站正常的使用。所以在設計上必須同時考量到執行的時間何時合適，預估每次執行作業所需的時間，以及影響的資料層面及處理的資料量。

若批次作業主要為查詢報表的產出，需要留意執行時間是否避開網站用量尖峰時間。若為大量資料異動作業，盡可能是每異動一小批資料即回寫乙次（commit），因為異動資料的作業在尚未commit之前，該資料是會被系統進行鎖定處理（data lock），若當時正好有其他程式需要異動同一筆資料時，則會進入等候狀態，就會造成整體處理時間過長。

妥善規畫網站後臺功能

網站的前臺與後臺管理系統，大都會設計成存取同一個資料庫，用意在於提供後臺資料管理的便利性，能即時將任何資料的異動反映到前臺內容中。然而這樣的架構下，後臺管理系統對於資料庫的存取設計，就會直接影響到前臺瀏覽效能的表現。

就設計上而言，網站前臺的頁面瀏覽方式，其對資料庫存取的方法重複性較高，對應到SQL指令的語法也較能在預期的範圍內，所以SQL語法上較比較能享受到因調校後所帶來的效能改善。例如常見的動態網頁設計，會傳入不同的編號代碼來取出對應的文章內容，或是以商品編號來顯示不同商品頁面的購買資訊。以這樣的資料模型架構下，建立對應的索引鍵值，以及查詢快取的機制，都可有效發揮，明顯改善網站的資料存取效能。

而後臺系統必須同時提供CRUD多種資料操控功能，而且為求方便管理人員作業便利，會透過多種join的方式來參照多個資料表，讓使用者可以在較少的點擊數下完成所預期的資料查詢或處理作業。像是提供地址縣市鄉鎮區域的下拉連動選單，點選商品分類時同時呈現屬於該分類下的所有商品，或是查詢某特定會員基本資料時，需要同步查詢其訂單交易資料及客服記錄等。這些資料關連的操作，都遠比前臺提供給一般使用者的頁面功能，成本來得高上許多。

為求對線上資料庫的衝擊降到最低，就實務面的設計上，以下是幾點在設計時可以參考的做法：

● 盡可能不要在單一網頁，呈現過多需要透過資料表關連才能得到的資料。就使用者行為而言，不見得每次頁面呈現，都需要閱讀到頁面上的所有資訊，以額外的連結提供進一步查詢，在真正需要顯示時再進行查詢，可以省下額外處理成本。

● 較複雜的資料存取功能，以權限控管的方式僅提供少數人員使用。一些特定的複雜操作功能可利用有效管控的方式減少使用者的使用次數及頻率，以免不當操作直接影響資料庫效能。

● 在管理操作介面上要求輸入較多的查詢條件，才允許進行作業。像查詢功能最忌諱的是，使用者不下任何條件即送出查詢請求。對於像是訂單查詢功能，必須同時提供兩項以上之查詢條件，才允許送出處理，可以避免單次查詢時所回傳的資料量過多，而造成效能不彰。至於查詢條件要多少才允許放行，則需再考量涉及的資料量多寡而定。

● 運用使用介面上的防呆設計，以免被不當重複執行。實務上常見到使用者因系統反應時間較長，會利用不斷重整網頁或重複按下送出，而造成主機端進行無謂的多次處理。若能套用防呆設計，可以阻擋因無意的點擊而產生的動作。像是在表單資料送出時，原送出按鍵予以失效，或是在按下送出時，利用CSS圖層方式來顯示執行中的提示，以防重複點選。

● 統計彙整型報表，避免對線上資料庫直接操作。像是周期性報表，需要透過大量彙總運算資源的報表內容，不宜直接利用線上環境的資料庫來產生，建議另外妥善設計資料匯出的功能，提供使用者自行下載原始資料，由他們自行透過Excel等工具製作進階的圖表，可以省去龐大系統代價。