Help Center/ Distributed Database Middleware/ User Guide (ME-Abu Dhabi Region)/ SQL Syntax/ DDL/ Sharding Algorithms/ YYYYDD
Updated on 2022-02-22 GMT+08:00
View PDF
Share

YYYYDD

Application Scenarios

This algorithm applies when data is routed to shards by the year and the day of the year specified in the sharding key value.

Instructions

The data type of the sharding key value is DATE, DATETIME, or TIMESTAMP.

Data Routing

The data route depends on the remainder of the hash value divided by the number of shards or sharded tables. Use the hash function and enter the year and the day of the year specified in the sharding key value to calculate the hash value.

For example, YYYYDD('2018-12-31 12:12:12') is equivalent to (2018 x 366 + 365) % D. D is the number of shards.

2018-12-31 is the 365th day of 2018. In the formula, 366 is a fixed value.

Calculation Method

Table 1 Required calculation methods

Condition

Calculation Method

Example

Database sharding key ≠ Table sharding key

Sharding key: yyyy-MM-dd

Database routing result = (yyyy x 366 + Day of the current year) % Database shards

Table routing result = (yyyy x 366 + Day of the current year) % Table shards

Sharding key: 2012-12-31

Database shard: (2012 x 366 + 366) % 8 = 6

Table shard: (2012 x 366 + 366) % 3 = 0

Database sharding key = Table sharding key

Sharding key: yyyy-MM-dd

Table routing result = (yyyy x 366 + Day of the current year) % (Database shards x Table shards)

Database routing result = Table routing result / Table shards

Sharding key: 2012-12-31

Database shard: (2012 x 366 + 366) % (8 x 3) = 6

Database shard: 6 / 3 = 2

Syntax for Creating Tables

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

Precautions

  • The sharding key and its value cannot be modified.
  • The YYYYDD algorithm does not apply if each day of a year corresponds to one shard.
  • Data of the same day in different years may be routed to the same shard.
Parent topic: Sharding Algorithms