目录

sharding-jdbc综合案例

需求描述

电商平台商品列表展示,每个列表项中除了包含商品基本信息、商品描述信息之外,还包括了商品所属的店铺信息,如下:

20201123124857

本案例实现功能如下:

  1. 添加商品
  2. 商品分页查询
  3. 商品统计

数据库设计

数据库设计如下,其中商品与店铺信息之间进行了垂直分库,分为了PRODUCT_DB(商品库)和STORE_DB(店铺 库);商品信息还进行了垂直分表,分为了商品基本信息(product_info)和商品描述信息(product_descript),地理区域信息(region)作为公共表,冗余在两库中:

20201123125024

考虑到商品信息的数据增长性,对PRODUCT_DB(商品库)进行了水平分库分片键使用店铺id,分片策略为店铺 ID%2 + 1,因此商品描述信息对所属店铺ID进行了冗余;

对商品基本信息(product_info)和商品描述信息(product_descript)进行水平分表,分片键使用商品id,分片策略为 商品ID%2 + 1,并将为这两个表设置为绑定表,避免笛卡尔积join;

为避免主键冲突,ID生成策略采用雪花算法来生成全局唯一ID,最终数据库设计为下图:

http://img.cana.space/picStore/20201123125210.png

要求使用读写分离来提升性能,可用性。

环境说明

  • 数据库:mysql-5.7
  • jdk:1.8
  • springboot:2.1.3.RELEASE
  • sharding-jdbc-spring-boot-starter:4.0.0-RC1
  • mybatis-spring-boot-starter:2.0.0
  • druid-spring-boot-starter:1.1.16
  • mysql-connector-java:5.1.47

环境准备

mysql主从同步

参考:主从同步

对以下库进行主从同步配置:

1
2
3
4
# 要同步的数据库
binlog-do-db=store_db
binlog-do-db=product_db_1
binlog-do-db=product_db_2

设置好后重启

1
$ dcp -f mysql-1m-1s.yml restart

查看从服务器slave状态

1
show slave status;

看到两个yes表示主从ok

初始化数据库

以下都在主服务器进行

创建store_db数据库,并执行以下脚本创建表:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 创建数据库user_db
CREATE DATABASE `store_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
-- 创建区域表
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;
-- 插入数据
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');

-- 店铺信息表
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 ROW_FORMAT = Dynamic;
-- 插入店铺信息
INSERT INTO `store_info` VALUES (1,'XX零食店',4,'110100'); 
INSERT INTO `store_info` VALUES (2,'XX饮品店',3,'410100');

创建product_db_1、product_db_2数据库,并分别对两库执行以下脚本创建表:

1
2
3
-- 创建数据库
CREATE DATABASE `product_db_1` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `product_db_2` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

执行以下脚本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
-- 商品信息表(水平分表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 '规 格',
	`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)
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;

