MySQL分库分表方案与实践分库分表是解决海量数据存储与高并发访问的核心方案,通过水平拆分将数据分散到多个库表,实现存储

概述

随着业务快速增长,单表数据量突破千万级甚至亿级,MySQL性能急剧下降,成为系统瓶颈。分库分表是解决海量数据存储与高并发访问的核心方案,通过水平拆分将数据分散到多个库表,实现存储容量和并发能力的线性扩展。本文深入剖析垂直拆分与水平拆分策略,详解ShardingSphere-JDBC集成方案,涵盖Snowflake分布式ID生成、跨分片查询优化、深分页问题解决、数据迁移完整流程,结合电商订单系统实战案例,提供从理论到落地的完整技术路径,助力构建支撑亿级数据的高性能数据库架构。

一、理论知识与核心概念

1.1 为什么需要分库分表?

单表数据量瓶颈

MySQL InnoDB存储引擎使用B+树索引,当单表数据量超过500万时,B+树高度增加,查询性能显著下降:

500万数据: B+树高度约4层,查询需要4次磁盘IO
1000万数据: B+树高度5层,性能下降30%
2000万数据: B+树高度6层,性能下降50%以上
5000万数据: 慢查询频发,系统不可用

并发压力瓶颈

单库单表承载所有读写请求,CPU、内存、磁盘IO成为瓶颈:

单机QPS上限: 通常5000-10000 QPS
写入瓶颈: InnoDB行锁冲突,TPS难以突破1000
备份困难: 单表100GB,mysqldump备份耗时数小时

业务增长预测

电商订单系统日订单量100万,单表预估:

1年数据: 3.65亿条记录
3年数据: 10.95亿条记录
单表极限: 建议不超过2000万,必须分库分表

1.2 分库分表核心概念

垂直拆分 (Vertical Sharding)

按业务模块或字段维度拆分:

垂直分库: 用户库、订单库、商品库分别部署,业务解耦
垂直分表: 商品基础表(id, name, price)与详情表(description, images)分离

优点: 业务隔离,微服务架构天然支持

缺点: 不解决单表数据量问题,JOIN查询复杂

水平拆分 (Horizontal Sharding)

按数据行拆分到多个库表,同构部署:

分库: order_db_0, order_db_1, order_db_2, order_db_3 (4个库)
分表: t_order_0 ~ t_order_255 (每库256张表)

优点: 单表数据量减少,性能线性提升,支持无限扩展

缺点: 跨分片查询、分布式事务、数据迁移复杂

混合拆分 (推荐生产方案)

先垂直拆分业务,再对大表水平拆分:

用户服务: user_db (垂直拆分,无需分表)
订单服务: order_db_0 ~ order_db_3 (垂直拆分 + 水平分表)
商品服务: product_db_0 ~ product_db_1 (垂直拆分 + 水平分表)

二、分库分表策略详解

2.1 分片键选择

分片键决定数据路由规则,选择不当导致数据倾斜或跨片查询:

常见分片键

分片键	适用场景	优点	缺点
user_id	用户维度查询	单用户数据集中,查询快	大V用户数据倾斜
order_id	订单查询	数据分布均匀	按用户查询需跨片
seller_id	卖家维度查询	单卖家数据集中	大卖家数据倾斜
created_time	按时间归档	历史数据归档方便	当前数据热点集中

电商订单表分片键选择

-- 方案1: 按user_id分片 (推荐)
优点: 查询"我的订单"无需跨片,单用户查询性能极佳
缺点: 卖家查询"我的销售订单"需要跨片聚合

-- 方案2: 按order_id分片
优点: 数据分布均匀,单订单查询快
缺点: 用户订单列表查询需要跨片

-- 生产建议: 按user_id分片,卖家维度查询走ES

2.2 分片算法对比

1. 取模算法 (Mod) - 最常用

// 分库分表规则
int dbIndex = user_id % 4;           // 4个库
int tableIndex = user_id % 256;      // 每库256张表

// 示例: user_id = 1001
dbIndex = 1001 % 4 = 1              → order_db_1
tableIndex = 1001 % 256 = 233       → t_order_233
最终路由: order_db_1.t_order_233

优点: 数据分布均匀,计算简单,性能高

缺点: 扩容需要数据迁移 (4库扩展到8库需rehash)

2. 范围算法 (Range)

// ID范围分片
if (order_id < 10000000) {
    dbIndex = 0;  // order_db_0
} else if (order_id < 20000000) {
    dbIndex = 1;  // order_db_1
} else if (order_id < 30000000) {
    dbIndex = 2;  // order_db_2
}

优点: 范围查询性能好,扩容无需迁移历史数据

缺点: 数据分布可能不均匀,新数据热点集中

3. 一致性哈希 (Consistent Hash)

// 哈希环分片
long hashValue = hash(user_id) % (2^32);
int dbIndex = findNearestNode(hashValue);  // 顺时针查找最近节点

优点: 扩容时仅迁移1/N数据,节点变化影响最小

