学习之前先要了解什么是慢日志, 为什么要开启慢日志?
MySQL 的慢日志是 MySQL 提供的一种日志记录. 用来记录在 MySQL 运行的时候, 响应超过阈值的语句. 具体指运行时间超过 long_query_time 值的 sql, 就会被记录到慢日志中. long_query_time 的值默认是 10 秒. 默认的情况下, MySQL 的慢日志默认是不开启的. 如果不需要调优的话, 一般不建议开启. 因为慢日志或多或少会带来一些性能的影响. 慢日志可以写入到文件中, 也可以写到数据库的表中.
慢日志相关的配置参数:
slow_query_log: 是否开启慢日志, 0 表示不开启, 1 表示开启.
long_slow_queries: 是旧版的 (5.6 以下的版本) 是 MySQL 慢日志的存储 路径, 可以不设置参数, 系统会给一个默认的缺省文件 host_name_slow.log
slow_query_log_file:(5.6 以上的版本), 是 MySQL 慢日志的存储 路径, 可以不设置参数, 系统会给一个默认的缺省文件 host_name_slow.log
long_query_time: 慢查询阈值, 将查询时间超过阈值的, 记录到日志中. l
log_queries_not_using_indexes: 没有使用索引的语句也记录到日志中
log_output: 日志的存储格式. log_output='FILE'表示将日志存入文件, 默认值是'FILE'.log_output='TABLE'表示将日志存入数据库, 这样日志信息就会被写入到 MySQL.slow_log 表中.
MySQL 数据库支持同时两种日志存储方式, 配置的时候以逗号隔开即可, 如: log_output='FILE,TABLE'.
日志记录到系统的专用日志表中, 要比记录到文件耗费更多的系统资源, 因此对于需要启用慢查询日志, 又需要能够获得更高的系统性能, 那么建议优先记录到文件.
慢日志查询:
默认的情况下, slow_qiery_log 的值为 OFF, 表示慢日志的查询是禁止的, 可以通过 slow_query_log 的值来设置, 如下
修改 my.cnf 文件, 增加或修改参数 slow_query_log 和 slow_query_log_file 后, 然后重启 MySQL 服务器, 如下所示:
- slow_query_log =1
- slow_query_log_file=/usr/local/MySQL/data/localhost-slow.log
- MySQL> show variables like 'slow_query%';
- +---------------------+---------------------+
- | Variable_name | Value |
- +---------------------+---------------------+
- | slow_query_log | ON |
- | slow_query_log_file | /usr/local/MySQL/data/localhost-slow.log |
- +---------------------+---------------------+
- rows in set (0.00 sec)
- MySQL>
- # 慢查询的参数 slow_query_log_file , 它指定慢查询日志文件的存放路径, 系统默认会给一个缺省的文件 host_name-slow.log
那么开启了慢查询日志后, 什么样的 SQL 才会记录到慢查询日志里面呢? 这个是由参数 long_query_time 控制, 默认情况下 long_query_time 的值为 10 秒, 可以使用命令修改, 也可以在 my.cnf 参数里面修改. 关于运行时间正好等于 long_query_time 的情况, 并不会被记录下来. 也就是说, 在 MySQL 源码里是判断大于 long_query_time, 而非大于等于. 从 MySQL 5.1 开始, long_query_time 开始以微秒记录 SQL 语句运行时间, 之前仅用秒为单位记录. 如果记录到表里面, 只会记录整数部分, 不会记录微秒部分.
如上所示, 我修改了变量 long_query_time, 但是查询变量 long_query_time 的值还是 10, 难道没有修改到呢? 注意: 使用命令 set global long_query_time=4 修改后, 需要重新连接或新开一个会话才能看到修改值. 你用 show variables like 'long_query_time'查看是当前会话的变量值, 你也可以不用重新连接会话, 而是用 show global variables like 'long_query_time'; 如下所示:
- MySQL> show variables like 'long_query_time';
- +-----------------+-----------+
- | Variable_name | Value |
- +-----------------+-----------+
- | long_query_time | 10.000000 |
- +-----------------+-----------+
- row in set (0.00 sec)
- MySQL>
- MySQL> show global variables like 'long_query_time';
- +-----------------+----------+
- | Variable_name | Value |
- +-----------------+----------+
- | long_query_time | 4.000000 |
- +-----------------+----------+
- row in set (0.00 sec)
- MySQL>
log_output 参数是指定日志的存储方式. log_output='FILE'表示将日志存入文件, 默认值是'FILE'.log_output='TABLE'表示将日志存入数据库, 这样日志信息就会被写入到 MySQL.slow_log 表中. MySQL 数据库支持同时两种日志存储方式, 配置的时候以逗号隔开即可, 如: log_output='FILE,TABLE'. 日志记录到系统的专用日志表中, 要比记录到文件耗费更多的系统资源, 因此对于需要启用慢查询日志, 又需要能够获得更高的系统性能, 那么建议优先记录到文件.
- MySQL> show variables like '%log_output%';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | log_output | TABLE |
- +---------------+-------+
- 1 row in set (0.00 sec)
- MySQL>
系统变量 log-queries-not-using-indexes: 未使用索引的查询也被记录到慢查询日志中(可选项). 如果调优的话, 建议开启这个选项. 另外, 开启了这个参数, 其实使用 full index scan 的 sql 也会被记录到慢查询日志.
- MySQL> show variables like 'log_queries_not_using_indexes';
- +-------------------------------+-------+
- | Variable_name | Value |
- +-------------------------------+-------+
- | log_queries_not_using_indexes | OFF |
- +-------------------------------+-------+
- 1 row in set (0.00 sec)
- MySQL> set global log_queries_not_using_indexes=1;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> show variables like 'log_queries_not_using_indexes';
- +-------------------------------+-------+
- | Variable_name | Value |
- +-------------------------------+-------+
- | log_queries_not_using_indexes | ON |
- +-------------------------------+-------+
- 1 row in set (0.00 sec)
- MySQL>
系统变量 log_slow_admin_statements 表示是否将慢管理语句例如 ANALYZE TABLE 和 ALTER TABLE 等记入慢查询日志
- MySQL> show variables like 'log_slow_admin_statements';
- +---------------------------+-------+
- | Variable_name | Value |
- +---------------------------+-------+
- | log_slow_admin_statements | OFF |
- +---------------------------+-------+
- row in set (0.00 sec)
- MySQL>
另外, 如果你想查询有多少条慢查询记录, 可以使用系统变量.
- MySQL> show global status like '%slow_queries%';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Slow_queries | 0 |
- +---------------+-------+
- row in set (0.00 sec)
- MySQL>
日志分析工具 mysqldumpslow
在实际生产环境中, 如果要手工分析日志, 查找, 分析 SQL, 显然是个体力活, MySQL 提供了日志分析工具 mysqldumpslow
查看 mysqldumpslow 的帮助信息:
- [root@localhost~]# 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
- mysqldumpslow --help
- View Code
-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
参考文档: https://www.cnblogs.com/saneri/p/6656161.html
来源: http://www.bubuko.com/infodetail-2998417.html