MySQL 常见的分库分表方案

发表于 阅读次数： 评论数： 本文字数： 2.7k 阅读时长 ≈ 2 分钟

大纲

前言

本文将介绍 MySQL 常见的分库分表方案，包括垂直拆分（纵向拆分）、水平拆分（横向拆分）。

分表

表的拆分有两种方式：水平分表、垂直分表，如下图所示：

水平分表

水平分表是指按照相同的列，依据一定的策略（如：Hash、Range），横向对表的行进行拆分，将一个表中的行拆分到多个表当中。水平分表遵循两个定律：1、拆分后每个表的结构（列）都一样；2、每个表的行数据都不一样。水平分表适用于单表数据量巨大的场景，可有效降低单表压力。

常见的水平分表策略：

策略名称描述适用场景
Range 策略 - 按照字段范围（如用户 ID、订单 ID、日期等）进行拆分，通常遵循左闭右开的规则。
- Range 还可以进行延展，例如年、月、日或地理位置等。		适用于具有明显区间或时间序列特征的数据。
基于哈希切分 - 根据哈希值将数据分散到不同表或库，常见实现方式是哈希取模。
- 哈希切分不完全等同于数值取模拆分，数值取模拆分只是一种特定的哈希方法。
- Hash 分库分表是最普遍的方案。		适用于数据量大且希望各分片均匀分布的场景。
基于目录切分 - 通过维护路由表（映射表）将数据按某种映射关系分配到不同表或数据库。适用于需要高度灵活的分片策略（如需要动态扩容、按照业务维度划分），但要求系统具备路由表管理能力。

垂直分表

垂直分表是指将单个表按照列的不同特性进行拆分，将一个表中的列拆分到多个表中，比如：将表中某些不常用的列，或者是占了大量空间的列拆分出去。垂直分表遵循两个定律：1、拆分后每个表的结构（列）都不一样；2、每个表的行数据几乎都不一样，但还是存在有交集的列，一般是用于存储关联的主键。垂直分表通常按照业务模块的不同进行拆分（如下图所示），也可以按数据的访问模式进行拆分（即将热点数据与冷数据分开存储）。

垂直分表的典型示例：

  • 用户与会员核心信息拆分
  • 商品静态信息独立存储
  • 支付流水相关字段拆分出为独立表
  • 支付与对账数据拆表降低耦合

分库

垂直分库

垂直分库的核心是以表为依据，按照业务属性或业务归属的不同，将不同的表拆分到不同的库中，即专库专用。垂直分库遵循两个定律：1、拆分后，每个库的结构都不相同（表不同）；2、每个库的数据也没有交集（数据不同）。

水平分库

水平分库是根据某个字段（或多个字段）按照特定规则，将数据拆分到多个数据库实例中，使每个库只存储部分数据。例如，可按主键分片：偶数主键记录放入 0 库（或表），奇数主键记录放入 1 库（或表），如下图所示。水平分库通常是在水平分表的基础上进一步进行，将原本分散到多张表的数据继续分布到多台数据库服务器上，以提升整体的并发处理能力。常见的拆分维度包括业务模块、地理区域、用户属性或访问模式（冷热数据）等。

比如，在上面垂直分库的基础上，将 ERP 数据库拆分为 4 个库，还存在 QPS 问题，这个时候可以对库进行水平拆分，如下图所示：

如果上面的水平分库逻辑不好理解，可以理解为对数据库进行水平切割（在表上横切了一刀），原来同一个表中的数据将会分散到不同的数据库中。如下图所示：

最佳实践

常用的分库分表工具

常用的分库分表工具（数据库中间件）如下表所示：

