MySQL面试前准备，看这一篇就够了

一、执行篇

1.1 说一下mysql一条select语句的执行流程

如果是主键：

1）通过聚集b+树查找第一个id为10的数据，

2）将数据作为结果集返回给客户端。

如果不是主键/但是有索引（还有回表）

1）通过该索引的树查找第一个判断值是否相等，如果不是则跳过，如果是则将这行存在结果集中；

2）调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3）执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

如果不是主键/也没有索引

1）调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；

2)调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3）执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

1.2、说一下update 语句时的内部流程（两阶段提交，脏页）

> 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）状态，更新完成。

redoLog 和binlog两个日志也是用来保证crash-safe能力（即使mysql服务宕机，也不会丢失数据的能力）

只有两份日记才有crash-safe功能为什么binlog不能做到crash-safe？假如只有binlog，有可能先提交事务再写binlog，有可能事务提交数据更新之后数据库崩了，还没来得及写binlog。我们都知道binlog一般用来做数据库的主从复制或恢复数据库，这样就导致主从数据库不一致或者无法恢复数据库了。同样即使先写binlog再提交事务更新数据库，还是有可能写binlog成功之后数据库崩掉而导致数据库更新失败，这样也会导致主从数据库不一致或者无法恢复数据库。所以只有binlog做不到crash-safe。为了支持crash-safe，需要redolog，而且为了保证逻辑一致，事务提交需要两个阶段：prepare阶段和commit阶段。写redolog并落入磁盘(prepare状态)-->写binlog-->commit。commit的时候是不会

二、索引篇

2.1 索引数据结构

每一个索引在 InnoDB 里面对应一棵 B+ 树

二叉树：缺点：特殊情况在查找记录时跟没加索引的情况是一样（key：所要查询的值 value：指针）
红黑树：缺点：在数据量大的时候，深度也很大树的高度不可控高度过高导致查询性能不快
Hash表：对key hsah得到指针位置（指针位置和hash值有个映射关系等值可以快速定位），缺点：难以支持范围查找也不适合模糊查询
B- tree:特点：1.非叶子结点与叶子结点（每个关键字都保存数---指向数据的指针）2.任何一个关键字出现且只出现在一个结点中
B 树和 B+ 都是通过多又树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。但是 MySQL 默认的存储引擎 InnoD8 采用的是 B+ 作为索引的数据结构，原因有:

B+树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比B树更「矮胖」，查询底层节点的磁盘 I/0次数会更少。
B+树有大量的冗余节点(所有非叶子节点都是冗余索引，只存放索引)，这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B树那样会发生复杂的树的变化;
B+树叶子节点之间用链表连接了起来，有利于范围查询，而B树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 /0 操作，范围查询效率不如 B+ 树。

2.2 数据页

2.2.1概念：

InnoDB 的数据是按「数据页」为单位来读写的，数据页的默认大小是 16KB，页在磁盘中不一定是连续的，是逻辑连续，数据页中的记录按照「主键」顺序组成单向链表，数据页中有一个页目录，起到记录的索引作用

页目录就是由多个槽组成的，槽相当于分组记录的索引。通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽(哪个记录分组)，定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。

查询数据：从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，定位到该页后，又会在该页内进行二分法快速定位记录所在的分组(槽号)，最后在分组内进行遍历查找。

2.2.2 页分裂（可能导致数据页有空洞）

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。而更糟的情况是，

如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响

2.3 聚簇索引和二级索引(普通索引)

聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点
二级索引的叶子节点存放的是主键值，而不是实际数据。

InnoDB主键索引查找流程：通过.ibd文件找到对应的索引，索引的value即为那行对应的完整数据。

InnoDB二级索引查找流程：通过.ibd文件找到对应的索引，索引的value即为那行对应的主键的值，再根据主键值去主键索引树中找到对应的行数据。这个过程叫做「回表」

2.4 索引失效的情况

当使用右或者左右模糊匹配的时候，也就是1ike%xx或者 1ike %xx% 这两种方式都会造成索引失效;
当在查询条件中对索引列使用函数，就会导致索引失效。
当在查询条件中对索引列进行表达式计算，也是无法走索引的。
MVSQL在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR后的条件列不是索引列，那么索引会失效。

2.5 索引：业务属性唯一可以设置为主键么

