文章目录
- 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、创建Mapper
- 3.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、创建Mapper
- 4.3.3、配置广播表
- 4.4、测试广播表
- 5 配置文件方式
- 6 问题集
- 6.1 简述
- 6.1 Parameter index out of range
- 6.2 No implementation class load from SPI
- 6.3 Error creating bean with name ‘org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration’
- 6.4 could not determine a constructor for the tag !SHARDING:
- 6.5 Data sources cannot be empty
- 6.6 Insert statement does not support sharding table routing to multiple data nodes
- 6.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.dmkdir server-order0/dataStep2：创建容器
docker run -it -p 3310:3306 --name server-order0 --privileged=true -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_PASSWORD=123456 -d mysql- step3：登录MySQL服务器：
#进入容器：docker exec -it server-order0 env LANG=C.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.dmkdir server-order1/dataStep2：创建容器
docker run -it -p 3311:3306 --name server-order1 --privileged=true -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_PASSWORD=123456 -d mysql- step3：登录MySQL服务器：
#进入容器：docker exec -it server-order0 env LANG=C.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.name=sharging-jdbc-demo# 开发环境设置spring.profiles.active=dev# 内存模式spring.shardingsphere.mode.type=Memory# 打印SQlspring.shardingsphere.props.sql-show=true2.2、数据源配置
#========================数据源配置# 配置真实数据源spring.shardingsphere.datasource.names=server-user,server-order0,server-order1# 配置第 1 个数据源spring.shardingsphere.datasource.server-user.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.server-user.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.server-user.jdbc-url=jdbc:mysql://192.168.100.201:3301/db_userspring.shardingsphere.datasource.server-user.username=rootspring.shardingsphere.datasource.server-user.password=123456# 配置第 2 个数据源spring.shardingsphere.datasource.server-order0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.server-order0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.server-order0.jdbc-url=jdbc:mysql://192.168.100.201:3310/db_orderspring.shardingsphere.datasource.server-order0.username=rootspring.shardingsphere.datasource.server-order0.password=123456# 配置第 3 个数据源spring.shardingsphere.datasource.server-order1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.server-order1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.server-order1.jdbc-url=jdbc:mysql://192.168.100.201:3311/db_orderspring.shardingsphere.datasource.server-order1.username=rootspring.shardingsphere.datasource.server-order1.password=1234562.3、标椎分片表配置
#========================标准分片表配置（数据节点配置）# spring.shardingsphere.rules.sharding.tables..actual-data-nodes=值# 值由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。# ：逻辑表名spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodes=server-user.t_userspring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=server-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测试：保留上面配置中的一个分片表节点分别进行测试，检查每个分片节点是否可用
/** * 水平分片：插入数据测试 */@Testpublic 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..actual-data-nodes=值# 值由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。# ：逻辑表名spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodes=server-user.t_userspring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=server-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-column=user_id# 分片算法名称spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name=alg_inline_userid#------------------------分片算法配置# 行表达式分片算法# 分片算法类型spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.type=INLINE# 分片算法属性配置spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.props.algorithm-expression=server-order$->{user_id % 2}# 取模分片算法# 分片算法类型spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.type=MOD# 分片算法属性配置spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.props.sharding-count=2为了方便测试，先设置只在 t_order0表上进行测试
xxx.actual-data-nodes=server-order$->{0..1}.t_order0测试：可以分别测试行表达式分片算法和取模分片算法
/** * 水平分片：分库插入数据测试 */@Testpublic 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-column=order_no# 分片算法名称spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=alg_hash_mod#------------------------分片算法配置# 哈希取模分片算法# 分片算法类型spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.type=HASH_MOD# 分片算法属性配置spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.props.sharding-count=2测试前不要忘记将如下节点改回原来的状态
xxx.actual-data-nodes=server-order$->{0..1}.t_order$->{0..1}测试：
/** * 水平分片：分表插入数据测试 */@Testpublic 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);}}/** * 测试哈希取模 */@Testpublic void testHash(){//注意hash取模的结果是整个字符串hash后再取模，和数值后缀是奇数还是偶数无关System.out.println("gaogzhen001".hashCode() % 2);System.out.println("gaogzhen0011".hashCode() % 2);}查询测试：
/** * 水平分片：查询所有记录 * 查询了两个数据源，每个数据源中使用UNION ALL连接两个表 */@Testpublic void testShardingSelectAll(){List<Order> orders = orderMapper.selectList(null);orders.forEach(System.out::println);}/** * 水平分片：根据user_id查询记录 * 查询了一个数据源，每个数据源中使用UNION ALL连接两个表 */@Testpublic void testShardingSelectByUserId(){QueryWrapper<Order> orderQueryWrapper = new QueryWrapper<>();orderQueryWrapper.eq("user_id", 1L);List<Order> 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.column=id# 分布式序列算法名称spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=alg_snowflake# 分布式序列算法配置# 分布式序列算法类型spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.type=SNOWFLAKE# 分布式序列算法属性配置#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")@Datapublic 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;@Mapperpublic interface OrderItemMapper extends BaseMapper<OrderItem> {}3.4、配置关联表
t_order_item的分片表、分片策略、分布式序列策略和t_order一致
#------------------------标准分片表配置（数据节点配置）spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=server-order$->{0..1}.t_order_item$->{0..1}#------------------------分库策略# 分片列名称spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-column=user_id# 分片算法名称spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-algorithm-name=alg_mod#------------------------分表策略# 分片列名称spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-column=order_no# 分片算法名称spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-name=alg_hash_mod#------------------------分布式序列策略配置# 分布式序列列名称spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.column=id# 分布式序列算法名称spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.key-generator-name=alg_snowflake3.5、测试插入数据
同一个用户的订单表和订单详情表中的数据都在同一个数据源中，避免跨库关联
/** * 测试关联表插入 */@Testpublic 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;@Datapublic class OrderVo {private String orderNo;private BigDecimal amount;}4.2、添加Mapper方法
- OrderMapper.java
package com.gaogzhen.shardingjdbcdemo.mapper;@Mapperpublic interface OrderMapper extends BaseMapper<Order> {/** * 计算订单金额 * @return 订单金额列表 */List<OrderVO> getOrderAmount();}OrderMapper.xml
<select id="getOrderAmount" resultType="com.gaogzhen.shardingjdbcdemo.vo.OrderVO">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</select>
4.3、测试关联查询
/** * 测试关联表查询 */@Testpublic void testGetOrderAmount(){List<OrderVo> orderAmountList = orderMapper.getOrderAmount();orderAmountList.forEach(System.out::println);}查询结果
2023-08-23 20:10:40.015INFO 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_no2023-08-23 20:10:40.015INFO 27448 --- [ main] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)2023-08-23 20:10:40.015INFO 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.015INFO 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.015INFO 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.015INFO 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.015INFO 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.015INFO 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.015INFO 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.015INFO 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(orderNo=gaogzhen1, amount=40.00)OrderVO(orderNo=gaogzhen2, amount=40.00)OrderVO(orderNo=gaogzhen5, amount=6.00)OrderVO(orderNo=gaogzhen6, amount=6.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")@Datapublic class Dict {//可以使用MyBatisPlus的雪花算法@TableId(type = IdType.ASSIGN_ID)private Long id;private String dictType;}4.3.2、创建Mapper
package com.gaogzhen.shardingjdbcdemo.mapper;@Mapperpublic interface DictMapper extends BaseMapper<Dict> {}4.3.3、配置广播表
#数据节点可不配置，默认情况下，向所有数据源广播spring.shardingsphere.rules.sharding.tables.t_dict.actual-data-nodes=server-user.t_dict,server-order$->{0..1}.t_dict# 广播表spring.shardingsphere.rules.sharding.broadcast-tables[0]=t_dict4.4、测试广播表
@Autowiredprivate DictMapper dictMapper;/** * 广播表：每个服务器中的t_dict同时添加了新数据 */@Testpublic void testBroadcast(){Dict dict = new Dict();dict.setDictType("type1");dictMapper.insert(dict);}/** * 查询操作，只从一个节点获取数据 * 随机负载均衡规则 */@Testpublic void testSelectBroadcast(){List<Dict> dicts = dictMapper.selectList(null);dicts.forEach(System.out::println);}5 配置文件方式
- application.properties
#----------------------- 基础配置# 项目名称spring.application.name=sharding-jdbc-demospring.profiles.active=dev# shardingsphere 配置# 模式spring.shardingsphere.mode.type=Memory# 数据源名称spring.shardingsphere.datasource.names=server-user,server-order0,server-order1#------------------------ 数据源配置# 配置第1个数据源spring.shardingsphere.datasource.server-user.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.server-user.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.server-user.jdbc-url=jdbc:mysql://127.0.0.1:3301/db_user" />#----------------------- 基础配置# 项目名称spring.application.name=sharding-jdbc-demospring.profiles.active=dev# mybatis plus 配置mybatis.mapper-locations=classpath:mapper/*.xmlmybatis.type-aliases-package=com.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?allowPublicKeyRetrieval=true&useSSL=falseusername: rootpassword: 123456server-order0:type: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3310/db_order?allowPublicKeyRetrieval=true&useSSL=falseusername: rootpassword: 123456server-order1:type: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3311/db_order?allowPublicKeyRetrieval=true&useSSL=falseusername: 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 ApplicationContextCaused 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.NullPointerExceptionCaused 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?allowPublicKeyRetrieval=true&useSSL=falseusername: 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?allowPublicKeyRetrieval=true&useSSL=falseusername: rootpassword: 123456- dataSourceClassName替换为type
- jdbcUrl替换为url
6.4 could not determine a constructor for the tag !SHARDING:
- 报错内容
java.lang.IllegalStateException: Failed to load ApplicationContextCaused 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" />&useSSL=falseusername: 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?allowPublicKeyRetrieval=true&useSSL=falseusername: rootpassword: 123456- dataSources替换为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].