采用Sharding-JDBC实现分库分表+读写分离

最新推荐文章于 2024-10-30 15:24:18 发布

置顶范学博

最新推荐文章于 2024-10-30 15:24:18 发布

阅读量2.9k

点赞数

分类专栏：学习内容输出文章标签： mysql java sharding-jdbc

本文链接：https://blog.csdn.net/fanxb92/article/details/106968366

版权

学习内容输出专栏收录该内容

15 篇文章 1 订阅

订阅专栏

需求说明

1、基于user_id对c_order表进行数据分片。

2、分别对master1和master2搭建一主两从的架构。

3、基于master1和master2主从集群实现读写分离。

特殊说明：

本博客中主要讲解spring配置文件配置思路。分库分表+读写分离使用本地两个数据库来演示。

搭建主从环境请参见：https://blog.csdn.net/fanxb92/article/details/106961829

1、环境准备之机器说明

机器名称	IP配置	服务角色
master1	192.168.186.129	主服务器1
slave1	192.168.186.131	从服务器1
slave2	192.168.186.132	从服务器2
master2	192.168.186.130	主服务器2
slave3	192.168.186.133	从服务器3
slave4	192.168.186.134	从服务器4

2、环境准备之数据库和表

DROP TABLE IF EXISTS `c_order`;
CREATE TABLE `c_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删除',
  `user_id` int(11) NOT NULL COMMENT '用户id',
  `company_id` int(11) NOT NULL COMMENT '公司id',
  `publish_user_id` int(11) NOT NULL COMMENT 'B端用户id',
  `position_id` int(11) NOT NULL COMMENT '职位ID',
  `resume_type` int(2) NOT NULL DEFAULT '0' COMMENT '简历类型：0 附件 1 在线',
  `status` varchar(256) NOT NULL COMMENT '投递状态 WAIT-待处理 AUTO_FILTER-自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝 ARRANGE_INTERVIEW-通知面试',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '处理时间',
  PRIMARY KEY (`id`),
  KEY `index_userId_positionId` (`user_id`,`position_id`),
  KEY `idx_userId_operateTime` (`user_id`,`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3、环境准备之项目pom依赖

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <spring-cloud.version>Dalston.SR1</spring-cloud.version>
        <spring-boot.version>2.2.5.RELEASE</spring-boot.version>
        <mysql-connector.version>5.1.48</mysql-connector.version>
        <shardingsphere.version>4.1.0</shardingsphere.version>
    </properties>

    <dependencies>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>${spring-boot.version}</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>${spring-boot.version}</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring-boot.version}</version>
            <scope>test</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <testSource>1.8</testSource>
                    <testTarget>1.8</testTarget>
                </configuration>
            </plugin>
        </plugins>
    </build>

4、环境准备之配置文件

#datasource ds0,ds3主数据库（搭建），其他是从数据库
spring.shardingsphere.datasource.names=master0,slave1,slave2,master1,slave3,slave4

spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://192.168.186.129:3306/mamba
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=root

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.186.131:3306/mamba
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root

spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.186.132:3306/mamba
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=root

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.186.130:3306/mamba
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root

spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://192.168.186.133:3306/mamba
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root

spring.shardingsphere.datasource.slave4.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave4.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave4.jdbc-url=jdbc:mysql://192.168.186.134:3306/mamba
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.password=root

#id
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE

#sharding-database-table    基于user_id对c_order表进⾏数据分⽚
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$->{user_id % 2}

spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master$->{1..2}.c_order

#master-slave   基于master1和master2主从集群实现读写分离
spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave1,slave2
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave3,slave4
#多个从库的时候使用负载均衡
spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN

5、测试验证，写操作连接主库，读操作连接从库。参见演示视频。

@Entity
@Table(name = "c_order")
public class COrder implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "is_del")
    private Boolean isDel;

    @Column(name = "user_id")
    private Integer userId;

    @Column(name = "company_id")
    private Integer companyId;

    @Column(name = "publish_user_id")
    private Integer publishUserId;

    @Column(name = "position_id")
    private Integer positionId;

    @Column(name = "resume_type")
    private Integer resumeType;

    @Column(name = "status")
    private String status;

    @Column(name = "create_time")
    private Date createTime;

    @Column(name = "update_time")
    private Date updateTime;

public interface COrderRepository extends JpaRepository<COrder, Long> {
}

@SpringBootApplication
public class ShardingJdbcApplication {
}

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingJdbcApplication.class)
public class TestShardingJdbc {

    @Autowired
    private COrderRepository cOrderRepository;

    @Test
    @Repeat(20)
    public void testAdd() {
        COrder cOrder = new COrder();
        cOrder.setIsDel(false);
        cOrder.setUserId(new Random().nextInt(10));
        cOrder.setCompanyId(new Random().nextInt(10));
        cOrder.setPublishUserId(new Random().nextInt(10));
        cOrder.setPositionId(new Random().nextInt(10));
        cOrder.setResumeType(new Random().nextInt(1));
        cOrder.setStatus("ARRANGE_INTERVIEW");
        cOrder.setCreateTime(new Date());
        cOrder.setUpdateTime(new Date());
        cOrderRepository.saveAndFlush(cOrder);
    }

    @Test
    public void testFind() {
        List<COrder> cOrderList = cOrderRepository.findAll();
        cOrderList.forEach(cOrder -> {
            System.out.println(cOrder.toString());
        });
    }
}