Java数据库分库分表案例

本文目录导读：

1. 项目结构
2. 核心代码实现
3. 数据库初始化脚本
4. 测试用例
5. 分片测试数据验证
6. 注意事项

我来为你提供一个完整的Java分库分表案例,使用最流行的ShardingSphere框架。

1 技术栈

• Spring Boot 2.7.x
• MyBatis Plus 3.5.x
• ShardingSphere-JDBC 5.2.x
• MySQL 8.0
• HikariCP 连接池

2 分片方案

• 分库策略：根据用户ID取模，分成2个库
• 分表策略：根据订单ID取模，每个库分成2张表
• 广播表：配置字典表，所有库同步
• 绑定表：订单表和订单详情表关联分片

项目结构

sharding-demo/
├── pom.xml
├── src/main/java/
│   └── com/example/sharding/
│       ├── ShardingApplication.java
│       ├── config/
│       │   └── ShardingConfig.java
│       ├── controller/
│       │   └── OrderController.java
│       ├── entity/
│       │   ├── Order.java
│       │   ├── OrderItem.java
│       │   └── Dict.java
│       ├── mapper/
│       │   ├── OrderMapper.java
│       │   ├── OrderItemMapper.java
│       │   └── DictMapper.java
│       └── service/
│           └── OrderService.java
└── src/main/resources/
    └── application.yml

核心代码实现

1 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 
         http://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.7.14</version>
    </parent>
    <groupId>com.example</groupId>
    <artifactId>sharding-demo</artifactId>
    <version>1.0.0</version>
    <properties>
        <java.version>1.8</java.version>
        <sharding-jdbc.version>5.2.1</sharding-jdbc.version>
        <mybatis-plus.version>3.5.3</mybatis-plus.version>
    </properties>
    <dependencies>
        <!-- Spring Boot Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- MyBatis Plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <!-- ShardingSphere JDBC -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>${sharding-jdbc.version}</version>
        </dependency>
        <!-- MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>
</project>

2 application.yml

server:
  port: 8080
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      # 数据源0
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/sharding_db0?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: root123
        max-pool-size: 50
        min-pool-size: 5
      # 数据源1
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/sharding_db1?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: root123
        max-pool-size: 50
        min-pool-size: 5
    rules:
      sharding:
        # 分片算法配置
        sharding-algorithms:
          # 数据库分片算法
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 2}
          # 订单表分片算法
          order-table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id % 2}
          # 订单详情表分片算法
          order-item-table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_item_$->{order_id % 2}
        # 绑定表配置
        binding-tables:
          - t_order,t_order_item
        # 广播表配置
        broadcast-tables:
          - t_dict
        # 表配置
        tables:
          # 订单表
          t_order:
            actual-data-nodes: ds$->{0..1}.t_order_$->{0..1}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: database-inline
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-table-inline
          # 订单详情表
          t_order_item:
            actual-data-nodes: ds$->{0..1}.t_order_item_$->{0..1}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: database-inline
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-item-table-inline
          # 字典表（广播表）
          t_dict:
            actual-data-nodes: ds$->{0..1}.t_dict
    # 属性配置
    props:
      sql-show: true  # 显示SQL
# MyBatis Plus 配置
mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      logic-delete-field: deleted
      logic-delete-value: 1
      logic-not-delete-value: 0

3 实体类

// Order.java
@Data
@TableName("t_order")
public class Order {
    @TableId(type = IdType.INPUT)
    private Long orderId;
    private Long userId;
    private BigDecimal totalAmount;
    private Integer status;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
    @TableLogic
    private Integer deleted;
}
// OrderItem.java
@Data
@TableName("t_order_item")
public class OrderItem {
    @TableId(type = IdType.INPUT)
    private Long itemId;
    private Long orderId;
    private Long userId;
    private Long productId;
    private String productName;
    private Integer quantity;
    private BigDecimal price;
    private LocalDateTime createTime;
    @TableLogic
    private Integer deleted;
}
// Dict.java（广播表）
@Data
@TableName("t_dict")
public class Dict {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String dictType;
    private String dictCode;
    private String dictValue;
    private Integer status;
}

