MySQL怎么快速定位慢查詢問題

這篇文章主要介紹“MySQL怎么快速定位慢查詢問題”，在日常操作中，相信很多人在MySQL怎么快速定位慢查詢問題問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL怎么快速定位慢查詢問題”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

一條慢查詢會造成什么后果？剛開始使用MySQL的開發、初級DBA 以為就是簡單的查詢變慢些，體驗稍微有一丟丟影響，殊不知，慢查詢的破壞力遠不止如此。業務高峰期，這頭SQL還沒處理完，大量新的查詢請求堆積，CPU使用率長時間居高不下，甚至高達100%，系統直接崩潰……慢查詢這樣的黑天鵝事件，可能直接影響業務穩定性，造成巨大經濟損失。

慢查詢，字面理解就是查詢慢的意思，比如某類查詢，正常情況下消耗100ms 左右，異常時可能飆升到 15s。為定位慢查詢問題，我們可以按如下幾個步驟進行：

一、開啟慢日志；

二、使用慢日志查詢分析管理工具；

三、基于已有的慢日志分析，對系統本身做優化（如查詢語句或表結構設計方面）。

啟用慢日志，定位異常

慢日志默認情況下時不開啟的，如果需要對SQL做優化，可以開啟這個功能。登陸 MySQL 后，執行如下 SQL 語句即可開啟慢日志（這里以MySQL 5.7.33 為例 ，其它版本基本通用）：

SET GLOBAL slow_query_log = 'ON';
-- 未使用索引的查詢也認為是一個可能的慢查詢
set global log_queries_not_using_indexes = 'ON';

一般情況下，MySQL 慢日志位于 /var/lib/mysql/<host-name>-slow.log，我們可以模擬一個慢查詢，然后即可看到慢日志記錄產生：

-- 手動觸發一個慢查詢：MySQL默認認為，一個大于 10s 的查詢就是慢查詢
SELECT sleep(11);

看下慢查詢日志：

$ sudo cat /var/lib/mysql/ubt-server-slow.log

/usr/sbin/mysqld, Version: 5.7.33-0ubuntu0.18.04.1 ((Ubuntu)). started with:

Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock

# Time: 2021-03-12T08:52:54.227174Z

# User@Host: df-test[df-test] @  [10.100.64.118]  Id:     2

# Query_time: 11.000551  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

use number1;

SET timestamp=1615539174;

select sleep(11);

從該日志中，我們能看到如下幾個信息（根據不同的 MySQL 版本或者配置，這些信息可能有增減）：

• 產生時間：2021-03-12T08:52:54.227174Z

• 來源：df-test[df-test] @  [10.100.64.118]，即用戶 df-test 在10.100.64.118這個機器上執行了這個查詢

• 查詢統計：如消耗的時間，發送/接收的行數

• 具體的 SQL 語句

從這些信息中，我們可以比較清晰地知道這個慢查詢的來龍去脈，比較精確地定位具體的業務代碼。但這里有個問題，為保障MySQL數據庫安全，MySQL要求只有登錄到具體服務器才能看到慢查詢日志詳情，這直接影響到對異常出現時的處理效率，拖累異常地位、分析和解決的進度。

除開啟系統自帶的慢日志之外，還有什么有效的方式能讓開發人員快速、直接且準確地解決這個問題？

使用MySQL慢日志分析工具

常用的對慢SQL做優化的分析工具有：mysqldumpslow、mysqlsla、mysql-explain-slow-log、mysql-log-filter、myprofi。

可參考：《4.6.9 mysqldumpslow-匯總慢查詢日志文件》

https://dev.mysql.com/doc/refman/8.0/en/mysqldumpslow.html

02 mysql-log-filter

google code 上找到的一個分析工具，提供了 python 和 php 兩種可執行的腳本。基本功能比官方的 mysqldumpslow 多了查詢時間的統計信息(平均、最大、累計)，其他類似。特色功能除了統計信息外，針對輸出內容做了排版和格式化，保證整體輸出的簡潔。推薦給喜歡簡潔報表的朋友。

安裝好 DataKit 后，在 /usr/local/cloudcare/dataflux/datakit/conf.d/log/ 目錄下，復制一份 MySQL 日志采集配置

$ sudo cp mysqlog.conf.sample mysqlog.conf

編輯 mysqlog.conf：

[[inputs.tailf]]
    # 填寫各種 MySQL 日志的文件路徑，不同版本可能不同
    # 注意，這里只支持文本文件。我們這里使用的版本是 MySQL 5.7.33
    
    logfiles = [
    "/var/lib/mysql/*.log",
    "/var/log/mysql/mysql.log",
    "/var/log/mysql/error.log",
    ]
    
    source = "mysqlog"
    
    # 指定服務名
    service = "mysqlog"
    
    # 專用的日志解析腳本（DataKit 已經內置了）
    pipeline = "mysql.p"
    
    [inputs.tailf.tags]
    # 這里可以添加一些標簽，比如：
    biz = "訂單系統"
    
    # 省略其它默認配置...

至此，MySQL 的日志采集就配置好了，重新啟動一下 DataKit 的即可（數據需要稍等一會才能在 Dataflux 平臺看到）

參考鏈接：《DataKit不同系統的重啟方式》

https://help.dataflux.cn/doc/0c6ebce225784bd2ad994d5f89c5dbc89e025792#toc27

接下來，我們就能在 DataFlux 平臺看到對應的日志了：

從日志詳情圖中，我們可以看到紅框標記的慢查詢 SQL 語句，另外還有其它提取出來的日志信息，比如查詢時間、來源、服務器主機名、請求發送的數據行數等信息。

除此之外，我們在拉出來的日志詳情中，還可看到當前這臺主機在慢日志產生的那個時間點附近（紅色虛豎線）的資源占用情況（諸如 CPU、內存、磁盤、網絡等信息），在一定程度上能幫助開發人員更好的解決問題。

至此，我們解決了 MySQL 慢日志的采集、解析以及展示問題。現在數據已經有了，開發人員就能方便的在網頁上找到對應的慢查詢日志，并且綜合 MySQL 服務器的整體資源占用情況，給出更加合理的解決方案。

以上便是今天我們針對MySQL慢日志查詢問題，提供的幾種解決辦法。在實際應用過程中，我們還是要多嘗試不同維度的解決方案，并結合自身所處行業、業務等特點，挑選適合自己和團隊使用的數據庫分析工具，保障系統和業務的穩定。

到此，關于“MySQL怎么快速定位慢查詢問題”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！