sharding‐jdbc之分库分表实战

时间：2024-04-13 12:20:42

数据库表结构

店铺数据库


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for region
-- ----------------------------
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;

-- ----------------------------
-- Records of region
-- ----------------------------
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');

-- ----------------------------
-- Table structure for store_info
-- ----------------------------
DROP TABLE IF EXISTS `store_info`;
CREATE TABLE `store_info`  (
  `id` bigint(20) NOT NULL COMMENT 'id',
  `store_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '店铺名称',
  `reputation` int(11) NULL DEFAULT NULL COMMENT '信誉等级',
  `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 COMMENT = '店铺表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of store_info
-- ----------------------------
INSERT INTO `store_info` VALUES (1, 'XX零食店', 4, '110100');
INSERT INTO `store_info` VALUES (2, 'XX饮品店', 3, '410100');

SET FOREIGN_KEY_CHECKS = 1;

商品和商品详情库

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for product_descript_1
-- ----------------------------
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 COMMENT = '商品详情表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for product_descript_2
-- ----------------------------
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;

-- ----------------------------
-- Table structure for product_info_1
-- ----------------------------
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 '规\r\n格',
  `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 COMMENT = '商品表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for product_info_2
-- ----------------------------
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 '规\r\n格',
  `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;

-- ----------------------------
-- Table structure for region
-- ----------------------------
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;

-- ----------------------------
-- Records of region
-- ----------------------------
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');

SET FOREIGN_KEY_CHECKS = 1;

数据库分别部署在2个服务，共计6个库，主库3个，从库3个

主数据库：店铺主库不拆分：store_db，商品主库拆分2个库：product_db_1，product_db_2

从数据库：店铺从库不拆分：store_db，商品从库拆分2个库：product_db_1，product_db_2

application.properties配置文件（主从库的配置信息）

server.port=56082

spring.application.name = shopping
spring.profiles.active = local

server.servlet.context-path = /shopping
spring.http.encoding.enabled = true
spring.http.encoding.charset = UTF-8
spring.http.encoding.force = true

spring.main.allow-bean-definition-overriding = true

mybatis.configuration.map-underscore-to-camel-case = true

#sharding-jdbc分片规则
#配置数据源 主库：m0,m1,m2,  从库：s0,s1,s2
spring.shardingsphere.datasource.names = m0,m1,m2,s0,s1,s2
#主库  store_db
spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url = jdbc:mysql://localhost:3306/store_db?useUnicode=true
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = root
#主库  product_db_1
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/product_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root
#主库  product_db_2
spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3306/product_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = root
#从库  store_db
spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.url = jdbc:mysql://localhost:3307/store_db?useUnicode=true
spring.shardingsphere.datasource.s0.username = root
spring.shardingsphere.datasource.s0.password = root
#从库  product_db_1
spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.url = jdbc:mysql://localhost:3307/product_db_1?useUnicode=true
spring.shardingsphere.datasource.s1.username = root
spring.shardingsphere.datasource.s1.password = root
#从库  product_db_2
spring.shardingsphere.datasource.s2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s2.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s2.url = jdbc:mysql://localhost:3307/product_db_2?useUnicode=true
spring.shardingsphere.datasource.s2.username = root
spring.shardingsphere.datasource.s2.password = root
#  ====================================================================================================
#主从关系
# 主库从库逻辑数据源定义 ds0为 store_db  告诉 sharding-jdbc 哪个是主库 哪个是从库
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  商品：product_info_ 商品详情：product_descript_  商品和商品详情都依赖店铺 都存 店铺id：store_info_id
#====================================================================================================
#分库策略（水平）  按照店铺id划分 store_info_id。店铺没分表，只做主从，故只需将商品和商品详情进行分库 ds1,ds2
    # 默认分库策略，以store_info_id为分片键，分片策略为 store_info_id % 2 + 1，也就是store_info_id为双数的 数据进入ds1，为单数的进入ds2
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}


#分表策略 ====================================================================================================
# 店铺表
# 1、 store_info分表策略  ds0包括：m0,s0;  读m0写s0 ；   固定分配至ds0的store_info真实表
    #actual-data-nodes: 数据节点
spring.shardingsphere.sharding.tables.store_info.actual-data-nodes = ds$->{0}.store_info
#   table-strategy.inline.sharding-column: 分片主键：id
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
#   --------------------------------------------------------------------------------------------------------------------------
#商品表
#  2、 product_info 分表策略       数据结点包括，ds1.product_info_1，ds1.product_info_2,ds2.product_info_1，ds2.product_info_2
    #  actual-data-nodes: 数据节点     分布在ds1,ds2的product_info_1 product_info_2表
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes = ds$->{1..2}.product_info_$->{1..2}
    #   table-strategy.inline.sharding-column: 分片主键：product_info_id 以商品id为分片主键
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column = product_info_id
    #   真实表名(表策略)  分片策略为product_info_id% 2 + 1， 为双数的数据进入product_info_1表，为单数的进入product_info_2表
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression = product_info_$->{product_info_id%2+1}
    # 表主键： product_info_id 生成为雪花算法，
spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_info_id
spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE
#   --------------------------------------------------------------------------------------------------------------------------
# 商品详情表 里面有商品id: product_info_id,由于商品详情和商品是绑定关系，所以商品详情 和 商品共用 product_info_id 为分片策略，有关联查询
# 3、product_descript 分表策略        数据结点包括，ds1.product_descript_1，ds1.product_descript_2,ds2.product_descript_1，ds2.product_descript_2
    #  actual-data-nodes: 数据节点    分布在ds1,ds2的product_descript_1 product_descript_2表
spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes = ds$->{1..2}.product_descript_$->{1..2}
    #   table-strategy.inline.sharding-column: 分片主键：product_info_id 以商品id为分片主键
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.sharding-column = product_info_id
    #   真实表名(表策略)：product_descript_；  分片策略为 product_info_id% 2 + 1， 为双数的数据进入product_descript_1表，为单数的进入product_descript_2表
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.algorithm-expression = product_descript_$->{product_info_id % 2 + 1}
    # 表主键： id 生成为雪花算法，
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
#  ====================================================================================================
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true

swagger.enable = true

logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding  = debug

配置数据源 主库：m0,m1,m2,  从库：s0,s1,s2

配置主从关系

分库策略（水平）

分表策略

 其他配置

开始插入数据

插入商品和商品详情的dao层，和普通sql一样

//添加商品基本信息
@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) " +
        " value(#{productInfoId},#{descript},#{storeInfoId})")
@Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id")
int insertProductDescript(ProductDescript productDescript);

 

定义service接口

//添加商品
public void createProduct(ProductInfo product);

services的实现类

@Autowired
ProductDao productDao;

//添加商品
@Override
@Transactional
public void createProduct(ProductInfo productInfo) {
    ProductDescript productDescript =new ProductDescript();
    //设置商品描述 信息
    productDescript.setDescript(productInfo.getDescript());
    //调用dao向商品信息表
    productDao.insertProductInfo(productInfo);
    //将商品信息id设置到productDescript
    productDescript.setProductInfoId(productInfo.getProductInfoId());
    //设置店铺id
    productDescript.setStoreInfoId(productInfo.getStoreInfoId());
    //向商品描述信息表插入数据
    productDao.insertProductDescript(productDescript);
}

测试插入方法

//添加商品
@Test
public void testCreateProduct(){
    for (int i=1;i<10;i++){
        ProductInfo productInfo = new ProductInfo();
        productInfo.setStoreInfoId(1L);  //店铺id = 2插入 m1 库  店铺id = 1插入 m2库
        productInfo.setProductName("Java编程思想"+i);//商品名称
        productInfo.setSpec("大号");
        productInfo.setPrice(new BigDecimal(60));
        productInfo.setRegionCode("110100");
        productInfo.setDescript("Java编程思想不错！！！"+i);//商品描述
        productService.createProduct(productInfo);
    }
}

数据库插入到 m2 对应的 product_db_2 库 

分页查询

dao层，和普通sql一样

@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<ProductInfo> selectProductList(@Param("start")int start, @Param("pageSize") int pageSize);

 

service接口

//查询商品
public List<ProductInfo> queryProduct(int page, int pageSize);

service接口实现类

 

@Override
public List<ProductInfo> queryProduct(int page, int pageSize) {
    int start = (page - 1) * pageSize;
    return productDao.selectProductList(start,pageSize);
}

测试查询方法

@Test
public void testQueryProduct(){

    List<ProductInfo> productInfos = productService.queryProduct(2, 2);
    System.out.println(productInfos);
}

都是去从库查询的，但是出现了笛卡尔积问题，说明商品和商品详情没绑定成功。

 需要修改配置文件
# 设置product_info,product_descript为绑定表  商品详情和商品是绑定关系， 关联查询需要
#spring.shardingsphere.sharding.binding-tables = product_info,product_descript    
# -- 这里应该是数组，会出现笛卡尔积的问题
spring.shardingsphere.sharding.binding-tables[0] = product_info,product_descript

不会出现笛卡尔积问题，说明商品和商品详情绑定成功

商品总数  和 商品分组统计

//商品总数
@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<Map> selectProductGroupList();

统计商品总数

//统计商品总数
@Test
public void testSelectCount(){
    int i = productDao.selectCount();
    System.out.println(i);
}

分组统计商品

//分组统计商品
@Test
public void testSelectProductGroupList(){
    List<Map> maps = productDao.selectProductGroupList();
    System.out.println(maps);
}

数据库共计18条数据

总结

select t.region_code,count(1) as num from product_info t group by t.region_code having num > 1 order by region_code

分组统计 group by  时一定要拼接   order by ，因为 流式归并 是先把每个表进行排序的，所以一定要和    order by 联合使用

在分组项与排序项完全一致的情况下，取得的数据是连续的，分组所需的数据全数存在于各个数据结果集的当前游标所指向的数据值，因此可以采用流式归并。如下图所示。

进行归并时，逻辑与排序归并类似。 下图展现了进行next调用的时候，流式分组归并是如何进行的；

    通过图中我们可以看到，当进行第一次next调用时，排在队列首位的t_score_java将会被弹出队列，并且将分组值同为“Jetty”的其他结果集中的数据一同弹出队列。 在获取了所有的姓名为“Jetty”的同学的分数之后，进行累加操作，那么，在第一次next调用结束后，取出的结果集是“Jetty”的分数总和。 与此同时，所有的数据结果集中的游标都将下移至数据值“Jetty”的下一个不同的数据值，并且根据数据结果集当前游标指向的值进行重排序。 因此，包含名字顺着第二位的“John”的相关数据结果集则排在的队列的前列。
 

相关文章