引言：

在高并发、高可用的系统中，下面我们将就是MySQL的分库分表在工作中是非常常用的，尤其分别详细讲解 MySQL 的“分库”和“分表”，包括它们的定义、分类、构建方式、应用场景、优缺点以及实际设计中的注意事项。

一、MySQL 分库

1. 什么是分库？

分库是指将原本存储在一个数据库实例中的多个表或数据，按照一定的规则拆分到多个独立的数据库（schema）中，这些数据库行部署在同一个 MySQL 实例上，也可以分布在不同的 MySQL 服务器上。

2. 分库的类型

（1）垂直分库

  • 定义：按业务模块划分，把不同功能的表放到不同的数据库中。

  • 举例：

    • 用户服务 → user_db（包含 user, address 表）
    • 订单服务 → order_db（包含 order, order_item 表）
    • 商品服务 → product_db（包含 product, category 表）

  • ✅ 优点：

    • 业务解耦清晰，便于微服务架构
    • 减少单库连接数压力
    • 提高安全性和权限控制粒度

  • ❌ 缺点：

    • 跨库 JOIN 困难（如查询“用户+订单”信息）
    • 分布式事务繁琐（需要使用 TCC、Saga 等柔性事务）

⚠️ 垂直分库是逻辑上的业务拆分，常用于微服务架构初期的资料隔离。

（2）水平分库

  • 定义：将同一张表的材料根据某个字段（如 user_id）分散到多个数据库中。

  • 举例：

    • 将 user 表按 user_id % 4 拆分为 4 个库：
      • user_db_0：user_id % 4 = 0
      • user_db_1：user_id % 4 = 1
      • ...

  • 数据分布均匀，每个库结构相同。

  • ✅ 优点：

    • 显著降低单库数据量
    • 拥护高并发写入和读取
    • 可借助增加数据库节点实现横向扩展（Scale Out）

  • ❌ 缺点：

    • 扩容时需重新分配数据（除非使用一致性哈希）
    • 跨库聚合查询性能差（如 COUNT、AVG）
    • 主键全局唯一疑问（不能依赖自增 ID）

3.分库的实现方式：

方式说明
应用层路由在代码中判断该访问哪个库（如根据 user_id 计算）
中间件代理启用 MyCat、ShardingSphere-Proxy 等中间件自动路由 SQL
JDBC 层拦截使用 ShardingSphere-JDBC，在 JDBC 层解析并路由 SQL

4. 分库的应用场景

  • 单库连接数接近上限（如 max_connections=1500 已满）
  • 单库 QPS/TPS 过高，CPU 或 IO 达到瓶颈
  • 数据总量过大，影响备份恢复效率
  • 多租户系统中按 tenant_id 隔离数据

5.分库的设计要点

  • 选择合适的分片键（Sharding Key）：如 user_id、order_id，要求分布均匀、高频查询。
  • 避免跨库事务：尽量用最终一致性替代强一致性。
  • 统一主键生成策略：如雪花算法（Snowflake）、UUID、Redis 自增等。
  • 监控与运维工具：统一管理多个数据库实例。

二、MySQL 分表（Table Sharding）

1. 什么是分表？

分表是指将一个大表的内容拆分成多个结构相同的表（或不同结构），以减少单表的数据量，提高查询和维护效率。

2. 分表的类型

（1）垂直分表（Vertical Table Splitting）

  • 定义：将一张宽表按列拆分为多个窄表，通常基于“冷热分离”原则。如按照日期垂直分表

  • ✅ 优点：

    • 减少 I/O 开销（只查 base 表即可）
    • 提升缓存命中率
    • 优化索引大小

  • ❌ 缺点：

    • 查询完整信息需要 JOIN
    • 维护成本上升（外键、事务等）

⚠️ 垂直分表适合字段多、部分字段访问频率差异大的场景。

（2）水平分表（Horizontal Table Splitting）
  • 定义：将一张表的行数据按某种规则拆分到多个结构相同的表中。
  • 常见拆分方式：

全屏复制

