MySQL 实战记录篇1

记录一下在MySQL实战中简单的笔记

MySQL的逻辑架构，一条查询语句是怎么执行的？

MySQL 的架构共分为两层：Server 层和存储引擎层  

简单的架构图：

连接器：用来和mysql服务器建立连接，tcp三次握手，输密码等等；空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时；mysql连接也有长短连接之分，长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。两种解决办法：1.定期断开连接  2.客户端主动重置连接

查询缓存：如果命中则直接返回结果；查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。因为表的不断更新 很难命中，因此很鸡肋。mysql 8.0之后直接把这块删除了

解析器：进行词法分析，得到表名，字段，sql类型，where等等；进行语法分析，看符不符合sql语法

在优化器和解析器之间还有一步预处理器：

• 检查 SQL 查询语句中的表或者字段是否存在；
• 将 select * 中的 * 符号，扩展为表上的所有列；

优化器：一条语句可能有多种执行次序，效率也不同，优化器选最快的（选最快的索引）

执行器：执行sql语句，根据表的引擎定义，去使用这个引擎的api，从引擎读取记录返还给客户端。执行器查询的过程是一个 while 循环，执行器和引擎有三种交互过程：主键索引查询，全表扫描，索引下推（小林有讲，很详细）

一条更新语句是怎么实现的？

前面都需要经过第一个问题所抛出的逻辑架构的步骤，不过在更新后面会多两个东西redo log（重做日志）和binlog（归档日志），用来记录更改，用于后面数据丢失了来恢复用的

当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

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

redo log是InnoDB引擎特有的日志，Server层也有自己的日志，称为binlog

为什么会有两份日志呢？

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

这两种日志有以下三点不同。

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。

3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会生成一个新的日志，并不会覆盖以前的日志。

在最后提交的时候redo log会被分为两步提交，刚开始更新操作记录到redo log使他处于prepare，将redo log刷到硬盘上（意味着可以随时提交事务结束了）；然后写入binlog；然后提交事务，binlog 刷入磁盘，redo log改成提交（commit）状态之后，redo log文件刷入磁盘。

这样是为了保证数据库的一致性，必须要保证2份日志一致，使用的2阶段式提交；其实感觉像事务，不是成功就是失败，不能让中间环节出现，也就是一个成功，一个失败

两个使用场景：

• binlog 用于备份恢复、主从复制；
• redo log 用于掉电等故障恢复。

一个问题：如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。

因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

如果有了事务操作加持，引入undo log

在redo log之前还会多一个undo log（回滚日志） 

他和redo log，这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

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

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务

undo log 两大作用：

• 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
• 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。