默认情况下mysql不记录慢查询日志，需要在启动的时候指定
bin\mysqld.exe - -slow-query-log
通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句。 show variables like ‘long_query_time’; set long_query_time=2;
配置慢查询相关设置
[mysqld]
slow_query_log=1
slow_query_log_file =
i:/myweb/mysql/lib/slowquery.log
long_query_time=0.001
log-queries-not-using-indexes
执行一个超过2秒操作，例如：select sleep（3）;日志会出现有问题的sql语句，可以根据这些sql进行修改和优化。
I:\myweb\mysql\bin\mysqld, Version: 5.5.20-log (MySQL Community Server
(GPL)). started with:
TCP Port: 3306, Named Pipe:
(null)
Time
Id Command Argument
# Time: 140412 15:57:52
# User@Host:
root[root] @ localhost [127.0.0.1]
# Query_time: 0.500029 Lock_time:
0.000000 Rows_sent: 1 Rows_examined: 0
use chinatupai;
SET
timestamp=1397289472;
select sleep(0.5);
# Time: 140412 15:58:15
# User@Host: root[root] @ localhost [127.0.0.1]
#
Query_time: 0.294017 Lock_time: 0.002001 Rows_sent: 6620
Rows_examined: 6620
SET timestamp=1397289495;
select title from
phphi_article;
# Time: 140412 16:04:53
# User@Host: root[root] @ localhost [127.0.0.1]
#
Query_time: 0.390001 Lock_time: 0.171601 Rows_sent: 10000
Rows_examined: 10000
SET timestamp=1397289893;
select*from db1.tb2 order
by id desc limit 10000;
I:\myweb\mysql\bin\mysqld, Version: 5.5.20-log (MySQL
Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe:
(null)
Time
Id Command Argument
# Time: 140412 16:08:37
# User@Host:
root[root] @ localhost [127.0.0.1]
# Query_time: 2.513605 Lock_time:
0.000000 Rows_sent: 1 Rows_examined: 0
use chinatupai;
SET
timestamp=1397290117;
select sleep(2.5);
I:\myweb\mysql\bin\mysqld,
Version: 5.5.20-log (MySQL Community Server (GPL)). started with:
TCP Port:
3306, Named Pipe:
(null)
Time
Id Command Argument
# Time: 140412 16:11:00
# User@Host:
root[root] @ localhost [127.0.0.1]
# Query_time: 2.508005 Lock_time:
0.000000 Rows_sent: 1 Rows_examined: 0
use chinatupai;
SET
timestamp=1397290260;
select sleep(2.5);