大批量数据，增删改查，要注意什么？

大批量数据，做增删改查，要注意什么？

如果数量级达到千万级别，有大数据相关的组件，可以优先使用。如 ClickHouse、Elasticsearch 、Flink等这些组件。

假设没有大数据相关的组件，那么有很多的要点需要注意。

大批量数据的查询

• 查询的sql语句，最好得走索引

• 游标查询，避免深度分页：

  每批大数量的分页，比如 LIMIT 1000000, 10 会导致 MySQL 扫描并丢弃前一百万条数据，极度消耗 CPU 和 IO。

改用“上一页最大 ID”的游标查询：

WHERE id > #{lastId} LIMIT 10

大批量数据的数据统计

不能在用户发起接口请求时进行实时统计。 利用大数据或调度框架（如 XXL-JOB等），在夜间或业务低峰期，把大批量数据的统计结果提前计算好，存入统计表中，最后在请求时查询统计表的数据。

大批量数据的新增

• 批量写入/更新：

@Value("${my.batch.size:500}")
private Integer batchSize;


for (int startIndex = 0; startIndex < userIdList.size(); startIndex += batchSize) {
    int endIndex = Math.min(startIndex + batchSize, userIdList.size());
    //分批更新
    myDao.saveList( userIdList.subList(startIndex, endIndex) );
}

• 削峰入库：

如果在业务高峰期有突发的海量写入，不要直接打库。先写入 MQ，再由后台消费者异步入库。

大批量数据的修改

• 避免长事务： 更新涉及大量数据时，不要在一个大事务里完成，否则Undo Log 暴增，且长时间占用连接池和锁资源。

• 并发更新使用乐观锁：

  使用版本号字段（Version）、状态机 控制。

  使用乐观锁，并发修改同一行，不要用悲观锁，也就是 SELECT ... FOR UPDATE。

大批量数据的删除

• 物理删除（DELETE）会破坏索引连续性，产生大量碎片空间，甚至引起 B+ 树结构重平衡。

  使用 is_deleted 字段，将 DELETE 转化为 UPDATE，逻辑删除。

• 删字段时，不要直接 drop column，不然会锁表。

尤其是千万级别的大表，不能直接删字段，可以Rename字段，视为废弃字段。

如果真的必须得删除，可以先新建一张新的表，将数据导入新的表，全量迁移，并将新的表修改表名，观察几天平稳运行后，最后再删除旧表。