原文地址：http://www.itpub.net/thread-1357925-1-1.html

英文原文：http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/

由大牛 newkid 翻譯整理。

設計高效SQL: 一種視覺的方法

      人們提到SQL時總是說，既然它是一種聲明性語言，你不必告訴它如何獲得你要的數據; 你只需描述你要找的數據。確實如此：描述你的需求，你就會得到你想要的，但沒人能保證能夠以你預期的速度和成本獲得。這就像在一個陌生的城市乘坐出租車。你可以告訴司機你要去哪兒，并且希望他會帶你走最好的路線，但有時候時間和花費都超過了你的預期，除非你能告訴司機一些你想要他走的路線的相關信息。
      不管優化器多么優秀，一定會有某些情形存在，這時它的算法不能很好地滿足你的需求。可能是統計信息造成的誤導，或者優化器對于你的數據做了一些假定，而這些假定是錯誤的。如果發生了這樣的事，你就需要找到一個方法能夠給優化器一些指引。
      這篇文章描述了一種視覺的方法來設計SQL查詢，特別是復雜的查詢，它能夠讓你寫出恰當的執行計劃。它不僅僅在寫新查詢的時候有用，特別是對那些優化器表現不夠好、需要幫助的查詢進行"查錯"、返工重寫的時候尤其有用。思想很簡單，本質上對所有數據庫都適用，盡管你的編程方法從計劃到SQL都很可能是依賴于某種數據庫的。

了解你的數據
      在你優化查詢之前，首先你必須知道：
            . 你期望處理多少數據(數據量)
            . 數據放在哪里(數據分布)
      當你在估計獲得所需結果必須付出的工作量和時間，數據量和數據分布同等重要。如果你需要訪問大量的數據，并且廣泛地分布在數據庫的不同地方，那么你的查詢就不太可能執行得很快。然而，如果你采用了諸如聚簇索引之類的技術來保證數據都集中在一個相對小的空間，那么你仍然有可能快速訪問大量數據。因此，你要提出的兩個首要問題是：數據有多少？數據在哪里？
接下來更進一步的問題是：怎么才能得到那些數據。比如你要從表中以某種條件挑出50行; 這聽起來很直觀，但最有效的方法是什么？你可能有如下選擇：
            1. 一個"十分精確"的索引
            2. 一個相當精確的索引，它能夠為你挑出100行，其中有50行你必須剔除
            3. 一個很"浪費"的索引，它能夠為你挑出500行，其中有450行你必須剔除
      很重要的一點：如果你必須從第二和第三個索引中選一個，從我披露的信息中你能否判斷哪個比較高效？正確的回答是“不能”。從100行中剔除50行聽起來比從500行中剔除450行更高效，但是請記住：聚簇，或者說，數據的物理組織或分布，是關系重大的。
      假設你有一個索引，找出了10頁（譯者注：“頁”可理解為ORACLE的“塊”）的數據，每頁包含10行，你要的50行在其中的5頁上；而另一個索引找出了5頁，每頁包含100行。訪問10個頁并剔除50行，或者訪問5個頁剔除450行，哪個方法好？訪問更少數據頁的方法可能更好。
      請記住，你比優化器更加了解你的應用和數據。有時候優化器選擇了一個不好的執行計劃只是因為它不能像你一樣了解數據，了解應用如何處理數據。

如果一幅畫勝過千言萬語......
      ......為什么不把你的查詢畫出來呢？如果你有一個復雜的查詢，包含了許多表，那么你確實需要有一種方法能夠表達大量的信息，然后又能夠被輕易接受。畫圖是個好主意，特別是你試圖替別人的SQL查錯的時候。
      我的方法很簡單：
      把SQL通讀一遍，把每個表畫成一個方框，把每個連接(JOIN)畫成方框之間的一條連線。
      如果你知道表連接的基數(一對一，一對多，多對多), 就在連接線“多”的那端畫一個“烏鴉爪”的形狀。
      如果你在表上有一個過濾謂詞，就畫一個進入方框箭頭的箭頭，把謂詞寫在旁邊。
      如果一個“表”實際上是一個內聯視圖，或者是一個包含多個表的子查詢，就把這組表用虛線框圍起來。
      例如，你有一個schema定義了一個簡單的訂單處理系統：客戶下訂單，訂單可以有多個訂單行，一個訂單行包含一種產品，產品來自多個供應商；某些產品可被其他產品代替。有一天你被要求提供一個報表：“來自倫敦的客戶上周下的訂單，產品來自利茲市(Leeds)的供應商并且可被其他地方的供應商替代”。
      假設我們只需要每個訂單中此類產品的訂單行的詳細信息，這可以寫成清單1中的查詢：

SELECT {list of columns}
FROM           customers cus
    INNER JOIN orders ord ON ord.id_customer = cus.id
    INNER JOIN order_lines orl ON orl.id_order = ord.id
    INNER JOIN products prd1 ON prd1.id = orl.id_product
    INNER JOIN suppliers sup1 ON sup1.id = prd1.id_supplier
WHERE   cus.location = 'LONDON'
    AND ord.date_placed BETWEEN
                             dateadd(day, -7, getdate())
                        AND  getdate()
    AND sup1.location = 'LEEDS'
    AND EXISTS ( SELECT  NULL
                 FROM  alternatives    alt
                       INNER JOIN     products prd2
                         ON prd2.id = alt.id_product_sub
                       INNER JOIN     suppliers sup2
                         ON sup2.id = prd2.id_supplier
                 WHERE    alt.id_product = prd1.id
                       AND sup2.location != 'LEEDS' )

      代碼清單1: 獲取替代供應商的查詢

      很可能我對schema的語言描述不能夠在SQL中立即可見，但是運用視覺方法可以把查詢變成如圖1所示的圖像。一般要兩三次嘗試才能畫出一幅清晰、整潔的圖像，這不足為奇，特別是當你對別人的SQL進行反向工程的時候。我的第一幅草圖總是會把所有的表擠在畫面的一角。

圖1: 我們的查詢的第一幅草圖

      在這幅圖的基礎上，我們開始把所需要的數值信息填進去。能詳細到什么程度取決于我們對相關表的熟悉程度，和我們對基礎應用的了解程度。在這個例子中，我會用orders表來演示我們所需信息的一些準則。這些信息有的來自INFORMATION_SCHEMA，有的可能來自對數據本身的查詢，但理想情況是大部分都是已知的，因為我們對應用的運作和數據的特點都很熟悉：
      .行數: 250,000
      .數據頁數: 8,000
      .開始基數: 2,400
      .最終基數: 20
      .進入orders表的現有相關索引：
          .(date_placed)聚簇性很好，每天大約400行
          .(id_customer)聚簇性很差，每個客戶 10 到 150 行
      .從orders表引出的現有相關索引:
          .order_lines (id_order, line_no)聚簇性很好，每個訂單 1 to 15 行
          .customers (id) primary key 主鍵
      注意我通常會把索引畫成方框之間的箭頭，并且標上它們的統計信息，我還會把表的統計信息寫在方框里；圖下方冗長的文字信息沒什么用。有時候你必須需要一張大紙才能畫出正確的草圖，而這個網頁的空間是很有限的。
      “開始基數”和“最終基數”必須解釋一下。開始基數指的是我期望從表中獲得的行數，假如這張表是我在整個查詢中要訪問的第一張表；換句話說，它是我在表上使用了帶常量的過濾謂詞之后的行數。原查詢要求“上周的訂單”，而我知道通常每周大約有2,400個訂單。否則，我也許必須運行一個簡單的查詢，select count(*) from orders group by week, 來獲得一個預估的數字。
      最終基數是指這張表會出現在最終結果集中的行數，而要估算這個數字則困難得多，除非有一個熟悉業務的人可以幫你。在本例中，我們最接近的估算數字表示開始基數和最終基數之間有巨大的差異。這說明在其中的某個步驟，我可能必須做很多工作來除掉這些額外的行，而我需要盡量讓這個“丟棄”動作的工作量最小化。
