PolarDB:YYYYMM

How it works

YYYYMM computes a hash value from the year and month of the partitioning key, then distributes records across database shards using modulo arithmetic:

hash value = (year × 12 + month) % D

where D is the number of database shards.

For example, YYYYMM('2012-12-31 12:12:12') evaluates to (2012 × 12 + 12) % D, routing the record to the shard that corresponds to December 2012.

Limitations

• The partitioning key must be of the DATE, DATETIME, or TIMESTAMP type.

• Determine the total number of physical table shards before creating the table. Base this on a cycle length (for example, 2 years). YYYYMM allocates one table shard per month within the cycle.

• After a cycle completes, months recur and data routes to the same shards as in the previous cycle. For example, with a two-year cycle starting in March 2012, data from March 2014 routes to the same table shard as data from March 2012. The specific table shard to which the data is routed depends on the number of table shards.

• Requires PolarDB-X 1.0 version 5.1.28-1320920 or later.

Create a table partitioned by YYYYMM

The following example creates a table that distributes data by year and month across 8 database shards, with one table shard per month over a two-year cycle.

Shard planning:

Use tbpartitions 3 to create 3 table shards per database shard.

create table test_yyyymm_tb (
        id int,
        name varchar(30) DEFAULT NULL,
    create_time datetime DEFAULT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
dbpartition by YYYYMM(create_time)
tbpartition by YYYYMM(create_time) tbpartitions 3;

A query that filters on create_time routes directly to the target table shard within its database shard, avoiding full-table scans.

Use cases

YYYYMM works well for datasets where records are naturally grouped by calendar month — for example, order records, log tables, or billing data. Use dbpartition by YYYYMM(ShardKey) with tbpartition by YYYYMM(ShardKey) to distribute data at both the database-shard and table-shard levels and enable precise single-shard routing on month-based queries.