shardingsphere实现分库分表

实验环境

springboot版本：2.2.2.RELEASE

shardingsphere版本：4.0.0-RC3

实验说明：

数据库：demo_ds_0，demo_ds_1

表user,分别在demo_ds_0，demo_ds_1各自建两张user表，user_1和user_2，两个库共四个表

user表结构如下，自行改表名在两个库各执行一次即可。

CREATE TABLE `user` (
  `id` bigint(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `uid` varchar(255) DEFAULT NULL,
  `school` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

第一步：引入shardingsphere依赖

 <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC3</version>
</dependency>
 
        <!-- for spring namespace -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.0.0-RC3</version>
</dependency>

 

第二步：application.properties配置

#读写分离配置，即一主多从方式配置
debug=false
server.port=8081
server.servlet.context-path=
spring.jackson.serialization.INDENT_OUTPUT=true
spring.profiles.active=

spring.shardingsphere.props.sql.show=true

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://youraddress:3306/demo_ds_0
spring.shardingsphere.datasource.ds0.username=
spring.shardingsphere.datasource.ds0.password=

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://youraddress:3306/demo_ds_1
spring.shardingsphere.datasource.ds1.username=
spring.shardingsphere.datasource.ds1.password=

spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}


spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user_$->{1..2},ds1.user_$->{1..2}
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{age % 2+1}
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.user.key-generator.props.worker.id=123

 

第三步：验证

通过代码插入多条数据

age=1

age=1

age=2

age=2

age=3

age=3

age=4

age=4

发现数据被分布到四个表中，验证完毕。注意，因为是按id分库的，id是用雪花算法得出来的，所以是趋势递增，但是不能保证奇数偶数的顺序，所以插入数据的时候，age相同的值，插了多条才能看到效果。