MySQL Query Optimizer 研究
了解基本知識以做好查詢最佳化
前言
查詢最佳化就是與資料庫的運作邏輯進行一場競賽，我們如何使查詢速度更快？有兩個重要因素，除了開發人員的技能以及撰寫良好的 SQL 指令外，再來就是透過 MySQL Query Optimizer 來決定查詢的效能了。
在前一篇文章 MySQL 查詢運作模式中介紹了 MySQL 的運作方法，在本文章要來獨立探討一個決定整個查詢效能最核心的東西：Query Optimizer。
Query Optimizer 是整個查詢最佳化化過程中最大的核心以及靈魂，在 MySQL 中，Query 微調的工作其實是一個非常有邏輯的過程。
MySQL Query Optimizer 的工作內容
Query Optimizer 主要的工作是對任何查詢來構建出最有效的執行計劃，加速取得查詢結果。透過挑選出最好的索引是構建最有效率執行計劃的任務之一。那他透過哪些方式來加快查詢？
MySQL Query Optimizer 的設計是按照以成本為基礎的演算法。這個演算法會透過評估多個方面的內容，例如磁碟的 I/O 操作、網路速度、記憶體使用和 CPU 時間。
Query Optimizer 首先分析一個 SQL 查詢邏輯，然後探索不同的執行策略。這個過程包括考慮各種 join 方法以及不同的索引選擇。在這個過程中，Query Optimizer 會利用資料庫的統計資訊，比如一張表的行數、索引的分佈，和列的唯一值的數量。這些統計資訊對於確定哪些索引和 join 策略最有效來說非常重要。因為 Query Optimizer 會評估使用特定索引，是否能顯著減少需要讀取的資料量。
此外，Query Optimizer 還會考慮資料庫的當前狀態，比如 cache 中已有的資料和當前的系統負載。也就是說即使相同的資料查詢，在不同時間執行可能會產生不同的執行計劃。
最後，Query Optimizer 還考慮了執行計劃的可預測性和一致性。在某些情況下，一個稍微成本高一點但更穩定的計劃就可能會被 MySQL 所選擇，以確保查詢的執行在不同情況下仍然效能很高。
透過這些方法，MySQL Query Optimizer 能夠提供快速且資源高效的查詢執行計劃，幫助保證資料庫的整體性能和回應時間。
Query Optimizer 的限制
那有哪些情況會造成 Query Optimizer 的表現不如預期？
首先如果資料庫的統計資訊不準確的話，Query Optimizer可能做出基於過時或錯誤的決策。這可能導致最後 Execution Engine 選擇非最佳的執行計劃，從而降低查詢效率。
第二個是本身的查詢就很複雜，像是多個 join 或是 subquery 的查詢，Query Optimizer 可能無法總是找到最佳的執行計劃。這是因為執行計劃的選擇空間非常大，而 Query Optimizer 可能因時間或資源限制而無法探索所有可能的執行計劃。
第三個是索引的選擇，Query Optimizer 在分析的時候沒有找到最好的索引值，造成的原因可能跟第一點有關，也有可能跟演算法本身的限制有關，所以當 Query Optimizer 選擇了非最佳的索引，就導致查詢速度變慢。
第四個是可能查詢的時候某些 db lock 卡住了，導致影響查詢的效能和執行計劃的選擇，Query Optimizer 無法預測最好的低查詢成本。
最後回到硬體規格，當記憶體不足或其他原因，資源分配不足或配置不當，即使是最佳的執行計劃也可能無法達到預期的性能。
Query Optimizer 最佳化
最大化 Query Optimizer 的性能，可以考量三個面向，這些評估完成後不僅可以提升查詢效率，而且按照了我們對資料庫內部運作的認知來執行。
首先我們先思考表的本身是否被調整過，這個調整包括是否選擇合適的索引、表本身的結構是否設計得宜，還有是否有效的 caching 資料。另一個常見的作法就是 partition，不過只單純分表，這個過往暴力的做法據說在 2023 已經不是最好的 practice 了。因為有時 join 的查詢反而因為 partition 而效能更糟糕。
第二個是可以透過理解 Query Optimizer 的運作機制，在前面的文章我們探討了一段時間，這邊我們可以透過前面的理論，搭配像是 EXPLAIN 指令，去評估查詢策略和成本，進而改寫 SQL query。
最後的面向則是對於重寫 SQL query，比如要不要加 Limit 語法，where 條件的調整，或是修改 join 的順序等等。
如果你只關心怎麼改 query，就只需要好好做到以下兩點，就有很初步的成效了。
- 查詢狀態：了解查詢的狀態，是評估和改進查詢性效能的關鍵。這涉及監控和分析查詢在資料庫中的行為，包括其執行時間、資源使用情況等。如果你有使用 AWS RDS，他有一些簡易的監控儀表板可以用。如果你是自建，可以透過 ELK stack 或是 Grafana 這些工具建立監控儀表板。
- EXPLAIN 指令： EXPLAIN 是一個強大的語法，用於分析特定查詢的執行計劃。透過這個關鍵字，我們可以了解該查詢是是如何被 Query Optimizer 評估，評估的內容包括它如何選擇索引、估計行數和決定 join 的順序。
透過這三個面向的思考與綜合應用，我們可以有效地提升查詢效能，同時深入理解資料庫的內部工作原理，從而讓查詢執行更有效能。
