Sharding-JDBC分布式分库分表实战指南：从原理到生产落地

<!-- Spring Boot核心依赖 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<!-- Sharding-JDBC核心依赖（分库分表+读写分离） -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

<!-- MyBatis-Plus（简化CRUD） -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>

<!-- MySQL驱动 -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- Lombok + 连接池 -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.16</version>
</dependency>

CREATE TABLE `t_order_2024` (
  `id` bigint NOT NULL COMMENT '订单ID（雪花算法生成）',
  `order_no` varchar(64) NOT NULL COMMENT '订单编号',
  `user_id` bigint NOT NULL COMMENT '用户ID（分片键）',
  `amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `status` tinyint NOT NULL COMMENT '订单状态',
  `create_time` datetime NOT NULL COMMENT '创建时间（分片键）',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- t_order_2025表结构与t_order_2024一致，仅表名不同

spring:
  shardingsphere:
    # 数据源配置（主从+分库）
    datasource:
      names: order-db0-master, order-db0-slave, order-db1-master, order-db1-slave
      # 订单库0-主库
      order-db0-master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order_db_0?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      # 订单库0-从库
      order-db0-slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3307/order_db_0?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      # 订单库1-主库
      order-db1-master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order_db_1?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      # 订单库1-从库
      order-db1-slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3307/order_db_1?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456

    # 分库分表规则配置
    rules:
      sharding:
        # 分库策略（按user_id哈希分2个库）
        databases:
          - database-name: order_db_${0..1}
            table-rules:
              - table-name: t_order_${2024..2025}
                # 分片键配置
                sharding-column: user_id
                # 分库算法（哈希取模）
                database-strategy:
                  standard:
                    sharding-algorithm-name: user-id-hash-algorithm
                # 分表策略（按create_time范围分表）
                table-strategy:
                  standard:
                    sharding-column: create_time
                    sharding-algorithm-name: create-time-range-algorithm

        # 分片算法配置
        sharding-algorithms:
          # 用户ID哈希算法（分2个库）
          user-id-hash-algorithm:
            type: HASH_MOD
            props:
              sharding-count: 2
          # 时间范围算法（2024年、2025年分表）
          create-time-range-algorithm:
            type: RANGE
            props:
              range-lower: '2024-01-01 00:00:00'
              range-upper: '2026-01-01 00:00:00'
              sharding-suffix-pattern: yyyy

        # 读写分离配置
        readwrite-splitting:
          data-sources:
            # 订单库0主从映射
            order-db0:
              type: Static
              props:
                write-data-source-name: order-db0-master
                read-data-source-names: order-db0-slave
                load-balancer-name: round_robin
            # 订单库1主从映射
            order-db1:
              type: Static
              props:
                write-data-source-name: order-db1-master
                read-data-source-names: order-db1-slave
                load-balancer-name: round_robin

          # 负载均衡策略（轮询）
          load-balancers:
            round_robin:
              type: ROUND_ROBIN

    # 其他配置
    props:
      sql-show: true # 打印解析后的SQL，便于调试
      check-table-metadata-enabled: false # 关闭表元数据校验（分表场景需关闭）

# MyBatis-Plus配置
mybatis-plus:
  mapper-locations: classpath:mapper/**/*.xml
  type-aliases-package: com.example.sharding.entity
  configuration:
    map-underscore-to-camel-case: true

// 订单实体（对应t_order_2024、t_order_2025表）
package com.example.sharding.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Data
@TableName("t_order") // 逻辑表名，Sharding-JDBC自动映射到实际分表
public class Order {
    private Long id;
    private String orderNo;
    private Long userId; // 分库键
    private BigDecimal amount;
    private Integer status;
    private LocalDateTime createTime; // 分表键
}

// 订单Mapper（MyBatis-Plus接口）
package com.example.sharding.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.sharding.entity.Order;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    // 无需额外方法，BaseMapper已提供CRUD能力
}

package com.example.sharding.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.sharding.entity.Order;
import com.example.sharding.mapper.OrderMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.time.LocalDateTime;
import java.util.List;
import java.util.UUID;

@Service
@Slf4j
public class OrderService {

    @Resource
    private OrderMapper orderMapper;

    /**
     * 创建订单：自动路由到对应库表（按user_id分库、create_time分表）
     */
    @Transactional(rollbackFor = Exception.class)
    public void createOrder(Order order) {
        order.setOrderNo(UUID.randomUUID().toString().replace("-", ""));
        order.setCreateTime(LocalDateTime.now());
        orderMapper.insert(order);
        log.info("订单创建成功，ID：{}，用户ID：{}", order.getId(), order.getUserId());
    }

    /**
     * 按用户ID查询订单：自动路由到对应库，合并多表结果
     */
    public List<Order> getOrdersByUserId(Long userId) {
        QueryWrapper<Order> wrapper = new QueryWrapper<>();
        wrapper.eq("user_id", userId);
        return orderMapper.selectList(wrapper);
    }

    /**
     * 按时间范围查询订单：自动路由到对应分表
     */
    public List<Order> getOrdersByTimeRange(LocalDateTime startTime, LocalDateTime endTime) {
        QueryWrapper<Order> wrapper = new QueryWrapper<>();
        wrapper.between("create_time", startTime, endTime);
        return orderMapper.selectList(wrapper);
    }
}

@SpringBootTest
public class ShardingTest {

    @Resource
    private OrderService orderService;

    @Test
    public void testCreateOrder() {
        // 测试数据：user_id=1（哈希分库0）、create_time=2024年（分表2024）
        Order order1 = new Order();
        order1.setUserId(1L);
        order1.setAmount(new BigDecimal("99.00"));
        order1.setStatus(1);
        orderService.createOrder(order1);

        // 测试数据：user_id=2（哈希分库1）、create_time=2025年（分表2025）
        Order order2 = new Order();
        order2.setUserId(2L);
        order2.setAmount(new BigDecimal("199.00"));
        order2.setStatus(1);
        // 手动设置2025年时间
        order2.setCreateTime(LocalDateTime.of(2025, 1, 10, 10, 30));
        orderService.createOrder(order2);
    }

// Mapper接口添加强制主库查询方法
@Select("/* SHARDINGSPHERE_ROUTE_TO_MASTER */ SELECT * FROM t_order WHERE user_id = #{userId}")
List<Order> selectByUserIdMaster(@Param("userId") Long userId);