海量数据存储Sharding-JDBC分库分表2-阿里云开发者社区

5.SpringBoot整合Sharding-Jdbc

5.1.Sharding-Jdbc项目创建

1、创建maven项目，pom.xml引入依赖

    <properties>
        <!--JDK版本，如果是jdk8则这里是 1.8-->
        <java.version>1.8</java.version>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <spring.boot.version>2.5.5</spring.boot.version>
        <mybatisplus.boot.starter.version>3.4.0</mybatisplus.boot.starter.version>
        <lombok.version>1.18.16</lombok.version>
        <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
        <junit.version>4.12</junit.version>
        <druid.version>1.1.16</druid.version>
        <!--跳过单元测试-->
        <skipTests>true</skipTests>
    </properties>

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring.boot.version}</version>
        </dependency>
        <!--<dependency>-->
        <!--<groupId>org.springframework.boot</groupId>-->
        <!--<artifactId>spring-boot-test</artifactId>-->
        <!--</dependency>-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring.boot.version}</version>
            <scope>test</scope>
        </dependency>
        <!--mybatis plus和springboot整合-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.boot.starter.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <!--<scope>provided</scope>-->
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-jdbc.version}</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring.boot.version}</version>
                <configuration>
                    <fork>true</fork>
                    <addResources>true</addResources>
                </configuration>
            </plugin>
        </plugins>
    </build>

2、创建application.properties

spring.application.name=sharding-jdbc
server.port=8080

3、创建主启动类

@MapperScan("com.lixiang.mapper")
@EnableTransactionManagement
@SpringBootApplication
public class ShardingApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingApplication.class);
    }
}

5.2.创建数据库表

分库分表需求

2库2表

数据库

shop_order_0

product_order_0

product_order_1

shop_order_1

product_order_0
product_order_1

脚本

CREATE TABLE `product_order_0` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `out_trade_no` varchar(64) DEFAULT NULL COMMENT '订单唯一标识',
  `state` varchar(11) DEFAULT NULL COMMENT 'NEW 未支付订单,PAY已经支付订单,CANCEL超时取消订单',
  `create_time` datetime DEFAULT NULL COMMENT '订单生成时间',
  `pay_amount` decimal(16,2) DEFAULT NULL COMMENT '订单实际支付价格',
  `nickname` varchar(64) DEFAULT NULL COMMENT '昵称',
  `user_id` bigint DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

实体bean

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("product_order")
public class ProductOrderDO {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    private String outTradeNo;
    private String state;
    private Date createTime;
    private Double payAmount;
    private String nickname;
    private Long userId;
}
//Mapper类
public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {
}

5.3.Sharding-JDBC配置文件

spring.application.name=sharding-jdbc
server.port=8080
spring.shardingsphere.props.sql.show=true
# 配置数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 配置ds0库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.159.101:3306/shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
# 配置ds1库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.159.101:3306/shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
# 指定product_order表的数据分布情况，配置数据节点，行表达式标识符使用${...}或$->{...}
# 但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}
# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
# 使用user_id作为分片健
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=user_id
# 分片策略为user_id和2取余，进入到某一张product_order_?表
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{user_id % 2}

5.4.分表代码测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingApplication.class)
@Slf4j
public class DBTest {
    @Resource
    private ProductOrderMapper productOrderMapper;
    @Test
    public void testSaveProductOrder(){
        for(int i=0;i<10;i++){
            ProductOrderDO productOrder = new ProductOrderDO();
            productOrder.setCreateTime(new Date());
            productOrder.setNickname("李祥：i="+i);
            productOrder.setOutTradeNo(UUID.randomUUID().toString().substring(0,32));
            productOrder.setPayAmount(100.00);
            productOrder.setState("PAY");
            productOrder.setUserId(Long.valueOf(i+""));
            productOrderMapper.insert(productOrder);
        }
    }
}

控制台SQL

Logic SQL : 逻辑SQL，没具体到哪个数据节点
Actual SQL：真实SQL, 具体到每个数据节点的SQL

问题：主键重复问题，分库分表后，主键自增会造成不同分片上的数据表主键会重复。

6.Snowflake雪花算法

6.1.业界常用ID解决方案

（1）数据库自增ID

利用自增id，设置不同的自增步长，auto_increment_offset、auto_increment_increment

