一. MySQL基础架构：一条SQL查询语句是如何执行的

基础架构：一条SQL查询语句是如何执行的

MySQL可以分为Server层和存储引擎层两部分。

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

存储引擎层负责数据的存储和提取，其架构模式是插件式的，支持InnoDB，MyISAM，Memoory等多个存储引擎。现在最常用的存储 引擎时InnoDB，它从MySQL5.5起开始成为了默认存储引擎。

1.连接器

执行SQL前，首先需要连接到这个数据库上，这时候接待你的是连接器。连接器负责跟客户端建立连接，获取权限，维护和管理连接。连接命令一般是这么写的：

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

数据库里面有长连接和短连接。但是建立连接的过程通常比较复杂，建议尽量使用长连接，减少连接的次数。

2.查询缓存

连接建立完成后，执行逻辑就来到第二步：查询缓存。

MySQL拿到一个查询请求后，会先到缓存查询看看，之前是不是执行过这条SQL语句。之前执行过的SQL和结果以key-value的形式，被缓存在内存中。key是执行的sql语句，value是查询的结果。如果查询能在缓存中找到key，哪个这个value就会直接返回给客户端。

如果语句不在查询缓存，就会继续执行后面的阶段。执行完成后，执行结果就会被添加进缓存。如果查询命中缓存，MySQL不需要执行后面的操作，查询效率很高。

但是在大多数情况下，不推荐使用缓存。查询缓存的更新非常频繁，只要有对一个表的更新，这个表上的所有查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

MySQL8.0已经将查询缓存整块功能删掉了。

3.分析器

如果没有命中查询缓存，就要开始真正执行语句了。首先MySQL要对 SQL 语句做解析。

分析器首先会做“词法分析”。输入的是由多个字符串和空格组成的一条SQL语句，MySQL首先需要识别出里面的字符串分别是什么，代表什么。

做完了这些识别之后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

4.优化器

经过了分析器，MySQL就知道要做什么。在开始执行之前，还要先经过优化器的处理。

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

有多种执行方法可以选择，这两种方法的逻辑结果是一样的，但是执行效率有所不同。优化器的作用是选择效率最高的方案。

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

5.执行器

MySQL通过分析器知道了要做什么，通过 优化器知道的了要怎么做。接下来就进入执行器阶段，开始执行语句。

select * from T where ID = 10;

比如表T中，ID字段没有索引，那么执行器的执行流程是这样的：

• 调用 InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中
• 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取的到这个表的最后一行。
• 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

在数据库慢查询日志中有一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是执行器每次调用引擎获取数据行的时候累加的。

思考：

mysql> select * from user where k = 1;
ERROR 1054 (42S22): Unknown column 'k' in 'where clause'

这个错是从分析器报出来的。

SQL更新语句的流程

MySQL更新流程涉及两个重要的日志模块:redo log（重做日志），bin log（归档日志）

redo log（WAL技术）

redo log的作用是为了提高MySQL的更新效率。如果每次更新操作都需要哦写进磁盘，然后磁盘也要找到对应的那条记录，然后更新。这个过程IO成本，查找成本都很高。为了提高效率，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这时候就算完成了。同时，InnoDB会在适当的时候，将这个操作记录更新到磁盘里面，这个更新往往是在系统比较空闲的时候完成。

如果一段时间的更新特别多，redo log被写满了。这时候即使系统很忙，也要将redo log写到磁盘里面去。

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢。这个能力成为 crash-safe。

bin log

从MySQL整体来看，其实就是两块。一块是server层，它主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们讲到的redo log是InnoDB引擎特有的日志，而server层也有自己的日志，称为binlog。

为什么会有两份日志：
MySQL自带binlog，binlog只能用于归档，没有crash-safe的能力。InnoDB引擎使用了redolog来实现crash-safe能力。

binlog和redolog的不同：

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

redo log与刷脏页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

当redo log写满了，要flush脏页。出现这样情况的时候，整个系统就不能再接受更新了，所有的更新都被堵住。

update语句的流程

mysql> create table T(ID int priamry key, c int);
mysql> update T set c = c + 1 where ID = 2;

1.执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索到这一行。如果ID=2这一行的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2.执行器拿到引擎给的行数据，把这个值加上1。得到新的数据，再调用引擎接口写入这行新数据。

3.引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告诉执行器执行完了，随时可以提交事务。

4.执行器生成这个操作的binlog，并把binlog写入磁盘。

5.执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成commit状态，更新完成。

redo log的写入拆成了两个步骤：prepare和commit，这就是“两阶段提交”。

为什么必须有“两阶段提交”

为了让两份日志之间的逻辑一致。如果不使用2pc，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

update T set c = c + 1 where ID = 2;

1.先写redo log后写binlog。

假设redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于redo log有crash safe能力，redo log写完之后，即使系统奔溃，仍能够把数据恢复过来。所以恢复后这一行c的值是1。但由于binlog没写完就crash了，这时候binlog里面就没有记录这条语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。如果使用这份binlog来恢复临时库的话，由于更新语句的binlog丢失，这个临时库就少了一次更新。恢复出来的数据和原来的库不一样。

2.先写binlog后写redo log

如果binlog写完之后crash，崩溃以后这个事务无效，所以这一行c的值是0.但由于binlog里面已经记录了‘c=c+1’。所以，在之后用binlog来恢复的时候就多了一个事务出来。恢复出来的数据和原来的库不一致。

redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案。

配置参数：

innodb_flush_log_at_trx_commit  //设成1表示每次事务的redo log都直接持久化到磁盘。建议设成1，可以保证MySQL异常重启之后数据不丢失。

sync_binlog // 设成1表示每次事务的binlog都持久化到磁盘。建议设成1防止binlog丢失