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]
      ]

Parent topic: DDL

Previous topic: Creating a Table

Next topic: Sharding Algorithms