MySQL 系列的第七篇，主要内容是分库分表与主从同步。

1. 分库分表

随着公司业务的快速发展，数据量也随之快速增加，你是否会经常接到来自运营、测试、产品对系统查询响应缓慢的抱怨？是的，我经常会听到。

不管是由于查询逻辑复杂还是因为数据量的硬伤，当优化索引后也无法再缩短查询时间时，这意味着数据库的性能达到了一个瓶颈，你可以尝试下面几种优化方案：

• 分区表
• 读写分离
• NoSQL 数据库
• 提升硬件性能（CPU、内存等）

如果这些还没用，那么或许你该考虑通过分库分表来优化查询效率了。

1.1 分库分表为什么能提高查询效率

当索引、分区表、缓存、提升硬件之后查询还是很慢时，意味着查询语句的命中行数，也就是说数据量实在是太大了，这才导致单个数据库实例的性能达到瓶颈。

所以通过分库分表，将本来让单个数据库实例来承受的压力分摊给多个数据库实例，这样每个数据库实例就承受了小很多的压力，从而达到提升性能的效果。

分表也是一样的道理，将单张表的数据分解为多张表，解决了由于列数过多导致的性能问题。

这就像是分治算法，将一个大问题，分解为若干个相同的小问题。

1.2 分库分表的方式

说完了分库分表提高性能的原理，再来说说实现分库分表的两种方式：水平切分（Horizental Sharding）和垂直切分（Vertical Sharding），下面分别进行讨论。

假设我们有一张用户表，它的数据量已经达到5000W行或者100G以上：

-- 用户表
CREATE TABLE `user`  (
  `user_id` int(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) NULL DEFAULT NULL,
  `age` int(12) NULL DEFAULT NULL,
  PRIMARY KEY (`record_id`) USING BTREE,
) ENGINE = InnoDB AUTO_INCREMENT = 1;

