因?yàn)镋SR, 我一定要推薦你這款 SQL 神器
點(diǎn)擊藍(lán)色“有關(guān)SQL”關(guān)注我喲
加個(gè)“星標(biāo)”，天天與8000人一起快樂成長(zhǎng)
圖 | 榖依米
經(jīng)常看我們【有關(guān)SQL】公眾號(hào)的讀者，對(duì)SQL執(zhí)行的理解，一定與別人不一樣。
別人看到一條SQL，腦子里除了從上到下執(zhí)行，就不會(huì)有別的想法。但是我們的讀者肯定不是這樣。比如以下這條簡(jiǎn)單的不能再簡(jiǎn)單的SQL，聚合求和：
select?flowid,?
???????sum(flowamount)?as?amount?
?from?workflow?
?group?by?flowid?
?order?by?2?desc
不看我們【有關(guān)SQL】公眾號(hào)的SQL開發(fā)，就會(huì)認(rèn)為這條語句就是完成以 flowid 為分組依據(jù)的求和。而經(jīng)常看我們公眾號(hào)的讀者，至少心里會(huì)想，workflow 表有沒有建索引啊，索引結(jié)構(gòu)怎么樣，group by 會(huì)用到這個(gè)索引么？
甚至鐵粉讀者還會(huì)想得更深入，沒有任何判斷條件的聚合，會(huì)不會(huì)鎖表呢，為什么不加事務(wù)控制？如果讓我來建這個(gè)索引，我該如何定這個(gè)索引結(jié)構(gòu)，并且讓數(shù)據(jù)庫(kù)優(yōu)化器只選擇我建的這個(gè)索引。
還沒關(guān)注的朋友，右上點(diǎn)擊關(guān)注，再來看文。
趁著這篇文章，我再稍微提一下之前文章的精華，SQL執(zhí)行步驟：
一條SQL語句達(dá)到數(shù)據(jù)庫(kù)后，并不馬上執(zhí)行，中間會(huì)穿插很多細(xì)小的步驟。
第一步，SQL語句會(huì)被編譯，生成語法樹，比如上面的語句，編譯成語法樹，就是從表讀取數(shù)據(jù)，按列做聚合，此時(shí)不會(huì)執(zhí)行SQL；
第二步，將表和列，綁定到對(duì)應(yīng)的語法樹對(duì)象上，若發(fā)現(xiàn)沒有對(duì)應(yīng)表，則報(bào)一個(gè)大家都不愿意看到的錯(cuò)“找不到對(duì)象”；
第三步，優(yōu)化器生成最快的執(zhí)行計(jì)劃，在這一步，很多不可見的神秘操作，就會(huì)在簡(jiǎn)單的SQL表象下，執(zhí)行。而大多數(shù)的開發(fā)人員，是看不見的；
第四步，執(zhí)行優(yōu)化器生成的最快的執(zhí)行計(jì)劃，返回結(jié)果。
數(shù)據(jù)庫(kù)這個(gè)行業(yè)如果說有門檻，那么優(yōu)化這塊絕對(duì)占據(jù)第一位
今天，我又要告訴各位一個(gè)非常有用的知識(shí)點(diǎn)，敲黑板…三次！
在Query Optimization階段，優(yōu)化器并不會(huì)生成所有可能的執(zhí)行計(jì)劃
可能很多開發(fā)的朋友都會(huì)發(fā)懵，所有可能的執(zhí)行計(jì)劃是什么意思，執(zhí)行計(jì)劃還不止一條？
對(duì)，沒錯(cuò)！雖然被執(zhí)行的執(zhí)行計(jì)劃只有一條，但優(yōu)化階段，產(chǎn)生的執(zhí)行計(jì)劃并不止一條，優(yōu)化器只是挑了一個(gè)比較低成本（也就是優(yōu)化器認(rèn)為執(zhí)行最快的）那條。
就拿上面的SQL語句來說，如果我們?cè)谶@表上加了 2 個(gè)索引 idx(flowid) 和 idx(flowamount) ，就會(huì)增多執(zhí)行計(jì)劃。比如：
直接訪問全表
訪問索引 idx(flowid)
訪問索引 idx(flowamount)
優(yōu)化器生成的執(zhí)行計(jì)劃可能會(huì)是以下幾種：
select?flowid,?
???????sum(flowamount)?as?amount?
??from?workflow?WITH?(INDEX?(0))?
?group?by?flowid?
?order?by?2?desc
?select?flowid,?
???????sum(flowamount)?as?amount?
??from?workflow?WITH?(INDEX?([idx_amt_id]))?
?group?by?flowid?
?order?by?2?desc
?select?flowid,?
???????sum(flowamount)?as?amount?
??from?workflow?WITH?(INDEX?(0))?
?group?by?flowid?
?order?by?2?desc?
OPTION?(ORDER?GROUP)
當(dāng)然還有其他執(zhí)行計(jì)劃，不一一列舉了。
所以當(dāng)執(zhí)行計(jì)劃越多，優(yōu)化器本身試圖窮盡這些計(jì)劃都非常耗時(shí)，因?yàn)閮?yōu)化器就偷了個(gè)懶，如果碰到某個(gè)執(zhí)行計(jì)劃在合理的時(shí)間內(nèi)，就不再往下分析。也就是優(yōu)化器也在將就著過日子，經(jīng)過分析找到一個(gè)比較快速的執(zhí)行計(jì)劃就急于丟給查詢引擎做執(zhí)行，而不是嘗試去遍歷所有可能的執(zhí)行計(jì)劃，再挑一個(gè)真正最優(yōu)的。
此時(shí)，給了優(yōu)化工程師發(fā)揮的余地，如果要追求極致的性能，這些工程師就要替優(yōu)化器去完成尋找最優(yōu)的執(zhí)行計(jì)劃。那么多少工程師能比計(jì)算機(jī)快呢，我想沒有吧。那么快就一定好嗎，那不一定。SQL優(yōu)化大師，憑借自己的經(jīng)驗(yàn)，一眼看到某個(gè)索引能顯著提升性能，而正由于優(yōu)化器的偷懶，恰好沒用到，于是他就給優(yōu)化器一個(gè)提示，這里可以用這個(gè)索引去提高性能。
所以，大部分不夠優(yōu)化的SQL，歸根結(jié)底，是由于兩類原因造成的：
要么沒有足夠的改造空間( plan space)，要么SQL寫死了
缺少足夠多的統(tǒng)計(jì)信息，使得判斷執(zhí)行成本錯(cuò)誤
在歷史的促進(jìn)下，現(xiàn)在的優(yōu)化器面臨更多的挑戰(zhàn)，比如 OLAP，分布式事務(wù)，還有并行執(zhí)行。這么多的優(yōu)化需要在一個(gè)優(yōu)化器中實(shí)現(xiàn)，難度空前增加。
所以要求優(yōu)化器做到極致，既要實(shí)時(shí)，還要精準(zhǔn)，是不現(xiàn)實(shí)的。我們必須發(fā)明一種程序，讓它可以替代優(yōu)化器做些事情。比如評(píng)估那些沒有窮盡的執(zhí)行計(jì)劃，在真實(shí)的環(huán)境中測(cè)試每個(gè)計(jì)劃的執(zhí)行時(shí)間，拿到最快的那個(gè)。
那有沒有這樣的神器呢？有，這就是本文的主角，ESR.
ESR：External SQL Rewriter，外部SQL重寫器
ESR 的主要優(yōu)點(diǎn)有 2 個(gè)：
可以無止境的尋找最優(yōu)執(zhí)行計(jì)劃，因?yàn)樗皇菍?shí)時(shí)生產(chǎn)執(zhí)行計(jì)劃的
不依賴統(tǒng)計(jì)信息，不計(jì)算成本，而是把所有的潛在執(zhí)行計(jì)劃都執(zhí)行一遍，太長(zhǎng)的自動(dòng)殺掉，留下短時(shí)間內(nèi)，比如500ms 內(nèi)執(zhí)行完畢的執(zhí)行計(jì)劃
原理就在這里了，真正實(shí)現(xiàn)了 ESR 的工具，介紹一個(gè)：Toad
在 Toad 之前，其實(shí)還有一個(gè) LECCO SQL Expert 的工具，不過停產(chǎn)了。現(xiàn)在 Toad 已經(jīng)集成了 ESR 工具，現(xiàn)在帶大家看看：
最底下的那個(gè)窗格，羅列了所有可以執(zhí)行的改寫過后的SQL，右邊的窗格，顯示了每個(gè)執(zhí)行計(jì)劃的成本分析。
好了，今天的分享就到這里。ESR 的概念是從微信交流群看到的，想要與更多的SQL，數(shù)據(jù)庫(kù)，大數(shù)據(jù)玩家一起討論，歡迎加入我們的微信群，期待有你！
往期精彩：