SQL语句执行过程

  • 1，查询语句执行过程
  • 2，修改语句执行过程
  • 2.1 update执行过程
  • 2.2 先写入redo log，后写入binlog
  • 2.3 先写入binlog，后写入redo log
  • 2.4，两阶段提交（prepare&commit）
  • 3，插入语句执行过程
  • 3.1 change buffer
  • 3.2 插入一条数据
  • 4，删除语句（物理删除）


1，查询语句执行过程

mysql基本逻辑架构图

mysql 语句块执行 mysql语句执行过程_数据


连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接过程相对复杂，尽量使用长连接方式，但是也要避免长连接累积导致内存得不到释放，最终OOM，可以在连接处理大资源后执行 mysql_reset_connection 重新初始化连接。此过程不需要重连和重新做权限验证，将连接恢复到创建完时状态。

查询缓存
MySQL拿到一个查询请求后，先找缓存，执行过的语句可能会以key-value对的形式缓存在内存中。key是查询的语句，value是查询的结果。下次查询缓存中找到key，那么这个value就会直接返回。查不到缓存中，就会继续后面的执行阶段，结果会被存入缓存。
但缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。在mysql8.0版本之后删除了查询缓存。

分析器
分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL会识别字符串代表什么，做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断SQL语句是否满足MySQL语法。

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

执行器
MySQL通过分析器知道做什么，通过优化器知道该怎么做，就进入执行器阶段，开始执行语句。开始执行的时候，先判断对这个表有没有执行权限，没就抛异常。执行器会调用对应引擎接口，取这个表的第一行，如果不是则跳过，是则将这行存在结果集中；调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

2，修改语句执行过程

2.1 update执行过程

在MySQL执行更新语句时，都会涉及到redo log日志和binlog日志的读写。一条更新语句的执行过程如下：

mysql 语句块执行 mysql语句执行过程_执行流程_02

从上图可以看出，MySQL在执行更新语句的时候，在服务层进行语句的解析和执行，在引擎层进行数据的提取和存储；同时在服务层对binlog进行写入，在InnoDB内进行redo log的写入。

不仅如此，在对redo log写入时有两个阶段的提交，一是binlog写入之前prepare状态的写入，二是binlog写入之后commit状态的写入。

之所以要安排这么一个两阶段提交，自然是有它的道理的。现在我们可以假设不采用两阶段提交的方式，而是采用“单阶段”进行提交，即要么先写入redo log，后写入binlog；要么先写入binlog，后写入redo log。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。

2.2 先写入redo log，后写入binlog

mysql 语句块执行 mysql语句执行过程_数据_03

在写完redo log之后，数据此时具有crash-safe能力，因此系统崩溃，数据会恢复成事务开始之前的状态。但是，若在redo log写完时候，binlog写入之前，系统发生了宕机。此时binlog没有对上面的更新语句进行保存，导致当使用binlog进行数据库的备份或者恢复时，就少了上述的更新语句。从而使得id=2这一行的数据没有被更新。

2.3 先写入binlog，后写入redo log

写完binlog之后，所有的语句都被保存，所以通过binlog复制或恢复出来的数据库中id=2这一行的数据会被更新为a=1。但是如果在redo log写入之前，系统崩溃，那么redo log中记录的这个事务会无效，导致实际数据库中id=2这一行的数据并没有更新。

mysql 语句块执行 mysql语句执行过程_日志系统_04


两阶段的提交就是为了避免上述的问题，使得binlog和redo log中保存的信息是一致的。

2.4，两阶段提交（prepare&commit）
  1. 当在写binlog崩溃时，重启后恢复：后发现redolog没有commit，通过binlog恢复也没有（事务没有得到提交），保持了一致；
  2. 当在commit时崩溃，重启后恢复：没有commit，但满足prepare和binlog完整，所以重启后会自动commit，保持了一致

3，插入语句执行过程

3.1 change buffer

redo log 节省的是随机写磁盘的 IO 消耗（转成顺序写），change buffer 节省的是随机读磁盘的 IO 消耗。

要更新一个数据页时，数据页在内存中就直接更新（适用于普通索引，不适用于唯一索引）
数据页还没有在内存中的话，在不影响数据一致性前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入数据页。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
change buffer，实际是可以持久化的数据。change buffer 在内存中有拷贝，也会被写入到磁盘。
change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。
访问这个数据页会触发 merge ，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作（因此适用于写多读少业务，写完立刻读就会触发merge导致change buffer没有作用）
如能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。
3.2 插入一条数据

当前处理的数据目标页在内存中（4,4000）

对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。
普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

当前处理的数据目标页不在内存中

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 能减少随机磁盘访问，对更新/插入性能的提升会很明显

4，删除语句（物理删除）

delete 命令只把记录的位置，或数据页标记为“可复用”，磁盘文件的大小是不会变的（近似理解冠希同学的问题）
数据空洞 ：如果删除后被标记的位置没有被复用，就会形成数据空洞（如果数据随机插入也可能形成空洞现象，可能造成数据页分裂）
收缩表空间：通过 alter table 命令重建表，才能达到表文件变小的目的，MySQL 5.6 版本引入 Online DDL，对重建表做了优化（支持不停机重建）。

Online DDL重建表的流程

mysql 语句块执行 mysql语句执行过程_事务隔离_05

建立一个临时文件，扫描表 A 主键的所有数据页；
用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
用临时文件替换表 A 的数据文件。

重建表
从 MySQL 5.6 开始，alter table t engine = InnoDB（也就是 recreate）默认的就是Online DDL；
analyze table t 不是重建表，只对表的索引信息做重新统计，没有修改数据，过程中加了 MDL 读锁；
optimize table t 等于 recreate+analyze。

Online 和 inplace

DDL 过程如果是 Online 的，就一定是 inplace 的；
反过来，inplace 的 DDL，有可能不是 Online 的，如添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。

参考：文章节选自 极客时间《MySQL45讲》 如有不当，连续侵删