MySQL触发器效率的分析与测试

针对MySQL触发器的效率，我做了如下实验：

测试用例：
1，创建简单数据表数据表，使用存储过程插入10万条数据，记录存储过程执行时间。
2，使用存储过程插入数据可以确保顺序执行，模拟数据库辅库的真实执行情况。
3，对比三种情况：无触发器、简单触发器、调用memc函数的存储过程。
4，每种情况均使用空数据表，测试环境在最大程度上保持一致。

测试环境：
1，运行于VMWare下的CentOS 5.5 虚拟机，512M内存，Intel i3 350 CPU，VMWare只使用单核，笔记本5400转硬盘。
2，数据库版本：Percona-Server-5.1.50-rel11.4-111-Linux-i686
3，数据库 innodb_buffer_pool_size 设置为 64M 。
4，其他程序版本：libmemcached-0.37 、 memcached_functions_mysql-0.9 、memcached 1.4.5 5，
     Memcached服务程序与MySQL数据库部署在同一机器。

测试脚本：
参见 triggerTest.sql

测试结果：
参见triggerTestResult.txt

测试数据如下：
1，无触发器耗时：    1.55秒
2，简单触发器耗时：      2.05秒
3，调用memc函数耗时：    10.72秒
注：在步骤3的测试过程中，MySQL数据库CPU使用率40%左右，memcached进程CPU使用率20%左右。

测试结论：
1，简单触发器在0.5秒钟之内可以完成10万次触发调用，所以：触发器本身不会形成性能瓶颈，
2，调用memc函数造成了触发器单次调用时间增加，大约9秒钟完成10万次调用，调用时间增加1800%左右。
3，虽然memc函数的调用会增加延迟，但如果使用得当，也并不一定成为性能瓶颈。

triggerTest.sql :

create database if not exists test;
use test;
create table tr1 (
k int unsigned not null auto_increment,
v int unsigned not null ,
primary key(k)
)engine=innodb;
delimiter ;;
create procedure pitr1(IN i int)
begin
while i > 1 do
insert into tr1 (v) values (i);
set i=i-1;
end while;
end;;
delimiter ;
set global innodb_flush_log_at_trx_commit=0;
truncate table tr1;
call pitr1(100000);
delimiter ;;
create trigger taiontr1 after insert on tr1
for each row
begin
declare x int;
set x=NEW.k+NEW.v;
end;;
delimiter ;
truncate table tr1;
call pitr1(100000);
drop trigger taiontr1;
select memc_servers_set("127.0.0.1:11211");
delimiter ;;
create trigger tmcaiontr1 after insert on tr1
for each row
begin
declare ret int;
select memc_set(NEW.k,NEW.v) into ret;
end;;
delimiter ;
truncate table tr1;
call pitr1(100000);
drop procedure pitr1;
drop table tr1;

triggerTestResult.txt: mysql>
mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> use test;
Database changed
mysql> create table tr1 (
-> k int unsigned not null auto_increment,
-> v int unsigned not null ,
-> primary key(k)
-> )engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;;
mysql> create procedure pitr1(IN i int)
-> begin
-> while i > 1 do
->   insert into tr1 (v) values (i);
->   set i=i-1;
-> end while;
-> end;;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> set global innodb_flush_log_at_trx_commit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table tr1;
Query OK, 0 rows affected (0.00 sec)
mysql> call pitr1(100000);
Query OK, 1 row affected (1.55 sec)
mysql> delimiter ;;
mysql> create trigger taiontr1 after insert on tr1
-> for each row
-> begin
-> declare x int;
->   set x=NEW.k+NEW.v;
-> end;;
Query OK, 0 rows affected (0.04 sec)
mysql> delimiter ;
mysql> truncate table tr1;
Query OK, 0 rows affected (0.01 sec)
mysql> call pitr1(100000);
Query OK, 1 row affected (2.05 sec)
mysql> drop trigger taiontr1;
Query OK, 0 rows affected (0.01 sec)
mysql> select memc_servers_set("127.0.0.1:11211");
+-------------------------------------+ |
memc_servers_set("127.0.0.1:11211")
| +-------------------------------------+ |
                                   0 |
 +-------------------------------------+
1 row in set (0.00 sec)
mysql> delimiter ;;
mysql> create trigger tmcaiontr1 after insert on tr1
-> for each row
-> begin
-> declare ret int;
-> select memc_set(NEW.k,NEW.v) into ret;
-> end;; Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> truncate table tr1; Query OK, 0 rows affected (0.00 sec)
mysql> call pitr1(100000); Query OK, 1 row affected (10.72 sec)
mysql> drop procedure pitr1; Query OK, 0 rows affected (0.00 sec)
mysql> drop table tr1; Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>