MySQL 内核原理总结
这篇文章还不错，尤其是三大日志那里，虽然不复杂，但讲的很明白了
- 学一个技术，我们先要 跳出来，看整体，先要在脑中有一个这个技术的全貌。然后再 钻进去，看本质，深入的研究细节。这样方便我们建立一个立体的知识网络。不然单学多个知识点，是串不起来的。不容易记住，理解也不会深刻。
- 所以，我们先把 MySQL 拆解一下，看看内部有哪些组件，我们 Java系统执行一条SQL，MySQL的内部是如何运作，给我们返回结果的。
- 我们先从我们访问数据库说起
- – 我们想要查询数据库，首先得建立网络连接
- MySQL 驱动负责建立网络连接，然后请求 MySQL 数据库
- 其实就是创建了一个数据库连接
- Java系统的 数据库连接池
- – 如果我们的系统所有线程访问数据库时，都使用一个连接会怎样
- – 所有线程抢夺一个连接，没有连接 就操作不了数据库，效率极低，因为需要后面的线程需要等待前面的线程处理完才行
- – 我们的系统如果每个线程访问数据库时，都创建一个连接，然后销毁，会怎样
- – 创建连接需要网络通信，网络通信是很耗时的
- 好不容易创建了连接，查询完就给销毁了，那效率肯定低
- – 所以，我们要使用数据库连接池
- – 数据库连接池里，会有多个数据库连接
- 每个线程使用完连接后，会放回池子，连接不会销毁
- 常用的数据库连接池有 DBCP、C3P0、Druid
- MySQL 的 连接器
- – Java 系统要和MySQL 建立多个连接，那 MySQL 自然也需要维护与系统之间的连接
- 所以，MySQL 整体架构的第一个组件就是 连接器
- MySQL 连接器的功能
- – 连接器负责跟客户端建立连接、获取权限、维持和管理连接
- 连接器内部也是一个 连接池，里面维护了各个系统跟这个数据库创建的所有连接
- Java 系统连接Mysql 的过程
- – 首先完成TCP的三次握手，创建一个网络连接
- 然后开始权限认证，也就是 你的 用户名 、密码 是否正确
- 连接成功后，如果没有后续动作，这个连接会处于空闲状态
- 空闲一定时间后，会自动断开连接，由参数 wait_timeout 控制的，默认值是 8 小时
- 我们现在已经知道，我们执行SQL，一定要先连接到数据库。数据库的 连接器 会对系统进行权限认证，如果认证成功，就创建了一个数据库连接。
- 那么，连接之后是怎么执行SQL语句的呢？
- 一个基本的知识点，网络连接必须要分配给一个线程去处理
- – 由一个 线程 来监听 和 读取 Java系统请求的数据
- 线程会从网络请求中解析出我们发送的sql语句
- 线程获取到了我们写好的SQL语句，那交给谁来执行呢
- – 其实在执行之前，还有一步，就是查询缓存
- 之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中
- – key 是查询的语句
- value 是查询的结果
- 如果在缓存中找到 key，那么这个 value 就会被直接返回给客户端
- 但是，建议不要使用缓存，往往弊大于利
- – 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空
- 查询缓存的命中率会非常低
- 可以将参数 query_cache_type 设置成 DEMAND，关闭缓存
- MySQL 8.0 版本直接将查询缓存的整块功能删掉了
- – 所以接下来的图，我就不画 查询缓存 这个步骤了
- 没有了查询缓存这个功能，我们写好的SQL，都是交给查询解析器来分析的
- 解析器
- – 我们写的SQL 语句，人认识，但是机器是不认识的，所以必须要解析我们的语句
- 拿一条SQL举例
select id,name from user where id = 10
- – 我们的SQL 是由 字符串 和 空格 组成的
- – 有些字符串 是 MySQL 的关键字
- MySQL 会识别这些关键字
- 语法解析器 会将 上面的SQL拆解为几部分
- – 要从 user 表里查询数据
- 查询 id 字段的值，等于 10 的那行数据
- 对查询的那行数据，提取出 id 、name 两个字段
- 如果语法不对，解析器会提示我们
ERROR 1064 (42000): You have an error in your SQL syntax;
- 优化器
- – 经过了解析器，MySQL 就知道你要做什么了，在开始执行之前，还要先经过优化器的处理，选择一个最优路径
- 我们的表可能创建了多个索引，或者多表关联（join）的时候
- – 这时，是有多个路径可以查询到结果的，但是执行效率会不同
- 查询优化器就是干这个事的，它会选一个效率最高的路径
- 这个我们后面会仔细分析，这里知道它会选一个最优路径就好。先了解MySQL的整体架构，再深究细节
- 执行器
- – MySQL 通过分析器知道了你要做什么
- 通过优化器知道了该怎么做，生成执行计划
- 于是就进入了执行器阶段，负责这个计划的执行
- 执行器 主要是 操作存储引擎来返回结果的，我们重点要关注的是存储引擎的执行原理
- 接下来，我们来研究一下，存储引擎的架构设计，以及如何基于存储引擎来完成一条更新语句的执行。
- MySQL 有多种存储引擎，InnoDB、MyISAM等，我们就说最常用的InnoDB。接下来的图，我就只画 InnoDB 存储引擎这部分的了，连接、解释器、优化器会去掉，不然画不下了。
- 我们以一条更新操作来看一下 InnoDB 的运行流程
- – 用这个SQL举例：
update users set name = ‘李四’ where id = 10
- InnoDB 中 重要的内存结构 Buffer Pool
- – Buffer Pool 缓冲池，是 InnoDB 存储引擎的核心组件。这里会缓存大量的数据，查询时会先看 缓冲池 内是否有这条数据，如果有，就可以不用查磁盘了
- 如果 Buffer Pool 中没有这条数据，就要先从磁盘中加载进来
- Buffer Pool 中的数据是缓存页，磁盘中的表数据是数据页，内部有其数据结构。我们这里忽略，先看一下整体的运行流程，之后再分析里面的物理结构。
- undo 日志文件
- – 如果我们执行一个更新语句，在没有提交事务之前，我们都是可以对数据进行回滚的
- undo 日志文件就是保证我们可以回滚数据的一个组件
- 举例：
- – 如果我们要把 id = 10 的数据的 name 从 张三 改为 李四
- 第一步是把数据加载到 Buffer Pool 里
- 第二步 就要把 id = 10 ，name = 张三 的这条原始数据，写到undo日志文件
- 如果数据回滚，就会从 undo 日志文件中读取原始数据恢复
- 如果多个事务同时对一条记录更新，则按照事务id，最大的修改在Buffer Pool中，其他的都在undo log中，各记录通过隐藏字段中的DB_ROOL_PTR链接起来。
- 备注：InnoDB 是个存储引擎，步骤2 其实也是我们上面说到的 执行器 来把原始数据写到磁盘上的，后面的步骤，但凡有写磁盘、读磁盘的操作，都是执行器执行的。这里为了画图方便，直接连线了
- 然后 执行器 会更新 Buffer Pool 中的缓存数据
- 现在，缓存内的数据已经从 张三 更新到 李四 了
- – 那么，现在有一个问题，如果 MySQL 此时宕机了会有问题吗
- 因为现在还没有提交事务，代表这条语句还没执行完
- 所以，此时宕机没有关系，事务没提交，重启后内存数据就没了，磁盘数据也没变化
- 磁盘的数据也是原始数据，所以没关系
- 我们在内存中修改的数据，终究要刷到磁盘上的。MySQL 不会马上把这条数据刷到磁盘上，会等系统不忙碌时，再刷回去。因为刷磁盘这事，本来实时性要就不高，我们查询的时候也是基于内存的，磁盘是什么数据无所谓，只要保证最终一致就好了。
- 我们只有提交事务后，才能把内存修改的数据刷到磁盘上
- 提交事务是一个过程，这个过程中我们需要先写入几份日志文件，只有这几个日志文件都写成功了，事务才算提交成功
- 所以，这里开始介绍 InnoDB 存储引擎中的另一个组件 Redo log Buffer
- – 内存中的 Redo log Buffer 配合 磁盘上的 redo log 日志文件，可以在 MySQL 意外宕机的情况下，恢复内存数据的。它会记录内存中修改的数据，然后把这些数据刷到磁盘上的 redo log 日志中
- 之前，我们已经修改了内存数据，在修改完成后，执行器就会向Redo log Buffer 中写入日志，到这一步为止，我们已经执行完了这条SQL语句，就差提交事务
- 如果我们提交事务，第一步就是把 Redo log Buffer 中的日志刷到磁盘上的 redo log 中
- – 此时，如果 MySQL 宕机，数据是不会丢失的。重启后，会加载磁盘上的 redo log 日志文件，恢复到内存中
- redo log 日志是 偏物理层面的日志，也叫 重做日志。而 binlog 是归档日志（这个后面说）
- – 为什么说是偏物理层面的日志，就是说不是给人看的，你看了也不知道修改的啥
- – 比如，对哪些数据页上的什么数据做了什么修改
- 备注：提交事务，不是一步完成的，是一个过程。后面的步骤 5、6、7都属于提交事务的过程，只要有一步失败，那提交就是不成功的
- 把 redo log 从内存刷到磁盘的策略有三种
- – 通过参数 innodb_flush_log_at_trx_commit 来配置 ，默认值为 1
- – 值为 0 ：提交事务后，不会把 redo log buffer 里的日志刷到磁盘
- – 此时如果 MySQL 宕机，redo log buffer 内数据全部丢失
- – 值为 1 ：提交事务后立刻把日志刷到磁盘，只要提交事务成功，那 redo log 一定在磁盘
- – 值为 2 ：提交事务后会把 redo log 先刷到 os cache(操作系统缓存) 里 ，然后 os cache 在适当的时机刷入磁盘
- – 在os cache 没刷磁盘期间，如果 MySQL 宕机，这部分数据会丢失
- – 我们平时开发还是要用 innodb_flush_log_at_trx_commit = 1 ，立刻刷磁盘。保证提交事务后，数据绝对不会丢失
硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写。
在个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint
- write pos 是当前记录的位置，一边写一边后移
- checkpoint 是当前要擦除的位置，也是往后推移
每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。
要每次把修改后的数据页直接刷盘不就好了，还有 redo log 什么事？它们不都是刷盘么？差别在哪里？
实际上，数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，有必要把完整的数据页刷盘吗？而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移 量、更新值，再加上是顺序写，所以刷盘速度很快。所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。其实内存的数据页在一定时机也会刷盘，我们把这称为页合并
在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。那如果redo log设置commit阶段发生异常，会不会回滚事务呢？并不会回滚事务，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。
- redo log 是偏物理层面的日志。如果发生数据库操作失误，我们不能根据这个来恢复数据。我们需要用 binlog 来恢复，binlog 是偏逻辑性的日志
- binlog
- – binlog 也叫 归档日志，是逻辑性的日志
- – 如：对 users 表的 id = 10 的一行数据做了更新操作
- binlog 不是 InnoDB 存储引擎特有的日志文件，是属于 MySQL Server 自己的日志文件
- 我们开始提交事务，第一步是把 redo log 日志刷到磁盘， 接下来执行器还要继续写 binlog 日志到磁盘
- binlog 刷磁盘有两种策略，通过 sync_binlog 参数来配置，默认值 0
- – 值为 0 ：先刷到 os cache 缓存，然后不定时刷入磁盘
- – 如果宕机，可能会丢失数据
- 值为 1 ：直接刷到 磁盘文件中 ，是要提交事务成功，数据一定不会丢失
- 最后，是 事务提交的最后一步
- – 执行器 会把本次更新对应的 binlog 日志的文件名 和 本次更新的 binlog 日志在文件中的位置，都写入 redo log 日志文件中
- 同时，还会写入一个 commit 标记
- 只有完成了这一步，才算是 事务提交成功
- 为什么要在 redo log 中写入 commit 标记呢？
- – 用来保证 redo log 和 bin log 的数据一致性
- 举例：
- – 如果完成了第5步，刷入了 redo log 后，MySQL 宕机了，那 bin log 就没法写入 commit 标记，那这条数据没有 commit 标记，就是无效的，提交事务失败
- 如果第6步，刷入了 binlog 后，MySQL 宕机了，一样没有 commit 标记，也是无效的
- 现在，本条更新语句已经提交了事务，更新完毕了
- – 此时，内存上的数据 已经是 更新过的 name = 李四 ，磁盘上是 name = 张三
- 此时，MySQL 宕机是无所谓的，数据不会丢失，重启后会从redo log 加载到缓冲池
- 然后，是最后一个步骤
- – MySQL 有一个后台的 IO 线程，在之后的某个时间，会随机的把内存 Buffer pool 中修改的脏数据刷回磁盘的数据文件中
- – 脏数据：就是内存和磁盘不一致，但是没有什么影响
- 到现在，我们已经知道了 MySQL 的整体运行流程，和内部的运行原理，MySQL 的全貌我们已经看见了。我们在脑海中要有下面这张图
