Use the UNI_HASH function for data sharding - PolarDB

When two logical tables share the same shard key but have different numbers of table shards, the standard HASH function changes its database shard routing as table shard counts change. This means that joining the two tables on the shard key triggers cross-database joins, which are expensive. UNI_HASH solves this by keeping database shard routing stable regardless of table shard count changes, so joins on the shard key stay within a single database shard.

UNI_HASH produces an even distribution only when shard key values are themselves evenly distributed.

Limitations

Shard key data type must be integer or string.
Requires PolarDB-X 1.0 instance version 5.1.28-1508068 or later.

How routing works

UNI_HASH routes data using a modulo operation on the shard key:

Integer keys: The key value is divided by the number of database shards. The remainder determines the target database shard.
String keys: The string is first converted to a hash value, then the same modulo operation is applied. For example, HASH('8') is equivalent to 8 % D, where D is the number of database shards.

When database sharding and table sharding both use the same shard key, UNI_HASH first routes to a database shard, then distributes data evenly across the table shards within that database shard.

Key property: For any given shard key value, the target database shard does not change as the number of table shards changes. This stability is what differentiates UNI_HASH from HASH.

Use cases

UNI_HASH works well for:

Sharding on user IDs or order IDs: These are common integer or string keys and typically have broad value distribution.
Joining tables with different table shard counts: When two logical tables share the same shard key but have different numbers of table shards, UNI_HASH ensures both tables always route to the same database shard for any given key value. Joins on the shard key stay within a single database shard, avoiding cross-database joins.

Create a sharded table

The following data definition language (DDL) statement creates a table sharded by UNI_HASH on the id column, with 4 table shards per database shard:

create table test_hash_tb (
    id int,
    name varchar(30) DEFAULT NULL,
    create_time datetime DEFAULT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
dbpartition by UNI_HASH(ID)
tbpartition by UNI_HASH(ID) tbpartitions 4;

UNI_HASH vs. HASH

Scenario	UNI_HASH	HASH
Database sharding only (no table sharding)	Routes by `key value % number of database shards`.	Identical behavior.
Database and table sharding with the same shard key	Database shard routing is stable — the result does not change when the number of table shards changes.	Database shard routing changes when the number of table shards changes.
Two logical tables share the same shard key but have different numbers of table shards	Joining on the shard key stays within a single database shard — no cross-database join.	Joining on the shard key may span multiple database shards — cross-database join occurs.

Choose UNI_HASH over HASH when both of the following are true:

You use the same shard key for database sharding and table sharding.
Two or more logical tables share the same shard key but have different numbers of table shards, and those tables are often joined on the shard key.