-- 商品描述信息表(水平分表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 ROW_FORMAT = Dynamic;

-- 商品描述信息表(水平分表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;

-- 区域表(公共表)
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;
-- 插入区域数据
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');

至此,数据库端工作完成

实现步骤

搭建maven工程shopping

pom

父pom

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<?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.eh</groupId>
    <artifactId>sharding-jdbc-test</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>sharding-jdbc-simple</module>
    </modules>

    <dependencyManagement>
        <dependencies>
            <!--spring boot 2.2.2-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>2.1.3.RELEASE</version>
                <!--默认type为jar,pom表示依赖的是一个父工程,父工程里引入了很多jar-->
                <type>pom</type>
                <!--
                在Spring boot 项目的POM文件中,我们可以通过在POM文件中继承 Spring-boot-starter-parent来引用Spring boot默认依赖的jar包
                但是,通过上面的parent继承的方法,只能继承一个 spring-boot-start-parent。
                实际开发中,用户很可能需要继承自己公司的标准parent配置,这个时候可以使用 scope=import 来实现多继承。
                -->
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.16</version>
            </dependency>
        </dependencies>
    </dependencyManagement>

</project>

子pom

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
<?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">
    <parent>
        <artifactId>sharding-jdbc-test</artifactId>
        <groupId>com.eh</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>shopping</artifactId>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <!--数据库-->
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
        <!--数据源druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.16</version>
        </dependency>
        <!--mysql-connector-java-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

</project>

properties

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
server.port=8002
spring.application.name=shopping
# 使用bean定义覆盖,sharding-jdbc的datasource覆盖druid定义的datasource,如果不覆盖会报
# Invalid bean definition with name 'dataSource' defined in class path resource
spring.main.allow-bean-definition-overriding = true
# 以下是分片规则配置

# 1. m0,就是这个真实数据源的名称,然后需要告诉Sharding-JDBC,咱们有哪些真实数据源,
# 这里有3个库都配置了读写分离,所以有6个真实的数据源
spring.shardingsphere.datasource.names=m0,m1,m2,s1,s1,s2

# 2. 既然是分库分表,那么就需要定义多个真实数据源,每一个数据库链接信息就是一个数据源定义,如:
# store_db master
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:3307/store_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=333
# product_db_1 master
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:3307/product_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=333
# product_db_2 master
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:3307/product_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=333
# 上面3个库的从库数据源配置
# store_db slave
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:3308/store_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=333
# product_db_1 slave
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:3308/product_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=333
# product_db_2 slave
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:3308/product_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.s2.username=root
spring.shardingsphere.datasource.s2.password=333

# 3. 如果需要配置读写分离,还需要告诉Sharding-JDBC,这么多真实数据源,那几个是一套读写分离?
# 也就是定义主从逻辑数据源:
# store_db
spring.shardingsphere.sharding.master‐slave‐rules.storeDS.master-data-source-name=m0
spring.shardingsphere.sharding.master‐slave‐rules.storeDS.slave-data-source-names=s0
# product_db_1
spring.shardingsphere.sharding.master‐slave‐rules.productDS1.master-data-source-name=m1
spring.shardingsphere.sharding.master‐slave‐rules.productDS1.slave-data-source-names=s1
# product_db_2
spring.shardingsphere.sharding.master‐slave‐rules.productDS2.master-data-source-name=m2
spring.shardingsphere.sharding.master‐slave‐rules.productDS2.slave-data-source-names=s2

# 4. 默认分库策略,这里指product分库:product_db_1和product_db_2
# 以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=productDS$->{store_info_id % 2 + 1}

# 5. 分表策略
# store_info分表策略,固定分配至storeDS的store_info真实表
spring.shardingsphere.sharding.tables.store_info.actual-data-nodes=storeDS.store_info

# product_info分表策略,分布在productDS1,productDS2的product_info_1 product_info_2表 ,
# 分片策略为product_info_id % 2 + 1,product_info_id生成为雪花算法,
# 为双数的数据进入product_info_1表,为单数的进入product_info_2 表
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes=productDS$->{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分表策略,同product_info,注意分片键要和product_info一致,因为它俩要构成绑定表
spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes=productDS$->{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为绑定表,要以数组形式配置,从下标0开始
spring.shardingsphere.sharding.binding‐tables[0]=product_info,product_descript

# 设置region为广播表(公共表),每次更新操作会发送至所有数据源
spring.shardingsphere.sharding.broadcast‐tables=region

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

主启动

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
package com.eh.shardingjdbc.shopping;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.eh.shardingjdbc.shopping.dao")
@SpringBootApplication
public class ShoppingMainApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShoppingMainApplication.class, args);
    }
}

业务类

dao

ProductMapper

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
package com.eh.shardingjdbc.shopping.dao;

import com.eh.shardingjdbc.shopping.entity.ProductDescript;
import com.eh.shardingjdbc.shopping.entity.ProductInfo;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface ProductMapper {
    //添加商品基本信息并回写通过雪花算法生成的主键:productInfoId
    @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);

    //添加商品描述信息,并回写通过雪花算法生成的主键:id
    @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);

    /**
     * 分页查询
     * 多表关联
     * 带排序
     * @param start
     * @param pageSize
     * @return
     */
    @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);

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

service

ProductService

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
package com.eh.shardingjdbc.shopping.service;

import com.eh.shardingjdbc.shopping.entity.ProductInfo;

import java.util.List;

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

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

ProductServiceImpl

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package com.eh.shardingjdbc.shopping.service.impl;

import com.eh.shardingjdbc.shopping.dao.ProductMapper;
import com.eh.shardingjdbc.shopping.entity.ProductDescript;
import com.eh.shardingjdbc.shopping.entity.ProductInfo;
import com.eh.shardingjdbc.shopping.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
public class ProductServiceImpl implements ProductService {

    @Autowired
    private ProductMapper productMapper;

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

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

entity

ProductDescript商品描述

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.eh.shardingjdbc.shopping.entity;

import lombok.Data;

@Data
public class ProductDescript {
    private Long id;

    /**
     * 所属商品id
     */
    private Long productInfoId;

    /**
     * 商品描述
     */
    private String descript;

    /**
     * 所属店铺id
     */
    private Long storeInfoId;
}

ProductInfo商品信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
package com.eh.shardingjdbc.shopping.entity;

import lombok.Data;

import java.math.BigDecimal;

@Data
public class ProductInfo {

    private Long productInfoId;

    /**
     * 所属店铺id
     */
    private Long storeInfoId;

    /**
     * 商品名称
     */
    private String productName;

    /**
     * 规格
     */
    private String spec;

    /**
     * 产地
     */
    private String regionCode;

    /**
     * 商品价格
     */
    private BigDecimal price;

    /**
     * 商品图片
     */
    private String imageUrl;

    ////////////////////////////////关联信息/////////////////////////////////////////

    /**
     * 商品描述
     */
    private String descript;

    /**
     * 产地名称
     */
    private String placeOfOrigin;

    /**
     * 店铺名称
     */
    private String storeName;

    /**
     * 店铺信誉等级
     */
    private int reputation;

