MySQL優化方案之開啟慢查詢日志

Posted on by

前言

本方案隻適應於小的項目、項目未上線或者緊急情況下可采用這種方式，一旦開啟慢日志查詢會增加數據庫的壓力，所以一般采用後臺對數據操作時間寫入日志文件中，每一周定時進行清除日志

mysql優化方案：開啟慢查詢日志（查詢sql執行超過一秒以上sql等等）

開啟慢查詢日志：可以讓MySQL記錄下查詢超過指定時間的語句，通過定位分析性能的瓶頸，才能更好的優化數據庫系統的性能。

參數說明：

slow_query_log 慢查詢開啟狀態，ON開啟，OFF關閉

slow_query_log_file 慢查詢日志存放的位置（這個目錄需要MySQL的運行帳號的可寫權限，一般設置為MySQL的數據存放目錄）

long_query_time 查詢超過多少秒才記錄

重點說明：開啟慢日志版本要高，低版本無法支持，本次版本是：5.7.20

SELECT VERSION();  查詢版本號

該版本默認情況下已開啟慢日志

mysql> show databases;

mysql> use test;   //指定一個數據庫

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name   | Value     |
+-----------------+-----------+
| slow_query_log | ON |
+-----------------+-----------+
| slow_query_log_file | YH-20161209QIZC-slow.log |
+-----------------+-----------+
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

//默認查詢超過10秒才記錄

設置慢查詢日志

方法一：全局變量設置(該方式數據庫重啟全部失效，得重新配置)

將 slow_query_log 全局變量設置為“ON”狀態

mysql> set global slow_query_log='ON';

設置慢查詢日志存放的位置

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log'; //linux
mysql> set global slow_query_log_file='D:\\mysq\data\slow.log';         //windows

設置查詢超過1秒就記錄（如果有時候用命令不起作用，那麼可以關閉再打開）

mysql> set global long_query_time=1;

方法二：配置文件設置（服務器重啟不影響）

修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log     //linux
long_query_time = 1

3.重啟MySQL服務

service mysqld restart

測試

1.執行一條慢查詢SQL語句

mysql> select sleep(2);

2.查看是否生成慢查詢日志

這裡可以看出是那個sql以及查詢時間

如果日志存在，MySQL開啟慢查詢設置成功！ 

附：日志分析工具mysqldumpslow

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

查看mysqldumpslow的幫助信息：

[root@DB-Server ~]# mysqldumpslow --help

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

 

Parse and summarize the MySQL slow query log. Options are

 

  --verbose    verbose

  --debug      debug

  --help       write this text to standard output

 

  -v           verbose

  -d           debug

  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default

                al: average lock time

                ar: average rows sent

                at: average query time

                 c: count

                 l: lock time

                 r: rows sent

                 t: query time  

  -r           reverse the sort order (largest last instead of first)

  -t NUM       just show the top n queries

  -a           don't abstract all numbers to N and strings to 'S'

  -n NUM       abstract numbers with at least n digits within names

  -g PATTERN   grep: only consider stmts that include this string

  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),

               default is '*', i.e. match all

  -i NAME      name of server instance (if using mysql.server startup script)

  -l           don't subtract lock time from total time

-s, 是表示按照何種方式排序，

  • c: 訪問計數
  • l: 鎖定時間
  • r: 返回記錄
  • t: 查詢時間
  • al:平均鎖定時間
  • ar:平均返回記錄數
  • at:平均查詢時間

-t, 是top n的意思，即為返回前面多少條的數據；

-g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；

比如

得到返回記錄集最多的10個SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到訪問次數最多的10個SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照時間排序的前10條裡面含有左連接的查詢語句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建議在使用這些命令時結合 | 和more 使用 ，否則有可能出現刷屏的情況。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

總結

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

推薦閱讀：