Low‑Cost Database Stability Improvement and Lightweight Table Partitioning Using MyBatis Interceptor

The article starts with a background describing a core business table that grows at a rate of 1 million rows per day, causing frequent slow‑SQL incidents and degrading system stability.

It then reviews typical industry solutions for large tables: (1) deleting or archiving old data, and (2) horizontal sharding. The drawbacks of deletion/archiving—performance impact, fragmentation, and risk—are discussed, leading to the decision to adopt sharding.

For sharding, two options are considered: Sharding‑JDBC (feature‑rich but with integration overhead) and a MyBatis interceptor (low‑cost, no new framework). The article chooses the MyBatis interceptor because the project already uses MyBatis and the interceptor can replace table names at runtime.

Sharding configuration objects :

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ShardingProperty {
    // sharding period in days, e.g., 7 for weekly sharding
    private Integer days;
    // start date for calculating period table names
    private Date beginDate;
    // original table name to be sharded
    private String tableName;
}

Sharding configuration class :

import java.util.concurrent.ConcurrentHashMap;

public class ShardingPropertyConfig {
    public static final ConcurrentHashMap<String, ShardingProperty> SHARDING_TABLE = new ConcurrentHashMap<>();
    static {
        ShardingProperty orderInfoShardingConfig = new ShardingProperty(15, DateUtils.string2Date("20231117"), "order_info");
        ShardingProperty userInfoShardingConfig = new ShardingProperty(7, DateUtils.string2Date("20231117"), "user_info");
        SHARDING_TABLE.put(orderInfoShardingConfig.getTableName(), orderInfoShardingConfig);
        SHARDING_TABLE.put(userInfoShardingConfig.getTableName(), userInfoShardingConfig);
    }
}

MyBatis interceptor implementation :

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.*;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.Properties;

@Slf4j
@Component
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class ShardingTableInterceptor implements Interceptor {
    private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
    private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
    private static final ReflectorFactory DEFAULT_REFLECTOR_FACTORY = new DefaultReflectorFactory();
    private static final String MAPPED_STATEMENT = "delegate.mappedStatement";
    private static final String BOUND_SQL = "delegate.boundSql";
    private static final String ORIGIN_BOUND_SQL = "delegate.boundSql.sql";
    private static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");
    private ConfigUtils configUtils = SpringContextHolder.getBean(ConfigUtils.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        boolean shardingSwitch = configUtils.getBool("sharding_switch", false);
        if (!shardingSwitch) {
            return invocation.proceed();
        }
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECTOR_FACTORY);
        MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue(MAPPED_STATEMENT);
        BoundSql boundSql = (BoundSql) metaStatementHandler.getValue(BOUND_SQL);
        String originSql = (String) metaStatementHandler.getValue(ORIGIN_BOUND_SQL);
        if (StringUtils.isBlank(originSql)) {
            return invocation.proceed();
        }
        String tableName = TemplateMatchService.matchTableName(boundSql.getSql().trim());
        ShardingProperty shardingProperty = ShardingPropertyConfig.SHARDING_TABLE.get(tableName);
        if (shardingProperty == null) {
            return invocation.proceed();
        }
        String shardingTable = getCurrentShardingTable(shardingProperty, new Date());
        String rebuildSql = boundSql.getSql().replace(shardingProperty.getTableName(), shardingTable);
        metaStatementHandler.setValue(ORIGIN_BOUND_SQL, rebuildSql);
        if (log.isDebugEnabled()) {
            log.info("rebuildSQL -> {}", rebuildSql);
        }
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        }
        return target;
    }

    @Override
    public void setProperties(Properties properties) {}

    public static String getCurrentShardingTable(ShardingProperty shardingProperty, Date createTime) {
        String tableName = shardingProperty.getTableName();
        Integer days = shardingProperty.getDays();
        Date beginDate = shardingProperty.getBeginDate();
        Date date = (createTime == null) ? new Date() : createTime;
        if (date.before(beginDate)) {
            return null;
        }
        LocalDateTime targetDate = SimpleDateFormatUtils.convertDateToLocalDateTime(date);
        LocalDateTime startDate = SimpleDateFormatUtils.convertDateToLocalDateTime(beginDate);
        LocalDateTime intervalStartDate = DateIntervalChecker.getIntervalStartDate(targetDate, startDate, days);
        LocalDateTime intervalEndDate = intervalStartDate.plusDays(days - 1);
        return tableName + "_" + intervalStartDate.format(FORMATTER) + "_" + intervalEndDate.format(FORMATTER);
    }
}

The article highlights a critical‑point issue: when a new weekly table is created at the boundary (e.g., 2023‑01‑08), the table is initially empty, causing queries for the most recent week to miss data. To solve this, the author proposes duplicating the previous week’s data into the new table, achieving a sliding‑window effect.

Binlog double‑write solution (code shown briefly):

@Slf4j
@Component
public class BinLogConsumer implements MessageListener {
    private static final String TABLE_PLACEHOLDER = "%TABLE%";
    @Value("${mq.doubleWriteTopic.topic}")
    private String doubleWriteTopic;
    @Autowired
    private JmqProducerService jmqProducerService;
    // onMessage and syncData methods implement the logic of reading binlog events,
    // determining which tables need to be synchronized (old, current, next),
    // and executing double‑write SQL statements while avoiding infinite loops.
}

Finally, the article mentions a data‑comparison step before release to ensure the new sharded tables contain the same data as the original tables, typically by querying a sample set from each table, converting results to JSON, and comparing them.

The article concludes with an "END" marker, indicating the end of the technical guide.