百万条数据数据库删除索引和老数据 数据库千万级数据删除

场景：

公司一张表在前期设计时没有考虑数据量以及代码设计上的不足，导致数据库存量达到三千万。导致后台系统无法操作，只能走脚本来操作，在修改代码不足，后考虑对数据库进行数据删除及备份，保存14天数据，备份30天（累计存储44天）

前期工作：

原表：simple_ac_mq_message

备份表：simple_ac_mq_message_back

配置文件

#定时归档任务规则
BACKUP_NUMBER=20|1000

定时任务说明

利用Rocket延迟队列设置定时任务，没30分钟执行一次方法

数据操作说明

原表数据量3千万+，从原表中抽出30天数据进行备份，原表存14天数数据

具体操作分为三步：

1、删除原表44天前的数据，

2、将原表14天到44天的数据进行备份

3、删除备份表中44天后的数据

为了提高效率，在备份数据时java中将事务提交改为自动提交，每操作1000条数据提交一次事务，每个定时任务循环20遍

代码实现

时间工具类

用户调用方法传入指定天数，即可获得多少天前对应日期"yyyy-MM-dd HH:mm:ss"格式的时间戳

public class TimeUtil {
	/**
	 * 
	 * @Description: 根据传入的参数获取指定时间
	 * @author xxs
	 * @date 2019年12月19日 下午2:15:20
	 * @param timeType
	 *            时间类型 1：分钟 2：天 3：月4：年
	 * @param time
	 *            需要获取到多长时间
	 * @return
	 */
	public static String getTime(String timeRang) {
		Calendar instance = Calendar.getInstance();
		String time = BackUpTimeEnum.getTimeRangeEnum(timeRang).getValue();
		// 获取指定的分钟
		instance.add(Calendar.DATE, Integer.valueOf("-" + time));

		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String format = simpleDateFormat.format(instance.getTime());
		return format;

	}
}

数据备份java代码实现

数据备份时，因为事务的调用属于内部调用，在spring boot 中事务回滚是通过代理模式实现的，只有调用代理对象才会触发事务，一个没有事务的方法调用该类有事务的方法，直接this.method(),是不产生代理事务的，所以事务不起作用。而解决的方法就是创建本类的代理对象，再调用代理对象，使事务生效。

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import com.sgcc.controller.AcOperatorController;
import com.sgcc.dao.AcMqJsonMessageBackDao;
import com.sgcc.dao.AcMqJsonMessagePersistenceService;
import com.sgcc.model.activity.SimpleAcMqMessage;
import com.sgcc.utils.LogUtil;
import com.sgcc.utils.TimeUtil;

/**
 * 
 * @author 作者 
 * 
 * @version 创建时间：2019年
 * 
 *          类说明 : 数据备份
 * 
 */
@Component
@RefreshScope
public class BackUpDataServiceImpl implements BackUpDataService {

	private final static LogUtil logger = new LogUtil(AcOperatorController.class);

	@Autowired
	private AcMqJsonMessagePersistenceService acMqJsonMessagePersistenceServiceimpl;
	@Autowired
	private AcMqJsonMessageBackDao acMqJsonMessageBackDao;

	
	@Autowired
	private BackUpDataServiceImpl backUpDataServiceImpl;

	@Value("${BACKUP_NUMBER:20|1000}")
	private String backUpNumber;
	
	

	@Override
	public void backUpData() {
		try {
			logger.info("【备份请求信息】任务开始");
			String timeRang1 = TimeUtil.getTime("14"); // 备份
			String timeRang2 = TimeUtil.getTime("44");// 删除
			String[] split = backUpNumber.split("\\|");
			Integer number = Integer.valueOf(split[0]);
			String backUpNumber = split[1];
			// 第一大步删除44天前的数据
			logger.info("【备份请求数据】时间节点是："+timeRang1+"【删除请求数据】时间节点是：" + timeRang2 + "遍历次数:"+number+"每次删除条数:" + backUpNumber);
			long currentTimeMillis1S = System.currentTimeMillis();
			for (int i = 0; i < number; i++) {
				Map<String, Object> map = new HashMap<>();
				map.put("timeRang2", timeRang2);
				map.put("backUpNumber", backUpNumber);
				try {
					long singleStart  = System.currentTimeMillis();
					Integer deleteNumbers = acMqJsonMessagePersistenceServiceimpl.deleteOldData(map);
					logger.info("单次删除simple_ac_mq_message数据--耗时："+(System.currentTimeMillis() - singleStart)+"删除"+deleteNumbers+"条数据");
					if (deleteNumbers == null || deleteNumbers == 0) {
						break;
					}
				} catch (Exception e) {
					logger.error("【删除请求表数据】出现异常", e);
				}
			}
			logger.info("删除simple_ac_mq_message--数据--耗时：" + (System.currentTimeMillis() - currentTimeMillis1S));
			// 第二大步备份44到14天的数据
			long currentTimeMillis2S = System.currentTimeMillis();
			for (int i = 0; i < number; i++) {
				Map<String, Object> map2 = new HashMap<>();
				map2.put("timeRang1", timeRang1);
				map2.put("timeRang2", timeRang2);
				map2.put("backUpNumber", backUpNumber);
				List<SimpleAcMqMessage> selectOldData = acMqJsonMessagePersistenceServiceimpl.selectOldData(map2);
				if(CollectionUtils.isEmpty(selectOldData)){
					logger.info("【请求表需要备份的数据】查询出的条数是:"+0);
					continue;
				}
				logger.info("【请求表需要备份的数据】查询出的条数是:"+selectOldData.size());
				backUpDataServiceImpl.transData(selectOldData);
			}
			long currentTimeMillis2E = System.currentTimeMillis();
			logger.info("迁移老数据用时：" + (currentTimeMillis2E - currentTimeMillis2S) + "ms");
			// 第三大步，删除备份表中44天前的数据
			long currentTimeMillis3S = System.currentTimeMillis();
			for (int i = 0; i < number; i++) {
				Map<String, Object> map3 = new HashMap<>();
				map3.put("timeRang2", timeRang2);
				map3.put("backUpNumber", backUpNumber);
				try {
					long start = System.currentTimeMillis();
					Integer deleteBackNumbers = acMqJsonMessageBackDao.deleteBackData(map3);
					logger.info("单次删除simple_ac_mq_message_back_up数据--耗时："+(System.currentTimeMillis() - start)+"删除"+deleteBackNumbers+"条数据");
					if (deleteBackNumbers == null || deleteBackNumbers == 0) {
						break;
					}
				} catch (Exception e) {
					logger.error("【删除备份表老数据】出现异常", e);
				}
			}
			logger.info("删除simple_ac_mq_message_back_up--数据--耗时：" + (System.currentTimeMillis() - currentTimeMillis3S));
			logger.info("数据归档整体--耗时：" + (System.currentTimeMillis() - currentTimeMillis1S));
		} catch (Exception e) {
			logger.error("【数据归档】--异常：",e);
		}
		
	}


	
	@Transactional(readOnly = true, rollbackFor = Exception.class)
	public void transData(List<SimpleAcMqMessage> item) {
		long start1 = System.currentTimeMillis();
		acMqJsonMessageBackDao.insertBackOldData(item);
		long start2 = System.currentTimeMillis();
		logger.info("插入simple_ac_mq_message_back_up 耗时："+(start2 - start1));
		acMqJsonMessagePersistenceServiceimpl.deleteBackOldData(item);
		logger.info("删除simple_ac_mq_message 耗时："+(System.currentTimeMillis() - start2));
	}

}

