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等处理错误)
<?php
$h=mysql_connect("localhost",'root','');
mysql_select_db("test",$h);
mysql_query("flush status");
$res=mysql_query("select t1.id from t1 inner join t2 on t1.id=t2.id");
while($r=mysql_fetch_assoc($res)){
var_dump($r);
}
mysql_close($h);
?>
以下是运行该脚本的log记录
D:\xampp\mysql\bin\mysqld.exe, Version: 5.1.41-log (Source distribution). started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# 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 Id Command Argument
# 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运行脚本
<?php
$h=mysql_connect("localhost",'root','');
mysql_select_db("test",$h);
$res=mysql_query("select t1.id from t1 inner join t2 on t1.id=t2.id");
while($r=mysql_fetch_assoc($res)){
var_dump($r);
}
mysql_close($h);
?>
以下是log结果:
D:\xampp\mysql\bin\mysqld.exe, Version: 5.1.41-log (Source distribution). started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# 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的速度!
