分布式全局id设计

2020-11-07  本文已影响0人  dylan丶QAQ

1. 分布式全局id概述及引发的问题分析

2. 通过UUID实现全局id

sharing-jdbc需要使用的分片表，主键要设置为32位varchar

CREATE TABLE `order_info_1` (
 `id` varchar(32) NOT NULL,
 `order_amount` decimal(10,2) NOT NULL,
 `order_status` int(1) NOT NULL,
 `user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用UUID自动设置column的值

spring.shardingsphere.datasource.names=ds0,ds1
​
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.99.182.22/shard_order
spring.shardingsphere.datasource.ds0.username=gavin
spring.shardingsphere.datasource.ds0.password=123456
​
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.100.17.31:3306/shard_order
spring.shardingsphere.datasource.ds1.username=gavin
spring.shardingsphere.datasource.ds1.password=123456
​
spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$->{0..1}.order_info_$->{1..2}
spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
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}
​
# 指定自动设置的column
spring.shardingsphere.sharding.tables.order_info.key-generator.column=id
spring.shardingsphere.sharding.tables.order_info.key-generator.type=UUID

使用UUID来进行分片，就需要自己扩展分片规则

需要将inline的规则改成standard的规则，自己是实现分片规则

spring.shardingsphere.sharding.tables.order_info.table-strategy.standard.sharding-column=id
# 具体的实现类的全包名路径
spring.shardingsphere.sharding.tables.order_info.table-strategy.standard.precise-algorithm-class-name=com.icodingedu.config.MyShardingRule

实现precise-algorithm-class-name的具体分片规则

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
​
import java.util.Collection;
​
public class MyShardingRule implements PreciseShardingAlgorithm<String> {
 @Override
 public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
 String id = preciseShardingValue.getValue();
 System.out.println("********id.hashCode():"+id.hashCode());
 int mode = id.hashCode()%collection.size();//hashcode可能出现负数
 mode = Math.abs(mode);
 Object [] nodes = collection.toArray();
 return nodes[mode].toString();
 }
}

3. MyCat的全局统一id序列设计

MyCat可以看作是一个数据库，MyCat作为一个分布式数据库，也会因为你对分片表设置了自增id导致数据id重复

MyCat给出的解决方案：不使用物理数据库自带的自增id算法，他会从一个地方统一获取id进行中心化管理

4. MyCat本地文件方式实现统一id序列

MyCat管理的表结构

CREATE TABLE `user_info` (
 `id` int(11) NOT NULL,
 `username` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

配置项

# 1.将server.xml里的配置进行修改0:本地文件方式
<property name="sequnceHandlerType">0</property>
# 2.修改conf下的配置文件:sequence_conf.properties
# 修改完要重启一下mycat
USER_INFO.HISIDS=
USER_INFO.MINID=1001
USER_INFO.MAXID=999999999
USER_INFO.CURID=1000
# 3.手动指定值
insert into user_info(id,username) values(
next value for mycatseq_USER_INFO,
'gavin-text'
)
2013 - Lost connection to MySQL server during query, Time: 2.788000s
# 4.这个问题需要将schema.xml里的checkSQLSchema改成false
<schema name="user_db" checkSQLschema="false" sqlMaxLimit="100">
# 5.重启mycat不会从头开始自增是因为CURID会记录当前id
​
# 6.自增id我们希望不写这一列就能插入
# 通过设置schema的table配置autoIncrement,primaryKey
<table name="user_info" autoIncrement="true" primaryKey="id" dataNode="DN186,DN195" rule="auto-sharding-long" />
reload @@config;

5. MyCat通过数据库方式实现统一id序列

# 1.将server.xml里的配置进行修改1:数据库方式
<property name="sequnceHandlerType">1</property>
# 2.要在数据库中创建一个存放id的表，只能放在一个物理数据库节点
conf/dbseq.sql
mysql> use user_186;
mysql> source dbseq.sql;
image-20200318213456476.png 
# 3.配置存放自增id的数据库的节点告知给MyCat
# sequence_db_conf.properties
USER_INFO=DN186
​
# 如果要实现自增id且自动设置，也需要将table中的autoIncrement和primaryKey设置好
<table name="user_info" autoIncrement="true" primaryKey="id" dataNode="DN186,DN195" rule="auto-sharding-long" />

6. 雪花算法实现的方式分析

7. MyCat使用雪花算法生成全局id

# 1.将server.xml里的配置进行修改2:雪花算法方式
<property name="sequnceHandlerType">2</property>
# id由int(11)修改为bigint(19)
​
# 2.分片规则也要改下
将auto-sharding-long改成mod-long
​
# 3.可以在conf下sequence_time_conf.properties
WORKID=01
DATAACENTERID=01
这里的值不能超过0-31
​
# 如果要实现自增id且自动设置，也需要将table中的autoIncrement和primaryKey设置好
<table name="user_info" autoIncrement="true" primaryKey="id" dataNode="DN186,DN195" rule="mod-long" />

8. Sharding-Jdbc使用雪花算法生成全局id

spring.shardingsphere.datasource.names=ds0,ds1
​
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.99.182.22/shard_order
spring.shardingsphere.datasource.ds0.username=gavin
spring.shardingsphere.datasource.ds0.password=123456
​
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.100.17.31:3306/shard_order
spring.shardingsphere.datasource.ds1.username=gavin
spring.shardingsphere.datasource.ds1.password=123456
​
spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$->{0..1}.order_info_$->{1..2}
spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$->{id % 2 + 1}
​
# 指定自动设置的column
spring.shardingsphere.sharding.tables.order_info.key-generator.column=id
spring.shardingsphere.sharding.tables.order_info.key-generator.type=snowflake
# 设置机房和机器id,这里是合并的一共10位
spring.shardingsphere.sharding.tables.order_info.key-generator.props.worker.id=666
# 最大的容忍回调时间间隔
spring.shardingsphere.sharding.tables.order_info.key-generator.props.max.tolerate.time.difference.milliseconds=100
上一篇下一篇

猜你喜欢

热点阅读