基本架构图

日志

redo log: 保证即使数据库发生异常重启, 之前提交的记录都不会丢失, 这个能力称为crash-safe. InnoDB引擎特有的日志
binlog: 归档日志

redo log与binlog不同点

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的, 所有引擎都可以使用.

2. redo log是物理日志, 记录的是”在某个数据页上做了什么修改”;binlog是逻辑日志, 记录的是这个语句的原始逻辑, 比如”给ID=2这一行的c字段加1”.

3. redo log是循环写的, 空间固定会用完; binlog是可以追加写入的. “追加写”是指binlog文件写到一定大小后会切换到下一个, 并不会覆盖以前的日志.

更新语句执行过程

假设更新前字段c的值为0

update t set c = c + 1 where id = 2 

1. 执行器先找引擎取 id=2 这一行。id是主键，引擎直接用树搜索找到这一行。如果 id=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log改成提交（commit）状态，更新完成。

写redo log和binlog使用了两阶段提交来保证数据一致性。

两阶段提交如何保证数据一致性

1. 如果在图中时刻 A 的地方，也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。
2. 如果在图中时刻 B 的地方，也就是binlog写完，redo log还没有commit完成就发生了crash
   • 如果redo log里面的事物是完整的，也就是有了commit标识，则直接提交事物
   • 如果redo log里面的事物有完整的prepare标识，此时需要判断binlog是否完整(如何找到redo log对应的binlog)
     • 如果binlog也是完整的，则提交事物(如何知道binlog是完整的)
     • 如果binlog不完整，则回滚这个事物

MySQL 怎么知道 binlog 是完整的

1. statement 格式的 binlog，最后会有 COMMIT；

2. row 格式的 binlog，最后会有一个 XID event。

另外，在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。所以，MySQL 还是有办法验证事务 binlog 的完整性的。

redo log 和 binlog 是怎么关联起来的

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

1. 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；

2. 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢

redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。

1. 如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。
2. 在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

不使用两阶段提交：

1. 先写redo log，再写binlog：假设redo log已经写完，binlog还没写完，此时MySQL进程异常重启，由于redo log里面已经记录了c = c+1 = 1，所以会将id=2这条记录的c字段恢复成c+1 = 1。但是binlog里面没有这个记录，如果使用binlog进行数据恢复或者其它从节点使用这个binlog进行数据同步，那么就会少了这一个更新操作，出现数据不一致。

2. 先写binlog，再写redo log：假设binlog已经写完，redo log还没写完，此时MySQL进程异常重启，由于redo log里面没有记录c+1，所以此时id=2这条记录的c字段还是0。但是binlog里面已经记录了更新操作，如果使用binlog进行数据恢复或者其它从节点使用binlog进行数据同步，此时id=1这条记录的c=1，出现数据不一致。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

redo log 用于保证 crash-safe能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后binlog 不丢失。

binlog_format

1. STATEMENT: 记录的是mysql执行的sql语句

2. ROW: 记录的是增删改查的数据行信息，有以下三种情况

   • FULL：数据修改时会记录一行数据所有列的内容，无论这些列是否被修改过。比如某张表有20列，只对其中一列进行了update操作，二进制日志会记录一行中所有20列被修改前与修改后的所有内
   • MINIMAL：比如某张表有20列，只对其中一列进行了update操作，二进制日志只会记录这一列被修改前与修改后的内容。
   • NOBLOB： 和FULL方式相似，仅仅是当blog或这些列没有进行修改时，不会记录这些属性的列

3. MIXED: 根据sql语句由系统决定在基于段和基于行的日志格式中进行选择

事物的隔离级别

读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。脏读。

读已提交：一个事务提交之后，它做的变更才会被其他事务看到。幻读。

可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化：顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

对四种隔离级别的实现，是依靠视图来实现的。

1、读未提交：没有视图的概念，直接返回记录的最新值；

2、读已提交：每次执行sql语句之前创建视图；

3、可重复读：每次创建事务的时候创建视图；

4、串行化：通过加锁来避免并行访问。

假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为。

四种隔离级别下的V1、V2、V3分别如下

若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。

若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。

若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。

若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

事物隔离级别的实现

在 MySQL 中，每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志(undo log)里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

回滚日志在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

索引

BST树 — 二叉排序树

特点

1. 根节点的值大于其左子树中任意一个节点的值

2. 根结点的值小于其右节点中任意一节点的值

3. 这一规则适用于二叉查找树中的每一个节点。

优点：查询的时间复杂度比链表快，链表的查询时间复杂度是O(n)，二叉排序树平均是O(logn)。二叉排序树越平衡，越能模拟二分法，所以越能想二分法的查询的时间复杂度O(logn)。

不足：如果插入结点的值的顺序，是越来越小或者越来越大的，那么就会退化为一条链表，那么其查询的时间复杂度就会降为O(n)。

AVL树 — 平衡二叉树

特点

1. 拥有BST树的特点：根节点的值大于其左子树中任意一个节点的值，小于其右节点中任意一节点的值，这一规则适用于二叉查找树中的每一个节点。

2. AVL树上任意结点的左、右子树的高度差最大为1。

由于AVL树的第二个特点，使得，AVL树的形状肯定不会退化成一条链表的，而是“矮胖”型的树。所以能确保AVL的查找、添加、删除的时间复杂度都是O(logn)。

红黑树

1. 拥有BST树的特点

2. 确保没有一条路径会比其他路径长出俩倍。因而，红黑树是相对接近平衡的二叉树。

*B树 *

数据保存在节点上。

B+树

数据保存在叶子节点上，叶子结点都会被连成一条链表。叶子本身按索引值的大小从小到大进行排序。即这条链表是 从小到大的。多了条链表方便范围查找数据。

锁

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份，主要针对不支持事物的存储引擎。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

行锁就是针对数据表中行记录的锁。比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

本作品采用《CC 协议》，转载必须注明作者和本文链接