MySQL 实战 45 讲 笔记 ----来源《极客时间》

01 | 基础架构：一条SQL查询语句是如何执行的？

1. MySQL 可以分为 Server层 和 存储引擎层 两部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等。存储引擎层支持 InnoDB、MyISAM等.

(1) 连接器：管理连接，权限认证。

(2) 查询缓存：命中则直接返回结果。

(3) 分析器：词法分析，语法分析。

(4) 优化器：生成执行计划，选择索引。

(5) 执行器：操作引擎，返回结果。

(6) 存储引擎：存储数据，提供读写接口。

2. 临时内存累积问题： 在执行过程中临时使用的内存会保留在连接对象里面，在连接断开的时候才释放。所以长连接累积下来，会导致内存占用太大，被系统强行杀掉(OOM)。

    解决办法：可以执行 mysql_reset_connection 来重新初始化连接资源，释放内存。而且它不需要重连和重做权限验证。

3. 查询缓存往往弊大于利，MySQL 8.0 版本将查询缓存的功能删掉了。

02 | 日志系统：一条SQL更新语句是如何执行的？

1. MySQL 最重要的两个日志：物理日志 redo log 和逻辑日志 binlog。

2. redo log 相关：

(1) 作用：避免每一次update语句都需要查找磁盘，降低 IO 成本。

(2) 简述：处理update语句时，不查找对应的记录去写入，而是把数据写进内存和日志，并把日志本身持久化到磁盘。之后空闲了再把日志对应的数据同步到磁盘。

(3) WAL 技术，关键点就是先写日志，再写磁盘。 应用于 redo log。

(4) redo log写入方式：环形方式，先从头开始写，写到末尾就又回到开头循环写。

(5) redo log的数据是写入磁盘的，所以可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

2. binlog 相关：

(1) 作用：记录原始的SQL语句，用于数据恢复操作。

3. redo log 和 binlog 的区别：

(1) redo log 是为了降低IO成本；binlog 是为了可以恢复数据。

(2) redo log 是 属于SQL的存储引擎层(InnoDB )的；binlog 是属于 MySQL 的 Server 层的。

(3) redo log 记录的是“在某个数据页上做了什么修改”；binlog 记录的是这个语句的原始逻辑。

(4) redo log 是循环写的，空间用完后会擦除数据；binlog 是追加写入的，并不会擦除以前的日志。

4. update 语句基于redo log和binlog相关的内部流程：

(1) 在内存查找相关数据，不在内存的话就从硬盘读取。

(2) 根据update语句，在内存中修改数据。

(3) 把操作记录写入到 redo log 里。redo log 设置为 prepare 状态。

(4) 生成这个操作的 binlog，并把 binlog 写入磁盘。

(5) 把redo log 设置成commit状态。

(6) 如果在上面(4)(5)步骤时出现了系统挂掉的情况，那么在系统重启时，会接着做完(4)(5)步骤。

作用：上面的“两阶段提交模式”，保证了任何情况下，redo log 和 binlog都是同步一致的。

03 | 事务隔离：为什么你改了我还看不见？

1. 事务是在MySQL的引擎层实现的。原生的 MyISAM 引擎不支持事务，是 MyISAM 被 InnoDB 取代的重要原因之一。

2. 有多个事务同时执行的时候，可能出现脏读、幻读的问题。SQL通过 “隔离级别”的概念，来解决这些问题。

3. SQL 标准的事务隔离级别包括：读未提交、读提交、可重复读 和 串行化。

(1) 读未提交：一个事务 还没提交，它做的变更，在别的事务里 已经生效。

(2) 读提交：一个事务 提交之后，它做的变更，在别的事务里 才生效。

(3) 可重复读：一个事务执行过程中看到的数据，与事务在启动时的数据是一致的。未提交的变更，对其他事务是不生效的。

(4) 串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。后访问的事务必须等前一个事务执行完成，才能继续执行。

4.  在实现上，数据库里会创建一个视图，访问时以视图的结果为准。

(1) 读未提交：没有视图概念，直接返回记录上的最新值。

(2) 读提交是指：视图是在每个 SQL 语句开始执行时创建的。

(3) 可重复读是指：视图在事务启动时创建，整个事务存在期间都用这个视图。

(4) 串行化：没有视图概念，用加锁的方式来避免并行访问。

5. MySql的事务隔离级别是可重复读，Oracle是读提交。做数据迁移时要注意修改。

6. 事务隔离的实现：每条记录在更新的时候，都会同时记录一条回滚操作。通过这些回滚操作，可以得到前一个状态的值。也就可以获得各个隔离级别状态下的值。

7. 回滚日志带来的问题：当没有事务再需要用到这些回滚日志时，回滚日志才会被删除。时间很长的事务，会导致大量占用存储空间。应尽量避免使用长事务。

8. 建议通过显式语句来启动事务(begin 或 start transaction），具体是使用 set autocommit=1。