PolarDB:RIGHT_SHIFT

Limits

• The shard key must be an integer.
• The version of the PolarDB-X 1.0 instance must be 5.1.28-1320920 or later..

Routing method

The RIGHT_SHIFT function performs a signed right shift on the value of the database shard key. The function then divides the resulting integer by the number of database or table shards and finds the remainder. Note that the value of the shard key must be an integer. You can specify the number of bits to shift by running a data definition language (DDL) statement.

Scenarios

The RIGHT_SHIFT function can produce more even hashing when the lower-digit parts of most shard key values are similar but the higher-digit parts vary greatly.

For example, assume you have the following four shard key values: 0x0100, 0x0200, 0x 0300 and 0x0400. The rightmost eight bits of each value are 0. Services may use the rightmost bits as flags. In this case, using the remainder method on the original values can result in less effective hashing. 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. These new values result in relatively even hashing. If a database is divided into four shards, each value corresponds to one shard.

Use cases

For example, assume that you are using the ID column as the shard key. You may want to shift the values of this column four bits to the right for hashing purposes. In this case, you can run the following 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;