一、分库分表基础概念
1.1 什么是分库分表？
分库分表是通过某种规则将原本存储在单一数据库/表中的数据分散到多个数据库/表中的技术方案，主要解决两方面问题：
-
性能瓶颈：单机QPS/TPS有限
-
存储瓶颈：单表数据量过大导致查询效率下降
1.2 分库分表的两种主要形式
| 类型 | 描述 | 优点 | 缺点 |
|---|---|---|---|
| 垂直拆分 | 按字段拆分，把不常用的字段或大字段拆分到其他表 | 减少IO，热点数据更集中 | 未解决单表数据量过大的问题 |
| 水平拆分 | 按行拆分，将表的数据分散到不同的表或库中 | 彻底解决单表数据量过大问题 | 跨库查询复杂，事务处理困难 |
1.3 何时需要考虑分库分表？
主要考量指标：
-
数据量：单表数据量超过500万行（非绝对，取决于硬件和业务）
-
增长速度：预计半年内会达到上述阈值
-
性能指标：查询响应时间明显变慢（>500ms）
-
并发量：单机数据库QPS超过2000（SSD环境）
业务场景：
-
用户表按用户ID分表
-
订单表按时间分表
-
日志表按月分库
二、垂直拆分详解与实现
2.1 垂直分库
典型场景：将不同业务模块的表拆分到不同数据库
-- 原始数据库（所有业务混在一起）
CREATE TABLE users (
id BIGINT PRIMARY KEY,
name VARCHAR(50),
...
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(10,2),
...
);
-- 垂直分库后
-- 用户库
CREATE DATABASE user_db;
USE user_db;
CREATE TABLE users (
id BIGINT PRIMARY KEY,
name VARCHAR(50),
...
);
-- 订单库
CREATE DATABASE order_db;
USE order_db;
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(10,2),
...
);
2.2 垂直分表
典型场景：将大字段或不常用字段拆分到扩展表
-- 原始用户表
CREATE TABLE users (
id BIGINT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(100),
avatar BLOB,
profile TEXT,
last_login_time DATETIME,
...
);
-- 垂直分表后
-- 主表存储常用字段
CREATE TABLE users (
id BIGINT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(100),
last_login_time DATETIME,
...
);
-- 扩展表存储大字段和不常用字段
CREATE TABLE users_ext (
user_id BIGINT PRIMARY KEY,
avatar BLOB,
profile TEXT,
...
);
三、水平拆分策略与实现
3.1 水平分片策略
3.1.1 范围分片（Range）
实现方式：按照某个字段的范围进行分片
-- 订单表按创建时间范围分表
CREATE TABLE orders_2023q1 (
id BIGINT PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(10,2),
create_time DATETIME,
...
) COMMENT '2023年第一季度订单';
CREATE TABLE orders_2023q2 (
-- 相同结构
) COMMENT '2023年第二季度订单';
优点：
-
易于管理，数据按时间顺序分布
-
范围查询效率高
缺点：
-
可能导致热点数据集中（最新表压力大）
3.1.2 哈希分片（Hash）
实现方式：对分片键取模确定分片位置
-- 用户表按ID哈希分表（分为4个表）
CREATE TABLE users_0 (
id BIGINT PRIMARY KEY,
name VARCHAR(50),
...
) COMMENT '用户表分片0';
CREATE TABLE users_1 (
-- 相同结构
) COMMENT '用户表分片1';
CREATE TABLE users_2 (
-- 相同结构
) COMMENT '用户表分片2';
CREATE TABLE users_3 (
-- 相同结构
) COMMENT '用户表分片3';
路由逻辑：
// 分片路由算法示例
int tableSuffix = userId % 4; // 计算表后缀
String tableName = "users_" + tableSuffix;
优点：
-
数据分布均匀
-
避免热点问题
缺点：
-
扩容困难（需要数据迁移）
-
范围查询效率低
3.1.3 一致性哈希
改进的哈希算法，减少扩容时的数据迁移量：
// 一致性哈希实现示例
public class ConsistentHash {
private final TreeMap<Long, String> virtualNodes = new TreeMap<>();
private final int virtualNodeCount;
public ConsistentHash(List<String> nodes, int virtualNodeCount) {
this.virtualNodeCount = virtualNodeCount;
for (String node : nodes) {
addNode(node);
}
}
public void addNode(String node) {
for (int i = 0; i < virtualNodeCount; i++) {
long hash = hash("VN-" + node + "-" + i);
virtualNodes.put(hash, node);
}
}
public String getNode(String key) {
long hash = hash(key);
SortedMap<Long, String> tail = virtualNodes.tailMap(hash);
if (tail.isEmpty()) {
return virtualNodes.get(virtualNodes.firstKey());
}
return tail.get(tail.firstKey());
}
private long hash(String key) {
// 使用CRC32或其他哈希算法
}
}
3.2 分片键选择原则
-
高区分度：字段值分布均匀（如用户ID）
-
业务相关性：常用查询条件（如订单ID、用户ID）
-
避免修改：分片键一旦确定不应修改
-
非空唯一：最好选择主键或唯一键
错误示范：
-- 使用性别作为分片键（区分度太低）
CREATE TABLE users_male (...);
CREATE TABLE users_female (...);
四、MySQL分库分表实现方案
4.1 应用层实现
4.1.1 手动路由
// 手动路由示例
public class OrderDao {
private DataSource orderDB0;
private DataSource orderDB1;
public void saveOrder(Order order) {
String dbName = "order_db_" + (order.getUserId() % 2);
DataSource dataSource = getDataSource(dbName);
try (Connection conn = dataSource.getConnection()) {
// 执行SQL
}
}
}
4.1.2 使用ShardingSphere-JDBC
# ShardingSphere-JDBC配置示例(yaml)
spring:
shardingsphere:
datasource:
names: ds0,ds1
ds0: # 数据源配置
ds1: # 数据源配置
sharding:
tables:
t_order:
actual-data-nodes: ds$->{0..1}.t_order_$->{0..15}
table-strategy:
inline:
sharding-column: order_id
algorithm-expression: t_order_$->{order_id % 16}
database-strategy:
inline:
sharding-column: user_id
algorithm-expression: ds$->{user_id % 2}
4.2 中间件实现
4.2.1 MyCat配置
<!-- MyCat schema.xml 配置 -->
<schema name="testdb" checkSQLschema="false">
<table name="users" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" />
</schema>
<dataNode name="dn1" dataHost="host1" database="db1" />
<dataNode name="dn2" dataHost="host2" database="db2" />
<dataHost name="host1" maxCon="1000" balance="0">
<heartbeat>select user()</heartbeat>
<connectionConfig>
<property name="url">jdbc:mysql://host1:3306</property>
</connectionConfig>
</dataHost>
4.2.2 ProxySQL配置
-- ProxySQL路由规则配置
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'db1.example.com',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'db2.example.com',3306);
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FROM users WHERE id=\d+$',10,1);
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply)
VALUES (2,1,'^SELECT.*FROM orders WHERE user_id=\d+$',20,1);
五、分库分表后的挑战与解决方案
5.1 分布式ID生成方案
5.1.1 雪花算法（Snowflake）
public class SnowflakeIdGenerator {
private final long datacenterId;
private final long workerId;
private long sequence = 0L;
private long lastTimestamp = -1L;
public synchronized long nextId() {
long timestamp = timeGen();
if (timestamp < lastTimestamp) {
throw new RuntimeException("Clock moved backwards");
}
if (lastTimestamp == timestamp) {
sequence = (sequence + 1) & sequenceMask;
if (sequence == 0) {
timestamp = tilNextMillis(lastTimestamp);
}
} else {
sequence = 0L;
}
lastTimestamp = timestamp;
return ((timestamp - twepoch) << timestampLeftShift)
| (datacenterId << datacenterIdShift)
| (workerId << workerIdShift)
| sequence;
}
}
5.1.2 数据库自增序列
-- ID生成表
CREATE TABLE sequence (
name VARCHAR(64) PRIMARY KEY,
current_value BIGINT NOT NULL,
increment INT NOT NULL DEFAULT 1
);
-- 获取下一个ID的函数
DELIMITER //
CREATE FUNCTION next_id(seq_name VARCHAR(64)) RETURNS BIGINT
BEGIN
DECLARE current_val BIGINT;
UPDATE sequence SET current_value = current_value + increment
WHERE name = seq_name;
SELECT current_value INTO current_val FROM sequence
WHERE name = seq_name;
RETURN current_val;
END //
DELIMITER ;
5.2 跨库查询解决方案
5.2.1 字段冗余
-- 订单表冗余用户信息
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT,
user_name VARCHAR(50), -- 冗余字段
amount DECIMAL(10,2),
...
);
5.2.2 全局表（广播表）
-- 所有库都存储相同的地区表
CREATE TABLE global_region (
id INT PRIMARY KEY,
name VARCHAR(100),
parent_id INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
5.2.3 数据聚合
// 在应用层聚合多个分片的查询结果
public List<Order> getOrdersByUser(long userId) {
// 确定所有可能的分片
List<DataSource> dataSources = getAllOrderDataSources();
List<Order> result = new ArrayList<>();
for (DataSource ds : dataSources) {
try (Connection conn = ds.getConnection()) {
List<Order> partial = orderDao.findByUserId(conn, userId);
result.addAll(partial);
}
}
return result;
}
5.3 分布式事务解决方案
5.3.1 XA协议
// 使用XA协议实现分布式事务
public void transferMoney(DataSource ds1, DataSource ds2, long amount) throws Exception {
XAConnection xaConn1 = ds1.getXAConnection();
XAResource xaRes1 = xaConn1.getXAResource();
Xid xid1 = new MyXid(100, new byte[]{0x01}, new byte[]{0x02});
XAConnection xaConn2 = ds2.getXAConnection();
XAResource xaRes2 = xaConn2.getXAResource();
Xid xid2 = new MyXid(100, new byte[]{0x01}, new byte[]{0x03});
try {
xaRes1.start(xid1, XAResource.TMNOFLAGS);
Connection conn1 = xaConn1.getConnection();
// 执行第一个数据库操作
conn1.createStatement().executeUpdate("UPDATE accounts SET balance = balance - " + amount + " WHERE user_id = 1");
xaRes1.end(xid1, XAResource.TMSUCCESS);
xaRes2.start(xid2, XAResource.TMNOFLAGS);
Connection conn2 = xaConn2.getConnection();
// 执行第二个数据库操作
conn2.createStatement().executeUpdate("UPDATE accounts SET balance = balance + " + amount + " WHERE user_id = 2");
xaRes2.end(xid2, XAResource.TMSUCCESS);
int ret1 = xaRes1.prepare(xid1);
int ret2 = xaRes2.prepare(xid2);
if (ret1 == XAResource.XA_OK && ret2 == XAResource.XA_OK) {
xaRes1.commit(xid1, false);
xaRes2.commit(xid2, false);
} else {
xaRes1.rollback(xid1);
xaRes2.rollback(xid2);
throw new Exception("Transaction failed");
}
} finally {
xaConn1.close();
xaConn2.close();
}
}
5.3.2 柔性事务（Saga）
public class OrderSaga {
@SagaStart
public void createOrder(Order order) {
// 1. 扣减库存
inventoryService.reduceStock(order.getProductId(), order.getQuantity());
// 2. 创建订单
orderDao.create(order);
// 3. 扣减余额
accountService.debit(order.getUserId(), order.getAmount());
}
@Compensate
public void compensateCreateOrder(Order order) {
// 1. 恢复库存
inventoryService.addStock(order.getProductId(), order.getQuantity());
// 2. 删除订单
orderDao.delete(order.getId());
// 3. 恢复余额
accountService.credit(order.getUserId(), order.getAmount());
}
}
六、分库分表最佳实践
6.1 分片数量规划
-
单表数据量建议：控制在500万-1000万行
-
分片数量公式：分片数 = 总数据量 / 单表建议数据量
-
预留扩容空间：建议一次规划2-3年的容量
6.2 分片扩容方案
扩容步骤：
-
准备新数据库服务器
-
配置双写（新旧库同时写入）
-
数据迁移（使用工具增量同步）
-
校验数据一致性
-
切换读流量到新库
-
停用旧库
使用ShardingSphere进行扩容：
# 扩容后配置
spring:
shardingsphere:
sharding:
tables:
t_order:
actual-data-nodes: ds$->{0..3}.t_order_$->{0..31}
table-strategy:
inline:
sharding-column: order_id
algorithm-expression: t_order_$->{order_id % 32}
database-strategy:
inline:
sharding-column: user_id
algorithm-expression: ds$->{user_id % 4}
6.3 监控与维护
关键监控指标：
-
分片均衡性：各分片数据量和访问量是否均衡
-
慢查询：分片后的SQL性能
-
连接数：各分库连接数使用情况
-
硬件资源：CPU、内存、磁盘IO
维护操作：
-- 定期检查分片表状态
ANALYZE TABLE orders_2023q1;
OPTIMIZE TABLE orders_2023q1;
-- 数据归档（将旧数据迁移到历史库）
INSERT INTO history_db.orders_2022
SELECT * FROM orders_2022q1 WHERE create_time < '2023-01-01';
DELETE FROM orders_2022q1 WHERE create_time < '2023-01-01';
七、MySQL分库分表实战案例
7.1 电商订单系统分库分表
业务场景：
-
日订单量10万+
-
订单表半年超过2000万行
-
需要按用户ID查询订单历史
解决方案：
-
分库策略：按用户ID哈希分4个库
-
分表策略：按订单创建时间季度分表
-
路由规则：
库路由：user_id % 4
表路由：create_time所在季度
SQL示例：
-- 创建分片表
CREATE TABLE orders_db0.orders_2023q1 (
id BIGINT PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(10,2),
create_time DATETIME,
INDEX idx_user_id (user_id),
INDEX idx_create_time (create_time)
) COMMENT '2023Q1订单表 - 分库0';
-- 其他分库分表类似创建...
7.2 社交平台用户关系分库分表
业务场景：
-
用户量超过1亿
-
关注关系表超过50亿行
-
需要查询粉丝列表和关注列表
解决方案：
-
分库策略：按用户ID哈希分16个库
-
分表策略：按关注者ID哈希分16个表
-
冗余设计：正向关系和反向关系分开存储
表结构设计：
-- 关注关系表（用户A关注用户B）
CREATE TABLE relation_db0.user_relation_0 (
user_id BIGINT COMMENT '关注者用户ID',
follow_id BIGINT COMMENT '被关注用户ID',
create_time DATETIME,
PRIMARY KEY (user_id, follow_id),
INDEX idx_follow_id (follow_id)
) COMMENT '关注关系表 - 分库0分表0';
-- 粉丝关系表（用户B被用户A关注）
CREATE TABLE relation_db0.user_fans_0 (
user_id BIGINT COMMENT '被关注用户ID',
fan_id BIGINT COMMENT '粉丝用户ID',
create_time DATETIME,
PRIMARY KEY (user_id, fan_id),
INDEX idx_fan_id (fan_id)
) COMMENT '粉丝关系表 - 分库0分表0';
扫一扫
1652
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
