程序数据备份

最近接触的项目由于数据量很大（千万级），导致查询很慢，于是和项目经理商量，只保留正式表的50个工作日数据，其他都备份到历史表中。

controller类

    @GetMapping("/testCreateTable")
    public String testCreateTable(@RequestParam("routerName") String routerName,
                                  @RequestParam("startNum") int startNum,
                                  @RequestParam("numSize") int numSize){
        String[] tableNames={"SYSTEM_ADUIT"};

        //第一种：根据指定分页的大小及参数内容进行创建新表备份 【适合数据量很少很少】
        String dateStr = DateUtil.getDateyyyyMmDdHHmmss();
        for(String tableName:tableNames) {
            String columns=systemAduitMapper.queryColumns(tableName);
            int table = systemAduitMapper.createTable(tableName,tableName+"_"+dateStr,columns,routerName,startNum,numSize);
            System.out.println("****************" + table);
        }

        //第二种：保留最近2个工作日，其他备份到历史表中，并将正式表数据删除

        //保留天数
        int reserveDay=2;
        //每轮备份10天
        int dayEveryBak=3;

        for(String tableName:tableNames) {
            try {
                //1.查询该表距离5个工作日需要备份的天数
                List<String> dayAll = systemAduitMapper.queryLastDayByLimit(0, tableName);
                //正式表工作日总数
                int dayAllCount = dayAll.size();

                while (dayAllCount > reserveDay) {
                    if (dayAllCount - dayEveryBak > reserveDay) {
                        dayAllCount = dayAllCount - dayEveryBak;
                    } else {
                        dayAllCount = reserveDay;
                    }
                    //2.查询最近时间
                    List<String> dayLast = systemAduitMapper.queryLastDayByLimit(dayAllCount-1, tableName);
                    String columns = systemAduitMapper.queryColumns(tableName);
                    System.out.println("备份时间"+dayLast);
                    //3.备份到历史表
                    systemAduitMapper.insertTable(tableName, tableName + "_HIS", columns, dayLast.get(0));
                    //4.删除正式表
                    systemAduitMapper.deleteByDayLast(tableName,dayLast.get(0));
                }
            }catch (Exception e){
                logger.error("{}表备份出现异常!",tableName,e);
            }
        }
        return "success";
    }

Mapper类

    //根据分页查询最近日期
    List<String> queryLastDayByLimit(@Param("reserveDay") int reserveDay,@Param("tableName") String tableName);

    //插入表内容
    public int insertTable(@Param("tableName") String tableName,@Param("backTableName") String backTableName,@Param("columns") String columns,
                           @Param("dayLast") String dayLast);

    //根据日期删除表数据
    int deleteByDayLast(@Param("tableName") String tableName,@Param("dayLast") String dayLast);

    //根据表名查询所有列
    String queryColumns(@Param("tableName") String tableName);

    //创建表
    public int createTable(@Param("tableName") String tableName,@Param("backTableName") String backTableName,@Param("columns") String columns,
                           @Param("routerName") String routerName,@Param("startNum") int startNum,@Param("numSize") int numSize);

xml文件

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bigdata.bigdata.mapper.SystemAduitMapper">

    <select id="queryColumns" parameterType="string" resultType="string">
        SELECT
          GROUP_CONCAT(COLUMN_NAME)
        FROM
         INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = #{tableName}
    </select>

    <select id="queryLastDayByLimit" resultType="string">
        select * from (
            select DISTINCT DATE_FORMAT( opertion_time,'%Y-%m-%d') as opertion_time
            from ${tableName}
            order by opertion_time desc)t
        <if test="reserveDay!=0">
            limit #{reserveDay},1
        </if>
    </select>

    <delete id="deleteByDayLast">
        delete FROM ${tableName}
        where  DATE_FORMAT( opertion_time,'%Y-%m-%d') &lt; STR_TO_DATE(#{dayLast},'%Y-%m-%d')
    </delete>

    <insert id="insertTable">
        INSERT INTO  ${backTableName} (${columns}) SELECT ${columns} FROM ${tableName}
        where  DATE_FORMAT( opertion_time,'%Y-%m-%d') &lt; STR_TO_DATE(#{dayLast},'%Y-%m-%d')
    </insert>

    <insert id="createTable">
        create table ${backTableName} as select ${columns} from (
        select * from ${tableName}
        where router_name=#{routerName} limit #{startNum},#{numSize})t
    </insert>

</mapper>