Help Center/ Distributed Database Middleware/ User Guide (Kuala Lumpur Region)/ SQL Syntax/ DDL/ Sharding Algorithms/ UNI_HASH
Updated on 2022-08-17 GMT+08:00
View PDF
Share

UNI_HASH

Application Scenarios

This algorithm applies if you want to route data to different shards by user ID or order ID.

Instructions

The sharding key must be the numeric data type (INT, INTEGER, BIGINT, MEDIUMINT, SMALLINT, TINYINT, DECIMAL, or NUMERIC) or the string data type.

Data Routing

The data route depends on the remainder of the sharding key values divided by the number of database shards or table shards. If the value is a string, convert the string into a hashed value and use the value to calculate remainders.

For example, UNI_HASH('6') is equivalent to 6%D. D is the number of shards. This example only applies to the sharding key value that is an integer.

Calculation Method

Method 1: Use an Integer as the Sharding Key

Table 1 Required calculation methods

Condition

Calculation Method

Example

Database sharding key ≠ Table sharding key

Database routing result = Database sharding key value % Database shards

Table routing result = Table sharding key value % Table shards

Database shard: 16 % 8 = 0

Table shard: 16 % 3 = 1

Database sharding key = Table sharding key

Database routing result = Sharding key value % Database shards

Table routing result = (Sharding key value % Database shards) x Table shards + (Sharding key value / Database shards) % Table shards

Database shard: 16 % 8 = 0

Table shard: (16 % 8) x 3 + (16 / 8)%3 = 2

Method 2: Use a String as the Sharding Key

Table 2 Required calculation methods

Condition

Calculation Method

Example

Database sharding key ≠ Table sharding key

Database routing result = hash(Database sharding key value) % Database shards

Table routing result = hash(Table sharding key value % Table shards

hash('abc') = 'abc'.hashCode()=96354

Database shard: 96354 % 8 = 2;

Table shard: 96354 % 3 = 0;

Database sharding key = Table sharding key

Database routing result = hash(Sharding key value) % Database shards

Table routing result ={hash(Sharding key value) % Database shards} x Table shards + {hash(Sharding key value) / Database shards} % Table shards

Database shard: 96354% 8 = 2

Table shard: :(96354 % 8) x 3 + (96354 / 8)%3 = 8

Syntax for Creating Tables

create table uni_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(name) tbpartitions 3;

Precautions

  • The sharding key and its value cannot be modified.
  • The UNI_HASH algorithm is a simple way to obtain the remainder of the sharding key divided by the number of shards. This algorithm features even distribution of sharding key values to ensure even results.

Comparison with MOD_HASH

  • When you use UNI_HASH to shard databases, UNI_HASH applies the same data routing method as MOD_HASH, that is, shard databases depending on the remainder of the sharding key value divided by database shards.
  • If you use MOD_HASH and the same sharding key to shard databases and tables, the database sharding result changes as you specify different table shards per database shard.
  • If you use UNI_HASH and the same sharding key to shard databases and tables, the database sharding result is fixed whatever table shards per database shard you specify.
  • If two logical tables with different table shards use the same sharding key to shard databases and tables, a cross-shard JOIN operation is required to join the two logical tables when MOD_HASH is used, but not when UNI_HASH is used.
Parent topic: Sharding Algorithms