用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;

三、 总结

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

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

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

出处：http://weakyon.com/2017/05/24/statistics-with-mysql-trigger.html