springboot使用⽤Sharding-JDBC实现c_order表分库分表+读写分离

最新推荐文章于 2024-02-18 17:32:11 发布

喵喵拳法

最新推荐文章于 2024-02-18 17:32:11 发布

阅读量1.5k

点赞数 1

分类专栏： MYSQL LAGOU shardingsphere 文章标签：数据库 java sharding spring boot

本文链接：https://blog.csdn.net/qq_29308413/article/details/108175924

版权

MYSQL 同时被 3 个专栏收录

5 篇文章 0 订阅

订阅专栏

LAGOU

2 篇文章 0 订阅

订阅专栏

shardingsphere

1 篇文章 0 订阅

订阅专栏

前言

本博客为拉钩java高薪训练营4-2MySQL海量数据存储与优化下的解题答案；

第一部分作业要求

1 基于user_id对c_order表进⾏数据分⽚；

2 分别对master1和master2搭建⼀主⼆从架构；

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

4 c_order建表SQL如下：

CREATE TABLE `c_order`(
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `is_del` bit(1) NOT NULL DEFAULT 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;

第二部分解题思路

根据作业要求，我们需要使用shardingsphere完成分库分表和读写分离功能；我们需要分别实现3个功能

1 mysql主从复制集群；

2 存储数据使用shardingsphere通过mysql主库并实现分库分表；

3 读取数据使用shardingsphere通过mysql从库；

4 使用springboot集成shardingsphere，通过代码进行存储和读取。

第三部分解题

3.1 搭建mysql主从集群

网络拓扑结构

master0   192.168.126.128
slave1    192.168.126.129
slave2    192.168.126.130



master1   192.168.126.134
slave3    192.168.126.132
slave4    192.168.126.133

搭建的主要过程可参考上一篇博客

https://mp.csdn.net/console/editor/html/107966208

3.2 数据库分表配置

在master0和master1数据库中分别创建c_order0、c_order1、c_order2，sql脚本如下：

CREATE TABLE `c_order0`(
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `is_del` bit(1) NOT NULL DEFAULT 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;



CREATE TABLE `c_order1`(
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `is_del` bit(1) NOT NULL DEFAULT 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;


CREATE TABLE `c_order2`(
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `is_del` bit(1) NOT NULL DEFAULT 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;

创建完成后，结果如下

从库通过mysql主从复制机制，实现表的复制。

3.3 springboot 集成 shardingshphere

3.3.1 引入jar

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.lagou</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>sharding-jdbc-master-slave</module>
    </modules>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.compile.sourceEncoding>UTF-8</project.compile.sourceEncoding>
        <shardingsphere.version>4.1.0</shardingsphere.version>
        <springboot.version>2.2.5.RELEASE</springboot.version>
    </properties>
    <dependencyManagement>

        <dependencies>

            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
                <version>${springboot.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
                <version>${springboot.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <version>${springboot.version}</version>
                <scope>test</scope>
            </dependency>

            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.48</version>
            </dependency>

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

        </dependencies>

    </dependencyManagement>

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

</project>

3.3.2 application.properties

#配置数据源
spring.shardingsphere.datasource.names=master0,slave1,slave2,master1,slave4,slave3
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.126.128:3306/master0?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.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.126.129:3306/master0?useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.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.126.130:3306/master0?useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.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.126.134:3306/master1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.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.126.133:3306/master1?useSSL=false
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.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.126.132:3306/master1?useSSL=false
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root

#分库分表
#通过user_id 分库
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}
#通过id 分表
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master$->{0..1}.c_order$->{0..2}
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order$->{id % 3}

#id 增长策略
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=LAGOUKEY

#读写分离 =====  》master0 对应从库slave1和slave2    master1  对应从库slave3和slave4
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

3.3.3 主要类作用说明

COrder                                      订单表对应的实体类；

COrderRepository                            订单表对应的dao;

MyLagouId                                   自定义主键增长策略；

TestMasterSlave                              分库分表和读写分离的测试类；

第四部分效果验证

4.1 分库分表的测试

运行TestMasterSlave中的testAddCorder方法。通过@Repeat(100)注解，每次添加100条数据至主库master0和master1，分表通过userid取模，分库通过id取模

user_id取模为0的均存储于master0中，取模为1的均存储在master1中。id取模为0存放于c_order0,id取模为1存放于c_order1,id取模为2存放于c_order2;

4.2 读写分离的测试

运行TestMasterSlave中的testFind方法。对c_order订单表中的数据进行查询。

所有的查询均通过从库slave1、slave2、slave3和slave4..

代码可见https://gitee.com/yulanzhilian.com/lagouphasefourmoduletwocode

喵喵拳法

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
springboot使用⽤Sharding-JDBC实现c_order表分库分表+读写分离

前言本博客为拉钩java高薪训练营4-2MySQL海量数据存储与优化下的解题答案；第一部分作业要求1 基于user_id对c_order表进⾏数据分⽚；2分别对master1和master2搭建⼀主⼆从架构；3基于master1和master2主从集群实现读写分离；4c_order建表SQL如下：CREATE TABLE `c_order`( `id` bigint(20) NOT NULL AUTO_INCREMENT, `is_del` bit(1) NOT...
复制链接

扫一扫