目录
InnoDb 用 Buffer Pool 缓冲池来管理内存，使用了 LRU 算法，它做了哪些优化？
redo log 与 binlog 如何保证数据的可靠性？redo log 与 binlog 的详细执行过程？
请说下你对 MySQL 架构的了解
大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层
Server 层包括连接器、查询缓存、分析器、优化器以及执行器，其中查询缓存在 MySQL 8.0 版本开始就不再有了。
- 连接器：负责管理连接和权限验证
- 查询缓存：如果查询的语句命中了查询缓存则直接返回结果
- 分析器：进行 SQL 语句的词法分析和语法分析
- 执行器：负责操作引擎，返回结果
存储引擎层
存储引擎层负责数据的存储和提取，提供读写接口。它的架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。不同的存储引擎共用一个 Server 层。最常用的存储引擎是 InnoDB，从MySQL 5.5.5 开始它成为了默认的存储引擎。
一条 SQL 查询语句是如何执行的
首先客户端要进行 SQL 语句查询，第一步肯定是要先连接数据库，这个时候连接器就会根据我们的用户名和密码是否正确来认证我们的身份，如果认证成功，连接器就会到权限表里查出我们拥有的权限并且获取，然后就成功连接到我们的数据库了。
然后接下来如果 MySQL 的版本是8.0之前的，那 MySQL 拿到这条 SQL 查询语句之后就会先到查询缓存里去看看，如果命中缓存，就直接返回结果，当然，在返回结果之前会先做权限验证，验证我们对要查询的表是否拥有查询权限。
如果没有命中缓存，就要开始真正执行这条 SQL 查询语句了。
首先是 MySQL 的分析器会对这条语句进行词法分析和语法分析，词法分析之后如果语法规则也符合，那 MySQL 就会根据这条 SQL 查询语句知道我们要做什么了，并且接下来还会调用 precheck 验证权限。
然后就是 MySQL 的优化器会根据这条 SQL 查询语句来确定一个效率更高的执行方案供执行器去执行。
最后就是执行器会先判断我们对要查询的表有没有查询的权限，如果有查询的权限，那么执行器就会根据被查询的表的引擎去使用这个引擎提供的接口，执行器调用引擎接口将这条 SQL 查询语句查询得到的所有行组成的记录集作为结果集返回给客户端。
到这里，这个 SQL 查询语句就执行完成了。
一条 SQL 更新语句是如何执行的
首先是连接器帮我们连接到数据库。
接着由于要在表上进行更新操作，那么跟这个表有关的查询缓存就会失效，这条更新语句会把与该表有关的所有缓存都清空。
然后分析器执行词法分析和语法分析操作。
接着优化器找到最佳效率的查找方案，更新的话，优化器一般就会决定使用索引，效率是最高（直接用树搜索）。
接下来就是执行器的工作了，与查询语句最大的不同也是在这里：
- 执行器首先调用引擎接口得到数据行：引擎会在内存中判断数据行所在的数据页是否在内存中，如果在内存就直接返回给执行器；如果不在内存，就会先从磁盘读入内存，然后再返回给执行器。
- 执行器拿到数据行之后就修改数据行的数据，然后调用引擎接口，引擎将更新后的数据行写入到内存中。
- 引擎将数据更新到内存后，InnoDB 存储引擎会将这个更新操作记录到 redo log 物理重做日志中，并将 redo log 改为 prepare 状态，然后告知执行器随时可以提交事务。
- 接下来执行器就生成这个更新操作的 binlog 逻辑归档日志，并把 binlog 写入磁盘中。（先在 binlog cache 中生成 binlog 逻辑日志，然后：1. 将 binlog 逻辑日志从 binlog cahce 中写入到 binlog 文件 --> 2. 调用 fsync 持久化到磁盘）
- 最后执行器调用 innoDB 存储引擎的提交事务接口，InnoDB 存储引擎把 redo log 改成commit 提交状态，至此就更新完成了，在内存中的数据页等待被刷到磁盘即可，redo log 也经由每秒轮询持久化到磁盘。
谈谈 InnoDB 和 MyISAM 存储引擎的区别？
- InnoDB 支持事务而 MyISAM 不支持事务。若有大量的事务操作，可以选择 InnoDB，但若仅需进行查询的非事务操作，那可以考虑选择 MyISAM，因为 MyISAM 拥有高速检索和全文搜索的能力，设计相对简单，效率更高。
- InnoDB 支持表级锁和行锁，而 MyISAM 只支持表级锁。
- InnoDB 的主键索引是聚簇索引，即叶子节点存放着整行的数据；而 MyISAM 是非聚簇索引，叶子节点存放的是数据的磁盘地址；但它们都是使用 B+ 树实现的索引。
- InnoDB 支持外键：即用一个表的列（外键）去检索另一个表的主键。
- InnoDB 支持在线热备份。
InnoDb 用 Buffer Pool 缓冲池来管理内存，使用了 LRU 算法，它做了哪些优化？
参考：《MySQL 实战45讲：33 | 我查这么多数据，会不会把内存打爆？》https://time.geekbang.org/column/article/79407
InnoDB 在管理内存的时候使用了优化后的 LRU 算法，是为了应对冷数据的全表扫描导致的性能问题。
在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域，young 区域用来保存热数据，old 区域用来保存冷数据。
新插入内存的数据页首先会被放到 old 区域。
如果这个数据页在 LRU 链表中，或者说在内存中存在的时间大于 1 秒，也就是经常会被访问到，那就把它移动到链表头部，也就是移动到 young 区。
如果这个数据页在内存中存在的时间少于 1 秒，也就是不会经常被访问到，那就保持位置不变，也就是不会移动到链表头部，等待后续新插入的数据页将其淘汰掉。
要清楚的是，一个数据页从头扫描到尾是顺序扫描的，也就是扫描一张数据页一次的时间是非常快的，往往都是少于 1 秒的。
对于冷数据的大表扫描，每次都会访问新的数据页，这些数据页在内存中都只会被扫描一次就被新数据页给淘汰掉，而由于扫描一次的时间肯定是少于 1 秒的，所以这些数据页都不会移动到链表头部，也就是 old 区域会不停的迭代，但 young 区域是不会受到影响的，young 区域仍然可以继续用来提供热数据的访问！
redo log 与 binlog 如何保证数据的可靠性？
redo log 与 binlog 的详细执行过程？
参考：《MySQL 实战45讲：23 | MySQL 是怎么保证数据不丢的？》https://time.geekbang.org/column/article/76161
只要 redo log（重做日志） 和 binlog（归档日志） 保证持久化到磁盘，就能确保 MySQL 异常重启后，数据可以恢复。
可以通过将 MySQL 设置为“双1”模式来让可靠性加强，但是效率会降低。
sync_binlog = 1; 让 binlog 写入到文件系统后就立即持久化到磁盘。
innodb_flush_log_at_trx_commit = 1; 使 redo log 在 prepare 阶段后就立即持久化到磁盘。
“双1”模式下 redo log 和 binlog 的具体流程为：
redo log 从 redo log buffer 写到文件系统中
（redo log buffer共享）
↓
binglog 从 binlog cache 写到文件系统中
（binlog cache 每个线程事务独有，因为事务要连续）
↓
redo log 进入 prepare 阶段后并持久化到磁盘
↓
binlog 持久化到磁盘
↓
redo log 进入 commit 阶段
↓
等待每秒轮询刷盘将 redo log 持久化到磁盘。
详细知识点可以看下面：
binlog 的写入机制
事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache write 到 binlog 日志文件中，并清空 binlog cache，最后调用 fsync 将 binlog 持久化到磁盘。
binlog cache 每个线程一份，但最终都写入到同一份 binlog 日志文件中。
要注意：写到日志文件中与持久化到磁盘是不同的概念：
- write 指的是把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
- fsync 才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。
write 和 fsync 的时机，是由参数 sync_binlog（控制 binlog 日志在文件系统中以怎样的机制持久化到磁盘） 控制的：
- sync_binlog=0 时，表示每次提交事务都只写到 binlog 文件，不持久化到磁盘；不安全，但磁盘 I/O 次数少，效率高；在进行主备复制时，若主备延迟太大，备库可以将其暂时设置为0。
- sync_binlog=1 时，表示每次提交事务写完到 binlog 文件后就持久化到磁盘；安全，但磁盘 I/O 次数多，效率不高。
- sync_binlog = N 时，表示每次提交事务写完 N 个后再持久化。
因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，让事务多写到文件中再一起持久化到磁盘，可以减少磁盘 I/O 的次数，进而提升性能。
但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。
所以，在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0 ，比较常见的是将其设置为 100~1000 中的某个数值。
redolog 的写入机制
首先明确 redo log 是内容，而 redo log 这个内容是写在 redo log buffer 中的。也就是说，事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的。
和 binlog 一样，redo log 也是从 redo log buffer 先写到文件系统的 page cache 再持久化到磁盘中。日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。
为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：
- 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer（内存）中 ;设置成“双非1”时不建议设置成0，应该设置成2，因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小。
- 设置为 1 的时候，表示每次事务在 redo log 的 prepare 阶段将 redo log 直接持久化到磁盘中，异常重启后，若 binlog 也持久化到磁盘了，那么数据就可以恢复，如果 此时 binlog 还没持久化到磁盘，那么事务就进行回滚；redo log 的 commit 阶段不需要控制其持久化磁盘，因为每秒会轮询刷盘。
- 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
注意，redo log 持久化到磁盘的行为不像 binlog 可能会直到 binlog 日志文件在文件系统中写满后才持久化到磁盘，而是：InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
所以，在事务执行过程中，就算还没提交的事务，其中的操作也是会被持久化到磁盘的。
现在说回两阶段提交：时序上 redo log 先 prepare， 再写 binlog，最后再把 redo log commit。
如果此时把 innodb_flush_log_at_trx_commit 设置成 1，那么持久化到磁盘的操作是在 redo log 的 prepare 阶段后进行的，反而在 redo log 的 commit 阶段后只需要 write 到文件系统的 page cache 中就够了，不需要持久化到磁盘。
因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的，异常重启后，若 binlog 也持久化到磁盘了，那么数据就可以恢复，但如果此时 binlog 还没持久化到磁盘就异常重启，那么事务就进行回滚。
正因为这个崩溃恢复逻辑再加上每秒一次后台会轮询刷盘，所以 InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了，只会 write 到文件系统的 page cache 中就够了。
所以，通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。
所以，两阶段提交可以更加细化的拆分为：
其中，1-3 与 2-4 持久化到磁盘的操作都可以触发组提交机制（组提交是指：一个事务在还没持久化磁盘时，又有另一个事务写到了文件系统，那可以一并的持久化到磁盘中）。
不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。
如果想提升 binlog 组提交的效果，可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。
binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用fsync;
binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。
通过组提交机制，可以大幅度降低磁盘的 IOPS 消耗。
653
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
