我在工作中用到的触发器
公司的主营产品是一款视频拍摄的app，同类竞品有抖音等等，作为一个服务器开发人员，除了开发维护接口，还需要负责公司产品管理平台的开发维护。
所以，注册用户的管理和用户上传视频的管理，是管理平台中很重要的两个功能。随着注册用户和上传视频的日益增加，运营和产品经理对管理平台提出了新的需求。原有的管理平台使用的查询sql的性能已经很糟糕，多表联查和子查询让sql在注册用户数据量和上传时数据量只有3万时就会锁表，所以，针对用户和视频管理的优化势在必行。
- 视频管理优化
视频管理部分，需要展示的数据如图
视频基本信息表设计如下
/*表： im_video*/----------------- /*列信息*/----------- Field Type Collation Null Key Default Extra Privileges Comment -------------------- ------------ ------------------ ------ ------ ------- -------------- ------------------------------- ------------------------------------------------------------------------------------------- id int(11) (NULL) NO PRI (NULL) auto_increment select,insert,update,references 视频ID user_id int(11) (NULL) NO MUL (NULL) select,insert,update,references 用户ID video_type int(11) (NULL) NO 1 select,insert,update,references 视频类型:1.视频,2.图片 video_url varchar(100) utf8mb4_general_ci YES (NULL) select,insert,update,references 视频/图片 S3 URL video_s3_key varchar(100) utf8mb4_general_ci YES (NULL) select,insert,update,references 视频S3存储对象 thumbnail_url varchar(100) utf8mb4_general_ci YES (NULL) select,insert,update,references 缩略图 thumbnail_s3_key varchar(100) utf8mb4_general_ci YES (NULL) select,insert,update,references S3存储对象 video_title varchar(500) utf8mb4_general_ci YES select,insert,update,references 视频描述标题 video_size bigint(32) (NULL) YES 0 select,insert,update,references 视频Size video_resolution varchar(20) utf8mb4_general_ci YES select,insert,update,references 视频分辨率 video_time int(11) (NULL) YES 0 select,insert,update,references 视频时长(毫秒) material_id_arr varchar(100) utf8mb4_general_ci YES 0 select,insert,update,references 使用素材ID集合字符串 video_status int(11) (NULL) YES 1 select,insert,update,references 视频状态:1.正常,0.关闭,2.用户删除 ctime timestamp (NULL) YES (NULL) select,insert,update,references 添加时间 etime timestamp (NULL) YES (NULL) select,insert,update,references 更新时间 tags varchar(600) utf8mb4_unicode_ci YES select,insert,update,references 用户tag Json集合 video_bitrate int(11) (NULL) YES 0 select,insert,update,references 视频码率 shoot_screen_type int(11) (NULL) YES 0 select,insert,update,references 拍摄屏幕模式:1.竖屏, 2.横屏 camera_type int(11) (NULL) YES 0 select,insert,update,references 摄像头情况(1.只有后置摄像头/2.只有前置摄像头/3.前置后置都用了) gif_thumbnail_s3_key varchar(50) utf8mb4_general_ci YES (NULL) select,insert,update,references 上传S3的GIF图地址 /*索引信息*/-------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -------- ---------- ------------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- --------------- im_video 0 PRIMARY 1 id A 1234 (NULL) (NULL) BTREE im_video 1 video_user_id 1 user_id A 154 (NULL) (NULL) BTREE
可见，视频的点赞数、观看数等计数信息，在视频的基础表里是无法统计的。
因此为了统计视频被点赞（点赞不可撤销）、被观看，分别设计了点赞表和观看表。视频被点赞或播放，app都会通过接口上报一次数据，服务器记录。
点赞记录表
/*表： im_video_like*/---------------------- /*列信息*/----------- Field Type Collation Null Key Default Extra Privileges Comment -------- --------- --------- ------ ------ ----------------- --------------------------- ------------------------------- -------------- id int(11) (NULL) NO PRI (NULL) auto_increment select,insert,update,references Id video_id int(11) (NULL) YES MUL (NULL) select,insert,update,references 视频ID user_id int(11) (NULL) YES MUL (NULL) select,insert,update,references 用户ID ctime timestamp (NULL) YES CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP select,insert,update,references 点赞时间 /*索引信息*/-------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ------------- ---------- ------------------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- --------------- im_video_like 0 PRIMARY 1 id A 535 (NULL) (NULL) BTREE im_video_like 1 video_like_video_id 1 video_id A 535 (NULL) (NULL) YES BTREE im_video_like 1 video_like_user_id 1 user_id A 53 (NULL) (NULL) YES BTREE
播放记录表
/*表： im_video_view*/---------------------- /*列信息*/----------- Field Type Collation Null Key Default Extra Privileges Comment ---------- ----------- --------------- ------ ------ ----------------- --------------------------- ------------------------------- ---------------------------------------------- id int(11) (NULL) NO PRI (NULL) auto_increment select,insert,update,references Id video_id int(11) (NULL) YES MUL (NULL) select,insert,update,references 视频ID user_id int(11) (NULL) YES MUL (NULL) select,insert,update,references 用户ID ip varchar(50) utf8_general_ci YES MUL select,insert,update,references IP(游客) view_type int(11) (NULL) YES 1 select,insert,update,references 观看类型:1.应用内,2.应用外(分享) view_count int(11) (NULL) YES 0 select,insert,update,references 非去重观看数递增 ctime timestamp (NULL) YES CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP select,insert,update,references 创建时间 /*索引信息*/-------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ------------- ---------- ------------------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- --------------- im_video_view 0 PRIMARY 1 id A 3067 (NULL) (NULL) BTREE im_video_view 1 video_view_video_id 1 video_id A 3067 (NULL) (NULL) YES BTREE im_video_view 1 video_view_user_id 1 user_id A 255 (NULL) (NULL) YES BTREE im_video_view 1 video_view_ip 1 ip A 1533 (NULL) (NULL) YES BTREE
理论上，到这里就可以满足运营的需求了，比如ID为23的视频的点赞数可以通过以下sql实现
SELECT COUNT(*) FROM `im_video_like` WHERE video_id=23;
视频的观看数有个重复观看的差别，根据user_id和用户IP区分。如下图，ID为247的视频的观看记录
SELECT * FROM `im_video_view` WHERE video_id=247;
id video_id user_id ip view_type view_count ctime ------ -------- ------- ------------- --------- ---------- --------------------- 5 247 4 140.207.22.8 1 10 2017-07-03 15:56:37 21 247 12 192.168.100.1 1 7 2017-07-03 16:50:01 93 247 8 192.168.100.1 1 1 2017-07-04 10:44:51
可见，视频247被id=4的用户累计观看了10次，被id=12的用户累计观看了7次。
如果我们需要的是去重后的观看数，则执行
SELECT COUNT(*) FROM `im_video_view` WHERE video_id=247;
count(*) ---------- 3
如果需要的是非去重的数据，则执行
SELECT SUM(view_count) FROM `im_video_view` WHERE video_id=247;
sum(view_count) ----------------- 18
对单条数据如上处理没问题，但是当数据量到百万级时，肯定会影响sql查询性能。所以我们又设计了一张表，专门用来统计视频点赞数、视频播放数的信息。
/*表： im_video_count*/----------------------- /*列信息*/----------- Field Type Collation Null Key Default Extra Privileges Comment ----------------------- ------- --------- ------ ------ ------- ------ ------------------------------- -------------------------------- video_id int(11) (NULL) NO PRI (NULL) select,insert,update,references 视频ID user_id int(11) (NULL) NO (NULL) select,insert,update,references 用户ID like_count int(11) (NULL) YES 0 select,insert,update,references 点赞数 comment_count int(11) (NULL) YES 0 select,insert,update,references 评论数 view_count int(11) (NULL) YES 0 select,insert,update,references 观看数(应用内) view_share_count int(11) (NULL) YES 0 select,insert,update,references 分享观看数 share_count int(11) (NULL) YES 0 select,insert,update,references 分享数 collect_count int(11) (NULL) YES 0 select,insert,update,references 收藏数 view_count_repeat int(11) (NULL) YES 0 select,insert,update,references 重复观看数（应用内） view_share_count_repeat int(11) (NULL) YES 0 select,insert,update,references 重复分享观看数 like_count_today int(11) (NULL) YES 0 select,insert,update,references 今日点赞数 like_count_yesterday int(11) (NULL) YES 0 select,insert,update,references 昨日点赞数 view_count_today int(11) (NULL) YES 0 select,insert,update,references 今日观看数 view_count_yesterday int(11) (NULL) YES 0 select,insert,update,references 昨日观看数 /*索引信息*/-------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -------------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- --------------- im_video_count 0 PRIMARY 1 video_id A 1216 (NULL) (NULL) BTREE
im_video_count表中的数据，基本上都通过触发器实现更新，部分数据通过数据库的定时任务实现。
以统计视频的点赞数为例，每当视频被点赞，表im_video_like都会插入一条记录，我们写了这样的一个触发器，每次im_video_like插入了新数据，im_video_count表对应视频的like_count,like_count_today均在原数据基础上加1。
DELIMITER $$ USE `imagingly`$$ DROP TRIGGER /*!50032 IF EXISTS */ `trigger_video_like_insert`$$ CREATE /*!50017 DEFINER = 'root'@'127.0.0.1' */ TRIGGER `trigger_video_like_insert` AFTER INSERT ON `im_video_like` FOR EACH ROW BEGIN #DECLARE userId INT(11);#被点赞视频的视频Id #SET userId = (SELECT user_id FROM `im_video` WHERE id =new.video_id); #点赞改变im_video_count表中对应视频的统计数据 UPDATE `im_video_count` SET like_count = like_count+1,like_count_today=like_count_today+1 WHERE video_id = new.video_id; #用户统计表，点赞数+1 UPDATE `im_user_count` SET like_count_total=like_count_total+1,like_count_today=like_count_today+1 WHERE user_id=new.user_id; END; $$ DELIMITER ;
至于昨日点赞数、昨日观看数，则通过每天凌晨执行一次mysql定时任务实现，具体实现如下：
DROP EVENT IF EXISTS `imagingly`.`bakVideoLikeAndViewCount`; DELIMITER $$ SET GLOBAL event_scheduler = ON$$ -- required for event to execute but not create CREATE /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `imagingly`.`bakVideoLikeAndViewCount` ON SCHEDULE EVERY 1 DAY STARTS '2017-12-28 00:00:05' ON COMPLETION PRESERVE ENABLE COMMENT '每日凌晨00:00:05，每隔一小时，今日点赞数赋值给昨日点赞数，今日观看数赋值给昨日观看数，今日点赞数、今日观看数清零' DO BEGIN UPDATE `im_video_count` SET like_count_yesterday=like_count_today,view_count_yesterday=view_count_today,like_count_today=0,view_count_today=0;END$$ DELIMITER ;
如此，以后获取点赞数、视频播放数，只需要左联im_video_count即可获得，节省了sql查询的时间。
