优化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-23JS4C29BV4-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.loglong_query_time = 2
Linux下启用MySQL慢查询MySQL在Windows系统中的配置文件一般是是my.cnf找到[mysqld]下面加上：log-slow-queries=/data/mysqldata/slowquery.loglong_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.gztar zvxf mysqlsla-2.03.tar.gzcd mysqlsla-2.03perl Makefile.PLmakemake installmysqlsla /data/mysqldata/slow.log 使用： [root@phpddt.com mysqlsla-2.03]# mysqlsla /var/lib/mysql/slow-queries.logAuto-detected logs as slow logsReportfor slow logs:/var/lib/mysql/slow-queries.log160 queries total,17 uniqueSortedby'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: stblogUsers: 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, 扫描的行数量. Database, 属于哪个数据库 Users, 哪个用户,IP, 占到所有用户执行的sql百分比 Query abstract, 抽象后的sql语句 Query sample, sql语句 原文链接： http://www.phpddt.com/db/mysql-slow-query.html转载于:https://www.cnblogs.com/zsmynl/p/3538611.html
