← 返回首页 📚 实战课程
📑 本篇章节导航 (共6章,点击直达)
本节目标(学完能回答的面试题)一、理论篇(30%):分库分表策略与原理二、实战篇(40%):ShardingSphere-JDBC与完整代码三、面试模拟篇(20%)四、练习篇(10%)本节小结

P2-04-MySQL分库分表实战

本节目标(学完能回答的面试题)

1. 什么时候需要分库分表?单表多大开始考虑?

2. 水平分表和垂直分表的适用场景?

3. 分片键怎么选?选错了会怎样?

4. ShardingSphere-JDBC如何配置?核心配置项有哪些?

5. 跨分片查询、跨分片事务怎么处理?

6. 分布式主键生成方案有哪些?Snowflake的时钟回拨问题?

7. 如何不停机完成分库分表数据迁移?


一、理论篇(30%):分库分表策略与原理

1.1 什么时候需要分


触发条件(满足任一即考虑):
- 单表行数超过 500万~2000万(取决于字段大小和查询复杂度)
- 单库数据文件超过 20GB
- 单库QPS超过 5000(读写混合)
- 出现明显慢查询且无法通过索引优化

不要过早分库分表!先尝试:优化索引 → 读写分离 → 缓存 → 字段拆分 → 最后才分库分表

1.2 垂直拆分 vs 水平拆分


垂直分库:按业务边界拆分
  user_db / order_db / product_db / pay_db
  优点:业务解耦、故障隔离   缺点:跨库JOIN、分布式事务

垂直分表:按字段访问频率拆分
  order(id, user_id, total_amount)        -- 高频
  order_detail(id, order_id, address, payment_method) -- 低频

水平分表:同一表结构,数据按分片键分散
  db_0.t_order_0, db_0.t_order_1
  db_1.t_order_0, db_1.t_order_1
  总分片数 = 库数 × 表数

1.3 分片策略对比

策略路由方式优点缺点适用场景
取模分片id % N数据均匀扩容需迁移用户业务表
范围分片id 1~1000w扩容方便热点倾斜自增ID/日志
一致性HashHash环迁移量小实现复杂节点频繁变动
按时间分片按月/季度归档方便可能不均匀日志/流水表

1.4 分片键选择


核心原则:高频查询字段 + 值分布均匀 + 不可变

常见选择:用户表→user_id  订单表→user_id  日志表→create_time
常见错误:用create_time分片但按user_id查询(全分片扫描)
          用status分片(只有几个枚举值,分布极不均匀)

二、实战篇(40%):ShardingSphere-JDBC与完整代码

2.1 建表SQL(2库各2表)


