数据库定时备份功能实例梳理

首先是定时任务注解+cron表达式

	@Scheduled(cron="0 0 20 1/1 * ? ")//每晚八点,无人使用时
	public void doBackup()
	{   
		final SicStwcService sicStwcService = SpringUtils.getBean(SicStwcService.class);
		sicStwcService.doBackup();
	}

.doBackup()触发了多个方法,以doBackupCaseVideo()为例:

先用sicSicCaseVideoService.getList(sicSicCaseVideoHolder)从数据库中获得需要修改备份的记录(backupFlag=1或2)

然后使用caseVideoService.backupCaseVideo(insertCaseVideoList, updateCaseVideoList);  通过jdbc方法进行跨数据库的备份.

最后将这些要修改 要备份的记录的backupFlag重置为0,避免重复备份修改.

先说CaseVideoService.CaseVideoService的方法如下:

	public void backupCaseVideo(List<SicSicCaseVideoHolder> insertCaseVideoList,
			List<SicSicCaseVideoHolder> updateCaseVideoList) throws Exception {
		this.caseVideoDao.backupCaseVideo(insertCaseVideoList, updateCaseVideoList);
	}

实际代码在CaseVideoDaoImpl中:

@Repository("caseVideoDao")
public class CaseVideoDaoImpl implements CaseVideoDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public void backupCaseVideo(final List<SicSicCaseVideoHolder> insertCaseVideoList,
			final List<SicSicCaseVideoHolder> updateCaseVideoList) {
		String sql = "insert INTO sic_case_video (COURT_CODE,RECEPTION_ID,CASE_VIDEO_ID,START_TIME,END_TIME,"
				+ "REAL_START_TIME,REAL_END_TIME,EXTEND_COL1,EXTEND_COL2,EXTEND_COL3,"
				+ "ISDEL,CREATE_USER_ID,UPDATE_USER_ID,CREATE_TIME,UPDATE_TIME) "
				+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
		this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return insertCaseVideoList.size() + updateCaseVideoList.size();
			}

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, insertCaseVideoList.get(i).getCourtCode());
				ps.setInt(2, insertCaseVideoList.get(i).getReceptionId());
				if (insertCaseVideoList.get(i).getStartTime() == null)
					ps.setDate(4, null);
				else
					ps.setDate(4, new Date(insertCaseVideoList.get(i).getStartTime().getTime()));
				
				if (insertCaseVideoList.get(i).getCreateUserId() == null)
					ps.setInt(12, 0);
				else
				ps.setInt(12, insertCaseVideoList.get(i).getCreateUserId());
				//....其他略去不看,只演示Integer String Date的写法
			}
		});

	}

关键代码在于this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {...});一句,使用了特殊的jdbcTemplate连接其他数据库,使用了BatchPreparedStatementSetter方法配置sql填写,批量处理---由于设计问题,id在不同数据库间重复,迁移前后id变化,暂未想到高效的办法批量更新目标数据库的旧数据,所以只有insert语句没有update语句---希望以后能用Snowflake这类工具生成不同数据库也不重复的id.

跨数据库操作关键看jdbcTemplate,是配置在context.xml中的,实际如下:

	<bean id="dataSource_jdbc" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
        <property name="jdbcUrl" value="jdbc:mysql://ip地址/数据库名称"></property>
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="user" value="帐号"></property>
        <property name="password" value="密码"></property>
        <property name="minPoolSize" value="5"></property>
        <property name="maxPoolSize" value="50"></property>
        <property name="initialPoolSize" value="5"></property>
    </bean>  
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource_jdbc"></property>
    </bean>

以上已经介绍完连接其他数据库进行操作的过程,之后是操作完了,将来源数据表的backupFlag重置的过程.问题在于,若是第一次迁移旧数据,数据量会很大,需要想办法优化.

首先想到的是项目常用的XXXService.updateBatch(XXXHolderList);但这句实际封装的代码是遍历列表,挨个触发.update(),执行的sql大致如下:

update 表名 set
		<trim suffixOverrides=",">
			.........
<if test="backupFlag != null">
				`BACKUP_FLAG` = #{backupFlag},
			</if>
		</trim>
		where ID=#{id}
	</update>

很显然,这一语句不是很符合需要,因为修改字段很明确就是backupFlag,其他判断会导致性能下降.于是想到了这个:

	<update id="updateBackupFlag" parameterType="com.....SicSicCaseVideoHolder">
		update 表名 set `BACKUP_FLAG` = 0
		where ID=#{id}
	</update>

似乎是会好一些,然而实际操作中10W条数据用了半个小时左右.恍然大悟,10W条的数据用了10W次sql建立连接再关闭,性能太低;使用sql的where id in (1,2,3,..)才好.于是改成了如下:

    <update id="updateBackupFlagBatch">
    		UPDATE 表名 SET `BACKUP_FLAG` = 0
    		WHERE ID IN (
    		<foreach collection="lists" item="emp" separator=",">#{emp.id}</foreach>)
    </update>

性能确实有提高,用时3分30秒.

至此,这一功能基本完成了.不过仍然有很多想问的问题,比如,这个备份操作要不要视为一个事务?已经设定在无人操作时执行且用的InnoDB,视为事务后要不要考虑阻塞 事务锁等待超时?

也有同事提议我使用Thread+BlockingQueue,程序启动后打开线程,检查有没有新的要备份的记录,若有则加入队列,没有则线程休眠;当队列到一定长度时,进行提交.这能平滑负载,降低服务器的瞬时压力,但在无人使用时触发似乎性能也还行,挺纠结的.如果有懂的大哥看到这里,希望能指点一下,不胜感激.

----------

补充遇到的一些问题:

### SQL: update sic_case_video_record set    `ID` = ?,                                                         `TRANSFER_STATUS` = ?,                                             `UPDATE_USER_ID` = ?,                 `UPDATE_TIME` = ?    where ID=?
### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:259)

这是我用最开始的方法运行时报的错,百度了一下,是事务锁等待超时,InnoDB默认为50s(innodb_lock_wait_timeout=50s),一旦数据库锁超过此时间则报错.