MySQL查询语句和更新语句是如何执行的

大体来说，MySQL可以分为 Server层和存储引擎层

Server层 主要包括连接器、分析器、优化器、执行器以及查询缓存。其中查询缓存 在 MySQL8.0之后就被删除了(因为功能实在是太鸡肋了)

连接器

连接器顾名思义就是管理MySQL和客户端之间的连接的。连接的时候查询一下用户名密码对不对，同时客户端如果太久没有动静的话，连接器也会自动断开，这个时间是由参数 wait_timeout控制的,默认情况下wait_timeout的大小为28800,即8个小时。

mariadb [(none)]> sh show variables like %wait_timeout%;
variable_name value
innodb_lock_wait_timeout 50
lock_wait_timeout 86400
wait_timeout 28800
3  rows in set (0.003 sec)

show processlist 可以展示MySQL连接情况

查询缓存

查询缓存的失效非常频繁，只要有一个对表的更新，这个表上所有的查询缓存就会被清零。对于一个更新压力大的数据库来说，查询缓存的命中率非常低，所以MySQL 8.0版本就直接把查询缓存的整块功能都删除掉了。

分析器

如果没有命中查询缓存，就要开始真正开始执行语句。分析器就是用来做词法分析和语法分析的。

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

执行器

简单一点理解，就是执行SQL语句，判断数据是否符合查询条件。

• 当读取数据的时候，如果数据存在于 **Buffer Poll **中，客户端就会直接读取Buffer Poll中的数据，否则再去磁盘中读取。
• 当修改数据的时候，如果数据存在于 Buffer Poll 中，那直接修改Buffer Poll中数据所在的页，然后将其页修改为脏页，为了减少磁盘I/O,不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。同时修改数据的时候，也会记录undo log日志中，如果是更新操作，需要把被更新的旧值记录下来，这样在回滚的时候，就读取undo log里的数据复现。修改数据的时候，还会记录redo log来保证数据的持久化，其实无论是数据页还是undo log页都需要通过redo log来保证持久化。

其实，执行事务的时候，redo log也不会直接刷入到磁盘中，redo log也有自己的缓存 redo log buffer,每当产生一条redo log时，会先写入到redo log buffer中，后续再持久化到磁盘中。

redo log什么时候刷盘？

• InnoDB 的后台线程每隔1秒，就将redo log buffer持久化到磁盘中
• 当redo log buffer中记录的写入量大于redo log buffer内存空间的一半时，会触发落盘
• MySQL 正常关闭的时候
• 每次事务提交的时候都将缓存在redo log buffer里的redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制）

innod_flush_log_at_trx_commit参数

• 0：每次事务提交，还是将redo log留在redo log buffer中，该模式下的事务提交是不会制动触发写入磁盘的操作的，参数为0的时候，MySQL进程的崩溃会导致上一秒的所有事务数据的丢失。
• 1：默认参数，每次事务提交，都会将redo log buffer中的redo log直接持久化到磁盘
• 2：每次事务提交，只是将缓存在 redo log buffer中的 redo log 写到** redo log 文件中，写入到redo log 文件并不意味着写入到磁盘，因为操作系统的文件系统中有一个Page Cache,Page Cache 是专门用来缓存文件数据的。操作系统会调用fsync**,将缓存在操作系统中Page Cache 里的 redo log 持久化到磁盘。只有在操作系统崩溃或者系统断电的情况下，上一秒的所有事务数据才会丢失。

关于undo log 的刷盘机制

undo log 页就和数据页一样，都被缓存在Buffer Poll中，并参与LRU换入换出机制以及刷脏机制。所以undo log也和普通的数据页一样，都依靠自己的redo log 来保证持久化的

binlog 刷盘机制

事务执行过程中，先将日志写到binlog cache中(Server层的 cache),事务提交的时候，再把binlog cache写到binlog 文件中(其实是内核空间的page cache)

MySQL给每个线程都分配了一片内存用于缓存binlog,该内存叫 binlog cache,参数binlog_cache_size用于控制单个线程所占内存的大小，但是在page cache中只有一个binlog 文件。如下所示，默认大小为32KB。

mysql> show variables like 'binlog_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set, 1 warning (0.02 sec)

sync_binlog参数在MySQL中用于控制二进制日志（binlog）的同步策略，它对数据库的性能和数据完整性有重要影响。该参数可以设置为0、1或大于1的整数，具体作用如下

• sync_binlog=0：MySQL不会在每次事务提交时主动将binlog内容同步到磁盘，而是依赖操作系统的文件系统来缓存和刷新数据，即只writebinlog cache中的数据到内核的page cache中，不主动调用fsync将page cache刷入到磁盘中。这种方式下，性能最好，但如果发生系统崩溃，可能会丢失最近的事务数据。
• sync_binlog=1表示每次提交事务都会write，然后马上执行 fsync
• **sync_binlog=N（N>1)**：表示每次提交事务都 write，但累积 N 个事务后才fsync