实用指南：MySQL分库分表

1. 为什么要分库分表？

当单库单表数据量过大时，会出现：

  • 查询变慢：索引树过大，磁盘 IO 增加。
  • 写入瓶颈：锁竞争严重，事务冲突多。
  • 存储压力：单表数据量超出存储引擎的最佳性能范围。
  • 备份恢复慢：数据量大时，备份和恢复耗时长。

经验值：

  • InnoDB 单表数据量建议 不超过千万级（具体取决于硬件和索引设计）。
  • 单库数据量建议 不超过 TB 级。

2. 分库分表的分类

2.1 垂直拆分（Vertical Sharding）

  • 按业务模块拆分，不同业务放到不同库。
  • 例如：
    • 用户库（user_db）
    • 订单库（order_db）
    • 商品库（product_db）

优点：

  • 业务隔离，易于维护。
  • 不同库可部署在不同服务器，分散压力。

缺点：

  • 不能解决单表数据量过大的问题。
  • 跨库查询复杂。

2.2 水平拆分（Horizontal Sharding）

  • 按数据范围拆分，同一业务的数据分散到多个库或表。
  • 常见规则：
    • 按 ID 范围拆分（如 1~100万放库1，100万~200万放库2）
    • 按哈希取模拆分（如 user_id % 4 → 4 个库）
    • 按时间拆分（如按月份或年份分表）

优点：

  • 单表数据量减少，查询性能提升。
  • 可线性扩展。

缺点：

  • 跨库跨表查询复杂。
  • 分片规则变更成本高。

3. 分库分表的实现方式

3.1 应用层分片

  • 在应用代码中，根据分片规则决定访问哪个库/表。
  • 例如：
int shardId = userId % 4; String tableName = "user_" + shardId;

优点：

  • 灵活可控。
  • 不依赖中间件。

缺点：

  • 代码侵入性强。
  • 跨分片查询需要应用层聚合。

3.2 中间件分片

  • 使用数据库中间件自动路由 SQL。
  • 常见中间件：
    • ShardingSphere（Java）
    • MyCAT（Java）
    • Vitess（Go）
    • Cobar（已停止维护）

优点：

  • 对应用透明，SQL 路由由中间件处理。
  • 支持分布式事务（部分中间件）。

缺点：

  • 中间件本身可能成为性能瓶颈。
  • 部署和维护成本高。

4. 查询处理

4.1 单分片查询

  • 根据分片规则直接定位到目标库/表，性能接近单库。

4.2 跨分片查询

  • 应用层聚合：
    1. 在多个分片执行相同 SQL。
    2. 将结果在应用层合并。
  • 中间件聚合：
    • 中间件执行多分片查询并合并结果。

4.3 分页查询

  • 单分片分页：正常 LIMIT。
  • 跨分片分页：
    • 各分片取出数据 → 合并排序 → 再分页。
    • 性能较差，建议避免。

5. 分库分表的常见问题

问题说明解决方案
跨分片事务多个分片同时更新使用分布式事务（XA / TCC / 可靠消息）
跨分片查询需要聚合多个分片数据应用层或中间件聚合
分片规则变更规则调整需迁移数据设计时预留扩展空间
热点数据某分片访问量过高热点分片拆分或引入缓存
ID 唯一性多分片主键冲突使用雪花算法 / UUID / 分布式 ID 服务

6. 分库分表设计建议

  1. 优先垂直拆分 → 再考虑水平拆分。
  2. 分片规则稳定，避免频繁变更。
  3. 预留扩展空间，如 user_id % 64，先用前 4 个分片。
  4. 引入缓存，减少跨分片查询压力。
  5. 监控分片负载，及时调整热点分片。

7. 总结对比表

类型拆分方式优点缺点适用场景
垂直拆分按业务模块拆分业务隔离，易维护不能解决单表数据量大多业务系统
水平拆分按数据范围/哈希拆分单表数据量减少，可扩展跨分片查询复杂单表数据量大

✅ 经验结论：

  • 如果是多业务系统 → 先垂直拆分。
  • 如果是单业务但数据量大 → 水平拆分。
  • 分库分表后，跨分片查询和事务是最大挑战，要提前设计好分片规则和 ID 生成策略。

一个 垂直拆分 和 水平拆分 的完整案例

1. 垂直拆分案例

场景：一个电商系统，用户表和订单表数据量都很大，且业务逻辑不同。
我们将 用户数据 和 订单数据 拆到不同的数据库中。

1.1 数据库设计

