《MySQL实战45讲》读书笔记，原课程链接：time.geekbang.org/column/intr…

作者：JaneRoad

基础架构

MySQL分为Server层和存储引擎层两部分

Server层

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令是mysql -h$ip -p$port -u$user -p

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。在使用中要尽量使用长连接。

查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，如果能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

查询缓存往往弊大于利。 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

分析器

如果没有命中查询缓存，就要开始真正执行语句了。

分析器先会做“词法分析”。输入的是由多个字符串和空格组成的一条 SQL 语句， MySQL 需要识别出里面的字符串分别是什么，代表什么。

做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断输入的这个SQL 语句是否满足 MySQL 语法。

优化器

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联 (join)的时候，决定各个表的连接顺序。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行的时候，会先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误

存储引擎层

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、 MyISAM、Memory 等多个存储引擎。

现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB。不同的存储引擎共用一个Server 层。

日志模块

与查询语句的流程不一样的是，更新语句的流程还涉及两个重要的日志模块：redo log（重做日志）和binlog（归档日志）。

redo log

MySQL存在一个问题，每一次的更新都需要写进磁盘，然后磁盘也要找到相应的记录然后再更新，整个过程 IO 成本、查找成本都很高。

为了解决这个问题，MySQL 的设计者就用了WAL 技术来提升更新效率。WAL 的全称 是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log中，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 4 个文件末尾后就回到 第1个文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的空着的部分可以用来记录新的操作，如果 write pos 追上 checkpoint，表示记录的空间满了，这个时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

binlog

redo log是InnoDB引擎特有的日志，InnoDB引擎属于MySQL的引擎层。而Server层也有自己的日志，那就是binlog（归档日志）。

最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

binlog和redo log的区别有以下几点：

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1”
• redo log 是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

执行器和 InnoDB 引擎在执行 update 语句时的内部流程。

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

下图是update 语句的执行流程图，绿色表示在InnoDB内部执行的，红色表示是在执行器中执行的。

两阶段提交

上图最后三步骤看上去有点绕，将redo log的写入拆成了两个步骤：prepare和commit，这就是两阶段提交。

两阶段提交的作用是为了让两份日志之间的逻辑一致。让两份日志之间逻辑一致的重要性下面举例说明，在说明之前要先说一下数据库的恢复过程。

binlog会记录所有的逻辑操作，并且采用追加写的形式。如果备份系统中有保存最近半个月所有的binlog，同时系统定期做整库备份，那么可以让数据库恢复到半个月内的任意一秒的状态。当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那么可以

• 首先，找到最近的一次全量备份，从这个备份恢复到临时库
• 然后，从备份的时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的那个时刻

这样临时库就跟误删之前的线上库一样了，然后可以把表数据从临时库取出来，按需要恢复到线上库去。

以上就是数据恢复的过程，由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序，就会出现问题。

假设当前 ID=2 的行，字段 c 的值是 0，再假设执 行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash

1、先写 redo log 后写 binlog

假设在 redo log 写完，binlog 还没有写完的时候， MySQL 进程异常重启。redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。 然后会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

2、先写 binlog 后写 redo log

如果在 binlog 写完之后 crash，由于 redo log 还没写， 崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了把c从0改成1这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。 而且，即使需要恢复临时库的场景不多，我们也应该使用两阶段提交，因为当你需要扩容的时候，就需要再多搭建一些备库来增加系统的读能力，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致线上出现主从数据库不一致的情况。简单地说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

事务

什么是事务？事务最经典的例子就是转账，你要你的朋友转账100元。转账会有一系列的操作，比如查询余额、做加减法、更新余额等，这些操作必须保证是一体的，不然会出现你的账户扣了100元，但你的朋友又没收到100元的情况。这就是事务。简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

在 MySQL 中，事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

隔离性和隔离级别

事务的四大特性有ACID(Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性)，其中就有隔离性。

当数据库上有多个事务同时执行的时候，就可能出现脏读、不可重复读 、幻读的问题，为了解决这些问题，就有 了隔离级别的概念。

隔离级别设置的越高，也就是越严格，那就意味着效率越低，所以安全性和性能必须寻找一个平衡点。SQL标准的事务隔离级别包括读未提交、读提交、可重复读和串行化。

读未提交

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

读提交

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

可重复读

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。

在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化

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

在不同的隔离级别下，事务A会有不同的返回结果。

如果隔离级别是读未提交，则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。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在可重复读隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。 在读提交隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，读未提交隔离级别下直接返回记录上的最新值，没有视图概念；而串行 化隔离级别下直接用加锁的方式来避免并行访问。配置的方式是，将启动参数transaction-isolation的值设置成 READ-COMMITTED。可以用show variables查看当前的值

存在即合理，每个隔离级别都有自己适合的使用场景。有人可能会问，什么时候需要“可重复读”的场景呢？ 假设一个表存了每个月月底的余额，一个表存了账单明细。这时候要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。在校对过程中，即使发生了一笔新的交易，也不应该影响你的校对结果，这个时候就应该用可重复读。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

事务隔离的实现

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

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

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

回滚日志在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。什么时候才不需要了呢?就是当系统里没有比这个回滚日志更早的 read-view 的时候。

因此，尽量不要使用长事务，长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

事务的启动方式

MySQL 的事务启动方式有：

• 显式启动事务语句，begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
• set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一 个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致 接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。因此，最好总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

对于一个需要频繁使用事务的业务，如果不希望频繁地使用执行一次begin才能开启事务，可以使用 commit work and chain 语法。

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 是提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

InnoDB 的事务和锁

InnoDB的事务为什么能秒级创建快照

在可重复读隔离级别下，事务在启动的时候就拍了个快照。这个快照是基于整库的。实际上，快照并不需要拷贝出这 100G 的数据。

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它在事务开始的时候向 InnoDB 的事务系统申请的，按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。如下图所示，就是一个记录被多个事务连续更新后的状态。

语句更新会生成 undo log，图中的三个箭头，就是 undo log。

而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。

比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。因此，一个事务只需要在启动的时候声明说，以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认。如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本。如果上一个版本也不可见，那就得继续往前找。如果是这个事务自己更新的数据，它自己还是要认的。在实现上，InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在活跃的所有事务 ID。 （“活跃”指的就是，启动了但还没提交）数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图(read-view)。 而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。这个视图数组把所有的row trx_id分成了几种不同的情况。

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能:

• 如果落在米黄色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的

• 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的;

