《首席DBA用SQL洪荒之力，造一把通向數(shù)據(jù)庫(kù)的鑰匙》要點(diǎn)：
本文介紹了首席DBA用SQL洪荒之力，造一把通向數(shù)據(jù)庫(kù)的鑰匙，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

本文根據(jù)DBAplus社群第70期線上分享整理而成

講師介紹

韓鋒宜信技術(shù)研發(fā)中心數(shù)據(jù)庫(kù)架構(gòu)師

• 精通多種關(guān)系型數(shù)據(jù)庫(kù),曾任職于當(dāng)當(dāng)網(wǎng)、TOM在線等公司,曾任多家公司首席DBA、數(shù)據(jù)庫(kù)架構(gòu)師等職,多年一線數(shù)據(jù)庫(kù)架構(gòu)、設(shè)計(jì)、開(kāi)發(fā)經(jīng)驗(yàn).著有《SQL優(yōu)化最佳實(shí)踐》一書.

大家好,很高心今天有機(jī)會(huì)跟大家做這個(gè)分享.其次,感謝社群工作人員,組織保證這個(gè)活動(dòng)得以開(kāi)展.本次分享的題目是《SQL-通向數(shù)據(jù)庫(kù)的一把鑰匙》.希望通過(guò)此次的分享,讓大家對(duì)SQL(這一古老的語(yǔ)言),有一種全新的認(rèn)識(shí).本人技術(shù)能力有限,只是把一些心得跟大家分享,希望能夠與大家共同學(xué)習(xí)、共同提高.本次分享主要包括以下部分：

1、SQL語(yǔ)言的歷史發(fā)展及重要性2、在新環(huán)境下SQL面臨的各種疑問(wèn)3、SQL優(yōu)化法則、執(zhí)行過(guò)程4、SQL優(yōu)化器5、解讀執(zhí)行計(jì)劃6、優(yōu)化案例

一、SQL ：一種熟悉又陌生的編程語(yǔ)言

這里有幾個(gè)關(guān)鍵詞；“熟悉”、“陌生”、“編程語(yǔ)言”.

說(shuō)它“熟悉”,是因?yàn)樗荄BA和廣大開(kāi)發(fā)人員,操作數(shù)據(jù)庫(kù)的主要手段,幾乎每天都在使用.說(shuō)它“陌生”,是很多人只是簡(jiǎn)單的使用它,至于它是怎么工作的?如何才能讓它更高效的工作?卻從來(lái)沒(méi)有考慮過(guò).

這里把SQL歸結(jié)為一種“編程語(yǔ)言”,可能跟很多人對(duì)它的認(rèn)知不同.讓我們看看它的簡(jiǎn)單定義(以下內(nèi)容摘自百度百科)

結(jié)構(gòu)化查詢語(yǔ)言(Structured Query Language),簡(jiǎn)稱SQL,是一種特殊目的的編程語(yǔ)言,是一種數(shù)據(jù)庫(kù)查詢和程序設(shè)計(jì)語(yǔ)言,用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫(kù)系統(tǒng).結(jié)構(gòu)化查詢語(yǔ)言是高級(jí)的非過(guò)程化編程語(yǔ)言,允許用戶在高層數(shù)據(jù)結(jié)構(gòu)上工作.它不要求用戶指定對(duì)數(shù)據(jù)的存放方法,也不需要用戶了解具體的數(shù)據(jù)存放方式,所以具有完全不同底層結(jié)構(gòu)的不同數(shù)據(jù)庫(kù)系統(tǒng), 可以使用相同的結(jié)構(gòu)化查詢語(yǔ)言作為數(shù)據(jù)輸入與管理的接口.結(jié)構(gòu)化查詢語(yǔ)言語(yǔ)句可以嵌套,這使它具有極大的靈活性和強(qiáng)大的功能.

總結(jié)一句話,SQL是一種非過(guò)程化的的編程語(yǔ)言,可通過(guò)它去訪問(wèn)關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng).

從上面兩張圖可以發(fā)現(xiàn),在近二十年來(lái),SQL語(yǔ)言一直穩(wěn)定出現(xiàn)在10～20名左右.它不同于一般的通用編程語(yǔ)言,作為一種功能較為“單一”的語(yǔ)言,能長(zhǎng)期保持這樣的排名,實(shí)屬不易.這也印證了SQL語(yǔ)言的廣泛流行性.

二、你真的了解“SQL”嗎?

下面我會(huì)通過(guò)一個(gè)小例子,看看大家是否真正了解SQL.

這是一個(gè)很簡(jiǎn)單的示例,是關(guān)于SQL語(yǔ)句執(zhí)行順序的.這里將一個(gè)普通的SELECT語(yǔ)句,拆分為三個(gè)子句.那么在實(shí)際的執(zhí)行過(guò)程中,是按照什么順序處理的呢?這里有A-F六個(gè)選項(xiàng),大家可以思考選擇一下…

