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

• 前言
  
• 设置慢查询日志
  
• 测试
  
• 附：日志分析工具mysqldumpslow
  
• 总结
  

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秒才记录

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

mysql> set global long_query_time=1;

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

service mysqld restart

[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

• c: 访问计数
  
• l: 锁定时间
  
• r: 返回记录
  
• t: 查询时间
  
• al:平均锁定时间
  
• ar:平均返回记录数
  
• at:平均查询时间
  

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

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

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

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