Updated on 2022-02-22 GMT+08:00

Sharding Algorithm Overview

Supported Sharding Algorithms

DDM supports database sharding, table sharding, and a variety of sharding algorithms.

Table 1 Sharding algorithms

Function

Description

Database Sharding

Table Sharding

MOD_HASH

Sharding using modulo operation

Yes

Yes

UNI_HASH

Sharding using modulo operation

Yes

Yes

RIGHT_SHIFT

Sharding using right-shift operation of the database sharding key

Yes

Yes

YYYYMM

Sharding by year and month

Yes

Yes

YYYYDD

Sharding by year and day

Yes

Yes

YYYYWEEK

Sharding by year and week

Yes

Yes

HASH

Sharding using modulo operation

Yes

Yes

RANGE

Sharding by range

Yes

No

MM

Sharding by month

No

Yes

DD

Sharding by date

No

Yes

MMDD

Sharding by month and day

No

Yes

WEEK

Sharding by week

No

Yes

  • Database and table sharding keys cannot be left blank.
  • In DDM, sharding of a logical table is defined by the sharding function (number of shards and routing algorithm) and the sharding key (including MySQL data type).
  • When database and table sharding algorithms of a logical table are different, full-shard or full-table scanning is performed if you do not specify conditions for database and table sharding in SQL queries.

Data Type of Sharding Algorithms

Different sharding algorithms support different data types. The following table lists supported data types.

Table 2 Supported Data types

Sharding Algorithm

TINYINT

SMALLINT

MEDIUMINT

INT

BIGINT

CHAR

VARCHAR

DATE

DATETIME

TIMESTAMP

OTHERS

HASH

×

UNI_HASH

×

×

×

×

RIGHT_SHIFT

×

×

×

×

×

×

YYYYDD

×

×

×

×

×

×

×

×

YYYYWEEK

×

×

×

×

×

×

×

×

YYYYMM

×

×

×

×

×

×

×

×

Table Creation Syntax of Sharding Algorithms

DDM is compatible with table creation syntax of MySQL databases and adds keyword partition_options for databases and tables sharding.

CREATE TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
 partition_options:
  DBPARTITION BY
       {{RANGE|HASH|MOD_HASH|RIGHT_SHIFT|YYYYMM|YYYYWEEK|YYYYDD}([column])}
      [TBPARTITION BY
{{HASH|MOD_HASH|UNI_HASH|RIGHT_SHIFT|YYYYMM|YYYYWEEK|YYYYDD}(column)}
          [TBPARTITIONS num]
      ]