-- 演出记录表
CREATE TABLE `show_record`  (
  `show_id` int(12) NOT NULL AUTO_INCREMENT,
  `user_id` int(12) NULL DEFAULT NULL,
  `show_name` varchar(36) NULL DEFAULT NULL,
  `show_date` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`show_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;

insert into user values (5,'重塑',17),(10,'达达',24),(15,'刺猬',15),(20,'五条人',12);

现在要对这张表进行分库分表。

1.2.1 垂直切分

垂直切分是指将数据按照业务进行拆分（比如按照热点列与非热点列进行垂直切分），如果拆分后的数据不在同一个数据库实例中，就是垂直分库，反之就是垂直分表。

1.2.1.1 垂直分库

垂直分库的核心思想是专库专用，比如将用户信息放在 DB1，作为用户库；将演出记录信息放在 DB2，作为记录库。

垂直分库带来的好处是：

• 通过专库专用的方式，优化了由于单库数据量过大而产生的性能问题（IO、连接数、内存、磁盘硬件资源等）
• 将不同的业务进行故障隔离

1.2.1.2 垂直分表

垂直分表就是将一张表按照字段拆分成多表，每个表存储其中一部分字段。比如将 show_record 表根据 show_id 拆分成 show_detail 和 record 两张表。

垂直分表带来的好处是：

• 可以减少IO冲突、锁表、死锁几率
• 将不同的业务进行故障隔离

1.2.2 水平切分

水平切分是指将同一张表中的数据按照一定的规则拆分到多张表中进行存储，如果这多张表不在同一个数据库实例中，就是水平分库，反之就是水平分表。

1.2.2.1 水平分库

可以根据用户的ID进行水平分库，ID为奇数的用户放在 user1 表，ID为偶数的用户放在 user2 表，同时库中其他表与ID关联的数据（比如 show_record 乐队演出记录表）也随之拆分，放在不同的库中。

这相当于是进行搭建私有云（本地化）的操作：既然所有用户的数据实在太大，那么以用户为单位，一个用户的数据放在一个数据库中，那数据量肯定减少许多（如果服务器够多的话）。

水平分库带来的好处是：

• 优化了由于单库数据量过大而产生的性能问题（IO、连接数、内存、磁盘硬件资源等）
• 提高系统可用性，单数据库实例宕机并不会影响所有业务
• 可以减少IO冲突、锁表、死锁几率

1.2.2.2 水平分表

同样可以根据用户的ID进行水平分表，ID为奇数的用户放在 user1 表，ID为偶数的用户放在 user2 表，同时库中其他表与ID关联的数据（比如 show_record 乐队演出记录表）也随之拆分，放在不同的表中。

水平分表带来的好处是：

• 优化了由于单表数据量过大而产生的性能问题
• 可以减少IO冲突、锁表、死锁几率

1.2.3 切分策略

垂直切分更注重业务性，而在水平切分中，我们可以根据下面的几种方式进行切分：

• 哈希：将ID进行哈希，这样的好处是使得ID充分散列，数据分布更均匀
• ID取模：跟哈希是相似的原理
• 范围：ID范围或时间范围
• 映射表：映射表的方式就是自定义切分策略，更具主观性，但由于需要通过映射表找到切分后的数据所在区域，所以会影响查询效率

1.3 分库分表后带来的问题

前文说过，要是在优化索引、分区表、读写分离、NoSQL 数据库之后还是没能提高查询效率，那么才考虑通过分库分表来优化，这是因为分库分表事实上也会带来许多比查询性能更严重的问题。

1.3.1 分布式事务

在进行分库之后，数据就存在于两个数据库实例中，在进行业务写操作时就需要通过分布式事务来保证跨库事务的完整性。

分布式事务解决方案：

• 全局事务（如两阶段提交 Two-Phase Commit, 2PC）
• 补偿事务（Try-Confirm-Cancel, TCC）
• 本地消息表
• MQ事务消息
• 开源框架（如 Seata）

1.3.2 跨库查询

第二个问题是关于查询，在进行分库分表之后，原先业务中的一些查询语句可能需要重写，比如：跨库关联查询、跨库分页查询。

先来说说跨库关联查询，这应该是需要尽量避免的，一般禁止进行跨库关联查询。如果实在避免不了，常见的做法是通过冗余表或者冗余字段来实现跨库关联查询，但这也会带来新的问题：若原始数据被修改了，冗余字段是否需要同步修改？

还有一种方法是将多个单表查询在返回给用户前通过代码进行手动关联。

再来谈谈跨库分页查询，这被称为“业界难题”，58的沈剑大佬曾写过一篇文章对这个问题进行讨论：业界难题-“跨库分页”的四种方案，建议详读。

除了上面分享分文章中所提及的几种方法，还可以通过中间件来实现跨库分页查询。

1.3.3 全局主键的唯一性

通常我们建议使用自增ID来作为主键，但进行分库分表之后，这种做法会造成主键重复问题，所以我们需要使用全局唯一的主键来避免这个问题。

常用的方法有：

• UUID：最简便的唯一性ID生成方案，但是UUID较占空间，且不连续易导致页分裂，同时还会影响查询性能
• 基于分布式锁实现自增ID：缺点是分布式锁会影响性能
• 分布式自增ID生成器：雪花算法（Snowflake）

2. 主从同步

主从同步部分主要围绕 MySQL 实现主从同步的原理来讲述，还会涉及到 bin log 的三种日志模式等内容。

2.1 主从同步实现原理

在本系列第一篇 基础架构 一文中曾提到 MySQL 是通过服务层的归档日志 bin log 来实现主从同步的，下面就来聊聊它的实现原理。

我们已经知道在 statement 级别下 bin log 中存储的是 SQL 本身，下图就展示了主库与从库进行同步的主要节点与流程。

• 当主库中有数据更新时，主库会按照配置的 binlog_format 的值来将日志写入 bin_log 中，之后主库会通过 Log Dump Thread 线程来通知从库有数据需要同步。
• 从库中的 I/O Thread 线程就是负责与主库建立连接的，它接收到主库的新数据通知后，将向 Log Dump Thread 线程发起读取日志数据的请求，然后将读取到的日志数据写入从库的中转日志 Relay log 中。
• 最后，从库中的 SQL Thread 线程检测到中转日志 Relay log 有更新后，会将这些发生在主库的日志数据解析为 SQL 语句全部执行一遍，以此来完成主从同步。

2.2 binlog_format 三种模式

在 MySQL5.7.7 版本以后，binlog_format 的默认值已经由 statement 改为 row 了，这样修改的原因也就是接下来要讨论的 binlog_format 三种模式下，bin log 都存储了些什么内容。

binlog_format 的三种模式分别是：

• statement : 客户端提交的原始 SQL
• row : 经过优化后，精确到行的 SQL（不会导致主从不一致，但日志空间也更大）
• mixd : 混合模式，易引起主从不一致的语句使用 row 模式，其他语句使用 statement 模式

2.3 为什么 statement 模式易导致主从不一致

我们已经知道 binlog_format 为 statement 时下，bin log 中记录的是客户端提交的原始 SQL。

我们做一个实验来说明这个问题，首先目前 user 表中现有的数据是：

mysql> select * from user;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    5 | 重塑   |   17 |
|   10 | 达达   |   24 |
|   15 | 刺猬   |   15 |
+------+--------+------+
3 rows in set (0.00 sec)

然后假设主库提交了下面这条删除语句：

delete from user where id > 1 and age>10 limit 1;

这条删除语句的意思是删除一条主键ID大于1并且age>10的数据，但是单从这条 SQL 来看我并不能确定哪一条数据被删除了，因为我不知道查询条件走的是哪个索引，再来看下它的执行计划：

mysql> explain delete from user where id > 1 and age>10 limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
|  1 | DELETE      | user  | NULL       | range | age           | age  | 5       | const |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

从 key 列可以看到，这条语句使用的是 age 索引，所以在执行删除操作时，删除的必定是 age 索引树上第一个age>10 且 id>1 的数据，也就是(20, ‘五条人’, 12)这一行。

我们知道 MySQL 的优化器选择索引时会考虑很多因素，比如扫描行数、是否排序等。

单凭 delete from user where id > 1 and age>10 limit 1 这样一句 SQL 并不能确定最终执行时到底使用的是主键索引还是 age 字段索引，也就无法确定这条语句删除的到底是哪一行了。

所以在进行主从同步时，如果 binlog_format 为 statement ，很可能会导致下面的情况发生：

• 主库使用 age 索引，删除的是(20, ‘五条人’, 12)这一行
• 从库使用主键索引，删除的是(5, ‘重塑’, 17)这一行

即导致主从不一致。

3. 温故知新

• 分库分表的方式？
• 垂直切分和水平切分有什么不同？
• 分库分表之后可能会遇到哪些问题？
• 如何保证全局主键的唯一性？
• 如何实现分布式事务？
• 雪花算法的原理
• 主从同步的原理？
• binlog_format 有几种模式？有什么不同？
• 为什么 statement 模式易导致主从不一致？

4. 参考资料

• [《Java性能调优实战》 37

  什么时候需要分表分库？](https://time.geekbang.org/column/article/779be75a80192717055de83d42337c65/share?code=VCKLPjeXdS0XDO%2FWFcwkbB1I3fpf0EgS-zEXpNP0u8k%3D)

• [《MySQL实战45讲》24

  MySQL是怎么保证主备一致的？](https://time.geekbang.org/column/article/9cad2b90b4bd32cd46ea8119005623a6/share?code=cbHwlgRYeERonSROIcKJXe0PPC4kk7tccvKC0sfh3Rc%3D)

• 彻底搞清分库分表（垂直分库，垂直分表，水平分库，水平分表）
• 聊聊 MySQL 主从同步那点事儿

最后，本文收录于个人语雀知识库: 我所理解的后端技术，欢迎来访。