缺点: 实现复杂,需要虚拟节点避免数据倾斜

4. 时间范围算法 (Time-based)

// 按月分表
String tableName = "t_order_" + yyyyMM;
// t_order_202401, t_order_202402, t_order_202403...

优点: 历史数据归档方便,时间范围查询性能极佳

缺点: 当月数据热点集中,跨月查询需union多表

选择建议

用户/订单维度: 取模算法 (数据均匀,查询性能稳定)
时间维度归档: 时间范围算法 (日志表、流水表)
动态扩容场景: 一致性哈希 (频繁扩容的系统)

2.3 分库分表数量规划

单表数据量建议

500万: 开始考虑分表
1000万: 强烈建议分表
2000万: 必须分表,性能严重下降

分片数量计算

总数据量: 预估3年10亿订单数据
单表容量: 建议200万条 (保证性能)
分表数量: 10亿 / 200万 = 500张表

推荐方案: 4个库 × 256张表 = 1024张物理表
单表数据: 10亿 / 1024 ≈ 97.6万条 ✓

扩展空间: 支持扩展到8库、16库,无需修改表数量

库表数量最佳实践

分库数量: 2^n (2, 4, 8, 16, 32),便于取模和扩容
分表数量: 2^n (16, 32, 64, 128, 256),每库表数量适中

推荐配置:
- 小型系统: 2库 × 16表 = 32张表
- 中型系统: 4库 × 64表 = 256张表
- 大型系统: 8库 × 256表 = 2048张表
- 超大型系统: 16库 × 512表 = 8192张表

三、ShardingSphere分库分表实战

3.1 ShardingSphere-JDBC简介

ShardingSphere-JDBC是Apache顶级项目,轻量级Java框架,以jar包形式提供服务:

核心特性

完全兼容JDBC: 无需修改业务代码
支持任意ORM框架: MyBatis、MyBatis-Plus、JPA、Hibernate
高性能: 本地计算,无额外网络开销
分片策略丰富: 支持取模、范围、自定义算法
读写分离: 集成主从复制,自动路由
分布式事务: 集成Seata,支持XA、AT模式

3.2 ShardingSphere-JDBC配置

引入依赖

<!-- pom.xml -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.3.2</version>
</dependency>

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.6</version>
</dependency>

完整配置 application.yml

spring:
  shardingsphere:
    # 数据源配置
    datasource:
      names: order-db-0,order-db-1,order-db-2,order-db-3

      # 订单库0
      order-db-0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.100:3306/order_db_0?useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
        username: root
        password: password
        hikari:
          maximum-pool-size: 50
          minimum-idle: 10
          connection-timeout: 30000

      # 订单库1
      order-db-1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.101:3306/order_db_1?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: password
        hikari:
          maximum-pool-size: 50
          minimum-idle: 10

      # 订单库2
      order-db-2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.102:3306/order_db_2?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: password
        hikari:
          maximum-pool-size: 50
          minimum-idle: 10

      # 订单库3
      order-db-3:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.103:3306/order_db_3?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: password
        hikari:
          maximum-pool-size: 50
          minimum-idle: 10

    # 分片规则配置
    rules:
      sharding:
        tables:
          # 订单表分片规则
          t_order:
            # 真实节点: 4个库 × 256张表 = 1024张物理表
            actual-data-nodes: order-db-$->{0..3}.t_order_$->{0..255}

            # 分库策略: 按user_id取模,路由到4个库
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-database-mod

            # 分表策略: 按user_id取模,路由到256张表
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-table-mod

            # 主键生成策略: 雪花算法
            key-generate-strategy:
              column: id
              key-generator-name: snowflake

          # 订单明细表分片规则 (与订单表相同分片键)
          t_order_item:
            actual-data-nodes: order-db-$->{0..3}.t_order_item_$->{0..255}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-database-mod
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-table-mod
            key-generate-strategy:
              column: id
              key-generator-name: snowflake

        # 分片算法定义
        sharding-algorithms:
          # 分库算法: user_id % 4
          order-database-mod:
            type: MOD
            props:
              sharding-count: 4

          # 分表算法: user_id % 256
          order-table-mod:
            type: MOD
            props:
              sharding-count: 256

        # 主键生成器定义
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1           # 机器ID (0-31)
              datacenter-id: 1       # 数据中心ID (0-31)

    # 属性配置
    props:
      sql-show: true                 # 打印SQL日志
      sql-simple: false              # 打印完整SQL (包含参数)

3.3 订单表Schema设计

订单主表

-- order_db_0 ~ order_db_3,每个库都需要创建256张表
-- t_order_0 ~ t_order_255