• 如果落在橘色部分，那就包括两种情况

  • 若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见
  • 若 row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见

比如，上上张图中的数据来说，如果有一个事务，它的低水位是 18，那么当它访问这一行数据时，就会从 V4 通过 U3 计算出 V3，所以在它看来，这一行的值是 11。

有了这个声明后，系统里面随后发生的更新，就跟这个事务看到的内容无关了。因为之后的更新，生成的版本一定属于上面的不可见情况，对它来说，这些新的数据版本是不存在的，所以这个事务的快照，就是静态的了。

所以，InnoDB 利用了所有数据都有多个版本的这个特性，实现了"秒级创建快照"的能力。

分析下列三个事务的执行，事务A返回什么结果？

作出假设

• 事务 A 开始前，系统里面只有一个活跃事务 ID 是 99;
• 事务 A、B、C 的版本号分别是 100、101、102，且当前系统里只有这四个事务
• 三个事务开始前，(1,1)这一行数据的 row trx_id 是 90。

这样，事务 A 的视图数组就是 [99,100], 事务 B 的视图数组是 [99,100,101], 事务 C 的视 图数组是 [99,100,101,102]。

第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。这时候，这个数据的最新版本的 row trx_id 是 102，而 90 这个版本已经成为了历史版本。

第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。这时候，这个数据的最新版本的 row trx_id是 101，而 102 又成为了历史版本。

在事务 A 查询的时候，其实事务 B 还没有提交，但是它生成的 (1,3) 这个版本已经变成当前版本了。但这个版本对事务 A 必须是不可见的，否则就变成脏读了。

事务 A 开始读数据了，它的视图数组是 [99,100]。读数据都是从当前版本读起的。所以，事务 A 查询语句的读数据流程是这样的：

• 找到 (1,3) 的时候，判断出 row trx_id=101，比高水位大，处于红色区域，不可见
• 接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见
• 再往前找，终于找到了(1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。

这样执行下来，虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。

所以，一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况:

• 版本未提交，不可见
• 版本已提交，但是是在视图创建后提交的，不可见
• 版本已提交，而且是在视图创建前提交的，可见

根据上述总结的情况来分析这个问题，会比一个个去比较事务版本id更加简便些。

事务A的查询语句的视图数组是在事务 A 启动的时候生成的，这时候：

• (1,3) 还没提交，属于情况 1，不可见
• (1,2) 虽然提交了，但是是在视图数组创建之后提交的，属于情况 2，不可见
• (1,1) 是在视图数组创建之前提交的，可见。

当前读

事务B的 update 语句，如果按照一致性读，不是矛盾了吗？

有这样一个疑问：事务B的视图数组是先生成的，之后事务C才提交，不是应该看不见 (1,2) 吗，怎么能算出 (1,3) 来？

是的，如果事务B在更新之前查询一次数据，这个查询返回的 k 的值确实是 1。但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务 C 的更新就丢失了。因此，事务 B 此时的 set k=k+1 是在(1,2)的基础上进行的操作。

所以，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为"当前读"(current read)。

因此，在更新的时候，当前读拿到的数据是 (1,2)，更新后生成了新版本的数据 (1,3)，这个新版本的row trx_id是 101。所以，在执行事务 B 查询语句的时候，一看自己的版本号是 101，最新数据的版本号也是 101，是自己的更新，可以直接使用，所以查询得到的 k 的值是 3。其实，除了 update 语句外，select 语句如果加锁，也是当前读。

所以，如果把事务 A 的查询语句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以读到版本号是 101 的数据，返回的 k 的值是 3。

下面这两个 select 语句，就是分别加了读锁(S 锁，共享锁)和写锁(X 锁，排他锁)。

select k from t where id=1 lock in share mode; 
select k from t where id=1 for update;

假设事务 C 不是马上提交的，而是变成了下面的事务 C'，会怎么样呢?

事务 C‘的不同是，更新后并没有马上提交，在它提交前，事务 B 的更新语句先发起了。

虽然事务 C’还没提交，但是 (1,2) 这个版本也已经生成了，并且是当前的最新版本。这时候，两阶段锁协议就派上用场了。事务 C‘没提交，也就是说 (1,2) 这个版本上的写锁还没释放。而事务 B 是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C释放这个锁，才能继续它的当前读。

由此，一致性读，当前读和行锁就串起来了。

事务的可重复读的能力是怎么实现的

可重复读的核心就是一致性读。而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

• 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图
• 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

在读提交隔离级别下，事务 A 和事务 B 的查询语句查到的 k，分别是多少呢?

start transaction with consistent snapshot;的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的 start transaction。

下图是读提交时的状态图。这两个查询语句的创建视图数组的时机发生了变化， 就是图中的 read view 框。(注意：这里，我们用的还是事务 C 的逻辑直接提交)

这时，事务 A 的查询语句的视图数组是在执行这个语句的时候创建的，时序上 (1,2)、(1,3) 的生成时间都在创建这个视图数组的时刻之前。但是，在这个时刻：

• (1,3) 还没提交，属于情况 1，不可见
• (1,2) 提交了，属于情况 3，可见

所以，这时候事务 A 查询语句返回的是 k=2。显然地，事务 B 查询结果 k=3。

总结

InnoDB 的行数据有多个版本，每个数据版本有自己的row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id和一致性视图确定数据版本的可见性。

1、对于可重复读，查询只承认在事务启动前就已经提交完成的数据 2、对于读提交，查询只承认在语句启动前就已经提交完成的数据 3、当前读，总是读取已经提交完成的最新版本。

数据库索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引的常见模型

索引的出现是为了提高查询效率，但是实现索引的方式却有很多种。三种常见、也比较简单的索引数据结构，它们分别是哈希表、有序数组和搜索树。

哈希表

哈希表是一种以键值对(key-value)存储数据的结构，只要输入待查找的值即 key， 就可以找到其对应的值即 Value。

哈希的思路是把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的 一种方法是，拉出一个链表。

假设，现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引的示意图如下所示。

图中，User2 和 User4 根据身份证号算出来的值都是 N，但后面还跟了一个链表。

假设，这时候你要查 ID_card_n2 对应的名字是什么，首先，将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2。

图中四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。如果要找身份证号在 [ID_card_X, ID_card_Y] 这个区间的所有用户，就必须全部扫描一遍了。所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

有序数组

和哈希表对比，有序数组在等值查询和范围查询场景中的性能就都非常优秀。依照上面根据身份证号查名字的例子，如果使用有序数组来实现的话，示意图如下。

假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候如果要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O(log(N))。

除此之外，这个索引结构支持范围查询。要查身份证号在 [ID_card_X, ID_card_Y] 区 间的 User，可以先用二分法找到 ID_card_X，如果不存在 ID_card_X，就找到大于 ID_card_X 的第一个 User，然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证号，退出循环。

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，每次往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

所以，有序数组索引只适用于静态存储引擎，比如要保存的是某一年某个城市的所有人口信息，这类不会再修改的数据。

搜索树

还是上面根据身份证号查名字的例子，如果用二叉搜索树来实现的话，示意图如下所示。

二叉搜索树的特点是，每个节点的左儿子小于父节点，父节点又小于右儿子。

这样如果要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))。

