mysql学习笔记（一）sql语句执行

一、mysql执行模块

先来了解下mysql的执行模块，如下图所示：

我们可以看到mysql分为Server层和存储引擎两部分。Server层包含了连接器、缓存、分析器、优化器、执行器，并且所有的存储过程、触发器等存储功能都在这一层实现。

存储引擎负责数据的查询和存储。我们一般用的mysql存储引擎默认都是InnoDB。

下面是创建表的语句，我们可以看到如何设置存储引擎。

· 各个执行模块的作用

（1）连接器 - 管理连接，权限验证。

（2）缓存 - 查询缓存，命中则直接返回结果。

（3）分析器 - 语法分析，词法分析。

（4）优化器 - 执行计划生成，索引选择。

（5）执行器 - 操作引擎，返回结果。

（6）存储引擎 - 存储数据，提供读写接口。

二、连接器

连接器的作用，主要是管理连接，权限验证。

（1）管理连接

当客户端连接到数据库上时，需要输入连接ip、端口、用户名和密码。输入后连接器开始工作，首先会认证我们的身份，即校验输入的用户名和密码。如果用户名或者密码不正确，就会收到一个“Access denied for user 'XXX'”的错误。如下图所示：

（2）权限验证

用户名和密码校验通过后，连机器会进行第二步操作权限验证。即会到权限列表里查询你的权限。后续的操作都会依赖此时查询出来的权限。

因为权限是先查询，因此当你处于连接中权限被修改后，你的权限不会立刻刷新，只有重新建立新的连接，才会查询你的权限，使用最新的权限。

（3）连接断开

经过步骤一和步骤二，就连接上了数据库。但是如果客户端长时间没有操作，连接器就会自动断开，这个时间是由参数 - wait_timeout控制的，默认为8小时。

三、缓存

缓存主要是查询缓存，命中则直接返回结果。

select id from user where name = '张三';以该sql为例子，mysql会将该查询语句去查询缓存中查看，是否可以命中，如果命中则直接返回缓存中的结果。

如果该sql之前执行过，会以key-value的形式存储在查询缓存中，key为查询sql语句，value为语句执行的结果。

· 查询缓存的缺点

这里使用查询缓存并不是最优的，因为如果需要使用缓存，我们一般会考虑这个缓存的命中率，而一般来说mysql的查询命中率是非常低的，除非这张表中存的是一些静态的配置数据，很长时间才会更新一次，这样的表使用查询缓存比较有意义。

第二点，查询缓存的失效非常频繁，当这张表被更新，那么这个表上所有的查询缓存都会被清空，所以对于频繁更新的表使用查询缓存，他的缓存命中率极低。

在mysql8.0以上的新版本直接选择将查询缓存的整个模块删掉了。

四、分析器

分析器的作用主要是进行语法分析，词法分析。

从分析器开始真正的进入sql语句执行的第一步，解析sql语句。

比如select id from user where name = '张三';

（1）词法分析

识别select、update、delete、insert关键字，如果是select表示是一个查询语句，会将from后面的user识别出来作为表名，将name识别出来作为列名。

（2）语法分析

词法分析完毕后，开始进行语法分析，主要根据词法分析的结果，再根据语法规则判断语句是否满足语法规则。

如果语法分析错误就会出现'You have an error in your SQL syntax'错误信息。

五、优化器

优化器的作用主要是执行计划生成，进行索引选择。

比如select * from user where name = '张三' and phone = ‘110’语句，并且name和phone各建立了索引。

优化器对执行方法进行判断。

（1）先查找表中name为张三的数据，然后从name为张三的数据中查找phone为110的数据。

（2）先查找表中phone为110的数据，然后从phone为110的数据中查找name为张三的数据。

虽然上述的结果都是一样的，但是sql执行的效率肯定是不一样的，优化器的作用就是选择选择合适的执行方案。

六、执行器

执行器的作用主要是操作引擎，返回结果。

（1）校验权限

执行之前先判断对表的查询有没有查询权限，如果没有就会出现‘

SELECT command denied to user 'b'@'localhost' for table 'user'

’错误信息。

（2）执行语句

调用存储引擎的读接口开始执行。

如果没有命中索引：

首先会调用innoDB引擎接口取这个表的第一行数据判断是否满足条件， 如果不是则跳过，如果是则将这行结果存储在结果集中。继续调用引擎 接口取下一行，直到取到这个表的最后一行。最后执行器将结果集返回 给客户端。

如果命中缓存：

