分布式ID - Snowflake算法

目录

中间件

• MyCat
  • 延伸之前文章继续，建议使用 CentOS7 部署MyCAT与测试 作为基础配置文件
• ShardingJdbc
  • 数据库环境模型，建议使用 Linux搭建MySQL主从
  • 基础模型，建议使用 ShardingJdbc 数据分片 + 读写分离

一、MyCat

1、server.xml

[root@localhost conf]# vim /opt/mycat/conf/server.xml 
<property name="sequnceHandlerType">2</property>  <!-- 使用雪花算法 -->

2、rule.xml

[root@localhost conf]# vim /opt/mycat/conf/rule.xml
<tableRule name="mod-long">  <!-- 使用取余方式 -->
	<rule>
		<!--<columns>user_id</columns>  屏蔽之前的user_id-->
		<columns>id</columns>
		<algorithm>mod-long</algorithm>
	</rule>
</tableRule>

3、schema.xml

[root@localhost conf]# vim /opt/mycat/conf/schema.xml     
<schema name="user" checkSQLschema="false" sqlMaxLimit="100" >
		<!-- auto sharding by id (long) -->
		<table name="user" dataNode="dn246,dn247" rule="mod-long" />
		<table name="province" dataNode="dn246,dn247" type="global"/>
		 <!-- 
			rule="auto-sharding-long" 修改 mod-long 
			auto-sharding-long 里面的 autopartition-long.txt 范围不能容纳19位id
		 -->
		<table name="o_order" autoIncrement="true" primaryKey="id"  dataNode="dn246,dn247" rule="mod-long" > 
		   <childTable name="order_item" joinKey="order_id" parentKey="id"/>
		</table>

		<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
				/> -->
</schema>

4、重载配置

mysql> reload @@config;
Query OK, 1 row affected (0.02 sec)
Reload config success

5、Mycat o_order 插入一条记录 id类型修改为bigint

INSERT INTO o_order (total_amount,order_status) VALUES (555,3);

/opt/mycat/conf/sequence_time_conf.properties
在多台MyCat情况下可以修改此处，但是里面的ID必需小于32*

二、ShardingJdbc

2.1 数据库需要修改部分

1、清空之前数据

DELETE FROM `sharding_order`.`t_order_1`
DELETE FROM `sharding_order`.`t_order_2`

DELETE FROM `shard_order`.`t_order_1`
DELETE FROM `shard_order`.`t_order_2`

2、修改字段类型
AUTO_INCREMENT 自增可有可无，最后 ShardingJdbc 会配置的

ALTER TABLE `sharding_order`.`t_order_1` 
MODIFY COLUMN `order_id` bigint(19) NOT NULL AUTO_INCREMENT FIRST

ALTER TABLE `sharding_order`.`t_order_2` 
MODIFY COLUMN `order_id` bigint(19) NOT NULL AUTO_INCREMENT FIRST

ALTER TABLE `shard_order`.`t_order_1`  
MODIFY COLUMN `order_id` bigint(19) NOT NULL AUTO_INCREMENT FIRST

ALTER TABLE `shard_order`.`t_order_2`  
MODIFY COLUMN `order_id` bigint(19) NOT NULL AUTO_INCREMENT FIRST

2.2 Spring命名空间配置

官方网址 - <sharding:key-generator />

1、 根据上述修改数据库，已知对应实体修改Long型，*mapping.xml修改BIGINT

2、 MySharding 需要String类型改Long

public class MySharding implements PreciseShardingAlgorithm<Long> {

