只会一点java

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>

五、结果

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

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

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