一文搞懂MySQL数据库分库分表

当数据量过大时，分库分表是常见的优化手段。分库相对简单，分表则涉及更多细节。以下从基础知识、中间件、分布式事务、选择依据及设计实践等方面全面解析MySQL水平分库分表。

1. 基础知识

分库

• 垂直分库：按业务模块切分，不同模块的表分布到不同数据库。例如电商系统分为用户库、商品库、订单库，各库独立变更且互不影响。

分表

• 垂直分表：基于列字段拆分“大表”，通常因表设计不合理需优化。例如将包含学生、老师、课程、成绩信息的表拆分为学生表、课程表、成绩表。
• 水平分表：针对数据量巨大的单表（如订单表），按规则（如RANGE、HASH取模）切分到多张表，但表仍在同一库中，存在IO瓶颈，不建议单独使用。
• 水平分库分表：将单表数据切分到多个服务器的库与表中，表中数据集合不同，有效缓解单机和单库性能瓶颈，突破IO、连接数、硬件资源限制。

• Sharding（分片）：思想源于分区，但数据库分区是数据对象级别（如表和索引分区）的操作，子数据集可具有不同物理存储属性，但属于单个数据库范围；Sharding可跨数据库甚至物理机器。
• MySQL分区：5.1版本提供表分区功能，但局限在单个数据库内，不能跨越服务器。分表时一般采用分片方案，数据存放在多个物理机器。

• 哈希切片

  mod-long：用于分区列为数值的哈希分区，分片列id = 分区列值 mod 分片数。

  mod-long-by-hash：用于分区列为字符串的哈希分区，分片列id = hash(分区列值) mod 分片数。

• 范围切片

  range：建表时创建分区规则，根据规则确定分区列值所在分区，分区列一般为时间或数值。例如：

  date_range: 0: 1000000 1: 2000000 2: 3000000 3: 4000000 4: maxvalue若分区列值为1500000，则数据放到1号分片上。

2. 分库分表中间件

为使使用者感知不到分片表，操作时如同正常表，一般需引入中间件。操作分片表有三种方式：

在应用层直接操作分片逻辑，分片规则需在应用多个节点间同步，每个应用层嵌入操作切片的逻辑实现。例如当当网的Sharding JDBC。

在应用层和数据库层之间添加代理层，将分片路由规则配置在代理层，代理层对外提供与JDBC兼容的接口给应用层，业务实现后在代理层配置路由规则即可。例如Mycat基于此方案实现。

如OceanBase、TiDB框架，将可伸缩特定和分布式事务的实现包装到分布式数据库内部，对使用者透明，无需直接控制这些特性，但对事务的支持不如关系型数据库，适合大数据日志系统、统计系统、查询系统、社交网站等。

支持事务的分布式数据库与MySQL关系不大。客户端分片和代理分片中，多数公司采用代理方式，如MyCAT、Dbatman，客户端分片方式较少接触。两者区别如下：

3. 分布式事务

分片意味着数据分布在多台物理机器上，引入分布式事务问题。单表数据切片后存储在多个数据库甚至实例中，数据库本身的事务机制无法满足需求，需用分布式事务解决。

分布式事务对操作MySQL的影响（以Dbatman为例）：

• 主键唯一性：分片版本不维护自增与主键唯一，业务需自行维护唯一键，不同分片的主键id可能重复。
• 跨分片事务：不支持跨分片事务写，可跨分片事务读。若事务操作内容在一个分片内，不是分布式事务，与单机行为一致；一个事务涉及多个分片为跨节点事务，单分片事务支持。
• SQL限制：update和insert必须带分片列，即使同一分片，也尽量避免使用特殊SQL（如insert not exists），因部分中间件可能不支持。

4. 是否选择分库分表

选择分库分表需考虑以下要素：

• 空间方面：单个物理实例无法支撑数据存储需求，单台物理机无法通过加盘扩容。可考虑删除历史数据、修改存储模型降低磁盘占用、改用空间压缩比更高的存储引擎等替代方案。
• 主库性能：受单个主库的CPU、内存、磁盘IOPS限制，接近或达到上限后需拆分。可通过读写分离降低写库读请求量、优化数据写入模型减少批量写入（削峰）等替代方案提升写入支撑能力。
• 容灾方面：减少单个主库宕机对写入的影响。若业务对读高可用要求高，建议读写分离，将重要请求路由至读库，读库数量多于写库，在代理层面自动容灾切换。但分库分表会扩大故障率，如单台物理机SLA为99.99%，2台为99.98%，10台为99.90%，平均每年故障时间从52分钟增至525分钟，且单个节点故障可能导致代理不可用，放大故障影响范围。

5. 设计实践

• 生成唯一码，码值为整数。
• 码值需批量插入数据库。
• 对码的更新操作均为单条处理，且操作码值需记录。
• 最终数量不定，长远看数据量很大。

• 主键控制：以码值为主键，自行控制主键唯一。
• 分片策略：码表和码表操作记录表均使用range分片，分片范围如0 - 1亿、1亿 - 2亿等。

计算发现，单表可存百亿条数据，若索引设计合理、业务逻辑简单、无高并发请求，单表也可满足需求。

总结

正常情况下，水平分库分表是常见需求，但涉及分布式事务，需考虑能否满足自身需求、想用的SQL语句是否支持，以及是否存在其他方案。关于中间件实现原理，后续可进一步学习。

资料

1. MySQL 分库分表方案，总结的非常好！
2. MySQL之分库分表(MyCAT实现)
3. Mysql分库分表实战（一）——一文搞懂Mysql数据库分库分表
4. MySql分库、分表、分片和分区知识
5. 数据库分片(Sharding)与分区(Partition)的区别（转）
6. 数据库分库分表中间件对比（很全）
7. 分库分表中间件
8. 分库分表：中间件方案对比
9. XA 分布式事务原理