[tutorial] shardingjdbc distributed sequence

The only problem caused by fragmentation

When the data of a table is divided into multiple tables, the unique primary key of a single table cannot be controlled through the database, because it is necessary to ensure the uniqueness of the primary key id of the same table in multiple real tables.

Note: 5 X version and 4 The X version is slightly different in configuration

Built in solution

Sharding JDBC has two built-in primary key algorithms to choose from;


uuid uses the uuid string generated by jdk to ensure that the primary key id is not repeated

The built-in algorithm class is UUIDKeyGenerateAlgorithm

The way of use is also very simple

  column: order_id
  keyGeneratorName: uuid
    type: UUID

Disadvantages: 1 The primary key must be of string type;

        2. If the primary key is discontinuous, when inserting data into the b-tree index, the performance of establishing the index is worse than that of continuous data;

Advantages: relatively simple

Not a very recommended way;

Snowflake algorithm

Snowflake algorithm is a distributed sequence algorithm of Twitter open source. The generated is a 64bit integer. And the generated is continuous according to time;

The overall structure is the serialization of timestamp + workid + machine id + milliseconds

The specific details are not repeated here. Please Baidu or google by yourself


Configure the primary key id column, configuration type and configuration attribute. You can configure a work id, that is, the middle machine id, which can effectively avoid duplication;

  column: order_id
  keyGeneratorName: snowflak

Define snowflow on the last face


Advantages: the generated sequence is ordered according to time;

Disadvantages: it depends on the clock. If the clock is dialed back, it may cause repetition;

Note that the generated id is a 64 bit integer. Remember to do some processing schemes when returning it to the front end. For example, handle json serialization and serialize long according to string

Custom algorithm

Of course, if the two built-in algorithms cannot meet the requirements, sharding JDBC supports spi extended distributed sequence algorithm;

1. First, define a sequence algorithm class. Here, only AtomicLong # is used to do a global self increment operation. Of course, it can be extended by itself in actual business;

Implement the getType method and return the type name of this algorithm

package com.example.shardingjdbcdemo.spi;

import org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import java.util.Properties;
import java.util.concurrent.atomic.AtomicLong;

public final class IncrementKeyGenerate implements KeyGenerateAlgorithm {

    private AtomicLong atomicLong;

    public Properties getProps() {
        return KeyGenerateAlgorithm.super.getProps();

    public void setProps(Properties props) {

    public String getType() {
        return "INCREMENT";

    public Comparable<?> generateKey() {
        return atomicLong.getAndIncrement();

    public void init() {
        atomicLong = new AtomicLong(1);

    public boolean isDefault() {
        return true;

2. Define spi so that customized classes can be scanned;

Create META-INF folder under restore, and then create services folder. Strictly install java spi standard to define.

The name of the file is the name of the interface to be implemented. Org apache. shardingsphere. sharding. spi. KeyGenerateAlgorithm

Write concrete implementation class internally

 3. Next, when defining the type of primary key algorithm, you can use the type defined by yourself;

The complete configuration is as follows

    name: shardingjdbcDemo
    allow-bean-definition-overriding: true

    #Data source information
      #Data source named dbsource-0
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://
        username: root
        password: 123456
      names: dbsource-0
    #Configuration of rules
          #Rules for order table
            actual-data-nodes: dbsource-0.t_order_$->{0..1}
            #Table splitting strategy
              #Standard strategy
                sharding-column: user_id
                sharding-algorithm-name: order-alg
              column: order_id
              keyGeneratorName: order-incr
          #Configuration of user table
            actual-data-nodes: dbsource-0.t_user_$->{0..1}
              #Standard strategy
                sharding-column: user_id
                sharding-algorithm-name: user-alg
              column: user_id
              keyGeneratorName: user-incr

        #Partition algorithm
            type: INLINE
              algorithm-expression: t_order_$->{user_id % 2}
            type: INLINE
              algorithm-expression: t_user_$->{user_id % 2}
          - t_order,t_user
            type: INCREMENT
            type: INCREMENT

      sql-show: true
      sql-comment-parse-enabled: true

Brief description: define algorithm name, algorithm type and some attribute information in keyGenerators;

Then, write the name of the definition in the # keyGenerateStrategy of the specific table by using # keyGeneratorName to complete the association definition of a primary key generation algorithm;

After the data is inserted, the id will be written to each table in an incremental installation order;

Note: two algorithms are defined here to use different instance classes for different tables and avoid using a self increasing id for different tables

Note: if you use mybatis plus, you should specify id type = idtype Auto can take effect;

@TableId(type = IdType.AUTO)

Some other distributed sequence schemes

  • Redis global auto increment: use the incr command of redis to maintain the global auto increment order
  • mysql maintains auto increment, and uses a separate data table to record the auto increment location and other information of each business table;
  • leaf algorithm, meituan's self increasing algorithm

Keywords: Distribution

Added by alex.saidani on Tue, 15 Feb 2022 16:10:09 +0200