需求：有一张表9亿多条数据，数据加索引总数据量61GB。考虑到这张表的大部分数据都不会再被使用并且大数据量可能影响整库的性能，所以决定将表里某一个时刻之前的数据备份到一张新表中，待备份完成后将旧表中已经备份的数据删除。由于数据量太大，不适合让DBA直接做备份。
- 方案1
main线程分页读取旧表数据，每页200条。每读取一页数据就新建一个线程，将200条数据交给新建的线程去完成insert到新表的操作。
弊端：需要将数据读到内存，然后再写回数据库，涉及到大量的IO操作。所有的数据都需要走网络，对网络带宽及稳定性要求很高。
- 方案2
直接使用SQL语句在MySQL端完成select和insert操作，不涉及IO操作。
弊端：在这种情况下，要分页操作就必须使用单线程。
综合考虑整个备份过程的速度与数据量，采用第二种方案。
涉及到的SQL语句如下：
<insert id="backupUniqueNumber" parameterClass="java.util.Map">
INSERT INTO UniqueNumber_backup_201603
(ID,
SerialNumber,
BusinessType,
AddTime
)
SELECT ID, SerialNumber, BusinessType, AddTime FROM UniqueNumber WHERE ID > #lastMaxId# limit #pageSize#
</insert>
<!-- 强制走主库 -->
<select id="getLastMaxId" resultClass="java.lang.Integer">
/*+zebra:w*/SELECT MAX(ID) FROM UniqueNumber_backup_201603
</select>
主要的Java代码如下：
public class UniqueNumberBackupBiz {
private static final AvatarLogger logger = AvatarLoggerFactory.getLogger(UniqueNumberBackupBiz.class);
@Autowired
private UniqueNumberDao uniqueNumberDao;
public void execute(){
int maxId = 932727664;// 932727664;//UniqueNumber表中2016-04-01 00:00:00数据的id为932727664
int lastMaxId = 0;
int pageSize = 300;
try{
lastMaxId = uniqueNumberDao.getLastMaxId();
}catch (Exception e){
//这里出现异常是因为备份表中还没有数据，此时lastMaxId取默认值0. 这个异常只会在第一次运行时出现。
logger.warn(String.format("Get lastMaxId failed, system exit, please run the system manually"));
System.exit(0);
}
long startTime = System.currentTimeMillis();
while(lastMaxId < maxId){
logger.info("lastMaxId=" + lastMaxId);
try {
uniqueNumberDao.backupUniqueNumber(lastMaxId, pageSize);
}catch (Exception e){
logger.error("backupUniqueNumber exception:", e);
}
try {
Thread.sleep(50);//防止MySQL压力过大
lastMaxId = uniqueNumberDao.getLastMaxId();
}catch (Exception e){
// logger.error("Thread sleep exception", e);
logger.error("Get lastMaxId failed, system exit, please run the system manually", e);
System.exit(0);
}
}
long endTime = System.currentTimeMillis();
logger.info(String.format("Data backup finished in %d ms", endTime - startTime));
}
}
为了防止对MySQL造成过大的压力，每一次循环中休眠50ms。运行中每秒insert的数据量大约5000条。若取消每次循环休眠50ms，每秒insert数据量大约为30000条。
PS：踩了一个坑，由于MySQL数据库有master slave之分，select操作是走slave库。master库与slave库同步存在一定的时差。之前没有强制select走master库，造成抛出了一些主键重复异常。不过这个异常不会造成多大的影响。后来强制select走主库就没有再抛出异常了。
数据全部从旧表搬到新表之后，需要将旧表中已备份的数据删除。
<delete id="deleteOldData" parameterClass="java.util.Map">
<![CDATA[
DELETE FROM UniqueNumber
WHERE ID >= #startId# AND ID < #endId#
]]>
</delete>
<!-- 强制走主库 -->
<select id="getMinId" resultClass="java.lang.Integer">
/*+zebra:w*/SELECT MIN(ID) FROM UniqueNumber
</select>
public void deleteOldData(){
int startId = uniqueNumberDao.getMinId();
int pageSize = 1000;
int endId = startId + pageSize;
int maxId = uniqueNumberDao.getLastMaxId();
logger.info(String.format("maxId=%d", maxId));
while(endId < (maxId - 1000)){//保险起见，防止多删
try {
int num = uniqueNumberDao.deleteOldData(startId, endId);
Thread.sleep(50);
logger.info(String.format("startId=%d, endId=%d,%d rows deleted", startId, endId, num));
startId = endId;
endId = startId + pageSize;
}catch (Exception e){
logger.error("Error occurred when deleting data", e);
System.exit(-1);
}
}
}
删除任务执行完之后可能还剩下不到1000条数据没删完，需要手动执行delete语句来删除剩下的部分。