Sharding-JDBC之广播表（公共表）

一、简介

  这里的广播表也叫公共表。

• 存储固定数据的表，表数据很少发生变化，通常进行关联查询
• 每个数据库中创建出相同结构的广播表

我们就在之前讲过的水平分库水平分表的基础上增加广播表的功能，本文示例大概架构，如下图：

二、maven依赖

pom.xml

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.alian</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-jdbc</name>
    <description>sharding-jdbc</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

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

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.15</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

  有些小伙伴的 druid 可能用的是 druid-spring-boot-starter

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.6</version>
</dependency>

  然后出现可能使用不了的各种问题，这个时候你只需要在主类上添加 @SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class}) 即可

package com.alian.shardingjdbc;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})
@SpringBootApplication
public class ShardingJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class, args);
    }

}

三、数据库

3.1、创建数据库

CREATE DATABASE `sharding_5` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE `sharding_6` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3.2、创建表

  在数据库sharding_5和sharding_6下面分别创建三张表：tb_order_status、tb_order_1和tb_order_2，这里我们的广播表就是：tb_order_status

tb_order_1

CREATE TABLE `tb_order_1` (
  `order_id` bigint(20) NOT NULL COMMENT '主键',
  `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `price` int unsigned NOT NULL DEFAULT '0' COMMENT '价格（单位：分）',
  `order_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '订单状态(1:待付款,2:已付款,3:已取消)',
  `order_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `title` varchar(100)  NOT NULL DEFAULT '' COMMENT '订单标题',
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_order_time` (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

tb_order_2

CREATE TABLE `tb_order_2` (
  `order_id` bigint(20) NOT NULL COMMENT '主键',
  `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `price` int unsigned NOT NULL DEFAULT '0' COMMENT '价格（单位：分）',
  `order_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '订单状态(1:待付款,2:已付款,3:已取消)',
  `order_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `title` varchar(100)  NOT NULL DEFAULT '' COMMENT '订单标题',
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_order_time` (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

tb_order_status

CREATE TABLE `tb_order_status` (
  `id` bigint unsigned NOT NULL COMMENT '主键',
  `status_code` tinyint NOT NULL DEFAULT 1 COMMENT '状态编号',
  `status_name` varchar(10) NOT NULL DEFAULT '' COMMENT '状态名称',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_status_code` (`status_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='状态码表';

四、配置（二选一）

4.1、properties配置

application.properties

server.port=8899
server.servlet.context-path=/sharding-jdbc

# 允许定义相同的bean对象去覆盖原有的
spring.main.allow-bean-definition-overriding=true
# 数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=ds1,ds2
# sharding_1数据库连接池类名称
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
# sharding_1数据库驱动类名
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
# sharding_1数据库url连接
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.0.129:3306/sharding_5?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
# sharding_1数据库用户名
spring.shardingsphere.datasource.ds1.username=alian
# sharding_1数据库密码
spring.shardingsphere.datasource.ds1.password=123456

# sharding_2数据库连接池类名称
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
# sharding_2数据库驱动类名
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
# sharding_2数据库url连接
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.0.130:3306/sharding_6?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
# sharding_2数据库用户名
spring.shardingsphere.datasource.ds2.username=alian
# sharding_2数据库密码
spring.shardingsphere.datasource.ds2.password=123456

# 指定tb_order表的数据分布情况，配置数据节点，使用Groovy的表达式,逻辑表tb_order对应的节点是：ds1.tb_order_1, ds1.tb_order_2,ds2.tb_order_1, ds2.tb_order_2
spring.shardingsphere.sharding.tables.tb_order.actual-data-nodes=ds$->{1..2}.tb_order_$->{1..2}

# 指定库分片策略，根据user_id的奇偶性来添加到不同的库中
spring.shardingsphere.sharding.tables.tb_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.tb_order.database-strategy.inline.algorithm-expression=ds$->{user_id%2==0?2:1}

# 采用行表达式分片策略：InlineShardingStrategy
# 指定tb_order表的分片策略中的分片键
spring.shardingsphere.sharding.tables.tb_order.table-strategy.inline.sharding-column=order_id
# 指定tb_order表的分片策略中的分片算法表达式,使用Groovy的表达式
spring.shardingsphere.sharding.tables.tb_order.table-strategy.inline.algorithm-expression=tb_order_$->{order_id%2==0?2:1}

# 指定tb_order表的主键为order_id
spring.shardingsphere.sharding.tables.tb_order.key-generator.column=order_id
# 指定tb_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.tb_order.key-generator.type=SNOWFLAKE
# 指定雪花算法的worker.id
spring.shardingsphere.sharding.tables.tb_order.key-generator.props.worker.id=100
# 指定雪花算法的max.tolerate.time.difference.milliseconds
spring.shardingsphere.sharding.tables.tb_order.key-generator.props.max.tolerate.time.difference.milliseconds=20

# 广播表或公共表
spring.shardingsphere.sharding.broadcast-tables=tb_order_status
# 指定tb_order_status表的主键为order_id
spring.shardingsphere.sharding.tables.tb_order_status.key-generator.column=id
# 指定tb_order_status表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.tb_order_status.key-generator.type=SNOWFLAKE
# 指定雪花算法的worker.id
spring.shardingsphere.sharding.tables.tb_order_status.key-generator.props.worker.id=102
# 指定雪花算法的max.tolerate.time.difference.milliseconds
spring.shardingsphere.sharding.tables.tb_order_status.key-generator.props.max.tolerate.time.difference.milliseconds=20

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

4.2、yml配置

application.yml

server:
  port: 8899
  servlet:
    context-path: /sharding-jdbc

spring:
  main:
    # 允许定义相同的bean对象去覆盖原有的
    allow-bean-definition-overriding: true
  shardingsphere:
    props:
      sql:
        # 打开sql输出日志
        show: true
    datasource:
      # 数据源名称，多数据源以逗号分隔
      names: ds1,ds2
      ds1:
        # 数据库连接池类名称
        type: com.alibaba.druid.pool.DruidDataSource
        # 数据库驱动类名
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 数据库url连接
        url: jdbc:mysql://192.168.0.129:3306/sharding_5?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
        # 数据库用户名
        username: alian
        # 数据库密码
        password: 123456
      ds2:
        # 数据库连接池类名称
        type: com.alibaba.druid.pool.DruidDataSource
        # 数据库驱动类名
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 数据库url连接
        url: jdbc:mysql://192.168.0.130:3306/sharding_6?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
        # 数据库用户名
        username: alian
        # 数据库密码
        password: 123456
    sharding:
      # 未配置分片规则的表将通过默认数据源定位
      default-data-source-name: ds1
      # 广播表
      broadcast-tables: tb_order_status
      tables:
        tb_order:
          # 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式
          actual-data-nodes: ds$->{1..2}.tb_order_$->{1..2}
          # 分库策略
          database-strategy:
            # 行表达式分片策略
            inline:
              # 分片键
              sharding-column: user_id
              # 算法表达式
              algorithm-expression: ds$->{user_id%2==0?2:1}
          # 分表策略
          table-strategy:
            # 行表达式分片策略
            inline:
              # 分片键
              sharding-column: order_id
              # 算法表达式
              algorithm-expression: tb_order_$->{order_id%2==0?2:1}
          # key生成器
          key-generator:
            # 自增列名称，缺省表示不使用自增主键生成器
            column: order_id
            # 自增列值生成器类型，缺省表示使用默认自增列值生成器（SNOWFLAKE/UUID）
            type: SNOWFLAKE
            # SnowflakeShardingKeyGenerator
            props:
              # SNOWFLAKE算法的worker.id
              worker:
                id: 100
              # SNOWFLAKE算法的max.tolerate.time.difference.milliseconds
              max:
                tolerate:
                  time:
                    difference:
                      milliseconds: 20
        tb_order_status:
          key-generator:
            # 自增列名称，缺省表示不使用自增主键生成器
            column: id
            # 自增列值生成器类型，缺省表示使用默认自增列值生成器（SNOWFLAKE/UUID）
            type: SNOWFLAKE
            # SnowflakeShardingKeyGenerator
            props:
              # SNOWFLAKE算法的worker.id
              worker:
                id: 102
              # SNOWFLAKE算法的max.tolerate.time.difference.milliseconds
              max:
                tolerate:
                  time:
                    difference:
                      milliseconds: 20

• actual-data-nodes ：使用Groovy的表达式 ds$->{1…2}.tb_order_$->{1…2}，表示逻辑表tb_order对应的物理表是：ds1.tb_order_1、 ds1.tb_order_2、ds2.tb_order_1、 ds2.tb_order_2
• key-generator ：key生成器，需要指定字段和类型，如果是SNOWFLAKE，最好也配置下props中的两个属性： worker.id 与 max.tolerate.time.difference.milliseconds 属性（主要还是yml中配置）
• table-strategy 表的分片策略，这里只是一个简单的奇数偶数，采用的是 行表达式分片策略 ，需要指定分片键和分片算法表达式（算法支持Groovy的表达式）
• broadcast-tables 来配置广播表（公共表）

五、实现

5.1、持久层

OrderStatus.java

@Data
@Entity
@Table(name = "tb_order_status")
public class OrderStatus implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "status_code")
    private Integer statusCode;

    @Column(name = "status_name")
    private String statusName;

    @Column(name = "create_time", nullable = false, insertable = false, updatable = false)
    private Date createTime;

    @Column(name = "update_time", nullable = false, insertable = false, updatable = false)
    private Date updateTime;

}

Order.java

@Data
@Entity
@Table(name = "tb_order")
public class Order implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "order_id")
    private Long orderId;

    @Column(name = "user_id")
    private Integer userId;

    @Column(name = "price")
    private Integer price;

    @ManyToOne
    @JoinColumn(name = "order_status", referencedColumnName = "status_code", updatable = false, insertable = false)
    private OrderStatus orderStatus;

    @Column(name = "order_status")
    private Integer status;

    @Column(name = "title")
    private String title;

    @Column(name = "order_time")
    private LocalDateTime orderTime;

}

5.2、持久层

OrderRepository.java

public interface OrderRepository extends PagingAndSortingRepository<Order, Long> {

    /**
     * 根据订单id查询订单
     * @param orderId
     * @return
     */
    Order findOrderByOrderId(Long orderId);
}

OrderStatusRepository.java

public interface OrderStatusRepository extends PagingAndSortingRepository<OrderStatus, Long> {

    /**
     * 根据id查询状态
     *
     * @param id
     * @return
     */
    OrderStatus findOrderStatusById(Long id);
}

5.3、服务层

OrderService.java

@Slf4j
@Service
public class OrderService {

    @Autowired
    private OrderRepository orderRepository;

    public void saveOrder(Order order) {
        orderRepository.save(order);
    }

    public Order queryOrder(Long orderId) {
        return orderRepository.findOrderByOrderId(orderId);
    }
}

OrderStatusService.java

@Slf4j
@Service
public class OrderStatusService {

    @Autowired
    private OrderStatusRepository orderStatusRepository;

    public void saveOrderStatus(OrderStatus user) {
        orderStatusRepository.save(user);
    }

    public OrderStatus queryOrderStatus(Long id) {
        return orderStatusRepository.findOrderStatusById(id);
    }
}

5.4、测试类

OrderTests.java

package com.alian.shardingjdbc;

import com.alian.shardingjdbc.domain.Order;
import com.alian.shardingjdbc.service.OrderService;
import lombok.extern.slf4j.Slf4j;
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.SpringJUnit4ClassRunner;

import java.time.LocalDateTime;

@Slf4j
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class OrderTests {

    @Autowired
    private OrderService orderService;

    @Test
    public void saveOrder() {
        for (int i = 0; i < 20; i++) {
            Order order = new Order();
            // 随机生成1000到1006的用户id
            int userId = (int) Math.round(Math.random() * (1006 - 1000) + 1000);
            order.setUserId(userId);
            // 随机生成50到100的金额
            int price = (int) Math.round(Math.random() * (10000 - 5000) + 5000);
            order.setPrice(price);
            order.setStatus(2);
            order.setOrderTime(LocalDateTime.now());
            order.setTitle("");
            orderService.saveOrder(order);
        }
    }

    @Test
    public void queryOrder() {
        Long orderId = 847217235293782017L;
        Order order = orderService.queryOrder(orderId);
        log.info("查询的结果：{}", order);
    }

}

OrderStatusTests.java

@Slf4j
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class OrderStatusTests {

    @Autowired
    private OrderStatusService orderStatusService;

    @Test
    public void saveOrderStatus() {
        OrderStatus orderStatus = new OrderStatus();
        orderStatus.setStatusCode(3);
        orderStatus.setStatusName("已取消");
        orderStatusService.saveOrderStatus(orderStatus);
    }

    @Test
    public void queryOrderStatus() {
        Long orderId = 847213544004280320L;
        OrderStatus orderStatus = orderStatusService.queryOrderStatus(orderId);
        log.info("查询的结果：{}", orderStatus);
    }

}

5.4.1、保存数据

  保存订单就不测试了和我们之前的一样，这里主要讲保存广播表的数据

效果图：

20:44:09 381 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_status (status_code, status_name, id) values (?, ?, ?) ::: [3, 已取消, 847213544004280320]
20:44:09 381 INFO [main]:Actual SQL: ds2 ::: insert into tb_order_status (status_code, status_name, id) values (?, ?, ?) ::: [3, 已取消, 847213544004280320]

  从上面的数据来看，我们插入一条数据到广播表，那么两个库的广播表都会插入相同的数据

5.4.2、查询广播表

21:15:33 984 INFO [main]:Logic SQL: select orderstatu0_.id as id1_1_, orderstatu0_.create_time as create_t2_1_, orderstatu0_.status_code as status_c3_1_, orderstatu0_.status_name as status_n4_1_, orderstatu0_.update_time as update_t5_1_ from tb_order_status orderstatu0_ where orderstatu0_.id=?

21:15:33 984 INFO [main]:Actual SQL: ds1 ::: select orderstatu0_.id as id1_1_, orderstatu0_.create_time as create_t2_1_, orderstatu0_.status_code as status_c3_1_, orderstatu0_.status_name as status_n4_1_, orderstatu0_.update_time as update_t5_1_ from tb_order_status orderstatu0_ where orderstatu0_.id=? ::: [847213544004280320]
21:15:34 023 INFO [main]:查询的结果：OrderStatus(id=847213544004280320, statusCode=3, statusName=已取消, createTime=2023-03-27 12:44:09.0, updateTime=2023-03-27 12:44:09.0)

5.4.3、查询订单数据（关联广播表）

21:10:32 915 INFO [main]:Logic SQL: select order0_.order_id as order_id1_0_, order0_.order_status as order_st4_0_, order0_.order_time as order_ti2_0_, order0_.price as price3_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order order0_ where order0_.order_id=?

21:10:32 916 INFO [main]:Actual SQL: ds1 ::: select order0_.order_id as order_id1_0_, order0_.order_status as order_st4_0_, order0_.order_time as order_ti2_0_, order0_.price as price3_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order_1 order0_ where order0_.order_id=? ::: [847217235293782017]

21:10:32 916 INFO [main]:Actual SQL: ds2 ::: select order0_.order_id as order_id1_0_, order0_.order_status as order_st4_0_, order0_.order_time as order_ti2_0_, order0_.price as price3_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order_1 order0_ where order0_.order_id=? ::: [847217235293782017]

21:10:32 966 INFO [main]:Logic SQL: select orderstatu0_.id as id1_1_0_, orderstatu0_.create_time as create_t2_1_0_, orderstatu0_.status_code as status_c3_1_0_, orderstatu0_.status_name as status_n4_1_0_, orderstatu0_.update_time as update_t5_1_0_ from tb_order_status orderstatu0_ where orderstatu0_.status_code=?

21:10:32 966 INFO [main]:Actual SQL: ds2 ::: select orderstatu0_.id as id1_1_0_, orderstatu0_.create_time as create_t2_1_0_, orderstatu0_.status_code as status_c3_1_0_, orderstatu0_.status_name as status_n4_1_0_, orderstatu0_.update_time as update_t5_1_0_ from tb_order_status orderstatu0_ where orderstatu0_.status_code=? ::: [2]

21:10:32 979 INFO [main]:查询的结果：Order(orderId=847217235293782017, userId=1004, price=8508, orderStatus=OrderStatus(id=847211414187040768, statusCode=2, statusName=已付款, createTime=2023-03-27 12:35:41.0, updateTime=2023-03-27 12:35:41.0), status=2, title=, orderTime=2023-03-27T20:58:49)

  从上面的结果我们可以看到当我们查询order_id为 847217235293782017 的记录时，因为我们之前是按 user_id 进行的分库，这里没有指定所以就是查询两个库，查询到订单记录后，关联查询订单状态值，就直接连当前库的广播表了，就只用查一次了。