mybatis分表插入与查询(一)

对于记录表，具有数据量大、频繁从表尾插入、经常检索最近数据等特点，若采用索引，数据量很大的时候索引也会占据不小的内存，且会增加插入数据的效率，分表查询是一种很不错的解决方案。

分表原理

记录表分表，采用时间分表，每隔一段时间，新的数据存入新表，查询的话，根据查询时间，对特定表进行查询，极大的缩减的检索范围，提高检索效率
本文采用对季度进行分表，每一季度数据存储一张记录表

操作步骤

1、建表

CREATE TABLE `record`  (
  `rid` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `aid` int NULL DEFAULT NULL COMMENT '记录账户ID',
  `bid` int NULL DEFAULT NULL COMMENT 'bID',
  `type` int NULL DEFAULT NULL COMMENT '类型',
  `amount` int NULL DEFAULT NULL COMMENT '金额',
  `creat_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `r_desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '记录描述',
  PRIMARY KEY (`rid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '记录表' ROW_FORMAT = Dynamic;

分表时，以2020年第一季度为1，建record_1、record_2、。。。若干表

2、辅助类

/**
 * 分表 工具类
 */
public class SeparateTableUtil {

    /**
     * 获取当前季度
     */
    public static int getCurSeason() {

        Date currentDate=new Date();//当前日期

        String yearYY= DateUtil.formatDateToStr(currentDate,"yy");//年

        int cSeason = (DateUtil.getMonth(currentDate))/3+1; //季度
        int ySeason=(Integer.parseInt(yearYY)-20)*4;//年季度
        int season = cSeason+ySeason;

        return season;
    }

    /**
     * @param month 获取近几个月得表名（1，3，6，12）
     * @return list<表名后缀> 季度，20年1月作为第一季度
     */
    public static List<String> tableNamesByDate(int month) {

        List<String> resList =new ArrayList<>();

        int season = getCurSeason();

        //默认  一个月/三个月，近两个季度
        resList.add(String.valueOf(season));
        resList.add(String.valueOf(season - 1));
        if (month == 12) {//一年，近五个季度
            resList.add(String.valueOf(season - 2));
            resList.add(String.valueOf(season - 3));
            resList.add(String.valueOf(season - 4));
        } else if (month == 6) {//半年，近三个季度
            resList.add(String.valueOf(season - 2));
            resList.add(String.valueOf(season - 3));
        }
        
        return resList;
    }

}

3、配置文件

    <insert id="insertRecord" parameterType="map">
        insert into record_${season}
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="record.aid != null">aid,</if>
            <if test="record.bid != null">bid,</if>
            <if test="record.type != null">type,</if>
            <if test="record.amount != null">amount,</if>
            <if test="record.creatTime != null">creat_time,</if>
            <if test="record.rDesc != null and record.rDesc != ''">r_desc,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="record.aid != null">#{record.aid},</if>
            <if test="record.bid != null">#{record.bid},</if>
            <if test="record.type != null">#{record.type},</if>
            <if test="record.amount != null">#{record.amount},</if>
            <if test="record.creatTime != null">#{record.creatTime},</if>
            <if test="record.rDesc != null and record.rDesc != ''">#{record.rDesc},</if>
         </trim>
    </insert>

    <select id="selectRecentMonth" parameterType="map" resultMap="recordResult">
        select ut.* from
        <foreach collection="tableNames" item="tableName" separator="union all" open="(" close=")">
            SELECT * FROM record_${tableName}  where aid=#{aid}
        </foreach> ut
    </select>

4、测试方法

    @Resource
    private RecordMapper recordMapper;

    /**
     * 查询近几个月记录
     *
     * @return 记录表
     */
    public List<Record> listRecentMonth(Integer month,int aid) {
        List<String> monthList = SeparateTableUtil.tableNamesByDate(month);
        Map<String,Object> qeuryMap =new HashMap<>();
        qeuryMap.put("aid",aid);
        qeuryMap.put("tableNames",monthList);
        return recordMapper.selectRecentMonth(qeuryMap);
    }

    @Test
    public void contextLoads() {

        Record record=new Record();
        record.setAid(2);
        record.setAmount(15);
        record.setBid(2);
        record.setCreatTime(new Date());
        record.setRDesc("jiug");
        record.setType(1);

        int season= SeparateTableUtil.getCurSeason();

        Map<String,Object> map=new HashMap<>();
        map.put("record",record);
        map.put("season",season);

        recordMapper.insertRecord(map);

    }