Sharding-JDBC分库分表-入门-1

Alexon Xu

已于 2023-09-02 22:49:13 修改

阅读量206

点赞数

分类专栏：分库分表文章标签： sharding-jdbc5 分库分表大数据

于 2023-08-29 23:38:44 首次发布

本文链接：https://blog.csdn.net/shuoyueqishilove/article/details/132572204

版权

分库分表专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Sharding Sphere简介与参考

Apache ShardingSphere 是一款分布式 SQL 事务和查询引擎，可通过数据分片、弹性伸缩、加密等能力对任意数据库进行增强

官网：https://shardingsphere.apache.org/index_zh.html

官方样例：https://shardingsphere.apache.org/blog/cn/material/oct_12_4_updates_and_faq_your_1_minute_quick_start_guide_to_shardingsphere/

Sharding-JDBC使用

目前有三种主流的maven坐标，推荐使用第二种

<!-- 老项目经常使用的版本，不再更新，有些特性不支持，资料多，上手难度小 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

<!-- 最近的Spring-Boot版本，迭代未知，上手难度一般 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version>
</dependency>

<!-- 官方最新正式版本，上手难度比较大，主要是官方的example比较难运行 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>5.4.0</version>
</dependency>

引入坐标

主要组件版本

组件	版本号
spring-boot-starter-parent	2.6.11
shardingsphere-jdbc-core-spring-boot-starter	5.2.1
mybatis-plus-boot-starter	3.4.1
druid-spring-boot-starter	1.2.9

<?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>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.11</version>
        <relativePath/>
    </parent>
   
    <modelVersion>4.0.0</modelVersion>
    
    <properties>
        <java.version>1.8</java.version>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <spring-boot.version>2.6.11</spring-boot.version>
    </properties>
    
    
    <dependencies>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.2.1</version>
        </dependency>

        <!--解决yaml读取问题：https://github.com/apache/shardingsphere/issues/21476-->
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.33</version>
        </dependency>
        
        <dependency>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter-web</artifactId>
             <version>${spring-boot.version}</version>
         </dependency>
        
         <dependency>
              <groupId>com.baomidou</groupId>
              <artifactId>mybatis-plus-boot-starter</artifactId>
              <version>3.4.1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.9</version>
        </dependency>
        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>

        <dependency>
            <groupId>com.github.javafaker</groupId>
            <artifactId>javafaker</artifactId>
            <version>1.0.2</version>
        </dependency>

        <dependency>
            <groupId>com.github.jsonzou</groupId>
            <artifactId>jmockdata</artifactId>
            <version>4.3.0</version>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

数据分片设计

新建三个数据库，同时创建订单表order_t

