用mysql触发器做数据统计

原创内容,转载请注明出处

Posted by Weakyon Blog on May 24, 2017

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

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

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

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

一 具体实现

文件表大致是这样的

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是文件大小

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 插入触发器

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 更新触发器

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 删除触发器

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

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

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

三 总结

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

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

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

24 May 2017