shardingsphere实现分库分表

傷城~ 2023-06-27 05:43 51阅读 0赞

## 实验环境 ##

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>
     
            
    <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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70][]

### 第三步：验证 ###

通过代码插入多条数据

age=1

age=2

age=3

age=4

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200103180910936.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2021051223025884.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA=,size_16,color_FFFFFF,t_70