詳細聊聊MySQL中慢SQL優化的方向

Posted on by

前言

影響一個系統的運行速度的原因有很多，是多方面的，甚至可能是偶然性的，或前端，或後端，或數據庫，或中間件，或服務器，或網絡等等等等，真正的去定位一個問題需要對系統有一定的認知，可以根據自身的判斷去縮小問題范圍。

今天不說其他的優化，單獨把數據庫的優化拿出來說幾個優化方向。

跟系統的優化方向一樣，數據庫的優化，同樣也是多方面的，其中涵蓋著SQL語句的執行情況，數據庫自身的情況等等，下面我們就來說一下MySQL數據庫中的慢SQL語句優化方向，希望也能給到大傢一些優化思路。

SQL語句優化

SQL語句的優化，有很多文章說起，也有很多在SQL編寫上的指導；但是那種隻能支持基本開發，如果要排查問題，那就不能單單的隻是停留在SQL編寫上瞭，而是有一個整體的發現問題的流程。

本次優化方向，大概分為發現慢查詢SQL，查看並解析SQL執行計劃，SQL編寫上的優化，索引優化等幾個方面。

記錄慢查詢SQL

MySQL中記錄慢查詢SQL是可以利用MySQL內部配置來實現的，這個配置就是slow_query_log配置。

可利用show variables like ‘%query%’;查詢出以下三個相關結果。

long_query_time     | 1.00000
slow_query_log      | off
slow_query_log_file | /data/mysql/mysql_slow.log

解釋一下這三個參數，

  • long_query_time：如何區分SQL查詢是慢查詢，就要規定一個查詢時間，超過這個時間的就歸類於慢查詢，此參數就是來設置時間范圍的；以秒為單位，可以設置小數。
  • slow_query_log：此參數為是否開啟記錄慢查詢SQL的開關，兩個選擇，on或者off，默認為off，所以在這裡我們就知道如果要開啟慢查詢SQL記錄，需要手動設置開啟。
  • slow_query_log_file：慢查詢SQL日志的文件路徑，可以自行指定。

如何修改配置

有兩個方法。

其一：修改my.ini或者是my.cnf文件，將此三項配置進行一個配置。

其二：直接在sqlplus中，使用set語法來修改參數，但是重啟mysql數據庫後就會失效，sql如下：

set global long_query_time = 10;

set global slow_query_log = on;

set global slow_query_log_file = /data/mysql/mysql_slow.log;

因為這個方法會重啟失效，所以還是建議使用第一種方式。

查看慢查詢日志

如何查詢慢查詢日志呢，如果量很小的情況下，其實是不需要使用工具的，完全可以直接打開即可。

如果量比較大，就需要mysqldumpslow工具查詢會更方便。

mysqldumpslow是和mysqld相同類型的執行腳本，可以直接在命令行中執行，具體的使用方法如下：

mysqldumpslow參數：

-s，是order的順序
—–al 平均鎖定時間
—–ar 平均返回記錄時間
—–at 平均查詢時間（默認）
—–c 計數
—–l 鎖定時間
—–r 返回記錄
—–t 查詢時間

-t，top，即為返回前面多少條的數據
-g，自定義正則表達式

舉個例子，如下：

mysqldumpslow -s r -t 5 /data/mysql/mysql_slow.log

查詢出返回記錄集最多的5個慢查詢SQL。

更多用法之後我建個測試庫單獨寫篇文章細說一下。

查看SQL執行計劃

查看執行計劃關鍵詞：EXPLAIN

如何使用

就是直接執行 EXPLAIN SELECT * FROM TABLE_NAME;

這個一開始我是打算簡單說一下的，後來發現篇幅太長瞭，這個留待下篇文章裡，感謝理解。

SQL編寫優化

SQL的編寫優化就很多瞭，我這裡也整理出瞭一些，請大傢自行查漏補缺。

  • 查詢語句無論是使用哪種判斷條件 等於、小於、大於, where左側的條件查詢字段不要使用函數或者表達式。
  • 不要直接使用select *，而應該使用具體需要查詢的表字段；select * 使用的是全表掃描，不會走索引的。
  • 避免在 WHERE 字句中對字段進行 NULL 判斷。
  • 避免在 WHERE 中使用 != 或 <> 操作符。
  • 使 用 BETWEEN AND 替代 IN。
  • 為常用搜索條件創建索引
  • 選擇正確的存儲引擎, InnoDB 、MyISAM 、MEMORY 等，不同的場景下使用不同的存儲引擎會有更好的效果。
  • 使用 like %123% 不會走索引, 而使用 like 123% 會走索引。非常重要！！！
  • 選擇合適的字段類型。
  • 設計字段時，要盡量使用NOT NULL。

為何要對慢SQL進行治理

從數據庫角度看：每個SQL執行都需要消耗一定I/O資源，SQL執行的快慢，決定資源被占用時間的長短。假設總資源是100，有一條慢SQL占用瞭30的資源共計1分鐘。那麼在這1分鐘時間內，其他SQL能夠分配的資源總量就是70，如此循環，當資源分配完的時候，所有新的SQL執行將會排隊等待。

從應用的角度看：SQL執行時間長意味著等待，在OLTP應用當中，用戶的體驗較差

治理的優先級上

  • master數據庫->slave數據庫
    • 目前數據庫基本上都是讀寫分離架構，讀在從庫（slave）上執行，寫在主庫（master）上執行。
    • 由於從庫的數據都是從主庫上復制過去的，主庫等待較多的，會加大與從庫的復制時延。
  • 執行次數多的SQL優先治理
  • 如果有一類SQL高並發集中訪問某一張表，應當優先治理。

總結

這裡面遠遠還沒有講全，還有很多種編寫規則，同時還有索引的建立並沒有聊，留給大傢一些自己看書的時間，希望大傢有所進步。

到此這篇關於MySQL中慢SQL優化方向的文章就介紹到這瞭,更多相關MySQL慢SQL優化方向內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：