放在自己部落格里搬過來一份~
前司使用的是自己魔改的TDDL,在家時間比較多就嘗試學一些業內比較常用的中介軟體.
這裡記錄一下學習中遇到的一些問題.
設定的比較簡單(太懶了就測試了幾個表), 兩個分庫, 各有幾張分表.
sharding-test_0
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可以, 這個在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/