【MySQL】性能调优（五）：架构。集群及分库分表

深藏阁楼爱情的钟 2022-11-01 04:27 142阅读 0赞

## 1.数据库集群 ##

如果单台数据库服务满足不了访问需求，那我们可以做数据库的集群方案。集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时读写多台数据库节点，怎么让所有的节点数据保持一致？

### 1.1 主从架构 ###

这个时候我们需要用到复制技术（replication），被复制的节点称为 master，复制的节点称为 slave。slave 本身也可以作为其他节点的数据来源，这个叫做级联复制。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]

主从复制是怎么实现的呢？更新语句会记录 **binlog**，它是一种逻辑日志。有了这个binlog，从服务器会获取主服务器的 binlog文件，然后解析里面的 SQL语句，在从服务器上面执行一遍，保持主从的数据一致。

这里面涉及到三个线程：

1.  log dump线程：Master节点上有一个log dump线程，是用来发送binlog给slave的。
2.  I/O线程：连接到master获取binlog，并且解析binlog写入relay log（中继日志）。
3.  SQL线程：从库的SQL线程，是用来读取relay log，把数据写入到数据库。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]

### 1.2 读写分离 ###

做了主从复制的方案之后，我们只把数据写入master节点，而读的请求可以分担到slave节点。我们把这种方案叫做读写分离。

> PS：主从复制与读写分离什么关系？ mysql的主从复制和mysql的读写分离两者有紧密的联系。
> 
> 1.  首先要部署主从复制，只有主从复制完成了，才能再此基础上进行数据的读写分离。
> 2.  一般通过主从复制的方式来同步数据后，再通过读写分离来提升数据库的并发负载能力。
> 
> 读写分离就是只在mysql主服务器上写，只在mysql从服务器上读。基本原理是让主数据库处理事务性查询，而从数据库处理select查询。数据库复制被用来把事务性查询导致的变更同步到集群中的数据库。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]

读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问题。如果我们在master写入了，马上到slave查询，而这个时候slave的数据还没有同步过来，怎么办？

**问题**

主从复制到底慢在哪里？在早期的MySQL中，**slave的SQL线程是单线程**。master可以支持SQL语句的并行执行，配置了多少的最大连接数就是最多同时多少个SQL并行执行。而slave的SQL却只能单线程排队执行，在主库并发量很大的情况下，同步数据肯定会出现延迟。

> 为什么从库上的SQL Thread不能并行执行呢？举个例子，主库执行了多条SQL语句，首先用户发表了一条评论，然后修改了内容，最后把这条评论删除了。这三条语句在从库上的执行顺序肯定是不能颠倒的。·
> 
>     insert into user_comments(10000009,'nice'); 
>     update user_comments set content ='verygood' where id=10000009; 
>     delete from user_comments where id=10000009;

怎么解决这个问题，即如何减少主从复制的延迟？

### 1.3 延迟解决方案 ###

首先我们需要知道，在主从复制的过程中，MySQL 默认是异步复制的。也就是说，对于主节点来说，写入binlog，事务结束，就返回给客户端了。对于slave来说，接收到 binlog，就完事儿了，master不关心slave的数据有没有写入成功。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 3]

如果要减少主从复制的延迟，是不是可以等待全部从库的事务执行完毕，才返回给客户端呢？这样的方式叫做全同步复制。从库写完数据，主库才返会给客户端。这种方式虽然可以保证在读之前，数据已经同步成功了，但是带来的副作用大家应该能想到，事务执行的时间会变长，它会导致master节点性能下降。

有没有更好的办法呢？既减少slave写入的延迟，又不会明显增加master返回给客户端的时间？

**1.主从连接方式：半同步复制**

介于异步复制和全同步复制之间，还有一种半同步复制的方式。半同步复制是什么样的呢？

主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到 binlog 并写到 relay log 中才返回给客户端。master 不会等待很长的时间，但是返回给客户端的时候，数据就即将写入成功了，因为它只剩最后一步了：就是读取 relay log，写入从库。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 4]  
如果我们要在数据库里面用半同步复制，必须安装一个插件，这个是谷歌的一位工程师贡献的。这个插件在mysql的插件目录下已经有提供：

cd /usr/lib64/mysql/plugin/

主库和从库是不同的插件，安装之后需要启用：

-- 主库执行 
    INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
    set global rpl_semi_sync_master_enabled=1; 
    show variables like '%semi_sync%';
    
    -- 从库执行 
    INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so'; 
    set global rpl_semi_sync_slave_enabled=1;
    show global variables like '%semi%';

相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，它需要等待一个slave写入中继日志，这里多了一个网络交互的过程，所以，半同步复制最好在低延时的网络中使用。

这个是从主库和从库连接的角度，来保证slave数据的写入。另一个思路，如果要减少主从同步的延迟，减少SQL执行造成的等待的时间，那有没有办法在从库上，让多个SQL语句可以并行执行，而不是排队执行呢？