在备份数据时，先在原表中查询14天到44天的数据，将其插入备份表中，再做删除操作，中间若出现异常，则事务回滚

Mapper实现

删除原表44天前数据（一次删1000条）

<delete id="deleteOldData" parameterType="map">
    delete from SIMPLE_AC_MQ_MESSAGE t
    where to_char(CREATE_TIME,'yyyy-mm-dd HH24:MI:SS') <
		#{timeRang2,jdbcType=VARCHAR} and rownum <#{backUpNumber}
  </delete>

分页查询14天到44天之间的数据（查1000条）

<select id="selectOldData" resultMap="BaseResultMap"
		parameterType="map">
		select * from (select id as "id", user_id as "userId", province_code as
		"provinceCode",serial_no
		as "serialNo", sub_serial_no as "subSerialNo",
		tag as "tag", buss_type
		as
		"bussType", status as "status", ext1 as
		"ext1", ext2 as "ext2",
		to_char(create_time,'yyyy-mm-dd hh24:mi:ss') as
		"createTime",dbms_lob.substr(JSON_STR) as "jsonStr",
		power_user_id as
		"powerUserId"
		from SIMPLE_AC_MQ_MESSAGE
		where 1=1 
		<if test="timeRang1 != null and timeRang1 != ''">
		and to_char(CREATE_TIME,'yyyy-mm-dd HH24:MI:SS') <![CDATA[<]]>
		#{timeRang1,jdbcType=VARCHAR}
		</if> 
		<if test="timeRang2 != null and timeRang2 != ''">
		and to_char(CREATE_TIME,'yyyy-mm-dd HH24:MI:SS') <![CDATA[>=]]>
		#{timeRang2,jdbcType=VARCHAR}
		</if> 
		order by create_time desc)  where  rownum
		<#{backUpNumber,jdbcType=VARCHAR}
	</select>

将查到的数据插入到备份表

<insert id="backUpOldData" parameterType="java.util.List">
		insert into SIMPLE_AC_MQ_MESSAGE_BACK
		(
		<include refid="Base_Column_List"></include>
		)
		<foreach collection="list" item="item" index="index" open="("
			close=")" separator="union">
		<![CDATA[
		        select 
		        #{item.id,jdbcType=VARCHAR}, #{item.userId,jdbcType=VARCHAR}, #{item.provinceCode,jdbcType=VARCHAR}, 
      			#{item.serialNo,jdbcType=VARCHAR}, #{item.subSerialNo,jdbcType=VARCHAR}, #{item.createTime,jdbcType=VARCHAR}, 
      			#{item.jsonStr,jdbcType=CLOB},#{item.tag,jdbcType=VARCHAR},#{item.bussType,jdbcType=VARCHAR},#{item.status,jdbcType=VARCHAR},
      			#{item.ext1,jdbcType=VARCHAR},#{item.ext2,jdbcType=VARCHAR},#{item.powerUserId,jdbcType=VARCHAR}
		        from dual
		  ]]>
		</foreach>
	</insert>

删除已经备份的数据

<delete id="deleteBackOldData" parameterType="java.util.List">
		delete from SIMPLE_AC_MQ_MESSAGE t
		where 1=1 and t.ID in
		<foreach collection="list" index="index" item="item" open="("
			separator="," close=")">
			#{item.id}
		</foreach>
	</delete>

删除备份表中40天前的数据（一次删除1000条）

<delete id="deleteBackData"  parameterType="map">
	delete from SIMPLE_AC_MQ_MESSAGE_BACK t
		where to_char(CREATE_TIME,'yyyy-mm-dd HH24:MI:SS') <
		#{timeRang2,jdbcType=VARCHAR} and rownum <#{backUpNumber}
	</delete>