mysql 优化2 慢查询

默认情况下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);