This topic describes how to use 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 DRDS 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

Comparison scenario UNI_HASH HASH
Database sharding is implemented and table sharding is not implemented. The two functions use the same routing method. In this method, the values of the database shard key are divided by the number of database shards to obtain the remainders.
Sharding is implemented by using the same shard key. The results that are routed to database shards by using the same key value do not change as the number of table shards changes. The results that are routed to database shards by using the same key value change as the number of table shards changes.
Sharding must be implemented for two logical tables by using the same shard key. However, the number of table shards for one logical table is different from that for the other logical table. When one table is joined with the other table by using the shard key, a cross-database join does not occur. When one table is joined with the other table by using the shard key, a cross-database join occurs.