工具名称支持分表支持分库支持的数据类型支持的存储引擎特性
ShardingSphere 是是支持多种数据类型 InnoDB、MyISAM 等支持 SQL 解析、数据分片、读写分离、弹性伸缩等
Vitess 是是主要支持数值和字符串类型主要支持 InnoDB 由 YouTube 开发，支持水平扩展、分布式事务等
MyCAT 是是支持多种数据类型支持多种存储引擎支持 SQL 路由、读写分离、多种数据分片策略等
Cobar 是是主要支持数值和字符串类型主要支持 InnoDB 阿里巴巴开源，支持 SQL 解析、数据分片、读写分离等
Atlas 是否支持多种数据类型主要支持 InnoDB 由奇虎 360 开源，主要支持 MySQL 的读写分离和分表

提示

更多关于 MySQL 数据库中间件的介绍，可以参考 这里 的说明。

什么情况下需要考虑分表

  • 单表数据量超过 500W 行或数据量大于 2GB，此时 MySQL 性能下降，就需要考虑分表（来自阿里开发手册）。
  • 注意，如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
  • 判断是否需要分表，不能脱离单表的数据量大小，仅关注表中的数据行数是不可取的。

分区、分表、分库如何选择

类型触发条件 / 应用场景
分区 1、当单个表的数据量增大，且响应时间变长时，首先考虑的不是分表，而是分区。
2、对于需要按时间段或按某种业务逻辑划分的数据集，考虑分区管理（例如：按年份区分订单数据的场景）
分表当单表数据量过大，超过 500 万行或数据量大于 2GB 时，需要考虑分表。
分库通常在数据库 QPS 过高，数据库连接数不足时，需要考虑分库。

特别注意

  • 从逻辑上看，分区、分表、分库的选择顺序通常为：优先考虑分区，其次是分表，最后才是分库。可以理解为分区 → 分表 → 分库的渐进式演进。
  • 需要注意的是，分区在实际使用中存在一些限制，例如分区键设计不够灵活、DDL 操作可能导致锁表、以及数据倾斜等一系列问题。因此，在真实的业务架构中，分表和分库往往比分区更常用，也更受欢迎。

如何评价分库分表方案的好坏

一个优秀的分库分表方案需要具备以下两个核心特性：

  • (1) 能够避免数据倾斜

    • 即不同分片之间的数据量和访问压力应尽量均衡，避免出现某些库或表数据量过大、访问量过高的情况。

  • (2) 具备可平滑扩容能力

    • 方案应具有持续性，能够通过常见的扩容方式（如翻倍扩容、一致性 Hash 扩容、基于 Snowflake 的分片方案等）实现平滑、可控的扩展，而不需要大量停机或数据重分片。

什么是数据倾斜

数据倾斜是指分库分表设计不合理导致各分片数据量或访问量分布不均。一些分片过载而其他分片轻载，容易在高并发场景下造成延迟波动，影响整体性能和稳定性。

设计分库分表方案的注意事项

在设计分库分表方案时，应重点关注以下方面：

  • (1) 分片键选择

    • 分片键直接影响数据分布均衡性和查询效率，需要保证查询尽可能依赖分片键以避免跨库操作。

  • (2) 数据一致性

    • 多库多表场景下，事务一致性更复杂，需要合理设计，必要时借助最终一致性方案或分布式事务。

  • (3) 避免跨库查询

    • 尽量减少 Join、Group by 等跨库操作，必要时可以通过冗余字段、数据汇总或中间层解决。

  • (4) 监控与运维

    • 分库分表引入系统复杂度，需要完善监控、容量规划、冷热数据迁移与后期扩容方案。

简易的分库分表（基于时间戳）伪代码核心部分实现如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class ShardingExample {

    public static void main(String[] args) {
        DataDAO dataDAO = new DataDAO();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMM");

        // 获取当前日期的数据库名和表名
        String dbName = dateFormat.format(new Date());
        String tableName = "data_" + dbName;

        // 插入数据
        dataDAO.insertData(tableName, "Sample data for " + dbName);

        // 查询数据
        List<String> dataList = dataDAO.fetchData(tableName);
        for (String data : dataList) {
            System.out.println(data);
        }
    }
}

参考资料