拆分方式示例
按时间拆分log_2023, log_2024, order_202301, order_202302
按ID取模user_0, user_1, ..., user_7（user_id % 8）
按范围拆分user_0_100w, user_100w_200w
一致性哈希用于动态扩容，减少数据迁移

  • ✅ 优点：

    • 单表数据量显著下降
    • 查询性能提升（索引更小、B+树层级更低）
    • 易于归档历史数据（如删除旧月表）

  • ❌ 缺点：

    • 跨表查询困难（如 SELECT COUNT(*) FROM ALL user_xxx）
    • 分页查询复杂（LIMIT OFFSET 性能差）
    • DDL 操作需批量执行（如加索引要改所有表）

⚠️ 水平分表常用于日志、订单、消息等快速增长的表。

3. 分表的实现方式：

方法说明
手动分表 + 应用路由开发人员在代码中决定操控哪张表
利用分片框架如 ShardingSphere，配置后自动路由到对应表
视图合并（有限承受）创建 VIEW 联合多个子表（仅适用于查询）
分区表（Partitioning）真正分表，但类似效果）就是MySQL 原生支持 RANGE、LIST、HASH 分区（不

4. 分表的应用场景

  • 单表记录超过千万级，查询变慢
  • 表中有大量历史数据，近期数据访问频繁（适合时间分片）
  • 写入压力大，自增锁竞争严重（可通过分表缓解）
  • 需要定期归档或删除旧数据（如日志表）

5. 分表的设计要点

  • 分片键选择：必须是查询条件常用字段（如 user_id、create_time）
  • 分片数量预估：提前规划未来几年的数据增长，避免频繁扩容
  • 命名规范：如 table_name_shard0000 到 shard1023
  • 自动化运维脚本：批量建表、加索引、迁移数据
  • 结合缓存：热点数据放入 Redis，减少对分表的直接访问

三、分库 vs 分表 对比总结

维度分库分表
拆分维度数据库存储层面表结构层面
主要目的处理连接数、IO、CPU 瓶颈解决单表数据量过大
部署位置可跨服务器通常在同一数据库内
扩展性更强（可加机器）有限（受限于单机资源）
复杂度高（涉及分布式事务）中等
典型场景高并发、大数据量系统大表性能优化

四、根据经验什么时候应该分库、分表

1.一般多少数据量应该分表？

单表数据量建议动作
超过 500 万行开始关注，评估是否需要分表
超过 1000 万行（1千万）强烈建议考虑分表
超过 5000 万行必须分表，否则性能严重下降
超过 1 亿行已经属于“大表”，必须已分表或使用分布式数据库
如果不分会怎么样？
问题说明
B+树索引层级变深内容越多，B+树层数增加（3层 → 4层），导致磁盘 I/O 增加
缓冲池命中率降低大表索引无法完全加载到 innodb_buffer_pool，频繁读磁盘
锁竞争加剧DML 操作（UPDATE/DELETE）容易引发行锁、间隙锁争用
DDL 变慢甚至阻塞如 ALTER TABLE ADD INDEX 可能耗时数小时
备份恢复困难单表过大导致 mysqldump 或 xtrabackup 时间过长
主从延迟风险高大事务同步慢，造成主从延迟

✅ 正确做法：

  • 监控单表数据增长趋势
  • 当接近 500万~1000万行时，启动分表方案设计
  • 在 1000万行以内完成分表迁移

2.什么情况下应该分库？

判断维度具体表现是否应分库
1. 单库连接数接近上限show status like 'Threads_connected'; 接近 max_connections（如 1500）✅ 必须分库
2. CPU 或 IO 达到瓶颈主库 CPU 长期 >70%，磁盘 IOPS 饱和✅ 考虑分库
3. 写入 QPS 过高单库写入 TP99 延迟上升，事务排队✅ 分库缓解压力
4. 材料总量过大单库总数据量超过 100GB ~ 1TB✅ 建议分库
5. 业务模块耦合严重用户、订单、商品都在一个库，维护混乱✅ 垂直分库解耦
6. 多租户隔离需求SaaS 系统中不同客户数据需物理隔离✅ 按 tenant_id 分库
7. 微服务架构演进各服务应拥有独立数据库✅ 垂直分库支持微服务
分库的数据量参考标准（经验）
单库总数据量建议
> 100GB关注，准备垂直拆分
> 500GB强烈建议水平分库或垂直分库
> 1TB必须分库，否则运维困难