MySQL优化方案之开启慢查询日志

吾爱主题阅读：181 2024-04-02 18:53:43 评论：0

前言

本方案只适应于小的项目、项目未上线或者紧急情况下可采用这种方式，一旦开启慢日志查询会增加数据库的压力，所以一般采用后台对数据操作时间写入日志文件中，每一周定时进行清除日志

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'` `; //linux` `mysql>` `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 = ON slow_query_log_file = /usr/ local /mysql/data/slow.log //linux long_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 --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