第45期:一條 SQL 語句最佳化的基本思路

愛可生雲資料庫發表於2023-02-16

SQL 語句最佳化是一個既熟悉又陌生的話題。面對千奇百怪的 SQL 語句,雖然資料庫本身對 SQL 語句的最佳化一直在持續改進、提升,但是我們不能完全依賴資料庫,應該在給到資料庫之前就替它做好各種準備工作,這樣才能讓資料庫來有精力做它自己擅長的事情。

就拿 MySQL 來講,一條 SQL 語句從客戶端發出到資料庫端返回結果一般會經歷幾個階段:詞法解析、語法解析、語義解析、邏輯最佳化、物理最佳化、最終執行並返回結果。那麼這幾個階段,我們 DBA 能參與的也就是兩個階段:邏輯最佳化以及少許物理最佳化。所以在我們 DBA 這側,對 SQL 語句的最佳化簡單來講就是讓我們自己寫的 SQL 語句能更好的適應資料庫內建的最佳化規則,進一步讓 SQL 語句在每個處理階段能掃描更少的記錄數量、欄位數量來改善查詢效果。

邏輯最佳化可以理解為基於N多資料庫內建規則的預處理,規則定義越全面,對 SQL 語句最佳化的就越極致。比如使用表關聯代替子查詢、分組聚合條件上推、在特定條件下用內連線來替換外連線、檢視上推到基表等等一系列最佳化措施。

物理最佳化可以理解為資料庫按照當前 SQL 語句涉及到的表統計資訊、列統計資訊、索引個數、索引優劣、當前執行負載、當前硬體資源等可變因素來決定如何生成最優執行路徑的方法。

一般來講,我們拿到一條“不是很最佳化”、“爛的”、“慢的” 等 SQL 語句(至於怎麼拿到這條語句,不在本篇討論範圍),應該按照以下幾個步驟來逐步分析:
一、定位該SQL 語句涉及到的表結構,確認是磁碟表還是檢視,如果是磁碟表,那麼該考慮以下幾點:
  1. 這些表是否統一為InnoDB引擎(MySQL 5.7 以及以前一些老舊版本可能會有非InnoDB引擎表),如果不是,轉換表引擎為InnoDB。因為MySQL從5.5 版本開始,所有針對儲存層的最佳化都是針對InnoDB引擎的。
  2. 極個別表為臨時表。檢視臨時表相關引數是否設定合理;或者說能否把臨時表替換為磁碟表。
  3. 查詢每張表的欄位型別,看有無不合理的部分。
  4. 查詢每張表的記錄數,檢查是否過大需要後續拆分。
  5. 查詢每張表的統計資訊,檢查是否及時做了更新。
  6. 針對這些表結構做進一步分析,檢視索引設計是否合理?大致會有以下幾種結果:

(1). 都沒有索引,有的連主鍵都沒有。

(2). 都有主鍵或者唯一索引,但是沒有二級索引。

(3). 有主鍵或者唯一索引,也有一些二級索引,並且二級索引可選擇性也比較最佳化。

(4). 有主鍵或者唯一索引,也有一些二級索引,但是這些二級索引可選擇性很差。

二、如果有些表是檢視,需要考慮以下幾點:
  1. 該檢視內部的演演算法有兩種,一種是臨時表(TEMPTABLE)、另外一種是合併(MERGE )。可以針對這兩種演演算法來分別測試檢視整體效能哪個較最佳化。
  2. 該檢視內部如果有很複雜的處理邏輯,想辦法把這部分內容簡化或者從資料庫剝離轉交給應用處理,避免資料庫將其劣勢放大。
  3. 該檢視如果非必須,可拆解為基表與上層SQL 語句做合併處理,這樣效率較之前更最佳化(比如檢視內部多表關聯與上層基表再次關聯,拆分後,最佳化器就會有更多更優的表關聯順序)。
三、到了這一步,如果是多張表關聯,此處檢查表關聯鍵:
  1. 表關聯鍵為主鍵和外來鍵,也即兩表用來關聯的欄位在一張表唯一併且在另一張表被引用,這時需要補充額外的過濾條件來減少掃描記錄數。
  2. 表關聯鍵為非主鍵,也即兩表用來關聯的欄位都不唯一, 需要最佳化為唯一鍵值關聯。
  3. 表關聯鍵欄位編碼不一致,需要人為轉換欄位編碼並改為一致。
四、基於以上幾點,表結構分析這塊已經大致完畢。接下來從SQL 語句層面來分析,比如這條SQL語句能否修改為更加最佳化的方式。可以考慮以下兩點:
  1. SQL語句本身很簡單,沒有必要做修改。

    比如這條語句本身是20張表的內聯查詢,那它不夠最佳化並不是因為寫的不好,而是表關聯個數實在太多。

  2. SQL語句本身很複雜,仔細分析後,可以簡化這條語句的寫法。

    複雜SQL語句又可以分為很多類別,比如多張子表關聯、多張表巢狀子查詢、多個子查詢合併輸出、多個聚合類操作等等。每種都有不同的最佳化方法,後續我會一一介紹。

五、那麼前面幾點做完後,進一步分析最佳化後SQL 語句的執行計劃(如果有條件模擬生產環境壓力模型),一般考慮如下幾點:
  1. 改寫後的語句執行計劃很最佳化,走最合適的索引、語句本身也改的很簡潔,那麼這條語句改寫完成。
  2. 改寫後的語句執行計劃沒有走合適的索引,可以考慮在表上建立合適的索引。如果建新索引後,這條語句執行效果很好,那麼改寫完成。
  3. 改寫後的語句走了合適的索引,執行效果依然不理想,這時可能有以下幾種原因:

(1). 這條語句走的索引在不同過濾條件下,執行效果忽好忽壞。

比如日期欄位,過濾條件為昨天的查詢記錄數為100條,過濾條件為前天的查詢記錄數則變為1W條。

(2). 這條語句走的索引較優,但是表記錄數實在太大,走索引過濾的記錄數也很多,需要從表結構這層做些最佳化。比如前面幾期講過的分割槽表、拆分表等方法;或者是從業務層面限制這條語句來掃描更少的記錄數等等。

經過以上幾個步驟,一般的語句基本上都能達到比較最佳化的結果。後續我將逐步介紹各種最佳化方法以及在MySQL裡如何付諸於實踐。


關於 MySQL 的技術內容,你們還有什麼想知道的嗎?趕緊留言告訴小編吧!

相關文章