弄明白表里有什么，我們需要從表中得到什么，如何才能拿到，需要多少工作量，接下來我們就必須從圖中選擇一張表作為開始，然后不斷重復這個問題：接下來我要訪問哪張表？怎么才能到那里去？為了回答這些問題，你可以考慮以下的四個子問題，它們之間的優先順序并不需要很嚴格：
      1.在訪問下一張表之前，我能不能在當前結果集上做一個聚合來(大幅度地)減少數據量？
      2.有沒有一張表我能夠訪問并且能夠(以低廉的代價)減少數據？
      3.有沒有一張表能夠僅僅(稍微地)增加行的大小而不會增加行數？
      4.哪張表增加的行數最少(代價低)？
      如果你讓這些問題來主導你對“下一張表”的選擇偏向，它就會趨向于讓中間數據量保持在一個低水平，從而使得工作量也在低水平。顯然，在不同選擇之間也有妥協，比如，是選擇行大小顯著增加（選項3）還是行數稍微增加（選項4），諸如此類。但是，如果你把這些選項當作靈活而不是死板的教條，并且總是提前思考幾個步驟，即使你錯了也差不遠。
      在一個數據倉庫，或者決策支持系統(DSS)中，你可能會發現理論上可以選擇任何一張表作為分析的起點，分析幾個不同路徑然后找到最合適的一個，但通常的準則是選擇一張能夠以相對低廉的代價獲取少量數據的表。
      在一個在線交易系統(OLTP), 通常只有一至兩個很明顯的開始點，假如你有相當全面的業務知識的話。在這個例子中，明顯的開始點是訂單(orders)表和客戶(customers) 表。畢竟，這個報表是關于“幾個客戶在一個短時期內的訂單”，所以從圖上的客戶/訂單一端開始，看起來會提供一個小的開始數據集。但是為了演示的目的，咱們不妨裝傻一下，看看如果選擇了供應商(suppliers)表作為起點會發生什么。
      從供應商(suppliers)表出發(返回Leeds的數據只有幾行)，接下來唯一合理的選擇是利用外鍵索引(你可以假定有這么個索引)去產品(products)表。當然，嚴格來講你可以去到任何一張表，前提是沒有笛卡爾積（交叉連接）的威脅。
      從產品(products)表出發，我們能夠去訂單行(Order_lines)表或者替代品(Alternatives)表。訪問 Order_lines表會使得行數大量增加，而我們將不得不從一張非常大的表中挑選一些廣泛分布的數據，所以我們將不得不在一個嵌套循環連接中使用一個昂貴的索引，或者用哈希連接做一個表掃描。
      另一方面，如果我們選擇替代品(Alternatives)表，并且期望隨后在子查詢中訪問products和suppliers表，然后回到 Order_lines表，那么我們可能發現有很多產品沒有替代品，從而行數會減少，因此這是一個較好的選擇。等我們過濾掉不是來自利茲市(Leeds) 的供應商，這個行數還會進一步減少。
      可是，最終我們還是會再次訪問Products表剩下的數據并且連接到Order_lines表，而根據我關于Order_lines這樣的表的常識，這樣將會很低效地生成一個很大的數據集。一種產品很可能會出現在許多訂單行之中，而且在表中的分布范圍相當廣泛；這不是一個好兆頭。
      所以，僅有的合理選擇是將orders和customers作為起點，并且，在訪問這兩張表之后，其他表的順序為：Order_lines, Products, Suppliers, (Alternatives, Products, Suppliers).
      那么，我們是以Customers–Orders 還是 Orders–Customers 開始? 這就是索引會聚簇的重要之處。
      按現在的情況，考慮到orders表現有的相關索引，如果我們選擇倫敦的客戶，那么我們就得用id_customer索引來訪問orders表來選擇這些客戶的所有訂單，然后再丟棄那些在這一周的日期范圍之外的訂單。回過來看看統計信息，我們總共有250,000個訂單，每周大約2,400個，這就說明我們的數據覆蓋了兩年(104周)的范圍。所以，如果我們選擇一個客戶，然后再選擇這個客戶的所有訂單，最終這些訂單中大約有99%將被丟棄。考慮到這樣的數據量，還有訂單隨著時間分布的方式，這將會花費大量工作收集大量數據，而這其中大部分都會被丟棄。
      另一種選擇就是從orders表開始，利用下單日期(date_placed)索引挑出那一周的2,400個訂單，然后利用customers的主鍵索引連接到customers表，丟棄那些不是來自倫敦的客戶。訂單隨著時間被聚簇得很好，而且，既然最近的訂單是最可能被持續處理的，它們很可能被緩存，并且還會繼續留在緩存中。這說明即使我們挑選了大量訂單來作為開始，我們的效率也很可能非常高。
      最壞的情況下，我們必須訪問2,400個客戶行，并且它們很可能是在customers表中隨機分布的，所以這可能會有點性能(I/O)問題。但是，比起訂單表這樣的事務表來，一個像客戶表這樣的引用表可能會從合理的緩存中獲益，因此我們對這樣的問題可以忽略。這樣我們就得到了圖2中所示的草圖。

