大批量更新和插入MySQL操作

需求: 假设每分钟插入MySQL数据库15w-20w条数据, 如何实现?

思考: 

1. 考虑批量入库的操作?  --肯定的, 毋庸置疑. 将20w数据分20次入库, 每一次就是1w.
2. 多线程? 可以考虑, 但是入库的动作考虑到MySQL的表锁, 所以单线程串行执行即可.
3. 异步? 线程1:批量组装数据, 线程2:批量操作入库. 
4. 如果重复的话? update, 否则就insert.  如果要实现此需求, 一般有三种做法:

• (1) 假设批量入库5000/次. 先根据主键查出来已经入库的那一部分, 比如2000条, 那么这两千就批量update, 剩下的三千就批量insert.
• (2) 将这5000条作为条件, 先批量delete, 再批量insert
• (3) 使用sql语言: INSERT INTO … ON DUPLICATE KEY UPDATE 进行一次性操作

如何设计表: 

1. 设计一个bigint的id自增主键, 再设计一个唯一索引列作为业务去重条件
2. 将唯一索引列作为唯一主键即可.

哪一个性能最好? 

     以实际测试为准, 请往下看.

表设计:

CREATE TABLE `table_name_01` (   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',   `ip` varchar(15) DEFAULT NULL COMMENT '代理ip地址,业务唯一, 重复的IP需要进行update',   `risk_score` int DEFAULT NULL COMMENT '权重',   `tag` varchar(10) DEFAULT NULL COMMENT '标签',   `ts` varchar(20) DEFAULT NULL COMMENT 'ts',   `country` varchar(20) DEFAULT NULL COMMENT '国别代号',   `is_del` tinyint DEFAULT NULL COMMENT '0-未删除 1-删除',   `created_stime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',   `modified_stime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',   PRIMARY KEY (`id`),   UNIQUE KEY `IDX_ip` (`ip`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分钟级代理ip入库表';

CREATE TABLE `table_name_02` (   `ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'ip地址',   `risk_score` int DEFAULT NULL COMMENT '权重',   `tag` varchar(100) DEFAULT NULL COMMENT '标签',   `ts` varchar(20) DEFAULT NULL COMMENT 'ts',   `country` varchar(20) DEFAULT NULL COMMENT '国别代号',   `is_del` tinyint DEFAULT NULL COMMENT '0-未删除 1-删除',   `created_stime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',   `modified_stime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',   PRIMARY KEY (`ip`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分钟级代理ip入库表';

测试MySQL版本: 5.7

实际测试结果如下: 

1. 如果是先批量delete, 在全部insert 的话, 表1 性能优于表2, 尤其是当表中数据越来越大的时候, 删除的耗时变长. 并且要考虑, 每分钟操作十几万数据量, 表的主键一直在自增, 需要处理主键增长的最大的情况.
2. 如果先查询, 在过滤分别执行update和insert的话, 表1和表2 基本性能一致.  但是这种方式比第一种好, 尤其是当表数据量变大的时候,  性能比第一种更好.  
3. 使用sql语言: INSERT INTO … ON DUPLICATE KEY UPDATE 进行一次性操作, 这种方式:  

• 如果使用表1的设计, 性能一般, 比上述第二条稍好, 数据量逐渐变大的时候, 性能表现下降也很快, 就不如第二种方案了. 并且本质上也是先delete, 再update, 表的主键也是在不断的快速增长.
• 当使用表2设计的时候: 性能表现非常强, 数据量小的时候, 要比第二种方案快至少2倍, 当数据量大的时候,  性能表现也非常稳定, 实测5000条数据一批, 平均耗时500ms. 如下图: 

此时此刻, 数据库已经有数据800w+.  如果使用表1设计方案, 方案3的话, 数据量不到200w的时候已经非常拉胯了. 下降很快. 

至此, 我们可以实现本文开头的需求: 处理20w/min, 假设每次5000, 耗时500ms, 那一秒钟就是一万的数据量, 一分钟理论上可以处理60w, 远远大于我们现在的20w.