为了维持 O(log(N)) 的查询复杂度，需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。原因是索引不止存在内存中，还要写到磁盘上。

想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么就不应该使用二叉树，而是要使用N叉树。这里，N叉树中的N取决于数据块的大小。以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。假设，有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。

create table T(   id int primary key,   k int not null,   name varchar(16),   index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引。

问：基于主键索引和普通索引的查询有什么区别?

答：如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此在应用中应该尽量使用主键查询。

索引的维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例， 如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

分析：哪些场景下应该使用自增主键，而哪些场景下不应该

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的 NOT NULL PRIMARY KEY AUTO_INCREMENT

插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

除了考虑性能外，还可以从存储空间的角度来看。假设表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型(bigint)则是 8 个字节。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的？

• 只有一个索引
• 该索引必须是唯一索引

这就是典型的 KV 场景。 由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

这时候就要优先考虑尽量使用主键查询原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

覆盖索引

在下面这个表 T 中，如果执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行?

 create table T ( ID int primary key, k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT '',   index k(k) )engine=InnoDB;

该表的索引组织结构和上图一致，这条SQL查询语句的执行流程如下

• 在 k 索引树上找到 k=3 的记录，取得 ID = 300
• 再到 ID 索引树查到 ID=300 对应的 R3
• 在 k 索引树取下一个值 k=5，取得 ID=500
• 再回到 ID 索引树查到 ID=500 对应的 R4
• 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，称为回表。

可以看到，这个查询过程读 了 k 索引树的 3 条记录(步骤 1、3 和 5)，回表了两次(步骤 2 和 4)。

由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，我们应该经过索引优化，避免回表过程。

如果执行的语句是select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经覆盖了查询需求，称为覆盖索引。

覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，R3~R5(对应的索引k上的记录项)，但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。

在一个市民信息表上，是否有必要将身份证号和名字建立联合索引?

CREATE TABLE `tuser` (  `id` int(11) NOT NULL,  `id_card` varchar(32) DEFAULT NULL,   `name` varchar(32) DEFAULT NULL,  `age` int(11) DEFAULT NULL, `ismale` tinyint(1) DEFAULT NULL,   PRIMARY KEY (`id`), KEY `id_card` (`id_card`),  KEY `name_age` (`name`,`age`) )ENGINE=InnoDB

身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，只要在身份证号字段上建立索引就够了，而再建立联合索引显得有些浪费空间，当时如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

最左前缀原则

如果为每一种查询都设计一个索引，索引显得太多了。如果现在要按照市民的身份证号去查家庭地址，虽然这个查询需求在业务中出现的概率不高，但是又不能让它走全表扫描，反过来说，单独为一个不频繁的请求创建一个 索引又感觉有点浪费。

其实，B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。 下面，用(name，age)这个联合索引来分析。

索引项是按照索引定义里面出现的字段顺序排序的。需求是查到所有名字是张三的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

如果要查的是所有名字第一个字是张的人，SQL 语句的条件是where name like ‘张 %’。这时也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

在建立联合索引的时候，如何安排索引内的字段顺序？

评估标准是索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

如果既有联合查询，又有基于 a、b 各自的查询呢？

查询条件里面只有 b 的语句， 是无法使用 (a,b) 这个联合索引的，这时候不得不维护另外一个索引，也就是说需要 同时维护 (a,b)、(b) 这两个索引。

这时候，要考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我最好是创建一个(name,age) 的联合索引和一个 (age) 的单字段索引。

索引下推

以市民表的联合索引(name, age)为例。

有一个需求：检索出表中名字第一个字是张，而且年龄是 10 岁的所有男孩。SQL 语句如下

select * from tuser where name like '张 %' and age=10 and ismale=1;

这个语句在搜索索引树的时候，用 ''张''找到第一个满足条件的记录 ID3，然后判断其他条件是否满足。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。下图是流程图

这两个图里面，每一个箭头表示回表一次。

第一张图中在 (name,age) 索引里面去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把name 第一个字是张的记录一条条取出来回表。因此， 需要回表 4 次。

第二张图的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

普通索引和唯一索引的选择

假设维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的 SQL 语句

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

所以，你一定会考虑在 id_card 字段上建索引。由于身份证号字段比较大，所以不能把身份证号当做主键，那么要么给 id_card 字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的，那么就要从性能上考虑应该用哪种索引了。

查询过程

假设，执行查询的语句是select id from T where k=5这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录， 直到碰到第一个不满足 k=5 条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

以上两种过程带来的性能差距微乎其微。InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次查找和判断下一条记录的操作，就只需要一次指针寻找和一次计算。如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。但是，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

更新过程

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭的过程中，也会执行 merge 操作。

如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

什么条件下可以使用change buffer ？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要 插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

如果要在这张表中插 入一个新记录 (4,400) 的话，

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束
• 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下:

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束
• 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 的使用场景

通过上述例子可以得出结论，change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。

普通索引的所有场景，使用 change buffer 都可以起到加速作用吗

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多(也就是这个页面上要更新的次数越多)，收益就越大。因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过 程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以， 对于这种业务模式来说，change buffer 反而起到了副作用。

索引选择和实践

其实普通索引和唯一索引这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以应该尽量选择普通索引。如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

在实际使用中会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

change buffer 和 redo log

现在要在表上执行插入语句：

insert into t(id,k) values(id1,k1),(id2,k2);

假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。

分析这条更新语句，会发现它涉及了四个部分:

• 内存
• redo log(ib_log_fileX)
• 数据表空间(t.ibd)
• 系统表空间(ibdata1)

这条更新语句做了如下的操作：（按照图中的数字顺序）

1、Page 1 在内存中，直接更新内存

2、Page 2 没有在内存中，就在内存的 change buffer 区域，记录下我要往 Page 2 插入一行这个信息

3、将上述两个动作记入 redo log 中。（图中 3 和 4）

执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

在这之后的读请求，比如，执行 select * from t where k in (k1, k2)

如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间(ibdata1)和 redo log(ib_log_fileX)无关了。

1、读 Page 1 的时候，直接从内存返回

2、要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。

可以看到，直到需要读 Page 2 的时候，这个数据页才会被读入内存。

所以，如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写)，而 change buffer 主要节省的则是随机读磁盘 的 IO 消耗。