CREATE TABLE `order_t` (
  `order_id` bigint(20) NOT NULL COMMENT 'order_id主键',
  `order_no` varchar(32) DEFAULT NULL COMMENT '订单编号',
  `user_id` bigint(10) NOT NULL COMMENT '用户ID',
  `order_date` date NOT NULL COMMENT '下单时间',
  `order_amount` decimal(16,2) NOT NULL COMMENT '订单金额',
  `delivery_amount` decimal(16,2) DEFAULT '0.00' COMMENT '运费',
  `total_amount` decimal(16,2) NOT NULL COMMENT '汇总金额',
  `receiver_id` bigint(10) NOT NULL COMMENT '收货地址ID',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态，1：已提交，2：已付款，3：待发货，4：已发货，5：已收货，6：已完成',
  `deleted` tinyint(4) DEFAULT '0' COMMENT '删除标志，0：未删除，1：已删除',
  `create_by` bigint(10) DEFAULT NULL COMMENT '创建人',
  `creation_date` datetime DEFAULT NULL COMMENT '创建时间',
  `last_update_by` bigint(10) DEFAULT NULL COMMENT '修改人',
  `last_update_date` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`order_id`),
  KEY `idx_useId` (`user_id`),
  KEY `idx_orderNo` (`order_no`),
  KEY `idx_orderDate` (`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

表的分布如下

order
    order_t
order1
    order_t
order2
    order_t

MyBatis-plus相关

BasePo

@Data
@AllArgsConstructor
@NoArgsConstructor
public class BasePo implements Serializable {
    private static final long serialVersionUID = -1113179217211499942L;
    /**
     * 创建时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @TableField("creation_date")
    protected Date creationDate;

    /**
     * 创建人
     */
    @TableField("create_by")
    protected Long createBy;

    /**
     * 最近更新时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @TableField("last_update_date")
    protected Date lastUpdateDate;

    /**
     * 更新人
     */
    @TableField("last_update_by")
    protected Long lastUpdateBy;

    /**
     * 设置创建人和创建时间
     *
     * @param basePoObj basePoObj的子类对象
     * @param <T>           对象类型
     */
    public <T extends BasePo> void setUserAndTime(T basePoObj) {
        this.createBy = basePoObj.getCreateBy();
        this.creationDate = basePoObj.getCreationDate();
        this.lastUpdateBy = basePoObj.getLastUpdateBy();
        this.lastUpdateDate = basePoObj.getLastUpdateDate();
    }
}

OrderPo

@EqualsAndHashCode(callSuper = true)
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@TableName("order_t")
public class OrderPo extends BasePo implements Serializable {
    private static final long serialVersionUID = 1337475900522675773L;

    @TableId(value = "order_id",type = IdType.AUTO)
    private Long orderId;

    @TableField("order_no")
    private String orderNo;

    @TableField("order_date")
    private Date orderDate;

    @TableField("user_id")
    private Long userId;

    @TableField("receiver_id")
    private Long receiverId;

    @TableField("order_amount")
    private BigDecimal orderAmount;

    @TableField("delivery_amount")
    private BigDecimal deliveryAmount;

    @TableField("total_amount")
    private BigDecimal totalAmount;

    @TableField("status")
    private Integer status;

    @TableField("deleted")
    private Integer deleted;
}

Mapper

public interface IOrderMapper extends BaseMapper<OrderPo> {
}

分库分表配置

分库分表策略：根据user_id字段分片，将数据按user_id对3取余，均分到ds0、ds1、ds2；核心配置如下：

spring:
  shardingsphere:
    mode:
      type: Standalone  # 可选：Standalone、Cluster
    props:
      sql-show: true # 日志打印SQL
    datasource:  #定义数据源
      names: ds0,ds1,ds2 # 数据库名称列表
      ds0: 
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=false
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order_1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=false
        username: root
        password: root
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order_2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=false
        username: root
        password: root
    rules:
      sharding:
        tables: # 需要分库表的规则配置
          order_t:
            actual-data-nodes: ds$->{0..2}.order_t  # 待选数据节点：ds0.order_t、ds1.order_t、ds2.order_t
            key-generate-strategy:  # 分布式ID列，一般是主键
              column: order_id
              key-generator-name: snowflake # 使用雪花算法
            database-strategy: # 分库策略配置
              standard: # 标准算法，可选：standard、complex、hint、none
                sharding-column: user_id # 分片列
                sharding-algorithm-name: order_inline # 分片算法
        key-generators: # 分布式ID生成算法
          snowflake:
            type: SNOWFLAKE  # 内置雪花算法，参考：org.apache.shardingsphere.sharding.algorithm.keygen.SnowflakeKeyGenerateAlgorithm
        sharding-algorithms: # 分片算法，配置后可以在分片表的分片策略中被引用
          default_database_inline: # 算法名称
            type: INLINE # 行内表达式算法
            props:
              algorithm-expression: ds$->{created_by % 3} # 将数据按created_by对3取余，均分到ds0、ds1、ds2
          order_inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 3} # 将数据按user_id对3取余，均分到ds0、ds1、ds2
        default-database-strategy: # 默认分库算法
          standard:
            sharding-column: created_by
            sharding-algorithm-name: default_database_inline

测试用例

@SpringBootTest
@Slf4j
public class ShardingJdbcTest {

    @Autowired
    private IOrderMapper mapper;

    @Test
    public void addOrder() {
        for (int i = 0; i < 10; i++) {
            OrderPo orderPo = JMockData.mock(OrderPo.class);
            orderPo.setOrderId((long) i);
            orderPo.setStatus(1);
            orderPo.setDeleted(0);
            Faker faker = new Faker();
            Calendar fromDate = Calendar.getInstance();
            fromDate.set(2022, Calendar.JANUARY, 1);
            Calendar toDate = Calendar.getInstance();
            toDate.set(2032, Calendar.DECEMBER, 31);
            orderPo.setOrderDate(faker.date().between(fromDate.getTime(), toDate.getTime()));
            mapper.insert(orderPo);
        }
    }
}

Alexon Xu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Sharding-JDBC分库分表-入门-1

Apache ShardingSphere 是一款分布式 SQL 事务和查询引擎，可通过数据分片、弹性伸缩、加密等能力对任意数据库进行增强官网：https://shardingsphere.apache.org/index_zh.html官方样例：https://shardingsphere.apache.org/blog/cn/material/oct_12_4_updates_and_faq_your_1_minute_quick_start_guide_to_shardingsphere/
复制链接

扫一扫