MySQL必须掌握的原理

整理多数据中心容灾备份的方案的时候，必然绕不过应用的持久化的备份方案。MySQL经历了这么多年的发展，对应也衍生除了很多高可用的部署架构，能够适用于两地三中心或者同城多活的场景。但是，这里我准备系统性的粗略理一遍MySQL的原理，日后再慢慢填充各部分的内容。

主流的MySQL版本为5.7和当前的8.0，存储引擎都推荐使用innodb，下文就先来讲讲innodb的存储。

Innodb原理

由外到内来分析，我们先从innodb引擎的文件类型，再到具体文件内部数据存储结构。

文件类型

其实，无论使用什么样的存储引擎，最终都是以文件的形势保存到磁盘上的，这点和elasticsearch很像。我们在mysql中创建一个数据库之后，对应在其data目录下会创建一个与该database同名的目录。比如，这里我创建了一个名叫haha的db，然后在里面添加了一张表Person，查看目录结构大致是这样的：

[root@aliyun-vm mysql]# tree haha
haha
├── db.opt
├── Persons.frm
└── Persons.ibd

0 directories, 3 files

接下来，我们来看看其中的两种扩展名文件:

• .frm

无论选择了哪种存储引擎，所有的MySQL表都会在硬盘上创建一个.frm文件用来描述表的格式（定义）。.frm文件的格式在不同的平台上都是相同的。

• .ibd 文件
  当打开innodb_file_per_table选项时，.ibd文件就是每一个表独有的表空间，文件存储了当前表的数据和相关的索引数据。如果不指定每个表单独一个innodb文件，会统一将数据存到其data目录下的ibdata1文件中。

存储结构

如上图所示，Persons.ibd这个文件所存储的内容主要就是B+树（索引），一个表可以有多个索引。一个.ibd文件可以存储多个索引，ibd文件存储的就是一个表的所有索引数据。索引文件有段（segment）,簇（extends）,页面（page）组成。

• 段 (segment): 就是.ibd文件的各部分，包括数据段、索引段、回滚段等。
• 簇 (extends): 簇是由64个连续的页组成的，每个页大小为16KB，即每个簇的大小为1MB。簇是构成段的基本元素，一个段由若干个簇构成。
• 页面 (page): 页是InnoDB磁盘管理的最小单位。

一个页面的存储由图中的几部分组成，下面重点讲解关键的部分：

1. 页头（Page Header）：记录页面的控制信息，共占150字节，包括页的左右兄弟页面指针、页面空间使用情况等；
2. 最小虚记录、最大虚记录：两个固定位置存储的虚记录，本身并不存储数据。最小虚记录比任何记录都小，而最大虚记录比任何记录都大。
3. 记录堆（record heap）：表示页面已分配的记录空间，也是索引数据的真正存储区域。记录堆分为：有效记录和已删除记录。有效记录就是索引正常使用的记录，而已删除记录表示索引已经删除，不在使用的记录，会组织成为自由空间链表。
4. 未分配空间：指页面未使用的存储空间，随着页面不断使用，未分配空间将会越来越小。当新插入一条记录时，首先尝试从自由空间链表中获得合适的存储位置（空间足够），如果没有满足的，就会在未分配空间中申请。
5. 页尾（Page Tailer）：页面最后部分，占8个字节，主要存储页面的校验信息。

页面中的页头，最大/最小虚记录以及页尾都是页面中有固定的存储位置。

当插入一条记录，会从Free Space中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。

一张表中可以有成千上万条记录，一个页只有16KB，所以可能需要好多页来存放数据。不同页其实构成了一条双向链表，File Header是InnoDB页的第一部分，它的FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号，即链表的上一个以及下一个节点指针。

表空间碎片

已删除数据的record heap会被新写入的数据覆盖，但是新的数据和老的数据长度不可能完全匹配，于是就产生了很多空间碎片。下面是最简单的空间碎片清理方式：

# 查看
show table status like '%xxx%'\G;

# 清理
alert table xxx engine=innodb;

各种log

MySQL配置文件里面就需要指定binlog，errorlog，其实除了这之外，还有redolog，undolog，relaylog等等….

redolog

InnoDB在更新数据的时候会采用WAL(Write Ahead Logging)，这个日志就是redolog用来保证数据库宕机后可以通过该文件进行恢复(类似于elasticsearch的translog)。这个文件一般只会顺序写，只有在数据库启动的时候才会读取 redolog 文件看是否需要进行恢复。