CREATE TABLE `t_order_0` (
  `id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID (雪花算法生成)',
  `order_no` VARCHAR(32) NOT NULL COMMENT '订单编号',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID (分片键)',
  `seller_id` BIGINT UNSIGNED NOT NULL COMMENT '卖家ID',
  `total_amount` DECIMAL(19, 2) NOT NULL COMMENT '订单总金额',
  `pay_amount` DECIMAL(19, 2) NOT NULL DEFAULT 0.00 COMMENT '实付金额',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单状态: 0-待支付,1-已支付,2-已发货,3-已完成,4-已取消',
  `pay_time` DATETIME DEFAULT NULL COMMENT '支付时间',
  `delivery_time` DATETIME DEFAULT NULL COMMENT '发货时间',
  `finish_time` DATETIME DEFAULT NULL COMMENT '完成时间',
  `is_deleted` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除: 0-未删除,1-已删除',
  `created_by` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人ID',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_by` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新人ID',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`, `created_at`),
  KEY `idx_seller_id` (`seller_id`, `created_at`),
  KEY `idx_status` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

-- 批量创建256张表的脚本
DELIMITER $$
CREATE PROCEDURE create_order_tables()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 256 DO
    SET @sql = CONCAT('CREATE TABLE t_order_', i, ' LIKE t_order_0');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

-- 执行创建
CALL create_order_tables();

订单明细表

CREATE TABLE `t_order_item_0` (
  `id` BIGINT UNSIGNED NOT NULL COMMENT '明细ID',
  `order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID (分片键,与订单表保持一致)',
  `product_id` BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
  `product_name` VARCHAR(128) NOT NULL COMMENT '商品名称 (冗余)',
  `product_image` VARCHAR(256) DEFAULT NULL COMMENT '商品图片',
  `sku_id` BIGINT UNSIGNED NOT NULL COMMENT 'SKU ID',
  `price` DECIMAL(19, 2) NOT NULL COMMENT '商品单价',
  `quantity` INT UNSIGNED NOT NULL COMMENT '购买数量',
  `total_amount` DECIMAL(19, 2) NOT NULL COMMENT '小计金额',
  `is_deleted` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单明细表';

3.4 Java代码实现

Entity层

@Data
@TableName("t_order")
public class Order {
    @TableId(type = IdType.ASSIGN_ID)  // 使用ShardingSphere雪花算法
    private Long id;

    private String orderNo;
    private Long userId;       // 分片键
    private Long sellerId;
    private BigDecimal totalAmount;
    private BigDecimal payAmount;
    private Integer status;
    private LocalDateTime payTime;
    private LocalDateTime deliveryTime;
    private LocalDateTime finishTime;

    private Integer isDeleted;
    private Long createdBy;
    private LocalDateTime createdAt;
    private Long updatedBy;
    private LocalDateTime updatedAt;
}

@Data
@TableName("t_order_item")
public class OrderItem {
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;

    private Long orderId;
    private Long userId;       // 分片键,与订单表一致
    private Long productId;
    private String productName;
    private String productImage;
    private Long skuId;
    private BigDecimal price;
    private Integer quantity;
    private BigDecimal totalAmount;

    private Integer isDeleted;
    private Long createdBy;
    private LocalDateTime createdAt;
    private Long updatedBy;
    private LocalDateTime updatedAt;
}

Mapper层

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    // MyBatis-Plus自动生成CRUD方法
    // ShardingSphere自动路由到正确的分片
}

@Mapper
public interface OrderItemMapper extends BaseMapper<OrderItem> {
}

Repository层

public interface OrderRepository {
    void saveCustom(Order order);
    Order findById(Long orderId);
    Order findByOrderNo(String orderNo);
    List<Order> findByUserId(Long userId);
    PageResult<Order> pageByUserId(Long userId, Integer pageNum, Integer pageSize);
    void updateStatus(Long orderId, Integer status);
}

@Repository
public class OrderRepositoryImpl extends ServiceImpl<OrderMapper, Order>
    implements OrderRepository {

    @Override
    public void saveCustom(Order order) {
        super.save(order);
    }

    @Override
    public Order findById(Long orderId) {
        return super.lambdaQuery()
            .eq(Order::getId, orderId)
            .eq(Order::getIsDeleted, 0)
            .one();
    }

    @Override
    public Order findByOrderNo(String orderNo) {
        return super.lambdaQuery()
            .eq(Order::getOrderNo, orderNo)
            .eq(Order::getIsDeleted, 0)
            .one();
    }

    @Override
    public List<Order> findByUserId(Long userId) {
        // ShardingSphere根据user_id路由到单个分片
        return super.lambdaQuery()
            .eq(Order::getUserId, userId)
            .eq(Order::getIsDeleted, 0)
            .orderByDesc(Order::getCreatedAt)
            .list();
    }

    @Override
    public PageResult<Order> pageByUserId(Long userId, Integer pageNum, Integer pageSize) {
        Page<Order> page = new Page<>(pageNum, pageSize);
        Page<Order> result = super.lambdaQuery()
            .eq(Order::getUserId, userId)
            .eq(Order::getIsDeleted, 0)
            .orderByDesc(Order::getCreatedAt)
            .page(page);

        return new PageResult<>(result.getRecords(), result.getTotal());
    }

    @Override
    public void updateStatus(Long orderId, Integer status) {
        super.lambdaUpdate()
            .eq(Order::getId, orderId)
            .eq(Order::getIsDeleted, 0)
            .set(Order::getStatus, status)
            .set(Order::getUpdatedAt, LocalDateTime.now())
            .update();
    }
}

Service层

@Service
public class OrderServiceImpl implements OrderService {

    private static final TraceLogger log = TraceLogger.getLogger(OrderServiceImpl.class);

    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private OrderItemRepository orderItemRepository;

    @Autowired
    private SnowflakeIdWorker idWorker;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public OrderDTO createOrder(OrderCreateRequest request) {
        log.info("Create order, userId={}, totalAmount={}", request.getUserId(), request.getTotalAmount());

        // 1. 生成订单号
        String orderNo = generateOrderNo();

        // 2. 创建订单 (ShardingSphere自动路由到user_id对应的分片)
        Order order = new Order();
        order.setOrderNo(orderNo);
        order.setUserId(request.getUserId());
        order.setSellerId(request.getSellerId());
        order.setTotalAmount(request.getTotalAmount());
        order.setPayAmount(request.getTotalAmount());
        order.setStatus(0);  // 待支付
        order.setCreatedBy(request.getUserId());
        orderRepository.saveCustom(order);

        log.info("Order created, orderId={}, orderNo={}", order.getId(), orderNo);

        // 3. 创建订单明细 (user_id与订单表相同,路由到同一分片)
        List<OrderItem> items = request.getItems().stream().map(item -> {
            OrderItem orderItem = new OrderItem();
            orderItem.setOrderId(order.getId());
            orderItem.setUserId(request.getUserId());  // 分片键
            orderItem.setProductId(item.getProductId());
            orderItem.setProductName(item.getProductName());
            orderItem.setSkuId(item.getSkuId());
            orderItem.setPrice(item.getPrice());
            orderItem.setQuantity(item.getQuantity());
            orderItem.setTotalAmount(item.getPrice().multiply(new BigDecimal(item.getQuantity())));
            orderItem.setCreatedBy(request.getUserId());
            return orderItem;
        }).collect(Collectors.toList());

        orderItemRepository.saveBatch(items);

        log.info("Order items created, orderId={}, itemCount={}", order.getId(), items.size());

        return convertToDTO(order);
    }

    @Override
    public OrderDTO getOrderById(Long orderId) {
        log.info("Get order by id, orderId={}", orderId);

        Order order = orderRepository.findById(orderId);
        if (order == null) {
            throw new BusinessException("订单不存在");
        }

        return convertToDTO(order);
    }

    @Override
    public PageResult<OrderDTO> pageOrdersByUserId(Long userId, Integer pageNum, Integer pageSize) {
        log.info("Page orders by userId, userId={}, pageNum={}, pageSize={}", userId, pageNum, pageSize);

        // ShardingSphere根据user_id路由到单个分片,无跨片查询
        PageResult<Order> pageResult = orderRepository.pageByUserId(userId, pageNum, pageSize);

        List<OrderDTO> orderDTOs = pageResult.getRecords().stream()
            .map(this::convertToDTO)
            .collect(Collectors.toList());

        return new PageResult<>(orderDTOs, pageResult.getTotal());
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void payOrder(Long orderId) {
        log.info("Pay order, orderId={}", orderId);

        // 更新订单状态
        orderRepository.updateStatus(orderId, 1);  // 已支付

        log.info("Order paid successfully, orderId={}", orderId);
    }

    private String generateOrderNo() {
        return "ORD" + System.currentTimeMillis() + RandomStringUtils.randomNumeric(6);
    }

    private OrderDTO convertToDTO(Order order) {
        OrderDTO dto = new OrderDTO();
        dto.setId(order.getId());
        dto.setOrderNo(order.getOrderNo());
        dto.setUserId(order.getUserId());
        dto.setTotalAmount(order.getTotalAmount());
        dto.setStatus(order.getStatus());
        dto.setCreatedAt(order.getCreatedAt());
        return dto;
    }
}

Controller层

@RestController
@RequestMapping("/api/order/v1/orders")
public class OrderController {

    @Autowired
    private OrderService orderService;

    @PostMapping("/create")
    public Response<OrderDTO> createOrder(@Valid @RequestBody OrderCreateRequest request) {
        OrderDTO order = orderService.createOrder(request);
        return Response.success(order);
    }

    @GetMapping("/{orderId}")
    public Response<OrderDTO> getOrderById(@PathVariable Long orderId) {
        OrderDTO order = orderService.getOrderById(orderId);
        return Response.success(order);
    }

    @GetMapping("/user/{userId}")
    public Response<PageResult<OrderDTO>> pageOrdersByUserId(
        @PathVariable Long userId,
        @RequestParam(defaultValue = "1") Integer pageNum,
        @RequestParam(defaultValue = "10") Integer pageSize
    ) {
        PageResult<OrderDTO> result = orderService.pageOrdersByUserId(userId, pageNum, pageSize);
        return Response.success(result);
    }

    @PutMapping("/{orderId}/pay")
    public Response<Void> payOrder(@PathVariable Long orderId) {
        orderService.payOrder(orderId);
        return Response.success();
    }
}

SQL日志 (ShardingSphere自动改写)

Logic SQL: SELECT * FROM t_order WHERE user_id = 1001 AND is_deleted = 0 ORDER BY created_at DESC

Actual SQL: order-db-1 ::: SELECT * FROM t_order_233 WHERE user_id = 1001 AND is_deleted = 0 ORDER BY created_at DESC

路由分析:
user_id = 1001
dbIndex = 1001 % 4 = 1 → order-db-1
tableIndex = 1001 % 256 = 233 → t_order_233

四、分布式主键生成方案

4.1 为什么需要分布式ID?

分库分表后,MySQL自增主键失效:

问题

多库自增冲突: order_db_0生成id=1, order_db_1也生成id=1,冲突
无法保证全局唯一: 订单号、用户ID需要全局唯一
无法保证趋势递增: InnoDB B+树索引要求主键递增,否则频繁页分裂

4.2 分布式ID生成方案对比

方案1: UUID

String id = UUID.randomUUID().toString().replace("-", "");
// 示例: 3e11fa4771ca11e19e33c80aa9429562

优点: 本地生成,性能极高,全局唯一

缺点:

❌ 字符串类型,占用空间大 (36字节)
❌ 无序,InnoDB插入性能差
❌ 不可读,无业务含义

方案2: 数据库自增ID+号段模式

-- 创建ID生成表
CREATE TABLE `id_generator` (
  `biz_type` VARCHAR(32) NOT NULL COMMENT '业务类型',
  `max_id` BIGINT UNSIGNED NOT NULL COMMENT '当前最大ID',
  `step` INT UNSIGNED NOT NULL DEFAULT 1000 COMMENT '步长',
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`biz_type`)
) ENGINE=InnoDB;

-- 获取号段
UPDATE id_generator SET max_id = max_id + step WHERE biz_type = 'order';
SELECT max_id FROM id_generator WHERE biz_type = 'order';

优点: 趋势递增,简单易实现

缺点:

❌ 依赖数据库,单点故障
❌ 号段用完需要请求数据库,有延迟

方案3: Redis INCR

Long id = redisTemplate.opsForValue().increment("order_id_seq");

优点: 性能高 (10万QPS), 趋势递增

缺点:

❌ 依赖Redis,需要持久化
❌ Redis宕机会导致ID重复

方案4: Snowflake算法 (推荐) ⭐⭐⭐⭐⭐

Java实现

@Component
public class SnowflakeIdWorker {

    private final long epoch = 1577808000000L;  // 起始时间: 2020-01-01 00:00:00
    private final long datacenterIdBits = 5L;
    private final long workerIdBits = 5L;
    private final long sequenceBits = 12L;

    private final long maxDatacenterId = -1L ^ (-1L << datacenterIdBits);  // 31
    private final long maxWorkerId = -1L ^ (-1L << workerIdBits);          // 31
    private final long maxSequence = -1L ^ (-1L << sequenceBits);          // 4095

    private final long workerIdShift = sequenceBits;                        // 12
    private final long datacenterIdShift = sequenceBits + workerIdBits;   // 17
    private final long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits; // 22

    private long datacenterId;
    private long workerId;
    private long sequence = 0L;
    private long lastTimestamp = -1L;

    public SnowflakeIdWorker(@Value("${snowflake.datacenter-id:1}") long datacenterId,
                             @Value("${snowflake.worker-id:1}") long workerId) {
        if (datacenterId > maxDatacenterId || datacenterId < 0) {
            throw new IllegalArgumentException("datacenter Id can't be greater than " + maxDatacenterId + " or less than 0");
        }
        if (workerId > maxWorkerId || workerId < 0) {
            throw new IllegalArgumentException("worker Id can't be greater than " + maxWorkerId + " or less than 0");
        }
        this.datacenterId = datacenterId;
        this.workerId = workerId;
    }

    public synchronized long nextId() {
        long timestamp = System.currentTimeMillis();

        // 时钟回拨检测
        if (timestamp < lastTimestamp) {
            throw new RuntimeException("Clock moved backwards. Refusing to generate id");
        }

        // 同一毫秒内,序列号自增
        if (timestamp == lastTimestamp) {
            sequence = (sequence + 1) & maxSequence;
            // 序列号溢出,等待下一毫秒
            if (sequence == 0) {
                timestamp = tilNextMillis(lastTimestamp);
            }
        } else {
            // 不同毫秒,序列号重置
            sequence = 0L;
        }

        lastTimestamp = timestamp;

        // 组装64位ID
        return ((timestamp - epoch) << timestampLeftShift)
            | (datacenterId << datacenterIdShift)
            | (workerId << workerIdShift)
            | sequence;
    }

    private long tilNextMillis(long lastTimestamp) {
        long timestamp = System.currentTimeMillis();
        while (timestamp <= lastTimestamp) {
            timestamp = System.currentTimeMillis();
        }
        return timestamp;
    }
}

配置

snowflake:
  datacenter-id: 1  # 数据中心ID (0-31)
  worker-id: 1      # 机器ID (0-31)

优点:

✅ 本地生成,性能极高 (400万/秒)
✅ 全局唯一
✅ 趋势递增,InnoDB友好
✅ 无依赖,高可用

缺点:

❌ 依赖系统时钟,时钟回拨会导致ID重复
❌ 最多支持1024台机器

生产环境推荐: Snowflake算法,性能和可靠性兼顾

五、跨分片查询与分页问题

5.1 跨分片查询类型

单分片查询 (推荐)

-- 带分片键user_id,路由到单个分片
SELECT * FROM t_order WHERE user_id = 1001;

执行计划: 只查询 order_db_1.t_order_233 (单分片)

广播查询 (所有分片)

-- 不带分片键,需要查询所有分片
SELECT * FROM t_order WHERE order_no = 'ORD20240115123456';

执行计划: 查询所有1024张表,性能差 ❌

优化方案: 为order_no建立全局路由表

CREATE TABLE `order_routing` (
  `order_no` VARCHAR(32) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`order_no`)
) ENGINE=InnoDB;

-- 查询流程
1. SELECT user_id FROM order_routing WHERE order_no = 'ORD20240115123456';
2. SELECT * FROM t_order WHERE user_id = {user_id} AND order_no = 'ORD20240115123456';

5.2 深分页问题与优化

问题SQL

-- 查询第100万页,每页10条
SELECT * FROM t_order WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 1000000, 10;

性能问题:
1. MySQL需要扫描1000010条记录
2. 丢弃前1000000条
3. 返回最后10条
4. 耗时: 5秒+

ShardingSphere分页问题

-- 原始SQL
SELECT * FROM t_order WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 100, 10;

-- ShardingSphere改写后 (假设4个分片)
-- 每个分片执行:
SELECT * FROM t_order_xxx WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 0, 110;

-- 应用层归并
1. 4个分片各返回110条 (共440条)
2. 归并排序
3. 跳过前100条,返回10条

问题: offset越大,网络传输数据越多
LIMIT 1000000, 10 → 每个分片返回1000010条 → 4个分片共400万条数据传输!

优化方案1: ID范围查询 (推荐)

// 第1页
SELECT * FROM t_order WHERE user_id = 1001
ORDER BY id DESC LIMIT 10;
// 返回10条,记录最后一条 last_id = 9999990

// 第2页
SELECT * FROM t_order WHERE user_id = 1001 AND id < 9999990
ORDER BY id DESC LIMIT 10;

// 第N页
SELECT * FROM t_order WHERE user_id = 1001 AND id < {last_id}
ORDER BY id DESC LIMIT 10;

优点: 只扫描10条,性能稳定,耗时10ms
缺点: 不支持跳页,只能顺序翻页

优化方案2: 业务限制 (最简单)

int maxPage = 100;  // 最多查询100页
if (pageNum > maxPage) {
    throw new BusinessException("最多查询" + maxPage + "页,请使用搜索功能");
}

理由: 99%用户不会翻到第100页,Google搜索也只展示10页

六、数据迁移方案

6.1 停机迁移方案

适用场景: 非核心业务,可接受短时间停机

迁移步骤

# 步骤1: 停止应用服务 (凌晨2:00)
systemctl stop order-service

# 步骤2: 全量数据导出
mysqldump -h192.168.1.100 -uroot -p --single-transaction \
  --skip-lock-tables order_db t_order > /tmp/order_db.sql

# 步骤3: 按分片规则导入新库
python3 split_and_import.py --source=/tmp/order_db.sql \
  --target-dbs=order-db-0,order-db-1,order-db-2,order-db-3 \
  --sharding-key=user_id \
  --sharding-count=1024

# 步骤4: 数据校验
SELECT COUNT(*) FROM order_db.t_order;  -- 100000000
SELECT SUM(cnt) FROM (
  SELECT COUNT(*) AS cnt FROM order_db_0.t_order_0 UNION ALL
  SELECT COUNT(*) AS cnt FROM order_db_0.t_order_1 UNION ALL
  ...
  SELECT COUNT(*) AS cnt FROM order_db_3.t_order_255
) AS tmp;  -- 100000000 ✓

# 步骤5: 切换应用配置,启动服务 (凌晨6:00)
systemctl start order-service

优点: 实现简单,数据一致性有保障

缺点: 业务中断4小时,用户体验差

6.2 不停机迁移方案 (推荐)

阶段1: 准备阶段

# 1. 创建新分库分表结构
for db in order_db_0 order_db_1 order_db_2 order_db_3; do
  mysql -h192.168.1.10x -uroot -p -e "CREATE DATABASE ${db}"
  for ((i=0;i<256;i++)); do
    mysql -h192.168.1.10x -uroot -p ${db} < t_order_${i}.sql
  done
done

# 2. 全量数据迁移 (历史数据,不影响线上)
mysqldump -h192.168.1.100 -uroot -p --where="created_at < '2024-01-15 02:00:00'" \
  order_db t_order | python3 split_and_import.py

记录迁移截止时间: cutoff_time = '2024-01-15 02:00:00'

阶段2: 双写阶段

@Service
public class OrderServiceImpl implements OrderService {

    @Autowired
    @Qualifier("oldDataSource")
    private DataSource oldDataSource;

    @Autowired
    @Qualifier("newDataSource")
    private DataSource newDataSource;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void createOrder(Order order) {
        try {
            // 1. 写旧库
            oldOrderRepository.save(order);

            // 2. 写新库 (ShardingSphere自动分片)
            newOrderRepository.save(order);
        } catch (Exception e) {
            // 新库异常时降级为单写
            if (isNewDbError(e)) {
                log.warn("New DB error, degrade to write old DB only");
                oldOrderRepository.save(order);
            } else {
                throw e;
            }
        }
    }

    @Override
    public Order getOrderById(Long orderId) {
        // 读操作仍然走旧库
        return oldOrderRepository.findById(orderId);
    }
}

阶段3: 增量数据追平

-- 补充增量数据 (cutoff_time到双写开启期间)
SELECT * FROM order_db.t_order
WHERE created_at > '2024-01-15 02:00:00'
  AND created_at < '2024-01-15 10:00:00'  -- 双写开启时间
INTO OUTFILE '/tmp/incremental.sql';

-- 按分片规则导入新库
python3 split_and_import.py --source=/tmp/incremental.sql

阶段4: 灰度切换读操作

@Configuration
public class DataSourceConfig {

    @Value("${read.new-db.ratio:0}")
    private int newDbRatio;  // 新库读流量比例 (0-100)

    public Order getOrderById(Long orderId) {
        // 灰度切换: 5% → 10% → 50% → 100%
        if (ThreadLocalRandom.current().nextInt(100) < newDbRatio) {
            return newOrderRepository.findById(orderId);  // 读新库
        } else {
            return oldOrderRepository.findById(orderId);  // 读旧库
        }
    }
}

# 配置文件
read:
  new-db:
    ratio: 5  # 第1天: 5%流量读新库

阶段5: 停止双写,完成迁移

@Service
public class OrderServiceImpl implements OrderService {

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void createOrder(Order order) {
        // 停止写旧库
        // oldOrderRepository.save(order);

        // 仅写新库
        newOrderRepository.save(order);
    }
}

// 保留旧库1-2周作为数据备份,确认无问题后下线

总耗时: 约3周,零业务中断 ✅

七、实战场景应用

7.1 电商订单系统分片方案

业务需求

日订单量: 100万
3年数据: 10.95亿条
读写比例: 9:1 (90%读,10%写)
高峰期QPS: 5000

分片方案

分片键: user_id (用户维度查询占95%)
分片算法: 取模算法 (数据分布均匀)
分片数量: 4库 × 256表 = 1024张物理表
单表数据: 10.95亿 / 1024 ≈ 107万条 ✓

分库:
- order_db_0: 192.168.1.100:3306
- order_db_1: 192.168.1.101:3306
- order_db_2: 192.168.1.102:3306
- order_db_3: 192.168.1.103:3306

分表: 每库256张表
- t_order_0 ~ t_order_255
- t_order_item_0 ~ t_order_item_255

性能数据

分片前 (单表1亿数据):
- 查询响应时间: 500ms
- 写入TPS: 1000
- 峰值QPS: 1500
- 系统状态: CPU 80%, 慢查询频发

分片后 (1024张表,单表100万):
- 查询响应时间: 50ms ✅
- 写入TPS: 5000 ✅
- 峰值QPS: 10000 ✅
- 系统状态: CPU 40%, 无慢查询

性能提升: 10倍 ✅

八、生产案例与故障排查

8.1 案例1: 分片键选择错误导致数据倾斜

故障现象

某电商平台按seller_id分片,发现order_db_0占用90%存储,其他库几乎为空

原因分析

-- 查询各分片数据量
SELECT 'order_db_0' AS db_name, COUNT(*) FROM order_db_0.t_order_0 UNION ALL
SELECT 'order_db_1', COUNT(*) FROM order_db_1.t_order_0;

结果:
order_db_0: 9000万条
order_db_1: 500万条
order_db_2: 300万条
order_db_3: 200万条

原因: Top卖家seller_id=1的订单占总量90%,全部路由到order_db_0
seller_id % 4 = 1 → order_db_0

解决方案

1. 改用user_id作为分片键 (用户分布均匀)
2. 对Top卖家特殊处理:
   - seller_id=1的订单按user_id+order_id联合分片
   - 或单独为Top卖家创建独立分片

8.2 案例2: 跨分片JOIN导致性能崩溃

故障现象

-- 查询用户订单及商品信息
SELECT o.*, p.product_name, p.price
FROM t_order o
JOIN t_product p ON o.product_id = p.id
WHERE o.user_id = 1001;

执行计划:
1. 查询t_order → order_db_1.t_order_233 (单分片)
2. 查询t_product → 需要查询product_db所有分片 (广播查询)
3. 应用层JOIN

问题: 商品表1024个分片全部查询,耗时10秒+

解决方案

// 方案1: 数据冗余,避免JOIN
@Data
@TableName("t_order")
public class Order {
    private Long productId;
    private String productName;  // 冗余商品名称
    private BigDecimal productPrice;  // 冗余商品价格
}

// 方案2: 应用层分步查询
List<Order> orders = orderRepository.findByUserId(userId);
List<Long> productIds = orders.stream().map(Order::getProductId).collect(Collectors.toList());
List<Product> products = productRepository.findByIds(productIds);  // 批量查询
// 应用层组装数据

九、常见问题与避坑指南

9.1 分片键可以修改吗?

不能修改 ❌

分片键一旦确定,数据已经按分片键路由到不同分片,修改分片键需要全量数据迁移

示例

-- 原分片键: user_id
user_id = 1001 → order_db_1.t_order_233

-- 修改分片键为order_id
order_id = 888 → order_db_0.t_order_120

问题: 数据已经在order_db_1.t_order_233,修改分片键无法自动迁移

解决方案: 分片键选择需要提前规划,无法修改则需要停机全量迁移

9.2 如何扩容分片?

扩容方案

原配置: 4库 × 256表 = 1024张表
扩容: 8库 × 256表 = 2048张表

步骤:
1. 创建新库: order_db_4 ~ order_db_7
2. 修改分片算法: user_id % 4 → user_id % 8
3. 数据迁移:
   - order_db_0 中 user_id % 8 in [4,5,6,7] 的数据迁移到新库
   - order_db_1 中 user_id % 8 in [4,5,6,7] 的数据迁移到新库
4. 修改应用配置,发布上线

迁移数据量: 50% (一半数据需要迁移)

避免频繁扩容: 一次性规划足够的分片数量 (如1024张表,支持10亿级数据)

9.3 分库分表后如何保证事务一致性?

单分片事务 (无问题)

@Transactional(rollbackFor = Exception.class)
public void createOrder(Order order) {
    // user_id相同,订单和订单明细在同一分片
    orderRepository.save(order);
    orderItemRepository.saveBatch(items);
}

跨分片事务 (需要分布式事务)

@GlobalTransactional  // Seata分布式事务
public void createOrder(Order order) {
    // 订单: user_id = 1001 → order_db_1
    orderRepository.save(order);

    // 库存: sku_id = 888 → inventory_db_0 (不同分片)
    inventoryService.deduct(skuId, quantity);
}

建议: 避免跨分片事务,业务层解耦

十、最佳实践与总结

10.1 分库分表最佳实践

1. 提前规划,宁可多不可少

预估3年数据量,一次性规划足够的分片数量
推荐: 1024张表 (4库×256表),避免频繁扩容

2. 分片键选择至关重要

选择查询频率最高的维度作为分片键
避免热点分片键 (Top卖家、大V用户)
分片键一旦确定,无法修改

3. 避免跨分片查询

单分片查询性能最佳
跨分片JOIN改为应用层JOIN或数据冗余

4. 读写分离 + 分库分表

主库写入,从库读取,提升吞吐量
ShardingSphere同时支持分片和读写分离

5. 监控告警

监控各分片数据量,及时发现数据倾斜
监控慢查询,优化跨分片查询

10.2 核心要点总结

分库分表策略

垂直拆分: 按业务模块拆分,解耦业务
水平拆分: 按数据行拆分,解决数据量问题
混合拆分: 先垂直后水平,生产推荐 ⭐⭐⭐⭐⭐

分片算法

取模算法: 最常用,数据分布均匀
范围算法: 历史数据归档方便
一致性哈希: 动态扩容场景
时间范围: 日志表、流水表

分布式ID

Snowflake: 推荐,性能高,趋势递增
数据库号段: 简单,依赖数据库
Redis INCR: 高性能,依赖Redis

跨分片查询

单分片查询: 性能最佳,推荐
广播查询: 性能差,避免使用
优化方案: 数据冗余,应用层JOIN

深分页优化

ID范围查询: 性能稳定,推荐
业务限制: 最多100页,超过用搜索
延迟关联: 支持跳页,性能提升明显

数据迁移

停机迁移: 简单,适合非核心业务
不停机迁移: 双写策略,零业务中断,推荐 ⭐⭐⭐⭐⭐

通过深入掌握分库分表核心原理,结合ShardingSphere等成熟框架,能够构建支撑亿级数据的高性能数据库架构,助力业务快速增长。

参考资料

《高性能MySQL》(第4版) - Baron Schwartz等
《大型网站技术架构:核心原理与案例分析》 - 李智慧
ShardingSphere官方文档: shardingsphere.apache.org/
MySQL官方文档: dev.mysql.com/doc/