不用业务字段做主键： 1. 业务字段不一定是递增的，有可能会造成主键索引的页分裂，导致性能不稳定。 2. 二级索引存储的值是主键，如果使用业务字段占用大小不好控制，如果业务字段过长可能会导致二级索引占用空间过大，利用率不高。（显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。）

有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：只有一个索引（没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题）；该索引必须是唯一索引。

2.6 联合索引的技巧

1、覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据

2、最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符

3、联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。

4、索引下推：like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度

2.7、普通索引和唯一索引应该如何选择（关键点：change buffer）

2.7.1 明确概念：

（1）mysql数据存储在主键索引树的叶子节点。

（2）普通索引和唯一索引也都有自己的索引树，树的叶子节点存储的是主键ID。

（3）做更新操作（插入，更新，删除）会同时更新所有的索引树结构。

insert：主键索引树和唯一建索引树的肯定都要更新，肯定是无法用到change buffer的（因为需要读取整个页判断是否唯一）；但是普通索引树的插入，是可以使用change buffer的。

update：只要涉及到相关字段更新，就要同时更新相应的索引树。道理同上。【显然，insert操作的影响更大，如果有多个唯一索引，insert对内存命中率会有极大影响】

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

change buffer的好处：1、减少读磁盘：仅仅是减少的是对二级普通索引页的读磁盘操作，而对于其他类型的页(唯一索引，主键索引)还是要读磁盘的。 2、减少内存占用：change buffer虽然还是需要内存占用(记录数据更新动作)，但相比于数据页来说(默认16K)，所占的内存还是小了很多的。

当需要更新一个数据页时，如果数据页在内存中就直接更新(数据页读入内存是需要占用 buffer pool )，而如果不在内存中，InnoDB 会将这些更新操作缓存在 change buffer 中（也是有个写入磁盘的操作，但是不需要读），这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，将 change buffer 中的操作merge到原数据页

除了第一次查询访问（也有定时更新merge,数据库关闭前也会merge）

merge 的执行流程是这样的：

从磁盘读入数据页到内存（老版本的数据页）；
从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；写 redo log。
这个 redo log 包含了数据的变更和 change buffer 的变更。

到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

2.7.2 redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

1.唯一索引，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

2.普通索引，则是将更新记录在 change buffer，语句执行就结束了。

2.7.3总结：

1、changebuffer跟普通数据页一样也是存在磁盘里，区别在于changebuffer是在共享表空间ibdata1里

2、redolog有两种，一种记录普通数据页的改动，一种记录changebuffer的改动

3、只要内存里脏页（innodb buffer pool）里的数据发生了变化，就一定会记录2中前一种redolog （对数据的修改记录在changebuffer里的时候，内存里是没有这个物理页的，不存在脏页）

4、真正对磁盘数据页的修改是通过将内存里脏页的数据刷回磁盘来完成的，而不是根据redolog

change buffer 丢失可不是小事儿，再从磁盘读入数据可就没有了 merge 过程，就等于是数据丢失了。会不会出现这种情况呢

答案：

1.change buffer有一部分在内存有一部分在ibdata. 做purge操作,会把change buffer里相应的数据持久化到ibdata

2.redo log里记录了数据页的修改以及change buffer新写入的信息如果掉电,持久化的change buffer数据已经purge,不用恢复。主要分析没有持久化的数据情况又分为以下几种:

(1)change buffer写入,redo log虽然做了fsync但未commit,binlog未fsync到磁盘,这部分数据丢失（后发现没有commit，回滚）

(2)change buffer写入,redo log写入但没有commit, binlog以及fsync到磁盘, 先从binlog恢复redo log,再从redo log恢复change buffer

(3)change buffer写入,redo log和binlog都已经fsync.那么直接从redo log里恢复。

三、日志篇

3.1 定义:

undo log(回滚日志):是Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
redo log(重做日志):是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复;
binlog (归档日志):是 Server 层生成的日志，主要用于数据备份和主从复制;

3.2 redo log和undo log区别：

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值;
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值;

3.3 redoLog 和binlog两者区别在于：

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

3.4 为什么需要 redo log :

实现事务的持久性，让 MySQL有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失;
将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

3.5 为什么还需要binlog

redolog只有InnoDB有，别的引擎没有。
redolog是循环写的，不持久保存，binlog的“归档”这个功能，redolog是不具备的。

3.6 InnoDB 引擎使用的是 WAL 技术，WAL：预写日志机制

WAL 机制主要得益于两个方面：

redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

执行流程：

1. MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

