梁軍科

摘要：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)化的方法。對(duì)于企業(yè)來說，數(shù)據(jù)是業(yè)務(wù)運(yùn)行的關(guān)鍵，需要后臺(tái)數(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 資源

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：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)。但是有時(shí)候需求分析的幾個(gè)方面仍然需要重新認(rèn)真考慮。當(dāng)前實(shí)現(xiàn)不一定完全對(duì)應(yīng)這個(gè)需求，比如檢查表有無記錄，通過取得表記錄數(shù)，如果記錄數(shù)>0，則表示有記錄。另一種實(shí)現(xiàn)是只要檢查到表存在一條記錄則表示有記錄，兩種實(shí)現(xiàn)都可以表達(dá)需求，但是取得所有記錄的代價(jià)和只取一條記錄的代價(jià)對(duì)大表來說完全不同。因此要對(duì)已有的需求再分析，明確真實(shí)需求，排除矛盾需求，進(jìn)行需求取舍，需求合并，需求改造。根據(jù)分析，可能一條sql不是必要的，可以丟棄；定期執(zhí)行的語句不用執(zhí)行那么頻繁；幾條語句合并執(zhí)行，減少了重復(fù)的操作；從全局訪問改成局部訪問；通過物化視圖或緩存的方式預(yù)先準(zhǔn)備好數(shù)據(jù)。雖然不是每次都能從系統(tǒng)的層面來考慮需求，可一旦能夠?qū)嵤?，將?huì)對(duì)優(yōu)化帶來明顯的效果。

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

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

避免或減少多余的操作：避免排序，遞歸等耗資源的操作，可以加大日志大小，增加日志組，減少日志切換頻率。個(gè)別情況下可關(guān)閉歸檔，關(guān)閉日志。避免表空間頻繁擴(kuò)展，降低定時(shí)掃描頻率，消除多余任務(wù)，批量提交，批量執(zhí)行。重新選擇采樣點(diǎn)和比率，只針對(duì)某些表，索引收集統(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ù)；減少訪問對(duì)象的大小，使用分區(qū)表，進(jìn)行表壓縮；減少邏輯讀，可以增大塊大小，減少全表掃描塊數(shù)，增大db_file_multiblock_read_count增加單次掃描所讀取的塊數(shù)，降低高水位線，降低讀取次數(shù)。

分散資源的操作如下：

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

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

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

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

以上介紹了需求分析及其與數(shù)據(jù)庫相關(guān)的操作和資源使用的情況。基于需求分析的sql優(yōu)化方法，可以有兩個(gè)相反的過程。在拿到需要優(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é)合起來，得到真正的需求。再從最初的需求入手，對(duì)需求從整體架構(gòu)到細(xì)節(jié)進(jìn)行分析，取舍，改造，看需求的達(dá)成有幾種方法，分析它的利弊和適用場景，哪種最好。確定一種需求的實(shí)現(xiàn)，進(jìn)行需求的分解，分成幾步動(dòng)作，每一步根據(jù)分析的結(jié)果采取一個(gè)優(yōu)化的策略方法，選擇數(shù)據(jù)庫采用的操作，估計(jì)耗費(fèi)的成本，最后對(duì)比原來語句的實(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ī)可循。