圖2: 表被訪問的順序

      一旦我們決定這是查詢的正確路徑，我們就可以著手實施，可能是簡單方便的重新排列一下表在查詢中的順序并且加上“強制順序”的提示。(譯者注：相當于ORACLE中的ORDERED提示，這僅僅在發現優化器生成的計劃不夠理想時使用)
      另一方面，如果這個查詢至關重要，而且我們早在系統設計階段就得以介入，那么我們可能需要考慮一下架構上的特點。

選擇索引
      我們可能會在customers表上建一個聚簇索引，但如果它是一個簡單的堆表帶一個非聚簇的主鍵索引(id)，我們可以考慮把地點(location) 列加入到索引中，所以我們不必再訪問表來檢查地點。這種安排，即一個小小的主鍵索引帶附加列，比起在表的id列上建一個聚簇索引會給我們帶來更多的緩存上的好處。
(譯者注：聚簇索引(clustered index)是SQL SERVER和SYBASE的概念，相當于ORACLE中的索引組織表(index organized table)和索引聚簇表(index clustered table), 帶附加列(included column)的主鍵在oracle中不支持，ORACLE中必須另外建立一個 (id,location) 上的索引)
      或者，如果我們在orders表的(id_customer, date_placed)上建立一個索引，我們可以考慮從customers表開始我們的查詢，因為這個新索引使得我們能夠利用customers表中選擇出來的客戶非常精確地訪問orders表：雖然這個索引可能比較大，對于這個查詢而言幾乎沒有緩存上的好處。因此也許(date_placed, id_customer)會更好。
      理所當然的，這又把我們帶回了聚簇這個話題。在orders表上有兩個候選的聚簇索引：下單日期(date_placed)和客戶id(id_customer)。如果要用的話，哪一個好？或者（悄悄說）我們應該只是建一個簡單的堆表？
      當然，這個基礎設計問題應該在系統設計階段就很明確地提出來，并且取決于我們期望按照什么來訪問表：根據客戶，根據日期，或者兩者的組合(如本例所示)。
既然數據是按照日期順序產生的，它自己很自然地就按日期來聚簇，哪怕我們只是用了一個簡單的堆表，所以按下單日期(date_placed)的聚簇索引不會帶來明顯的好處。另一方面，假如我們在客戶id建立一個聚簇索引，數據插入將會變得昂貴得多，并且，除非我們經常查詢一個客戶的完整歷史，當我們運行基于單個客戶的查詢的時候，一個(id_customer, date_placed)上的非聚簇索引已經能給我們足夠好的性能。
      最后要說的是，在一個生產系統上修改索引肯定會有風險，為了解決SQL的性能問題，我們希望通過對代碼的處理，對統計信息的調整，或者使用提示來強制更優的執行計劃。畫圖使得你更好地看清楚和理解可供選擇的手段，也使得你在作一些困難決定時更加心里有底。

結論
      為了寫出高效的查詢，你必須知道你需要獲取多少數據，數據又在哪里。你還必須知道你有哪些手段可以獲取這些數據，為了訪問你不需要的數據必須浪費多少代價，這樣你才能決定訪問表的順序。
      對于復雜的查詢，最好的辦法是從畫圖開始，包括所有相關的表，畫出表間的連接，指出相關的數據量，描述出能讓你從一張表到達另一張表的所有索引。這樣的一張圖會使你更易于理解你的查詢所有可能的訪問路徑的效率。