-- 192.168.1.100 上创建 db_order_0,192.168.1.101 上创建 db_order_1
-- 每个库都执行以下SQL:
CREATE TABLE t_order_0 (
    id BIGINT PRIMARY KEY COMMENT '雪花ID',
    user_id BIGINT NOT NULL,
    order_no VARCHAR(64) NOT NULL,
    total_amount DECIMAL(10,2) DEFAULT 0,
    status TINYINT DEFAULT 0 COMMENT '0待支付 1已支付 2已发货 3已完成',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id), INDEX idx_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE t_order_1 ( -- 同 t_order_0 结构完全一致
    id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL,
    order_no VARCHAR(64) NOT NULL, total_amount DECIMAL(10,2) DEFAULT 0,
    status TINYINT DEFAULT 0,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id), INDEX idx_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE t_order_item_0 (
    id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL, product_name VARCHAR(200),
    quantity INT DEFAULT 1, unit_price DECIMAL(10,2),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_id (order_id), INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE t_order_item_1 ( -- 同 t_order_item_0 结构
    id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL, product_name VARCHAR(200),
    quantity INT DEFAULT 1, unit_price DECIMAL(10,2),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_id (order_id), INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 广播表:每个库都存一份完整数据
CREATE TABLE t_dict_product (
    id BIGINT PRIMARY KEY, product_name VARCHAR(200), category VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 ShardingSphere YAML完整配置


# application-sharding.yml — 2库 × 2表 = 4分片
mode:
  type: Standalone
  repository:
    type: JDBC

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.1.100:3306/db_order_0?useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: your_password
    hikari: { maximumPoolSize: 20, minimumIdle: 5 }
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.1.101:3306/db_order_1?useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: your_password
    hikari: { maximumPoolSize: 20, minimumIdle: 5 }

rules:
  - !SHARDING
    tables:
      t_order:
        actualDataNodes: ds_${0..1}.t_order_${0..1}
        tableStrategy:
          standard: { shardingColumn: user_id, shardingAlgorithmName: order_table_mod }
        databaseStrategy:
          standard: { shardingColumn: user_id, shardingAlgorithmName: order_db_mod }
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake
      t_order_item:
        actualDataNodes: ds_${0..1}.t_order_item_${0..1}
        tableStrategy:
          standard: { shardingColumn: user_id, shardingAlgorithmName: order_table_mod }
        databaseStrategy:
          standard: { shardingColumn: user_id, shardingAlgorithmName: order_db_mod }
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake
    bindingTables:
      - t_order, t_order_item          # 关联查询同路由,避免笛卡尔积
    broadcastTables:
      - t_dict_product                  # 小表广播,本地JOIN
    shardingAlgorithms:
      order_table_mod:
        type: MOD
        props: { sharding-count: 2 }
      order_db_mod:
        type: MOD
        props: { sharding-count: 2 }
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
        props: { worker-id: 1 }

props:
  sql-show: true

2.3 Snowflake算法实现(含时钟回拨处理)


public class SafeSnowflakeIdWorker {
    private final long twepoch = 1704067200000L; // 2024-01-01基准
    private final long workerIdBits = 5L, datacenterIdBits = 5L, sequenceBits = 12L;
    private final long maxWorkerId = ~(-1L << workerIdBits);
    private final long maxDatacenterId = ~(-1L << datacenterIdBits);
    private final long workerIdShift = sequenceBits;
    private final long datacenterIdShift = sequenceBits + workerIdBits;
    private final long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits;
    private final long sequenceMask = ~(-1L << sequenceBits);
    private final long workerId, datacenterId;
    private long sequence = 0L, lastTimestamp = -1L;
    private static final long MAX_CLOCK_DRIFT = 5L; // 时钟回拨容忍阈值(ms)

    public SafeSnowflakeIdWorker(long workerId, long datacenterId) {
        if (workerId > maxWorkerId || workerId < 0)
            throw new IllegalArgumentException("workerId out of range");
        if (datacenterId > maxDatacenterId || datacenterId < 0)
            throw new IllegalArgumentException("datacenterId out of range");
        this.workerId = workerId;
        this.datacenterId = datacenterId;
    }

    public synchronized long nextId() {
        long timestamp = System.currentTimeMillis();
        // 时钟回拨检测
        if (timestamp < lastTimestamp) {
            long offset = lastTimestamp - timestamp;
            if (offset <= MAX_CLOCK_DRIFT) {
                try { Thread.sleep(offset); timestamp = System.currentTimeMillis(); }
                catch (InterruptedException e) { throw new RuntimeException("时钟回拨等待中断", e); }
            } else {
                throw new RuntimeException("时钟回拨超过阈值: " + offset + "ms");
            }
        }
        if (timestamp == lastTimestamp) {
            sequence = (sequence + 1) & sequenceMask;
            if (sequence == 0) timestamp = tilNextMillis(lastTimestamp);
        } else { sequence = 0L; }
        lastTimestamp = timestamp;
        return ((timestamp - twepoch) << timestampLeftShift)
             | (datacenterId << datacenterIdShift)
             | (workerId << workerIdShift) | sequence;
    }

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

2.4 业务代码


@Entity
@Table(name = "t_order")
public class Order {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;             // ShardingSphere雪花ID自动填充
    private Long userId;
    private String orderNo;
    private BigDecimal totalAmount;
    private Integer status;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
    // getter/setter ...
}

public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByUserIdAndId(Long userId, Long id);           // 精确路由
    List<Order> findByUserIdAndCreateTimeBetween(Long userId, LocalDateTime start, LocalDateTime end);
}

@Service
@Slf4j
public class OrderService {
    @Autowired private OrderRepository orderRepository;

    @Transactional
    public Order createOrder(Long userId, BigDecimal amount) {
        Order order = new Order();
        order.setUserId(userId);
        order.setOrderNo("ORD" + System.currentTimeMillis()
            + ThreadLocalRandom.current().nextInt(1000, 9999));
        order.setTotalAmount(amount);
        order.setStatus(0);
        order.setCreateTime(LocalDateTime.now());
        return orderRepository.save(order);
        // SQL日志可见: INSERT INTO t_order_1 ... (ds_1) — 根据userId路由
    }

    public List<Order> getUserOrders(Long userId) {
        return orderRepository.findByUserIdAndCreateTimeBetween(
            userId, LocalDateTime.now().minusMonths(3), LocalDateTime.now());
        // 带分片键,只命中一个分片
    }
}

2.5 跨分片查询解决方案


# 方案一:绑定表(已配置)— 同分片键的表关联查询自动路由到同一分片
bindingTables:
  - t_order, t_order_item

# 方案二:广播表 — 小表每个库一份,本地JOIN
broadcastTables:
  - t_dict_product

// 方案三:冗余字段 — 订单明细中冗余product_name,避免JOIN商品表
// 方案四:应用层组装 — 先查订单,再Feign调商品服务,代码中合并
@Service
public class OrderQueryService {
    @Autowired private OrderRepository orderRepository;
    @Autowired private ProductApiClient productApiClient;

    public OrderDetailVO getOrderDetail(Long orderId, Long userId) {
        Order order = orderRepository.findByUserIdAndId(userId, orderId).get(0);
        List<Long> productIds = orderItemRepository
            .findByUserIdAndOrderId(userId, orderId).stream()
            .map(OrderItem::getProductId).collect(Collectors.toList());
        Map<Long, ProductDTO> productMap = productApiClient.batchQuery(productIds);
        return OrderDetailVO.build(order, productMap);
    }
}

2.6 数据迁移:双写 + CDC方案


@Service
@Slf4j
public class DualWriteOrderService {
    @Autowired private OrderMapper oldOrderMapper;   // 旧单库
    @Autowired private OrderMapper newOrderMapper;   // 新分片
    @Value("${migration.dual-write:true}")   private boolean dualWriteEnabled;
    @Value("${migration.read-new-ratio:0}")  private int readNewRatio; // 灰度比例0~100

    @Transactional
    public void createOrder(Order order) {
        oldOrderMapper.insert(order);                // 写旧库(主)
        if (dualWriteEnabled) {
            try { newOrderMapper.insert(order); }    // 双写新库
            catch (Exception e) {
                log.error("双写失败, orderId={}", order.getId(), e);
                compensationService.record(order.getId(), "INSERT"); // 补偿表
            }
        }
    }

    public Order getOrder(Long orderId) {
        // 灰度读:按比例分配新老库
        if (readNewRatio > 0 && ThreadLocalRandom.current().nextInt(100) < readNewRatio)
            return newOrderMapper.selectById(orderId);
        return oldOrderMapper.selectById(orderId);
    }
}
// 迁移步骤:部署新分片 → 双写 → Canal同步历史 → 校验 → 灰度切读 → 关旧库

三、面试模拟篇(20%)

Q1:分库分表后怎么做跨页查询?

ShardingSphere向每个分片发 LIMIT offset+size,汇总排序后取目标段。分页越深性能越差。解决:游标分页(基于上一页最后一条排序值);限制最大页码;用ES做搜索DB做存储。

Q2:分库分表后JOIN怎么处理?

1) 绑定表(同分片键自动同路由);2) 广播表(小表全量冗余);3) 冗余字段(反范式);4) 应用层组装(分别查再合并);5) ES宽表。

