MySQL的slow log可以用来看执行时间超过指定时间的SQL，但是指定的时间最小只能是一秒，有点太大了。通常是要找那些执行次数很多但每次执行又超不过一秒的SQL。打一个补丁，即可记录所有SQL的详细执行时间。原文见：
为slow log打补丁的原文注意这里用了prefix，是因为机器还有一个mysql5.0.22，我不想引发冲突。
brum@brum-laptop:~/mysql-5.0.26$ ./configure --prefix=/usr/local/mysql
....
....
checking for termcap functions library... configure: error: No curses/termcap library found
需要安装libncurses5-dev包，安装之后，configure就通过了
补丁从这里下载：
打补丁
brum@brum-laptop:~$ pwd
/home/brum
brum@brum-laptop:~$ patch -p0 < patch.slow-micro.5.0.26.diff
patching file mysql-5.0.26/include/my_time.h
patching file mysql-5.0.26/scripts/mysqldumpslow.sh
patching file mysql-5.0.26/sql/log.cc
patching file mysql-5.0.26/sql/mysqld.cc
patching file mysql-5.0.26/sql/set_var.cc
patching file mysql-5.0.26/sql/set_var.h
patching file mysql-5.0.26/sql/sql_class.cc
patching file mysql-5.0.26/sql/sql_class.h
patching file mysql-5.0.26/sql/sql_parse.cc
patching file mysql-5.0.26/sql/sql_show.cc
patching file mysql-5.0.26/sql/structs.h
patching file mysql-5.0.26/sql-common/my_time.c
brum@brum-laptop:~$ cd mysql-5.0.26/
brum@brum-laptop:~/mysql-5.0.26$ make
brum@brum-laptop:~/mysql-5.0.26$ sudo make install
....
....
/usr/bin/ld: cannot find -lz
collect2: ld returned 1 exit status
libtool: install: error: relink `libmysqlclient.la' with the above command before installing it
make[3]: *** [install-pkglibLTLIBRARIES] 错误 1
make[3]: Leaving directory `/home/brum/mysql-5.0.26/libmysql'
make[2]: *** [install-am] 错误 2
make[2]: Leaving directory `/home/brum/mysql-5.0.26/libmysql'
make[1]: *** [install-recursive] 错误 1
make[1]: Leaving directory `/home/brum/mysql-5.0.26'
make: *** [install] 错误 2
brum@brum-laptop:~/mysql-5.0.26$ find -name libz*
./zlib/libz.la
./zlib/.libs/libz.so.1.2.3
./zlib/.libs/libz.so.1
./zlib/.libs/libz.so
./zlib/.libs/libz.a
./zlib/.libs/libz.lai
./zlib/.libs/libz.la
brum@brum-laptop:~/mysql-5.0.26$ export LD_LIBRARY_PATH=~/mysql-5.0.26/zlib/.libs
brum@brum-laptop:~/mysql-5.0.26$ sudo make install
....
....
mv: cannot stat `libmysqlclient.so.15.0.0': No such file or directory
今天又抽了些时间，看了看configure --help的帮助，重新configure。首先是用make clean把前面做过的编译都清除。然后：
brum@brum-laptop:~/mysql-5.0.26$ ./configure --prefix=/usr/local/mysql --exec-prefix=/usr/local/mysql
make
sudo make install
brum@brum-laptop:~$ cd /usr/local/mysql/
brum@brum-laptop:~$ mkdir etc
brum@brum-laptop:/usr/local/mysql$ sudo cp /home/brum/mysql-5.0.26/support-files/my-small.cnf /usr/local/mysql/etc/my.cnf
这是为了不修改/etc/my.cnf，因为还有另一个mysql服务。
修改my.cnf中[mysql]和[mysqld]
port = 3307
socket = /usr/local/mysql/var/mysql.sock
这也是为了不与已经存在的mysql冲突
brum@brum-laptop:/usr/local/mysql$ sudo bin/mysql_install_db --user=brum
brum@brum-laptop:/usr/local/mysql$ sudo chown -R root .
brum@brum-laptop:/usr/local/mysql$ sudo chown -R brum var
brum@brum-laptop:/usr/local/mysql$ sudo chgrp -R brum .
brum@brum-laptop:/usr/local/mysql$ bin/mysqld_safe &
服务启动了
连接一下试试
brum@brum-laptop:/usr/local/mysql$ bin/mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2 to server version: 5.0.26
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>
好了，成功了。
修改/usr/local/mysql/etc/my.cnf
在[mysqld]中加上
log-slow-queries
long-query-time=0
前
者是让mysql记录slow query；后者是利用前面打的补丁，设置为0的含义是把所有的SQL都记录在slow query
log中。如果不打补丁，那么long-query-time最小只能设为1，即1秒，即只有超过1秒的SQL才会记录。打了补丁则没有这个限制了。
随便执行了几个SQL，看一下日志：
brum@brum-laptop:~/mysql-5.0.26$ cat /usr/local/mysql/var/brum-laptop-slow.log
/usr/local/mysql/libexec/mysqld, Version: 5.0.26-log. started with:
Tcp port: 3307 Unix socket: /usr/local/mysql/var/mysql.sock
Time Id Command Argument
# Time: 061104 17:47:22 # User@Host: [brum] @ localhost []
# Query_time: 0.014273 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use test;
create table test(a int, b varchar(100))engine=innodb;
# Time: 061104 17:47:50 # User@Host: [brum] @ localhost []
# Query_time: 0.000378 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
alter table test modify a auto_increment primary key;
# Time: 061104 17:48:23 # User@Host: [brum] @ localhost []
# Query_time: 0.007990 Lock_time: 0.000329 Rows_sent: 0 Rows_examined: 0
SET insert_id=1;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:31 # User@Host: [brum] @ localhost []
# Query_time: 0.004855 Lock_time: 0.000116 Rows_sent: 0 Rows_examined: 0
SET insert_id=2;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:32 # User@Host: [brum] @ localhost []
# Query_time: 0.000767 Lock_time: 0.000114 Rows_sent: 0 Rows_examined: 0
SET insert_id=3;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:34 # User@Host: [brum] @ localhost []
# Query_time: 0.031763 Lock_time: 0.000115 Rows_sent: 0 Rows_examined: 0
SET insert_id=4;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:40 # User@Host: [brum] @ localhost []
# Query_time: 0.000846 Lock_time: 0.000116 Rows_sent: 4 Rows_examined: 4
select * from test;
# Time: 061104 17:48:43 # User@Host: [brum] @ localhost []
# Query_time: 0.000042 Lock_time: 0.000000 Rows_sent: 4 Rows_examined: 4
# administrator command: Quit;
精确到微秒级了。然后，还可以下载一个mysql_slow_log_parser，在我上面提供的链接中可以下载。
brum@brum-laptop:~/MySQL5.0.26SlowLogPatch$ ./mysql_slow_log_parser /usr/local/mysql/var/brum-laptop-slow.log
Starting...
### 1 Query
### Total time: 0.014273, Average time: 0.014273
### Taking 0.014273 seconds to complete
### Rows analyzed 0
use test;
create table test(a int, b varchar(XXX))engine=innodb;
use test;
create table test(a int, b varchar(100))engine=innodb;
### 1 Query
### Total time: 0.000846, Average time: 0.000846
### Taking 0.000846 seconds to complete
### Rows analyzed 4
select * from test;
select * from test;
### 1 Query
### Total time: 0.000378, Average time: 0.000378
### Taking 0.000378 seconds to complete
### Rows analyzed 0
alter table test modify a auto_increment primary key;
alter table test modify a auto_increment primary key;
### 4 Queries
### Total time: 0.045375, Average time: 0.01134375
### Taking 0.000767 , 0.004855 , 0.007990 , 0.031763 seconds to complete
### Rows analyzed 0, 0, 0 and 0
SET insert_id=XXX;
insert into test(b) values('XXX');
SET insert_id=4;
insert into test(b) values('aaaaa');
每个SQL执行了几遍，总共多少时间，非常清楚。打了补丁以后，可以检测到那些每次执行时间小于1秒而执行次数非常多的SQL。很多时候都是这样的SQL在做怪，把这些SQL进行优化，或者修改程序减少这样的SQL的执行次数，都会大大提高应用的效率。
我之前在RHEL4.0上也装过，非常顺利，
./configure --prefix=/usr/local/mysql
之后，make和make install都没有问题。但在ubuntu6.06上，就需要使用
./configure --prefix=/usr/local/mysql --exec-prefix=/usr/local/mysql
没有仔细去研究原因。
