mysql慢查询日志可以删掉_MySql慢查询日志——开启/查看/删除

1,开启慢查询日志

修改mysql.ini文件，加入如下配置：

[mysqld]

log-slow-queries=H:\mysql_log\slow_query.log

long-query-time=3

set global slow_query_log=ON;

set global slow_launch_time=5;

show variables like "%slow%";

设置查询日志的存储位置，慢查询时间。

show variables like '%slow%'; /*查看慢查询配置*/

show variables like "long_query_time"; /*查看慢查询时间*/

show status like "%slow_queries%";/*查看慢查询配置情况*/

2，查看慢查询

打开log文件：

F:\mysql\bin\mysqld, Version: 5.5.24-log (MySQL Community Server (GPL)). started with:

TCP Port: 3306, Named Pipe: (null)

Time Id Command Argument

# Time: 161008 21:20:02

# User@Host: root[root] @ localhost [127.0.0.1]

# Query_time: 8.718499 Lock_time: 0.001002 Rows_sent: 0 Rows_examined: 908972

use energy;

SET timestamp=1475932802;

/*结束时间*/

select

DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') as create_time,/*创建时间*/

(select count(0)

from biz_work_order tmp

where

DATE_FORMAT(tmp.create_time,'%Y-%m-%d')=DATE_FORMAT(worker_order.create_time,'%Y-%m-%d')

or (tmp.create_time

)as should_change_worker_order

from biz_work_order worker_order

WHERE

(CASE WHEN IFNULL(@belongCity,'0')='0' OR @belongCity='' THEN 1 ELSE

CAST(LOCATE(@belongCity,worker_order.belong_city) AS SIGNED )

END)=1

AND

(CASE WHEN IFNULL(@energyStation,'0')='0' THEN 1 ELSE

CAST(LOCATE(@energyStation,worker_order.repository_id) AS SIGNED )

END)=1

AND

(CASE WHEN IFNULL(@serviceStatioin,'0')='0' THEN 1 ELSE

CAST(LOCATE(@serviceStatioin,worker_order.station_id) AS SIGNED )

END)=1

AND

DATE_FORMAT(worker_order.create_time, '%Y-%m-%d') BETWEEN DATE_FORMAT(cast(@beginDateTime as datetime), '%Y-%m-%d') and DATE_FORMAT(cast(@endDateTime as datetime), '%Y-%m-%d')

GROUP BY DATE_FORMAT(worker_order.create_time,'%Y-%m-%d');

# Time: 161008 21:20:40

# User@Host: root[root] @ localhost [127.0.0.1]

# Query_time: 33.695952 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 3239812

SET timestamp=1475932840;

/*结束时间*/

select

DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') as create_time,/*创建时间*/

(select count(0)

from biz_work_order tmp

where

DATE_FORMAT(tmp.create_time,'%Y-%m-%d')=DATE_FORMAT(worker_order.create_time,'%Y-%m-%d')

or (tmp.create_time

)as should_change_worker_order

from biz_work_order worker_order

WHERE

(CASE WHEN IFNULL(@belongCity,'0')='0' OR @belongCity='' THEN 1 ELSE

CAST(LOCATE(@belongCity,worker_order.belong_city) AS SIGNED )

END)=1

AND

(CASE WHEN IFNULL(@energyStation,'0')='0' THEN 1 ELSE

CAST(LOCATE(@energyStation,worker_order.repository_id) AS SIGNED )

END)=1

AND

(CASE WHEN IFNULL(@serviceStatioin,'0')='0' THEN 1 ELSE

CAST(LOCATE(@serviceStatioin,worker_order.station_id) AS SIGNED )

END)=1

AND

DATE_FORMAT(worker_order.create_time, '%Y-%m-%d') BETWEEN DATE_FORMAT(cast(@beginDateTime as datetime), '%Y-%m-%d') and DATE_FORMAT(cast(@endDateTime as datetime), '%Y-%m-%d')

GROUP BY DATE_FORMAT(worker_order.create_time,'%Y-%m-%d');

# Time: 161008 21:21:49

# User@Host: root[root] @ localhost [127.0.0.1]

# Query_time: 48.615826 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 4920668

SET timestamp=1475932909;

/*结束时间*/

select

DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') as create_time,/*创建时间*/

(select count(0)

from biz_work_order tmp

where

DATE_FORMAT(tmp.create_time,'%Y-%m-%d')=DATE_FORMAT(worker_order.create_time,'%Y-%m-%d')

or (tmp.create_time

)as should_change_worker_order

from biz_work_order worker_order

WHERE

(CASE WHEN IFNULL(@belongCity,'0')='0' OR @belongCity='' THEN 1 ELSE

CAST(LOCATE(@belongCity,worker_order.belong_city) AS SIGNED )

END)=1

AND

(CASE WHEN IFNULL(@energyStation,'0')='0' THEN 1 ELSE

CAST(LOCATE(@energyStation,worker_order.repository_id) AS SIGNED )

END)=1

AND

(CASE WHEN IFNULL(@serviceStatioin,'0')='0' THEN 1 ELSE

CAST(LOCATE(@serviceStatioin,worker_order.station_id) AS SIGNED )

END)=1

AND

DATE_FORMAT(worker_order.create_time, '%Y-%m-%d') BETWEEN DATE_FORMAT(cast(@beginDateTime as datetime), '%Y-%m-%d') and DATE_FORMAT(cast(@endDateTime as datetime), '%Y-%m-%d')

GROUP BY DATE_FORMAT(worker_order.create_time,'%Y-%m-%d');

# Time: 161008 21:22:37

# User@Host: root[root] @ localhost [127.0.0.1]

# Query_time: 41.309375 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 6050268

SET timestamp=1475932957;

call Pro_query_work_order_report('50010',NULL,270,date_sub(curdate(), INTERVAL 6 DAY),curdate());

# Time: 161008 21:23:59

# User@Host: root[root] @ localhost [127.0.0.1]

# Query_time: 79.538506 Lock_time: 0.000000 Rows_sent: 7 Rows_examined: 12282968

SET timestamp=1475933039;

call Pro_query_work_order_report('50010',NULL,270,date_sub(curdate(), INTERVAL 6 DAY),curdate());

被自己写的SQL慢哭了。。。

三，删除慢查询日志

直接delete掉log,删除后需要执行下面两句中的任意一句，重新生成日志文件：

-> mysqladmin -u root-p flush-logs

-> flush logs