PolarDB:UNI_HASH

Considerations

The UNI_HASH functions perform simple modulo operations. The hash output can be evenly distributed only when the values in the sharding column are evenly distributed.

Limits

• The data type of shard keys must be integers or strings.
• The version of the PolarDB-X 1.0 instance must be 5.1.28-1508068 or later.

Routing method

UNI_HASH is used in the following scenarios:

• When you run the UNI_HASH function to perform database sharding, the values of the database shard key are divided by the number of database shards to obtain the remainders. If the key values are strings, the strings are converted to hash values. Then, the hash values are calculated to complete route computing. For example, HASH('8') is equivalent to 8 % D. Letter D indicates the number of database shards.
• Assume that you run the UNI_HASH function to implement database sharding and table sharding by using the same shard key. The values of the database shard key are divided by the number of database shards to obtain the remainders. Then, data is evenly routed to each table shard of the database shard.

Scenarios

• Database sharding is implemented based on user IDs or order IDs.
• The data type of shard keys is an integer or a string.
• The database sharding must be implemented for two logical tables by using the same shard key. The number of table shards for one table is different from that for the other table. The two tables are often joined by using the shard key.

Use cases

Assume that you must run the UNI_HASH function to implement sharding by using the values of the ID column and each database shard contains four tables. You can execute the following data definition language (DDL) statement to create tables:

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;

Comparison with HASH