2. buffer pool中可能缓存了innodb经常访问的一些聚簇索引数据页与二级索引数据页，暂且称为缓存页，缓存页的数据版本是大于等于磁盘里面的数据版本的。

3. 查询的时候会先看缓存中是否有这个数据页的相关信息，若有的话，根据mvvc多版本模式获取到当前事务版本的数据

4. 更新的时候会查看这个记录所对应的数据页是否在缓存中，如果在直接更新缓存，rodo log 记录数据页的变动)

5. 更新的时候如果没有在内存中，若更新的字段并没有涉及到唯一索引，则不需要进行查询判断，把更新记录到change buffer中，redo log记录change buffer数据页的变动记录。change buffer 需要持久化落盘，因为redo log 应用change buffer 变动记录的时候需要找到对应的change buffer 记录

6. 如果是插入操作，涉及的字段如果没有唯一索引，则直接在change buffer中记录数据页的变动.

3.7 为什么需要两阶段提交

1 prepare阶段 2 写binlog 3 commit

当在2之前崩溃时

重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。一致

当在3之前崩溃

重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；

如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务。

追问1：MySQL 怎么知道 binlog 是完整的?回答：一个事务的 binlog 是有完整格式的：

回答：一个事务的 binlog 是有完整格式的，还有一个 binlog-checksum 参数，可以通过校验 checksum 的结果来验证事务 binlog 的完整性的。

追问 2：redo log 和 binlog 是怎么关联起来的?

回答：它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

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

回答：redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页

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

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

追问 4：redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

回答：redo log 会在事务提交的时候再写入到文件中，之前是『一直』存在于 redo log buffer 之中的，因此要避免长事务，减缓 redo log buffer 占满

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

回答： binlog一来时机控制不好（MySQL写数据是写在内存里的，不保证落盘，如图，所以commit1的数据也可能丢失；但是恢复只恢复binlog失败的也就是commit2的数据，所以数据会丢失），二来内容的能力不足（没有页面信息）数据的落盘是以page为最小单位，而redo log记录的维度也是page。而对于binglog，记录的维度太粗，是以单条语句的维度记录的。

仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1.先写 redo log 后写 binlog。（数据库可以恢复到半个月内任意一秒的状态--依靠的就是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，与原库的值不同。

3.8、双1配置

binlog 和 redo log 在内存中都对应的缓存空间，binlog 会缓存在 binlog cache，redo log 会缓存在 redolog buffer，它们持久化到磁盘的时机分别由下面这两个参数控制。一般设置为 1:

当 sync_binlog =1的时候，表示每次提交事务都会将 binlog cache 里的 binlog 直接持久到磁盘;
当 innodb _flush_log_at_ trx_commit =1时，表示每次事务提交时，都将缓存在 redo log buffer 里的redo log 直接持久化到磁盘:

可以看到，如果 sync_binlog 和当 innodb _fush_log_at_trx_commit 都设置为1，那么在每个事务提交过程中，都会至少调用2次刷盘操作，一次是 redo log 刷盘，一次是 binlog 落盘，所以这会成为性能瓶颈。

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。

redolog写入逻辑：

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 持久化到磁盘。

3.9 组提交

binlog 组提交(group commit)机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 IO的次数，如果说 10 个事务依次排队刷盘的时间成本是 10，那么将这10 个事务一次性一起刷盘的时间成本则近似于 1。

引入了组提交机制后，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分为三个过程

flush 阶段:多个事务按进入的顺序将 binlog 从 cache 写入文件(不刷盘);
sync 阶段:对 binlog 文件做 fsync 操作(多个事务的 binlog 合并一次刷盘):
commit 阶段:各个事务按顺序做InnoDB commit 操作;

redelog 组提交(group commit)机制

四、内存

4.1 buff_pool

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页(该页的内存数据和磁盘上的数据已经不一致)，为了减少磁盘!/0，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

4.2.刷盘

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

刷盘的速度：根据 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力，然后计算刷盘速度

而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用

刷盘时机：

1.InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写checkpoint 可不是随便往前修改一下位置就可以的。比如图中，把 checkpoint 位置从 CP 推进到 CP’，就需要将两个点之间的日志（浅绿色部分），对应的所有脏页都 flush 到磁盘上。之后，图中从 write pos 到 CP’之间就是可以再写入的 redo log 的区域

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

3.MySQL 认为系统“空闲”的时候。

4.MySQL 正常关闭的情况

