基于需求分析的SQL優化

梁軍科

摘要：sql優化是數據庫優化中最后的環節，無論sql語句如何改寫都必須實現它原有的需求。本文介紹了一種通過sql語句還原需求，并結合業務上給定的需求，以需求的分析為起點，估計優化策略，來實現sql優化的方法。對于企業來說，數據是業務運行的關鍵，需要后臺數據庫的支持。數據庫的運行效率決定著業務運行的效率，數據庫優化在其中起著關鍵的作用。在優化過程中，可以通過調整內存分布，增加索引，收集統計數據等方法把問題處理掉，但是總有一些情況無法按上述辦法解決，最終都要進行sql的優化。

關鍵詞：需求 優化 sql 資源

中圖分類號：TP311 文獻標識碼：A 文章編號：1007-9416（2016）07-0095-01

sql優化的方法有多種多樣，但所有sql都是要實現用戶的目的要求，本文從需求分析的角度來總結一下。

系統設計之初，從設備選型到軟件設計，庫表結構設計就是根據用戶的需求來構建，到達sql語句這一層次，已經分解成最小單元的需求。按說在sql調優這一步，已經沒有獲取需求，分析需求的問題了，需求的實現也已經由sql表現完畢，調優所做的只是選擇一種更好的實現。但是有時候需求分析的幾個方面仍然需要重新認真考慮。當前實現不一定完全對應這個需求，比如檢查表有無記錄，通過取得表記錄數，如果記錄數>0，則表示有記錄。另一種實現是只要檢查到表存在一條記錄則表示有記錄，兩種實現都可以表達需求，但是取得所有記錄的代價和只取一條記錄的代價對大表來說完全不同。因此要對已有的需求再分析，明確真實需求，排除矛盾需求，進行需求取舍，需求合并，需求改造。根據分析，可能一條sql不是必要的，可以丟棄；定期執行的語句不用執行那么頻繁；幾條語句合并執行，減少了重復的操作；從全局訪問改成局部訪問；通過物化視圖或緩存的方式預先準備好數據。雖然不是每次都能從系統的層面來考慮需求，可一旦能夠實施，將會對優化帶來明顯的效果。

下一步對具體語句的優化，得到這條語句的需求，進行需求分解。需要關聯數據庫的操作和使用的資源，對分解后的每一個需求進行分析。實現的數據庫操作盡可能減少，可以只訪問部分數據的就不要全部訪問，只能全部訪問的考慮減少表的大小，增加訪問的吞吐量，同時可考慮批量訪問，一次訪問更多數據。操作耗費的成本可以通過幾個指標來估計：表訪問次數，訪問行數，小結果集驅動，返回行數，邏輯讀，緩存，局部訪問，全局訪問，批量執行，排序操作，遞歸，物理讀。執行計劃中的這些操作都能估計出成本的大小。使用的資源方面，要減少資源的使用，存在瓶頸時增加資源，資源集中訪問時分散資源，資源使用不合理時優化訪問路徑，調整資源結構。

減少資源使用的操作如下：

避免或減少多余的操作：避免排序，遞歸等耗資源的操作，可以加大日志大小，增加日志組，減少日志切換頻率。個別情況下可關閉歸檔，關閉日志。避免表空間頻繁擴展，降低定時掃描頻率，消除多余任務，批量提交，批量執行。重新選擇采樣點和比率，只針對某些表，索引收集統計信息。

重用數據，避免重復操作：加大共享池，設置cursor_sharing或session_cached參數，加大數據緩沖區。緩存結果集。

合并操作：case when，decode分析把相似的條件合并為一條語句。

局部訪問：盡可能采用局部掃描，可以為列增加索引使用局部索引掃描，轉化為分區表變為分區掃描，使用NL連接。使每次的訪問都只訪問部分數據。

全局訪問：只能進行全掃描的，盡可能使用全索引掃描，在適當的列增加索引，使索引能包含查詢的所有列；統計功能多考慮分析函數，減少表訪問次數；減少訪問對象的大小，使用分區表，進行表壓縮；減少邏輯讀，可以增大塊大小，減少全表掃描塊數，增大db_file_multiblock_read_count增加單次掃描所讀取的塊數，降低高水位線，降低讀取次數。

分散資源的操作如下：

使用臨時表空間組，分散臨時表空間的使用，緩存sequence，分散熱塊，調整塊尺寸，使用反向鍵索引，減少資源等待和阻塞

優化資源使用的操作如下：

使用優化的訪問路徑：返回較多的數據多采用全表掃描，全索引掃描，使用hash連接和排序合并連接，返回較少記錄多采用索引局部掃描，嵌套循環連接。

使用優化的存儲結構：優化的算法需要有好的存儲結構來配合，良好的聚合因子能避免索引回表，簇表，索引組織表能避免回表，降低高水位減少全掃描的塊數，使用臨時表空間組減少資源競爭，反向鍵索引減少熱塊競爭。在適當的列上加索引可能選擇局部掃描，使用分區掃描減少塊訪問。

以上介紹了需求分析及其與數據庫相關的操作和資源使用的情況。基于需求分析的sql優化方法，可以有兩個相反的過程。在拿到需要優化的sql語句之后，按照從內到外的順序，從最內部的子查詢開始向外分析，判斷出語句實現的什么需求。傳統上分析出語句結構后就可以進行優化，弱化選取出來的字段，重點關注涉及的表和視圖，表連接順序，連接字段，過濾條件，結合執行計劃的指標，表的統計信息及表結構，判斷是否是優化的實現，從中發現原有語句的缺陷。基于需要的優化可以到此為止，也可以繼續分析，進入相反的過程，忘掉原來語句的實現方法，把根據語句判斷出的需求和業務上給定的需求結合起來，得到真正的需求。再從最初的需求入手，對需求從整體架構到細節進行分析，取舍，改造，看需求的達成有幾種方法，分析它的利弊和適用場景，哪種最好。確定一種需求的實現，進行需求的分解，分成幾步動作，每一步根據分析的結果采取一個優化的策略方法，選擇數據庫采用的操作，估計耗費的成本，最后對比原來語句的實現方法，確認給定需求下的最優化方法。然后看一看數據庫是否支持這些方法，不能支持的有什么次優的方法，折衷的方式達成。

通過以上步驟，完成了基于需求的sql優化。任何語句都是用來實現需求的，所以此方法可以普遍使用。通過需求分析，可以在最細致的功能上考慮適用的操作和使用的資源，降低實現的復雜度，從而得到優化的訪問策略，使用語句優化有規可循。