\

数据库锁

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁就是对整个数据库实例加锁。

MySQL 提供了一个加全局读锁的方法， 命令是Flush tables with read lock 。

当你需要让整个库处于只读状态的时候， 可以使用这个命令，之后其他线程的以下语句会被阻塞：

• 数据更新语句(数据的增删改)
• 数据定义语句(包括建表、修改表结构等)
• 更新类事务的提交语句

全局锁的典型使用场景是，做全库逻辑备份。 也就是把整库每个表都 select 出来存成文本。

以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。但是让整库都只读，听上去就很危险。因为如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；如果在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

看来加全局锁不太好。但是备份为什么要加锁呢？

假设现在要维护购买系统，最主要的是用户账户余额表和用户课程表。

现在发起一个逻辑备份。假设备份期间，有一个用户，他购买了一门课程，业务逻辑里就要扣掉他的余额，然后往已购课程里面加上一门课。如果时间顺序上是先备份账户余额表，然后用户购买，然后备份用户课程表，会产生如下的过程。

这个备份结果里，用户 A 的数据状态是账户余额没扣，但是用户课程表里面已经多了一门课。如果后面用这个备份来恢复数据的话，用户 A 就发现，自己赚了。如果备份表的顺序反过来，先备份用户课程表再备份账户余额表，那问题就更大了，用户扣了钱但是没有课程。也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

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

虽然有了mysqldump，但是还需要 FTWRL 。一致性读是好，但前提是引擎要支持这个隔离级别。 对于 MyISAM 这种不支持事务的引擎，如果备份过程中有 更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，就需要使用 FTWRL 命令了。

所以，single-transaction 方法只适用于所有的表使用事务引擎的库。 如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员 使用 InnoDB 替代 MyISAM 的原因之一。

之所以我们使用FTWRL命令而不使用set global readonly=true 的方式，是因为在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库；而且在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个 库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状 态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁

表锁的语法是 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 写锁。读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

虽然 MDL 锁是系统默认会加的，但是一个不能忽略的机制

假设表 t 是一个小表。

可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。

之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。

如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

基于上述分析，如果我们要安全地给小表加字段，首先要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

如果要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而不得不加个字段。这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，例如MyISAM 引擎就不支持行锁。

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

两阶段锁

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

所以，如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。举个例子，假如现在维护一个电影票的交易业务，顾客A要在影院B购买电影票，这个场景需要涉及以下操作：

• 从顾客A账户余额中扣除电影票价
• 给影院B的账户余额增加票价
• 记录一条交易日志

也就是说，要完成这个交易，需要update两条记录，并insert一条记录。为了保证交易的原子性，要把三个操作放在一个事务里。

如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。根据两阶段锁协议，不论怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

死锁和死锁检测

根据上面说的电影院的例子，如果这个影院做活动，可以低价预售一年内所有的电影票，而且这个活动只做一天。于是在活动时间开始的时候，MySQL 就挂了。登上服务器一看，CPU 消耗接近 100%，但整个数据库每秒就执行不到 100 个事务。这样的现象就和死锁有关了。

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

上图中，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的 行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

当出现死锁以后，有两种策略:

• 直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout的默认值是 50s，如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行，这个等待时间往往是无法接受的。但是，又不能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下还是要采用第二种策略，主动死锁检测。而且innodb_deadlock_detect的默认值本身就是 on。

主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就 是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，就会出现上面说的例子， CPU 利用率很高，但是每秒却执行不了几个事务。

如何解决由热点行更新导致的性能问题？

• 确保这个业务一定不会出现死锁，可以临时把死锁检测关掉

这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

• 控制并发度

根据上面的分析会发现如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的办法就是，在客户端做并发控制。但是这个方法不太可行，因为客户端很多。有的应用有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000。

因此，这个并发控制要做在数据库服务端。如果有中间件，可以考虑在中间件实现。如果能修改MySQL源码的人也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

如果团队里暂时没有数据库方面的专家，不能实现这样的方案，能不能从设计上优化这个问题？

可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。

这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理。

知识点

MySQL有时候会选错索引

如果我们遇到了原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，有三种办法：

1、采用 force index 强行选择一个索引

2、修改语句，引导 MySQL 使用我们期望的索引

3、新建一个更合适的索引，来提供给优化器做选择或删掉误用的索引。

如何给字符串字段加索引

前缀索引

遇到类似邮箱这样的字段，可以使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。在建立前缀索引时需要注意区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。所以前缀索引需要预先设定一个可以接受的损失比。

倒序存储

类似于邮箱这样的字段来说，使用前缀索引的效果可能还不错。但是，遇到前缀的区分度不够好的情况时，比如身份证号，我们就可以用到倒序存储的方法了。存储身份证号的时候把它倒过来存，每次查询的时候反过来查。

由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度。

hash 字段

身份证的字段加索引也可以使用hash字段。在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。每次插入新记录的时候，都同时用 crc32()这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32()函数得到的结果可能是相同的，所以查询语句 where 部分要判断 id_card 的值是否精确相同。

这样，索引的长度变成了 4 个字节，比原来小了很多。

倒序存储和使用 hash 字段这两种方法的异同点

它们的相同点是：

都不支持范围查询。

倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民了。同样地，hash 字段的方式也只能支持等值查询。

它们的区别，主要体现在三个方面：

1、从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。

2、CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数， 而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。

3、从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。 而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

为什么SQL语句有时候会变慢

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为"脏页"。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为"干净页"。

平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔变慢就是因为执行SQL语句的瞬间可能在刷脏页（flush）。以下情况会引发数据库的 flush 过程：

1、InnoDB的redo log写满了，这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间才可以继续写

这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果从监控上看，这时候更新数会跌为 0。

2、系统内存不足当，需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是"脏页"，就要先将脏页写到磁盘。