4.3 缓存淘汰机制（lru算法改革）

lru算法是基于链表实现的,如果我们对一个冷数据表进行全表扫描，比如说日志表，这些不是正常用户访问的表，那么在bufferpool中就会大量存在这些数据的表，那么就会导致用户正常访问存放的业务数据会被淘汰掉，这个过程被称为 Buffer Pool 污染。

改进了 LRU 算法，将 LRU 划分了 2 个区域：old 区域和 young 区域。预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。

举例：

图 7 中状态 1，要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成状态 2。
之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。
处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：

若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；
如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。

一个数据页有多条记录会被访问，所以这数据页会被多次访问到，但是由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过一秒，所以就会一直在old区域，在继续扫描后面的数据页，之前的这个数据页也不会被访问到，因此就会一直在old区域，也就很快就会被淘汰掉了

五、事务

5.1 特性

原子性，一致性，隔离性，持久性

5.2、什么是脏读幻读不可重复读

脏读：脏读是指一个事务中访问到了另外一个事务未提交的数据

幻读：一个事务读取2次，得到的记录条数不一致（幻读仅专指“新插入的行”）

不可重复读：一个事务读取同一条记录2次，得到的结果不一致

5.3、Mysql隔离级别，分别有什么问题(innodb默认隔离级别是RR)

读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。可以脏读，幻读，不可重复读
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。解决了脏读，可以幻读，不可重复读
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。解决了脏读，可以重复读，可以幻读（rr级别下才会有mvcc）
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。以上都解决

5.4 快照读和当前读。

针对快照读(普通 select 语句)，是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读(select. . for update 等语句)，是通过 next-key lock(记录锁+间隙锁)方式解决了幻读，因为当执行 select.. for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-keylock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

5.5、MVCC

mvcc+乐观锁解决读写冲突 mvcc+悲观锁解决写写冲突

