第45期：一條 SQL 語句優化的基本思路

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 的技術内容，你們還有什麼想知道的嗎？趕緊留言告訴小編吧！