最終的答案是D,即按照先執(zhí)行FROM子句,然后WHERE子句,最后是SELECT部分.

針對(duì)上面的示例,讓我們真實(shí)構(gòu)造一個(gè)場(chǎng)景,通過(guò)查看執(zhí)行計(jì)劃看看是否按照我們選擇的順序執(zhí)行的.關(guān)于執(zhí)行計(jì)劃的判讀,我后面會(huì)專門談到.這里我先解釋一下整個(gè)執(zhí)行過(guò)程.

• 第一步,是按照全表掃描的方式訪問(wèn)了對(duì)象表(EMP).對(duì)應(yīng)于語(yǔ)句中的FROM部分.
• 第二步,是對(duì)提取出的結(jié)果集進(jìn)行了過(guò)濾(filter部分),即將滿足條件的記錄篩選出來(lái).對(duì)應(yīng)于語(yǔ)句中的WHERE部分.
• 第三步,是對(duì)滿足條件的記錄進(jìn)行字段投射,即將需要顯示的字段提取出來(lái).對(duì)應(yīng)于語(yǔ)句中的SELECT部分.

這是一個(gè)詳細(xì)的SQL各部分執(zhí)行順序的說(shuō)明.

通過(guò)對(duì)執(zhí)行順序的理解,可以為我們未來(lái)的優(yōu)化工作帶來(lái)很大幫助.一個(gè)很淺顯的認(rèn)識(shí)就是,優(yōu)化動(dòng)作越靠前越好.

三、SQL現(xiàn)在是否仍然重要?

這里引入了一個(gè)新的問(wèn)題,在現(xiàn)有階段SQL語(yǔ)言是否還重要?

之所以引入這一話題,是因?yàn)殡S著NOSQL、NEWSQL、BIGDATA等技術(shù)逐步成熟推廣,“SQL語(yǔ)言在現(xiàn)階段已經(jīng)變得不那么重要”成為一些人的觀點(diǎn).那實(shí)際情況又是如何呢?

讓我們先來(lái)看一張經(jīng)典的圖.圖中描述了傳統(tǒng)SMP架構(gòu)的關(guān)系型數(shù)據(jù)庫(kù)、MPP架構(gòu)的NEWSQL、MPP架構(gòu)的NoSQL不同方案的適用場(chǎng)景對(duì)比.

從上面的“數(shù)據(jù)價(jià)值密度、實(shí)時(shí)性”來(lái)看,傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)適合于價(jià)值密度更高、實(shí)時(shí)性要求更高的場(chǎng)景(這也就不難理解類似賬戶、金額類信息都是保存在傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)中)；MPP架構(gòu)的NewSQL次之,MPP架構(gòu)的NoSQL更適合于低價(jià)值、實(shí)時(shí)性要求不高的場(chǎng)景.

從下面的“數(shù)據(jù)規(guī)模”來(lái)看,傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)適合保存的大小限制在TB級(jí)別,而后兩者可在更大尺度上(PB、EB)級(jí)保存數(shù)據(jù).

從下面的“典型場(chǎng)景”來(lái)看,傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)適合于OLTP在線交易系統(tǒng)；MPP架構(gòu)的NewSQL適合于OLAP在線分析系統(tǒng)；而NoSQL的使用場(chǎng)景較多(利于KV型需求、數(shù)據(jù)挖掘等均可以考慮).

最后從“數(shù)據(jù)特征”來(lái)看,前兩者適合于保存結(jié)構(gòu)化數(shù)據(jù),后者更適合于半結(jié)構(gòu)化、乃至非結(jié)構(gòu)化數(shù)據(jù)的保存.

歸納一下,不同技術(shù)有其各自特點(diǎn),不存在誰(shuí)代替誰(shuí)的問(wèn)題.傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)有其自身鮮明特點(diǎn),在某些場(chǎng)合依然是不二選擇.而作為其主要交互語(yǔ)言,SQL必然長(zhǎng)期存在發(fā)展下去.

我們?cè)賮?lái)對(duì)比一下傳統(tǒng)數(shù)據(jù)庫(kù)與大數(shù)據(jù)技術(shù).從數(shù)據(jù)量、增長(zhǎng)型、多樣化、價(jià)值等維度對(duì)比兩種技術(shù),各自有其適用場(chǎng)景.

對(duì)于大數(shù)據(jù)領(lǐng)域而言,各種技術(shù)層出不窮.但對(duì)于廣大使用者來(lái)說(shuō),往往會(huì)存在一定的使用門檻,因此現(xiàn)在的一種趨勢(shì)就是在大數(shù)據(jù)領(lǐng)域也引入“類SQL”,以類似SQL的方式訪問(wèn)數(shù)據(jù).這對(duì)于廣大使用者來(lái)說(shuō),無(wú)疑大大降低了使用門檻.