「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
[可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View,这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对，通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）通过readview+undolog实现mvcc。

每行记录隐藏版本号，事务id,回滚指针，每行数据也都是有多个版本的。每次事务更新数据x的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到

undolog存储（定时删除早记录）

一个事务要查看一条数据时,必须先用该事务的up_limit_id与该行的transaction id做比对, 如果up_limit_id>=transaction id,那么可以看.如果up_limit_id<transaction id,则只能去undo log里去取。去undo log查找数据的时候,也需要做比对,必须up_limit_id>transaction id,才返回数据

5.6、innodb怎么解决幻读

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

间隙锁是在可重复读rr隔离级别下才会生效

六、锁

6.1 两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

6.2Next-Key Lock

是行锁 + 间隙锁的组合

6.3间隙锁带来的问题（间隙锁不互斥）

跟行锁有冲突关系的是“另外一个行锁”，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

影响并发度，容易产生死锁

6.4 怎么解决更新冲突

加事务，加排它锁，select也加排它锁

6.5加锁规则

两个“原则”、两个“优化”和一个“bug”

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；

6.6 如何避免死锁

设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。
开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行

七、主从备份

7.1 主从复制

MySQL 集群的主从复制过程梳理成3个阶段:

写入 Binlog:主库写 binlog 日志，提交事务，并更新本地存储数据。

同步 Binlog:把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。

回放 Binlog:回放 binlog，并更新存储引擎中的数据。

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

1.在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。

位置改成GTID（从库会发给主库自己的GTID集合，主库会筛选出差集，然后发给从库）

2.在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。

3.主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。

4.备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。

5.sql_thread （多线程）读取中转日志，解析出日志里的命令，并执行。

sql_thread从单线程复制到最新版本的多线程复制（按表分发，按行分发）必须同一个事务下

按行分发：通过“库名 + 表名 + 索引名 + 值”计算出来hash值，写入到binlog里

7.2 biglong的三种格式

1. statement 记录原文（主备索引可能不一样）

由于 statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。因此，MySQL 认为这样写是有风险的

2.row 定义的行为（很占空间），好处是可以恢复数据

3.mixed MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式

7.3 双M结构（互为主备）

1.从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；

2.传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；

3.再传回给节点 A， A 判断到这个 server id 与自己的相同，就不会再处理这个日志。

7.4 主备延迟

1.备库所在机器的性能要比主库所在的机器性能差（解决方案：对称部署）

2.备库的压力大（解决方案：一主多从）

3.大事务-主库上必须等事务执行完成才会写入 binlog（如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟。）

4.备库的并行复制能力

7.5 主备切换（有不同策略）

seconds_behind_master：表示slave上SQL thread与IO thread之间的延迟，如果为0，表示主从一致

1.可靠性优先策略---

1.判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；

2.把主库 A 改成只读状态，即把 readonly 设置为 true；

3.判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；

4.把备库 B 改成可读写状态，也就是把 readonly 设置为 false；

5.把业务请求切到备库 B。

步骤2完成后，就是不可用时间，如果延迟越大，不可用时间越长

2.可用性优先策略

把上面步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了。但是可能出现数据不一致的情况。

建议使用可靠性优先的策略。毕竟保证数据准确，应该是数据库服务的底线。在这个基础上，通过减少主备延迟，提升系统的可用性

7.6 主备切换流程

GTID：全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识

GTID=server_uuid:gno （ server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值， gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1）

change master to master_auto_position=1（使用GTID协议）

举个例子：备库 B 要设置为新主库 A’的从库

系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例 B 需要的日志已经不存在，A’就拒绝把日志发给 B。

这跟基于位点的主备协议不同。基于位点的协议，是由备库决定的，备库指定哪个位点，主库就发哪个位点，不做日志的完整性判断。

1.实例 B 指定主库 A’，基于主备协议建立连接。

2.实例 B 把 set_b 发给主库 A’。

3.实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GTID 的集合，判断 A’本地是否包含了这个差集需要的所有 binlog 事务。

a. 如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误；

b. 如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B；

4.之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行。

7.7 主备问题

1. binlog数据传输前，主库宕机，导致提交了的事务数据丢失。

解：使用半同步复制，（一主多从的情况下，只要有一个从库返回ack就可以确认事务完成）

1）事务提交的时候，主库把 binlog 发给从库；2）从库收到 binlog 以后，发回给主库一个 ack，表示收到了；3）主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

2. 一主多从，即使采用半同步，也只能保证binlog至少在两台机器上，没有一个机制能够选出拥有最完整binlog的从库作为新的主库。

3.. 互为主备，如果互为主备的两个实例全部宕机，mysql直接不可用。

7.8 主从延迟导致从库过期读如何解决

1.强制查主库(主库压力大)

2.查之前sleep（可能还会出现过期读，或者等待时间过久）

3.GUID方案：trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；选定一个从库执行查询语句；在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；如果返回值是 0，则在这个从库执行查询语句；否则，到主库执行查询语句

八、其他

8.1、InnoDB MyISAM区别

MyISAM 引擎就不支持行锁，InnoDB 是支持行锁的

redolog只有InnoDB有，别的引擎没有

InnoDB支持事务，MyISAM不支持

MyISAM叶子节点存放的数据的指针，InnoDB是整行数据

8.2、查看SQL执行时间

1.数据库要开启profile（默认不开）set profiling=1

2.show profiles （返回所有sql 执行时间）

show profile for query 1（第一个sql的执行时间 1 queryId）

3.还可以查看出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等

show profile cpu, block io, memory,swaps,context switches,source for query 1

4.关闭参数：set profiling=0

8.3、为什么表数据删掉一半，表文件大小不变？

delete 命令其实只是把记录的位置或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的，

要删掉某条记录，InnoDB 引擎只会把这个记录标记为删除。如果之后要再插入一个 ID 在它前面和后面之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

不止是删除数据会造成空洞，插入数据也会。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。如图

可以使用alter table A engine=InnoDB 命令来重建表（相当于新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中，然后再用表 B 替换

缺点：临时表插入数据时，如果有数据写入，会造成数据丢失，所以必须不是online）

Online ddl:就是把数据写入写到一个缓存log里，在操作到表B，数据一致

8.4、主键ID用完了 rowid用完了怎么办

在 InnoDB 表中主动创建自增主键。因为，表自增 id 到达上限后，再插入数据时报主键冲突错误，是更能被接受的。可靠性优先于可用性

表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
row_id （系统自增id）达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的 bug，好在留给我们的时间还很充裕。
thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了

8.5 count(*)这么慢

对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历
对于 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(*)。

8.6 排序（如果内存够，就要多利用内存，尽量减少磁盘访问）

举例：select city,name,age from t where city='杭州' order by name limit 1000 ;“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序（归并算法排序）

全字段排序

有 4000 条满足 city='杭州’的记录，可以看到 examined_rows=4000，表示参与排序的行数是 4000 行，select @b-@a 的结果会显示为 4001