Practical Guide to Monthly Horizontal Partitioning in MySQL Using Stored Procedures and SpringBoot

1. Business Background and Motivation

The project originates from a hardware‑based point‑of‑sale (POS) system deployed on about 1,000 cash‑register devices, each tied to a single user. Although the overall user count is modest (~1,200), each device generates 500‑600 billing records per day, leading to 5 million rows per day and 1.5 billion rows per month.

Such data volume makes a single monolithic table impractical for storage, indexing, backup, and maintenance, prompting the need for a partitioning solution.

1.1 Project Challenges

Despite a small concurrent user count (max 1,000), the massive daily insert rate stresses the database layer. Simple sharding or micro‑service decomposition does not solve the core issue, which is the rapid growth of the billing table.

2. Monthly Partition Solution

The chosen approach is to keep a single database (single‑instance) but split the billing table horizontally by month, creating tables named month_bills_YYYYMM (e.g., month_bills_202211 ).

2.1 Creating Monthly Tables with a Stored Procedure

The DDL for a monthly table is:

CREATE TABLE `month_bills_202211` (
    `month_bills_id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'Bill ID',
    `serial_number` varchar(50) NOT NULL COMMENT 'Serial Number',
    `bills_info` text NOT NULL COMMENT 'Bill Details',
    `pay_money` decimal(10,3) NOT NULL COMMENT 'Payment Amount',
    `machine_serial_no` varchar(20) NOT NULL COMMENT 'Machine Serial',
    `bill_date` timestamp NOT NULL DEFAULT now() COMMENT 'Bill Date',
    `bill_comment` varchar(100) NULL DEFAULT 'None' COMMENT 'Bill Comment',
    PRIMARY KEY (`month_bills_id`) USING BTREE,
    UNIQUE `serial_number` (`serial_number`),
    KEY `bill_date` (`bill_date`)
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Compact;

A stored procedure create_table_by_month() calculates the next month (format YYYYMM ), builds the CREATE TABLE statement dynamically, and executes it via PREPARE/EXECUTE . The procedure also checks whether the table already exists.

DELIMITER //
DROP PROCEDURE IF EXISTS create_table_by_month //
CREATE PROCEDURE `create_table_by_month`()
BEGIN
    DECLARE nextMonth varchar(20);
    DECLARE createTableSQL varchar(5210);
    DECLARE tableCount int;
    DECLARE tableName varchar(20);
    DECLARE table_prefix varchar(20);
    SELECT SUBSTR(REPLACE(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '-', ''), 1, 6) INTO @nextMonth;
    SET @table_prefix = 'month_bills_';
    SET @tableName = CONCAT(@table_prefix, @nextMonth);
    SET @createTableSQL = CONCAT('CREATE TABLE IF NOT EXISTS ', @tableName, '(...same columns as above...) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=Compact;');
    PREPARE create_stmt FROM @createTableSQL;
    EXECUTE create_stmt;
    DEALLOCATE PREPARE create_stmt;
    SELECT COUNT(1) INTO @tableCount FROM information_schema.`TABLES` WHERE TABLE_NAME = @tableName;
    SELECT @tableCount 'tableCount';
END //
DELIMITER ;

To automate the creation each month, a MySQL event is scheduled to call the procedure on the first day of the month.

CREATE EVENT `create_table_by_month_event`
ON SCHEDULE EVERY 1 MONTH STARTS '2022-11-28 00:00:00'
ON COMPLETION PRESERVE ENABLE
DO CALL create_table_by_month();

2.2 Inserting Data into the Correct Monthly Table

The backend is built with SpringBoot + MyBatis. The entity MonthBills mirrors the table columns and adds a targetTable field to hold the dynamic table name.

public class MonthBills {
    private Integer monthBillsId;
    private String serialNumber;
    private BigDecimal payMoney;
    private String machineSerialNo;
    private Date billDate;
    private String billsInfo;
    private String billComment;
    private String targetTable; // dynamic table name
    // getters/setters omitted
}

A utility class TableTimeUtils provides two methods:

getYearMonth(Date date) – formats a date to YYYYMM .

getDataByTable(MonthBills mb) – sets targetTable to month_bills_ + formatted month.

The MyBatis mapper uses the dynamic table name with `${targetTable}` in the INSERT statement.

insert into ${targetTable}
month_bills_id,
serial_number,
...
#{monthBillsId,jdbcType=INTEGER},
#{serialNumber,jdbcType=VARCHAR},
...

The service layer calls TableTimeUtils.getDataByTable() before invoking the mapper, ensuring each record lands in the proper month‑specific table.

A simple REST endpoint /bills/settleUp receives bill details, sets billDate to the current time, generates a unique serialNumber by concatenating the machine serial and System.currentTimeMillis() , and inserts the record.

3. Post‑Partition Operations

Beyond insertion, the system needs delete, update, and query capabilities.

3.1 Query by Serial Number

The serial number embeds a 13‑digit timestamp, allowing the service to extract the month and compute the target table:

public static void getTableBySerialNumber(MonthBills mb) {
    String timeMillis = mb.getSerialNumber().substring(mb.getSerialNumber().length() - 13);
    long millis = Long.parseLong(timeMillis);
    String yearMonth = getYearMonth(new Date(millis));
    mb.setTargetTable(tablePrefix + yearMonth);
}

The mapper then runs a SELECT on ${targetTable} using the provided serial number.

3.2 Range Query by Date

Two utility methods support querying across multiple monthly tables:

getRangeQueryByTables(String start, String end) – returns a list of table names between the two months.

getRangeQuerySQL(String start, String end) – builds a UNION‑ALL SQL that selects from each relevant table, applying appropriate BETWEEN or > / < conditions for the first and last tables.

The mapper receives the generated SQL string and executes it, returning a list of MonthBills objects.

4. Summary of In‑Database Partitioning

Monthly horizontal partitioning solves the storage and performance bottleneck of a massive billing table while keeping the overall architecture simple (single‑instance, single‑database). The same technique can be extended to half‑month, weekly, or daily partitions if data volume grows further. For even larger scale, a full sharding (split‑database) strategy or a framework like Sharding‑JDBC may be preferable.

Overall, the article demonstrates a complete end‑to‑end implementation: table creation, dynamic insertion, serial‑number based lookup, and date‑range queries, all using plain MySQL features and SpringBoot/MyBatis without additional third‑party dependencies.