用户库（user_db）
CREATE TABLE user_info (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE user_profile (
    user_id BIGINT PRIMARY KEY,
    gender ENUM('male','female','other'),
    birthday DATE,
    address VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user_info(user_id)
);
订单库（order_db）
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    status ENUM('pending','paid','shipped','completed','cancelled') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

1.2 更新流程

新增订单：

  1. 在 user_db 查询用户是否存在。
  2. 在 order_db 插入订单记录。
-- 检查用户
SELECT * FROM user_db.user_info WHERE user_id = 1001;
-- 插入订单
INSERT INTO order_db.orders(user_id, product_id, quantity, total_price, status)
VALUES(1001, 2005, 2, 199.98, 'pending');

1.3 查询流程

查询某用户的订单列表（跨库查询）：

  • 应用层处理：先查用户信息，再查订单库。
-- 查用户信息
SELECT * FROM user_db.user_info WHERE user_id = 1001;
-- 查订单
SELECT * FROM order_db.orders WHERE user_id = 1001 ORDER BY created_at DESC;

注意：跨库不能直接用 JOIN，需要应用层聚合。

2. 水平拆分案例

场景：订单表数据量非常大（上亿条），我们按 user_id % 4 拆分成 4 张表。

2.1 数据库设计

订单库（order_db）
CREATE TABLE orders_0 (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    status ENUM('pending','paid','shipped','completed','cancelled') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id(user_id)
);
-- 复制结构生成 orders_1, orders_2, orders_3

2.2 更新流程

新增订单：

  1. 根据 user_id % 4 计算分片表。
  2. 插入对应的表。
-- 例如 user_id = 1005
-- 1005 % 4 = 1 → 插入 orders_1
INSERT INTO orders_1(user_id, product_id, quantity, total_price, status)
VALUES(1005, 3002, 1, 99.99, 'pending');

2.3 查询流程

查询某用户的订单列表：

  1. 根据 user_id % 4 定位分片表。
  2. 查询对应表。
-- user_id = 1005 → 1005 % 4 = 1
SELECT * FROM orders_1 WHERE user_id = 1005 ORDER BY created_at DESC;

跨用户查询（例如统计所有订单总额）：

  • 应用层聚合：
SELECT SUM(total_price) FROM orders_0
UNION ALL
SELECT SUM(total_price) FROM orders_1
UNION ALL
SELECT SUM(total_price) FROM orders_2
UNION ALL
SELECT SUM(total_price) FROM orders_3;
  • 在应用层将结果相加。

3. 垂直 vs 水平拆分对比

项目垂直拆分水平拆分
拆分依据按业务模块按数据范围/哈希
跨库查询常见较少（单用户查询不跨库）
扩展性增加新业务库增加分片表/库
实现难度中等高（需要分片规则和路由）
适用场景多业务系统单业务数据量超大

✅ 经验建议：

  • 如果是多业务系统 → 先垂直拆分。
  • 如果是单业务但数据量大 → 水平拆分。
  • 水平拆分一定要提前设计好分片规则，否则后期迁移成本极高。

疑问
水平拆分后，如果需要增加分片表（扩容），原来的分片算法是否还能继续用

这个问题在实际生产中非常常见，我给你详细分析一下，并告诉你如何设计才能避免后期扩容带来的灾难性迁移。

1. 为什么直接加表会导致算法失效？

假设我们一开始用：

分片规则：user_id % 4 → orders_0 ~ orders_3

如果后期加到 8 张表：

user_id % 8 → orders_0 ~ orders_7

那么原来 user_id = 1005：

  • 旧规则：1005 % 4 = 1 → orders_1
  • 新规则：1005 % 8 = 5 → orders_5

结果：老数据在 orders_1，新数据在 orders_5，查询时就会错位，必须迁移老数据，非常痛苦。

2. 解决方案

方案 1：固定分片槽（推荐）

  • 一开始就设计一个固定的分片槽数量（比如 1024 个槽），不管当前有多少表。
  • 分片规则：
slot = user_id % 1024
  • 槽映射到表：
    • 初期：1024 个槽映射到 4 张表（每表 256 个槽）。
    • 扩容：重新映射槽到更多表（比如 8 张表，每表 128 个槽）。
  • 好处：分片算法不变，扩容只需要调整槽映射，不需要迁移数据。

方案 2：一致性哈希

  • 使用一致性哈希环，将分片节点映射到表。
  • 新增表时，只会迁移一部分数据（哈希环重新分布）。
  • 常用于缓存系统（如 Redis Cluster），但在数据库中实现复杂，需要额外的路由层。

方案 3：预留分片位

  • 一开始就用大模数，比如：
user_id % 64
  • 初期只用前 4 张表（每表 16 个分片）。
  • 扩容时启用更多分片，不改变算法。
  • 缺点：初期表数据分布不均衡，浪费空间。

3. 实际案例：固定槽映射

假设我们用 1024 槽：

int slot = userId % 1024;
int tableIndex = slot / slotsPerTable; // slotsPerTable = 256 初期
String tableName = "orders_" + tableIndex;

  • 初期：

    • orders_0 → 槽 0~255
    • orders_1 → 槽 256~511
    • orders_2 → 槽 512~767
    • orders_3 → 槽 768~1023

  • 扩容到 8 张表：

    • orders_0 → 槽 0~127
    • orders_1 → 槽 128~255
    • orders_2 → 槽 256~383
    • orders_3 → 槽 384~511
    • orders_4 → 槽 512~639
    • orders_5 → 槽 640~767
    • orders_6 → 槽 768~895
    • orders_7 → 槽 896~1023

查询时算法不变：

slot = userId % 1024;
tableIndex = slot / slotsPerTable;

只是 slotsPerTable 改了，老数据仍在原表，不需要迁移。

4. 总结对比表

方案是否迁移数据扩容复杂度实现难度适用场景
直接改模数需要迁移全部数据高低小规模临时系统
固定槽映射不迁移低中大型系统，推荐
一致性哈希部分迁移中高分布式缓存/数据库
预留分片位不迁移低低数据量可预估

✅ 结论：
如果你要做长期可扩展的水平拆分，必须在一开始就设计固定槽映射或预留分片位，否则后期加表会让你陷入全量数据迁移的噩梦。

posted on 2026-01-29 15:50  ljbguanli  阅读(3)  评论(0)    收藏  举报

博客园  ©  2004-2026
浙公网安备 33010602011771号 浙ICP备2021040463号-3