大致过程相似，调用innoDB引擎接口取第一行数据会从索引叶的第一行 数据开始取数据判断，第二行会从索引叶的第二行数据判断。其余过程 一样。

select查询相关的语句的执行比较容易理解，主要理解了上述mysql的执行模块就可以理解select语句是如何执行的。更新语句的执行，除了mysql的执行模块外还有2个重要的日志模块（redo log、binlog），可以通过日志模块将mysql的数据恢复到近期任意一秒的状态。

七、存储引擎日志 - redo log

redo log中主要存储的是物理数据，发生在存储引擎中。主要目的是为了解决出现大量更新操作时，减少频繁更新操作频繁写入磁盘，减少IO成本。

具体操作如下：

（1）当有一天记录要进行更新操作时，InnoDB会先把记录写到redo log中，并更新内存，此时更新的语句就算完成。

（2）innoDB引擎会在合适的时候，将这些更新操作记录更新到磁盘中，并擦除redo log记录。我们可以发现，磁盘写入的频率由多次，骤降为了单次。减少了IO成本。

· 如何处理redo log容量满的情况

由于innoDB的redo log的大小是固定的，因此当redo log出现满的情况，需要采用合适的方式将redo log中的内容更新到磁盘中，并进行擦除操作。

如下图所示：

write_pos表示当前记录的位置，一边写一边顺时针向后移动。check_point表示当前要擦除的位置，擦除记录前需要把记录更新到数据库中，他也是循环顺时针向后移动。write_pos和check_point空着的部分就是可以记录新的日志的空间。

如果当write_pos和check_point指向同一个空间，表示没有容量可以用来记录新的日志。那么就不能再执行新的更新，mysql会先优先保障check_point的推进，将redo_log日志中的部分数据更新到mysql中，有了写入空间再开始执行更新操作。

有了redo_log日志，可以保证数据库遇到异常或者重启，之前提交的更新操作都不会丢失，遇到异常重启后，仍可以通过redo_log日志恢复更新操作的数据到数据库中。

八、Server层日志binlog

Server层也有日志即binlog归档日志。binlog日志没有固定大小，当binlog文件到达一定大小后会切换到下一个进行写入，并不会覆盖之前的日志记录。

redo_log是物理日志，记录的是数据 - 即某个数据页上做了什么修改。而binlog是逻辑日志，记录的是这个语句的原始逻辑，比如给id=1这一行的score字段加100。

有了binlog也可以保证我们快速恢复近期某一时刻的数据。

九、update语句执行总结

update语句的执行流程（update user set name = '李四' where id =1）。

（1）执行器先调用存储引擎接口找到id=1这一行，由于id是主键，直接会通过主键索引找到这一行。如果这一行在内存中，直接返回给执行器。如果不在内存中会先从磁盘中读入到内存，再返回给执行器。

（2）执行器拿到数据后将name设置为'李四'，得到新的数据，再调用存储引擎的写接口写入新数据。

（3）存储引擎将新数据更新到内存中，并将操作记录记录的redo_log日志中，此时redo_log处于prepare状态，然后告诉执行器处理完毕。

（4）执行器得到返回结果后，会生成这个操作的binlog，并把binlog写入磁盘。

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

十、总结

· 更新语句为什么需要两阶段提交？

假设没有两阶段提交，分为2种情况。

（1）redo log写入成功，binlog写入失败。

redo log写入成功，但是在写binlog时出现异常导致mysql重启。虽然重启后mysql仍可以恢复到最新的数据。但是此时的binlog缺少了执行这一条更新sql的日志，如果需要用binlog恢复临时库的时候，会导致少了这条更新sql日志导致最终结果出现不一致的情况，与原库的值不同。

（2）binlog写入成功，redo log写入失败。

binlog写入成功，redo log写入时出现异常导致mysql重启。重启后mysql的由于redo log日志缺失这条更新sql，所以此时的数据库的值已经是错误的了。同理，使用binlog恢复临时库的时候，即使通过binlog恢复的结果是正确的，但是也会与原库的值不同。

使用两阶段提交，实际上和事物一样，保证要么全部成功，要么全部失败，保证2份日志一致。

· 之前第一次学完mysql的日志模块，我觉得binlog很多余，不知道有没有人和我一样的想法，实际上目前不能只使用redo log日志。

一方面是上文中也提到了redo log日志大小是固定的，并且是循环写的，就会导致之前的日志数据会被覆盖不能持久保持，因此假如要恢复近期某一时刻的数据单单用于redo_log日志是没法完成的。

另一方面是redo_log只有innoDB有，别的引擎没有。所以需要配合redo log日志和binlog日志达到数据完整。