基于需求分析的SQL優(yōu)化

梁軍科

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

關(guān)鍵詞：需求 優(yōu)化 sql 資源

中圖分類號：TP311 文獻(xiàn)標(biāo)識碼：A 文章編號：1007-9416（2016）07-0095-01

sql優(yōu)化的方法有多種多樣，但所有sql都是要實(shí)現(xiàn)用戶的目的要求，本文從需求分析的角度來總結(jié)一下。

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

下一步對具體語句的優(yōu)化，得到這條語句的需求，進(jìn)行需求分解。需要關(guān)聯(lián)數(shù)據(jù)庫的操作和使用的資源，對分解后的每一個需求進(jìn)行分析。實(shí)現(xiàn)的數(shù)據(jù)庫操作盡可能減少，可以只訪問部分?jǐn)?shù)據(jù)的就不要全部訪問，只能全部訪問的考慮減少表的大小，增加訪問的吞吐量，同時可考慮批量訪問，一次訪問更多數(shù)據(jù)。操作耗費(fèi)的成本可以通過幾個指標(biāo)來估計(jì)：表訪問次數(shù)，訪問行數(shù)，小結(jié)果集驅(qū)動，返回行數(shù)，邏輯讀，緩存，局部訪問，全局訪問，批量執(zhí)行，排序操作，遞歸，物理讀。執(zhí)行計(jì)劃中的這些操作都能估計(jì)出成本的大小。使用的資源方面，要減少資源的使用，存在瓶頸時增加資源，資源集中訪問時分散資源，資源使用不合理時優(yōu)化訪問路徑，調(diào)整資源結(jié)構(gòu)。

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

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

重用數(shù)據(jù)，避免重復(fù)操作：加大共享池，設(shè)置cursor_sharing或session_cached參數(shù)，加大數(shù)據(jù)緩沖區(qū)。緩存結(jié)果集。

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

局部訪問：盡可能采用局部掃描，可以為列增加索引使用局部索引掃描，轉(zhuǎn)化為分區(qū)表變?yōu)榉謪^(qū)掃描，使用NL連接。使每次的訪問都只訪問部分?jǐn)?shù)據(jù)。

全局訪問：只能進(jìn)行全掃描的，盡可能使用全索引掃描，在適當(dāng)?shù)牧性黾铀饕顾饕馨樵兊乃辛?；統(tǒng)計(jì)功能多考慮分析函數(shù)，減少表訪問次數(shù)；減少訪問對象的大小，使用分區(qū)表，進(jìn)行表壓縮；減少邏輯讀，可以增大塊大小，減少全表掃描塊數(shù)，增大db_file_multiblock_read_count增加單次掃描所讀取的塊數(shù)，降低高水位線，降低讀取次數(shù)。

分散資源的操作如下：

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

優(yōu)化資源使用的操作如下：

使用優(yōu)化的訪問路徑：返回較多的數(shù)據(jù)多采用全表掃描，全索引掃描，使用hash連接和排序合并連接，返回較少記錄多采用索引局部掃描，嵌套循環(huán)連接。

使用優(yōu)化的存儲結(jié)構(gòu)：優(yōu)化的算法需要有好的存儲結(jié)構(gòu)來配合，良好的聚合因子能避免索引回表，簇表，索引組織表能避免回表，降低高水位減少全掃描的塊數(shù)，使用臨時表空間組減少資源競爭，反向鍵索引減少熱塊競爭。在適當(dāng)?shù)牧猩霞铀饕赡苓x擇局部掃描，使用分區(qū)掃描減少塊訪問。

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

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