优化sql语句是优化数据库的一个很重要的方面，那么怎么发现那些耗时耗资源的sql语句呢，开启Mysql慢查询!
1.查看是否开启慢查询，默认情况下是关闭的。你的mysql最好在5.1版本以上
mysql> show variables like "%slow%";
- +---------------------+---------------------------------------------------------
- ------------------+
- |Variable_name|Value
- |
- +---------------------+---------------------------------------------------------
- ------------------+
- | log_slow_queries | OFF
- |
- | slow_launch_time |2
- |
- | slow_query_log | OFF
- |
- | slow_query_log_file | D:\software\wamp\wamp\bin\mysql\mysql5.5.20\data\WIN-23J
- S4C29BV4-slow.log |
- +---------------------+---------------------------------------------------------
- ------------------+
2.命令行开启mysql慢查询：
- mysql>setglobal slow_query_log=on;
- 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慢查询日志类似这样的：
- /usr/local/mysql/libexec/mysqld,Version:5.1.26-rc-log (Source distribution). started with:
- Tcp port:3306Unix socket:/tmp/mysql.sock
- TimeIdCommandArgument
- # Time: 100814 13:28:30
- # User@Host: root[root] @ localhost []
- # Query_time: 10.096500 Lock_time: 0.045791 Rows_sent: 1 Rows_examined: 2374192
- SET timestamp=1281763710;
- 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);
- # Time: 100814 13:37:02
- # User@Host: root[root] @ localhost []
- # Query_time: 10.394134 Lock_time: 0.000091 Rows_sent: 1 Rows_examined: 2374192
- SET timestamp=1281764222;
- 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);
- # Time: 100814 13:37:16
- # User@Host: root[root] @ localhost []
- # Query_time: 4.608920 Lock_time: 0.000078 Rows_sent: 1 Rows_examined: 1260544
- SET timestamp=1281764236;
- 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
安装：
- wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
- tar zvxf mysqlsla-2.03.tar.gz
- cd mysqlsla-2.03
- perl Makefile.PL
- make
- make install
- mysqlsla /data/mysqldata/slow.log
使用：
- [root@phpddt.com mysqlsla-2.03]# mysqlsla /var/lib/mysql/slow-queries.log
- Auto-detected logs as slow logs
- Reportfor slow logs:/var/lib/mysql/slow-queries.log
- 160 queries total,17 unique
- Sortedby't_sum'
- GrandTotals:Time1 s,Lock0 s,Rows sent 3.79k,RowsExamined380.47k
- ______________________________________________________________________ 001 ___
- Count:1(0.62%)
- Time:1 s total,1 s avg,1 s to 1 s max (100.00%)
- LockTime(s):0 total,0 avg,0 to 0 max (0.00%)
- Rows sent :10 avg,10 to 10 max (0.26%)
- Rows examined :10.46k avg,10.46k to 10.46k max (2.75%)
- Database:
- Users:
- root@localhost :100.00%(1) of query,100.00%(160) of all users
- Queryabstract:
- 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;
- Query sample:
- SET timestamp=1381248477;
- 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;
- ______________________________________________________________________ 002 ___
- Count:1(0.62%)
- Time:0 total,0 avg,0 to 0 max (0.00%)
- LockTime(s):0 total,0 avg,0 to 0 max (0.00%)
- Rows sent :25 avg,25 to 25 max (0.66%)
- Rows examined :25 avg,25 to 25 max (0.01%)
- Database: stblog
- Users:
- root@localhost :100.00%(1) of query,100.00%(160) of all users
- Queryabstract:
- SELECT * FROM (settings);
- Query sample:
- SELECT *
- 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, 扫描的行数量.
Rows examined, 扫描的行数量.
Database, 属于哪个数据库
Users, 哪个用户,IP, 占到所有用户执行的sql百分比
Query abstract, 抽象后的sql语句
Query sample, sql语句