SQL最佳化方案

Arno_z發表於2024-10-11
SQL
  • 微信公眾號：阿俊的學習記錄空間
  • 小紅書：ArnoZhang
  • wordpress：arnozhang1994
  • 部落格園：arnozhang
  • CSDN：ArnoZhang1994

1. 基礎最佳化策略

  • 理解資料庫設計對效能的影響：

    • 資料庫的設計直接影響查詢效能，特別是索引和約束的設定。
    • 大型表中的查詢通常會隨著表的增長而變慢，因此需要合理的表設計。

  • 最佳化查詢設計：

    • 避免使用 SELECT *，只查詢所需的列。
    • 對於效能差的查詢，需要了解資料庫管理系統 (DBMS) 如何生成執行計劃，並透過工具（如解釋計劃、分析器）來診斷查詢瓶頸。
    • 避免子查詢，使exists > in > inner join > outer join

  • 理解解釋計劃和最佳化器：

    • 查詢最佳化的核心是檢視 DBMS 生成的“執行計劃”，瞭解查詢的執行步驟。
    • 解釋計劃可以幫助識別全表掃描、索引缺失和最佳化器決策錯誤。
    • 使用 EXPLAIN 和 ANALYZE 等工具檢視查詢計劃。

2. 索引最佳化

  • 新增索引：

    • 確保在 WHERE 子句、連線條件、排序欄位和分組欄位上建立合適的索引。
    • 定期檢查和刪除冗餘索引，避免索引過多影響寫入效能。

  • 最佳化索引使用：

    • 避免在查詢中使用不匹配的資料型別（如 VARCHAR 和 NVARCHAR 的比較），確保索引能夠被最佳化器使用。
    • 使用 EXISTS 代替 IN 進行最佳化，避免效能問題。
    • 避免在 LIKE 查詢的開始部分使用萬用字元 %，這會導致無法使用索引。

3. 記憶體和快取管理

  • 記憶體不足的最佳化：

    • 保證查詢處理和排序時分配足夠的記憶體，最佳化 ORDER BY 和 GROUP BY 操作。
    • 確保資料快取中的資料能夠保留足夠長的時間，減少磁碟 I/O 操作。

  • 快取機制：

    • 利用查詢快取機制儲存常用查詢的結果，減少重複查詢帶來的負載。
    • 使用 RESULT_CACHE 來快取複雜查詢的結果。

4. 查詢執行最佳化

  • 避免全表掃描：

    • 分析解釋計劃，減少或消除全表掃描。對大表可以考慮分割槽處理，最佳化資料訪問路徑。

  • 批次操作與過濾：

    • 在 WHERE 子句中使用適當的過濾條件，避免返回不必要的行。
    • 儘量使用批次操作而非逐行處理，以提高事務執行效率。

  • 避免不必要的排序和聚合：

    • 避免 DISTINCT 和 UNION 等會增加排序和去重開銷的關鍵字。
    • 使用記憶體充足的情況下執行排序操作，避免使用磁碟。

5. 高階最佳化策略

  • 分割槽表：

    • 對大型表進行分割槽，最佳化查詢時可以避免掃描不必要的分割槽。

  • 並行查詢：

    • 對於大規模查詢操作，考慮使用並行執行來分攤 CPU 和 I/O 負載。

  • 物化檢視：

    • 使用物化檢視儲存複雜查詢的結果，減少執行時的計算量。

6. 監控與工具

  • 效能監控：

    • 持續監控查詢的效能，使用工具（如 performance_schema 和 sys 模式）收集查詢效能指標。

  • 分析工具：

    • 利用 MySQL 的 pt-query-digest 工具或者 Oracle 的 AWR 報告來分析慢查詢和識別效能瓶頸。

7. 系統配置最佳化

  • 伺服器資源管理：

    • 調整伺服器記憶體、磁碟 I/O 和 CPU 配置，確保系統資源與查詢負載匹配。

  • 資源組和排程：

    • 使用資源組對不同的查詢進行優先順序分配，以平衡負載並最佳化重要查詢的效能。

8. 總結

透過以上最佳化步驟，你可以系統化地分析和最佳化 SQL 查詢的效能，涵蓋了從基礎設計到高階最佳化的全方位策略。最佳化時要根據實際情況逐步實施，首先從索引、記憶體和查詢設計等最基本的方面入手，結合分割槽表、並行查詢等高階技術進行深度最佳化。
在微服務場景中，可以使用skywalking監控服務的sql執行情況。也可以使用mysql自帶的一些工具.
關於使用各種效能分析軟體，還涉及到成本的衡量。再有，執行在同一臺主機上的其它應用很可能也會對DB服務造成影響，需要保證DB服務有足夠的配置可供使用。

相關文章