这种情况其实是常态。InnoDB 用缓冲池(buffer pool)管理内存，缓冲池中的内存页有三种状态：

• 还没使用的
• 使用了并且是干净页
• 使用了并且是脏页

InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉:如果要淘汰的是一个干净页，就直接释放出来复用；如果是脏页就必须将脏页先刷到磁盘，变成干净页后才能复用。

3、MySQL认为系统空闲的时候，进行flush操作

这种情况属于MySQL空闲操作，不会影响系统。

4、MySQL关闭的时候会把内存的脏页都flush到磁盘上

这种情况属于要关闭数据库了，也不会影响系统。

综上所述，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

• 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长
• 日志写满，更新全部堵住，写性能跌为 0

InnoDB刷脏页的控制策略

1、使用innodb_io_capacity 这个参数，它会告诉 InnoDB 你的磁盘能力。这个值应该设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 工具来测试。如果这个参数设置的过低，InnoDB会认为系统能力差，导致刷脏页刷的特别慢从而影响查询和更新的性能。

2、多关注脏页比例，不要经常接近75%。脏页比例通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到

InnoDB刷脏页的连坐机制

一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL 中的一个机制，可能让查询会更慢。在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个邻居也带着一起刷掉。而且这个把邻居拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的 数据页也还是脏页的话，也会被放到一起刷。

在 InnoDB 中，innodb_flush_neighbors参数就是用来控制这个行为的，值为 1 的时候 会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。在 MySQL 8.0 中，innodb_flush_neighbors参数的默认值已经是 0 了。

为什么表数据删除一半文件大小却不变

MySQL中delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

实际上，不止是删除数据会造成空洞，插入数据也会。 如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。当数据页出现了满了的情况，本页末尾数据是500和600，这时插入一个550的数据，就不得不再申请一个页面来保存数据，这就发生了页分裂，550和600会分到新的一页，前一页的500后面就会产生一个空洞。

另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。这也是会造成空洞的。也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，才能达到收缩表空间的目的。

我们可以使用重建表的方法，新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。 表 B 的主键索引更紧凑，数据页的利用率也更高。如果把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。

Online DDL

MySQL 5.6 版本开始引入 Online DDL，为了对之前MySQL 从主表往临时表插入数据的过程不支持主表数据更新做优化。

引入了 Online DDL 之后，重建表的流程：

0. 建立一个临时文件，扫描表 A 主键的所有数据页;
1. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中;
2. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件(row log)中
3. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件
4. 用临时文件替换表 A 的数据文件。

Online 和 inplace的区别

DDL 过程如果是 Online 的，就一定是 inplace 的；inplace 的 DDL，有可能不是 Online 的。

（整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个原地操作，这就是inplace名称的来源）

count的实现原理

在不同的 MySQL 引擎中，count(*)有不同的实现方式。

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*)的时候会直接返回这个数，效率很高
• InnoDB 引擎执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

需要注意的是，如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。

之所以InnoDB 不跟 MyISAM一样把数字存起来，是因为即使是在同一个时刻的多个查询，由于多版本并发控制(MVCC)的原因， InnoDB 表应该返回多少行也是不确定的。

虽然如此，InnoDB对于count(*)操作还是做了优化的。InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

用 show table status 命令输出结果里面也有一个 TABLE_ROWS 能代替 count(*) 吗？

索引统计的值是通过采样来估算的。实际上，TABLE_ROWS 就是从这个采样估算得来的，因此它也很不准。官方文档说误差可能达到 40% 到 50%。所以，show table status 命令显示的行数也不能直接使用。

综上所述总结来说：

• MyISAM 表虽然 count(*)很快，但是不支持事务
• ·show table status· 命令虽然返回很快，但是不准确
• InnoDB 表直接 count(*)会遍历全表，虽然结果准确，但会导致性能问题

如果有需求需要用到count(*)，比如一个页面经常要显示交易系统的操作记录总数，只能自己计数。可以采用以下几种解决方式

1、用缓存系统保存计数

用一个 Redis 服务来保存这个表的总行数。这个表每被插入一行 Redis 计数就加 1，每被删除一行 Redis 计数就减 1。

缺点：缓存系统可能会丢失更新。

如果刚刚在数据表中插入了一行，Redis 中保存的值也加了 1，然后 Redis 异常重启了，重启后要从存储 redis 数据的地方把这个值读回来，而刚刚加 1 的这个计数操作却丢失了，这就必须在每次重启以后，到数据库里面单独执行一次 count(*) 获取真实的行数，再把这个值写回到 Redis 里。

除此之外，Redis也有可能存在不精确的情况。比如说当业务场景需要显示最近操作的 100 条记录。那么，这个页面的逻辑就需要先到 Redis 里面取出计数，再到数据表里面取数据记录。可能存在查到的 100 行结果里面有最新插入记录，而 Redis 的计数里还没加 1。或者，查到的 100 行结果里没有最新插入的记录，而 Redis 的计数里已经加了1。这两种情况，都是逻辑不一致的。

2、在数据库保存计数

用缓存系统保存计数有丢失数据和计数不精确的问题，那么可以把这个计数直接放到数据库里单独的一张计数表 C 中。

首先，这解决了崩溃丢失的问题，InnoDB 是支持崩溃恢复不丢数据的，而且因为支持事务，所以可以保证计数的精准。不同的会话查计数值和“最近 100 条记录”看到的结果，逻辑上是一致的。

不同的 count 用法

select count(?) from t这样的查询语句里面，count(*)、count(主键 id)、count(字段) 和 count(1) 等不同用法的性能有哪些差别？

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数。

而 count(字段)，则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

分析性能差别的时候，记住几个原则：

• server 层要什么就给什么
• InnoDB 只给必要的值
• 现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一 行，放一个数字“1”进去，判断是不可能为空的，按行累加。

count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(字段) 来说:

• 如果这个字段是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加
• 如果这个字段定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。 count(*) 肯定不是 null，按行累加。

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count( * )，所以尽量使用 count(*)。

两阶段提交发生异常重启如何保证数据完整性

图中时刻 A 的地方，写 binlog 之 前，发生了崩溃(crash)，由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。

当时刻B崩溃了，redo log 还没 commit 前发生 crash，那崩溃恢复的时候MySQL 会判断redo log 里面的事务是不是完整的，如果有了 commit 标识，则直接提交。如果事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整，如果是的话提交事务，否则回滚。

MySQL 怎么知道 binlog 是完整的?

