mysql使用slow log

86565656

　　1.运行环境:xampp1.7.3,mysql版本为5.1.41
　　2.开启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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040057  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.030044  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.030043  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.030044  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.020029  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4
　　SET timestamp=1322120098;
　　select t1.id from t1 inner join t2 on t1.id=t2.id;
　　# User@Host: root[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040057  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040057  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4
　　SET timestamp=1322120519;
　　select t1.id from t1 inner join t2 on t1.id=t2.id;
　　# User@Host: root[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040057  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4
　　SET timestamp=1322120519;
　　select t1.id from t1 inner join t2 on t1.id=t2.id;
　　# User@Host: root[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.070101  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4
　　SET timestamp=1322120741;
　　select t1.id from t1 inner join t2 on t1.id=t2.id;
　　# User@Host: root[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040057  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4
　　SET timestamp=1322120742;
　　select t1.id from t1 inner join t2 on t1.id=t2.id;
　　# User@Host: root[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.030043  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040057  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040057  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.010015  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.010014  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4
　　SET timestamp=1322121646;
　　select t1.id from t1 inner join t2 on t1.id=t2.id;
　　# User@Host: root[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.020029  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4
　　SET timestamp=1322121648;
　　select t1.id from t1 inner join t2 on t1.id=t2.id;
　　# User@Host: root[root] @ localhost [127.0.0.1]
　　# Query_time: 0.040058  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4
　　SET timestamp=1322121648;
　　select t1.id from t1 inner join t2 on t1.id=t2.id;
　　# User@Host: root[root] @ localhost [127.0.0.1]
　　# Query_time: 0.020029  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.010014  Lock_time: 0.000000 Rows_sent: 4  Rows_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[root] @ localhost [127.0.0.1]
　　# Query_time: 0.020029  Lock_time: 0.000000 Rows_sent: 4  Rows_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的速度!