mysql使用slow log
1.运行环境:xampp1.7.3,mysql版本为5.1.412.开启slow log:
(1)打开d:/xampp/mysql/bin/my.ini,设置
slow_query_log = 1
slow_query_log_file = "d:/xampp/mysql/data/mysql-slow.log"
long_query_time=0//新加
(2)D:\xampp\mysql\data\下新建txt文件mysql-slow.log
(3)重启或启动xampp中apache mysql
注:(2)必须后跟(3),看后面的log记录开头几行就会明白
3.测试:运行脚本(注:仅测试log使用,无die等处理错误)
以下是运行该脚本的log记录
D:\xampp\mysql\bin\mysqld.exe, Version: 5.1.41-log (Source distribution). started with:
TCP Port: 3306, Named Pipe: MySQL
Time > # Time: 111124 15:32:10
# User@Host: root @ localhost
# Query_time: 0.040057Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
use test;
SET timestamp=1322119930;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:33:23
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120003;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:33:49
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120029;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:34:07
# User@Host: root @ localhost
# Query_time: 0.030044Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120047;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:34:29
# User@Host: root @ localhost
# Query_time: 0.030043Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120069;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:34:56
# User@Host: root @ localhost
# Query_time: 0.030044Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120096;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:34:58
# User@Host: root @ localhost
# Query_time: 0.020029Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120098;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120098;
select t1.id from t1 inner join t2 on t1.id=t2.id;
4.在my.ini中改long_query_time=0.04,也就是40毫秒,重启xampp,以下是运行脚本的log结果:
D:\xampp\mysql\bin\mysqld.exe, Version: 5.1.41-log (Source distribution). started with:
TCP Port: 3306, Named Pipe: MySQL
Time > # Time: 111124 15:41:40
# User@Host: root @ localhost
# Query_time: 0.040057Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
use test;
SET timestamp=1322120500;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:41:54
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120514;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:41:56
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120516;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:41:59
# User@Host: root @ localhost
# Query_time: 0.040057Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120519;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# User@Host: root @ localhost
# Query_time: 0.040057Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120519;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120519;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:45:41
# User@Host: root @ localhost
# Query_time: 0.070101Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120741;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# User@Host: root @ localhost
# Query_time: 0.040057Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120741;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:45:42
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120742;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120742;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 15:45:43
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322120743;
select t1.id from t1 inner join t2 on t1.id=t2.id;
5. 在my.ini中改long_query_time=0,重启xampp运行脚本
以下是log结果:
D:\xampp\mysql\bin\mysqld.exe, Version: 5.1.41-log (Source distribution). started with:
TCP Port: 3306, Named Pipe: MySQL
Time > # Time: 111124 16:00:05
# User@Host: root @ localhost
# Query_time: 0.030043Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
use test;
SET timestamp=1322121605;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 16:00:27
# User@Host: root @ localhost
# Query_time: 0.040057Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121627;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 16:00:28
# User@Host: root @ localhost
# Query_time: 0.040057Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121628;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 16:00:45
# User@Host: root @ localhost
# Query_time: 0.010015Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121645;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 16:00:46
# User@Host: root @ localhost
# Query_time: 0.010014Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121646;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121646;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 16:00:47
# User@Host: root @ localhost
# Query_time: 0.020029Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121647;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 16:00:48
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121648;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# User@Host: root @ localhost
# Query_time: 0.040058Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121648;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# User@Host: root @ localhost
# Query_time: 0.020029Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121648;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 16:00:49
# User@Host: root @ localhost
# Query_time: 0.010014Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121649;
select t1.id from t1 inner join t2 on t1.id=t2.id;
# Time: 111124 16:00:50
# User@Host: root @ localhost
# Query_time: 0.020029Lock_time: 0.000000 Rows_sent: 4Rows_examined: 4
SET timestamp=1322121650;
select t1.id from t1 inner join t2 on t1.id=t2.id;
该份脚本比3和4运行的脚本少了mysql_query("flush status");,看log记录可以看到该份脚本运行有不少0.01和0.02秒的记录,这也能看出mysql自身的缓存也加快了select的速度!
页:
[1]