ç®å½
1ãååºå表å®ä¹
2ãååºå表æ¡ä¾
3ã读åå离æ¡ä¾
4ãå ¶å®é®é¢
å¨éå°æ°æ®é大ãæ§è½ç¶é¢çæ¶åï¼ååºå表å读åå离è½å¤å¾å¥½è§£å³æ¤ç±»é®é¢ã
Sharding-JDBCæ¯ShardingSphereçæåç产åä¹ä¸ã
ShardingSphereæ¯ä¸å¥å¼æºçåå¸å¼æ°æ®åºä¸é´ä»¶è§£å³æ¹æ¡ç»æççæåï¼å®ç±Sharding-JDBCãSharding-ProxyåSharding-Sidecarï¼è®¡åä¸ï¼è¿3款ç¸äºç¬ç«ç产åç»æã ä»ä»¬åæä¾æ ååçæ°æ®åçãåå¸å¼äºå¡åæ°æ®åºæ²»çåè½ï¼å¯éç¨äºå¦Javaåæãå¼æè¯è¨ã容å¨ãäºåççåç§å¤æ ·åçåºç¨åºæ¯ã
æ¬æåèå®æ¹ææ¡£ï¼https://shardingsphere.apache.org/document/legacy/3.x/document/cn/overview/
1ãååºå表å®ä¹
ååºå表å æ¬ååºåå表两个é¨åï¼å¨ç产ä¸é常å æ¬ï¼åç´(纵å)å表ãåç´(纵å)ååºãæ°´å¹³ååºãæ°´å¹³å表åç§æ¹å¼ã
å¾å¥½ç解ï¼åç´å°±æ¯æåï¼æ°´å¹³å°±æ¯æ©å±ã
- åç´å表ï¼å¯ä»¥æä¸ä¸ªå®½è¡¨çå段æ访é®é¢æ¬¡ãæ¯å¦æ¯å¤§å段çååæå为å¤ä¸ªè¡¨ï¼è¿æ ·æ¢è½ä½¿ä¸å¡æ¸ æ°ï¼è¿è½æåé¨åæ§è½ãæååï¼å°½éä»ä¸å¡è§åº¦é¿å èæ¥ï¼å¦åæ§è½æ¹é¢å°å¾ä¸å¿å¤±ã
- åç´ååºï¼å¯ä»¥æå¤ä¸ªè¡¨æä¸å¡è¦åæ¾ç´§å½ç±»ï¼åå«åæ¾å¨ä¸åçåºï¼è¿äºåºå¯ä»¥åå¸å¨ä¸åæå¡å¨ï¼ä»è使访é®åå被å¤æå¡å¨è´è½½ï¼å¤§å¤§æåæ§è½ï¼åæ¶è½æé«æ´ä½æ¶æçä¸å¡æ¸ æ°åº¦ï¼ä¸åçä¸å¡åºå¯æ ¹æ®èªèº«æ åµå®å¶ä¼åæ¹æ¡ãä½æ¯å®éè¦è§£å³è·¨åºå¸¦æ¥çææå¤æé®é¢ã
- æ°´å¹³ååºï¼å¯ä»¥æä¸ä¸ªè¡¨çæ°æ®(ææ°æ®è¡)åå°å¤ä¸ªä¸åçåºï¼æ¯ä¸ªåºåªæè¿ä¸ªè¡¨çé¨åæ°æ®ï¼è¿äºåºå¯ä»¥åå¸å¨ä¸åæå¡å¨ï¼ä»è使访é®åå被å¤æå¡å¨è´è½½ï¼å¤§å¤§æåæ§è½ãå®ä¸ä» éè¦è§£å³è·¨åºå¸¦æ¥çææå¤æé®é¢ï¼è¿è¦è§£å³æ°æ®è·¯ç±çé®é¢ã
- æ°´å¹³å表ï¼å¯ä»¥æä¸ä¸ªè¡¨çæ°æ®(ææ°æ®è¡)åå°å¤ä¸ªåä¸ä¸ªæ°æ®åºçå¤å¼ 表ä¸ï¼æ¯ä¸ªè¡¨åªæè¿ä¸ªè¡¨çé¨åæ°æ®ï¼è¿æ ·åè½å°å¹ æåæ§è½ï¼å®ä» ä» ä½ä¸ºæ°´å¹³ååºçä¸ä¸ªè¡¥å ä¼åã
2ãååºå表æ¡ä¾
ç°å¨æä¸å¼ ç¨æ·è¡¨t_userï¼æç®éç¨æ°´å¹³ååºå表ãæ ¹æ®æ°æ®éå访é®éï¼ææ4å¼ è¡¨ï¼å¹³ååå°2个åºä¸ã以èªå¢çuser_idå模è¿è¡è·¯ç±ã
ï¼1ï¼ç®æ³åæï¼åå®10æ¡æ°æ®ï¼user_idæ¯1~10ã
- é¦å æ¯å¹³ååå°2个åºï¼user_id%2ç»ææ¯0æ1ï¼å°±å½å两个åºï¼å ¶å®æ¯æ°æ®æºï¼ä¸ºds0ãds1ï¼ds0åçè®°å½user_id为2ã4ã6ã8ã10ï¼ds1åçè®°å½user_id为1ã3ã5ã7ã9ã
- ç¶åæ¯æ¯ä¸ªåºå¹³ååå°2å¼ è¡¨ï¼æä¹åå¢ï¼user_id%4ï¼ds0çç»ææ¯0æ2ï¼é£ä¹ä¸¤å¼ 表å¯ä»¥å½å为t_user0åt_user2ï¼ds1çç»ææ¯1æ3ï¼é£ä¹ä¸¤å¼ 表å¯ä»¥å½å为t_user1åt_user3ã
- 综ä¸ï¼ååºéç¨user_id%2(2个åº)ï¼å表éç¨user_id%4(4å¼ è¡¨)ãæç»åå¨æ åµæ¯ï¼4ã8åå°ds0.t_user0ï¼2ã6ã10åå°ds0.t_user2ï¼1ã5ã9åå°ds1.t_user1ï¼3ã7åå°ds1.t_user3ã
æ®æ¤ï¼æ们建ç«2个åºå4å¼ è¡¨ï¼ds0.t_user0ãds0.t_user2ãds1.t_user1ãds1.t_user3ã示ä¾å¦ä¸ï¼ä¸ç¨çº ç»æ¯å¦è§èï¼
CREATE TABLE `t_user0` (
`user_id` bigint unsigned NOT NULL COMMENT '主é®',
`name` varchar(64) NOT NULL COMMENT 'å§å',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='ç¨æ·è¡¨'
ï¼2ï¼æ°å»ºSpring Boot项ç®ï¼å¼å ¥ä¾èµ
<!-- for spring boot -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
ï¼3ï¼é ç½®properties.yml
sharding:
jdbc:
datasource:
names: ds0,ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://ip:port/ds0
username: root
password: 123456
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://ip:port/ds1
username: root
password: 123456
config:
sharding:
# æ°æ®åç
default-database-strategy:
inline:
sharding-column: user_id
algorithm-expression: ds$->{user_id % 2}
tables:
t_user:
actual-data-nodes: ds$->{0..1}.t_user$->{0..3}
key-generator-column-name: user_id
# å
ç½®ï¼SNOWFLAKEãUUID
key-generator-class-name: cn.zhh.keygen.AutoIncrementKeyGenerator
table-strategy:
inline:
sharding-column: user_id
algorithm-expression: t_user$->{user_id % (2 * 2)}
å ¶ä¸ç主é®çæå¨ï¼æ们为äºæµè¯æ¹ä¾¿ï¼å°±é便åäºä¸ä¸ªï¼å®é è¯å®ä¸è½è¿ä¹å¹²ï¼å¾éç¨åå¸å¼IDæ¹æ¡ï¼ã
package cn.zhh.keygen;
import io.shardingsphere.core.keygen.KeyGenerator;
import java.util.concurrent.atomic.AtomicLong;
/**
* èªå¢ä¸»é®çæå¨
*/
public class AutoIncrementKeyGenerator implements KeyGenerator {
private static final AtomicLong KEY_GENERATOR = new AtomicLong(1L);
@Override
public Number generateKey() {
return KEY_GENERATOR.getAndIncrement();
}
}
ï¼4ï¼æµè¯
æ们ç´æ¥ç¨JdbcTemplateæä½ï¼æå ¥10æ¡è®°å½ï¼èªå¨çæå®ä»¬çuser_id为1~10ã
@Test
public void testInsert() {
for (int i = 0; i < 10; i++) {
String sql = String.format("insert into t_user (name) values ('%s')", UUID.randomUUID().toString());
jdbcTemplate.execute(sql);
}
}
ç»æå¦æææï¼æ¯å¦ds0.t_user0çæ°æ®
3ã读åå离æ¡ä¾
æ们设å®slave0æ¯ds0çä»åºï¼slave1æ¯ds1çä»åºã人æ+èçæ¶é´ï¼å°±ä¸æ建çæ£ç主ä»äºï¼ç´æ¥å©ç¨Navicatçæ°æ®ä¼ è¾å·¥å ·æds0åds1çç»æåæ°æ®åå«åæ¥å°slave0åslave1ï¼å°±ç®é 置好äº#_#ã
ä¿®æ¹ä¸properties.ymlçé ç½®
sharding:
jdbc:
datasource:
names: ds0,ds1,slave0,slave1
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://ip:port/ds0
username: root
password: 123456
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://ip:port/ds1
username: root
password: 123456
slave0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://ip:port/slave0
username: root
password: 123456
slave1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://ip:port/slave1
username: root
password: 123456
config:
sharding:
# æ°æ®åç
default-database-strategy:
inline:
sharding-column: user_id
algorithm-expression: ds$->{user_id % 2}
tables:
t_user:
actual-data-nodes: ds$->{0..1}.t_user$->{0..3}
key-generator-column-name: user_id
# å
ç½®ï¼SNOWFLAKEãUUID
key-generator-class-name: cn.zhh.keygen.AutoIncrementKeyGenerator
table-strategy:
inline:
sharding-column: user_id
algorithm-expression: t_user$->{user_id % (2 * 2)}
# 读åå离
master-slave-rules:
ds0:
master-data-source-name: ds0
slave-data-source-names: slave0
ds1:
master-data-source-name: ds1
slave-data-source-names: slave1
为äºçåºè¯»åªä¸ªåºï¼æ们æ¹ä¸ä»åºéé¢è¡¨æ°æ®çnameå¼
update slave0.t_user0 set name = 'slave0.t_user0';
update slave0.t_user2 set name = 'slave0.t_user2';
update slave1.t_user1 set name = 'slave1.t_user1';
update slave1.t_user3 set name = 'slave1.t_user3';
æµè¯ä¸ä¸
@Test
public void testSelect() {
for (int i = 0; i < 10; i++) {
int userId = i + 1;
String sql = String.format("select name from t_user where user_id = %s", userId);
String name = jdbcTemplate.queryForObject(sql, String.class);
System.out.printf("userId: %s, name: %s%n", userId, name);
}
}
ç»ææ£å¸¸
4ãå ¶å®é®é¢
éç¨äºååºå表ï¼é¾å ä¼éå°ä¸äºé®é¢ãè¿éç¨å¾®å举ä¸ä¸
ï¼1ï¼joinãorder byãlimitï¼å°½éé¿å / 代ç å±é¢å¤ç / æäºå·¥å ·æ¯æã
ï¼2ï¼åå¸å¼äºå¡ï¼å¼ºä¸è´æ§å ç§æ¹æ¡Â / èèæç»ä¸è´æ§ / æäºå·¥å ·æ¯æã
ï¼3ï¼å¯ä¸ID
- éªè±ç®æ³ï¼Sharding-JDBCå ç½®ï¼
- UUIDï¼Sharding-JDBCå ç½®ï¼
- Redisçæ
- ä¸é¨ä¸ä¸ªæ°æ®åºçèªå¢çæ
ï¼4ï¼å ¶å®æ¡ä»¶æ¥è¯¢
- æ å°æ³ï¼æ¯å¦t_useréè¦æ ¹æ®ææºå·ç æ¥è¯¢ï¼å¯ä»¥å»ºç«ä¸å¼ user_idä¸mobileçæ å°è¡¨ã
- åä½æ³ï¼æ¯å¦t_useréè¦æ¯ä¸ªæ注åçç¨æ·ï¼å¯ä»¥åä½ä¸ä»½ä»¥æ份路ç±çæ°æ®ã
- NoSQLä¸é´ä»¶ï¼å ¨éæ°æ®åæ¥ESçã
æç« æ¡ä¾ä»£ç å·²ä¸ä¼ è³githubï¼https://github.com/zhouhuanghua/sharding-jdbc-demo.gitã