MySQL慢日誌優化

壁觀發表於2021-12-16
MySql優化

慢日誌的效能問題

  1. 造成 I/O 和 CPU 資源消耗：慢日誌通常會掃描大量非目的的資料，自然就會造成 I/O 和 CPU 的資源消耗，影響到其他業務的正常使用，有可能因為單個慢 SQL 就能拖慢整個資料庫的效能，而且這種慢 SQL，在實際業務場景下，通常都是程式發起數個 SQL 請求，通過 SHOW PROCESSLIST 命令可以捕捉到同時有 N 個類似的 SQL 請求在執行。
  2. 鎖等待消耗：由於慢 SQL(select 查詢)會阻塞 MDL 鎖的獲取，所以針對 XtraBackup 全量備份和針對表的 DDL 操作都有可能被阻塞，一旦 DDL 被阻塞，針對表的請求就會變成序列阻塞，後續業務也就無法執行。
  3. 鎖申請消耗：對於非 select 查詢的慢事務， SQL 還會把持鎖不釋放，讓後續事務無法申請到鎖，造成等待失敗，對業務本身來講是不可以接受的。

怎麼收集慢日誌？

    ELK 體系分析慢日誌　

  • MySQL 開啟慢日誌——＞檔案記錄慢日誌
  • ELK 環境搭建
  • MySQL 伺服器安裝 Filebeat 並進行 mysql-slow.log 過濾處理配置
  • ELK-WEB 進行維度檢視

　Percona 分析慢日誌

　　Percona 的 pt-query-digest 是一款可以針對 MySQL 慢日誌進行定製化分析的工具

  • MySQL 開啟慢日誌——＞檔案記錄慢日誌
  • Percona 元件安裝並編寫 pt-query-digest 定時指令碼
  • 遠端資料庫進行定期刪除保留
  • 遠端資料庫提供 Web API 介面查詢展示

你需要了解的優化基礎

　　優化慢日誌的思路是“收集——分析——優化——預防”

　　優化 SQL 的基礎手段是 EXPLAIN，我們要在此基礎上，針對 SQL 語句定點優化消除。

　　EXPLAIN 基本語法是 EXPLAIN + SQL，我們需要針對 EXPLAIN 進行解讀：

　　

  • select_type：查詢的模式

  • type：掃描的方式，ALL（全表掃描）；SIMPLE（簡單查詢）；RANGE（範圍查詢）……

  • table：選擇的目標

  • possible_keys：可能用到的索引（優化器可能選擇的索引項）

  • key：實際用到的索引（要注意，如果 key 為 NULL 或者並不是你所期望看到的索引項，就需要進行處理）

  • key_len：索引長度（需要關注），實際用到的索引長度，此項針對聯合索引，因為存在並沒有全部應用聯合索引的情況，通過索引長度和聯合索引的定義長度進行對比

  • rows：掃描的行數（需要關注），理論上掃描得越多，效能消耗就越大（注意，並不是實際的資料行數而是目標的資料）

  • extra：額外的資訊（需要關注）Using temporary （採用臨時表）；Using filesort （採用檔案排序）；Using index（採用覆蓋索引）；Using join buffer (Block Nested Loop) BNL 優化，出現此項則代表多表 JOIN 連線沒有走索引

SQL 具體的優化思路

　　新增索引優化慢日誌

　　在索引新增時，你需要注意以下幾點情況：

  • 避免索引欄位使用函式，儘量在程式端完成計算；

  • 避免發生隱式轉換，這要注意條件查詢的型別區別，比如字串型別需要加引號；

  • order by 欄位需要走索引，否則會發生 filesort；

  • 當全表掃描成本低於使用索引成本，需要重新選擇區分度大的條件選項；

  • 由於後設資料不準確造成優化器選擇失誤，需要手動進行後設資料收集統計；

  • 聯合索引的使用順序基於索引欄位的建立順序。

