做印刷的有什么网站,如何在百度网站收录提交入口,营销型网站建设公司平台,各大企业网站文案文章目录 1 准备服务器1.1 创建server-order0容器1.2 创建server-order1容器 2、基本水平分片2.1、基本配置2.2、数据源配置2.3、标椎分片表配置2.4、行表达式2.5、分片算法配置2.6、分布式序列算法 3、多表关联3.1、创建关联表3.2、创建实体类3.3、创建Mapper3.4、配置关联表3… 文章目录 1 准备服务器1.1 创建server-order0容器1.2 创建server-order1容器 2、基本水平分片2.1、基本配置2.2、数据源配置2.3、标椎分片表配置2.4、行表达式2.5、分片算法配置2.6、分布式序列算法 3、多表关联3.1、创建关联表3.2、创建实体类3.3、创建Mapper3.4、配置关联表3.5、测试插入数据 4、绑定表4.1、创建VO对象4.2、添加Mapper方法4.3、测试关联查询4.4、配置绑定表 5、广播表4.1、什么是广播表4.2、创建广播表4.3、程序实现4.3.1、创建实体类4.3.2、创建Mapper4.3.3、配置广播表 4.4、测试广播表 5 配置文件方式6 问题集6.1 简述6.1 Parameter index out of range6.2 No implementation class load from SPI6.3 Error creating bean with name org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration6.4 could not determine a constructor for the tag !SHARDING:6.5 Data sources cannot be empty6.6 Insert statement does not support sharding table routing to multiple data nodes6.7 Inline sharding algorithm expression cannot be null or empty 结语 1 准备服务器
随着系统业务的发展t_order表数据快速增长服务器压力增大影响系统性能我需要对server-order进行分库分表。 服务器规划 服务器容器名server-order0端口号3310服务器容器名server-order1端口号3311
1.1 创建server-order0容器 step1创建挂载文件夹 mkdir -p server-order0/conf/conf.d
mkdir server-order0/dataStep2创建容器 docker run -it -p 3310:3306 --name server-order0 --privilegedtrue -v /Users/gaogzhen/data/docker/mysql/mysql8/server-order0/conf/conf.d:/etc/mysql/conf.d -v /Users/gaogzhen/data/docker/mysql/mysql8/server-order0/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD123456 -d mysqlstep3登录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;step4创建数据库 注意水平分片的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创建挂载文件夹 mkdir -p server-order1/conf/conf.d
mkdir server-order1/dataStep2创建容器 docker run -it -p 3311:3306 --name server-order1 --privilegedtrue -v /Users/gaogzhen/data/docker/mysql/mysql8/server-order1/conf/conf.d:/etc/mysql/conf.d -v /Users/gaogzhen/data/docker/mysql/mysql8/server-order1/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD123456 -d mysqlstep3登录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;step4创建数据库 注意水平分片的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)
);2、基本水平分片
2.1、基本配置
#基本配置
# 应用名称
spring.application.namesharging-jdbc-demo
# 开发环境设置
spring.profiles.activedev
# 内存模式
spring.shardingsphere.mode.typeMemory
# 打印SQl
spring.shardingsphere.props.sql-showtrue2.2、数据源配置
#数据源配置
# 配置真实数据源
spring.shardingsphere.datasource.namesserver-user,server-order0,server-order1# 配置第 1 个数据源
spring.shardingsphere.datasource.server-user.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-user.driver-class-namecom.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-user.jdbc-urljdbc:mysql://192.168.100.201:3301/db_user
spring.shardingsphere.datasource.server-user.usernameroot
spring.shardingsphere.datasource.server-user.password123456# 配置第 2 个数据源
spring.shardingsphere.datasource.server-order0.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-order0.driver-class-namecom.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-order0.jdbc-urljdbc:mysql://192.168.100.201:3310/db_order
spring.shardingsphere.datasource.server-order0.usernameroot
spring.shardingsphere.datasource.server-order0.password123456# 配置第 3 个数据源
spring.shardingsphere.datasource.server-order1.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-order1.driver-class-namecom.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-order1.jdbc-urljdbc:mysql://192.168.100.201:3311/db_order
spring.shardingsphere.datasource.server-order1.usernameroot
spring.shardingsphere.datasource.server-order1.password1234562.3、标椎分片表配置
#标准分片表配置数据节点配置
# spring.shardingsphere.rules.sharding.tables.table-name.actual-data-nodes值
# 值由数据源名 表名组成以小数点分隔。多个表以逗号分隔支持 inline 表达式。
# table-name逻辑表名
spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodesserver-user.t_user
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodesserver-order0.t_order0,server-order0.t_order1,server-order1.t_order0,server-order1.t_order1修改Order实体类的主键策略
//TableId(type IdType.AUTO)//依赖数据库的主键自增策略
TableId(type IdType.ASSIGN_ID)//分布式id测试保留上面配置中的一个分片表节点分别进行测试检查每个分片节点是否可用
/*** 水平分片插入数据测试*/
Test
public void testInsertOrder(){Order order new Order();order.setOrderNo(20230822001);order.setUserId(1L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);
}2.4、行表达式
优化上一步的分片表配置
https://shardingsphere.apache.org/document/5.1.1/cn/features/sharding/concept/inline-expression/
#标准分片表配置数据节点配置
# spring.shardingsphere.rules.sharding.tables.table-name.actual-data-nodes值
# 值由数据源名 表名组成以小数点分隔。多个表以逗号分隔支持 inline 表达式。
# table-name逻辑表名
spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodesserver-user.t_user
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodesserver-order$-{0..1}.t_order$-{0..1}2.5、分片算法配置
水平分库
分片规则order表中user_id为偶数时数据插入server-order0服务器user_id为奇数时数据插入server-order1服务器。这样分片的好处是同一个用户的订单数据一定会被插入到同一台服务器上查询一个用户的订单时效率较高。
#------------------------分库策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-columnuser_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-namealg_inline_userid#------------------------分片算法配置
# 行表达式分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.typeINLINE
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.props.algorithm-expressionserver-order$-{user_id % 2}# 取模分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.typeMOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.props.sharding-count2为了方便测试先设置只在 t_order0表上进行测试
xxx.actual-data-nodesserver-order$-{0..1}.t_order0测试可以分别测试行表达式分片算法和取模分片算法
/*** 水平分片分库插入数据测试*/
Test
public void testInsertOrderDatabaseStrategy(){for (long i 0; i 4; i) {Order order new Order();order.setOrderNo(20230821001);order.setUserId(i 1);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}}水平分表
分片规则order表中order_no的哈希值为偶数时数据插入对应服务器的t_order0表order_no的哈希值为奇数时数据插入对应服务器的t_order1表。因为order_no是字符串形式因此不能直接取模。
#------------------------分表策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-columnorder_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-namealg_hash_mod#------------------------分片算法配置
# 哈希取模分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.typeHASH_MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.props.sharding-count2
测试前不要忘记将如下节点改回原来的状态
xxx.actual-data-nodesserver-order$-{0..1}.t_order$-{0..1}测试
/*** 水平分片分表插入数据测试*/
Test
public void testInsertOrderTableStrategy(){for (long i 1; i 5; i) {Order order new Order();order.setOrderNo(gaogzhen 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(gaogzhen i);order.setUserId(2L);order.setAmount(new BigDecimal(100));orderMapper.insert(order);}
}/*** 测试哈希取模*/
Test
public void testHash(){//注意hash取模的结果是整个字符串hash后再取模和数值后缀是奇数还是偶数无关System.out.println(gaogzhen001.hashCode() % 2);System.out.println(gaogzhen0011.hashCode() % 2);
}查询测试
/*** 水平分片查询所有记录* 查询了两个数据源每个数据源中使用UNION ALL连接两个表*/
Test
public void testShardingSelectAll(){ListOrder orders orderMapper.selectList(null);orders.forEach(System.out::println);
}/*** 水平分片根据user_id查询记录* 查询了一个数据源每个数据源中使用UNION ALL连接两个表*/
Test
public void testShardingSelectByUserId(){QueryWrapperOrder orderQueryWrapper new QueryWrapper();orderQueryWrapper.eq(user_id, 1L);ListOrder orders orderMapper.selectList(orderQueryWrapper);orders.forEach(System.out::println);
}2.6、分布式序列算法
雪花算法
https://shardingsphere.apache.org/document/5.1.1/cn/features/sharding/concept/key-generator/
水平分片需要关注全局序列因为不能简单的使用基于数据库的主键自增。
这里有两种方案一种是基于MyBatisPlus的id策略一种是ShardingSphere-JDBC的全局序列配置。
基于MyBatisPlus的id策略将Order类的id设置成如下形式
TableId(type IdType.ASSIGN_ID)
private Long id;基于ShardingSphere-JDBC的全局序列配置和前面的MyBatisPlus的策略二选一
#------------------------分布式序列策略配置
# 分布式序列列名称
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.columnid
# 分布式序列算法名称
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-namealg_snowflake# 分布式序列算法配置
# 分布式序列算法类型
spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.typeSNOWFLAKE
# 分布式序列算法属性配置
#spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.props.xxx此时需要将实体类中的id策略修改成以下形式
//当配置了shardingsphere-jdbc的分布式序列时自动使用shardingsphere-jdbc的分布式序列
//当没有配置shardingsphere-jdbc的分布式序列时自动依赖数据库的主键自增策略
TableId(type IdType.AUTO)3、多表关联
3.1、创建关联表
在server-order0、server-order1服务器中分别创建两张订单详情表t_order_item0、t_order_item1
我们希望同一个用户的订单表和订单详情表中的数据都在同一个数据源中避免跨库关联因此这两张表我们使用相同的分片策略。
那么在t_order_item中我们也需要创建order_no和user_id这两个分片键
CREATE TABLE t_order_item0(id BIGINT,order_no VARCHAR(30),user_id BIGINT,price DECIMAL(10,2),count INT,PRIMARY KEY(id)
);CREATE TABLE t_order_item1(id BIGINT,order_no VARCHAR(30),user_id BIGINT,price DECIMAL(10,2),count INT,PRIMARY KEY(id)
);3.2、创建实体类
package com.gaogzhen.shardingjdbcdemo.entity;TableName(t_order_item)
Data
public class OrderItem {//当配置了shardingsphere-jdbc的分布式序列时自动使用shardingsphere-jdbc的分布式序列TableId(type IdType.AUTO)private Long id;private String orderNo;private Long userId;private BigDecimal price;private Integer count;
}3.3、创建Mapper
package com.gaogzhen.shargingjdbcdemo.mapper;Mapper
public interface OrderItemMapper extends BaseMapperOrderItem {}3.4、配置关联表
t_order_item的分片表、分片策略、分布式序列策略和t_order一致
#------------------------标准分片表配置数据节点配置
spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodesserver-order$-{0..1}.t_order_item$-{0..1}#------------------------分库策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-columnuser_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-algorithm-namealg_mod#------------------------分表策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-columnorder_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-namealg_hash_mod#------------------------分布式序列策略配置
# 分布式序列列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.columnid
# 分布式序列算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.key-generator-namealg_snowflake3.5、测试插入数据
同一个用户的订单表和订单详情表中的数据都在同一个数据源中避免跨库关联
/*** 测试关联表插入*/
Test
public void testInsertOrderAndOrderItem(){for (long i 1; i 3; i) {Order order new Order();order.setOrderNo(gaogzhen i);order.setUserId(1L);orderMapper.insert(order);for (long j 1; j 3; j) {OrderItem orderItem new OrderItem();orderItem.setOrderNo(gaogzhen i);orderItem.setUserId(1L);orderItem.setPrice(new BigDecimal(10));orderItem.setCount(2);orderItemMapper.insert(orderItem);}}for (long i 5; i 7; i) {Order order new Order();order.setOrderNo(gaogzhen i);order.setUserId(2L);orderMapper.insert(order);for (long j 1; j 3; j) {OrderItem orderItem new OrderItem();orderItem.setOrderNo(gaogzhen i);orderItem.setUserId(2L);orderItem.setPrice(new BigDecimal(1));orderItem.setCount(3);orderItemMapper.insert(orderItem);}}}4、绑定表
**需求**查询每个订单的订单号和总订单金额
4.1、创建VO对象
package com.gaogzhen.shardingjdbcdemo.entity;Data
public class OrderVo {private String orderNo;private BigDecimal amount;
}4.2、添加Mapper方法
OrderMapper.java
package com.gaogzhen.shardingjdbcdemo.mapper;Mapper
public interface OrderMapper extends BaseMapperOrder {/*** 计算订单金额* return 订单金额列表*/ListOrderVO getOrderAmount();}OrderMapper.xml select idgetOrderAmount resultTypecom.gaogzhen.shardingjdbcdemo.vo.OrderVOselectt1.order_no,sum(t2.price * t2.count) amountfromt_order t1join t_order_item t2 on t2.order_no t1.order_nogroup byt1.order_no/select4.3、测试关联查询
/*** 测试关联表查询*/
Test
public void testGetOrderAmount(){ListOrderVo orderAmountList orderMapper.getOrderAmount();orderAmountList.forEach(System.out::println);
}查询结果
2023-08-23 20:10:40.015 INFO 27448 --- [ main] ShardingSphere-SQL : Logic SQL: selectt1.order_no,sum(t2.price * t2.count) amountfromt_order t1join t_order_item t2 on t2.order_no t1.order_nogroup byt1.order_no
2023-08-23 20:10:40.015 INFO 27448 --- [ main] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(tableOptional.empty, limitOptional.empty, lockOptional.empty, windowOptional.empty)
2023-08-23 20:10:40.015 INFO 27448 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: selectt1.order_no,sum(t2.price * t2.count) amountfromt_order0 t1join t_order_item0 t2 on t2.order_no t1.order_nogroup byt1.order_no ORDER BY t1.order_no ASC
2023-08-23 20:10:40.015 INFO 27448 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: selectt1.order_no,sum(t2.price * t2.count) amountfromt_order1 t1join t_order_item0 t2 on t2.order_no t1.order_nogroup byt1.order_no ORDER BY t1.order_no ASC
2023-08-23 20:10:40.015 INFO 27448 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: selectt1.order_no,sum(t2.price * t2.count) amountfromt_order0 t1join t_order_item1 t2 on t2.order_no t1.order_nogroup byt1.order_no ORDER BY t1.order_no ASC
2023-08-23 20:10:40.015 INFO 27448 --- [ main] ShardingSphere-SQL : Actual SQL: server-order1 ::: selectt1.order_no,sum(t2.price * t2.count) amountfromt_order1 t1join t_order_item1 t2 on t2.order_no t1.order_nogroup byt1.order_no ORDER BY t1.order_no ASC
2023-08-23 20:10:40.015 INFO 27448 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: selectt1.order_no,sum(t2.price * t2.count) amountfromt_order0 t1join t_order_item0 t2 on t2.order_no t1.order_nogroup byt1.order_no ORDER BY t1.order_no ASC
2023-08-23 20:10:40.015 INFO 27448 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: selectt1.order_no,sum(t2.price * t2.count) amountfromt_order1 t1join t_order_item0 t2 on t2.order_no t1.order_nogroup byt1.order_no ORDER BY t1.order_no ASC
2023-08-23 20:10:40.015 INFO 27448 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: selectt1.order_no,sum(t2.price * t2.count) amountfromt_order0 t1join t_order_item1 t2 on t2.order_no t1.order_nogroup byt1.order_no ORDER BY t1.order_no ASC
2023-08-23 20:10:40.015 INFO 27448 --- [ main] ShardingSphere-SQL : Actual SQL: server-order0 ::: selectt1.order_no,sum(t2.price * t2.count) amountfromt_order1 t1join t_order_item1 t2 on t2.order_no t1.order_nogroup byt1.order_no ORDER BY t1.order_no ASC
OrderVO(orderNogaogzhen1, amount40.00)
OrderVO(orderNogaogzhen2, amount40.00)
OrderVO(orderNogaogzhen5, amount6.00)
OrderVO(orderNogaogzhen6, amount6.00)
4.4、配置绑定表
在原来水平分片配置的基础上添加如下配置
#------------------------绑定表
spring.shardingsphere.rules.sharding.binding-tables[0]t_order,t_order_item配置完绑定表后再次进行关联查询的测试 **如果不配置绑定表测试的结果为8个SQL。**多表关联查询会出现笛卡尔积关联。 如果配置绑定表测试的结果为4个SQL。 多表关联查询不会出现笛卡尔积关联关联查询效率将大大提升。
绑定表指分片规则一致的一组分片表。 使用绑定表进行多表关联查询时必须使用分片键进行关联否则会出现笛卡尔积关联或跨库关联从而影响查询效率。
目前测试还是查询8个SQL 配置未生效暂时没找到解决方法
5、广播表
4.1、什么是广播表
指所有的分片数据源中都存在的表表结构及其数据在每个数据库中均完全一致。 适用于数据量不大且需要与海量数据的表进行关联查询的场景例如字典表。
广播具有以下特性
1插入、更新操作会实时在所有节点上执行保持各个分片的数据一致性
2查询操作只从一个节点获取
3可以跟任何一个表进行 JOIN 操作
4.2、创建广播表
在server-order0、server-order1和server-user服务器中分别创建t_dict表
CREATE TABLE t_dict(id BIGINT,dict_type VARCHAR(200),PRIMARY KEY(id)
);4.3、程序实现
4.3.1、创建实体类
package com.gaogzhen.shardingjdbcdemo.entity;TableName(t_dict)
Data
public class Dict {//可以使用MyBatisPlus的雪花算法TableId(type IdType.ASSIGN_ID)private Long id;private String dictType;
}4.3.2、创建Mapper
package com.gaogzhen.shardingjdbcdemo.mapper;Mapper
public interface DictMapper extends BaseMapperDict {
}4.3.3、配置广播表
#数据节点可不配置默认情况下向所有数据源广播
spring.shardingsphere.rules.sharding.tables.t_dict.actual-data-nodesserver-user.t_dict,server-order$-{0..1}.t_dict# 广播表
spring.shardingsphere.rules.sharding.broadcast-tables[0]t_dict4.4、测试广播表
Autowired
private DictMapper dictMapper;/*** 广播表每个服务器中的t_dict同时添加了新数据*/
Test
public void testBroadcast(){Dict dict new Dict();dict.setDictType(type1);dictMapper.insert(dict);
}/*** 查询操作只从一个节点获取数据* 随机负载均衡规则*/
Test
public void testSelectBroadcast(){ListDict dicts dictMapper.selectList(null);dicts.forEach(System.out::println);
}5 配置文件方式
application.properties
#----------------------- 基础配置
# 项目名称
spring.application.namesharding-jdbc-demo
spring.profiles.activedev
# shardingsphere 配置
# 模式
spring.shardingsphere.mode.typeMemory# 数据源名称
spring.shardingsphere.datasource.namesserver-user,server-order0,server-order1#------------------------ 数据源配置
# 配置第1个数据源
spring.shardingsphere.datasource.server-user.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-user.driver-class-namecom.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-user.jdbc-urljdbc:mysql://127.0.0.1:3301/db_user?allowPublicKeyRetrievaltrueuseSSLfalse
spring.shardingsphere.datasource.server-user.usernameroot
spring.shardingsphere.datasource.server-user.password123456# 配置第2个数据源
spring.shardingsphere.datasource.server-order0.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-order0.driver-class-namecom.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-order0.jdbc-urljdbc:mysql://127.0.0.1:3310/db_order?allowPublicKeyRetrievaltrueuseSSLfalse
spring.shardingsphere.datasource.server-order0.usernameroot
spring.shardingsphere.datasource.server-order0.password123456# 配置第3个数据源
spring.shardingsphere.datasource.server-order1.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-order1.driver-class-namecom.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-order1.jdbc-urljdbc:mysql://127.0.0.1:3311/db_order?allowPublicKeyRetrievaltrueuseSSLfalse
spring.shardingsphere.datasource.server-order1.usernameroot
spring.shardingsphere.datasource.server-order1.password123456#------------------------数据节点配置
## 标准分配表配置
# spring.shardingsphere.rules.sharding.tables.table-name.actual-data-nodes值
# 值由数据源名 表名组成以小数点分隔。多个表以逗号分隔支持 inline 表达式。
# table-name逻辑表名
spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodesserver-user.t_user
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodesserver-order$-{0..1}.t_order$-{0..1}
#spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodesserver-order$-{0..1}.t_order0#------------------------分库策略
# 分片列配置
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-columnuser_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-namealg_inline_userid#------------------------分片算法配置
# 行表达式分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.typeINLINE
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.props.algorithm-expressionserver-order$-{user_id % 2}# 取模分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.typeMOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.props.sharding-count2#------------------------分表策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-columnorder_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-namealg_hash_mod#------------------------分片算法配置
# 哈希取模分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.typeHASH_MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.props.sharding-count2#------------------------分布式序列策略配置
# 分布式序列列名称
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.columnid
# 分布式序列算法名称
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-namealg_snowflake# 分布式序列算法配置
# 分布式序列算法类型
spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.typeSNOWFLAKE
# 分布式序列算法属性配置
#spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.props.xxx#------------------------标准分片表配置数据节点配置
spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodesserver-order$-{0..1}.t_order_item$-{0..1}#------------------------分库策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-columnuser_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-algorithm-namealg_mod#------------------------分表策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-columnorder_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-namealg_hash_mod#------------------------分布式序列策略配置
# 分布式序列列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.columnid
# 分布式序列算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.key-generator-namealg_snowflake#------------------------绑定表
spring.shardingsphere.rules.sharding.binding-tablest_order,t_order_item# 广播表
spring.shardingsphere.rules.sharding.broadcast-tables[0]t_dict# 打印日志
spring.shardingsphere.props.sql-showtrue# mybatis plus 配置
mybatis.mapper-locationsclasspath:mapper/*.xml
mybatis.type-aliases-packagecom.gaogzhen.shardingjdbcdemo.entity application.propertiesapplicaton-dev.yml #----------------------- 基础配置
# 项目名称
spring.application.namesharding-jdbc-demo
spring.profiles.activedev# mybatis plus 配置
mybatis.mapper-locationsclasspath:mapper/*.xml
mybatis.type-aliases-packagecom.gaogzhen.shardingjdbcdemo.entityspring:shardingSphere:mode:type: Memoryschema:name: horizontal-shardingdatasource:names: server_user,server-order0,server-order1server-user:type: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3301/db_user?allowPublicKeyRetrievaltrueuseSSLfalseusername: rootpassword: 123456server-order0:type: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3310/db_order?allowPublicKeyRetrievaltrueuseSSLfalseusername: rootpassword: 123456server-order1:type: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3311/db_order?allowPublicKeyRetrievaltrueuseSSLfalseusername: rootpassword: 123456rules:sharding:tables:t_user:actualDataNodes: server-user.t_usert_order:actualDataNodes: server-order$-{0..1}.t_order$-{0..1}databaseStrategy:standard:shardingColumn: user_idshardingAlgorithmName: alg-inline-useridtableStrategy:standard:shardingColumn: order_noshardingAlgorithmName: alg-hash-modkeyGenerateStrategy:column: idkeyGeneratorName: alg-snowflaket_order_item:actualDataNodes: server-order$-{0..1}.t_order_item$-{0..1}databaseStrategy:standard:shardingColumn: user_idshardingAlgorithmName: alg-modtableStrategy:standard:shardingColumn: order_noshardingAlgorithmName: alg-hash-modkeyGenerateStrategy:column: idkeyGeneratorName: alg-snowflakekeyGenerators:alg-snowflake:type: SNOWFLAKEshardingAlgorithms:alg-inline-userid:type: INLINEprops:algorithm-expression: server-order$-{user_id % 2}alg-mod:type: MODprops:sharding-count: 2alg-hash-mod:type: HASH_MODprops:sharding-count: 2binding-tables: t_order,t_order_itembroadcast-tables: t_dictprops:sqlShow: true6 问题集
6.1 简述
sharding-jdbc 报错多半报错因为配置文件引起的除了个人粗心大意外多半和官方给的配置字段名有关。官方文档配置字段名有的给驼峰形式有的给”-“连接形式这里建议统一用”-连接的形式。
props下的所有配置需要使用-连接的形式不然报错或者不生效
6.1 Parameter index out of range
报错内容如下
### Error updating database. Cause: java.sql.SQLException: Parameter index out of range (1 number of parameters, which is 0).
### The error may exist in com/gaogzhen/shardingjdbcdemo/mapper/OrderItemMapper.java (best guess)
### The error may involve com.gaogzhen.shardingjdbcdemo.mapper.OrderItemMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO t_order_item0 ( order_no, user_id, price, count ) VALUES ( ?, ?, ?, ? )
### Cause: java.sql.SQLException: Parameter index out of range (1 number of parameters, which is 0).
; Parameter index out of range (1 number of parameters, which is 0).; nested exception is java.sql.SQLException: Parameter index out of range (1 number of parameters, which is 0).at com.gaogzhen.shardingjdbcdemo.HorizontalShardingTest.testInsertOrderAndOrderItem(HorizontalShardingTest.java:102)
Caused by: java.sql.SQLException: Parameter index out of range (1 number of parameters, which is 0).at com.gaogzhen.shardingjdbcdemo.HorizontalShardingTest.testInsertOrderAndOrderItem(HorizontalShardingTest.java:102)可能出现问题原因
首选确保官网文档固定的配置项不出现错误比如table-strategy 大小写下划线或驼峰形式对于自定义的数据源名称、逻辑表名称注意前后一致然后MybatisPlus实体类表名注解TableName(value “t_order_item”) 其中表名为配置的逻辑表名非真实表名
6.2 No implementation class load from SPI
报错内容
org.apache.shardingsphere.spi.exception.ServiceProviderNotFoundException: No implementation class load from SPI org.apache.shardingsphere.sharding.spi.ShardingAlgorithm with type null.6.3 Error creating bean with name ‘org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration’
报错内容
java.lang.IllegalStateException: Failed to load ApplicationContext
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration: Initialization of bean failed; nested exception is java.lang.NullPointerException
Caused by: java.lang.NullPointerException出错原因 # 按照官网文档配置的数据源如下datasource:names: server_user,server_order0,server_order1server_user:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://127.0.0.1:3301/db_user?allowPublicKeyRetrievaltrueuseSSLfalseusername: rootpassword: 123456解决方案 datasource:names: server_user,server_order0,server_order1server_user:type: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3301/db_user?allowPublicKeyRetrievaltrueuseSSLfalseusername: rootpassword: 123456dataSourceClassName替换为typejdbcUrl替换为url
6.4 could not determine a constructor for the tag !SHARDING:
报错内容
java.lang.IllegalStateException: Failed to load ApplicationContext
Caused by: org.yaml.snakeyaml.constructor.ConstructorException:
could not determine a constructor for the tag !SHARDING:in reader, line 28, column 7:- !SHARDING:我的shardingsphere 版本5.1.1 按照官网sharding-jdbc yaml配置会报上述错误不识别- !SHARDING 解决方案 把- !SHARDING替换为sharding如下图所示
6.5 Data sources cannot be empty 报错内容 Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method shardingSphereDataSource threw exception; nested exception is java.lang.IllegalArgumentException: Data sources cannot be empty.
Caused by: java.lang.IllegalArgumentException: Data sources cannot be empty.出错原因 # 按照官网文档配置dataSources:server_user:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://127.0.0.1:3301/db_user?allowPublicKeyRetrievaltrueuseSSLfalseusername: rootpassword: 123456解决方案 datasource:names: server_user,server_order0,server_order1server_user:type: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3301/db_user?allowPublicKeyRetrievaltrueuseSSLfalseusername: rootpassword: 123456dataSources替换为datasource添加names属性值为逻辑属性源
6.6 Insert statement does not support sharding table routing to multiple data nodes 报错内容 org.mybatis.spring.MyBatisSystemException:
nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.lang.IllegalStateException: Insert statement does not support sharding table routing to multiple data nodes.
### The error may exist in com/gaogzhen/shardingjdbcdemo/mapper/OrderMapper.java (best guess)
### The error may involve com.gaogzhen.shardingjdbcdemo.mapper.OrderMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO t_order ( order_no, user_id ) VALUES ( ?, ? )
### Cause: java.lang.IllegalStateException: Insert statement does not support sharding table routing to multiple data nodes.at com.gaogzhen.shardingjdbcdemo.HorizontalShardingTest.testInsertOrderAndOrderItem(HorizontalShardingTest.java:94)
Caused by: org.apache.ibatis.exceptions.PersistenceException: 报错原因 rules:sharding:tables:t_order_item:actualDataNodes: server-order$-{0..1}.t_order_item$-{0..1}databaseStrategy:standard:shardingColumn: user_idshardingAlgorithmName: alg-modtableStrategy:standard:shardingColumn: order_idshardingAlgorithmName: alg-hash-modkeyGenerateStrategy:column: idkeyGeneratorName: alg-snowflakekeyGenerators:alg-snowflake:type: SNOWFLAKEshardingAlgorithms:alg-inline-userid:type: INLINEprops:algorithm-expression: server-order$-{user_id % 2}alg-mod:type: MODprops:sharding-count: 2alg-hash-mod:type: HASH_MODprops:sharding-count: 2分库或者分表算法名称不能使用“_下划线分割 用“-”代替 6.7 Inline sharding algorithm expression cannot be null or empty 报错原因 shardingAlgorithms:alg-inline-userid:type: INLINEprops:algorithmExpression: server-order$-{user_id % 2}algorithmExpression不能为驼峰命名 解决方案 algorithmExpression改为algorithm-expression结语
如果小伙伴什么问题或者指教欢迎交流。 QQ:806797785 仓库源代码地址https://gitee.com/gaogzhen/shardingsphere-jdbc-demo.git 参考链接:
[1]ShardingSphere5实战教程[CP/OL].2022-09-14.p18-23.
[2]0101读写分离测试-jdbc-shardingsphere-中间件[CP/OL].
[3]0102垂直分片-jdbc-shardingsphere[CP/OL].