    /**
     * 店铺所在地名称
     */
    private String storeRegionName;

}

Region区域信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.eh.shardingjdbc.shopping.entity;

import lombok.Data;

@Data
public class Region {
    private Long id;

    /**
     * 地理区域编码
     */
    private String regionCode;

    /**
     * 地理区域名称
     */
    private String regionName;

    /**
     * 地理区域级别(省、市、县)
     */
    private int level;

    /**
     * 上级地理区域编码
     */
    private String parentRegionCode;
}

StoreInfo店铺信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.eh.shardingjdbc.shopping.entity;

import lombok.Data;

@Data
public class StoreInfo {
    private Long id;

    /**
     * 店铺名称
     */
    private String storeName;

    /**
     * 信誉等级
     */
    private int reputation;

    /**
     * 店铺所在地
     */
    private String regionCode;

    /**
     * 店铺所在地名称
     */
    private String regionName;
}

测试类:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
package com.eh.shardingjdbc.shopping.service;

import com.eh.shardingjdbc.shopping.dao.ProductMapper;
import com.eh.shardingjdbc.shopping.entity.ProductInfo;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@RunWith(SpringRunner.class)
@SpringBootTest
public class ProductServiceTest {

    @Autowired
    private ProductService productService;
    @Autowired
    private ProductMapper productMapper;

    @Test
    public void createProduct() {
        for (int i = 1; i < 10; i++) {
            ProductInfo productInfo = new ProductInfo();
            productInfo.setStoreInfoId(2L);//店铺id

            productInfo.setProductName("Java编程思想" + i);//商品名称
            productInfo.setSpec("大号");
            productInfo.setPrice(new BigDecimal(60));
            productInfo.setRegionCode("110100");
            productInfo.setDescript("Java编程思想不错!!!" + i);//商品描述
            productService.createProduct(productInfo);
        }
    }

    //查询商品
    @Test
    public void testQueryProduct() {
        List<ProductInfo> productInfos = productService.queryProduct(2, 2);
        System.out.println(productInfos);
    }

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

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

添加商品

通过添加商品接口新增商品进行分表验证,product_id为偶数的数据在product_info_1、product_descript_1,为 奇数的数据在product_info_2、product_descript_2。将店铺id改成1L,可以发现此时用的分库是product_db_2,数据分布规律同之前的product_db_1。

查询商品

执行testQueryProduct,日志:

http://img.cana.space/picStore/20201123151755.png

可以看到我们分页设置是2,2,sharding-jdbc为了保证结果的正确性,改写了sql,传入到mysql的时候成了0,4

总结

分页查询是业务中最常见的场景,Sharding-jdbc支持常用关系数据库的分页查询,不过Sharding-jdbc的分页功能 比较容易让使用者误解,用户通常认为分页归并会占用大量内存。 在分布式的场景中,将 LIMIT 10000000 , 10改 写为 LIMIT 0, 10000010 ,才能保证其数据的正确性。 用户非常容易产生ShardingSphere会将大量无意义的数据 加载至内存中,造成内存溢出风险的错觉。 其实大部分情况都通过流式归并获取数据结果集,因此ShardingSphere 会通过结果集的next方法将无需取出的数据全部跳过,并不会将其存入内存。

但同时需要注意的是,由于排序的需要,大量的数据仍然需要传输到Sharding-Jdbc的内存空间。 因此,采用LIMIT 这种方式分页,并非最佳实践(一般多数据源分页可以先将数据保存到es中,再从es取出数据分页)。 由于LIMIT并不能通过索引查询数据,因此如果可以保证ID的连续性,通过ID进行 分页是比较好的解决方案,例如:

1
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id;

或通过记录上次查询结果的最后一条记录的ID进行下一页的查询,例如:

1
SELECT * FROM t_order WHERE id > 10000000 LIMIT 10;

排序功能是由Sharding-jdbc的排序归并来完成,由于在SQL中存在 ORDER BY 语句,因此每个数据结果集自身是有 序的,因此只需要将数据结果集当前游标指向的数据值进行排序即可。 这相当于对多个有序的数组进行排序,归并排序是最适合此场景的排序算法。

统计商品

统计商品总数

http://img.cana.space/picStore/20201123152901.png

可以看到4个表都会查一遍

分组统计

http://img.cana.space/picStore/20201123153124.png

分组统计总结

分组统计也是业务中常见的场景,分组功能的实现由Sharding-jdbc分组归并完成。分组归并的情况最为复杂,它 分为流式分组归并和内存分组归并。 流式分组归并要求SQL的排序项与分组项的字段必须保持一致,否则只能通过 内存归并才能保证其数据的正确性。

举例说明,假设根据科目分片,表结构中包含考生的姓名(为了简单起见,不考虑重名的情况)和分数。通过SQL 获取每位考生的总分,可通过如下SQL:

1
SELECT name, SUM(score) FROM t_score GROUP BY name ORDER BY name;

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

http://img.cana.space/picStore/20201123154425.png

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

http://img.cana.space/picStore/20201123154219.png

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

小结:利用rs,将几个结果集中当前游标指向的值进行重排序,最前面的出列。