引言：之前数据库模块更新过几篇数据库基础内容，这些内容基本处于学校教学阶段的知识，现在实际应用中MySQL用的更普遍一些，包括面试的时候这一块儿问的也是比较频繁，这里结合linxiaobin的实战45讲 总结一下学习笔记，以供大家一起学习讨论。

基础架构

作为基础架构的引入，作者先问了一个很多sqlboy可能都没有考虑过的问题，一条sql语句究竟是怎么运行的。

盗图：

架构主要包括server和引擎两个层次。

server层涵盖大多数的核心服务功能，包括连接器，查询缓存，分析器，优化器，执行器 ，以及内置函数 （比如我们常用的时间，日期，数学，加密函数）。需要注意的是，像是触发器以及视图的功能也是在这一层实现的。
存储引擎层，主要负责数据的存储和读取。架构模式是插件式的。目前主要是InnoDB作为存储引擎

一条查询sql如何执行

好，我们来看一条简单的sql语句：

select * form T where ID=10;

1.连接器——权限与身份

这里主要是负责权限的验证，有的小伙伴可能好奇权限的意义，因为在学校里可能课题或者自己做一个小demo并不在意这个，但是实际开发过程中，一个库内的表是很多的，在保证权限尽可能收敛的情况下，我们一般只给必须用到的表给人必须的权限。
连接命令：

mysql -h$ IP -P$ port -u$ user -p

这里注意需要完成TCP三次握手之后，连接器才开始认证你的身份,并查询权限表固定你的权限（这意味着即使管理员更改了你的权限，如果你还维持当前连接，权限不会改变，只有建立新的连接，连接器才能确认你新的权限）

建立连接是复杂的，因此通常使用长连接，但是长连接会带来一个问题内存占用很大， 原因是mysql执行过程中临时使用的内存是管理在连接对象中，直到连接断开释放。如果长期积累，就会被操作系统强行杀掉，mysql就会异常重启。

解决方案是：重置连接
mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存——注意缓存失效场景

完成连接之后，进入第二步，查询缓存；
之前执行过的语句和结果，会以key-value的键值对形式缓存在内存中。

但是大多数时候不要使用缓存，因为查询失效非常频繁（命中率极低），只要对表有更新，针对该表的所有查询缓存都会被清空。
注意mysql8.0之后的版本不再有缓存

缓存失效场景：

1. 查询语句不同
2. 查询语句中含有不确定值，比如当前时间。
3. 不涉及表
4. 表已经更新
5. 存储函数，触发器或事件主题内执行的查询。

2.分析器——词法分析，语法检查

词法分析：主要是针对关键词，通过关键词识别select表示查询，T表示一张表，ID表示T的某一列。
语法分析：根据语法规则，判度语法是否正确，比如select 打错了。

3.优化器——索引选择，连表顺序

主要在表中有多个索引的时候，决定使用哪个索引；在有多张表join的时候决定哪两张表先连。
原则是：避免先进行连接，而是先进行选择、增加一些投影操作再连接

这一部分和我们之前文章所说的是一致的：查询优化分为两步，重写（代数优化，调整操作顺序，即这里我们讲的连表顺序），以及操作优化
添加链接描述

4.执行器——判度对表是否有权限操作，并执行

先检查对表T有无查询权限，有的话，调用InnnoDB引擎接口，读取第一行而后判度是否满足。

一条更新sql如何执行

首先明确上文所述的几个步骤肯定也是要走的。（我们假设有语句 update user set a=2 where id=1;）

更新流程涉及两个重要的日志模块：redo log（重做日志） binlog(归档日志)。

引擎层——物理日志redo log

首先明确一个IO代价，即如果每个更新必定要写入磁盘，那么我们如果每次都在磁盘上去找某一条记录的IO开销和查找开销都是很大的。

为了解决这个问题，设计者使用WAL技术，WAL的全称是Write-Ahead Logging。先写日志，再写磁盘 ，即把日志作为一个更改的缓冲区域，等我不忙的时候慢慢的在磁盘里找，把日志上的改动写进去。

当有一条记录需要更新，InnoDB引擎先把记录写在redo log，并更新内存完成操作。引擎在适当的时候将操作更新到磁盘中

redo log是固定大小的，比如一组4个文件，每个文件1GB，总共redo log 可以记录4GB的操作。从头开始写，写到末尾再回到开头循环。

实际实现方面，用两个指针：
write_pos：当前记录的位置，写到文件3的末尾就要回到文件0的开头。
check_point:当前擦除的位置，表示已经把redo log的操作处理到磁盘上了。移动同上循环。

所以从write_pos到 check_point之间的文件0目前就是空着的状态，可以用来记录新的操作。
一旦两个指针相遇，表示redo log满了，得暂停write，让check_point消化一下。
这样，redo log就保证了即使异常重启，之前提交的记录也不会丢失，称之为crash-safe

server层——binlog

上述内容是在引擎层中，InnoDB引擎的特有日志——redo log。

但是在server层，用的时binlog .
主要原因是最开始mysql并没有InnoDB这个引擎，用的是MySAM引擎，并没有上述crash-safe的能力。
后来InnoDB是另一个公司以插件的形式引入MySQL的。

bin log 与redo log的不同：

1. redo log是InnoDB独有的，而bin log在server层实现，所有引擎都可以使用。
2. redo log是物理日志，记录了在某个数据页上做了什么修改。而binlog是逻辑日志，记录语句原始逻辑，比如给ID=2的这一行的某个属性加1
3. redo log 是循环写，空间固定，会用完。 bin log是追加写（文件写到一定大小就切换到下一个），不会覆盖之前的日志。

InnoDB更新逻辑

了解了binlog之后，我们看InnoDB如何执行更新语句的。

1. 执行器先找到ID=2这一行，由于ID是主键，引擎可以直接用树搜索到这一行。注意如果这一行所在的数据本来就在内存中，直接返回给执行器，否则需要从磁盘中读到内存里。
2. 执行器拿到数据执行操作，得到一行新的数据，然后调用引擎接口写入这行新数据。
3. 引擎将这行数据更新到内存中，同时将操作记录到redo log。此时redo log处于prepare阶段，告知执行器记录完了，随时可以提交
4. 而后执行器生成这个操作的bin log，把bin log 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚写入redo log的记录改为commit提交阶段，更新完成
   

两阶段提交

我们注意到写入redo log 的步骤分为prepare和commit两个阶段。这就是两阶段提交，主要为了让两份日志保持逻辑一致

binlog会记录所有操作，并追加写入，系统定期做整库备份。假设我们找到的整库备份与需求记录点差一天，我们只需要从这个点开始根据binlog重做这一天所有的操作即可。

假设不使用两阶段提交，而先写某一个日志，写完了再写另一个
假设场景为，ID=2，这一行c=0；要将其加1，更新完第一个日志，之后再写第二个日志的时候发生了crash。

1. 假设先写redo log ，在写binlog 时crash。由于redo log由crash-safe特性，仍可以恢复c被加了1，此时c=1.但是由于binlog没写完，如果用binlog恢复只会得到c=0。
2. 假设先写bin log,后写redo log，由于还没有写redolog，会认定为这个事务无效，c还是=0。但是因为我已经写了binlog，如果用Bin log恢复就会多一个事务。

总之，如果不使用两阶段提交，就会导致用不同log恢复的状态不一样，即两份log逻辑无法保持一致。常见的做法是全量备份，再应用bin log