用mysql触发器做数据统计

发表于 更新于 分类于 ， 阅读次数： 本文字数： 1.8k 阅读时长 ≈ 2 分钟

做文件系统的后台数据统计，需要实时统计数据库存储文件状况

写代码来做的话，文件的sql操作和文件表的更新要做到原子性，就很复杂。所以需要依赖mysql本身的功能。

事务，存储过程和触发器都能做到。

对于数据统计来说，由于本身对业务逻辑相对独立（操作完全独立的汇总表），因此我认为触发器就足堪大用了

具体实现

文件表大致是这样的

1
2
3
4
5
6
create table if not exists `file` (                                                
    `Name` varchar(255) NOT NULL,                                                  
    `Bucket` varchar(255) NOT NULL,                                                
    `Size` bigint unsigned NOT NULL,                                               
    PRIMARY KEY (`Name`,`Bucket`)                                                  
)ENGINE=InnoDB CHARSET=utf8;

Name是文件名，Bucket的业务名，Size是文件大小

1
2
3
4
5
6
7
8
create table if not exists `fileinfo` (                                            
    `Bucket` varchar(255) NOT NULL,                                                
    `UploadSize` bigint unsigned NOT NULL DEFAULT 0,                               
    `DeleteSize` bigint unsigned NOT NULL DEFAULT 0,                               
    `UploadCount` bigint unsigned NOT NULL DEFAULT 0,                              
    `DeleteCount` bigint unsigned NOT NULL DEFAULT 0,                              
    PRIMARY KEY (`Bucket`)                                                         
)ENGINE=InnoDB CHARSET=utf8;

汇总表是这样的，统计了上传的总量，删除的总量，以及上传次数和删除次数

1 插入触发器

1
2
3
4
5
6
7
8
9
10
11
12
drop TRIGGER if exists file_insert_after;

delimiter //                                                                       
create trigger file_insert_after                                                   
after insert on file                                                               
for each row                                                                       
begin                                                                              
  insert into fileinfo(Bucket,UploadSize,UploadCount) values(new.Bucket,new.Size,1) on duplicate key 
  update UploadSize = UploadSize + new.Size,UploadCount = UploadCount + 1;         
end                                                                                
//                                                                                 
delimiter ;

在file表插入数据后，如果fileinfo表没有这个业务，就进行插入，否则对上传次数和容量做自增

2 更新触发器

1
2
3
4
5
6
7
8
9
10
11
drop TRIGGER if exists file_update_after;

delimiter //                                                                       
create trigger file_update_after                                                   
after update on file                                                               
for each row                                                                       
begin                                                                           
  update fileinfo set UploadSize = UploadSize + new.Size - old.Size where Bucket = new.Bucket;
end                                                                             
//                                                                              
delimiter ;

3 删除触发器

当文件表发生更新时，相应做更新

1
2
3
4
5
6
7
8
9
10
11
drop TRIGGER if exists file_delete_after;

delimiter //                                                                    
create trigger file_delete_after                                                
after delete on file                                                            
for each row                                                                    
begin                                                                           
  update fileinfo set DeleteSize = DeleteSize + old.Size,DeleteCount = DeleteCount + 1 where Bucket = old.Bucket;
end                                                                             
//                                                                              
delimiter ;

当文件表发生删除事件时，删除的文件总量做自增

注意点

由于插入和更新操作都是一条一条进行的，因此触发器对mysql影响不大

但是删除操作是可以批量进行的，而删除操作需要锁全表，因此对性能会有所影响

经过我的测试，裸表和加了触发器的表，在执行100W的数据删除时，会由毫秒级变成秒级

此时file表的插入操作会阻塞，可能会导致上层调用超时

因此批量删除要加上limit

1
delete from file where Bucket = 'sync' limit 1000;

总结

当触发器和业务逻辑相关后，触发器作为隐性的操作，可能会导致意料之外的情况，甚至造成死锁

特别是不同表之间的触发器和触发器相互触发，会造成灾难性的后果

所以触发器特别适用于对业务逻辑相对独立的场合，例如数据统计