Sharding Algorithm Overview

Supported Sharding Algorithms

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

**Table 1** Sharding algorithms
Algorithm	Description	Database Sharding Supported	Table Sharding Supported
MOD_HASH	Performing a simple modulo operation	Yes	Yes
MOD_HASH_CI	Performing a simple modulo operation (case-insensitive)	Yes	Yes
HASH	Calculating the CRC32 value and performing the modulo operation	Yes	Yes
RANGE	Performing a RANGE-based operation	Yes	No
RIGHT_SHIFT	Arithmetically right shifting a sharding key value and then performing a modulo operation	Yes	Yes
YYYYMM	Getting a hash code for a YearMonth object and then performing a modulo operation	Yes	Yes
YYYYDD	Getting a hash code for a YearDay object and then performing a modulo operation	Yes	Yes
YYYYWEEK	Getting a hash code for a YearWeek object and then performing a modulo operation	Yes	Yes
MM	Getting a hash code for a MONTH object and then performing a modulo operation	No	Yes
DD	Getting a hash code for a DAY object and then performing a modulo operation	No	Yes
MMDD	Getting a hash code for a MonthDay object and then performing a modulo operation	No	Yes
WEEK	Getting a hash code for a WEEK object and then performing a modulo operation	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 (MySQL data type).
If a logical table uses different database and table sharding algorithms, DDM will perform full-shard or full-table scanning when you do not specify database and table conditions 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	INTEGER	INT	BIGINT	CHAR	VARCHAR	DATE	DATETIME	TIMESTAMP	OTHERS
MOD_HASH	√	√	√	√	√	√	√	√	╳	╳	╳	╳
MOD_HASH_CI	√	√	√	√	√	√	√	√	╳	╳	╳	╳
HASH	√	√	√	√	√	√	√	√	╳	╳	╳	╳
RANGE	√	√	√	√	√	√	╳	╳	╳	╳	╳	╳
RIGHT_SHIFT	√	√	√	√	√	√	╳	╳	╳	╳	╳	╳
YYYYMM	╳	╳	╳	╳	╳	╳	╳	╳	√	√	√	╳
YYYYDD	╳	╳	╳	╳	╳	╳	╳	╳	√	√	√	╳
YYYYWEEK	╳	╳	╳	╳	╳	╳	╳	╳	√	√	√	╳
MM	╳	╳	╳	╳	╳	╳	╳	╳	√	√	√	╳
DD	╳	╳	╳	╳	╳	╳	╳	╳	√	√	√	╳
MMDD	╳	╳	╳	╳	╳	╳	╳	╳	√	√	√	╳
WEEK	╳	╳	╳	╳	╳	╳	╳	╳	√	√	√	╳

√: Supported. ×: Not supported.

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