redolog是几个文件组成类似一个环一样，循环写的。写入 redolog 的时候不能将没有同步到数据页上的记录覆盖，如果碰到这种情况会停下来先进行数据页同步然后在继续写入 redolog 。

其粒度为整个mysql，位于data目录下，在磁盘上默认文件名为：ib_logfile{n}

[root@aliyun-vm mysql]# ll
total 126804
drwxr-x--- 2 mysql mysql     4096 Sep 22 20:46 haha
-rw-r----- 1 mysql mysql 12582912 Sep 22 20:57 ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep 22 20:57 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep  3 21:05 ib_logfile1
......

其刷盘方式受该参数影响：innodb_flush_log_at_tx_commit:
0 : redo log thread 每1s刷新redo log和数据到磁盘；
1 : 每次事务提交时，刷新redo log和数据到磁盘；
2 : 每次事务提交时，只刷新redo log到磁盘，但不刷新数据到磁盘；

undolog

主要用来做事务回滚和MVCC, 这个文件存储在共享表空间中。undolog是逻辑日志(和binlog一样)，它不是记录的将物理的数据页恢复到之前的状态，而是记录的和原sql相反的sql, 比如insert对应delete, update对应另外一个update　。

另外undolog的写入也会有对应的redolog，因为undolog也需要持久化，通过WAL可以提高效率。

truncate 是DDL语言，无法回滚，操作之后，自增ID回归到0。
delete 是DML，可以回滚。delete了之后，自增ID不会回到0。

binlog

binlog和redolog的区别是: 记录的是逻辑操作（也就是对应的sql）,而redolog记录的底层某个数据页的物理操作，redolog是循环写的，而binlog是追加写的，不会覆盖以前写的数据。

binlog的三种格式：statement, raw, mixed

binlog 的写入页需要通过fsync来保证落盘，MySQL通过sync_binlog来控制是否需要同步刷盘。
1 : 不管sync问题，由系统决定何时刷盘；
n : 每执行n个事务之后，执行sync binlog到磁盘；

事务提交流程

在事务提交的时候要保证 redolog 写入到文件里，而这个 redolog 包含 主键索引上的数据页的修改，以及共享表空间的回滚段中 undolog 的插入。

在一次更改数据的事务提交中，几种log的刷盘流程如下：

1. 分配事务ID ，开启事务，获取锁，没有获取到锁则等待；
2. 执行器先通过存储引擎找到的数据页，如果缓冲池有则直接取出，没有则去主键索引上取出对应的数据页放入缓冲池；
3. 在数据页内找到记录，取出，对数据做更改，然后写入内存；
4. 生成redolog和undolog到内存，redolog状态为prepare；
5. 将redolog和undolog写入文件并调用fsync；
6. server层生成binlog并写入文件调用fsync；
7. 事务提交；
8. 将redolog的状态改为commited 释放锁。

总结起来就是： 开启事务-> 找记录-> 改字段 -> 生成redolog、undolog并刷盘 -> 生成binlog并刷盘 -> 提交事务

索引

关于B+ TREE相关的理论就不分析了，在之前的一片文章中有提到，有兴趣的可以参考这里

如图，索引分为聚集索引和非聚集索引，他们的区别是是否主键索引在叶子节点包含了数据。非聚集索引是将数据存储在别的地方，通过在索引的叶子节点中存储指针来实现的。

注意: 如图所示，innodb普通索引的叶子节点并不包含所有行的数据记录，只会在叶子节点存有自己本身的键值和主键的值；

使用索引的情况

• 主键自动建立唯一索引
• 经常作为查询条件在WHERE或者ORDER BY语句中出现的列要建立索引
• 用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引
• 查询中与其他表关联的字段，外键关系建立索引
• 高并发条件下倾向组合索引

注意: LIKE操作中，’%aaa%’不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。

锁

先来学习两个概念：

• 脏读：读到其他事务没有commit的数据；
• 幻读：在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多或者少的那一行被叫做幻行。

根据加锁的范围，可以分为：

• 全局锁
  全局锁会把整个数据库实例加锁，命令为 flush tables withs read lock ，将使数据库处于只读状态，其他数据写入和修改表结构等语句会阻塞，一般在备库上做全局备份使用。

• 表级锁

  1. 表锁，命令为 lock table with read/write ，和读写锁一样。
  2. 元数据锁，也叫意向锁，不需要显示申明，当执行修改表结构，加索引的时候会自动加元数据写锁，对表进行增删改查的时候会加元数据读锁。

  如果一条SQL语句用不到索引就不会使用行级锁的，会使用表级锁。

