1. 什么时候需要分库分表?单表多大开始考虑?
2. 水平分表和垂直分表的适用场景?
3. 分片键怎么选?选错了会怎样?
4. ShardingSphere-JDBC如何配置?核心配置项有哪些?
5. 跨分片查询、跨分片事务怎么处理?
6. 分布式主键生成方案有哪些?Snowflake的时钟回拨问题?
7. 如何不停机完成分库分表数据迁移?
触发条件(满足任一即考虑):
- 单表行数超过 500万~2000万(取决于字段大小和查询复杂度)
- 单库数据文件超过 20GB
- 单库QPS超过 5000(读写混合)
- 出现明显慢查询且无法通过索引优化
不要过早分库分表!先尝试:优化索引 → 读写分离 → 缓存 → 字段拆分 → 最后才分库分表
垂直分库:按业务边界拆分
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
总分片数 = 库数 × 表数
| 策略 | 路由方式 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|---|
| 取模分片 | id % N | 数据均匀 | 扩容需迁移 | 用户业务表 |
| 范围分片 | id 1~1000w | 扩容方便 | 热点倾斜 | 自增ID/日志 |
| 一致性Hash | Hash环 | 迁移量小 | 实现复杂 | 节点频繁变动 |
| 按时间分片 | 按月/季度 | 归档方便 | 可能不均匀 | 日志/流水表 |
核心原则:高频查询字段 + 值分布均匀 + 不可变
常见选择:用户表→user_id 订单表→user_id 日志表→create_time
常见错误:用create_time分片但按user_id查询(全分片扫描)
用status分片(只有几个枚举值,分布极不均匀)
-- 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;
# 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
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;
}
}
@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());
// 带分片键,只命中一个分片
}
}
# 方案一:绑定表(已配置)— 同分片键的表关联查询自动路由到同一分片
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);
}
}
@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同步历史 → 校验 → 灰度切读 → 关旧库
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%) → 关旧库。关键:补偿机制 + 随时可回滚。
练习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 |
| 分片键选择 | 高频查询字段、分布均匀、不可变、避免跨分片 |
| ShardingSphere | YAML配置、actualDataNodes、分片算法、绑定表、广播表 |
| 分布式主键 | Snowflake 64位结构、时钟回拨检测、号段模式 |
| 跨分片查询 | 绑定表、广播表、冗余字段、应用层组装、ES宽表 |
| 数据迁移 | 双写代理、Canal CDC、数据校验、灰度切流 |