mysql、mybatsi水平分表

最近做了一个员工工作日志增删改查和统计功能，考虑到每天有五六千人填写日志，每个人平均每天可以填写3条，一个月日志数量可以达到30W+，每年日志数量可以达到300W+，不会超过千万，所以根据年做了分表处理，即每年创建一张表。

使用mybatis时要考虑两个问题：

1. 增删改查都要传入年作为选择表的参数；

2. 增和查之前要判断是否已创建表，如果没有要先创建表，否则会报表不存在。

使用update标签做创建表操作，如下：

void createTable(@Param("year") String year);

<update id="createTable" parameterType="java.lang.String">
    CREATE TABLE IF NOT EXISTS work_log_${year} (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `log_date` char(10) NOT NULL COMMENT '日志日期',
    ......
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT '工作日志表'
</update>

增删改查时都要传入年作为参数：

void insertByYear(@Param("workLog") WorkLog workLog, @Param("year") String year);

<insert id="insertByYear" useGeneratedKeys="true" keyProperty="workLog.id">
    insert into work_log_${year}
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="workLog.logDate != null">
            log_date,
        </if>

查询和统计时会有些麻烦：

一种方法是查询一次，join多张表，表的数量不定，mybatis需要使用foreach标签；

另外一种方法就是查询多次，效率会比较低。

第二种方法，需要根据查询条件开始结束时间判断查询次数，并循环查询。

utils类：

/**
 * 根据开始结束日期分配日期和年份
 *
 * @param startDate
 * @param endDate
 * @return
 */
public static List<DateAndYear> getDateAndYear(String startDate, String endDate){
    List<DateAndYear> list = new ArrayList<>();
    String startYear = startDate.substring(0, 4);
    String endYear = endDate.substring(0, 4);

    if(startYear.equals(endYear)) {
        // 开始结束日期在同一年
        DateAndYear data = new DateAndYear();
        data.setStartDate(startDate);
        data.setEndDate(endDate);
        data.setYear(startYear);
        list.add(data);
        return list;
    }

    // 开始日期结束日期不在同一年
    List<String> yearList = getYearBetween(startYear, endYear);
    if(null == yearList || yearList.isEmpty()) {
        return list;
    }

    int size = yearList.size();
    DateAndYear data;
    String sDate = "-01-01";
    String eDate = "-12-31";
    for(int i=0; i<size; i++) {
        data = new DateAndYear();
        data.setYear(yearList.get(i));

        if(i == 0) {
            data.setStartDate(startDate);
            data.setEndDate(yearList.get(i) + eDate);
        } else if(i == size -1) {
            data.setStartDate(yearList.get(i) + sDate);
            data.setEndDate(endDate);
        } else {
            data.setStartDate(yearList.get(i) + sDate);
            data.setEndDate(yearList.get(i) + eDate);
        }
        list.add(data);
    }

    return list;
}

Bean：

import lombok.Data;

/**
 * 工作日志使用
 */
@Data
public class DateAndYear {

    /**
     * 开始日期
     */
    private String startDate;

    /**
     * 结束日期
     */
    private String endDate;

    /**
     * 年
     */
    private String year;

}

使用公共方法处理开始结束时间，并获取到工作日志list：

List<DateAndYear> list = DateUtils.getDateAndYear(startDate, endDate);

List<WorkLog> workLogs = new ArrayList<>();
for(DateAndYear item : list) {
    log.info("year is {}, startDate is {}, endDate is {}", item.getYear(), item.getStartDate(),
            item.getEndDate());

    // 如果表不存在则创建
    mapper.createTable(item.getYear());

    workLogs.addAll(mapper.selectLogBetween(item.getYear(), item.getStartDate(), item.getEndDate()));
}