4 Mapper层

// OrderMapper.java
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    // 自定义分页查询
    IPage<Order> selectOrderPage(Page<Order> page, @Param("userId") Long userId);
    // 统计用户订单数
    @Select("SELECT COUNT(*) FROM t_order WHERE user_id = #{userId}")
    Long countByUserId(@Param("userId") Long userId);
}
// OrderItemMapper.java
@Mapper
public interface OrderItemMapper extends BaseMapper<OrderItem> {
    // 根据订单ID查询订单详情
    @Select("SELECT * FROM t_order_item WHERE order_id = #{orderId}")
    List<OrderItem> selectByOrderId(@Param("orderId") Long orderId);
}
// DictMapper.java
@Mapper
public interface DictMapper extends BaseMapper<Dict> {
    // 根据类型查字典
    @Select("SELECT * FROM t_dict WHERE dict_type = #{dictType} AND status = 1")
    List<Dict> selectByType(@Param("dictType") String dictType);
}

5 Service层

@Service
public class OrderService {
    @Resource
    private OrderMapper orderMapper;
    @Resource
    private OrderItemMapper orderItemMapper;
    /**
     * 创建订单（包含订单详情）
     */
    @Transactional(rollbackFor = Exception.class)
    public Order createOrder(Order order, List<OrderItem> items) {
        // 生成订单ID
        Long orderId = IdGenerator.generateId();
        order.setOrderId(orderId);
        order.setStatus(1);
        order.setCreateTime(LocalDateTime.now());
        order.setUpdateTime(LocalDateTime.now());
        order.setDeleted(0);
        // 插入订单
        orderMapper.insert(order);
        // 插入订单详情
        for (OrderItem item : items) {
            item.setItemId(IdGenerator.generateId());
            item.setOrderId(orderId);
            item.setUserId(order.getUserId());
            item.setCreateTime(LocalDateTime.now());
            itemMapper.insert(item);
        }
        return order;
    }
    /**
     * 查询用户订单列表（分页）
     */
    public IPage<Order> getUserOrders(Long userId, int page, int size) {
        Page<Order> orderPage = new Page<>(page, size);
        IPage<Order> result = orderMapper.selectPage(orderPage,
            new LambdaQueryWrapper<Order>()
                .eq(Order::getUserId, userId)
                .orderByDesc(Order::getCreateTime));
        // 遍历订单，查询详情
        if (result.getRecords() != null && !result.getRecords().isEmpty()) {
            for (Order order : result.getRecords()) {
                List<OrderItem> items = orderItemMapper.selectByOrderId(order.getOrderId());
                order.setItems(items);
            }
        }
        return result;
    }
    /**
     * 订单统计
     */
    public Map<String, Object> getOrderStatistics(Long userId) {
        Map<String, Object> result = new HashMap<>();
        // 订单总数
        Long orderCount = orderMapper.countByUserId(userId);
        result.put("orderCount", orderCount);
        // 订单总金额
        List<Order> orders = orderMapper.selectList(
            new LambdaQueryWrapper<Order>()
                .eq(Order::getUserId, userId)
                .eq(Order::getStatus, 1)
        );
        BigDecimal totalAmount = orders.stream()
            .map(Order::getTotalAmount)
            .reduce(BigDecimal.ZERO, BigDecimal::add);
        result.put("totalAmount", totalAmount);
        return result;
    }
    /**
     * ID生成器（简化版，生产环境建议使用雪花算法）
     */
    public static class IdGenerator {
        private static final AtomicLong ID = new AtomicLong(1);
        public static Long generateId() {
            return System.currentTimeMillis() * 1000000 + ID.getAndIncrement();
        }
    }
}

