MySQL之SQL優化詳解（一）

丁可樂發表於2019-06-20
MySql優化

目錄

 
序言： 在我面試很多人的過程中，很多人談到SQL優化都頭頭是道，建索引，explain分析，like全模糊會導致索引失效 云云，於是我問道：優化之前，需要找出資料庫中比如超過2s的慢SQL，你是怎麼找的？很多人答不上來，要是找都找不到，怎麼去優化呢，一箇中大型系統可能成千上萬條SQL都不過分，難道要一個個去分析麼。

所以今天和大家聊聊 慢SQL的挖掘機-慢查詢日誌

 

慢查詢日誌

MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。

當然，如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日誌會或多或少帶來一定的效能影響。

 

1. 慢查詢日誌開啟

  • 檢視是否開啟： show variables like '%slow_query_log%';
  • 開啟慢查詢日誌：set global slow_query_log=1; (重啟會失效)

 

開啟了慢查詢日誌後，什麼樣的SQL才會記錄到查詢日誌裡面？

這個是由引數long_query_time控制，預設情況下long_query_time的值為10秒

檢視命令： show variables like 'long_query_time%';

 
MySQL之SQL優化詳解（一）

注： 永久設定慢查詢日誌開啟，以及設定慢查詢日誌時間臨界點（不建議）

linux中，mysql配置檔案一般預設在 /etc/my.cnf 更改對應引數即可

 

2. 慢查詢日誌設定與檢視

  • 設定閥值命令： set global long_query_time=3 （修改為閥值到3秒鐘的就是慢sql）

 

為什麼設定後看不出變化：

  • 需要重新連線或新開一個會話才能看到修改值。 show variables like 'long_query_time%';
  • 直接 show global variables like 'long_query_time';

 

檢視慢查詢日誌：

cat -n /data/mysql/mysql-slow.log

MySQL之SQL優化詳解（一）

從慢查詢日誌中，我們可以看到每一條查詢時間高於3s 的sql語句，並可以看到執行的時間是多少。

比如上面，就表示 sql語句  select * from comic where comic_id < 1952000;  執行時間為3.902864秒，超出了我們設定的慢查詢時間臨界點3s，所以被記錄下來了

 

MySQL之SQL優化詳解（一）

檢視有多少條慢查詢記錄: show global status like '%Slow_queries%';

 

3.日誌分析工具mysqldumpslow

在生產環境中，如果要手工分析日誌，查詢、分析SQL，顯然是個體力活，MySQL提供了日誌分析工具 mysqldumpslow

  • s: 是表示按照何種方式排序
  • c: 訪問次數
  • l: 鎖定時間
  • r: 返回記錄
  • t: 查詢時間
  • al:平均鎖定時間
  • ar:平均返回記錄數
  • at:平均查詢時間
  • t:即為返回前面多少條的資料
  • g:後邊搭配一個正則匹配模式，大小寫不敏感的

 

工作常用參考:

  1. 得到返回記錄集最多的10個SQL: mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log

  2. 得到訪問次數最多的10個SQL: mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log

  3. 得到按照時間排序的前10條裡面含有左連線的SQL: mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql-slow.log

     

建議: 為方便 可以結合 | 和 more 使用，否則可能出現爆屏

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

相關文章