五分鐘讀完《SQL優化最佳實踐》之原理篇,SQL優化必看

大白君上個禮拜讀完了《SQL優化最佳實踐》,書中講解了SQL優化的常見案例以及背後的原理,值得細細品味,今天在這裡跟大家分享一下基本原理。

我們從一個sql執行過程開始入手,下面的圖展示了一個sql被數據庫執行的全過程。

五分鐘讀完《SQL優化最佳實踐》之原理篇,SQL優化必看

sql執行過程

我們先來看看這張圖。

Syntax check:

語法檢查,檢查你的sql語句的書寫錯誤,例如將“from”寫成“form”,相信你應該也幹過吧。

Sematic check

語義檢查,數據庫會對sql語句進行語義分析,其中包括對象的有效性,用戶是否具有訪問權限等。

Shared Pool Check

數據庫首先將SQL文本轉化為ASCII字符,然後根據哈希算法計算其對應的值(就是對應於V$SQL.SQL_ID)。根據計算出的這個值到共享池中的一個區域(就是library cache)中找到對應的一塊結構(又稱bucket),然後比較bucket裡是否存在該SQL語句。如果找到該語句,則返回對應的執行計劃(可能有多個,需要選擇一個)。執行計劃就是數據庫基於現有的優化器對sql語句進行解析優化之後保存的執行步驟,此過程比較費時,故將解析之後的sql語句的執行計劃進行保存,避免重複優化。如果沒有找到,則進入硬解析的過程。

Hard Parse

即硬解析。如果數據庫無法在內存中找到這條語句,則需要經歷一個硬解析的過程。在這個過程中需要申請一塊內存空間(並通過名叫latch的結構保證不被別人訪問)。同時還需要訪問數據字典獲得對象必要的信息。

Optimization

在這一階段,數據庫會根據很多因素由優化器生成最優的執行計劃。

Row Source Generation

所謂Row Source是指在上面的執行計劃中每一步採用什麼樣的方法去關聯、獲得數據。Row Source可能對應於表、視圖、結果集、表關聯操作、分組操作等。最終結果是一棵樹的形態。可以看成是執行計劃的樹形表達,也是執行器執行執行計劃的輸入轉變。

Execution

這一步就是執行器根據Row Source樹的每一步去執行。

這其中,執行計劃是我們關心的重點,我們來看看數據庫是怎麼根據我們sql語句制定最優的執行計劃的。首先我們需要理解對於數據庫來說針對一個特定sql什麼樣的執行計劃是最優的。

成本與優化器

要想找到最優的執行計劃,那我們就需要對執行計劃的執行代價有一個量化的標準——成本,成本是指(單數據庫讀取時間+多數據塊讀取時間+CPU處理時間)/單數據庫塊讀取所花費的時間(成本在不同版本的數據庫的定義是不大相同的)。

對sql語句進行成本量化並找到最優解的過程是由優化器來完成的。優化器是數據庫最核心的功能,也是最複雜的一部分。學習SQL優化,從本質來講就是學習從優化器的角度如何看待SQL,如何制定出更優的執行計劃。

統計信息

那麼,優化器是根據什麼來計算代價,從而找到最優解的呢?數據庫設計者自然不會傻到將所有的執行計劃都執行一遍吧!優化器的信息來源就是統計信息。

統計信息包括但不限於

  1. 表統計信息,表中數據的總行數,行平均長度,數據塊中平均空餘空間(字節),高水位線下的數據塊個數(全表掃描的數據塊數)。
  2. 索引統計信息,記錄表上的索引信息,索引的塊數,索引的高度,索引的聚簇因子。其中聚簇因子用於表示表中數據的存儲順序和某些索引字段順序的符合程度,聚簇因子是優化器是否採用索引掃描的重要依據。
  3. 字段統計信息,字段塊數,不同的值得個數,選擇率,直方圖信息等。

瞭解了數據庫執行sql的過程,我們在寫sql語句的過程中,需要根據執行計劃的優化策略來優化我們的sql語句。

關注技術大白,帶你閱讀技術書籍,瞭解技術內幕


分享到:


相關文章: