MySQL 分库分表

以MySQL为例讲讲分库分表。

1. 什么是分库分表

分库分表，可以拆分为两个词：分库和分表。

分库：将一个数据库拆分成多个独立的数据库。这些独立的数据库可以部署在多个服务器上。
分表：将一个表拆分成多个小表。这些小表可以存储在同一个数据库中，也可以分散到不同的数据库中。

理解了分库和分表，那么怎么分呢？按照拆分方法，可以分为垂直拆分和水平拆分。因此，可以总结出以下四种：

1.1 垂直分库

垂直分库，就是将一个数据库中的表，按照关系拆分到多个数据库，和微服务的架构相同：

1.2 垂直分表

垂直分表指将存在一张表中的字段切分到多张表，最终：

每个表的结构不一样；
每个表的数据不一样；
所有表的字段并集是原表的字段；
表之间的关系为1:1；

例如，用户完整表可以拆分出基本信息和详细信息两张表，通过user_id进行关联：

1.3 水平分库

水平分库是指一个数据库存放不了所有用户的信息，那么可以将用户信息放在多个数据库中：

1.4 水平分表

水平分表与水平分库的概念类似，只不过是在一个数据库中，将一个表拆分为多个小表，这些小表结构相同，但是数据不同：

2. 为什么要分库分表

假设由于业务的发展，数据量变得非常大，达到千万级甚至亿级，如果我们将所有数据放在单库单表，将会出现很多问题：

读写性能会变得非常低。对于读操作，如果没有索引结构的支持，那么只能走全表扫描，效率可想而知；即使有索引，由于数据量过大，会导致B+树索引层级过多，IO操作增加。对于写操作，需要维护索引，也会影响效率；
单台服务器的CPU、内存、磁盘I/O等资源有限，单库单表会对服务器资源造成压力；
对于高并发应用，单库只能提供有限的连接数，会造成客户端等待；

所以，为了解决以上问题，在迫不得己的情况下需要进行分库分表。也就是说，只有在必须做分库分表的时候才做（读写分离方案也可以考虑），并且，分库和分表可以分开做：

只分库不分表：微服务化；
不分库只分表；
既分库又分表；

分库和分表解决的问题也不一样，或者说侧重点不一样。

分库解决高并发：单库只能提供有限的数据库连接，分库可以增加数据库连接；
分表解决大数据量的问题：将数据存放在多个表中，可以减小单表压力；

3. 分库分表如何落地

假设现在选择了分库分表的方案，并且是针对订单数据进行分库分表，那么就有两个问题：

如何分库？也就是说，新增的订单应该存放在哪个数据库；
如何分表？也就是说，新增的订单应该存放在哪个数据表；

3.1 流程分析

首先，应该评估要分多少库，要分多少表。

我们以单表存储1000万的数据量为标准。假设每日订单量平均为100万，那么1年就是365x100万=365000000，即3.65亿条数据，我们的应用程序存活时间为10年，那么总共是36.5亿条数据。

最后，总共需要365张表，那么有多种拆分方法，例如，1个库每个库365张表，5个库每个库73张表，10个库每个库37张表。

整体来看，10个库每个库37张表的组合，数据比较分散，所以采用这种方式，并且在计算机中，一般采用 $2^{n}$ 来计数，所以最终采用16个库每个库32张表的组合（总共512张表，比我们之前评估的多，但是可以接受，以容纳高峰期爆单、业务增长的情况）。

然后，我们要解决新订单应该存放在哪个库哪个表的问题。

只要我们设定规则决定新订单放在哪个库哪个表就行，但是在实际情况下，需要结合业务考虑，合理选择规则。

例如，我们可以通过规则 订单ID % 16 计算出新订单存放在哪个数据库；假设订单需要配送，为了保证履约的时效，用户在下单时，会选择收货地址，那么可以根据收货地址把订单下到最近的仓库，因此，可以根据收货地址计算出存放在哪个数据库。

那如何将订单存放在哪张表呢，同样的，我们可以通过规则 订单ID % 32计算出新订单存放在哪个表，但是，这种方案真的好吗？假设现在某用户要查自己的订单列表，查询条件就是 用户ID，那要怎么查呢，如果采用订单ID作为分表依据，则只能查询所有表，然后将结果汇总起来，显然可见，效率非常低下。

所以，如果我们业务的最常用场景，是用户查询自己的订单，那么根据规则 用户ID % 32是比较好的，这样会把该用户的所有订单放在同一张表，并且，库的路由规则也需要改变，需要通过用户ID进行路由。

那又有问题，对于客服或运营人员，需要通过订单ID查询订单，如果使用用户ID作为库和表的路由规则，那要查询某个订单时，需要查询所有表，这显然是不可接受的，所以其中一种解决方法是生成订单ID时，在订单ID中加入部分用户ID字段，例如，如下的订单ID规则：

txt

时间戳 + 随机数 + 用户ID后四位

这样，我们可以使用用户ID后四位作为分库分表的路由规则，然后，使用订单ID查询时，也可以从订单中提取用户ID找到对应的库表，这称为基因法。

还有一种场景，如果某个商家要查询自己的订单，那么查询条件是商家ID，这种情况该如何解决呢？解决方案之一是数据冗余。即使用Cannal等数据同步方案，另外搭建一套以商家ID为分库分表规则的商家只读数据库，当商家需要查询订单时，去商家数据库查询。

图中的PUMA是美团内部开发的MySQL binlog实时解析服务，可以理解为Cannal.
图源：https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html

对于非Sharding Key的查询，我们也可以同步发送到ES（ElasticSearch），然后通过ES来查询。

3.2 切分策略

切分策略就是确定了Sharding Key之后，如何把新数据放到哪个表中。

3.2.1 range划分

例如，按照订单的创建时间进行分表，同一个月的订单放在一张表。

优点： range范围分表，有利于扩容。
缺点：可能会有热点问题。平时用户一般都查最近一个月的订单比较多，请求都打到同一张表了。

3.2.2 hash

一般是将选择的 Sharding key进行哈希计算，然后再对分表总数取余。

txt

hash(key) % N

N 为分表总数。

优点：hash取模的方式，不会存在明显的热点问题。
缺点：如果未来某个时候，表数据量又到瓶颈了，需要扩容，就比较麻烦。所以一般建议提前规划好，一次性分够。（可以考虑一致性哈希）

3.2.3 一致性hash

如果用hash方式分表，前期规划不好，需要扩容二次分表，表的数量需要增加，所以hash值需要重新计算，这时候需要迁移数据了。

例如，最开始分了32张表，由于业务的快速增长，需要扩容，分为64张表，那么需要对数据进行重新定位：

txt

hash(key) % 32
		｜
		｜
		\/
hash(key) % 64

结果会导致大量数据进行迁移。

为了二次扩容时减少数据量的迁移，我们可以采用一致性哈希算法。

一致性哈希的核心思想是构建一个虚拟的哈希环（Hash Ring）。按照常用的hash算法，将对应的key哈希到一个具有 $2^{32}$ 个空间的首尾相接的环中。

首先，将所有服务器节点（如服务器的IP地址或名称）通过一个哈希函数映射到哈希环上的某个位置。如下面的节点A、节点B和节点C；
同样地，将数据（如数据的键值）也通过相同的哈希函数映射到哈希环上的某个位置。如下面的key-1、key-2和key-3；
对于任何一个数据，它会被存储在哈希环上顺时针方向遇到的第一个服务器节点上。

节点增减的影响：

增加节点： 当一个新的服务器节点加入时，它会被映射到哈希环上的某个位置。此时，只有在这个新节点逆时针方向到下一个已有节点之间的数据会从原节点迁移到新节点上，其他数据不受影响。
删除节点： 当一个服务器节点离开时，它所负责的数据会顺时针地迁移到哈希环上的下一个可用节点上。同样，只有受影响节点的数据会迁移，其他数据不受影响。

通过一致性哈希算法，会减少需要迁移的数据量。

但是，一致性哈希会有节点分布不均匀的问题，出现数据倾斜，这样会导致大量数据存放在某一台服务器上：

为了解决数据倾斜问题，可以采用虚拟节点方案：

每个物理服务器节点不再只映射到哈希环上的一个点，而是映射到多个哈希点，这些点被称为虚拟节点。
当一个物理节点上线时，它会生成多个虚拟节点并映射到哈希环上。
当一个物理节点下线时，它对应的所有虚拟节点都会从哈希环上移除。
对于任何一个数据，同样找在顺时针方向第一个碰到的节点，此时该节点是虚拟节点，需要通过映射关系找到实际物理节点；

例如，原来的节点A、B和C分别设置三个虚拟节点：

也就是说，节点越多，越不容易出现数据倾斜问题，而没有这么多物理节点，那就弄虚拟节点。

4. MySQL中的分表

在MySQL中，支持多种分表策略，这里以hash分表为例：

sql

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

以store_id作为分表键，使用hash策略，分为四个表。

当执行上面的语句后，会在文件系统中产生如下四个文件，就对应着四个分区表：

插入如下测试数据：

sql

INSERT INTO employees VALUES
(1, 'zhang', 'san', '2020-01-01','2020-12-01', 1, 1),
(2, 'li', 'san', '2020-01-01','2020-12-01', 1, 2),
(3, 'wang', 'san', '2020-01-01','2020-12-01', 1, 3),
(4, 'zhao', 'san', '2020-01-01','2020-12-01', 1, 4),
(5, 'mary', 'san', '2020-01-01','2020-12-01', 1, 5),
(6, 'long', 'san', '2020-01-01','2020-12-01', 1, 6);

然后执行查询操作：

sql

SELECT * FROM employees PARTITION(p1);

通过PARTITION(p1)表示只查询分区表p1的数据：

5. 分库分库有哪些问题

当实行了分库分表后，会带来以下问题。

5.1 分布式ID

在单库单表中，通常使用数据库自增序列作为ID，但是，在分库分表环境下，数据库自增序列通常不唯一，所以需要引入分布式ID。分布式ID需要满足以下条件：

全局唯一：必须保证ID是全局性唯一的，基本要求；
高性能：高可用低延时，ID生成响应要块，否则反倒会成为业务瓶颈；
高可用：100%的可用性是骗人的，但是也要无限接近于100%的可用性；
好接入：要秉着拿来即用的设计原则，在系统设计和实现上要尽可能的简单；
趋势递增：最好趋势递增，这个要求就得看具体业务场景了，一般不严格要求；

常见的方案有：UUID、数据库号段模式、使用Redis获取分布式ID、雪花算法。

UUID

优点：UUID 生成方便，本地生成没有网络消耗

缺点：

不利于存储：UUID太长，16字节128位；
信息安全：基于MAC地址生成UUID的算法可能会造成MAC地址泄露；
对MySQL索引不利：如果作为数据库主键，UUID的无序性可能会引起数据位置频繁变动（索引），严重影响性能；

数据库号段模式

创建一个专用于获取分布式ID的数据库，每次从该数据库中获取一批数据，这样可以避免频繁访问数据库造成的性能问题。

首先创建如下表：

sql

CREATE TABLE `sequence_id_generator` (
  `biz_type` int(20) NOT NULL COMMENT '业务类型',
  `current_id` bigint(20) NOT NULL COMMENT '当前最大id',
  `step` int(10) NOT NULL COMMENT '号段的长度',
  `version` int(20) NOT NULL COMMENT '版本号',
   PRIMARY KEY (`biz_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建好后，根据业务类型，先往表中插入一些初始数据：

sql

INSERT INTO `sequence_id_generator` (`biz_type`, `current_id`, `step`, `version`) VALUES
('user_id', 0, 1000, 0),
('order_id', 0, 1000, 0);

之后，在我们的程序启动时，执行一条查询语句和一条更新语句，获取号段：

sql

SELECT * FROM `sequence_id_generator` WHERE `biz_type` = 'user_id';

-- 第一步查询后，获得 version \ step

UPDATE `sequence_id_generator`
SET current_id = current_id + {step}, version = version + 1
WHERE biz_tag = 'user_id' AND version = {version};

采用乐观锁，如果 UPDATE 语句影响的行数为 1，说明更新成功，可以安全地获取到第一次查询时范围为 $[c u r r e n t_{i} d, c u r r e n t_{i} d + s t e p)$ 的ID。之后，就可以从内存中获取缓存的ID了。

如果影响行数为 0，说明在更新过程中其他线程或服务已经先一步更新了 version（并发冲突），需要重试获取。

数据库号段模式的优点：

高并发： 大部分ID生成操作都在内存中完成，无需频繁访问数据库，能支持极高的并发量。
ID趋势递增： 生成的ID是趋势递增的，有利于数据库索引的优化。
实现简单： 相对于雪花算法等其他方案，实现逻辑相对简单。
DBA友好： ID是纯数字，占用空间小，且易于存储和索引。

缺点：

数据库单点故障： 数据库是核心瓶颈，如果数据库挂掉，ID服务将不可用。可以通过主从复制、数据库集群等方式提高可用性。
号段用尽阻塞： 如果预取机制设计不当，或者数据库压力过大，在号段用尽时可能会出现短暂的阻塞。
ID不够随机： ID是趋势递增的，不是完全随机的，可能导致一些安全性的考虑（例如，竞争对手可以轻易猜测下一个用户ID）。

使用Redis获取分布式ID

Redis 获取分布式 ID 的核心原理是利用其提供的原子自增命令，如 INCR、INCRBY 或 INCRBYFLOAT。通过这些命令，可以保证在并发环境下，每次获取到的 ID 都是唯一且递增的。

雪花算法

Snowflake，雪花算法是由Twitter开源的分布式ID生成算法，以划分命名空间的方式将 64-bit位分割成多个部分，每个部分代表不同的含义。而 Java中64bit的整数是Long类型，所以在 Java 中 SnowFlake 算法生成的 ID 就是 long 来存储的。

第1位占用1bit，其值始终是0，可看做是符号位不使用。
第2位开始的41位是时间戳，41-bit位可表示2^41个数，每个数代表毫秒，那么雪花算法可用的时间年限是(1L<<41)/(1000*3600*24*365)=69 年的时间；
中间的10-bit位可表示机器数，即2^10 = 1024台机器；
最后12-bit位是自增序列，可表示2^12 = 4096个数；

这样的划分之后相当于在一毫秒内一台机器上可产生4096个有序的不重复的ID。

雪花算法的优缺点如下：

优点：生成速度比较快、生成的 ID 有序递增、比较灵活（可以对 Snowflake 算法进行简单的改造比如加入业务 ID）；

缺点：需要解决重复 ID 问题（ID 生成依赖时间，在获取时间的时候，可能会出现时间回拨的问题，也就是服务器上的时间突然倒退到之前的时间，进而导致会产生重复 ID）；

有很多基于 Snowflake 算法的开源实现比如美团的 Leaf、百度的 UidGenerator。

Seata关于雪花算法的改进：https://seata.apache.org/zh-cn/blog/seata-snowflake-explain

5.2 联表查询

在分库分表后，联表查询将变得不可用，解决方案

应用层解决：在应用层多次查询数据库，然后将查询到的数据在内存中进行整合。例如，查询用户订单列表，包含用户信息。

首先根据用户ID查询 order 表（可能分库分表）。
从 order 结果中提取出所有相关的 user_id。
根据这些 user_id，批量查询 user 表（可能分库分表）。
在应用层将订单和用户数据进行关联合并。

数据冗余：将关联查询中经常需要用到的字段，冗余存储在主表中。这是一种空间换时间的策略。例如，查询订单时需要显示用户名称：

将 user_name 字段冗余到 order 表中。
查询订单时直接从 order 表获取用户名称，无需联表。

数据异构 / 引入大数据或搜索引擎：对于复杂的查询、统计分析、报表生成、非分片键查询等场景，联表查询问题会变得非常棘手。此时，可以考虑将数据异构到其他存储系统。

同步到 Elasticsearch (ES)：将分库分表的数据通过 ETL（Extract, Transform, Load）工具或消息队列（如 Kafka）实时同步到 Elasticsearch。ES 强大的全文检索和聚合能力可以很好地解决复杂查询和联表查询问题。
同步到 HBase/MongoDB：对于一些非关系型或需要宽表存储的场景，可以将数据同步到 HBase 或 MongoDB 进行查询。
同步到数据仓库：对于离线分析和复杂报表，可以将数据同步到数仓（如 Hive、ClickHouse）进行批处理。

5.3 事务问题

分库分表后，假设两个表在不同的数据库，那么本地事务已经无效，需要使用分布式事务了。

常用的分布式事务解决方案有：

两阶段提交
三阶段提交
TCC
本地消息表
最大努力通知
saga

参考资料

[1] https://www.bilibili.com/video/BV1HF411C7xZ

[2] https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html

[3] https://zhuanlan.zhihu.com/p/605884141

[4] https://learn.lianglianglee.com/专栏/ShardingSphere 核心原理精讲-完/01 从理论到实践：如何让分库分表真正落地？.md

[5] https://dev.mysql.com/doc/refman/8.4/en/partitioning.html

MySQL 分库分表 ​

1. 什么是分库分表 ​

1.1 垂直分库 ​

1.2 垂直分表 ​

1.3 水平分库 ​

1.4 水平分表 ​

2. 为什么要分库分表 ​

3. 分库分表如何落地 ​

3.1 流程分析 ​

3.2 切分策略 ​

3.2.1 range划分 ​

3.2.2 hash ​

3.2.3 一致性hash ​

4. MySQL中的分表 ​

5. 分库分库有哪些问题 ​

5.1 分布式ID ​

5.2 联表查询 ​

5.3 事务问题 ​

参考资料 ​