Mysql慢查询使用与参数详解

慢查询是mysql中一个非常重要的日志了,我们可以通过mysql慢查询日志来分析具体导致mysql查询慢的原因与sql语子,下面就和小编来简单的学习一下.

慢查询为系统中查询时间超过long_query_time的值,分析慢查询是优化SQL的基础,默认清下Mysql慢查询为关闭状态,可以通过:

show variables where Variable_name = 'log_slow_queries';

来查看是否开启,如果为OFF则需要修改mysql配置文件,在mysqld下面增加以下参数.

1. #慢查询日志地址,需要mysql运行帐号对该目录有写权限 
2. log-slow-queries="/log/slow.log" 
3. #当query语句大于2s时记录慢查询日志 
4. long_query_time=2 
5. #没有使用索引的query也计入慢查询日志（可根据情况增加） 
6. log-queries-not-using-indexes 

慢查询分析

可以使用mysql自带的mysqldumpslow来进行分析,该命令可带3个参数.

-s,是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar，表示相应的倒叙.

-t,是top n的意思,即为返回前面多少条的数据.

-g,后边可以写一个正则匹配模式,大小写不敏感的.

mysqldum<a href="/fw/photo.html" target="_blank">ps</a>low -s t -t 10 -g "left join" /alidata/log/mysql/slow.log

mysql运行状态和变量查看

通过查看mysql的status和variables来优化mysql

1. mysql> show variables like '%slow%'; 
2. +---------------------+-----------------------------+ 
3. | Variable_name       | Value                       | 
4. +---------------------+-----------------------------+ 
5. | log_slow_queries    | ON                          | 
6. | slow_launch_time    | 2                           | 
7. | slow_query_log      | ON                          | 
8. | slow_query_log_file | /alidata/log/mysql/slow.log | 
9. +---------------------+-----------------------------+ 
10. 4 rows in set (0.00 sec) --Vevb.com 
11. mysql> show global status like '%slow%'; 
12. +---------------------+-------+ 
13. | Variable_name       | Value | 
14. +---------------------+-------+ 
15. | Slow_launch_threads | 4     | 
16. | Slow_queries        | 3     | 
17. +---------------------+-------+ 
18. 2 rows in set (0.00 sec) 

2、连接数查看，max_connections为允许的最大连接数,Max_used_connections 系统中出现过的最大连接数,据说理想的设置是.

1. Max_used_connections / max_connections * 100% ≈ 85% 
2. mysql> show variables like '%connections%'; 
3. +----------------------+-------+ 
4. | Variable_name        | Value | 
5. +----------------------+-------+ 
6. | max_connections      | 2000  | 
7. | max_user_connections | 0     | 
8. +----------------------+-------+ 
9. 2 rows in set (0.00 sec) 
10. mysql> show global status like '%connections%'; 
11. +----------------------+-------+ 
12. | Variable_name        | Value | 
13. +----------------------+-------+ 
14. | Connections          | 35049 | 
15. | Max_used_connections | 12    | 
16. +----------------------+-------+ 
17. 2 rows in set (0.00 sec) 

分析日志?mysqldumpslow

分析日志,可用mysql提供的mysqldumpslow,使用很简单,参数可?help查看.

1. # -s：排序方式。c , t , l , r 表示记录次数、时间、查询时间的多少、返回的记录数排序； 
2. #                             ac , at , al , ar 表示相应的倒叙； 
3. # -t：返回前面多少条的数据； 
4. # -g：包含什么，大小写不敏感的； 
5. mysqldumpslow -s r -t 10  /slowquery.log     #slow记录最多的10个语句 
6. mysqldumpslow -s t -t 10 -g "left join"  /slowquery.log     #按照时间排序前10中含有"left join"的 

推荐用分析日志工具?mysqlsla

1. wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz 
2. tar zvxf mysqlsla-2.03.tar.gz 
3. cd mysqlsla-2.03 
4. perl Makefile.PL 
5. make 
6. make install 
7. mysqlsla /data/mysqldata/slow.log 
8. # mysqlsla会自动判断日志类型,为了方便可以建立一个配置文件“~/.mysqlsla” 
9. # 在文件里写上:top=100,这样会打印出前100条结果. 

说明:

queries total:总查询次数 unique:去重后的sql数量

sorted by:输出报表的内容排序

最重大的慢sql统计信息,包括 平均执行时间,等待锁时间,结果行的总数,扫描的行总数.

Count,sql的执行次数及占总的slow log数量的百分比.

Time,执行时间,包括总时间,平均时间,最小,最大时间,时间占到总慢sql时间的百分比.

95% of Time,去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.

Lock Time,等待锁的时间.

95% of Lock,95%的慢sql等待锁时间.

Rows sent,结果行统计数量,包括平均,最小,最大数量.

Rows examined,扫描的行数量.

Database,属于哪个数据库

Users,哪个用户,IP,占到所有用户执行的sql百分比

Query abstract,抽象后的sql语句

Query sample,sql语句