    /**
     * @param collection    获取到的表名
     * @param shardingValue 获取到的主键和生成好的ID值
     * @return
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> shardingValue) {
        Long id = shardingValue.getValue();

        // 取余
        long mode = id.hashCode() % collection.size();
        String[] strings = collection.toArray(new String[0]);
        // mode 是复数，需要通过math取绝对值
        mode = Math.abs(mode);
        // mode=1  = t_order_2的表
        System.out.println("mode=" + mode);
        System.out.println(strings[0] + "========" + strings[1]);

        return strings[(int) mode];
    }

}

3、 sharding-jdbc.xml
uuid 修改 SNOWFLAKE算法

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
       xmlns:master-slave="http://shardingsphere.apache.org/schema/shardingsphere/masterslave"
       xmlns:bean="http://www.springframework.org/schema/util"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave
                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd http://www.springframework.org/schema/util https://www.springframework.org/schema/util/spring-util.xsd">
    <!-- MySQL数据源 -->
    <bean id="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="eddie" />
        <property name="password" value="Abc@123456" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.8.246/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    <!-- MySQL已经搭建好主从关系, 并非ShardingJdbc做的 -->
    <bean id="slave0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="eddie" />
        <property name="password" value="Abc@123456" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.8.245/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    <bean id="ms1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="eddie" />
        <property name="password" value="Abc@123456" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.8.247/shard_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>

    <!-- 主从负载均衡, 策略：随机 -->
    <master-slave:load-balance-algorithm id="msStrategy" type="random" />

    <!-- sharding-jdbc数据源 -->
    <sharding:data-source id="sharding-data-source">
        <!-- 多个数据源用逗号分割 -->
        <sharding:sharding-rule data-source-names="ds0,slave0,ms1">
            <!-- 主从关系 -->
            <sharding:master-slave-rules>
                <sharding:master-slave-rule id="ms0" master-data-source-name="ds0" slave-data-source-names="slave0" strategy-ref=""/>
            </sharding:master-slave-rules>

            <sharding:table-rules>
                <!-- 逻辑表名称 logic-table="t_order" 对应Mapper.xml里面的表名 -->
                <!-- 数据节点 actual-data-nodes="ds$->{0..1}.t_order_$->{1..2}"  -->
                <!-- 数据库分片策略 database-strategy-ref="databaseStrategy" -->
                <!-- 数据表分片策略 table-strategy-ref="tableStrategy"  -->
                <sharding:table-rule logic-table="t_order"
                                     actual-data-nodes="ms$->{0..1}.t_order_$->{1..2}"
                                     database-strategy-ref="databaseStrategy"
                                     table-strategy-ref="standard"
                                     key-generator-ref="snowflake"
                />
            </sharding:table-rules>
            <!-- 广播表规则列表 -->
            <sharding:broadcast-table-rules>
                <sharding:broadcast-table-rule table="area"/>
            </sharding:broadcast-table-rules>
            <!-- 绑定表 -->
            <!--<sharding:binding-table-rules>-->
            <!--<sharding:binding-table-rule logic-tables="t_order,t_order_item" />-->
            <!--</sharding:binding-table-rules>-->
        </sharding:sharding-rule>
    </sharding:data-source>
    
    <sharding:key-generator id="snowflake" column="order_id" type="SNOWFLAKE"  props-ref="snow"/>
    
    <bean:properties id="snow">
        <prop key="worker.id">678</prop>
        <prop key="max.tolerate.time.difference.milliseconds">10</prop>  <!-- 最大容忍时间：单位-秒 -->
    </bean:properties>

    <!-- 数据库分片规则 -->
    <sharding:inline-strategy id="databaseStrategy"
                              sharding-column="user_id"
                              algorithm-expression="ms$->{user_id % 2}"
    />

    <!-- 实例化 -->
    <bean id="mySharding" class="com.example.shardingjdbcdemo.sharding.MySharding" />
    
    <sharding:standard-strategy id="standard" sharding-column="order_id" precise-algorithm-ref="mySharding" />

    <!-- 数据表分片规则 -->
    <!-- id取模，可能会出现寻找到 t_order_0 的表，但实际上是没有的，所以 +1  -->
    <sharding:inline-strategy id="tableStrategy"
                              sharding-column="order_id"
                              algorithm-expression="t_order_$->{order_id % 2 + 1}"
    />

    <!-- 设置 mybatis 数据源 -->
    <bean class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="sharding-data-source"/>
        <property name="mapperLocations" value="classpath*:/mybatis/*.xml" />
    </bean>

</beans>

4、单元测试

    @Test
    public void testOrder() {
        Order order = new Order();

        order.setUserId(15);
        order.setOrderAmount(BigDecimal.TEN);
        order.setOrderStatus(1);

        //  配置 t_order 分库策略
        System.out.println("ds" + order.getUserId() % 2);

        orderMapper.insertSelective(order);
    }

2.3 Spring Boot配置

官方网址 - <sharding:key-generator />

1、 注入依赖

<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>4.0.0-RC2</version>
</dependency>

2、 写入配置

####################################################
#
#  博客：blog.eddilee.cn
#  备注：为了方便查看，使用 properties 而不是 yml 格式
#
####################################################

# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,slave0,ms1

# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://192.168.8.246/sharding_order
spring.shardingsphere.datasource.ds0.username=eddie
spring.shardingsphere.datasource.ds0.password=Abc@123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbcUrl=jdbc:mysql://192.168.8.245/sharding_order
spring.shardingsphere.datasource.slave0.username=eddie
spring.shardingsphere.datasource.slave0.password=Abc@123456

# 配置第 3 个数据源
spring.shardingsphere.datasource.ms1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ms1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ms1.jdbcUrl=jdbc:mysql://192.168.8.247/shard_order
spring.shardingsphere.datasource.ms1.username=eddie
spring.shardingsphere.datasource.ms1.password=Abc@123456

# 配置 主从关系
spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=ds0
spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-names=slave0

# 配置 负载均衡, 策略：随机
spring.shardingsphere.sharding.master-slave-rules.ms0.load-balance-algorithm-type=RANDOM

# 配置 t_order 表规则
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ms$->{0..1}.t_order_$->{1..2}

# 配置 t_order 分库策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ms$->{user_id % 2}

# 配置 t_order 分表策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id

# 主键生成 UUID 策略
# precise-algorithm-class-name 精准分片表达式 ( = 和 in)
#spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbcdemo.sharding.MySharding
#spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
#spring.shardingsphere.sharding.tables.t_order.key-generator.type=UUID

# 主键生成 SNOWFLAKE 策略
# precise-algorithm-class-name 精准分片表达式 ( = 和 in)
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbcdemo.sharding.MySharding
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123
spring.shardingsphere.sharding.tables.t_order.key-generator.props.max.tolerate.time.difference.milliseconds=10

# 配置 广播表规则列表
spring.shardingsphere.sharding.broadcast-tables=area

# 配置 mybatis
mybatis.mapper-locations=/mybatis/*.xml
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

# 设置日志日期格式
logging.pattern.dateformat=yyyy-MM-dd HH:mm:ss.SSS

额外也提供一下YAML格式

logging:
  pattern:
    dateformat: yyyy-MM-dd HH:mm:ss.SSS
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: /mybatis/*.xml
spring:
  shardingsphere:
    datasource:
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.8.246/sharding_order
        password: Abc@123456
        type: com.zaxxer.hikari.HikariDataSource
        username: eddie
      ms1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.8.247/shard_order
        password: Abc@123456
        type: com.zaxxer.hikari.HikariDataSource
        username: eddie
      names: ds0,slave0,ms1
      slave0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.8.245/sharding_order
        password: Abc@123456
        type: com.zaxxer.hikari.HikariDataSource
        username: eddie
    sharding:
      broadcast-tables: area
      master-slave-rules:
        ms0:
          load-balance-algorithm-type: RANDOM
          master-data-source-name: ds0
          slave-data-source-names: slave0
      tables:
        t_order:
          actual-data-nodes: ms$->{0..1}.t_order_$->{1..2}
          database-strategy:
            inline:
              algorithm-expression: ms$->{user_id % 2}
              sharding-column: user_id
          key-generator:
            column: order_id
            props:
              max:
                tolerate:
                  time:
                    difference:
                      milliseconds: 10
              worker:
                id: 123
            type: SNOWFLAKE
          table-strategy:
            standard:
              precise-algorithm-class-name: com.example.shardingjdbcdemo.sharding.MySharding
              sharding-column: order_id

3、单元测试

    @Test
    public void testOrder() {
        Order order = new Order();

        order.setUserId(15);
        order.setOrderAmount(BigDecimal.TEN);
        order.setOrderStatus(1);

        //  配置 t_order 分库策略
        System.out.println("ds" + order.getUserId() % 2);

        orderMapper.insertSelective(order);
    }

三、Redis定制分布式ID

涉及曾经项目不方便贴代码，如有需要可以下方评论留言！

四、源码

github.com/eddie-code/sharding-jdbc-demo