• 行锁
  通过索引条件检索数据，InnoDB才使用行级锁。
  行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

还有一种行级锁称为间隙锁，他锁定的是两条记录之间的间隙，防止其他事务往这个间隙插入数据，间隙锁是隐式锁，是存储引擎自己加上的。

备份

冷备

需要停机做备份，该方式最稳妥，但是对业务影响很大，很多环境无法具备冷备条件。

热备

也就是在线备份数据，这里又分为几种情况:

• 裸文件备份（XtraBackup）
  这种方式下，mysql的文件会被整体备份。XtraBackup主要使用了MySQL redolog的特征来实现该功能。

  1. XtraBackup复制InnoDB 数据文件，这会导致内部不一致的数据，但是它会对文件执行崩溃恢复，以使其再次成为一个一致的可用数据库。
  2. 这样做是可行的，因为InnoDB维护一个 REDO日志，也称为事务日志。REDO日志包含了InnoDB数据每次更改的记录。当InnoDB启动时，REDO日志会检查数据文件和事务日志，并执行两个步骤。它将已提交的事务日志条目应用于数据文件，并对任何修改了数据但未提交的事务执行undo操作。
  3. Percona XtraBackup会在启动时记住日志序列号(LSN)，然后复制数据文件。这需要一些时间来完成，如果文件正在改变，那么它们会在不同的时间点反映数据库的状态。同时，Percona XtraBackup运行一个后台进程，用于监视事务日志文件，并从中复制更改。PerconaXtra backup需要持续这样做，因为事务日志是以循环方式写人的，并且可以在一段时间后重新使用。 Percona XtraBackup开始执行后，需要复制每次数据文件更改对应的事务日志记录。

• 逻辑备份
  逻辑备份不是存mysql文件，而是将数据导出来存储。下面工具都可以用来做逻辑备份：mysqldump, mysqldumper, select ... into outfile。

数据复制

GTID

• 引入
  整个集群架构的节点,通常是master在工作,其他两个结点做备份。各个节点的机器,性能不可能完全一致,所以,在做备份时,备份的速度就不一样。当 master突然宕掉之后,马上会启用从节点机器,接管 master 的工作。当有多个从节点时,选择备份日志文件最接近 master的那个节点。现在就出现问题了,当salve1 变成主节点, 那slave2就应该从slave1 的什么位置开始同步数据呢？

  有了GTID全局的,将所有节点对于同一个event的标记完全一致,当master宕掉之后,slave2根据同一个GTID直接去读取slave1的日志文件,继续同步。

• GTID原理
  全局事务ID的格式： GTID=server_id:transaction_id
  transaction_id 是顺序化的序列号(sequence number)，在每台 MySQL 服务器上都是从1开始自增长的序列，是事务的唯一标识。

  Master: gtid_next 是默认的 AUTOMATIC,即 GTID 在每次事务提交时自动生成。它从当前已执行的 GTID 集合(即 gtid_executed)中，找一个大于 0 的未使用的最小值作为下个事务 GTID。同时将 GTID 写入到 binlog(set gtid_next 记录)，在实际的更新事务记录之前。
  Slave: 从 binlog 先读取到主库的 GTID(即 set gtid_next 记录)，而后执行的事务采用该 GTID。

异步复制

这是MySQL最传统的复制方式，由主库负责binlog的线程将binlog发送给从库，主库不会去验证Binlog有没有成功复制到从库。

异步复制中，如果主库提交一个事务并写入Binlog中后，当从库还没有从主库得到Binlog时，主库宕机了或因磁盘损坏等故障导致该事务的Binlog丢失了，那从库就不会得到这个事务，也就造成了主从数据的不一致。

半同步复制

为解决异步复制可能会丢事务的缺陷，半同步复制中，当主库每提交一个事务后，不会立即返回，而是等待任意一个从库接收到Binlog并成功写入Relay-log中才返回客户端。这就保证了一个事务至少有两份日志，一份保存在主库的Binlog，另一份保存在其中一个从库的Relay-log中。

如果在主库推送binlog到从库的过程中，从库宕机了或网络故障，导致从库没有接收到这个事务的Binlog；主库在等待（rpl_semi_sync_master_timeout）时间后，将半同步复制切换到异步复制模式；当从库恢复正常连接到主库后，主库又会自动切换回半同步复制。

组复制

