总体概览

总体概览

mysql整体分成Server层和引擎层。Server层又包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说如果我们在create table时不指定表的存储引擎类型,默认会给你设置存储引擎为InnoDB。

连接器

连接器的职责是管理连接和权限校验。客户端的连接，不管是mysql自带的客户端还是市面上一些常见的客户端连接工具（navicat,mysql front,jdbc,SQLyog等）必须先跟Server端建立通信连接，而建立连接的工作就是有连接器完成的。连接器负责跟客户端建立连接、获取权限、维持和管理连接。
可以通过mysql自带客户端发起连接，命令如下：
mysql ‐h host[数据库地址] ‐u root[用户] ‐p root[密码] ‐P 3306
在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。主要会做下面两件事：
1、如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
2、如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置

连接示意

如图所示，客户端连接成功之后会有一个会话（session），本次会话里会拉取一份系统用户表以及用户权限等信息，所以此时如果有另外一个客户端连接上来修改了系统用户表，此前的会话是感知不到，还是会保留之前的用户及权限，除非是断开后重新连接。说的具体点，就是会话生成之后用户和权限之类的都是固定的，不管是写权限还是读权限，此时有其他客户端修改了该用户的权限为只读，那已生成的会话该用户还是有写和读的权限。
为什么要这么设计呢？主要目的是出于隔离保护，试想一下如果数据库当前有1000个连接，每次增删改查都要去查下系统用户表不是很费时吗？如果，有其他地方修改了系统用户表在同步到这1000个会话不是要响应很久吗？如果此时是高并发的业务，比如秒杀，直接从会话内存中读取当然比系统用户表快的多，而且也不怕他们的改动或同步造成卡顿。
可以通过show processlist;查看当前有多少连接，分别在做什么事。

查看连接

其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。
客户端的连接是长连接，可以通过show global variables like "wait_timeout";这个命令查看连接的失效时间。

连接失效时间

28800单位是秒，也就是失效时间是8小时。长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

查询缓存

查询缓存主要是缓存查询过的语句，MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。
默认情况下，缓存的功能是关闭的。可以去my.cnf文件下开启这个query_cache_type配置，query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE需要用的时候才缓存或从缓存中查询。

缓存查询

可以通过show status like'%Qcache%';查看缓存的命中次信息。

缓存命中信息

缓存命中信息主要关注两个值，Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。
但其实这个查询缓存功能用处并不大，为什么呢？因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你好不容易地把结果存起来，还没使用呢，就被一个更新操作全清空了。对于更新频繁的数据库来说，查询缓存的命中率会非常低。而且，在引擎层如果用的是innodb，innodb有buffer pool 也会有一个缓存，这边的查询缓存是mysql server层级别。所以非要用的话记得将my.cnf参数query_cache_type 设置成 DEMAND（按需使用）,而且只适合那些基本不更新的表如：系统配置表、字典表，区域表。

分析器

词法分析器的主要目的就是分析一条sql语句的语法是否合法，是否正确。

分析器流程示意

词法解析粒度会大一点，主要是语法解析会解析成一个一个的token到分析机中进行解析，最终是要生成一个语法树。
MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

解析出的语法树

优化器

经过了分析器构建出语法树之后，MySQL 就知道你要做什么了。但是还不能直接执行，还要先经过优化器的处理，比如表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联决定各个表的连接顺序等。假设有这样一条语句：
 select * from t1 join t2 using(ID) where t1.name='ali' and t2.name='huawei';
可以先从表 t1 里面取出 name='ali'的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 name的值是否等于huawei。也可以先从表 t2 里面取出 name=huawei 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 name的值是否等于ali。这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同(大小表驱动问题，通常以小表为驱动表)，而优化器的作用就是决定选择使用哪一个方案。

执行器

执行器就是到了具体执行sql的时候，会根据表引擎到具体的引擎进行执行。比如innodb表的一个查询语句，会根据查询条件到底是是走索引查询，或是全表扫描执行都是执行器调用innodb的接口去执行的。

bin-log

