需求: 假设每分钟插入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. 如下图: 

java多线程批量更新数据怎么操作 多线程批量更新数据库_主键

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

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