解答一些疑問(wèn)：

1. NoSQL、NewSQL已經(jīng)超越了傳統(tǒng)數(shù)據(jù)庫(kù),SQL沒(méi)有了用武之地!各種技術(shù)有著各自適合的不同場(chǎng)景,不能一概而論.SQL語(yǔ)言作為關(guān)系型數(shù)據(jù)庫(kù)的主要訪問(wèn)方式,依然有其用武之地.
2. 以后都是云時(shí)代了,誰(shuí)還用關(guān)系型數(shù)據(jù)庫(kù)!對(duì)于價(jià)值密度高,嚴(yán)格一致性的場(chǎng)景,仍然適合采用關(guān)系型數(shù)據(jù)庫(kù)作為解決方案.
3. 我編程都是用OR Mapping工具,從不需要寫SQL!的確,引入OR Mapping工具大大提高了生產(chǎn)效率,但是它的副作用也很明顯,那就是對(duì)語(yǔ)句的運(yùn)行效率失去了控制.很多低效的語(yǔ)句,往往是通過(guò)工具直接生成的.這也是為什么有的Mapping工具還提供了原始的SQL接口,用來(lái)保證關(guān)鍵語(yǔ)句的執(zhí)行效率.
4. 大數(shù)據(jù)時(shí)代,我們都用Hadoop、Spark了,不用寫SQL啦!無(wú)論是使用Hadoop、Spark都是可以通過(guò)編寫程序完成數(shù)據(jù)分析的,但其生產(chǎn)效率往往很低.這也是為什么產(chǎn)生了Hive 、Spark SQL等“類SQL”的解決方案來(lái)提高生產(chǎn)效率.
5. 數(shù)據(jù)庫(kù)處理能力很強(qiáng),不用太在意SQL性能!的確,隨著多核CPU、大內(nèi)存、閃存等硬件技術(shù)的發(fā)展,數(shù)據(jù)庫(kù)的處理能力較以前有了很大的增強(qiáng).但是SQL的性能依然很重要.后面我們可以看到,一個(gè)簡(jiǎn)單SQL語(yǔ)句就可以輕易地搞垮一個(gè)數(shù)據(jù)庫(kù).
6. SQL優(yōu)化,找DBA就行了,我就不用學(xué)了!SQL優(yōu)化是DBA的職責(zé)范疇,但對(duì)于開(kāi)發(fā)人員來(lái)講,更應(yīng)該對(duì)自己的代碼負(fù)責(zé).如果能在開(kāi)發(fā)階段就注重SQL質(zhì)量,會(huì)避免很多低級(jí)問(wèn)題.
7. 我只是個(gè)運(yùn)維DBA,SQL優(yōu)化我不行!DBA的發(fā)展可分為“運(yùn)維DBA->開(kāi)發(fā)DBA->數(shù)據(jù)架構(gòu)師…”.如果只能完成數(shù)據(jù)庫(kù)的運(yùn)維類工作,無(wú)疑是技能的欠缺,也是對(duì)各人未來(lái)發(fā)展不利.況且,隨著Paas云的逐步推廣,對(duì)于數(shù)據(jù)庫(kù)的運(yùn)維需求越來(lái)越少,對(duì)于優(yōu)化、設(shè)計(jì)、架構(gòu)的要求越來(lái)越多.因此,SQL優(yōu)化是每個(gè)DBA必須掌握的技能.
8. 現(xiàn)在優(yōu)化有工具了,很簡(jiǎn)單的!的確現(xiàn)在有些工具可以為我們減少些優(yōu)化分析工作,會(huì)自動(dòng)給出一些優(yōu)化建議.但是,作為DBA來(lái)講,不僅要知其然,還要知其所以然.況且,數(shù)據(jù)庫(kù)優(yōu)化器本身就是一個(gè)非常復(fù)雜的組件,很難做到完全無(wú)誤的優(yōu)化,這就需要人工的介入,分析.
9. 優(yōu)化不就是加索引嘛,這有啥!的確,加索引是一個(gè)非常常用的優(yōu)化手段,但其不是唯一的.且很多情況下,加了索引可能導(dǎo)致性能更差.后面,會(huì)有一個(gè)案例說(shuō)明.

四、SQL仍然很重要!

我們通過(guò)一個(gè)示例,說(shuō)明一下理解SQL運(yùn)行原理仍然很重要.

這是我在生產(chǎn)環(huán)境碰到的一個(gè)真實(shí)案例.Oracle數(shù)據(jù)庫(kù)環(huán)境,兩個(gè)表做關(guān)聯(lián).執(zhí)行計(jì)劃觸目驚心,優(yōu)化器評(píng)估返回的數(shù)據(jù)量為3505T條記錄,計(jì)劃返回量127P字節(jié),總成本9890G,返回時(shí)間999:59:59.

從執(zhí)行計(jì)劃中可見(jiàn),兩表關(guān)聯(lián)使用了笛卡爾積的關(guān)聯(lián)方式.我們知道笛卡爾連接是指在兩表連接沒(méi)有任何連接條件的情況.一般情況下應(yīng)盡量避免笛卡爾積,除非某些特殊場(chǎng)合.否則再?gòu)?qiáng)大的數(shù)據(jù)庫(kù),也無(wú)法處理.這是一個(gè)典型的多表關(guān)聯(lián)缺乏連接條件,導(dǎo)致笛卡爾積,引發(fā)性能問(wèn)題的案例.

從案例本身來(lái)講,并沒(méi)有什么特別之處,不過(guò)是開(kāi)發(fā)人員疏忽,導(dǎo)致了一條質(zhì)量很差的SQL.但從更深層次來(lái)講,這個(gè)案例可以給我們帶來(lái)如下啟示：

1. 開(kāi)發(fā)人員的一個(gè)疏忽,造成了嚴(yán)重的后果,原來(lái)數(shù)據(jù)庫(kù)竟是如此的脆弱.需要對(duì)數(shù)據(jù)庫(kù)保持一種”敬畏”之心.
2. 電腦不是人腦,它不知道你的需求是什么,只能用寫好的邏輯進(jìn)行處理.
3. 不要去責(zé)怪開(kāi)發(fā)人員,誰(shuí)都會(huì)犯錯(cuò)誤,關(guān)鍵是如何從制度上保證不再發(fā)生類似的問(wèn)題.

五、SQL優(yōu)化法則

下面我們來(lái)看看常見(jiàn)的優(yōu)化法則.這里所說(shuō)的優(yōu)化法則,其實(shí)是指可以從那些角度去考慮SQL優(yōu)化的問(wèn)題.可以有很多種方式去看待它.下面列舉一二.

這里來(lái)自阿里-葉正盛的一篇博客里的一張圖,相信很多人都看過(guò).這里提出了經(jīng)典的漏斗優(yōu)化法則,高度是指我們投入的資源,寬度是指可能實(shí)現(xiàn)的收益.從圖中可見(jiàn),“減少數(shù)據(jù)訪問(wèn)”是投入資源最少,而收益較多的方式；“增加硬件資源”是相對(duì)投入資源最多,而收益較少的一種方式.受時(shí)間所限,這里不展開(kāi)說(shuō)明了.

這是我總結(jié)的一個(gè)優(yōu)化法則,簡(jiǎn)稱為“DoDo”法則.

• 第一條,“Do Less or not do!”翻譯過(guò)來(lái),就是盡量讓數(shù)據(jù)庫(kù)少做工作、甚至不做工作.

怎么樣來(lái)理解少做工作呢?比如創(chuàng)建索引往往可以提高訪問(wèn)效率,其原理就是將原來(lái)的表掃描轉(zhuǎn)換為索引掃描,通過(guò)一個(gè)有序的結(jié)構(gòu),只需要少量的IO訪問(wèn)就可以得到相應(yīng)的數(shù)據(jù),因此效率才比較高.這就可以歸納為少做工作.

怎么樣來(lái)理解不做工作呢?比如在系統(tǒng)設(shè)計(jì)中常見(jiàn)的緩存設(shè)計(jì),很多是將原來(lái)需要訪問(wèn)數(shù)據(jù)庫(kù)的情況,改為訪問(wèn)緩存即可.這樣既提高了訪問(wèn)效率,又減少了數(shù)據(jù)庫(kù)的壓力.從數(shù)據(jù)庫(kù)角度來(lái)說(shuō),這就是典型的不做工作.

• 第二條,“If must do,do it fast!”翻譯過(guò)來(lái),如果數(shù)據(jù)庫(kù)必須做這件事件,那么請(qǐng)盡快做完它.

怎么樣來(lái)理解這句話呢?比如數(shù)據(jù)庫(kù)里常見(jiàn)的并行操作,就是通過(guò)引入多進(jìn)程來(lái)加速原來(lái)的執(zhí)行過(guò)程.加速處理過(guò)程,可以少占用相關(guān)資源,提高系統(tǒng)整體吞吐量.

六、SQL 執(zhí)行過(guò)程

SQL的執(zhí)行過(guò)程比較復(fù)雜,不同數(shù)據(jù)庫(kù)有一定差異.下面介紹以兩種主流的數(shù)據(jù)庫(kù)(Oracle、MySQL)介紹一下.

