单表千万级数据迁移实践方案-乞丐版，不使用大数据平台

Posted on 2019-03-12 10:12 只会一点java 阅读(...) 评论(...) 编辑 收藏

一、现状

mysql下，某business单表已近2000万且还在持续增加中,存在多个索引，有较高的查询压力。现业务端使用guava cache拦了一道，还能顶得住，但是后台管理系统的全量数据的分页排序查询比较慢，且将来会越来越慢。

二、目标

 业务端+admin查询都快。

三、解决方案

1.基于实际情况（大家一定要根据实际情况来），把数据库拆为三个，如下：

1. 热数据(老表)：提供CURD操作，事务加锁等等，最大限度的不用更改原代码。
2. 半年内数据（history）：只提供查询业务。
3. 半年之前数据(backup)：归档，不提供业务查询，只支持手动查库(已跟产品沟通好)。

2.数据迁移，采用公司统一任务调度平台，注册任务后调度执行，自带WEB管理页面。支持暂停、恢复、执行计划、日志查询。

3.由于历史数据过千万，需要上线前进行一次手动迁移，初始化数据

            1）history表保存：7天~半年，非进行状态的数据。

            2）backup表保存：半年前的，非进行状态的数据。

            3）删除business表中，7天前的，非进行状态的数据。

4. 后续每天凌晨定时任务迁移数据（迁移时注意：保证ID一致  ）

            business-->history      >7天，非进行状态的数据。

            history-->backup   >半年，非进行状态的数据。

5.admin切到从库读。主从分离,避免从库读全量数据，导致业务端查询缓慢。

四、采坑

1.千万级带索引删除记录，记得不能一次性直接delete,可以根据创建时间来，一次删除百万级数据，多分几次删除。否则容易出现假死，慢查询，kill不掉执行sql.

2.注意初始化数据时候，可能当天多次执行，所以加上修改时间在当天前的，这样多次执行，不会出现数据重复。

3.写批量插入sql时，

1）不要用函数：sql中使用函数极端消耗时间。

2）不要用#，要用$：避免再次编译消耗时间，这里不用怕什么sql注入，内部接口。

1 <insert id="transferTradingOrderByIds">
 2     insert into ${toTableName} (<include refid="Base_Column_List"/>)
 3     select <include refid="Base_Column_List"/>
 4     from ${fromTableName}
 5     <where>
 6         id in
 7         <foreach collection="transferTradingOrderIds" item="id" separator="," open="(" close=")">
 8             ${id}
 9         </foreach>
10     </where>
11 </insert>      

五、结果

热表数据:一百万内，增删改查极快。

历史数据:一千万内，查询快。

归档数据：千万级以上，慢，但是业务不调用。