一个事务的 binlog 是有完整格式的。statement 格式的 binlog，最后会有 COMMIT。row 格式的 binlog，最后会有一个 XID event。

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

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

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

• 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交;
• 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

处于 prepare 阶段的redo log 加上完整 binlog，重启就能恢复，MySQL为什么要这么设计

在时刻 B，也就是 binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被 从库(或者用这个 binlog 恢复出来的库)使用。所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

如果这样的话，为什么还要两阶段提交呢？干脆先redo log写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑?

两阶段提交是经典的分布式系统问题，并不是 MySQL 独有的。

对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚(如果这还允许回滚，就可能覆盖掉别的事务的更新)。而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了。

两阶段提交就是为了给所有人一个机会，当每个人都说“我 ok”的时候，再一起提交。

不引入两个日志，也就没有两阶段提交的必要了。只用 binlog来 支持崩溃恢复，又能支持归档，不就可以了?

只保留 binlog，然后可以把提交流程改成：... -> 数据更新到内存-> 写 binlog -> 提交事务

binlog 还是不能支持崩溃恢复的，binlog 没有能力恢复“数据页”。

InnoDB 并不是 MySQL 的原生存储引擎。MySQL 的原生引擎是 MyISAM，设计之初就没有支持崩溃恢复。

InnoDB 在作为 MySQL 的插件加入 MySQL 引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。

InnoDB 接入了 MySQL 后，发现既然 binlog 没有崩溃恢复的能力，那就用 InnoDB 原 有的 redo log 好了。

能不能只用redo log，不要binlog

如果只从崩溃恢复的角度来讲是可以的。可以把 binlog 关掉，这样就没有两阶段提交了，但系统依然是 crash-safe 的。

但是 binlog 有着 redo log 无法替代的功能，一个是归档。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用；一个就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。

还有很多公司有异构系统(比如一些数据分析系统)，这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话，这些下游系统就没法输入了。

现在包括 MySQL 高可用在内的很多系统机制都依赖于 binlog。

redo log一般设置多大?

redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样WAL 机制的能力就发挥不出来了。

所以，如果是现在常见的几个 TB 的磁盘的话，直接将 redo log 设置为 4 个文件、每个文件 1GB 。

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

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

1、如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。 最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。

2、在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

redo log buffer 是什么

begin;insert into t1 ...insert into t2 ...commit;

一个事务的更新过程中，日志是要写多次的。一个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。所以，redo log buffer就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。但是真正把日志写到 redo log 文件(文件名是 ib_logfile+ 数字)，是在执行 commit 语句的时候做的。单独执行一个更新语句的时候，InnoDB 会自己启动一个事务，在语句执行完成的时候提交。过程跟上面是一样的，只不过是“压缩”到了一个语句里面完成。

order by 的工作原理

order by分为全字段排序和rowid 排序

全字段排序

举例

select city,name age from T where city='杭州' order by name limit 1000;

SQL 查询语句的执行过程如下：

0. 初始化 sort_buffer，确定放入 name、city、age 这三个字段
1. 从索引 city 找到第一个满足 city='杭州’条件的主键 id
2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中
3. 从索引 city 取下一个记录的主键 id
4. 重复步骤 3、4 直到 city 的值不满足查询条件为止
5. 对 sort_buffer 中的数据按照字段 name 做快速排序
6. 按照排序结果取前 1000 行返回给客户端

"按 name 排序"这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。

sort_buffer_size是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

确定一个排序语句是否使用了临时文件。是通过查看 OPTIMIZER_TRACE的结果来确认的，可以从 number_of_tmp_files中看到是否使用了临时文件。它表示的是，排序过程中使用的临时文件数。MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。如果 sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是 0， 表示排序可以直接在内存中完成。

否则就需要放在临时文件中排序。sort_buffer_size越小，需要分成的份数越多， number_of_tmp_files的值就越大。

rowid 排序

全字段排序只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。

这个算法有一个问题，就是如果查询要返回的字段很多的话，那么sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。所以如果单行很大，这个方法效率不够好。

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

city、name、age 这三个字段的定义总长度是 36，把 max_length_for_sort_data设置为 16，新的算法放入 sort_buffer 的字段，只有要排序的列(即 name 字段)和主键 id。但这时，排序的结果就因为少了 city 和 age 字段的值，不能直接返回了，整个执行流程 就变成如下所示的样子：

0. 初始化 sort_buffer，确定放入两个字段，即 name 和 id

1. 从索引 city 找到第一个满足 city='杭州’条件的主键 id

2. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中;

3. 从索引 city 取下一个记录的主键 id;

4. 重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y;

5. 对 sort_buffer 中的数据按照字段 name 进行排序;

6. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三

   个字段返回给客户端。

这个执行流程称为 rowid 排序。对比全字段排序流程会发现，rowid 排序多访问了一次表 t 的主键索引，就是步骤 7。

全字段排序 VS rowid排序

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

如何正确地显示随机信息

1、避免使用order by rand()

因为order by rand()这个语句需要 Using temporary 和 Using filesort， 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。这种写法都会让计算过程非常复杂，需要大量的扫描行数，因此排序过程的资源消耗也会很大。

2、应该采取自己设计的随机算法

比如方法一：

• 取得这个表的主键 id 的最大值 M 和最小值 N
• 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N
• 取不小于 X 的第一个 ID 的行。

方法二：

• 取得整个表的行数，并记为 C
• 取得 Y = floor(C * rand()) floor 函数在这里的作用，就是取整数部分
• 再用 limit Y,1 取得一行

方法三：

• 取得整个表的行数，记为 C
• 根据相同的随机方法得到 Y1、Y2、Y3
• 再执行三个limit Y, 1语句得到三行数据。

SQL调优

条件字段函数操作

举例：where t_modified='2018-7-1’的时候可以用上索引，改成 where month(t_modified)=7的时候不行。

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

隐式类型转换

select * from tradelog where tradeid=110717;

交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描。

tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。

对于优化器来说，这个语句相当于：

select * from tradelog where CAST(tradid AS signed int) = 110717;

这条语句触发了对索引字段做函数操作，优化器会放弃走树搜索功能。

所以，尽量避免隐式类型转换。

隐式字符编码转换

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

在这个执行计划里，是从tradelog表中取 tradeid字段，再去trade_detail表里查询匹配字段。因此，把 tradelog称为驱动表，把trade_detail称为被驱动表，把 tradeid称为关联字段。

