前言
本方案只適應于小的項目、項目未上線或者緊急情況下可采用這種方式，一旦開啟慢日志查詢會增加數據庫的壓力，所以一般采用后臺對數據操作時間寫入日志文件中，每一周定時進行清除日志
mysql優化方案：開啟慢查詢日志（查詢sql執行超過一秒以上sql等等）
開啟慢查詢日志：可以讓MySQL記錄下查詢超過指定時間的語句，通過定位分析性能的瓶頸，才能更好的優化數據庫系統的性能。
參數說明：
slow_query_log 慢查詢開啟狀態，ON開啟，OFF關閉
slow_query_log_file 慢查詢日志存放的位置（這個目錄需要MySQL的運行帳號的可寫權限，一般設置為MySQL的數據存放目錄）
long_query_time 查詢超過多少秒才記錄
重點說明：開啟慢日志版本要高，低版本無法支持，本次版本是：5.7.20
SELECT VERSION(); 查詢版本號
該版本默認情況下已開啟慢日志
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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”狀態
|
1
|
mysql> set global slow_query_log='ON'; |
設置慢查詢日志存放的位置
|
1
2
|
mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log'; //linuxmysql> set global slow_query_log_file='D:\\mysq\data\slow.log'; //windows |
設置查詢超過1秒就記錄（如果有時候用命令不起作用，那么可以關閉再打開）
|
1
|
mysql> set global long_query_time=1; |
方法二：配置文件設置（服務器重啟不影響）
修改配置文件my.cnf，在[mysqld]下的下方加入
|
1
2
3
4
|
[mysqld]slow_query_log = ONslow_query_log_file = /usr/local/mysql/data/slow.log //linuxlong_query_time = 1 |
3.重啟MySQL服務
|
1
|
service mysqld restart |
測試
1.執行一條慢查詢SQL語句
mysql> select sleep(2);
2.查看是否生成慢查詢日志
這里可以看出是那個sql以及查詢時間
如果日志存在，MySQL開啟慢查詢設置成功！
附：日志分析工具mysqldumpslow
在生產環境中，如果要手工分析日志，查找、分析SQL，顯然是個體力活，MySQL提供了日志分析工具mysqldumpslow
查看mysqldumpslow的幫助信息：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
[root@DB-Server ~]# mysqldumpslow --helpUsage: 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開啟慢查詢日志內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://blog.csdn.net/qq_39291929/article/details/81561388
