PolarDB:RIGHT_SHIFT

Description

You can use the RIGHT_SHIFT function to perform a signed right shift on the values of the database shard key. Then, the function divides the values that are of the integer type by the number of database shards or table shards to obtain the remainders. Take note that the values of the database shard key must be integers. You can execute a DDL statement to specify the number of bits to shift.

Limits

A shard key must be of the INTEGER type.

Scenarios

The RIGHT_SHIFT function is suitable for scenarios in which the lower-part digits of most shard key values are similar but the higher-part digits significantly vary. This function can implement uniform hashing.

For example, you have four shard key values 0x0100, 0x0200, 0x0300, and 0x0400. The eighth bit of each value is 0. The last N bits of the shard key values for some services can be used only as flag bits for these services. In this case, if the shard key values are calculated to obtain remainders for data routing, the hash result may not be expected. You can use RIGHT_SHIFT (shardKey, 8) to shift the values of the keys eight bits to the right and obtain the following values: 0x01, 0x02, 0x03, and 0x04. This way, data can be evenly distributed based on the hash result. If the data is partitioned into four database shards, each of the four values corresponds to a database shard.

Examples

The ID column needs to be used as the shard key. The values of this column need to be shifted eight bits to the right so that the shift result can be used as hash values. In this case, you can execute the following DDL statement:

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 RIGHT_SHIFT(id, 8) 
tbpartition by RIGHT_SHIFT(id, 8) tbpartitions 4;