当两张表的字符集不同，比如一个是utf8，一个是utf8mb4，那么它们做表连接查询的时候用不上关联字段的索引。因为上面的SQL相当于执行了select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;，这条语句触发了对索引字段做函数操作，优化器会放弃走树搜索功能。

连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

查询慢的几种场景

查询长时间不返回

例如这样的SQLselect * from t where id=1;查询长时间不返回

一般碰到这种情况的话，大概率是表 t 被锁住了。分析原因的时候，一般都是先执行一下show processlist命令，看看当前语句处于什么状态。

• 等MDL锁

出现这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。

这类问题的处理方式，就是找到谁持有 MDL 写锁，然后把它 kill 掉。通过查询 sys.schema_table_lock_waits这张表，就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

select blocking_pid from sys.schema_table_lock_waits;

• 等flush

在表 t 上，执行SQL 语句：select * from information_schema.processlist where id=1;

查出来这个线程的状态是Waiting for table flush这个状态表示的是，现在有一个线程正要对表 t 做 flush 操作。MySQL 里面对表做 flush 操作的用法，一般有以下两个：

1、flush tables t with read lock;

2、flush tables with read lock;

这两个 flush 语句，如果指定表 t 的话，代表的是只关闭表 t。如果没有指定具体的表名， 则表示关闭 MySQL 里所有打开的表。但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了。所以，出现 Waiting for table flush 状态的可能情况是:有一个 flush tables 命令被别的语句堵住了，然后它又堵住了select 语句。

• 等行锁

select * from t where id=1 lock in share mode;由于访问 id=1 这个记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个 写锁，select 语句就会被堵住。

可以通过 sys.innodb_lock_waits表查到是谁占着这个写锁然后kill掉就行了

"饮鸩止渴"提高性能的方法

短连接风暴

正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示Too many connections。

碰到这种情况时，如果调高max_connections的值是有风险的。因为设计max_connections这个参数的目的是想保护 MySQL，如果我们把它改得太大，让更多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行 业务的 SQL 请求。

解决短连接风暴有两种方法：

1、先处理占着连接但是不工作的线程

连接数过多，可以优先断开事务外空闲太久的连接;如果这样还不够，再考虑断开事务内空闲太久的连接。

从服务端断开连接使用的是 kill connection + id的命令， 一个客户端处于 sleep 状态 时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一 个请求的时候，才会收到报错。

2、减少连接过程的消耗

有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。

跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。

这种方法特别饮鸩止渴，风险极高。

慢查询性能问题

在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：

• 索引没有设计好

这种场景一般就是通过紧急创建索引来解决。MySQL 5.6 版本以后，创建索引都支持 Online DDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。

比较理想的是能够在备库先执行。假设现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：

0. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行alter table语句加上索引
1. 执行主备切换
2. 这时候主库是 B，备库是 A。在 A 上执行set sql_log_bin=off，然后执行alter table语句加上索引

在需要紧急处理时，这个方案的效率是最高的。

• SQL 语句没写好

这种情况可以通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。

• MySQL 选错了索引

这时候，应急方案就是给这个语句加上 force index，使用查询重写功能，给原来的语句加上 force index，也可以解决这个问题。

以上三种情况可以通过下面的过程预先发现问题：

0. 上线前，在测试环境，把慢查询日志(slow log)打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志
1. 在测试表里插入模拟线上的数据，做一遍回归测试
2. 观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致

QPS突增问题

有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。

0. 一种是由全新业务的 bug 导致的。假设 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
1. 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。
2. 如果这个新增的功能跟主体功能是部署在一起的，那么只能通过处理语句来限制。 这时，可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。

这个操作的风险很高，需要特别细致。它可能存在两个副作用：

0. 如果别的功能里面也用到了这个 SQL 语句模板，会有误伤
1. 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败。

MySQL是如何保证数据不丢的

binlog的写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把 binlog cache写到 binlog 文件中。一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。

系统给binlog cache分配了一片内存，每个线程一个，参数binlog_cache_size 用于控制单个线程内binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把 binlog cache里的完整事务写入到 binlog 中，并清空 binlog cache。状态如下图 所示。

每个线程有自己 binlog cache，但是共用同一份 binlog 文件。图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。

图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下认为 fsync 才占磁盘的 IOPS。

write 和 fsync 的时机，是由参数sync_binlog 控制的:

• sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync;
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync;
• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才fsync。

在出现 IO 瓶颈的场景里，将 sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0， 比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

redo log 的写入机制

redo log中有一个redo log buffer。事务在执行过程中redo log是要先写到redo log buffer的。redo log buffer 里面的内容不是每次生成后都要直接持久化到磁盘的。如果事务执行期间 MySQL 发生异常重启，那这部分日志就丢了。由于事务并没有提交， 所以这时日志丢了也不会有损失。

日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。

为了控制 redo log 的写入策略，InnoDB 提供了innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

• 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中
• 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘
• 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也 是可能已经持久化到磁盘的。实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。

• redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘
• 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘

两阶段提交时序上 redo log 先 prepare， 再写 binlog，最后再把 redo log commit。如果把innodb_flush_log_at_trx_commit设置成 1，那么 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了，只会 write 到文件系统的 page cache 中就够了。

如果 MySQL 现在出现了性能瓶颈，而且瓶颈 在 IO 上，可以通过哪些方法来提升性能

0. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count参数，减少 binlog 的写盘次数。这个方法是基于额外的故意等待来实现的，因此可能会增加语句的响应时间，但没有丢 失数据的风险。
1. 将 sync_binlog设置为大于 1 的值(比较常见是 100~1000)。这样做的风险是，主机掉电时会丢 binlog 日志。
2. 将innodb_flush_log_at_trx_commit设置为 2。这样做的风险是，主机掉电的时候会丢数据。

MySQL是怎么保证主备一致的

主备的基本原理

下图是MySQL的主备切换流程

在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行。这样可以保持节点 B 和 A 的数据是相同的。当需要切换的时候，就切成状态 2。这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。

在状态 1 中，虽然节点 B 没有被直接访问，但是依然应该把节点B设置成只读模式。这样做，有以下几个考虑：

• 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作
• 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致
• 可以用 readonly 状态，来判断节点的角色。

虽然是只读模式，但是还是可以跟主库保持同步更新。因为readonly 设置对超级权限用户是无效的，而用于同步更新的线程，就拥有超级权限。

下图是一个update语句在节点A执行，然后同步到节点B的完整流程图

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