binlog是Server层实现的二进制日志,所以是所有引擎共用的日志，他会记录我们的新增，修改和删除操作。Binlog为逻辑日志,记录的是一条语句的原始逻辑而且不限大小,追加写入,不会覆盖以前的日志。所以我们可以借助binlog做一些数据恢复的工作。
在my.cnf配置文件中配置一个binlog目录即视为开启了binlog功能。

binlog配置 binlog常用命令

binlog何时开启，只会记录开启时间节点及之后的操作，之前的操作不可追溯。
binlog里的内容是二进制形式记录的，可读性比较差，可能需要专业的DBA才能看懂，但是作为一个开发人员也是有一定的技巧可以去读懂它。比如begin,commit这种关键词信息，只要在binlog当中看到了，你就可以理解为begin-commit之间的信息是一个完整的事务逻辑,然后再根据位置position判断恢复即可。

binlog 内容

根据上面的信息可以恢复到指定位置
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="45" ‐‐stop‐position="445"
也可以恢复全部数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001
或者恢复到指定时间
usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001‐‐stop‐date= "2021‐12‐13 12:00:00" ‐‐start‐date= "2021‐12‐14 11:55:00

innodb存储引擎

虽然mysql的引擎层是可插拔的，但是当前mysql默认的存储引擎就是innodb而且也是使用最多的。所以在mysql的组件这边也重点认识一下innodb，它主要是会跟你执行器交互，概图如下：

执行器和innodb交互

一个语句经过分析器，优化器，执行器之后在innodb引擎是怎么处理的呢？
ibd是innodb的磁盘文件，一条sql语句的执行不是直接在磁盘上进行执行的。比如上面的这样一个更新name的语句。
1.到了执行器之后，执行器调到innodb引擎，会先从磁盘（ibd文件）加载这条语句的原始数据到缓存池（BufferPool）
2.将加载到的原始数据也就是旧值写入到innodb的undo日志文件，这个日志文件是innodb引擎独有的，binlog是mysql server层的，所有引擎都有。将旧值写到undo日志文件目的是为了方便事务回滚。更新失败回滚事务的时候，从undo日志里拿到旧值重新写到BufferPool来达到回滚的目的。
3.undo日志写成功之后，就开始在bufferpool里面更新数据，将name更新成新的值。
4.bufferpool里的值更新成功之后，开始往redo日志缓冲里里插入一条记录，redo日志和undo日志一样都是innodb引擎独有的。功能上redo日志跟binlog较相似就是将操作记录追加进来。redo日志是为了恢复innodb引擎的bufferpool里的数据，binlog是为了恢复整个mysql层面的磁盘里的数据。
5.提交事务，提交事务会将redo缓存区的记录写到redo磁盘文件中，注意此时name的新值还没有更新到磁盘。但此时redo磁盘文件有这个记录，如果中途机器挂了，重启的时候redo文件的数据会恢复的bufferpool里。
6.往binlog文件追加记录。
7.binlog写成功之后，会往redo提交一个commit标志，目的是保证binlog和redo数据一致的。到此为止，这条更新语句完成了它的更新操作，但是name的新值还没写到磁盘呢！看第8点
8.innodb后台会有一个异步线程，不定时的将bufferpool的数据（以页为单位）刷到磁盘（ibd文件）中，这样就保证了bufferpool的数据和磁盘文件的数据是一致的。
也就是说执行器跟innodb打交道实际上就是跟bufferpool打交道，而为了保障bufferpool的功能正常，undo日志提供了事务回滚的功能，redo日志保障了机器故障重启数据不丢失。而最后数据同步到磁盘是后台异步线程将bufferpool的数据刷到磁盘，而且是以页为单位刷到磁盘。这样mysql的操作效率就非常的高，也是对磁盘非常好的保护。
undo日志和redo日志的写入，不但有缓冲而且还是顺序写入性能非常高，而写磁盘文件（ibd）是随机写入的如果不用bufferpool那是性能非常低的。

为什么Mysql不能直接更新磁盘上的数据而且设置缓存池这么一套复杂的机制来执行SQL了？
1.一个请求就直接对磁盘文件进行读写这是随机IO，随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。
2.每个请求都是更新内存BufferPool，然后顺序写日志文件，来保证各种异常情况下的数据一致性。而更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。
正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干的读写请求。