1. 用戶提交了一條SQL語(yǔ)句
2. 數(shù)據(jù)庫(kù)按照SQL語(yǔ)句的字面值計(jì)算出一個(gè)HASH值
3. 根據(jù)HASH值,判斷一下在數(shù)據(jù)庫(kù)緩沖區(qū)中是否存在此SQL的執(zhí)行計(jì)劃.
4. 如果不存在,則需要生成一個(gè)執(zhí)行計(jì)劃(硬解析過(guò)程),然后將結(jié)果存入緩沖區(qū).
5. 如果存在的話,判斷是否為相同SQL(同樣HASH值的語(yǔ)句,可能字符不相同；即使完全相同,也可能代表不同的語(yǔ)句.這塊不展開(kāi)說(shuō)了)
6. 確認(rèn)是同一條SQL語(yǔ)句,則從緩沖區(qū)中取出執(zhí)行計(jì)劃.
7. 將執(zhí)行計(jì)劃,交給執(zhí)行器執(zhí)行.
8. 結(jié)果返回給客戶端.

1. 客戶提交一條語(yǔ)句
2. 現(xiàn)在查詢緩存查看是否存在對(duì)應(yīng)的緩存數(shù)據(jù),如有則直接返回(一般有的可能性極小,因此一般建議關(guān)閉查詢緩存).
3. 交給解析器處理,解析器會(huì)將提交的語(yǔ)句生成一個(gè)解析樹(shù).
4. 預(yù)處理器會(huì)處理解析樹(shù),形成新的解析樹(shù).這一階段存在一些SQL改寫的過(guò)程.
5. 改寫后的解析樹(shù)提交給查詢優(yōu)化器.查詢優(yōu)化器生成執(zhí)行計(jì)劃.
6. 執(zhí)行計(jì)劃交由執(zhí)行引擎調(diào)用存儲(chǔ)引擎接口,完成執(zhí)行過(guò)程.這里要注意,MySQL的Server層和Engine層是分離的.
7. 最終的結(jié)果有執(zhí)行引擎返回給客戶端,如果開(kāi)啟查詢緩存的話,則會(huì)緩存.

七、SQL優(yōu)化器

在上面的執(zhí)行過(guò)程描述中,多次提高了優(yōu)化器.它也是數(shù)據(jù)庫(kù)中最核心的組件.下面我們來(lái)介紹一下優(yōu)化器.

上面是我對(duì)優(yōu)化器的一些認(rèn)識(shí).優(yōu)化器是數(shù)據(jù)庫(kù)的精華所在,值得DBA去認(rèn)真研究.但是遺憾的是,數(shù)據(jù)庫(kù)對(duì)這方面的開(kāi)放程度并不夠.(相對(duì)來(lái)說(shuō),Oracle還是做的不錯(cuò)的)

這里我們看到的MySQL的優(yōu)化器的工作過(guò)程,大致經(jīng)歷了如下處理：

1. 詞法分析、語(yǔ)法分析、語(yǔ)義檢查
2. 預(yù)處理階段(查詢改寫等)
3. 查詢優(yōu)化階段(可詳細(xì)劃分為邏輯優(yōu)化、物理優(yōu)化兩部分)
4. 查詢優(yōu)化器優(yōu)化依據(jù),來(lái)自于代價(jià)估算器估算結(jié)果(它會(huì)調(diào)用統(tǒng)計(jì)信息作為計(jì)算依據(jù))
5. 交由執(zhí)行器執(zhí)行

此圖是DBAplus社群MySQL原創(chuàng)專家李海翔對(duì)比不同數(shù)據(jù)庫(kù)優(yōu)化器技術(shù)所總結(jié)的.從這里可以看出：

1. 不同數(shù)據(jù)庫(kù)的實(shí)現(xiàn)層次不同,有些支持、有些不支持
2. 即使支持,其實(shí)現(xiàn)原理也差異很大
3. 這只是列出了一小部分優(yōu)化技術(shù)
4. 以上對(duì)比,也可以解釋不同數(shù)據(jù)庫(kù)對(duì)同樣語(yǔ)句的行為不同.下面會(huì)有一個(gè)示例說(shuō)明

八、SQL 執(zhí)行計(jì)劃

看懂執(zhí)行計(jì)劃是DBA優(yōu)化的前提之一,它為我們開(kāi)啟一扇通往數(shù)據(jù)庫(kù)內(nèi)部的窗口.但是很遺憾,從沒(méi)有一本書叫做“如何看懂執(zhí)行計(jì)劃”,這里的情況非常復(fù)雜,很多是需要DBA常年積累而成.

這是Oracle執(zhí)行計(jì)劃簡(jiǎn)單的示例,說(shuō)明了執(zhí)行計(jì)劃的大致內(nèi)容.

九、幾個(gè)案例分享

前面講了很多理論內(nèi)容,下面通過(guò)幾個(gè)案例說(shuō)明一下.方便大家對(duì)前面內(nèi)容的理解.

• 案例1：數(shù)據(jù)庫(kù)對(duì)比

