开启Mysql慢查询来优化mysql

开启Mysql慢查询来优化mysql

优化sql语句是优化数据库的一个很重要的方面，那么怎么发现那些耗时耗资源的sql语句呢，开启Mysql慢查询!

1.查看是否开启慢查询，默认情况下是关闭的。你的mysql最好在5.1版本以上

mysql> show variables like "%slow%";

1. +---------------------+---------------------------------------------------------
2. ------------------+
3. |Variable_name|Value
4. |
5. +---------------------+---------------------------------------------------------
6. ------------------+
7. | log_slow_queries | OFF
8. |
9. | slow_launch_time |2
10. |
11. | slow_query_log | OFF
12. |
13. | slow_query_log_file | D:\software\wamp\wamp\bin\mysql\mysql5.5.20\data\WIN-23J
14. S4C29BV4-slow.log |
15. +---------------------+---------------------------------------------------------
16. ------------------+

2.命令行开启mysql慢查询：

1. mysql>setglobal slow_query_log=on;
2. Query OK,0 rows affected (0.09 sec)

或者修改mysql配置文件开启mysql慢查询：(记得重启mysql)

Windows下开启MySQL慢查询
MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上：
log-slow-queries = F:\MySQL\log\mysqlslowquery.log
long_query_time = 2

Linux下启用MySQL慢查询
MySQL在Windows系统中的配置文件一般是是my.cnf找到[mysqld]下面加上：
log-slow-queries=/data/mysqldata/slowquery.log
long_query_time=2

3.分析mysql慢查询日志：
mysql慢查询日志类似这样的：

1. /usr/local/mysql/libexec/mysqld,Version:5.1.26-rc-log (Source distribution). started with:
2. Tcp port:3306Unix socket:/tmp/mysql.sock
3. TimeIdCommandArgument
4. # Time: 100814 13:28:30
5. # User@Host: root[root] @ localhost []
6. # Query_time: 10.096500 Lock_time: 0.045791 Rows_sent: 1 Rows_examined: 2374192
7. SET timestamp=1281763710;
8. select count(distinct ad_code)as x from ad_visit_history where ad_code in(select ad_code from ad_list where media_id=15);
9. # Time: 100814 13:37:02
10. # User@Host: root[root] @ localhost []
11. # Query_time: 10.394134 Lock_time: 0.000091 Rows_sent: 1 Rows_examined: 2374192
12. SET timestamp=1281764222;
13. select count(distinct ad_code)as x from ad_visit_history where ad_code in(select ad_code from ad_list where media_id=15);
14. # Time: 100814 13:37:16
15. # User@Host: root[root] @ localhost []
16. # Query_time: 4.608920 Lock_time: 0.000078 Rows_sent: 1 Rows_examined: 1260544
17. SET timestamp=1281764236;
18. select count(*)as cou from ad_visit_history where ad_code in(select ad_code from ad_list where id=41) order by id desc;

看日志可以看出它记录了mysql慢查询语句的执行时间、锁定时间等等。
(1)用mysql自带的mysql自带的慢查询分析工具mysqldumpslow

参数可–help查看

 

# -s：排序方式。c , t , l , r 表示记录次数、时间、查询时间的多少、返回的记录数排序；

 

#  ac , at , al , ar 表示相应的倒叙；

 

# -t：返回前面多少条的数据；

 

# -g：包含什么，大小写不敏感的；

 

mysqldumpslow -s r -t 10  /slowquery.log     #slow记录最多的10个语句

 

mysqldumpslow -s t -t 10 -g "left join"  /slowquery.log     #按照时间排序前10中含有"left join"的

(2)第三方分析工具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

使用：

1. [root@phpddt.com mysqlsla-2.03]# mysqlsla /var/lib/mysql/slow-queries.log
2. Auto-detected logs as slow logs
3. Reportfor slow logs:/var/lib/mysql/slow-queries.log
4. 160 queries total,17 unique
5. Sortedby't_sum'
6. GrandTotals:Time1 s,Lock0 s,Rows sent 3.79k,RowsExamined380.47k
7.  
8.  
9. ______________________________________________________________________ 001 ___
10. Count:1(0.62%)
11. Time:1 s total,1 s avg,1 s to 1 s max (100.00%)
12. LockTime(s):0 total,0 avg,0 to 0 max (0.00%)
13. Rows sent :10 avg,10 to 10 max (0.26%)
14. Rows examined :10.46k avg,10.46k to 10.46k max (2.75%)
15. Database:
16. Users:
17. root@localhost :100.00%(1) of query,100.00%(160) of all users
18.  
19. Queryabstract:
20. SET timestamp=N; SELECT *,SUM(hits) AS SUM FROM typecho_ranks INNER JOIN typecho_contents ON typecho_contents.cid = typecho_ranks.cid WHERE (month(date)= month(now())) GROUP BY typecho_ranks.cid ORDER BY SUM(hits) DESC LIMIT N;
21.  
22. Query sample:
23. SET timestamp=1381248477;
24. SELECT *,sum(`hits`)as`sum` FROM typecho_ranks INNER JOIN typecho_contents ON typecho_contents.`cid`= typecho_ranks.`cid` WHERE (MONTH(`date`)= MONTH(now())) GROUP BY typecho_ranks.`cid` ORDER BY sum(`hits`) DESC LIMIT 10;
25.  
26. ______________________________________________________________________ 002 ___
27. Count:1(0.62%)
28. Time:0 total,0 avg,0 to 0 max (0.00%)
29. LockTime(s):0 total,0 avg,0 to 0 max (0.00%)
30. Rows sent :25 avg,25 to 25 max (0.66%)
31. Rows examined :25 avg,25 to 25 max (0.01%)
32. Database: stblog
33. Users:
34. root@localhost :100.00%(1) of query,100.00%(160) of all users
35.  
36. Queryabstract:
37. SELECT * FROM (settings);
38.  
39. Query sample:
40. SELECT *
41. FROM (`settings`);

分析格式如下：

总查询次数 (queries total), 去重后的sql数量 (unique)

输出报表的内容排序(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语句 

原文链接：http://www.phpddt.com/db/mysql-slow-query.html