Sharding functions for database and table sharding in PolarDB-X 1.0 - PolarDB

PolarDB-X 1.0 supports both database sharding and table sharding. Each sharding configuration is defined by two components: a sharding function (which specifies the number of shards and the routing algorithm) and a shard key (a table column, along with its MySQL data type).

How sharding works

When the same sharding function and shard key are used for both database sharding and table sharding, PolarDB-X 1.0 treats them as the same sharding method. With a consistent sharding method, PolarDB-X 1.0 can route a query directly to a unique physical database shard and physical table shard based on the shard key value.

If different sharding methods are used for database sharding and table sharding on the same logical table, and the SQL statement includes no conditions for either dimension, PolarDB-X 1.0 scans all database shards or all table shards to retrieve data.

Sharding functions

PolarDB-X 1.0 provides 12 sharding functions in two categories:

Hash-based (for numeric or string shard keys): use HASH, UNI_HASH, or RANGE_HASH to distribute data evenly across shards; use STR_HASH for string-only keys; use RIGHT_SHIFT for integer keys where the low-order bits carry most of the variance.
Time-based (for date/datetime/timestamp shard keys): use MM, DD, WEEK, or MMDD for table-only sharding by calendar unit; use YYYYMM, YYYYWEEK, or YYYYDD when you also need database-level sharding by time.

The following table shows each function, its database and table sharding support, and the compatible shard key data types.

Sharding function	Description	Database sharding	Table sharding	INT / BIGINT / MEDIUMINT / SMALLINT / TINYINT	VARCHAR / CHAR	DATE / DATETIME / TIMESTAMP
HASH	Modulo operation	Yes	Yes	√	√	×
UNI_HASH	Modulo operation	Yes	Yes	√	√	×
STR_HASH	Returns a substring	Yes	Yes	×	√	×
RIGHT_SHIFT	Signed right shift on the database shard key value	Yes	Yes	√	×	×
RANGE_HASH	Hash when two sharding keys are required	Yes	Yes	√	√	×
MM	Hash by month	No	Yes	×	×	√
DD	Hash by date	No	Yes	×	×	√
WEEK	Hash by week	No	Yes	×	×	√
MMDD	Hash by month and date	No	Yes	×	×	√
YYYYMM	Hash by year and month	Yes	Yes	×	×	√
YYYYWEEK	Hash by year and week	Yes	Yes	×	×	√
YYYYDD	Hash by year and date	Yes	Yes	×	×	√

Global secondary indexes

PolarDB-X 1.0 supports global secondary indexes (GSIs). Each GSI corresponds to a logical table that stores the index data, called an index table.

When creating a GSI, specify the sharding method for the index table. Index tables support the same sharding functions as normal logical tables. For details, see Use global secondary indexes.

Sharding function syntax

PolarDB-X 1.0 is compatible with MySQL data definition language (DDL) CREATE TABLE statements and extends them with the drds_partition_options clause for sharding configuration.

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

Use DBPARTITION BY to configure database sharding, and TBPARTITION BY together with TBPARTITIONS num to configure table sharding.