Design of distributed global ID

1. Overview of distributed global id and problems caused

When creating a table, we use self increment for the primary key id to uniquely distinguish the data
In the scenario of sub database and sub table, the problem of self adding id cannot be solved
Duplicate IDs of two different business data will lead to query errors or related data problems

2. Implement global id through UUID

UUID universal unique identifier
Disadvantages: it is just a simple id and has no practical significance. It is 32 bits long and has no order. It is not friendly to the index organization table of MySQL innoDB engine
MyCat does not support UUID mode, and sharding JDBC supports UUID mode

2.1. Using UUID to divide primary key data in sharding JDBC

Create table

CREATE TABLE `order_content_1` (
  `id` varchar(32) NOT NULL,
  `order_amount` decimal(10,2) DEFAULT NULL,
  `order_status` int(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Configure properties to take the UUID column as the partition method of the database

We need to change the inline rules into standard rules and implement the standard fragment rule class ourselves

# Rules of sub database
spring.shardingsphere.sharding.tables.order_content.database-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.order_content.database-strategy.standard.precise-algorithm-class-name=com.icodingedu.config.MyShardingRule
# Rules of sub table
spring.shardingsphere.sharding.tables.order_content.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.order_content.table-strategy.standard.precise-algorithm-class-name=com.icodingedu.config.MyShardingRule

The generation rules for the specified id are provided by sharding JDBC

# The generation rule of the specified id is UUID, which is provided by sharding JDBC
# If the health value is written, the written instead of UUID will be used
spring.shardingsphere.sharding.tables.order_content.key-generator.column=id
spring.shardingsphere.sharding.tables.order_content.key-generator.type=UUID

Custom class of sub Library

package com.icodingedu.config;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;

import java.util.Collection;

public class MyShardingRule implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        String id = preciseShardingValue.getValue();
        int mode = id.hashCode()%collection.size();
        mode = Math.abs(mode);
        Object [] nodes = collection.toArray();
        return nodes[mode].toString();
    }
}

3. Realize the global id through the snowflake algorithm

SnowFlack is a distributed ID algorithm proposed by Twitter
It is a 64bit long number
The concept of timestamp is introduced to maintain self increment
SnowFlack data structure
- The first bit is 0, which is fixed, indicating a positive number. If it is 1, it is negative
- 41 bit timestamp: the current time minus the milliseconds of the start time you set. The start time can be set in the snowflake algorithm generation. The longest time range is 69 years
- 5-digit machine room id
- 5-bit machine id: the sequence of 5-bit machine room and 5-bit machine uniquely identifying the machine. It can be configured to the 10th power of 2, that is, 1024 machines can not be repeated in the case of concurrency
- 12 bit serial number: at the same time, the same machine can generate 2 12 power sequences concurrently, that is, 4096 different sequences
- Note: snowflake supports 4096 milliseconds of concurrency
Time callback will cause repetition

When you go online at the beginning, it is inconsistent with the actual time, which is earlier than the actual time. If you modify the time after discovery, it may lead to time overlap and repetition, and the probability of repetition is relatively low
Both MyCat and sharding JDBC support the snowflake algorithm
Sharding JDBC can set the maximum allowable callback time. If the callback time is exceeded and id is generated through snowflag, an exception will be thrown

3.1. How does MYCAT use snowflakes to generate IDS

# 0. First change the id from int to bigint
# 1. Modify server Modify the configuration of XML to 2, and change the automatically generated id to snowflake algorithm
<property name="sequnceHandlerType">2</property>
# 2. Set the machine room id and machine id
conf Lower sequence_time_conf.properties
WORKID=03 #Machine id
DATAACENTERID=03 #Machine room id
# 3. id rules generated by configuration table
<table name="order_info" dataNode="dn213,dn214" rule="mod-long" autoIncrement="true" primaryKey="id">
		<childTable name="order_item" joinKey="order_id" parentKey="id"/>
</table>
# autoIncrement
# primaryKey

3.2. Sharding JDBC implementation

# Name two data sources
spring.shardingsphere.datasource.names=ds0,ds1
# The data source link ds0 should be consistent with the name
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://39.103.163.215:3306/shard_order
spring.shardingsphere.datasource.ds0.username=gavin
spring.shardingsphere.datasource.ds0.password=123456
# The data source link ds1 should be consistent with the name
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbcUrl=jdbc:mysql://39.101.221.95:3306/shard_order
spring.shardingsphere.datasource.ds1.username=gavin
spring.shardingsphere.datasource.ds1.password=123456

# Specific fragmentation rules are based on data nodes
spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$->{0..1}.order_info_$->{1..2}
# Rules of sub database
spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds$->{id % 2}
# Rules of sub table
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$->{user_id % 2 + 1}
# Set snowflake id
spring.shardingsphere.sharding.tables.order_info.key-generator.column=id
spring.shardingsphere.sharding.tables.order_info.key-generator.type=snowflake
# Here, you also need to set the machine room id and machine id, which are combined and are 10 bits, so it should not exceed 1024
spring.shardingsphere.sharding.tables.order_info.key-generator.props.worker.id=1000
# Maximum time tolerance interval
spring.shardingsphere.sharding.tables.order_info.key-generator.props.max.tolerate.time.difference.milliseconds=60000

Keywords: Java Database MySQL Distribution

Added by bibby on Sat, 22 Jan 2022 19:05:01 +0200

Programming VIP