**2.多库并行复制：异步复制之 GTID 复制**

怎么实现并行复制呢？设想一下，如果3条语句是在三个数据库执行，操作各自的数据库，是不是肯定不会产生并发的问题呢？执行的顺序也没有要求。当然是，所以如果是操作三个数据库，这三个数据库的从库的SQL线程可以并发执行。这是MySQL5.6版本里面支持的多库并行复制。

![在这里插入图片描述][20201103184506718.png_pic_center]  
但是在大部分的情况下，我们都是单库多表的情况，在一个数据库里面怎么实现并行复制呢？或者说，我们知道，数据库本身就是支持多个事务同时操作的；为什么这些事务在主库上面可以并行执行，却不会出现问题呢？

因为他们本身就是互相不干扰的，比如这些事务是操作不同的表，或者操作不同的行，不存在资源的竞争和数据的干扰。那在主库上并行执行的事务，在从库上肯定也是可以并行执行，是不是？比如在master上有三个事务同时分别操作三张表，这三个事务是不是在slave上面也可以并行执行呢？

所以，我们可以把那些在主库上并行执行的事务，分为一个组，并且给他们编号，这一个组的事务在从库上面也可以并行执行。这个编号，我们把它叫做 **GTID**（GlobalTransaction Identifiers），这种主从复制的方式，我们把它叫做基于GTID的复制。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 5]

如果我们要使用GTID复制，我们可以通过修改配置参数打开它，默认是关闭的：

show global variables like 'gtid_mode';

**小结**

无论是优化master和slave的连接方式，还是让从库可以并行执行SQL，都是从数据库的层面去解决主从复制延迟的问题。

除了数据库本身的层面之外，在应用层面，我们也有一些减少主从同步延迟的方法。我们在做了主从复制之后，如果单个 master 节点或者单张表存储的数据过大的时候，比如一张表有上亿的数据，单表的查询性能还是会下降，我们要进一步对单台数据库节点的数据分型拆分，这个就是分库分表。

## 2.分库分表 ##

> 垂直分库，减少并发压力。水平分表，解决存储瓶颈。

1）垂直分库

把一个数据库按照业务拆分成不同的数据库

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 6]

2）水平分库分表

把单张表的数据按照一定的规则分布到多个数据库

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 7]

## 3.实现高可用 ##

通过主从或者分库分表可以减少单个数据库节点的访问压力和存储压力，达到提升数据库性能的目的，但是如果master节点挂了，怎么办？所以，高可用（High Available）也是高性能的基础。

1）主从复制：传统的HAProxy + keepalived的方案，基于主从复制。

2）NDB Cluster：基于NDB集群存储引擎的MySQL Cluster。[参考链接][Link 1]…  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 8]  
3）Galera：一种多主同步复制的集群方案。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 9]  
4）MHA/MMM：MMM（Master-Master replication manager for MySQL），一种多主的高可用架构，是一个日本人开发的，像美团这样的公司早期也有大量使用MMM。MHA（MySQL Master High Available）。

MMM和MHA都是对外提供一个虚拟IP，并且监控主节点和从节点，当主节点发生故障的时候，需要把一个从节点提升为主节点，并且把从节点里面比主节点缺少的数据补上，把VIP指向新的主节点。 [参考链接…][Link 2]

5）MGR：MySQL 5.7.17 版本推出的 InnoDB Cluster，也叫 MySQL Group Replicatioin（MGR），这个套件里面包括了mysql shell 和 mysql-route。[参考链接1][1]，[参考链接2][2]

![,][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 10]

> 总结一下：高可用HA方案需要解决的问题都是当一个master节点宕机的时候，如何提升一个数据最新的slave成为master。如果同时运行多个master，又必须要解决master之间数据复制，以及对于客户端来说连接路由的问题。不同的方案，实施难度不一样，运维管理的成本也不一样。

以上是架构层面的优化，可以用集群，分库分表等方面入手，并且还要注意实现高可用。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221024/b51b8edee765406e9cb98908b6b0bf8f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221024/2c7925d672e24ff58a607c0332b46d2c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221024/7da9cadf2ed349bcb61301b41fac63e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221024/b9f803578d5f4658910eb0dd12de5bb7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221024/a7444d49b42e4921b8d315cd73d680ea.png
[20201103184506718.png_pic_center]: /images/20221024/be48f48bf87a400586cdaa9423554720.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221024/9a238e4325734ab5b6055d69ae13422d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221024/7b5dabd43edf430daf5d81ed8f54cbab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221024/31281b1d4c8b45d4ad65fad26076f4d7.png
[Link 1]: https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-overview.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221024/0d0181565a1d49b796c13ad59f897945.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221024/21c105323fb54f778908568a9a63b0b1.png
[Link 2]: https://tech.meituan.com/2017/06/29/database-availability-architecture.html
[1]: https://dev.mysql.com/doc/refman/5.7/en/group-replication.html
[2]: https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20221024/8b465db52b5d4bc5920aada2ac9ad210.png