搞懂MySQL如何运行【MySQL学习】

大家好，我是九三二十七。今天和大家一起学习MySQL😊

---

1.MySQL的基础架构

作为一名后端开发，少不了要对数据库进行DDL DML的操作，当我们写下一行数据库命令时，我们应该要学会俯瞰全貌，思考这个命令这条语句，在数据库端将要怎么运行，执行性能怎么样。学会下意识去深度思考，也可以学会为将来的查询优化做综合考虑。

代码的编写，不仅只是业务逻辑的实现，面对一个功能，也需要深度思考，这个功能的输入输出是什么 输入要考虑功能的背景是什么，建表建索引就可以考虑周全 工具要考虑功能实现的业务链路，后期可以从产品从代码层面来优化 输出的时候，就需要用到 SQL ，考虑 SQL 是怎么执行的

这次我打算进行系统性的学习，更好的将自己了解的MySQL知识点形成网络图，防止解决完一个问题后又容易忘记。MySQL作为基础知识点，在工作和加薪，面试中是非常重要的。

这张图是MySQL的逻辑架构图，从客户端（我们所理解的nevicat客户端工具）到server层到存储引擎层

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

1. MySQL是底层是插件式开发，有着不同的存储引擎，通过配置来切换，不同的引擎共用一个server层
2. 在进行SQL优化时，从基础架构中我们就可以考虑到，我们要少用函数、存储过程、视图等，存储引擎讲需要的数据全部返回然后在server层进行筛选，这也是性能低下的原因之一

---

2. 查询语句是如何执行的

一、客户端通过连接器连接到服务端，获取到权限等信息， 然后在连接的有效时长内(interactive_timeout和wait_timeout参数控制， 5.7版本会断开可以自动重连)对sql进行处理。

1. 建立连接的过程通常是比较复杂的，所以建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。
2. 长链接占用资源等断开连接才会释放资源，随着时间推移会出现OOM，现象就是服务重启了。 解决方法： 1、定期断开链接； 2、mysql5.7以后可以通过设置mysql_reset_connection来定期重置链接

二、首先会判断查询缓存是否开启，如果已经开启，会判断sql是select还是update/insert/delete，对于select，尝试去查询缓存，如果命中缓存直接返回数据给客户端， 如果缓存没有命中，或者没有开启缓存， 会进入到下一步分析器。

MySQL8.0把查询缓存步骤给去掉了。原因是1.查询缓存会占用内存 2.命中率太低，之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中，但是缓存老是会被表更新操作清除掉，缓存了还没来得及用就又没了

三、分析器进行语法分析、词法分析，检查sql的语法顺序等得到解析树， 然后预处理器对解析树进一步分析，验证数据表、字段是否存在

四、优化器对sql执行计划分析， 得到最终执行计划，得到优化后的执行计划之后交给执行器。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

五、执行器调用存储引擎提供的接口来执行sql，得到响应结果， 将结果返回给客户端，如果缓存是开启状态， 会更新缓存。

全表扫描是在执行器层来进行判断的

1. 没有索引：执行器调用sql引擎的api进行全表扫描，默认存储引擎是提供按行读取的接口
2. 存在索引：执行器调用sql引擎的api，引擎通过索引的缓存快速定位到数据并返回，存储引擎会提供有条件的接口，加大数据结果集的返回

3. 更新语句是如何执行的

查询语句那一套流程，更新语句同样需要走一遍，server层和存储引擎层

1. 客户端访问数据库，连接器进行权限校验，建立连接并维系长连接
2. 在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。
3. 分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。
4. innodb先生成了redolog日志，然后server层再生成binlog日志

两阶段提交redo log
主要是为了让两个日志之间逻辑一致才行，不然出现crash会导致数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

一般备库的做法也是 定时全量备份+应用binlog日志增量备份

redo log 重做日志

WAL技术 write ahead logging 先写redo log顺序日志到磁盘，再异步更新数据到磁盘文件。

先写redo log到log buffer，具体内容就是针对哪个表空间的哪些页面做了哪些修改，然后log buffer中的日志内容会在某些时候写到redo日志文件中，比如事务提交时。至于为什么写redo日志会比刷新内存中的数据页到磁盘快，是因为服务器在启动时就已经给redo日志文件分配好了一块物理上连续的磁盘空间，每次写redo日志都是往文件中追加写，并没有寻址的过程。而修改过的数据页要刷新到磁盘的话，可能对应的磁盘空间并不是物理连续的，找起来费劲

crash safe： redo log 是 InnoDB引擎所特有的，所以我们如果再使用InnoDB引擎创建表时，如果数据库发生异常重启，之前提交的记录都不会丢失。 InnoDB正因为有了 redo log(重做日志)，才有了 crash-safe 的能力（即使mysql服务宕机，也不会丢失数据的能力）。

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

binlog 归档日志

binlog 处于MySQL server层日志
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

4. 来源

此文章为2月Day1学习笔记，内容来源于极客时间《MySQL实战45 讲》，强烈推荐该课程！