建设银行网站功能,微信公众号微网站 建设报价表,wordpress 屏蔽国内ip,离婚协议书模板免费下载1、准备服务器
随着业务的扩大#xff0c;订单表数据量不断增加#xff0c;数据库面临存储压力#xff0c;开始考虑对订单表进行水平分片。
将t_order表扩展为server-order0中的t_order0和t_order1、server-order1中的t_order0和t_order1
服务器规划#xff1a;使用dock…1、准备服务器
随着业务的扩大订单表数据量不断增加数据库面临存储压力开始考虑对订单表进行水平分片。
将t_order表扩展为server-order0中的t_order0和t_order1、server-order1中的t_order0和t_order1
服务器规划使用docker方式创建如下容器 服务器容器名server-order0端口3310服务器容器名server-order1端口3311
1.1、创建server-order0容器
step1创建容器
docker run -d \
-p 3310:3306 \
-v /dongguo/server/order0/conf:/etc/mysql/conf.d \
-v /dongguo/server/order0/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD123456 \
--name server-order0 \
mysql:8.0.29step2登录MySQL服务器
#进入容器
docker exec -it server-order0 env LANGC.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码插件
ALTER USER root% IDENTIFIED WITH mysql_native_password BY 123456;step3创建数据库
注意水平分片的id需要在业务层实现不能依赖数据库的主键自增
CREATE DATABASE db_order;
USE db_order;
CREATE TABLE t_order0 (id BIGINT,order_no VARCHAR(30),user_id BIGINT,amount DECIMAL(10,2),PRIMARY KEY(id)
);
CREATE TABLE t_order1 (id BIGINT,order_no VARCHAR(30),user_id BIGINT,amount DECIMAL(10,2),PRIMARY KEY(id)
);1.2、创建server-order1容器
step1创建容器
docker run -d \
-p 3311:3306 \
-v /dongguo/server/order1/conf:/etc/mysql/conf.d \
-v /dongguo/server/order1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD123456 \
--name server-order1 \
mysql:8.0.29step2登录MySQL服务器
#进入容器
docker exec -it server-order1 env LANGC.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码插件
ALTER USER root% IDENTIFIED WITH mysql_native_password BY 123456;step3创建数据库
和server-order0相同
注意水平分片的id需要在业务层实现不能依赖数据库的主键自增
CREATE DATABASE db_order;
USE db_order;
CREATE TABLE t_order0 (id BIGINT,order_no VARCHAR(30),user_id BIGINT,amount DECIMAL(10,2),PRIMARY KEY(id)
);
CREATE TABLE t_order1 (id BIGINT,order_no VARCHAR(30),user_id BIGINT,amount DECIMAL(10,2),PRIMARY KEY(id)
);查看容器 查看navicat
3310 3311 后续还需要server-user服务启动该服务 2、基本水平分片
2.1、基本配置
application.yml
# 应用名称
spring:application:name: sharding-jdbc-demoprofiles:active: devdatasource:driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver#读写分离
# url: jdbc:shardingsphere:classpath:shardingsphere-readwrite.yaml#垂直分片
# url: jdbc:shardingsphere:classpath:shardingsphere-sharding.yaml#水平分片-分库url: jdbc:shardingsphere:classpath:shardingsphere-sharding2.yaml
# Swagger配置
swagger:# 是否开启swaggerenabled: true# 请求前缀pathMapping: /2.2、数据源配置
shardingsphere-sharding2.yaml
# 数据源配置
dataSources:server-user:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3301/db_userusername: rootpassword: 123456server-order0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3310/db_orderusername: rootpassword: 123456server-order1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3311/db_orderusername: rootpassword: 123456
#规则配置
rules:
- !SHARDINGtables:# 逻辑表名t_user:# 值由数据源名 表名组成以小数点分隔。actualDataNodes: server-user.t_usert_order:actualDataNodes: server-order0.t_order_0,server-order0.t_order_1,server-order1.t_order_0,server-order1.t_order_1#属性配置
props:sql-show: true修改Order实体类的主键策略
水平分片的id需要在业务层实现不能依赖数据库的主键自增将id生成策略设置成雪花算法生成id
package com.dongguo.shardingjdbc.entity;import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;import java.math.BigDecimal;TableName(t_order)
Data
public class Order {//TableId(type IdType.AUTO)//依赖数据库的主键自增策略TableId(type IdType.ASSIGN_ID)//分布式idprivate Long id;private String orderNo;private Long userId;private BigDecimal amount;
}在配置分片算法之前确认每个分片节点都是可用。
测试保留上面配置中的一个分片表节点分别进行测试检查每个分片节点是否可用 /*** 水平分片插入数据测试*/Testpublic void testInsertOrder(){Order order new Order();order.setOrderNo(SP202308140001);order.setUserId(1L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}shardingsphere-sharding2.yaml
server-order0.t_order0 t_order:actualDataNodes: server-order0.t_order02023-08-14 20:49:25.528 INFO 1296 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-14 20:49:25.528 INFO 1296 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1691069489988296705, SP202308140001, 1, 100]server-order0.t_order1 t_order:actualDataNodes: server-order0.t_order12023-08-14 20:50:35.950 INFO 5476 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-14 20:50:35.951 INFO 5476 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1691069785481207809, SP202308140001, 1, 100]server-order1.t_order0 t_order:actualDataNodes: server-order1.t_order02023-08-14 20:51:12.503 INFO 11204 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-14 20:51:12.503 INFO 11204 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1691069939445686274, SP202308140001, 1, 100]server-order1.t_order1 t_order:actualDataNodes: server-order1.t_order12023-08-14 20:51:54.268 INFO 22500 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-14 20:51:54.268 INFO 22500 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1691070114000031746, SP202308140001, 1, 100]每个分片节点都可用每次测试完之后记得将数据删除。
2.3、行表达式
优化上一步的分片表配置
https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/
行表达式是为了解决配置的简化与一体化这两个主要问题。在繁琐的数据分片规则配置中随着数据节点的增多大量的重复配置使得配置本身不易被维护。 通过行表达式可以有效地简化数据节点配置工作量。
对于常见的分片算法使用 Java 代码实现并不有助于配置的统一管理。 通过行表达式书写分片算法可以有效地将规则配置一同存放更加易于浏览与存储。
行表达式的使用非常直观只需要在配置中使用 ${ expression } 或 $-{ expression } 标识行表达式即可。 目前支持数据节点和分片算法这两个部分的配置。 行表达式的内容使用的是 Groovy 的语法Groovy 能够支持的所有操作行表达式均能够支持。 例如
${begin..end} 表示范围区间 ${[unit1, unit2, unit_x]} 表示枚举值
行表达式中如果出现连续多个 ${ expression } 或 $-{ expression } 表达式整个表达式最终的结果将会根据每个子表达式的结果进行笛卡尔组合。
shardingsphere-sharding2.yaml
# 数据源配置
dataSources:server-user:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3301/db_userusername: rootpassword: 123456server-order0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3310/db_orderusername: rootpassword: 123456server-order1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3311/db_orderusername: rootpassword: 123456
#规则配置
rules:
- !SHARDINGtables:# 逻辑表名t_user:# 值由数据源名 表名组成以小数点分隔。actualDataNodes: server-user.t_usert_order:actualDataNodes: server-order${0..1}.t_order${0..1}#属性配置
props:sql-show: true2.4、分片算法配置
ShardingSphere 内置提供了多种分片算法按照类型可以划分为自动分片算法、标准分片算法、复合分片算法和 Hint 分片算法能够满足用户绝大多数业务场景的需要。此外考虑到业务场景的复杂性内置算法也提供了自定义分片算法的方式用户可以通过编写 Java 代码来完成复杂的分片逻辑。 需要注意的是自动分片算法的分片逻辑由 ShardingSphere 自动管理需要通过配置 autoTables 分片规则进行使用。
水平分库
分片规则order表中user_id为偶数时数据插入server-order0服务器user_id为奇数时数据插入server-order1服务器。这样分片的好处是同一个用户的订单数据一定会被插入到同一台服务器上查询一个用户的订单时效率较高。
分片算法相关参考
https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/sharding/
shardingsphere-sharding2.yaml
标准分片算法-行表达式分片算法
# 数据源配置
dataSources:server-user:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3301/db_userusername: rootpassword: 123456server-order0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3310/db_orderusername: rootpassword: 123456server-order1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3311/db_orderusername: rootpassword: 123456
#规则配置
rules:
- !SHARDINGtables:# 逻辑表名t_user:# 值由数据源名 表名组成以小数点分隔。actualDataNodes: server-user.t_usert_order:actualDataNodes: server-order${0..1}.t_order${0..1}#分库策略databaseStrategy:standard:# 分片列名称shardingColumn: user_id# 分片算法名称shardingAlgorithmName: alg_inline_userid# 分片算法配置shardingAlgorithms:# 标准分片算法-行表达式分片算法alg_inline_userid:#基于行表达式的分片算法type: INLINEprops:algorithm-expression: server-order${user_id % 2}
#属性配置
props:sql-show: true配置了分片算法alg_inline_userid
注:在之前有些版本中shardingAlgorithmName对应的值好像不支持下划线在ShardingSphere5.4.0版本是支持下划线命名的。
如果版本不支持下划线报错如下:
Caused by: org.apache.shardingsphere.spi.exception.ServiceProviderNotFoundException: No implementation class load from SPI org.apache.shardingsphere.sharding.spi.ShardingAlgorithm with type null.为了方便测试先设置只在 t_order0表上进行测试测试分库策略-行表达式分片算法当前未配置分表策略
shardingsphere-sharding2.yaml
#规则配置
rules:
- !SHARDINGtables:# 逻辑表名t_user:# 值由数据源名 表名组成以小数点分隔。actualDataNodes: server-user.t_usert_order:actualDataNodes: server-order${0..1}.t_order0#分库策略databaseStrategy:standard:# 分片列名称shardingColumn: user_id# 分片算法名称shardingAlgorithmName: alg_inline_userid# 分片算法配置shardingAlgorithms:# 标准分片算法-行表达式分片算法alg_inline_userid:type: INLINEprops:algorithm-expression: server-order${user_id % 2}/*** 水平分片分库插入数据测试*/Testpublic void testInsertOrderDatabaseStrategy(){for (long i 1; i 4; i) {Order order new Order();order.setOrderNo(SP20230814000 i);order.setUserId(i);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}}2023-08-17 18:58:27.716 INFO 8660 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 18:58:27.716 INFO 8660 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692128729070862338, SP202308140001, 1, 100]
2023-08-17 18:58:27.742 INFO 8660 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 18:58:27.743 INFO 8660 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692128753561403393, SP202308140002, 2, 100]
2023-08-17 18:58:27.749 INFO 8660 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 18:58:27.749 INFO 8660 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692128753561403394, SP202308140003, 3, 100]
2023-08-17 18:58:27.754 INFO 8660 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 18:58:27.754 INFO 8660 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692128753561403395, SP202308140004, 4, 100]user_id为1,3奇数的数据保存在server-order1数据源的t_order_0表user_id为2,4偶数的数据保存在server-order0数据源的t_order_0表
server-order0 server-order1 水平分表
分片规则server-order0服务器中id为偶数时数据插入t_order0id为奇数时数据插入t_order1。
shardingsphere-sharding3.yaml
# 数据源配置
dataSources:server-user:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3301/db_userusername: rootpassword: 123456server-order0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3310/db_orderusername: rootpassword: 123456server-order1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3311/db_orderusername: rootpassword: 123456
#规则配置
rules:
- !SHARDINGtables:# 逻辑表名t_order:actualDataNodes: server-order0.t_order${0..1}#分表策略tableStrategy:standard:# 分片列名称shardingColumn: id# 分片算法名称shardingAlgorithmName: alg_inline_id# 分片算法配置shardingAlgorithms:# 标准分片算法-行表达式分片算法alg_inline_id:#基于行表达式的分片算法type: INLINEprops:algorithm-expression: t_order${id % 2}
#属性配置
props:sql-show: true执行 /*** 水平分片分表插入数据测试*/Testpublic void testInsertOrderTableStrategy(){for (long i 1; i 10; i) {Order order new Order();order.setId(i);order.setOrderNo(SP20230814000 i);order.setUserId(i);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}}id为2,4插入到t_order0表中id为1,3插入到t_order1表中
2023-08-17 21:59:30.107 INFO 24352 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 21:59:30.107 INFO 24352 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1, SP202308140001, 1, 100]
2023-08-17 21:59:30.139 INFO 24352 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 21:59:30.139 INFO 24352 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [2, SP202308140002, 2, 100]
2023-08-17 21:59:30.144 INFO 24352 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 21:59:30.144 INFO 24352 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [3, SP202308140003, 3, 100]
2023-08-17 21:59:30.148 INFO 24352 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 21:59:30.148 INFO 24352 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [4, SP202308140004, 4, 100]
2023-08-17 21:59:30.151 INFO 24352 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )分库分表
分片规则order表中user_id为偶数时数据插入server-order0服务器user_id为奇数时数据插入server-order1服务器。
id为偶数时数据插入t_order0id为奇数时数据插入t_order1。
shardingsphere-sharding4.yaml
# 数据源配置
dataSources:server-user:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3301/db_userusername: rootpassword: 123456server-order0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3310/db_orderusername: rootpassword: 123456server-order1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3311/db_orderusername: rootpassword: 123456
#规则配置
rules:
- !SHARDINGtables:# 逻辑表名t_order:actualDataNodes: server-order${0..1}.t_order${0..1}#分库策略databaseStrategy:standard:# 分片列名称shardingColumn: user_id# 分片算法名称shardingAlgorithmName: alg_inline_userid#分表策略tableStrategy:standard:# 分片列名称shardingColumn: id# 分片算法名称shardingAlgorithmName: alg_inline_id# 分片算法配置shardingAlgorithms:# 标准分片算法-行表达式分片算法alg_inline_userid:#基于行表达式的分片算法type: INLINEprops:algorithm-expression: server-order${user_id % 2}alg_inline_id:#基于行表达式的分片算法type: INLINEprops:algorithm-expression: t_order${id % 2}
#属性配置
props:sql-show: true/*** 水平分片分库分表插入数据*/Testpublic void testInsertOrderDatabaseAndTableStrategy(){for (long i 1; i 5; i) {Order order new Order();order.setId(i);order.setOrderNo(SP20230814000 i);order.setUserId(1L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}for (long i 5; i 9; i) {Order order new Order();order.setId(i);order.setOrderNo(SP20230814000 i);order.setUserId(2L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}}可以看到userId为偶数的插入到server-order0userId为奇数的插入到server-order1
id为偶数的插入到t_table0id为奇数的插入到table1中。
2023-08-17 22:09:59.399 INFO 23180 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 22:09:59.399 INFO 23180 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1, SP202308140001, 1, 100]
2023-08-17 22:09:59.443 INFO 23180 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 22:09:59.443 INFO 23180 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [2, SP202308140002, 1, 100]
2023-08-17 22:09:59.448 INFO 23180 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 22:09:59.448 INFO 23180 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [3, SP202308140003, 1, 100]
2023-08-17 22:09:59.452 INFO 23180 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 22:09:59.452 INFO 23180 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [4, SP202308140004, 1, 100]
2023-08-17 22:09:59.457 INFO 23180 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 22:09:59.457 INFO 23180 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [5, SP202308140005, 2, 100]
2023-08-17 22:09:59.462 INFO 23180 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 22:09:59.462 INFO 23180 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [6, SP202308140006, 2, 100]
2023-08-17 22:09:59.465 INFO 23180 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 22:09:59.465 INFO 23180 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [7, SP202308140007, 2, 100]
2023-08-17 22:09:59.468 INFO 23180 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-17 22:09:59.468 INFO 23180 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [8, SP202308140008, 2, 100]自动分片算法
为了帮助用户更好的使用分片功能降低配置复杂度和提升使用体验Apache ShardingSphere 5.0.0 版本推出了一种新的分片配置方式AutoTable。
自动分片算法的分片逻辑由 ShardingSphere 自动管理需要通过配置 autoTables 分片规则进行使用。
取模分片算法
shardingsphere-sharding5.yaml
使用取模分片算法总共4个分片两个数据源每个数据源中有两个分片
# 数据源配置
dataSources:server-order0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3310/db_orderusername: rootpassword: 123456server-order1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3311/db_orderusername: rootpassword: 123456
#规则配置
rules:- !SHARDINGautoTables:# 逻辑表名t_order:actualDataSources : server-order${0..1}#分片策略shardingStrategy:standard:# 分片列名称shardingColumn: user_id# 分片算法名称shardingAlgorithmName: alg_mod# 分片算法配置shardingAlgorithms:alg_mod:#自动分片算法-取模分片算法type: MODprops:sharding-count: 4
#属性配置
props:sql-show: true这里值得注意的一点是自动分片算法的分片逻辑由 ShardingSphere 自动管理所以之前创建的表可能不符合自动分片规则的可以让shardingsphere根据自动分片规则为我们创建对应的表 /*** 自动分片创建数据库表*/AutowiredJdbcTemplate jdbcTemplate;Testpublic void testCreateAutoOrderMod() {jdbcTemplate.execute(CREATE TABLE t_order (\n id BIGINT,\n order_no VARCHAR(30),\n user_id BIGINT,\n amount DECIMAL(10,2),\n PRIMARY KEY(id) USING BTREE\n ) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATDYNAMIC;);}将之前创建的表删除后执行
2023-08-18 07:59:52.896 INFO 10544 --- [ main] ShardingSphere-SQL : Logic SQL: CREATE TABLE t_order (id BIGINT,order_no VARCHAR(30),user_id BIGINT,amount DECIMAL(10,2),PRIMARY KEY(id) USING BTREE
) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATDYNAMIC;
2023-08-18 07:59:52.896 INFO 10544 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: CREATE TABLE t_order_1 (id BIGINT,order_no VARCHAR(30),user_id BIGINT,amount DECIMAL(10,2),PRIMARY KEY(id) USING BTREE
) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATDYNAMIC;
2023-08-18 07:59:52.896 INFO 10544 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: CREATE TABLE t_order_3 (id BIGINT,order_no VARCHAR(30),user_id BIGINT,amount DECIMAL(10,2),PRIMARY KEY(id) USING BTREE
) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATDYNAMIC;
2023-08-18 07:59:52.896 INFO 10544 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: CREATE TABLE t_order_0 (id BIGINT,order_no VARCHAR(30),user_id BIGINT,amount DECIMAL(10,2),PRIMARY KEY(id) USING BTREE
) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATDYNAMIC;
2023-08-18 07:59:52.896 INFO 10544 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: CREATE TABLE t_order_2 (id BIGINT,order_no VARCHAR(30),user_id BIGINT,amount DECIMAL(10,2),PRIMARY KEY(id) USING BTREE
) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATDYNAMIC;很明显ShardingSphere根据分片规则为我们在server-order0创建了t_order_0、t_order_2在server-order1中创建了t_order_1、t_order_3,这与我们之前手动创建的表名还是有些区别的。 插入数据 /*** 分片插入数据测试*/Testpublic void testInsertOrderShardingStrategy(){for (long i 1; i 5; i) {Order order new Order();order.setOrderNo(SP20230814000 i);order.setUserId(1L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}for (long i 5; i 9; i) {Order order new Order();order.setOrderNo(SP20230814000 i);order.setUserId(2L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}for (long i 10; i 15; i) {Order order new Order();order.setOrderNo(SP20230814000 i);order.setUserId(3L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}for (long i 15; i 19; i) {Order order new Order();order.setOrderNo(SP20230814000 i);order.setUserId(4L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}}根据取模分片算法推算下userId为1的数据会插入到第一个分片server-order1的t_order1表中
userId为2的数据会插入到第一个分片server-order0的t_order2表中
userId为3的数据会插入到第一个分片server-order1的t_order3表中
userId为4的数据会插入到第一个分片server-order1的t_order0表中
执行果然如此
2023-08-18 08:06:31.169 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.169 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327051316428801, SP202308140001, 1, 100]
2023-08-18 08:06:31.207 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.208 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074754199554, SP202308140002, 1, 100]
2023-08-18 08:06:31.212 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.212 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074821308417, SP202308140003, 1, 100]
2023-08-18 08:06:31.214 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.214 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074821308418, SP202308140004, 1, 100]
2023-08-18 08:06:31.215 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.215 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_2 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074821308419, SP202308140005, 2, 100]
2023-08-18 08:06:31.219 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.219 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_2 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074821308420, SP202308140006, 2, 100]
2023-08-18 08:06:31.223 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.223 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_2 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074821308421, SP202308140007, 2, 100]
2023-08-18 08:06:31.225 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.225 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_2 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074821308422, SP202308140008, 2, 100]
2023-08-18 08:06:31.227 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.227 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_3 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074888417282, SP2023081400010, 3, 100]
2023-08-18 08:06:31.229 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.229 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_3 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074888417283, SP2023081400011, 3, 100]
2023-08-18 08:06:31.231 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.231 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_3 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074888417284, SP2023081400012, 3, 100]
2023-08-18 08:06:31.233 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.233 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_3 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074888417285, SP2023081400013, 3, 100]
2023-08-18 08:06:31.235 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.235 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_3 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074888417286, SP2023081400014, 3, 100]
2023-08-18 08:06:31.236 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.236 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074888417287, SP2023081400015, 4, 100]
2023-08-18 08:06:31.239 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.239 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074888417288, SP2023081400016, 4, 100]
2023-08-18 08:06:31.241 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.241 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074888417289, SP2023081400017, 4, 100]
2023-08-18 08:06:31.243 INFO 21164 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-18 08:06:31.244 INFO 21164 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692327074951331842, SP2023081400018, 4, 100]哈希取模分片算法
shardingsphere-sharding6.yaml
# 数据源配置
dataSources:server-user:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3301/db_userusername: rootpassword: 123456server-order0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3310/db_orderusername: rootpassword: 123456server-order1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3311/db_orderusername: rootpassword: 123456
#规则配置
rules:
- !SHARDINGautoTables:# 逻辑表名t_order:actualDataSources : server-order${0..1}#分片策略shardingStrategy:standard:# 分片列名称shardingColumn: order_no# 分片算法名称shardingAlgorithmName: alg_hash_mod# 分片算法配置shardingAlgorithms:alg_hash_mod:type: HASH_MODprops:sharding-count: 4
#属性配置
props:sql-show: trueorder_no是个字符串类型值比如为SP2023081400001不能直接取模这样就不能对order_no使用取模分片算法所以针对order_no使用哈希取模分片算法注意这里的分片规则是order_no的hash值hash值对4取模就可以看出该订单落在那个分片上。
执行 /*** 哈希取模*/Testpublic void testInsertOrderHashModStrategy(){for (long i 1; i 10; i) {Order order new Order();order.setOrderNo(SP20230814000 i);order.setUserId(1L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);System.out.println(对订单号的hash值取模 order.getOrderNo().hashCode() % 4);}}2023-08-19 08:24:05.112 INFO 20980 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-19 08:24:05.112 INFO 20980 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692693855566049282, SP202308140001, 1, 100]
对订单号的hash值取模0
2023-08-19 08:24:05.164 INFO 20980 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-19 08:24:05.164 INFO 20980 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_3 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692693883210706946, SP202308140002, 1, 100]
对订单号的hash值取模-3
2023-08-19 08:24:05.176 INFO 20980 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-19 08:24:05.176 INFO 20980 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_2 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692693883277815809, SP202308140003, 1, 100]
对订单号的hash值取模-2
2023-08-19 08:24:05.182 INFO 20980 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-19 08:24:05.182 INFO 20980 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692693883340730370, SP202308140004, 1, 100]
对订单号的hash值取模-1
2023-08-19 08:24:05.186 INFO 20980 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-19 08:24:05.186 INFO 20980 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692693883340730371, SP202308140005, 1, 100]
对订单号的hash值取模0
2023-08-19 08:24:05.191 INFO 20980 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-19 08:24:05.191 INFO 20980 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_3 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692693883340730372, SP202308140006, 1, 100]
对订单号的hash值取模-3
2023-08-19 08:24:05.196 INFO 20980 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-19 08:24:05.196 INFO 20980 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_2 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692693883407839233, SP202308140007, 1, 100]
对订单号的hash值取模-2
2023-08-19 08:24:05.200 INFO 20980 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-19 08:24:05.200 INFO 20980 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692693883407839234, SP202308140008, 1, 100]
对订单号的hash值取模-1
2023-08-19 08:24:05.204 INFO 20980 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-19 08:24:05.204 INFO 20980 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1692693883407839235, SP202308140009, 1, 100]
对订单号的hash值取模0这里只是简单的介绍下AuTotable,更多分片算法可以参考
https://blog.csdn.net/ctwy291314/article/details/130561221
2.5、分布式序列算法
实现动机
传统数据库软件开发中主键自动生成技术是基本需求。而各个数据库对于该需求也提供了相应的支持比如 MySQL 的自增键Oracle 的自增序列等。 数据分片后不同数据节点生成全局唯一主键是非常棘手的问题。同一个逻辑表内的不同实际表之间的自增键由于无法互相感知而产生重复主键。 虽然可通过约束自增主键初始值和步长的方式避免碰撞但需引入额外的运维规则使解决方案缺乏完整性和可扩展性。
目前有许多第三方解决方案可以完美解决这个问题如 UUID 等依靠特定算法自生成不重复键或者通过引入主键生成服务等。为了方便用户使用、满足不同用户不同使用场景的需求 Apache ShardingSphere 不仅提供了内置的分布式主键生成器例如 UUID、SNOWFLAKE还抽离出分布式主键生成器的接口方便用户自行实现自定义的自增主键生成器。
内置的主键生成器
UUID
采用 UUID.randomUUID() 的方式产生分布式主键。
SNOWFLAKE
在分片规则配置模块可配置每个表的主键生成策略默认使用雪花算法snowflake生成 64bit 的长整型数据。
雪花算法是由 Twitter 公布的分布式主键生成算法它能够保证不同进程主键的不重复性以及相同进程主键的有序性。
实现原理
在同一个进程中它首先是通过时间位保证不重复如果时间相同则是通过序列位保证。 同时由于时间位是单调递增的且各个服务器如果大体做了时间同步那么生成的主键在分布式环境可以认为是总体有序的这就保证了对索引字段的插入的高效性。 例如 MySQL 的 Innodb 存储引擎的主键。
使用雪花算法生成的主键二进制表示形式包含 4 部分从高位到低位分表为1bit 符号位、41bit 时间戳位、10bit 工作进程位以及 12bit 序列号位。
符号位1bit
预留的符号位恒为零。
时间戳位41bit
41 位的时间戳可以容纳的毫秒数是 2 的 41 次幂一年所使用的毫秒数是365 * 24 * 60 * 60 * 1000。 通过计算可知
Math.pow(2, 41) / (365 * 24 * 60 * 60 * 1000L);结果约等于 69.73 年。 Apache ShardingSphere 的雪花算法的时间纪元从 2016年11月1日 零点开始可以使用到 2086 年相信能满足绝大部分系统的要求。
工作进程位10bit
该标志在 Java 进程内是唯一的如果是分布式应用部署应保证每个工作进程的 id 是不同的。 该值默认为 0可通过属性设置。
序列号位12bit
该序列是用来在同一个毫秒内生成不同的 ID。如果在这个毫秒内生成的数量超过 4096 (2 的 12 次幂)那么生成器会等待到下个毫秒继续生成。
雪花算法主键的详细结构见下图。 时钟回拨
服务器时钟回拨会导致产生重复序列因此默认分布式主键生成器提供了一个最大容忍的时钟回拨毫秒数。 如果时钟回拨的时间超过最大容忍的毫秒数阈值则程序报错如果在可容忍的范围内默认分布式主键生成器会等待时钟同步到最后一次主键生成的时间后再继续工作。 最大容忍的时钟回拨毫秒数的默认值为 0可通过属性设置。
雪花算法
水平分片需要关注全局序列因为不能简单的使用基于数据库的主键自增。
这里有两种方案一种是基于MyBatisPlus的id策略一种是ShardingSphere-JDBC的全局序列配置。
基于MyBatisPlus的id策略将Order类的id设置成如下形式
TableId(type IdType.ASSIGN_ID)
private Long id;基于ShardingSphere-JDBC的全局序列配置和前面的MyBatisPlus的策略二选一
配置分布式序列策略配置和分布式序列算法配置
#规则配置
rules:
- !SHARDINGautoTables:# 逻辑表名t_order:actualDataSources : server-order${0..1}#分片策略shardingStrategy:standard:# 分片列名称shardingColumn: order_no# 分片算法名称shardingAlgorithmName: alg_hash_mod#分布式序列策略配置keyGenerateStrategy:# 分布式序列列名称column: id# 分布式序列算法名称keyGeneratorName: snowflake# 分片算法配置shardingAlgorithms:alg_hash_mod:type: HASH_MODprops:sharding-count: 4# 分布式序列算法配置 keyGeneratorsnowflake:# 分布式序列算法类型type: SNOWFLAKE
#属性配置
props:sql-show: true此时需要将实体类中的id策略修改成以下形式
//当配置了shardingsphere-jdbc的分布式序列时自动使用shardingsphere-jdbc的分布式序列
//当没有配置shardingsphere-jdbc的分布式序列时自动依赖数据库的主键自增策略
TableId(type IdType.AUTO)执行方法 /*** 哈希取模*/Testpublic void testInsertOrderHashModStrategy(){for (long i 1; i 10; i) {Order order new Order();order.setOrderNo(SP20230814000 i);order.setUserId(1L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);System.out.println(对订单号的hash值取模 order.getOrderNo().hashCode() % 4);}}
}2023-08-20 10:12:23.145 INFO 27620 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-20 10:12:23.145 INFO 27620 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1693083499130646529, SP202308140001, 1, 100]
对订单号的hash值取模0
2023-08-20 10:12:23.179 INFO 27620 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-20 10:12:23.179 INFO 27620 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_3 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1693083525789642754, SP202308140002, 1, 100]
对订单号的hash值取模-3
2023-08-20 10:12:23.185 INFO 27620 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-20 10:12:23.185 INFO 27620 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_2 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1693083525789642755, SP202308140003, 1, 100]
对订单号的hash值取模-2
2023-08-20 10:12:23.188 INFO 27620 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-20 10:12:23.188 INFO 27620 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1693083525789642756, SP202308140004, 1, 100]
对订单号的hash值取模-1
2023-08-20 10:12:23.192 INFO 27620 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-20 10:12:23.192 INFO 27620 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1693083525852557313, SP202308140005, 1, 100]
对订单号的hash值取模0
2023-08-20 10:12:23.196 INFO 27620 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-20 10:12:23.196 INFO 27620 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_3 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1693083525852557314, SP202308140006, 1, 100]
对订单号的hash值取模-3
2023-08-20 10:12:23.199 INFO 27620 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-20 10:12:23.200 INFO 27620 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_2 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1693083525852557315, SP202308140007, 1, 100]
对订单号的hash值取模-2
2023-08-20 10:12:23.203 INFO 27620 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-20 10:12:23.203 INFO 27620 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: INSERT INTO t_order_1 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1693083525852557316, SP202308140008, 1, 100]
对订单号的hash值取模-1
2023-08-20 10:12:23.206 INFO 27620 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_no,
user_id,
amount ) VALUES ( ?,
?,
?,
? )
2023-08-20 10:12:23.207 INFO 27620 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: INSERT INTO t_order_0 ( id,
order_no,
user_id,
amount ) VALUES (?, ?, ?, ?) ::: [1693083525852557317, SP202308140009, 1, 100]
对订单号的hash值取模0分布式序列算法
https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/keygen/
