This topic describes how to use the YYYYDD function.

Limits

  • A shard key must be of the DATE, DATETIME, or TIMESTAMP type.
  • Before you use the YYYYDD function, determine the total number of physical table shards that are required based on a specific cycle, such as two years. The YYYYDD function can be used to create only one table shard for each day within a cycle.
  • When a date recurs in the next cycle, data generated on the date may be routed to the same table shards that store data generated on the same date in the last cycle. For example, if you specify a two-year cycle starting from March 1, 2012, data generated on March 1, 2014 in the next cycle may be routed to the same table shard that stores data generated on March 1, 2012. The table shard to which the data is routed depends on the number of table shards.
  • The version of the PolarDB-X 1.0 instance must be 5.1.28-1320920 or later.

Routing method

You can use the YYYYDD function to calculate hash values based on the years and days in the time values of a database shard key. Then, the function divides the hash values by the number of database shards to obtain the remainders. As a result, data is partitioned based on the remainders.

For example, if you specify parameters for the YYYYDD function in the YYYYDD('2012-12-31 12:12:12') format, the remainder is calculated based on the following formula: (2012 x 366 + 366)%D, in which D indicates the number of database shards. The calculation result indicates that December 31, 2012 is the 366th day of year 2012.

Scenarios

The YYYYDD function is suitable for scenarios in which data needs to be partitioned into database shards by year and day of the year. We recommend that you use this function with tbpartition by YYYYDD(ShardKey).

Example

In this example, the PolarDB-X 1.0 instance has two nodes. By default, each node has eight database shards. The data must be partitioned based on the following requirements:

  • Data is partitioned into the database shards by year and day of the year.
  • Data generated on the same day is partitioned into the same table shard. Each day within two years corresponds to an independent table shard.
  • A query is directly distributed to a specific physical table shard of a database shard if shard keys are specified in the query.

The YYYYDD function can meet the preceding requirements. You require that each day within two years correspond to a table shard. Therefore, a total of 732 (366 x 2) physical table shards must be created because a year has up to 366 days. A PolarDB-X 1.0 instance has 16 database shards. The number of physical table shards in each database shard is calculated in the following two steps: 1. Divide the total number of physical table shards that must be created by the number of database shards. 2. Round the result up to the next nearest integer. In this case, the number of physic table shards is 46, which is the nearest integer to 45.75 (732/16). We recommend that the number of table shards be an integer multiple of the number of database shards.

You can use the following Data Definition Language (DDL) statement to create tables:

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