案例：SpringBoot集成Sharding-JDBC实现分表分库与主从同步(详细版)

04-23 4469阅读 0评论

案例：SpringBoot集成Sharding-JDBC实现分表分库与主从同步：详细版

  • 1. 案例分析
  • 2. 主从同步
    • 2.1 主从数据库准备
    • 2.2 简单插点数据
    • 3 案例代码
      • 3.1 application.properties配置信息
      • 3.2 测试
      • 4. 遇到的坑
        • 4.1 水平分表时的属性设置
        • 4.2 绑定表的配置

          1. 案例分析

          表结构：

          案例：SpringBoot集成Sharding-JDBC实现分表分库与主从同步(详细版) 第1张

          垂直分库：STORE_DB与PRODUCT_DB

          垂直分表：商品表分为：商品信息与商品描述表

          水平分库：PRODUCT_DB分为PRODUCT_DB_1和PRODUCT_DB_2

          水平分表：商品信息与商品描述表1和商品信息与商品描述表2

          案例：SpringBoot集成Sharding-JDBC实现分表分库与主从同步(详细版) 第2张

          2. 主从同步

          2.1 主从数据库准备

          案例：SpringBoot集成Sharding-JDBC实现分表分库与主从同步(详细版) 第3张

          主库与从库保持一致，本文案例主要涉及到三个数据库：store_db、product_db_1、product_db_2

          案例：SpringBoot集成Sharding-JDBC实现分表分库与主从同步(详细版) 第4张

          具体MySQL的主从同步配置，请看我之前的文章。Mysql8.0以上的版本实现主从同步

          数据库store_db中的表创建：

          DROP TABLE IF EXISTS `region`;
