MySQL语句的执行过程你还不知道吗？

MySQL数据库分层

大致可以分为两层：服务层和存储引擎层

以下从分层的思想，讲解一条查询语句的执行流程

服务层

链接层

链接层：当客户端来连接数据库时，首先由链接层接收到请求，它首先验证账号和密码是否正确，如果正确，则就开始读取该账号的权限，并进行保存，之后的请求都会根据这里的权限来判断是否拥有权限

查询缓存

首先，MySQL数据库8.0以下的版本是存在缓存功能的，但默认是关闭的，如果开启了缓存功能，则会优先去缓存查找，如果查到了，就直接返回，如果没找到，再去到下一个模块。MySQL数据库8.0版本已经将缓存功能删除了，原因很简单，缓存的功能应该由专业人士去处理，例如Redis等，而不是在关系型数据库中处理

分析器（解析器）

分析器模块对SQL语句进行解析，然后检查SQL语句是否能被正确执行，其中主要有词法分析和语法分析

预处理器

预处理器主要对SQL语句进行语义分析，检查SQL语句是否有歧义，例如，表是否存在，别名是否存在

优化器

当上面都完成后，改SQL语句就已经定型了，MySQL也知道你想要什么了，就可以开始解析SQL语句了，但是SQL语句的执行路径有很多（例如多表关联的时候，先关联哪个表效率更高），这个时候就需要优化器来工作了，优化器会选择一条它认为最优的执行路径去解析SQL语句，但是请注意，优化器并不是万能的，它并不会优化所有的SQL语句，而且有可能经过优化器后，效率更低，所有大家还是应该注意自己的SQL语句的写法，减少优化器的使用

执行计划

执行计划模块，会根据优化器给出的最优执行路径打印出执行计划，这部分你可以通过explain关键字查看到，请注意，这个时候并没有真正的去执行SQL语句

执行器

到了执行器模块后就开始真正执行SQL语句，首先判断是否有执行的权限，如果有，则调用存储引擎层的接口拿到对应表的数据，请注意，只有基于索引的查询才会在存储引擎层进行过滤，其它的数据都只能在服务层进行过滤筛选，拿到数据后返回给客户端，至此一条select语句执行完毕

存储引擎层

在MySQL中提供了很多种不同的存储引擎，例如有innoDB，MyISAM等，这些存储引擎提供了不同的存储数据的方式，例如InnoDB有两个文件.frm表结构和.ibd数据+索引，MyISAM有三个文件.frm表结构、.MYD数据，.MYI索引，那么执行器怎么从这么多的存储引擎中拿到数据呢？这就要归功于MySQL的存储引擎层的实现，MySQL中所有的存储引擎都必须暴露相同的（增删查改）AIP给存储引擎层调用，这样执行器就只要调用存储引擎层，就可以拿到相应的数据了

一条修改语句的执行过程

在MySQL的底层将删除修改更新操作统称为修改操作，这一点可以在mybatis的源码中体现

页的概念

当服务层向存储引擎层拿数据时，并不是数据多大，就取多大的数据，而是会有一个单位，就是页，每次向存储引擎层拿到一页的数据（16KB），前面我们也说过存储引擎层只负责数据的提供，并不会筛选数据，所以服务层拿到这一页数据之后，自己再做筛选和过滤，拿到自己真正想要的数据

Buffer Poll

假设第一次向存储引擎层拿到了一页的数据，之后又去拿这一页数据，重复在磁盘读取效率并不高，所以在存储引擎层就引入了buffer poll内存缓冲池

对于读取操作，先将数据放入到buffer poll中，读取数据时优先去buffer poll中读取

对于修改操作，从存储引擎层拿到数据，在服务层中修改，然后优先将数据存储到buffer poll中，之后再由另外一条线程将buffer poll中的数据同步到磁盘

那么buffer poll中的数据页和硬盘的数据页不一致我们称之为脏页，由线程将buffer poll中的数据同步到磁盘的操作叫做刷脏

redo log日志

因为buffer poll是内存缓冲池，是放在内存中的，一旦机器断电或者其它的情况崩溃了，脏页没有及时同步去硬盘，那不是会造成数据丢失和数据不一致的情况？

对于innoDB存储引擎是有崩溃恢复的能力的，对了，就是redo log日志帮我们做崩溃恢复，它是这样工作的，当修改的数据存入buffer poll后，再将数据存入redo log中，一旦发生崩溃后，下次开机就可以利用redo log中的数据同步到硬盘做崩溃恢复

这里可能就有小伙伴要说了，你在写入buffer poll之后还要写入redo log日志，也一样是硬盘，我还不如直接写入硬盘，这里就需要科普一下了，一般操作磁盘有两种io操作，一种是随机io，一种是顺序io，在特殊情况下顺序io的效率是可能比内存操作还要快的，redo log正是利用了这一点，而直接写入硬盘就不一样了，每个表的表结构不一样，修改后的数据再存入肯定是采用随机io操作的，磁盘寻址就非常耗时

undo log日志

该日志是一个逻辑日志，主要帮助进行撤销和回滚，什么叫逻辑日志？例如一条insert语句，在日志中就会记录对应的delete语句，在写入redo log时，同时会将逻辑语句写入undo log日志中

bin log日志

该日志存在于服务层，也就是说bin log并不受存储引擎的影响，所有的存储引擎都可以使用

bin log也是一个逻辑日志，它会存储DDL和DML语句，但是和redo log不一样，它可以进行全量备份，而redo log只能存储固定大小的数据，如果超过了就只能覆盖前面的数据

bin log主要用来进行数据恢复和主从复制，bin log默认是关闭的

总结一条修改语句的执行过程

假设执行了一条update语句，开启事务，首先经过前面查询操作，把符合要求的数据全部拿到服务层来

服务层进行筛选之后，将符合要求的数据进行修改，然后将数据发送给存储引擎层更新到buffer poll中

记录undo log和redo log，将redo log的记录状态改成prepare，

通知服务层可以提交事务了，服务层将语句写入到bin log中，然后提交事务

redo log收到事务提交的通知后，将该条事务的状态改成commit

为什么redo log要使用两阶段提交？

在修改语句的执行过程中我们可以看到，redo log是先将事务的记录改成prepare，等到事务提交后，再将状态修改成commit，为什么要这样做，这样做的好处是什么？

首先引入redo log的目的就是为了做崩溃恢复的，而bin log是为了做备份的，如果redo log没有使用两阶段提交的话，就可能导致redo log中记录的值和bin log中记录的值不一致，所以必须等到redo log写完bin log也写完了，才会让redo log真正提交

如果redo log写入成功了，而bin log写入失败之后会直接利用undo log回滚事务，这样redo log也不会更新状态到commit，也就保证了数据的一致性