放在自己博客里搬过来一份~
前司使用的是自己魔改的TDDL,在家时间比较多就尝试学一些业内比较常用的中间件.
这里记录一下学习中遇到的一些问题.
设置的比较简单(太懒了就测试了几个表), 两个分库, 各有几张分表.
sharding-test_0
- order_0 (order_id)
- order_1
- order_item_0 (order_id)
- order_item_1
- user_0 (user_id)
- user_1
- address (用来做broadcast表)
CREATE TABLE `order_0` (
`order_id` int NOT NULL,
`user_id` int NOT NULL,
`address_id` int NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `order_item_0` (
`order_item_id` bigint NOT NULL,
`order_id` int NOT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `user_0` (
`user_id` bigint NOT NULL,
`user_name` varchar(45) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `address` (
`address_id` int NOT NULL,
`address_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
spring-boot-starter使用2.7.5
shardingsphere-jdbc-core-spring-boot-starter使用5.2.1
测试的时候最好直接跑,不要用单测,会被自动回滚掉.
可以定义多个ApplicationRunner来测试.
@Component
public class MyApplicationRunner implements ApplicationRunner {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void run(final ApplicationArguments args) throws Exception {
用JdbcTemplate方便点也省去了依赖更多的东西.
返回自增key的代码样例:
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
PreparedStatement ps = connection.prepareStatement("insert into user(`user_name`) values (?)",
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "cc2");
return ps;
}, keyHolder);
System.out.println("key:" + keyHolder.getKey());
配置
本来使用yaml的配置,但看了一下有点太乱,先用properties的代替.
配的时候有比较多的问题,几个配置错误会导致没法启动或者测试时报错,但配完之后感觉整体逻辑还是比较清晰的.
spring.shardingsphere.mode.type=Standalone
spring.shardingsphere.props.sql-show=true
# logic datasource
spring.shardingsphere.datasource.names=shard00,shard01
# real datasource
spring.shardingsphere.datasource.shard00.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.shard00.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.shard00.jdbc-url=jdbc:mysql://localhost:3306/sharding_test_0
spring.shardingsphere.datasource.shard00.username=root
spring.shardingsphere.datasource.shard00.password=
spring.shardingsphere.datasource.shard01.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.shard01.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.shard01.jdbc-url=jdbc:mysql://localhost:3306/sharding_test_1
spring.shardingsphere.datasource.shard01.username=root
spring.shardingsphere.datasource.shard01.password=
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=\
shard0$->{0..1}.user_$->{0..1}
spring.shardingsphere.rules.sharding.tables.order_item.actual-data-nodes=\
shard0$->{0..1}.order_item_$->{0..1}
spring.shardingsphere.rules.sharding.tables.order.actual-data-nodes=\
shard0$->{0..1}.order_$->{0..1}
spring.shardingsphere.rules.sharding.tables.address.actual-data-nodes=\
shard0$->{0..1}.address
# database strategy and table strategy
spring.shardingsphere.rules.sharding.tables.user.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.user.database-strategy.standard.sharding-algorithm-name=alg_db_user
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_user.type=MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_user.props.sharding-count=2
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=alg_table_user
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_user.type=HASH_MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_user.props.sharding-count=2
spring.shardingsphere.rules.sharding.tables.order.database-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order.database-strategy.standard.sharding-algorithm-name=alg_db_order
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_order.type=MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_order.props.sharding-count=2
spring.shardingsphere.rules.sharding.tables.order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order.table-strategy.standard.sharding-algorithm-name=alg_table_order
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_order.type=HASH_MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_order.props.sharding-count=2
# order_item and order use the same strategy
spring.shardingsphere.rules.sharding.tables.order_item.database-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order_item.database-strategy.standard.sharding-algorithm-name=alg_db_order
spring.shardingsphere.rules.sharding.tables.order_item.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order_item.table-strategy.standard.sharding-algorithm-name=alg_table_order
# key generator
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=user_id
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=alg_snowflake
spring.shardingsphere.rules.sharding.tables.order.key-generate-strategy.column=order_id
spring.shardingsphere.rules.sharding.tables.order.key-generate-strategy.key-generator-name=alg_snowflake
spring.shardingsphere.rules.sharding.tables.order_item.key-generate-strategy.column=order_item_id
spring.shardingsphere.rules.sharding.tables.order_item.key-generate-strategy.key-generator-name=alg_snowflake
# key generator algorithm
spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.alg_uuid.type=UUID
# binding table and broadcast table
spring.shardingsphere.rules.sharding.binding-tables[0]=order,order_item
spring.shardingsphere.rules.sharding.broadcast-tables=address
也就分为逻辑datasource定义, 真实的datasource定义.
对于每个逻辑表,定义分库分表规则,如果需要生成分布式key,定义key的生成算法.
分别对应spring.shardingsphere.datasource.前缀和spring.shardingsphere.rules.sharding前缀.
对于SNOWFLAKE要注意数据库的字段类型要bigint,int不够放.
启动报错
***************************
APPLICATION FAILED TO START
***************************
Description:
An attempt was made to call a method that does not exist. The attempt was made from the following location:
org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.<init>(ShardingSphereYamlConstructor.java:44)
The following method did not exist:
'void org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.setCodePointLimit(int)'
The calling methods class, org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1, was loaded from the following location:
jar:file:/.m2/repository/org/apache/shardingsphere/shardingsphere-infra-util/5.2.1/shardingsphere-infra-util-5.2.1.jar!/org/apache/shardingsphere/infra/util/yaml/constructor/ShardingSphereYamlConstructor$1.class
The called methods class, org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1, is available from the following locations:
jar:file:/.m2/repository/org/apache/shardingsphere/shardingsphere-infra-util/5.2.1/shardingsphere-infra-util-5.2.1.jar!/org/apache/shardingsphere/infra/util/yaml/constructor/ShardingSphereYamlConstructor$1.class
The called methods class hierarchy was loaded from the following locations:
null: file:/.m2/repository/org/apache/shardingsphere/shardingsphere-infra-util/5.2.1/shardingsphere-infra-util-5.2.1.jar
org.yaml.snakeyaml.LoaderOptions: file:/.m2/repository/org/yaml/snakeyaml/1.30/snakeyaml-1.30.jar
Action:
Correct the classpath of your application so that it contains a single, compatible version of org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1
很明显的一个以来冲突问题, 主要是这行代码:
public ShardingSphereYamlConstructor(final Class<?> rootClass) {
super(rootClass, new LoaderOptions() {
{
setCodePointLimit(Integer.MAX_VALUE);
}
});
ShardingSphereYamlConstructFactory.getInstances().forEach(each -> typeConstructs.put(each.getType(), each));
ShardingSphereYamlShortcutsFactory.getAllYamlShortcuts().forEach((key, value) -> addTypeDescription(new TypeDescription(value, key)));
this.rootClass = rootClass;
}
snakeyaml的版本冲突,使用的版本中LoaderOptions没有setCodePointLimit这个方法.
使用的springboot的依赖的是1.30.0,显式依赖1.33.0即可.
<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.33</version>
</dependency>
配置错误导致的报错
这类报错种类比较多
比如
DataNodesMissedWithShardingTableExceptionShardingRuleNotFoundExceptionInconsistentShardingTableMetaDataException
等等, 启动就会失败, 因为是读取了配置解析异常.
这种就要看看对应的错误和配置.
不过有点奇怪的是一些错误没有打出详细的报错信息.比如:
Caused by: org.apache.shardingsphere.sharding.exception.metadata.DataNodesMissedWithShardingTableException: null
at org.apache.shardingsphere.sharding.rule.TableRule.lambda$checkRule$4(TableRule.java:246) ~[shardingsphere-sharding-core-5.2.1.jar:5.2.1]
at org.apache.shardingsphere.infra.util.exception.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41) ~[shardingsphere-infra-util-5.2.1.jar:5.2.1]
at org.apache.shardingsphere.sharding.rule.TableRule.checkRule(TableRule.java:245) ~[shardingsphere-sharding-core-5.2.1.jar:5.2.1]
看了下是基类没调用super,导致message没有值.看了下这个已经在master分支修好了:
public ShardingSphereSQLException(final SQLState sqlState, final int typeOffset, final int errorCode, final String reason, final Object... messageArguments) {
this(sqlState.getValue(), typeOffset, errorCode, reason, messageArguments);
}
public ShardingSphereSQLException(final String sqlState, final int typeOffset, final int errorCode, final String reason, final Object... messageArguments) {
this.sqlState = sqlState;
vendorCode = typeOffset * 10000 + errorCode;
this.reason = null == reason ? null : String.format(reason, messageArguments);
// missing super(resaon) here
}
数据库自动生成的key不能作为route key
但是分布式生成的key可以, 这个在FAQ里有, 有这个错误是刚开始配分布式key的时候配错了.
[分片] ShardingSphere 除了支持自带的分布式自增主键之外，还能否支持原生的自增主键？
回答：是的，可以支持。但原生自增主键有使用限制，即不能将原生自增主键同时作为分片键使用。 由于 ShardingSphere 并不知晓数据库的表结构，而原生自增主键是不包含在原始 SQL 中内的，因此 ShardingSphere 无法将该字段解析为分片字段。如自增主键非分片键，则无需关注，可正常返回；若自增主键同时作为分片键使用，ShardingSphere 无法解析其分片值，导致 SQL 路由至多张表，从而影响应用的正确性。 而原生自增主键返回的前提条件是 INSERT SQL 必须最终路由至一张表，因此，面对返回多表的 INSERT SQL，自增主键则会返回零。
分表分库的规则思考
最开始的时候对于分库分表无脑两个都用了MOD, 但因为分区数和分表数是一样的(都是2).
所以mod 2数据的分布也是一样的,这就导致了sharding_test_0的user_1是没有数据的,sharding_test_1的user_0也是没有数据的.
分表了个寂寞.
不只是一样,其实只要分库和分表数最大公约数不为1如果无脑MOD都会有倾斜的问题.
可以代码验证下:
int dbShard = 6;
int tableShard = 32;
Map<Tuple2<Integer, Integer>, Integer> count = new TreeMap<>();
for (int i = 0; i < dbShard; i++) {
for (int j = 0; j < tableShard; j++) {
count.put(Tuple.tuple(i, j), 0);
}
}
for (int i = 0; i < 100000; i++) {
count.computeIfPresent(Tuple.tuple(i % dbShard, i % tableShard), (k, v) -> v + 1);
}
count.forEach((k,v) -> {
System.out.println(k + ":" + v);
});
因为前司我经手的项目用的都是分表,还没有到分库,没有意识到这个问题,也算是一点点小经验吧,要考虑下分库分表的规则组合会不会导致数据倾斜.
其他还有些实践中的问题,当时没有记录把配置整对之后也不知道怎么复现了.
不得不说shardingsphere-jdbc的易用性是非常高了,通俗易懂.
参考
shardingsphere官网: https://shardingsphere.apache.org
shardingsphere-jdbc配置: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/
shardingsphere FAQ: https://shardingsphere.apache.org/document/current/cn/faq/
How to get generated ID after I inserted into a new data record in database using Spring JDBCTemplate?
github page的博客原文:https://bingowith.me/2022/11/05/shardingsphere-jdbc-learn-note/