ShardingSphere分库分表案例

wen java案例 2026-08-10 212

本文目录导读：

项目背景
Maven依赖配置
数据库初始化SQL
ShardingSphere核心配置（application.yml）
实体类和Mapper
Service和Controller
复杂查询场景配置
最佳实践与注意事项
生产环境建议

我为您整理一个完整的ShardingSphere分库分表实战案例，涵盖核心配置、代码示例和最佳实践。

项目背景

假设我们有一个电商系统，订单表（t_order）数据量巨大,需要：

分库：按用户ID取模分到2个数据库
分表：在每个库中按订单ID取模分到2张表

这样总共2库×2表=4个分片。

Maven依赖配置

<!-- Spring Boot Starter -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.7.14</version>
</dependency>
<!-- ShardingSphere-JDBC 核心依赖 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.3.2</version>
</dependency>
<!-- MyBatis-Plus -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>
<!-- MySQL 驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

数据库初始化SQL

-- 创建数据库 ds0
CREATE DATABASE ds0 CHARACTER SET utf8mb4;
USE ds0;
-- 订单表 t_order_0
CREATE TABLE `t_order_0` (
  `order_id` BIGINT NOT NULL,
  `user_id` BIGINT NOT NULL,
  `order_amount` DECIMAL(10,2) DEFAULT NULL,
  `order_status` VARCHAR(50) DEFAULT NULL,
  `create_time` DATETIME DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 订单表 t_order_1
CREATE TABLE `t_order_1` LIKE `t_order_0`;
-- 创建数据库 ds1
CREATE DATABASE ds1 CHARACTER SET utf8mb4;
USE ds1;
CREATE TABLE `t_order_0` LIKE `ds0`.`t_order_0`;
CREATE TABLE `t_order_1` LIKE `ds0`.`t_order_0`;

ShardingSphere核心配置（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?useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root123
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds1?useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root123
    # 分片规则配置
    rules:
      sharding:
        tables:
          # 逻辑表名
          t_order:
            # 实际物理表：均匀分布在两个库中
            actual-data-nodes: ds$->{0..1}.t_order_$->{0..1}
            # 分库策略：按user_id取模
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: database-inline
            # 分表策略：按order_id取模
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: table-inline
        # 分片算法定义
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 2}
          table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id % 2}
    # 显示SQL日志
    props:
      sql-show: true

实体类和Mapper

订单实体类

package com.example.entity;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.math.BigDecimal;
import java.time.LocalDateTime;
@Data
@TableName("t_order") // 逻辑表名
public class Order {
    @TableId
    private Long orderId;
    private Long userId;
    private BigDecimal orderAmount;
    private String orderStatus;
    private LocalDateTime createTime;
}

Mapper接口

package com.example.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    // 自定义SQL查询
    Order selectByOrderIdAndUserId(@Param("orderId") Long orderId, 
                                    @Param("userId") Long userId);
}

Mapper XML（如果要自定义SQL）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.OrderMapper">
    <select id="selectByOrderIdAndUserId" resultType="com.example.entity.Order">
        SELECT order_id, user_id, order_amount, order_status, create_time
        FROM t_order
        WHERE order_id = #{orderId} AND user_id = #{userId}
    </select>
</mapper>

Service和Controller

服务层

package com.example.service;
import com.example.entity.Order;
import com.example.mapper.OrderMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.math.BigDecimal;
import java.time.LocalDateTime;
@Service
public class OrderService {
    @Autowired
    private OrderMapper orderMapper;
    /**
     * 插入订单 - 自动根据分片规则路由
     */
    public Order insertOrder(Long userId, BigDecimal amount) {
        Order order = new Order();
        order.setOrderId(System.currentTimeMillis()); // 使用时间戳作为ID
        order.setUserId(userId);
        order.setOrderAmount(amount);
        order.setOrderStatus("PENDING");
        order.setCreateTime(LocalDateTime.now());
        orderMapper.insert(order);
        return order;
    }
    /**
     * 查询单条订单（必须携带分片键）
     */
    public Order getOrder(Long orderId, Long userId) {
        return orderMapper.selectByOrderIdAndUserId(orderId, userId);
    }
}

控制器

package com.example.controller;
import com.example.entity.Order;
import com.example.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.math.BigDecimal;
@RestController
@RequestMapping("/order")
public class OrderController {
    @Autowired
    private OrderService orderService;
    @PostMapping("/insert")
    public Order insert(@RequestParam Long userId, 
                       @RequestParam BigDecimal amount) {
        return orderService.insertOrder(userId, amount);
    }
    @GetMapping("/get")
    public Order get(@RequestParam Long orderId,
                     @RequestParam Long userId) {
        return orderService.getOrder(orderId, userId);
    }
}

复杂查询场景配置

广播表（公共字典表）

rules:
  sharding:
    tables:
      # 字典表在所有库中保持一致
      t_dict:
        actual-data-nodes: ds$->{0..1}.t_dict
        broadcast: true   # 标记为广播表

绑定表（减少JOIN查询跨库）

rules:
  sharding:
    binding-tables:
      - t_order,t_order_item   # 订单表和订单明细表绑定
    tables:
      t_order_item:
        actual-data-nodes: ds$->{0..1}.t_order_item_$->{0..1}
        table-strategy:
          standard:
            sharding-column: order_id
            sharding-algorithm-name: table-inline

最佳实践与注意事项

注意事项	详细说明
分片键必须携带	查询时必须包含分片键，否则会全库扫描
避免跨库JOIN	尽量使用绑定表设计避免跨库关联
分布式ID生成	使用雪花算法等保证全局唯一性
范围查询	使用`BETWEEN`需要配置范围分片算法
事务处理	跨库事务需使用`@ShardingTransactionType`

分布式事务示例（XA强一致）

@ShardingTransactionType(TransactionType.XA)
@Transactional(rollbackFor = Exception.class)
public void createOrderWithPayment() {
    // 跨库操作订单和支付表
}

生产环境建议

容量规划：选择分片数量时考虑未来3-5年数据增长
监控告警：接入ShardingSphere-Elasticjob实现自动治理
数据迁移：使用官方提供的Scaling组件在线迁移
SQL规范：禁止无分片键的查询，定期review慢SQL

这个案例涵盖了ShardingSphere分库分表的核心配置和用法，实际项目中需要根据业务场景灵活调整分片策略，需要注意,路由优化和分片键的选择是分库分表成功的关键。

本文地址： https://www.bmjup.com/post/42360.html