第一個(gè)例子,是一個(gè)優(yōu)化器行為的對(duì)比案例.示例對(duì)比了三種數(shù)據(jù)庫(kù)(四種版本)對(duì)于同樣語(yǔ)句的行為.通過(guò)這個(gè)例子,大家可以了解,不同數(shù)據(jù)庫(kù)(乃至不同版本)優(yōu)化器的行為不同.對(duì)于數(shù)據(jù)庫(kù)選型、數(shù)據(jù)庫(kù)升級(jí)等工作,要做到充分的評(píng)估測(cè)試,也正是出于此目的.

簡(jiǎn)單構(gòu)造了兩張測(cè)試表,主要注意的是前一個(gè)字段是包含空值的.

第一種情況,是對(duì)于IN子查詢的處理.對(duì)于Oracle來(lái)說(shuō),10g、11g行為相同,這里就列了一個(gè).

對(duì)于這樣的一個(gè)例子,不同數(shù)據(jù)庫(kù)已經(jīng)表現(xiàn)出不同的差異.Oracle和PG的行為類似,MySQL由于不支持哈希連接,因此采用了其他處理方式.具體的技術(shù)細(xì)節(jié),這里不展開(kāi)說(shuō)明了.

第二種情況,是對(duì)于NOT IN子查詢的處理.這種情況下,Oracle的不同版本、PG和MySQL表現(xiàn)出不同的行為.從上面例子可以看出,11g的優(yōu)化器在處理此種情況是更加智能一些.

• 案例2：解決“ERP匯單慢”問(wèn)題

這里我構(gòu)造了類似的結(jié)構(gòu),模擬了上線的情況.

示例是一個(gè)關(guān)聯(lián)子查詢,其核心部分是轉(zhuǎn)化為一個(gè)表關(guān)聯(lián),并使用了嵌套循環(huán)的一個(gè)變體-Filter實(shí)現(xiàn)關(guān)聯(lián)方式.顯然,如果外層表過(guò)大或內(nèi)層探查效率過(guò)低,其執(zhí)行效率可想而知.通常來(lái)說(shuō),兩表關(guān)聯(lián),嵌套循環(huán)是最后的一種選擇,如果能使用其他方式(例如HASH JOIN、SORT MERGE)可能會(huì)帶來(lái)更好的效果.

這里優(yōu)化器沒(méi)有選擇更優(yōu)的計(jì)劃,是優(yōu)化器的Bug?還是功能所限?可通過(guò)人工手段干預(yù),看看是否能達(dá)到意向不到的效果.

引入了一個(gè)Hint-unnest,主動(dòng)實(shí)現(xiàn)子查詢的解嵌套.將子查詢部分提前,讓優(yōu)化器有了更多的選擇.從執(zhí)行計(jì)劃來(lái)看,優(yōu)化器生成了一個(gè)內(nèi)聯(lián)視圖,然后跟外部表實(shí)現(xiàn)了一個(gè)哈希連接,整體效率大大提高.

這個(gè)示例說(shuō)明,優(yōu)化器的功能還是有所局限.在某些場(chǎng)合,可以人工干預(yù)語(yǔ)句的執(zhí)行,提升整體執(zhí)行效率.

• 案例3：處理“ERP清理數(shù)據(jù)”問(wèn)題

下面這個(gè)示例,是因?yàn)榻Y(jié)構(gòu)設(shè)計(jì)不良導(dǎo)致的問(wèn)題.

在日常的優(yōu)化中,我們往往遵循著“語(yǔ)句級(jí)、對(duì)象級(jí)、架構(gòu)級(jí)、業(yè)務(wù)級(jí)”的順序考慮優(yōu)化策略.但在項(xiàng)目需求、設(shè)計(jì)階段,是按照反向的順序進(jìn)行.后者的影響力要遠(yuǎn)遠(yuǎn)大于前者.一個(gè)糟糕的對(duì)象結(jié)構(gòu)設(shè)計(jì),可能會(huì)帶來(lái)一系列SQL的問(wèn)題.示例中,就是這樣的一個(gè)問(wèn)題.

這是某公司后臺(tái)的ERP系統(tǒng),系統(tǒng)已經(jīng)上線運(yùn)行了10多年.隨著時(shí)間的推移,累積的數(shù)據(jù)量越來(lái)越大.公司計(jì)劃針對(duì)部分大表進(jìn)行數(shù)據(jù)清理.在DBA對(duì)某個(gè)大表進(jìn)行清理中,出現(xiàn)了問(wèn)題.這個(gè)表本身有數(shù)百G,按照指定的清理規(guī)則只需要根據(jù)主鍵字段范圍(>=)選擇出一定比例(不超過(guò)10%)的數(shù)據(jù)進(jìn)行清理即可.但在實(shí)際使用中發(fā)現(xiàn),該SQL的是全表掃描,執(zhí)行時(shí)間大大超出預(yù)期時(shí)間.DBA嘗試使用強(qiáng)制指定索引方式清理數(shù)據(jù),依然無(wú)效.