6 Controller层

@RestController
@RequestMapping("/api/order")
public class OrderController {
    @Resource
    private OrderService orderService;
    /**
     * 创建订单
     */
    @PostMapping("/create")
    public Result createOrder(@RequestBody CreateOrderRequest request) {
        Order order = new Order();
        order.setUserId(request.getUserId());
        order.setTotalAmount(request.getTotalAmount());
        List<OrderItem> items = request.getItems().stream().map(item -> {
            OrderItem orderItem = new OrderItem();
            orderItem.setProductId(item.getProductId());
            orderItem.setProductName(item.getProductName());
            orderItem.setQuantity(item.getQuantity());
            orderItem.setPrice(item.getPrice());
            return orderItem;
        }).collect(Collectors.toList());
        Order result = orderService.createOrder(order, items);
        return Result.success("下单成功", result);
    }
    /**
     * 查询用户订单
     */
    @GetMapping("/user/{userId}")
    public Result getUserOrders(@PathVariable Long userId,
                                @RequestParam(defaultValue = "1") int page,
                                @RequestParam(defaultValue = "10") int size) {
        IPage<Order> orders = orderService.getUserOrders(userId, page, size);
        return Result.success("查询成功", orders);
    }
    /**
     * 订单统计
     */
    @GetMapping("/statistics/{userId}")
    public Result getStatistics(@PathVariable Long userId) {
        Map<String, Object> statistics = orderService.getOrderStatistics(userId);
        return Result.success("统计成功", statistics);
    }
}
// 请求体类
@Data
public class CreateOrderRequest {
    private Long userId;
    private BigDecimal totalAmount;
    private List<OrderItemRequest> items;
}
@Data
public class OrderItemRequest {
    private Long productId;
    private String productName;
    private Integer quantity;
    private BigDecimal price;
}
// 统一响应类
@Data
public class Result {
    private Integer code;
    private String message;
    private Object data;
    public static Result success(String message, Object data) {
        Result result = new Result();
        result.setCode(200);
        result.setMessage(message);
        result.setData(data);
        return result;
    }
}

数据库初始化脚本

