MySQL分库分表策略
MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。
一、为什么需要分库分表
#
1.1 单库瓶颈
| 瓶颈类型 |
具体表现 |
临界值（参考） |
| 数据量 |
查询和索引维护变慢 |
5000万-1亿行 |
| 并发连接 |
连接数耗尽 |
1000-2000连接 |
| 写入性能 |
写入延迟增加 |
5000-10000 TPS |
| 存储容量 |
磁盘空间不足 |
1-2TB |
#
1.2 分库 vs 分表
分表：解决单表数据量过大问题
- 同一数据库中，表拆分为多张结构相同的表
- user → user_0, user_1, user_2
分库：解决单库性能瓶颈
- 数据分布到多个数据库实例
- 每个库可以独立部署在不同服务器
分库分表：同时解决容量和性能问题
二、拆分策略
#
2.1 垂直拆分
垂直分库：按业务模块拆分
数据库拆分前：
一个数据库包含：user, order, product, log...
垂直分库后：
用户库：user, user_profile, user_address
订单库：order, order_item, payment
商品库：product, category, inventory
|
垂直分表：按字段拆分
表拆分前：
user表：id, name, avatar, bio, settings, ...（100+字段）
垂直分表后：
user_basic：id, name, phone, email（常用字段）
user_detail：id, avatar, bio, settings（大字段/不常用）
user_ext：id, ext_json（扩展字段）
|
适用场景：
- 垂直分库：业务耦合度低，可以独立演进
- 垂直分表：表字段过多，冷热数据分离
#
2.2 水平拆分
水平分表：同一库内分多张表
user表 → user_0, user_1, user_2, ... user_15
|
水平分库：数据分布到多个库
DB0: user_0, user_1, user_2, user_3
DB1: user_4, user_5, user_6, user_7
DB2: user_8, user_9, user_10, user_11
DB3: user_12, user_13, user_14, user_15
|
适用场景：
三、分片算法
#
3.1 哈希取模
int shard = userId % 16;
String tableName = "user_" + shard;
|
优点：
缺点：
#
3.2 范围分片
if (userId >= 0 && userId < 1000000) {
table = "user_0";
} else if (userId < 2000000) {
table = "user_1";
}
|
优点：
缺点：
- 可能造成数据倾斜（新数据集中在最后一个分片）
- 热点问题
#
3.3 一致性哈希
TreeMap<Long, String> virtualNodes = new TreeMap<>();
for (String node : physicalNodes) {
for (int i = 0; i < 160; i++) {
long hash = hash(node + "#" + i);
virtualNodes.put(hash, node);
}
}
long hash = hash(key);
Map.Entry<Long, String> entry = virtualNodes.ceilingEntry(hash);
if (entry == null) {
entry = virtualNodes.firstEntry();
}
String node = entry.getValue();
|
优点：
缺点：
#
3.4 分片算法对比
| 算法 |
数据分布 |
扩容成本 |
范围查询 |
热点问题 |
| 哈希取模 |
均匀 |
高 |
不支持 |
无 |
| 范围分片 |
可能倾斜 |
低 |
支持 |
可能有 |
| 一致性哈希 |
较均匀 |
低 |
不支持 |
无 |
#
3.5 分片键选择
选择原则：
- 高频查询条件：大部分查询都能命中分片键
- 数据均匀：避免数据倾斜
- 业务无关：尽量不使用业务含义强的字段
常见选择：
- 用户ID
- 订单ID（雪花算法）
- 租户ID（SaaS系统）
四、ShardingSphere实践
#
4.1 引入依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>5.4.0</version>
</dependency>
|
#
4.2 配置分库分表
dataSources:
ds_0:
url: jdbc:mysql://localhost:3306/db0
username: root
password: password
ds_1:
url: jdbc:mysql://localhost:3306/db1
username: root
password: password
rules:
- !SHARDING
tables:
user:
actualDataNodes: ds_${0..1}.user_${0..15}
tableStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: user_table_hash
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: user_database_hash
shardingAlgorithms:
user_table_hash:
type: INLINE
props:
algorithm-expression: user_${user_id % 16}
user_database_hash:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
|
#
4.3 代码中使用
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
public User getUser(Long userId) {
return userMapper.selectById(userId);
}
public void createUser(User user) {
user.setUserId(IdWorker.getId());
userMapper.insert(user);
}
}
|
#
4.4 绑定表
具有关联关系的表使用相同分片策略：
bindingTables:
- order,order_item
|
这样order和order_item会路由到相同的分片，避免跨库JOIN。
五、分库分表后的问题
#
5.1 分布式主键
方案一：雪花算法
public class SnowflakeIdWorker {
private long workerId;
private long datacenterId;
private long sequence = 0;
public synchronized long nextId() {
long timestamp = System.currentTimeMillis();
if (timestamp < lastTimestamp) {
throw new RuntimeException("Clock moved backwards");
}
if (timestamp == lastTimestamp) {
sequence = (sequence + 1) & 4095;
if (sequence == 0) {
timestamp = tilNextMillis(lastTimestamp);
}
} else {
sequence = 0;
}
lastTimestamp = timestamp;
return ((timestamp - EPOCH) << 22)
| (datacenterId << 17)
| (workerId << 12)
| sequence;
}
}
|
方案二：号段模式
CREATE TABLE id_generator (
biz_tag VARCHAR(32) PRIMARY KEY,
max_id BIGINT NOT NULL,
step INT NOT NULL,
description VARCHAR(255)
);
|
#
5.2 跨分片查询
分页查询问题：
SELECT * FROM user ORDER BY create_time LIMIT 100000, 10;
|
分片后需要：
- 在每个分片执行LIMIT 100000, 10
- 汇总结果排序
- 取最终的10条
优化方案：
- 限制深分页
- 使用游标分页（每次带查询条件）
- 建立全局索引表
#
5.3 跨分片JOIN
问题：分片键不同的表JOIN需要跨库查询
解决方案：
- 绑定表：关联表使用相同分片策略
- 字段冗余：在需要JOIN的表中冗余字段
- 全局表：小表在每个分片都存一份
- 应用层组装：分别查询后在代码中JOIN
#
5.4 事务一致性
问题：跨分片事务无法使用本地事务
解决方案：
- 最终一致性：使用消息队列
- Seata分布式事务
- Saga模式：补偿事务
- 避免跨分片事务：通过业务设计规避
@GlobalTransactional
public void createOrder(Order order) {
orderMapper.insert(order);
inventoryService.deduct(order.getProductId(), order.getQuantity());
userService.deductBalance(order.getUserId(), order.getAmount());
}
|
#
5.5 数据迁移
平滑迁移方案：
- 双写阶段：应用同时写入旧库和新分片
- 数据同步：使用 Canal 等工具同步历史数据
- 数据校验：对比旧库和新分片数据
- 切换读流量：先切换部分读流量到新分片
- 切换写流量：确认无误后切写流量
- 下线旧库：观察一段时间，下线旧库
六、分库分表示例设计
#
6.1 电商系统分片设计
用户模块（分库分表）：
DB: user_0, user_1
Table: user_0 ~ user_15
分片键：user_id
订单模块（分库分表）：
DB: order_0, order_1
Table: order_0 ~ order_15
分片键：user_id（按买家）或 order_id
商品模块（全局表）：
每个库都有完整的商品数据
Table: product
库存模块（分库）：
DB: inventory_0, inventory_1
分片键：product_id
|
#
6.2 订单表分片策略
order:
actualDataNodes: ds_${0..1}.order_${0..15}
tableStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: order_hash
order_index:
actualDataNodes: ds_${0..1}.order_index_${0..15}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: order_index_hash
|
七、避免过度拆分
#
7.1 拆分前的优化
在拆分前，先尝试：
- 索引优化：确保查询都有合适的索引
- SQL优化：避免全表扫描、深分页
- 读写分离：主从复制分担读压力
- 缓存：Redis缓存热点数据
- 归档：历史数据归档到历史库
#
7.2 拆分的时机
满足以下条件再考虑拆分：
- 单表数据量 > 5000万
- 单库QPS > 5000
- 读写分离后仍无法满足
- 优化空间已经很小
八、总结
| 拆分方式 |
解决什么问题 |
复杂度 |
| 垂直分库 |
业务耦合、连接数 |
中 |
| 垂直分表 |
字段过多、冷热数据 |
低 |
| 水平分表 |
单表数据量 |
中 |
| 水平分库 |
单库性能瓶颈 |
高 |
| 问题 |
解决方案 |
| 分布式ID |
雪花算法、号段模式 |
| 跨分片查询 |
限制分页、全局索引 |
| 跨分片JOIN |
绑定表、字段冗余 |
| 事务一致性 |
Seata、消息队列 |
| 数据迁移 |
双写+同步+校验 |
分库分表是”最后的手段”。在实施前，充分评估业务需求和拆分成本，选择合适的拆分策略和中间件，做好平滑迁移方案。
核心要点
索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型
SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN
事务隔离级别的选择：根据业务需求选择合适的级别
常见的锁问题：行锁、表锁、死锁的处理
总结
MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。
