通过面试题学MySQL进阶篇

欢迎来我的个人网站，里面有最新的版本

这篇介绍了下MySQL的主从同步及分库分表

常见面试题

数据库如何实现并发（主从、分库）

mysql 主从同步怎么搞的？分哪几个过程？如果有一台新机器要加到从机里，怎么个过程。

binlog 日志是 master 推的还是 salve 来拉的？

一、主从复制（同步/分离）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5RSDLl1X-1589031550221)(https://cdn.jsdelivr.net/gh/hhf443/blog-graph/img/20200509155433.png)]

1.1 主从复制原理

复制功能并不是copy文件来实现的，而是借助binlog日志文件里面的SQL命令实现的主从复制，可以理解为我再Master端执行了一条SQL命令，那么在Salve端同样会执行一遍，从而达到主从复制的效果；

主库会做哪些事？

1)、在master机器上的操作：
当master上的数据发生变化时，该事件变化会按照顺序写入bin-log中；

当slave链接到master的时候，master机器会为slave开启binlog dump线程；

当master的binlog发生变化的时候，bin-log dump线程会通知slave，并将相应的binlog内容发送给slave。

主库会做哪些事？

2)、在slave机器上操作：

当主从同步开启的时候，slave上会创建两个线程：I/O线程和SQL线程。

I/O线程。该线程连接到master机器，master机器上的binlog dump 线程会将binlog的内容发送给该I\O线程，该I/O线程接收到binlog内容后，再将内容写入到本地的relay log；

Sql线程。该线程读取到I/O线程写入的ralay log。并且根据relay log 的内容对slave数据库做相应的操作。

1.2 主从复制流程

分为同步复制和异步复制，实际复制架构中大部分为异步复制。 复制的基本过程如下：

在Master机器上，主从同步事件会被写到特殊的log文件中(binary-log);

主从同步事件有3种形式:statement、row、mixed。

1. statement：会将对数据库操作的sql语句写入到binlog中。
2. row：会将每一条数据的变化写入到binlog中。
3. mixed：statement与row的混合。Mysql决定什么时候写statement格式的，什么时候写row格式的binlog。

1）当master上的数据发生改变的时候，该事件(insert、update、delete)变化会按照顺序写入到binlog中。

2）当slave连接到master的时候，master机器会为slave开启binlog dump线程。当master 的 binlog发生变化的时候，binlog dump线程会通知slave。

在Slave机器上

1. Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；

2. Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave 的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；

3. Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”；

4. Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

1.3 主从复制优点

主从复制可以实现数据备份、故障转移、MySQL集群、高可用、读写分离

1. 实现服务器负载均衡(读写分离)

2. 通过复制实现数据的异地备份(数据备份)

3. 提高数据库系统的可用性(故障转移)

1.4 数据不一致问题

主从同步延迟 (读写分离)导致主库从库数据不一致问题的及解决方案

具体请参考 https://www.dgstack.cn/archives/887.html

1.忽略错误后，继续同步
该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况

2.强制读主

使用一个高可用主库提供数据库服务，读和写都落在主库上；采用缓存来提升系统性能

3.选择性读主

在缓存中记录哪些数据发生过写请求，来路由读主还是读从

二、分库分表

当DB的数据量级到达一个阶段，写入和读取的速度会出现瓶颈，即使是有索引，索引也会变得很大，而且数据库的物理文件会使备份和恢复等操作变的很困难。这个时候由于DB的瓶颈已经严重危害到了业务，最有效的解决方案莫过于DB的分库分表了。

数据库分布式核心内容无非就是数据切分（Sharding），以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题，从而达到提升数据库操作性能的目的。

数据切分根据其切分类型，可以分为两种方式：垂直（纵向）切分和水平（横向）切分

垂直（纵向）切分

垂直切分常见有垂直分库和垂直分表两种。

垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。

1. 概念：以 表为依据，按照业务归属不同，将不同的 表拆分到不同的 库中 。
2. 结果：
   • 每个 库的 结构都不一样；
   • 每个 库的 数据也不一样，没有交集；
   • 所有 库的 并集是全量数据；
3. 场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
4. 分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

垂直分表是基于数据库中的"列"进行，将不经常用或字段长度较大的字段拆分出去到扩展表中。

在字段很多的情况下（例 如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导 致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了 磁盘IO，从而提升了数据库性能。

1. 概念：以 字段为依据，按照字段的活跃性，将 表中字段拆到不同的 表（主表和扩展表）中。
2. 结果：
   • 每个 表的 结构都不一样；
   • 每个 表的 数据也不一样，一般来说，每个表的 字段至少有一列交集，一般是主键，用于关联数据；
   • 所有 表的 并集是全量数据；
3. 场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
4. 分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

水平（横向）切分

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。

水平分库

1. 概念：以 字段为依据 ，按照一定策略（hash、range等），将一个 库中的数据拆分到多个 库中。
2. 结果：
   • 每个 库的 结构都一样；
   • 每个 库的 数据都不一样，没有交集；
   • 所有 库的 并集是全量数据；
3. 场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
4. 分析：库多了，io和cpu的压力自然可以成倍缓解。

水平分表

1. 概念：以 字段为依据 ，按照一定策略（hash、range等），将一个 表中的数据拆分到多个 表中。
2. 结果：
   • 每个 表的 结构都一样；
   • 每个 表的 数据都不一样，没有交集；
   • 所有 表的 并集是全量数据；
3. 场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
4. 分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。