0. 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
1. 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
2. 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog， 发给 B。
3. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志(relay log)。
4. sql_thread 读取中转日志，解析出日志里的命令，并执行。

binlog 里面到底是什么内容，为什么备库拿过去可以直接执行？

binlog 有三种格式，

• statement

  该格式下每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。它相比row模式能节约很多性能与日志量，具体节约的多少取决于应用的SQL情况。正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，考虑到整表删除等一些大量数据操作，row格式会产生大量日志，所以总体来讲Statement模式会稍微好一些。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。

• row

此格式不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row格式的日志内容会非常清楚的记录下每一行数据修改的细节。

缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句或者一条alter语句，修改多条记录，则binlog中每一条修改都会有记录，每条记录都发生改变，那么该表每一条记录都会记录到日志中，这样造成binlog日志量会很大。

• mixed

该格式是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，当statement无法完成主从复制的操作时(设计一些函数时)，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row模式也被做了优化，并不是所有的修改都会以row模式来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

循环复制问题

binlog 的特性确保了在备库执行相同的 binlog，可以得到与主库相同的状态，所以正常情况下主备的数据是一致的。

如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。这个 要怎么解决呢?

MySQL 在 binlog 中记录了这个命令第一次执行时所在实例 的 server id。因此，可以用下面的逻辑，来解决两个节点间的循环复制的问题：

0. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系
1. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的binlog
2. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

按照这个逻辑，如果设置了双 M 结构，日志的执行流就会变成这样：

0. 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id
1. 传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id
2. 再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

MySQL是如何保证高可用的

主备延迟

0. 主库 A 执行完成一个事务，写入 binlog，把这个时刻记为 T1
1. 之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2
2. 备库 B 执行完成这个事务，把这个时刻记为 T3。

主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。

可以在备库上执行 show slave status命令，它的返回结果里面会显示seconds_behind_master，用于表示当前备库延迟了多少秒。

seconds_behind_master的计算方法是这样的：

• 每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间
• 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到seconds_behind_master

这个参数计算的就是 T3-T1。所以可以用 seconds_behind_master来作为主备延迟的值，这个值的时间精度是秒

可靠性优先策略

在双 M 结构下，从主切换到备的详细过程是这样的：

0. 判断备库 B 现在的 seconds_behind_master，如果小于某个值(比如 5 秒)继续下一 步，否则持续重试这一步
1. 把主库 A 改成只读状态，即把 readonly 设置为 true
2. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止
3. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false
4. 把业务请求切到备库 B

这个切换流程一般是由专门的 HA 系统来完成的，称之为可靠性优先流程。

这个切换流程中是有不可用时间的。因为在步骤 2 之后，主库 A 和备库 B 都 处于 readonly 状态，也就是说这时系统处于不可写状态，直到步骤 5 完成后才能恢复。在这个不可用状态中，比较耗费时间的是步骤 3，可能需要耗费好几秒的时间。这也是为什么需要在步骤 1 先做判断，确保 seconds_behind_master 的值足够小。

可用性优先策略

根据上面的切换流程顺序强行把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了。

这个切换流程，称作可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。

下图是可用性优先策略，且 binlog_format=mixed时的切换流程和数据结果。

0. 步骤 2 中，主库 A 执行完 insert 语句，插入了一行数据(4,4)，之后开始进行主备切换
1. 步骤 3 中，由于主备之间有 5 秒的延迟，所以备库 B 还没来得及应用插入 c=4这 个中转日志，就开始接收客户端插入 c=5的命令
2. 步骤 4 中，备库 B 插入了一行数据(4,5)，并且把这个 binlog 发给主库 A
3. 步骤 5 中，备库 B 执行插入 c=4这个中转日志，插入了一行数据(5,4)。而直接在备库 B 执行的插入 c=5这个语句，传到主库 A，就插入了一行新数据(5,5)

最后的结果就是，主库 A 和备库 B 上出现了两行不一致的数据。这个数据不一致，是由可用性优先流程导致的。

如果还是用可用性优先策略，但设置 binlog_format=row

因为 row 格式在记录 binlog 的时候，会记录新插入的行的所有字段值，所以最后只会有一行不一致。而且，两边的主备同步的应用线程会报错 duplicate key error 并停止。也就 是说，这种情况下，备库 B 的 (5,4) 和主库 A 的 (5,5) 这两行数据，都不会被对方执行。

0. 使用 row 格式的 binlog 时，数据不一致的问题更容易被发现。而使用 mixed 或者 statement 格式的 binlog 时，数据很可能悄悄地就不一致了。如果过了很久才发现数据不一致的问题，很可能这时的数据不一致已经不可查，或者连带造成了更多的数据逻辑不一致。
1. 主备切换的可用性优先策略会导致数据不一致。

因此，大多数情况下，都建议使用可靠性优先策略。对数据服务来说的话，数据的可靠性一般还是要优于可用性的。

有没有哪种情况数据的可用性优先级更高？

有一个库的作用是记录操作日志。这时候，如果数据不一致可以通过 binlog 来修补， 而这个短暂的不一致也不会引发业务问题。 同时，业务系统依赖于这个日志写入逻辑，如果这个库不可写，会导致线上的业务操作无法执行。

这时候就需要选择先强行切换，事后再补数据的策略。

总结：

在满足数据可靠性的前提下，MySQL 高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。

join的使用

Index Nested-Loop Join

0. 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好
1. 如果使用 join 语句的话，需要让小表做驱动表

Simple Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表 t2 的字段 b 上没有索引，因此再用图 2 的执行流程时，每次到 t2 去匹配的时候， 就要做一次全表扫描。

这个算法看上去太“笨重”了。

Block Nested-Loop Join

被驱动表上没有可用的索引，算法的流程是这样的：

0. 把表 t1 的数据读入线程内存 join_buffer 中，由于这个语句中写的是 select *，因此是把整个表 t1 放入了内存;
1. 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

如果使用 Simple Nested-Loop Join 算法进行查询，扫描行数也是10万行。从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join 算法的这 10 万次判断是内存操作，速度上会快很多，性能也更好。

能不能使用 join 语句?

0. 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引， 是没问题的
1. 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

• 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表

• 如果是 Block Nested-Loop Join 算法

  • 在 join_buffer_size 足够大的时候，是一样的
  • 在 join_buffer_size 不够大的时候(这种情况更常见)，应该选择小表做驱动表

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤， 过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。