-- 创建数据库
CREATE DATABASE IF NOT EXISTS sharding_db0 DEFAULT CHARACTER SET utf8mb4;
CREATE DATABASE IF NOT EXISTS sharding_db1 DEFAULT CHARACTER SET utf8mb4;
-- sharding_db0 表结构
USE sharding_db0;
-- 订单表
CREATE TABLE IF NOT EXISTS t_order_0 (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status INT DEFAULT 1,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS t_order_1 LIKE t_order_0;
-- 订单详情表
CREATE TABLE IF NOT EXISTS t_order_item_0 (
    item_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(100),
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    INDEX idx_order_id (order_id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS t_order_item_1 LIKE t_order_item_0;
-- 字典表（广播表）
CREATE TABLE IF NOT EXISTS t_dict (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    dict_type VARCHAR(50) NOT NULL,
    dict_code VARCHAR(50) NOT NULL,
    dict_value VARCHAR(100) NOT NULL,
    status TINYINT DEFAULT 1,
    UNIQUE KEY uk_type_code (dict_type, dict_code)
) ENGINE=InnoDB;
-- 初始化字典数据
INSERT INTO t_dict (dict_type, dict_code, dict_value) VALUES
('ORDER_STATUS', '1', '待支付'),
('ORDER_STATUS', '2', '已支付'),
('ORDER_STATUS', '3', '已发货'),
('ORDER_STATUS', '4', '已完成');
-- sharding_db1 表结构（与 db0 相同）
USE sharding_db1;
CREATE TABLE IF NOT EXISTS t_order_0 LIKE sharding_db0.t_order_0;
CREATE TABLE IF NOT EXISTS t_order_1 LIKE sharding_db0.t_order_1;
CREATE TABLE IF NOT EXISTS t_order_item_0 LIKE sharding_db0.t_order_item_0;
CREATE TABLE IF NOT EXISTS t_order_item_1 LIKE sharding_db0.t_order_item_1;
-- 广播表需要手动创建并同步数据
CREATE TABLE IF NOT EXISTS t_dict (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    dict_type VARCHAR(50) NOT NULL,
    dict_code VARCHAR(50) NOT NULL,
    dict_value VARCHAR(100) NOT NULL,
    status TINYINT DEFAULT 1,
    UNIQUE KEY uk_type_code (dict_type, dict_code)
) ENGINE=InnoDB;
INSERT INTO t_dict (dict_type, dict_code, dict_value) VALUES
('ORDER_STATUS', '1', '待支付'),
('ORDER_STATUS', '2', '已支付'),
('ORDER_STATUS', '3', '已发货'),
('ORDER_STATUS', '4', '已完成');

测试用例

@SpringBootTest
@RunWith(SpringRunner.class)
public class OrderServiceTest {
    @Resource
    private OrderService orderService;
    @Test
    public void testCreateOrder() {
        // 创建订单
        Order order = new Order();
        order.setUserId(1001L);
        order.setTotalAmount(new BigDecimal("1999.00"));
        List<OrderItem> items = new ArrayList<>();
        OrderItem item1 = new OrderItem();
        item1.setProductId(1L);
        item1.setProductName("iPhone 15");
        item1.setQuantity(1);
        item1.setPrice(new BigDecimal("5999.00"));
        items.add(item1);
        OrderItem item2 = new OrderItem();
        item2.setProductId(2L);
        item2.setProductName("AirPods Pro");
        item2.setQuantity(1);
        item2.setPrice(new BigDecimal("1899.00"));
        items.add(item2);
        Order result = orderService.createOrder(order, items);
        System.out.println("订单创建成功：" + result);
    }
    @Test
    public void testQueryOrders() {
        // 查询用户订单
        IPage<Order> orders = orderService.getUserOrders(1001L, 1, 10);
        System.out.println("订单总数：" + orders.getTotal());
        orders.getRecords().forEach(order -> {
            System.out.println("订单号：" + order.getOrderId() +
                             ", 金额：" + order.getTotalAmount() +
                             ", 商品数：" + order.getItems().size());
        });
    }
    @Test
    public void testDataDistribution() {
        // 测试数据分布
        List<Long> userIds = Arrays.asList(1L, 2L, 3L, 4L, 5L, 6L);
        for (Long userId : userIds) {
            System.out.println("用户 " + userId + " -> 数据库 " + (userId % 2));
        }
    }
}

分片测试数据验证

-- 验证分库效果
USE sharding_db0;
SELECT 'db0' AS database_name, COUNT(*) AS order_count FROM t_order_0;
SELECT 'db0' AS database_name, COUNT(*) AS order_count FROM t_order_1;
USE sharding_db1;
SELECT 'db1' AS database_name, COUNT(*) AS order_count FROM t_order_0;
SELECT 'db1' AS database_name, COUNT(*) AS order_count FROM t_order_1;
-- 查询某个用户的订单（会自动路由到对应分片）
-- 用户ID=1001 应路由到 ds1
EXPLAIN SELECT * FROM t_order WHERE user_id = 1001 AND order_id = 123456;

注意事项

1 分片键选择

• 必须包含分片键的查询才能路由到正确分片
• 全表扫描会广播到所有分片

2 性能优化建议

• 合理设计分片数量（建议2的幂次方）
• 使用绑定表避免跨库join
• 避免分布式事务（可使用柔性事务）

3 常见问题

• 分布式ID：使用雪花算法或号段模式
• 数据迁移：使用elastic-job等工具
• 跨分片查询：使用ShardingSphere的联邦查询或汇总层

这个案例涵盖了分库分表的核心实现,你可以根据实际业务需求进行调整和扩展。