這套ERP系統(tǒng)歷史很久遠(yuǎn),相關(guān)信息已經(jīng)找不到了.只能從純數(shù)據(jù)庫(kù)的角度進(jìn)行分析,這是一個(gè)普通表(非分區(qū)表)按照主鍵字段的范圍查詢一批記錄進(jìn)行清理.按照正常理解,執(zhí)行索引范圍掃描應(yīng)該是效率較高的一種處理方式,但實(shí)際情況確實(shí)全表掃描.進(jìn)一步分析發(fā)現(xiàn),該表的主鍵是沒(méi)有業(yè)務(wù)含義的,僅僅是自增長(zhǎng)的數(shù)據(jù),其來(lái)源是一個(gè)序列.但奇怪的是,這個(gè)主鍵字段的類型是變長(zhǎng)文本類型,而不是通常的數(shù)字類型.現(xiàn)在已經(jīng)無(wú)從考證,當(dāng)初定義該字段類型的依據(jù),但實(shí)驗(yàn)表明正是這個(gè)字段的類型“異常”,導(dǎo)致了錯(cuò)誤的執(zhí)行路徑.

下面構(gòu)造了一個(gè)測(cè)試環(huán)境.

可以很好的復(fù)現(xiàn)案例的問(wèn)題.選擇少范圍數(shù)據(jù),文本方式依然走的全表掃描,數(shù)字方式走的索引掃描.效率高低,顯而易見(jiàn).

大家頭腦中可以構(gòu)想出一棵索引樹(shù)結(jié)構(gòu),對(duì)于字符串來(lái)說(shuō),這個(gè)有序的結(jié)構(gòu)該如何存放?是與你預(yù)期一樣的嗎?

知道了問(wèn)題所在,該如何處理呢?修改結(jié)構(gòu)無(wú)疑成本太高,不具備可操作性.這里所采取的策略是“局部有序”.利用修改語(yǔ)句中條件的范圍,由開(kāi)放區(qū)間變?yōu)榉忾]區(qū)間,影響基數(shù)的選擇.(關(guān)于這部分,大家有興趣可多看看《基于成本的Oracle優(yōu)化》一書)

如仍然不起作用,可考慮進(jìn)一步細(xì)化分段或干脆采用“逐條提取+批綁定”的方式解決.

一個(gè)小小的數(shù)據(jù)類型設(shè)置不當(dāng),會(huì)為我們后面的工作帶來(lái)的多大的麻煩.

• 案例4： “抽絲剝繭”找出問(wèn)題所在

這里會(huì)描述一次完整的優(yōu)化過(guò)程,看看DBA是如何“抽絲剝繭”,發(fā)現(xiàn)問(wèn)題本質(zhì)的.

這個(gè)案例本身不是為了說(shuō)明某種技術(shù),而是展現(xiàn)了DBA在分析處理問(wèn)題時(shí)的一種處理方式.其采用的方法往往是根據(jù)自己掌握的知識(shí),分析判斷某種可能性,然后再驗(yàn)證確認(rèn)是否是這個(gè)原因.在不斷的拋出疑問(wèn),不斷的驗(yàn)證糾錯(cuò)中,逐步接近問(wèn)題的本質(zhì).

也想通過(guò)這個(gè)示例,告知廣大開(kāi)發(fā)人員,DBA優(yōu)化語(yǔ)句的不容易.

這是某數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng),有一個(gè)作業(yè)在某天出現(xiàn)較大延遲.為了不影響明天的業(yè)務(wù)系統(tǒng),必須在今天解決這個(gè)問(wèn)題.經(jīng)和開(kāi)發(fā)人員的溝通,該業(yè)務(wù)的SQL語(yǔ)句沒(méi)有修改,相關(guān)的數(shù)據(jù)結(jié)構(gòu)也沒(méi)有變更相類似的其他業(yè)務(wù)(SQL語(yǔ)句相似的)也都正常運(yùn)行,數(shù)據(jù)庫(kù)系統(tǒng)本身也沒(méi)有異常.

修改后執(zhí)行計(jì)劃,跟其他類似SQL相同了.整個(gè)計(jì)劃可概述為”HASH JOIN” + “FULL TABLE SCAN”.經(jīng)測(cè)試,速度略有提升,但是整個(gè)運(yùn)行時(shí)間仍然超過(guò)2個(gè)小時(shí).

開(kāi)始了第一次嘗試,開(kāi)始想到的方法很簡(jiǎn)單,既然類似的SQL執(zhí)行效率沒(méi)問(wèn)題,而這個(gè)SQL由于其他SQL執(zhí)行計(jì)劃偏差較大,我可以手工采取固化執(zhí)行計(jì)劃的方法.這里使用了抽取OUTLINE的方式.經(jīng)測(cè)試,對(duì)速度提升不大,不知問(wèn)題主因.

第二次嘗試,從等待事件角度入手.首先考慮的是和緩存有關(guān)的問(wèn)題.

