Spring Boot + ShardingSphere 分库分表实战：电商订单场景案例

码里看花‌

已于 2025-04-14 15:37:33 修改

阅读量681

点赞数 10

文章标签： spring boot 数据库后端

于 2025-04-14 15:26:17 首次发布

本文链接：https://blog.csdn.net/hellojava6666/article/details/147224142

版权

摘要：本文通过电商系统中订单表分库分表的实际案例，结合Spring Boot和ShardingSphere框架，详细讲解如何实现水平分库分表，解决海量数据存储与查询性能问题。

一、场景分析

在电商系统中，订单表随着业务增长可能面临以下问题：

单表数据量超过500万，查询性能下降
高频写入导致数据库负载过高
存储空间受单机限制

解决方案：采用水平分库分表，将订单表拆分到多个数据库实例的多个表中。

二、技术选型

Spring Boot 3.1.6：快速构建项目
ShardingSphere-JDBC 5.3.2：分库分表中间件
MySQL 8.0：数据库
MyBatis-Plus 3.5.3.1：ORM框架

三、项目搭建

1. 数据库准备

创建两个数据库，每个库包含两个订单表：

-- 数据库ds0
CREATE TABLE orders_0 (order_id BIGINT PRIMARY KEY, user_id INT, amount DECIMAL(10,2));
CREATE TABLE orders_1 (order_id BIGINT PRIMARY KEY, user_id INT, amount DECIMAL(10,2));

-- 数据库ds1
CREATE TABLE orders_0 (order_id BIGINT PRIMARY KEY, user_id INT, amount DECIMAL(10,2));
CREATE TABLE orders_1 (order_id BIGINT PRIMARY KEY, user_id INT, amount DECIMAL(10,2));

2. Maven依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>

四、核心配置

application.yml 配置分片规则：

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: root
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds1
        username: root
        password: root
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds$->{0..1}.orders_$->{0..1}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: db-sharding
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: table-sharding
            key-generate-strategy:
              column: order_id
              key-generator-name: snowflake
        sharding-algorithms:
          db-sharding:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 2}
          table-sharding:
            type: INLINE
            props:
              algorithm-expression: orders_$->{user_id % 4 / 2}
        key-generators:
          snowflake:
            type: SNOWFLAKE

分片策略说明：

数据库分片：user_id % 2 决定使用哪个数据库
表分片：user_id % 4 / 2 将数据分布到4个表中

五、代码实现

1. 订单实体类

@Data
@TableName("orders")
public class Order {
    @TableId(type = IdType.ASSIGN_ID)
    private Long orderId;
    private Integer userId;
    private BigDecimal amount;
    private LocalDateTime createTime;
}

2. Mapper接口

public interface OrderMapper extends BaseMapper<Order> {
    @Select("SELECT * FROM orders WHERE user_id = #{userId}")
    List<Order> selectByUserId(@Param("userId") Integer userId);
}

3. 测试用例

@SpringBootTest
class ShardingTest {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    void testInsert() {
        for (int i = 1; i <= 100; i++) {
            Order order = new Order();
            order.setUserId(i);  // user_id决定分片
            order.setAmount(new BigDecimal("100.00"));
            orderMapper.insert(order);
        }
    }

    @Test
    void testQuery() {
        List<Order> orders = orderMapper.selectByUserId(5);
        System.out.println("查询结果：" + orders.size());
    }
}