　　除此之外，針對多表聯查的 SQL 我也提供給你幾點建議：

  • 多表聯查的語句一定要在連線欄位新增索引，這非常重要；

  • 永遠是小表驅動大表，合理地選擇你的驅動表。

　　要知道優化的目標是儘可能減少 JOIN 中 Nested Loop 的迴圈次數，從而保證“永遠用小結果集驅動大結果集（這一點很重要）”。A JOIN B，其中，A為驅動，A 中每一行和 B 進行迴圈JOIN，看是否滿足條件，所以當 A 為小結果集時，越快，那麼：

  • 儘量不要巢狀太多的 JOIN 語句，連表的數量越多，效能消耗越大，業務複雜性也會越高，MySQL 不是 Oracle，這一點需要你切記；

  • 多表聯查的不同表如果字符集不一致，會導致連線欄位索引失效。

　　最後，索引新增你也需要注意這樣兩點：

  • 建議用 pt-osc、gh-ost 等工具進行新增索引，這樣能夠在執行 DDL 語句時不會阻塞表；

  • 要在業務低峰期進行操作，儘量避免影響業務。

　　通過拆分冷熱資料優化慢日誌

　　你可能對“通過拆分冷熱資料優化慢日誌的方案”感到陌生，但實際來說，這個方案非常實用，尤其適合“超大表暫時無法新增有效索引的情況”，超大表是因為歷史資料不斷插入形成的，後面業務需要查詢某些特定條件，而這些特定條件區分度又比較低，即便新增索引效率也不會提升太大。

　　比如 A 系統只需要近一年的資料，但是這個掃描條件沒辦法新增合適的索引，所以將之前的資料進行歸檔，在某些特定的條件下，能有效地減少掃描行數，大大加快 SQL 語句的執行時間。

　　拆分冷熱資料，針對特定場景的慢日誌是有效果的，也有利於資料管理，根據我的經驗，可以設立定時任務，按照每天/每週/每月的頻率，指定業務低峰時期執行資料歸檔，執行完成後郵件/微信通知即可。

　　通過讀寫分離進行優化

　　當主庫的負載增多，我們有必要做讀寫分離：將定時的慢日誌剝離出主庫，轉而查詢沒有提供服務的從庫，從另一個角度降低了慢日誌對於主資料庫的影響，現階段比較成熟的資料庫讀寫分離方案大概有 3 種。

  • Sharding-JDBC+LVS+Keepalived：Sharding-JDBC 在程式端指定讀的 VIP，作為讀資料來源，然後 LVS+Keepalived 繫結 VIP+後端的 MySQL 從庫提供讀的服務。

  • ProxySQL：ProxySQL 是比較成熟的中介軟體方案，通過針對 SQL 語句的正規表示式配置，然後將 SQL 分發給從庫 or 主庫，精確到具體的 SQL 業務。

  • MySQLRouter：MySQLRouter 是 MySQL 官方推行的一款輕量級中介軟體，用來實現 MySQL 的讀寫分離。

　　提升硬體水平

　　提升伺服器配置能有效減少慢日誌的生成量，尤其是針對 PCIE-SSD 的磁碟裝置使用，非常優秀，但是使用成本也會隨之增加。

　　建立 SQL 整體優化機制

  • 建立 DB——應用負責人機制

　　這點非常重要，是一切的基礎。簡單來說，就是針對每個庫都要有一個相應的負責人，如果一個庫存在多個人呼叫的情況下(核心庫)就需要包含多個負責人。

  • 過濾慢日誌，傳送郵件 TOP

　　通過第一步建立的負責人對應機制，然後通過程式/指令碼過濾指定的庫的 TOP N 慢 SQL，按照一定時期傳送給相應的研發負責人，讓他們進行跟蹤優化（定時期可以是一天、一週或者半個月，按照機制進行即可）。

  • 建立追蹤機制

　　DBA 根據慢日誌建立追蹤機制表，比如，記錄每個慢 SQL 的優化進度、是否可以優化、最終期限……

 　　優化 SQL 本身就是事後救火，那麼只有建立長期有效的機制才是王道

相關文章