不要盲目相信別人的話,優(yōu)化之前先按照故有流程檢驗(yàn)一遍,做到心中有數(shù).對(duì)于此例來(lái)說(shuō),可以大大加快問(wèn)題的解決.

Q&A

Q1：ANSI 的SQL標(biāo)準(zhǔn),會(huì)一直推出新版本嗎? 后續(xù)版本是否會(huì)加入新的語(yǔ)法和特性呢?

A1：這個(gè)問(wèn)題沒(méi)有仔細(xì)考慮過(guò),ANSI-SQL的標(biāo)準(zhǔn)一直在變化,不同的數(shù)據(jù)庫(kù)根據(jù)自身情況實(shí)現(xiàn)了它的子集.從我個(gè)人角度來(lái)看,未來(lái)ANSI-SQL可能會(huì)對(duì)大數(shù)據(jù)、數(shù)據(jù)挖掘方向有所考慮,加入部分新語(yǔ)法或特性.畢竟SQL接口作為人們最為熟悉的數(shù)據(jù)訪問(wèn)接口,未來(lái)在大數(shù)據(jù)等方向大有可為.

 

Q2：優(yōu)化SQL最終的目的是不是改變SQL執(zhí)行計(jì)劃?

A2：第一目的,是理解現(xiàn)有優(yōu)化器選擇的行為,并考慮是否是最佳選擇.第二目的,是在優(yōu)化器功能有所局限的情況下,通過(guò)人工介入的方式,讓數(shù)據(jù)庫(kù)以更優(yōu)的方式執(zhí)行SQL.畢竟人要比電腦更理解數(shù)據(jù).

 

Q3：能不能介紹一下開(kāi)發(fā)中,數(shù)據(jù)類型的選擇對(duì)數(shù)據(jù)庫(kù)的影響?

A3：數(shù)據(jù)類型在優(yōu)化層面,主要可從以下角度考慮：

1. 選擇“合適”的類型存儲(chǔ)數(shù)據(jù).注意,這里使用的詞是“合適”,要確保精度、夠用、不浪費(fèi)的原則.
2. 數(shù)據(jù)類型在數(shù)據(jù)庫(kù)自身存儲(chǔ)、計(jì)算上的特性,不同類型的效率是不同的.
3. 類型間要做到兼容,保證關(guān)聯(lián)字段的類型一致性.

 

Q4：能不能介紹下oracle數(shù)據(jù)遷移的常用方式和利弊?

A4：這個(gè)有很多,取決于遷移的需求,比如常用的：

1.備份、恢復(fù)；2.邏輯導(dǎo)入、導(dǎo)出(含傳輸表空間等)；3.DATAGUARD；4.LOG SYNC(例如OGG等)；5.程序同步……利弊,主要取決于成本、代價(jià)了,每種方案都有自身的適用場(chǎng)景.

 

Q5：請(qǐng)問(wèn)必須全表掃描的語(yǔ)句有什么優(yōu)化思路?

A5：必須用全表掃描的情況,就適用于分享中的“DoDo”原則第二條,盡量讓其更快的完成.可考慮的策略有：

1. DIRECT PATH READ
2. 加大MULTI BLOCK READ COUNT
3. 啟用PARALLEL
4. 更好的IO

Q6：對(duì)于group by語(yǔ)句如何優(yōu)化?

A6：對(duì)于分組來(lái)說(shuō),Oracle 11g以后的版本提供了HASH GROUP BY的實(shí)現(xiàn).HASH是個(gè)重內(nèi)存消耗操作,可從內(nèi)存使用角度基于優(yōu)化考慮.

 

Q7：訪問(wèn)路徑是會(huì)緩存起來(lái)的,怎么判斷回收沒(méi)用的緩存中的訪問(wèn)路徑呢?

A7：一般不需要考慮回收問(wèn)題,如果非要做可從內(nèi)存信息中了解此執(zhí)行計(jì)劃是否最近被使用,使用DBMS包清除即可.

?

Q8：oracle發(fā)現(xiàn)在云機(jī)上安裝之后,在并發(fā)性方面不行,這是為什么?

A8：不同云的實(shí)現(xiàn)策略不同.并發(fā)性方面,可考慮從vCPU使用、IO等方面著手.這方面經(jīng)驗(yàn)不多,抱歉!

 

Q9：全表掃描想辦法修改為索引全表掃描是否合適?使用with子句來(lái)優(yōu)化sql,這個(gè)手段如何?

A9：將全表掃描修改為索引全掃描,根本原則是能夠縮小訪問(wèn)量,即讓數(shù)據(jù)庫(kù)干更少的活.

WITH子句,定義查詢塊,一個(gè)目的是減少多次引用,但也有可能出現(xiàn)不允許執(zhí)行查詢語(yǔ)句變形的情況,要具體分情況分析.

文章出處：DBAplus社群

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/4452.html