组复制由多个MySQL节点组成，组中的每一个节点都能够在任意时刻独立执行事务，但是事务的提交需要得到组内所有成员的冲突检测（certification）一致通过。

假设我们要修改一条记录，当处理MySQL请求的节点修改了数据并写入内存之后，它会向组内所有节点发起广播请求（包含了原始的数据和更新的内容）。group组内的通讯是采用基于paxos协议的xcom来实现的，它的一个特性就是消息是有序传送，每个节点接收到的消息顺序都是相同的。其他节点以相同的顺序接收到请求，此时会生成一个请求的顺序队列。由于各个节点都在处理各种事物，很有可能接收队列中的两条来自不同请求节点的事务之间存在冲突，此时需要根据其消息体中的原始记录以及更新内容来做冲突检测（certification）。

一旦发现半数以上节点返回存在冲突，解决过程也非常简单，直接按照进入队列的先后顺序，对晚提交的事务不予通过即可。此时需要在发起请求的节点上执行回滚操作，其他节点丢弃该事务请求即可。

总结起来就是：冲突检测在行级处理，首先执行的一方提交成功，晚提交的一方回滚。

集群方案

MHA (MMM)

需要部署MHA manager，用来探测master的健康状况。当发现master挂掉后，自动将具有最新数据的slave提升为master，并将其他slave的master改为新的master。

HMA node作为agent运行在每台mysql服务器节点上，通过监控具备解析和清理日志的脚本来加快故障转移速度。该方案中，MHA管理节点本身的HA无法保证。MMM方案和MHA类似。

MM + keepalive

后端主从复制，在master故障的时候由keepalive的脚本来负责切换VIP和master，并修改主从关系。

Galera

节点在接收sql请求后，对于DDL操作，在commit之前，由WSREP API调用galera库进行集群内广播，所有其他节点验证成功后事务在集群所有节点进行提交，反之rollback。至少三个节点，每个节点都可读写，不共享数据，集群通过wsrep自动将写入的数据在集群节点之间做同步。需要为原生MySQL节点打wsrep补丁。

基于galera的方案有两个：

1. Percona XtraDB cluster
2. mariadb galera cluster

这种方案是完全的同步复制多主方案，好处：是能够完全保证数据的一致性，坏处：是整个集群的性能受限于性能最差的节点。

DRBD

DRBD(DistributedReplicatedBlockDevice)是一个基于块设备级别在远程服务器直接同步和镜像数据的软件，用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。当用户将数据写入本地磁盘时，还会将数据发送到网络中另一台主机的磁盘上，这样的本地主机(主节点)与远程主机(备节点)的数据就可以保证实时同步。

高可用软件加DRBD其实在架构上跟SAN是相同的，唯一不同的是没有使用SAN网络存储，而是使用Local Disk实时复制磁盘数据，虽然没有MySQL Replication那样主从有数据不一致性，但是DRBD实时复制数据在性能上有很大的影响，网上有人测过大概是降40%性能。

DRBD是应用级别的磁盘IO同步工具，Linux内核提供了一个钩子，每一次IO都通知到DRBD，然后DRBD将该IO同步至备机，并且返回ACK，主机才会认为该IO操作完成。所以DRBD的方案优点就是数据可以保证完全的一致，缺点则是牺牲了MySQL的扩展性以及极大的降低了磁盘IO的性能。

MGR

有两种模式：单主模式single-primary mode 和 多主模式multi-primary mode。

在前面组复制的章节已经讲到其冲突检测的原理，另外还涉及到选主、加入group以及底层通信的原理，这些都是通过Paxos协议来保障的。下面是MGR plugin的功能结构图。

NDB

将MySQL的存储引擎和传统MySQL服务器分离，底层使用Ndbd来提供存储，MySQL层只负责缓存、优化等工作。

• 管理(MGM)节点
  这类节点的作用是管理MySQL集群内的其他节点，如提供配置数据、启动并停止节点、运行备份等。由于这类节点负责管理其他节点的配置，应在启动其他节点之前首先启动这类节点。
• 数据节点
  这类节点用于保存集群的数据。数据节点的数目与副本的数目相关，是片段的倍数。例如，对于两个副本，每个副本有两个片段，那么就有4个数据节点。
• SQL节点
  这是用来访问集群数据的节点。对于MySQL集群，客户端节点是使用NDB集群存储引擎的传统MySQL服务器。

Ndbd节点负责存储数据，其数据的存储类似于ES中Lucene的方案；