CREATE TABLE `region` (
`id` BIGINT(20) NOT NULL COMMENT 'id',
`region_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT
'地理区域编码',
`region_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
COMMENT '地理区域名称',
`level` TINYINT(1) NULL DEFAULT NULL COMMENT '地理区域级别(省、市、县)',
`parent_region_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
COMMENT '上级地理区域编码',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `store_info`;
CREATE TABLE `store_info` (
  `id` bigint NOT NULL COMMENT 'id',
  `store_name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '店铺名称',
  `reputation` int DEFAULT NULL COMMENT '信誉等级',
  `region_code` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '店铺所在地',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

          product_db_1和product_db_2结构一样，用于水平分库：

          DROP TABLE IF EXISTS `product_descript_1`;
CREATE TABLE `product_descript_1` (
`id` BIGINT(20) NOT NULL COMMENT 'id',
`product_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属商品id',
`descript` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `FK_Reference_2`(`product_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `product_descript_2`;
CREATE TABLE `product_descript_2` (
`id` BIGINT(20) NOT NULL COMMENT 'id',
`product_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属商品id',
`descript` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `FK_Reference_2`(`product_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `product_info_1`;
CREATE TABLE `product_info_1` (
`product_info_id` BIGINT(20) NOT NULL COMMENT 'id',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
`product_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
COMMENT '商品名称',
`spec` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '规
格',
`region_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT
'产地',
`price` DECIMAL(10, 0) NULL DEFAULT NULL COMMENT '商品价格',
`image_url` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT
'商品图片',
PRIMARY KEY (`product_info_id`) USING BTREE,
INDEX `FK_Reference_1`(`store_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `product_info_2`;
CREATE TABLE `product_info_2` (
`product_info_id` BIGINT(20) NOT NULL COMMENT 'id',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
`product_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
COMMENT '商品名称',
`spec` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '规
格',
`region_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT
'产地',
`price` DECIMAL(10, 0) NULL DEFAULT NULL COMMENT '商品价格',
`image_url` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT
'商品图片',
PRIMARY KEY (`product_info_id`) USING BTREE,
INDEX `FK_Reference_1`(`store_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

          2.2 简单插点数据

          INSERT INTO `region` VALUES (1, '110000', '北京', 0, NULL);
INSERT INTO `region` VALUES (2, '410000', '河南省', 0, NULL);
INSERT INTO `region` VALUES (3, '110100', '北京市', 1, '110000');
INSERT INTO `region` VALUES (4, '410100', '郑州市', 1, '410000');

          这里只向region表中插入数据，其他表测试时在搞

          3 案例代码

          3.1 application.properties配置信息

          # Server port
server.port=8080
# Spring Boot 应用属性配置
spring.main.allow-bean-definition-overriding=true
spring.application.name=sharding-jdbc-test-04
# ShardingSphere 数据源配置，总共对应六个主库：m0,m1,m2;从库：s0,s1,s2
spring.shardingsphere.datasource.names=m0,m1,m2,s0,s1,s2
#配置m0连接
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/store_db?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
#配置s0连接
spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://localhost:3307/store_db?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=root
#配置m1连接
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/product_db_1?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
#配置s1连接
spring.shardingsphere.datasource.s1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s1.url=jdbc:mysql://localhost:3307/product_db_1?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=root
#配置m2连接
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/product_db_2?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
#配置s2连接
spring.shardingsphere.datasource.s2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s2.url=jdbc:mysql://localhost:3307/product_db_2?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.shardingsphere.datasource.s2.username=root
spring.shardingsphere.datasource.s2.password=root
# 数据库的主从同步指定
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1
spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=s2
# 设计数据库的分片键：store_info_id以及分片算法，根据{store_info_id%2 +1}可以计算机目标数据库名称
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=store_info_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{store_info_id%2 +1}
#store_info表的配置
spring.shardingsphere.sharding.tables.store_info.actual-data-nodes=ds$->{0}.store_info
spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.algorithm-expression=store_info
#product_info：水平分表设计
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes=ds$->{1..2}.product_info_$->{1..2}
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column=product_info_id
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression=product_info_$->{product_info_id%2+1}
spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_info_id
spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE
#product_descript：水平分表设计
spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes=ds$->{1..2}.product_descript_$->{1..2}
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.sharding-column=product_info_id
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.algorithm-expression=product_descript_$->{product_info_id%2+1}
spring.shardingsphere.sharding.tables.product_descript.key-generator.column=id
spring.shardingsphere.sharding.tables.product_descript.key-generator.type=SNOWFLAKE
# 绑定关联表：product_info,product_descript
spring.shardingsphere.sharding.binding-tables[0]=product_info,product_descript
#指定广播表region
spring.shardingsphere.sharding.broadcast-tables=region
# 添加日志
spring.shardingsphere.props.sql.show=true
# MyBatis configuration
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper-locations=classpath:/mapper/*.xml
mybatis.type-aliases-package=com.rql.entity

          3.2 测试

          案例：SpringBoot集成Sharding-JDBC实现分表分库与主从同步(详细版) 第5张

          按照数据库表创建各个实体类：

          其中ProductInfo是一个模型类，关联了其他表的属性：

          package com.rql.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.math.BigDecimal;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ProductInfo {
    private Long productInfoId;
    
    
    private Long storeInfoId;
    
    
    private String productName;
    
    
    private String spec;
    
    
    private String regionCode;
    
    
    private BigDecimal price;
    
    
    private String imageUrl;
    //关联信息
    private String descript;
    private String storeName;
    private int reputation;
    private String storeRegionName;
    private String placeOfOrigin;
}

          Dao层：

          @Mapper
@Component
public interface ProductDao {
    //添加商品基本信息
    @Insert("insert into product_info(store_info_id,product_name,spec,region_code,price)values (#{storeInfoId},#{productName},#{spec},#{regionCode},#{price})")
    @Options(useGeneratedKeys = true,keyProperty = "productInfoId",keyColumn = "product_info_id")
    int insertProductInfo(ProductInfo productInfo);
    //添加商品描述信息
    @Insert("insert into product_descript(product_info_id,descript,store_info_id) values (#{productInfoId},#{descript},#{storeInfoId})")
    @Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id")
    int insertProductDescript(ProductDescript productDescript);
    //分页查询
    @Select("select i.*,d.descript,r.region_name placeOfOrigin from product_info i join product_descript d on i.product_info_id=d.product_info_id join region r on i.region_code=r.region_code order by product_info_id desc limit #{start},#{pageSize}")
    List selectProductList(@Param("start")int start,@Param("pageSize") int pageSize);
    //商品总数
    @Select("select count(1) from product_info")
    int selectCount();
    //商品分组统计
    @Select("select t.region_code,count(1) as num from product_info t group by t.region_code having num>1 order by region_code")
    List selectProductGroupList();
}

          直接在测试类中测试：

          @SpringBootTest(classes = SpringBootApplication.class)
@RunWith(SpringRunner.class)
public class ShardingTest {
    @Autowired
    ProductService productService;
    @Autowired
    ProductDao productDao;
    @Test
    public void testCreateProduct() {
        for (int i = 0; i  

          4. 遇到的坑
           

          4.1 水平分表时的属性设置
           

          之前我是这样设置的：
           

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

           

          结果发现在插入数据时，m1与m2数据源均执行了插入过程，而我的目的是根据store_info_id设置的为1，正确的过程应该是只在m2的表中插入数据才对。
           

          解决：原来问题出现在配置的属性上，下面是修改后的属性配置，就成功地解决了上面的问题
           

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

           

          可以发现不同在于default.database-strategy改为了default-database-strategy，很明显第一种是不符合Sharding-JDBC的规范的。这种小细节有时候很难发现。
           

          4.2 绑定表的配置
           

          我之前是这样配置的：
           

          spring.shardingsphere.sharding.binding-tables=product_info,product_descript

           

          在进行关联查询时，就会以笛卡尔积的形式去查，这明显是不对的，因此修改后的配置如下：
           

          spring.shardingsphere.sharding.binding-tables[0]=product_info,product_descript

           

          如果还想继续添加绑定，则就继续增加数据即可。

相关阅读：

1、基于单片机STM32的智能衣柜设计（可以写进毕设与简历的项目）

2、【JavaEE多线程】深入解析Java并发工具类与应用实践

3、【数据结构】图论（图的储存方式，图的遍历算法DFS和BFS、图的遍历算法的应用、图的连通性问题）

4、【大数据与云计算】虚拟机安装Linux

5、wx小程序-input事件改变数据

免责声明
1、本网站属于个人的非赢利性网站，转载的文章遵循原作者的版权声明。
2、本网站转载文章仅为传播更多信息之目的，凡在本网站出现的信息，均仅供参考。本网站将尽力确保所
提供信息的准确性及可靠性，但不保证信息的正确性和完整性，且不对因信息的不正确或遗漏导致的任何
损失或损害承担责任。
3、任何透过本网站网页而链接及得到的资讯、产品及服务，本网站概不负责，亦不负任何法律责任。
4、本网站所刊发、转载的文章，其版权均归原作者所有，如其他媒体、网站或个人从本网下载使用，请在
转载有关文章时务必尊重该文章的著作权，保留本网注明的“稿件来源”，并白负版权等法律责任。

手机扫描二维码访问

文章版权声明：除非注明，否则均为主机测评原创文章，转载或复制请以超链接形式并注明出处。

相关阅读

发表评论

快捷回复： 表情：
评论列表 （暂无评论，4469人围观）

还没有评论，来说两句吧...

目录[+]