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<DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') and tmp.`status` in (1,2,4))
)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<DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') and tmp.`status` in (1,2,4))
)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<DATE_FORMAT(worker_order.create_time,'%Y-%m-%d') and tmp.`status` in (1,2,4))
)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
时间： 2024-11-24 07:40:47