DB1:单数
//从1开始、每次加2
DB2:偶数
//从2开始，每次加2

缺点

依靠数据库系统的功能实现，但是未来扩容麻烦
主从切换时的不一致可能导致重复发号
性能瓶颈存在单台sql上

（2）UUID

性能非常高，没有网络消耗
缺点

无序的字符串，不具备趋势自增特性
UUID太长，不易于存储，浪费存储空间，很多场景不适用

（3）Redis发号器

利用Redis的INCR和INCRBY来实现，原子操作，线程安全，性能比Mysql强劲
缺点

需要占用网络资源，增加系统复杂度

（4）Snowflake雪花算法

twitter 开源的分布式 ID 生成算法，代码实现简单、不占用宽带、数据迁移不受影响
生成的 id 中包含有时间戳，所以生成的 id 按照时间递增，算法性能高

部署了多台服务器，需要保证系统时间一样，机器编号不一样
缺点

依赖系统时钟（多台服务器时间一定要一样）

6.2.Snowflake原理

雪花算法生成的数字，long类，所以就是8个byte，64bit

表示的值 -9223372036854775808（-2的63次方） ~ 9223372036854775807（2的63次方-1）

生成的唯一值用于数据库主键，不能是负数，所以值为0~9223372036854775807（2的63次方-1）

6.3.Snowflake生成ID的问题

全局唯一不能重复

（1）分布式部署就需要分配不同的workId, 如果workId相同，可能会导致生成的id相同

配置文件配置实操，在application.properties种，yml文件注意层级即可

spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1

使用sharding-jdbc中的使用IP后几位来做workId, 但在某些情况下会出现生成重复ID的情况

解决办法时：在启动时给每个服务分配不同的workId, 引入redis/zk都行，缺点就是多了依赖

（2）分布式情况下，需要保证各个系统时间一致，如果服务器的时钟回拨，就会导致生成的 id 重复

配置雪花算法生成id
订单id使用MybatisPlus的配置,ProductOrder类配置

@TableId(value = "id", type = IdType.ASSIGN_ID)
默认实现类为DefaultIdentifierGenerator雪花算法

使用Sharding-Jdbc配置文件,注释DO类里面的id分配策略

#id生成策略
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

7.广播表和绑定表配置

7.1.Sharding-JDBC广播表配置

1、什么是广播表

指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致

适用于数据量不大且需要与海量数据的表进行关联查询的场景
例如：字典表、配置表

2、配置实战

（1）增加ad_config表

CREATE TABLE `ad_config` (
  `id` bigint unsigned NOT NULL COMMENT '主键id',
  `config_key` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置key',
  `config_value` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置value',
  `type` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

（2）POJO类、Mapper

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("ad_config")
public class AdConfigDO {
    private Long id;
    private String configKey;
    private String configValue;
    private String type;
}
public interface AdConfigMapper extends BaseMapper<AdConfigDO> {
}

（3）配置文件

# 配置广播表
spring.shardingsphere.sharding.broadcast-tables=ad_config
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE

（4）测试代码

    @Test
    public void testSaveAdConfig(){
        AdConfigDO adConfigDO = new AdConfigDO();
        adConfigDO.setConfigKey("key");
        adConfigDO.setConfigValue("value");
        adConfigDO.setType("type");
        adConfigMapper.insert(adConfigDO);
    }

7.2.水平分库分表配置

1、分库分表配置

分库规则根据 user_id 进行分库
分表规则根据 product_order_id 订单号进行分表

spring.application.name=sharding-jdbc
server.port=8080
spring.shardingsphere.props.sql.show=true
# 配置数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 配置ds0库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.159.101:3306/shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
# 配置ds1库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.159.101:3306/shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
# id生成策略（雪花算法）
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE
# 指定product_order表的数据分布情况，配置数据节点，行表达式标识符使用${...}或$->{...}
# 但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
# 使用id作为分片健
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
# 分片策略为id和2取余，进入到某一张product_order_?表
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id % 2}
# 配置广播表
spring.shardingsphere.sharding.broadcast-tables=ad_config
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE

2、测试代码

    @Test
    public void testSaveProductOrder(){
        Random random = new Random();
        for(int i=0;i<10;i++){
            ProductOrderDO productOrder = new ProductOrderDO();
            productOrder.setCreateTime(new Date());
            productOrder.setNickname("李祥：i="+i);
            productOrder.setOutTradeNo(UUID.randomUUID().toString().substring(0,32));
            productOrder.setPayAmount(100.00);
            productOrder.setState("PAY");
            productOrder.setUserId(Long.valueOf(random.nextInt(50)));
            productOrderMapper.insert(productOrder);
        }
    }

7.3.Sharding-JDBC绑定表配置

1、什么是绑定表

指分片规则一致的主表和子表
比如product_order表和product_order_item表，均按照order_id分片，则此两张表互为绑定表关系
绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升

2、创建product_order_item表，一个库创建两个

CREATE TABLE `product_order_item_0` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_order_id` bigint DEFAULT NULL COMMENT '订单号',
  `product_id` bigint DEFAULT NULL COMMENT '产品id',
  `product_name` varchar(128) DEFAULT NULL COMMENT '商品名称',
  `buy_num` int DEFAULT NULL COMMENT '购买数量',
  `user_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

3、编写实体ProductOrderItemDO，Mapper

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("product_order_item")
public class ProductOrderItemDO {
    private Long id;
    private Long productOrderId;
    private Long productId;
    private String productName;
    private Integer buyNum;
    private Long userId;
}
public interface ProductOrderItemMapper extends BaseMapper<ProductOrderItemDO> {
    @Select("select o.id from product_order o left join product_order_item i on o.id = i.product_order_id")
    List<Map<String,Object>> listProductOrderDetail();
}

4、编写配置文件

spring.application.name=sharding-jdbc
server.port=8080
spring.shardingsphere.props.sql.show=true
# 配置数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 配置ds0库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.159.101:3306/shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
# 配置ds1库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.159.101:3306/shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
# 配置默认分库规则
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{user_id % 2}
# 配置product_order表 id生成策略（雪花算法）
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id % 2}
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE
# 配置广播表 id生成策略（雪花算法）
spring.shardingsphere.sharding.broadcast-tables=ad_config
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE
# 配置product_order_item表 id生成策略（雪花算法）
spring.shardingsphere.sharding.tables.product_order_item.actual-data-nodes=ds$->{0..1}.product_order_item_$->{0..1}
spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.sharding-column=product_order_id
spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.algorithm-expression=product_order_item_$->{product_order_id % 2}
spring.shardingsphere.sharding.tables.product_order_item.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order_item.key-generator.type=SNOWFLAKE
# 配置绑定表 让对应的主表为1的找副表为1的关联,注意不加这个，会产生笛卡尔积
spring.shardingsphere.sharding.binding-tables[0]=product_order,product_order_item

5、测试代码

    @Test
    public void testFindOrderItem(){
        List<Map<String, Object>> list = productOrderItemMapper.listProductOrderDetail();
        list.forEach(map -> System.out.println(map));
    }

不加配置绑定表的结果（spring.shardingsphere.sharding.binding-tables[0]=product_order,product_order_item）

加配置绑定表的结果（spring.shardingsphere.sharding.binding-tables[0]=product_order,product_order_item）

7.4.水平分库分表后查询和删除操作

1、查询操作

有分片键（标准路由）

    @Test
    public void testSelectHavePartitionKey(){
        //id为分片键
        ProductOrderDO orderDO = productOrderMapper.selectOne(new QueryWrapper<ProductOrderDO>().eq("id", 678627813053431809L));
        System.out.println(orderDO);
    }

无分片键（全库表路由）

    @Test
    public void testSelectNoHavePartitionKey(){
        //id为分片键
        ProductOrderDO orderDO = productOrderMapper.selectOne(new QueryWrapper<ProductOrderDO>().eq("out_trade_no", "59c66f86-55f7-4b80-b803-13a76048"));
        System.out.println(orderDO);
    }

2、删除操作

有分片键（标准路由）

    @Test
    public void testDeleteHavePartitionKey(){
        //id为分片键
        productOrderMapper.delete(new QueryWrapper<ProductOrderDO>().eq("id", 678627813053431809L));
    }

无分片键（全库表路由）

    @Test
    public void testDeleteNoHavePartitionKey(){
        //id为分片键
        productOrderMapper.delete(new QueryWrapper<ProductOrderDO>().eq("out_trade_no", "59c66f86-55f7-4b80-b803-13a76048"));
    }

海量数据存储Sharding-JDBC分库分表2