PolarDB-X 1.0 is a database service that supports both database sharding and table sharding. This topic describes the sharding functions of PolarDB-X 1.0.

Sharding method

In PolarDB-X 1.0, the sharding method of a logical table is defined by a sharding function and a sharding key (including the MySQL data type of the key). The sharding function contains the number of shards and the routing algorithm. The database shard and table shard of a logical table in PolarDB-X 1.0 are generated with the same sharding method only when the same sharding function and sharding key are used. If the database shard and table shard are generated with the same sharding method, PolarDB-X 1.0 can locate a unique physical database shard and physical table shard based on the value of the sharding key. If the sharding methods used for the database sharding and table sharding of a logical table are different, and no conditions are specified for database sharding and table sharding in the SQL statement, PolarDB-X 1.0 scans all database shards or all table shards to query data.

Support for database sharding and table sharding

Sharding function Description Support database sharding Support table sharding
HASH Performs a simple modulo operation. Yes Yes
STR_HASH Returns a substring. Yes Yes
UNI_HASH Performs a simple modulo operation. Yes Yes
RIGHT_SHIFT Performs a signed right shift on the value of the database shard key. Yes Yes
RANGE_HASH Performs hashing when two sharding keys are required. Yes Yes
MM Performs hashing by month. No Yes
DD Performs hashing by date. No Yes
WEEK Performs hashing by week. No Yes
MMDD Performs hashing by month and date. No Yes
YYYYMM Performs hashing by year and month. Yes Yes
YYYYWEEK Performs hashing by year and week. Yes Yes
YYYYDD Performs hashing by year and date. Yes Yes

Support for global secondary indexes

  • PolarDB-X 1.0 supports Global secondary indexes. In terms of data storage, each GSI corresponds to a logical table that stores index data. This table is called an index table.
  • PolarDB-X 1.0 also allows you to specify the sharding method of the index table when you create a GSI. Index tables and normal logical tables support the same sharding functions. For more information, see Use global secondary indexes.

Supported data types

Sharding function Data type
INT BIGINT MEDIUMINT SMALLINT TINYINT VARCHAR CHAR DATE DATETIME TIMESTAMP Other type
HASH √ √ √ √ √ √ √ × × × ×
UNI_HASH √ √ √ √ √ √ √ × × × ×
RANGE_HASH √ √ √ √ √ √ √ × × × ×
RIGHT_SHIFT √ √ √ √ √ × × × × × ×
STR_HASH × × × × × √ √ × × × ×
MM × × × × × × × √ √ √ ×
DD × × × × × × × √ √ √ ×
WEEK × × × × × × × √ √ √ ×
MMDD × × × × × × × √ √ √ ×
YYYYMM × × × × × × × √ √ √ ×
YYYYWEEK × × × × × × × √ √ √ ×
YYYYDD × × × × × × × √ √ √ ×

Sharding function syntax

PolarDB-X 1.0 is compatible with the Data Definition Language (DDL) table statements in MySQL. It also provides the drds_partition_options keyword for database sharding and table sharding, as shown in the following statements. 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [drds_partition_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [drds_partition_options]
    [partition_options]
    select_statement

drds_partition_options:
    DBPARTITION BY
        { {HASH|YYYYMM|YYYYWEEK|YYYYDD|...}([column])}
        [TBPARTITION BY
            { {HASH|MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|...}(column)}
          [TBPARTITIONS num]
        ]