Q3:分布式事务怎么处理?

1) 避免跨分片事务(合理选分片键);2) 最终一致性(MQ+本地事务);3) Seata AT模式;4) TCC模式。

Q4:Snowflake时钟回拨?

小幅回拨(<=5ms)sleep等待;大幅回拨拒绝。生产方案:百度UidGenerator(未来时间)、美团Leaf(号段模式)、禁止NTP主动回拨。

Q5:不停机迁移?

双写+CDC:新库同时写 → Canal同步历史 → 数据校验 → 灰度切读(10%→100%) → 关旧库。关键:补偿机制 + 随时可回滚。

四、练习篇(10%)


练习1:ShardingSphere环境搭建
  1. 两台MySQL创建 db_order_0/1,每库建4张分片表
  2. 配置YAML,启动Spring Boot,插入数据验证路由

练习2:分片路由验证
  1. 插入userId=1的订单 → 观察路由到哪个库哪张表
  2. SELECT * WHERE user_id=1 → 确认只命中一个分片
  3. SELECT * WHERE order_no='xxx' → 确认全分片扫描

练习3:分布式主键并发测试
  1. 并发插入1000条,验证ID唯一性和趋势递增
  2. 模拟时钟回拨,观察异常处理

本节小结

知识点关键内容
分片策略垂直拆分(业务)、水平拆分(数据)、取模/范围/一致性Hash
分片键选择高频查询字段、分布均匀、不可变、避免跨分片
ShardingSphereYAML配置、actualDataNodes、分片算法、绑定表、广播表
分布式主键Snowflake 64位结构、时钟回拨检测、号段模式
跨分片查询绑定表、广播表、冗余字段、应用层组装、ES宽表
数据迁移双写代理、Canal CDC、数据校验、灰度切流