大纲内容

  • mysql的内部组件结构
  • select的执行流程
  • update的执行流程
  • redoLog和binLog
  • 面试问题

读书才能够系统的学习，听别人偶尔的两句断章取义的结论，远远不能够支撑设计一套系统。:

博主建立了一个群, 有兴趣的可以一起一起分享问题, 解决问题, 分享面试题, 内推.

MySQL的内部组件结构

mysql 设置查询结果作为更新值_mysql 设置查询结果作为更新值

Server层：包含连接器，分析器，查询缓存，优化器，执行器

连接器

作用： 管理链接，用户权限验证。
管理链接：分为长链接和短链接，一般推荐使用长链接
用户权限验证：链接建立成功，去mySQL的用户表中查看用户的权限，若在MySQL正常启动中，修改了用户的权限，则需要重启才能获取最新的权限。

建立链接： 数据库服务器跟客户端建立链接，需要经历TCP三次握手。

长链接：用一个链接可以处理多个请求命令，当数据库一直被访问时，可以使用同一个链接，减少反复建立链接带来的开销，使用长连接需注意：要避免长连接长期不断开的问题，会一直消耗内存，应该适当的断开长连接。

短链接：一个请求的开始和一个请求的结束，这样的生命周期称之为短链接，再来一个新的请求需要重新建联，使用短链接需注意：短链接风暴，在业务高峰期时，可能出现链接数暴增，每一次建立链接需要TCP三次握手，并且还要验证用户权限，固时间成本很高。
解决短链接风暴主要有三点：

  • 使用max_connection参数，控制链接数，如果超过了则会提示“ too many connection”，但抛出业务异常，业务系统是不允许的。
  • 及时处理掉一些站着链接却不适用的线程
  • 关闭所有用户权限验证这一步骤，可以减少消耗，但风险极高。

分析器

作用：语法分析，词义分析，词法分析，目的直到要干什么

跟JVM中类加载过程中的验证步骤类似，主要是验证书写+关键字是否正确
比如执行比如执行select，MySQL知道要进行查询，比如执行update，MySQL知道要更新。

若关键字存在错误, 列存在错误, 主要关注报错信息"… user near" 附近的内容。(下例: 故意把select关键字写错成了 selet)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selet * from invoiceapplyrecord' at line 1

查询缓存

当分析器知道要进行Select查询时，会优先查询缓存，若命中则直接返回结果，若未命中则走优化器->执行器->执行引擎中查询，把查询到的结果以key-value的形式存储在缓存中，key是查询语句，value是结果。
此功能十分鸡肋：当表中的数据被修改，跟表相关的所有缓存记录都是失效的，针对于更新频繁的表，查询缓存是没有意义的，只适用于一些配置表字典表。mysql8.0版本以上移除了此功能。

优化器

作用：执行计划的生成，索引的选择，目的：知道怎么做
索引选择：当搜索条件中存在多个索引的时，决定使用哪个索引，可能存在有索引条件但并未用到索引查询，原因是：优化器觉得全表扫描比索引扫描的效率要高。

在我们实际面试中的场景：假设(age, name)是联合索引
select * from user where name="peter" and age="18"

复合索引遵循索引最左前缀原则，但优化器帮我们调整了索引顺序。

执行器

目的：从执行引擎中获取读写的结果，binlog模块存在执行器中。
判断是否存在查询当前表的权限
若没有权限，则抛出没有查询此表的异常
若存在权限，则根据表中的存储引擎，一条一条的查询并判断，把符合的结果集统一返回给客户端。
个人理解： slow log中精准扫描了多少行记录，都是由执行器来进行判断的。

执行引擎层：包含执行引擎，提供读写方法

一般默认是InnoDB引擎，还有MyISAM，Memory等其他引擎，提供读和写的操作， 其中InnoDB存在Redolog模块。

执行引擎都是以页未单位读取磁盘数据文件的，执行器都是以行的数据读取执行引擎，提供判断逻辑。

·

select的执行流程

假设查询语句：select * from user where name = “peter”，name没有索引
执行器去查找执行引擎的步骤如下
1：调用InnoDB引擎接口取表中的第一条数据，判断name是否未peter，如果不是则跳过，如果是，则将这条记录存在结果集中
2：重复1中的操作，直至全表扫描结束
3：执行器将上述所有成功存在的记录都汇总成结果集返回给客户端。

注意：执行引擎都是以页为单位读取磁盘数据的，执行器调用执行引擎的读接口读取一行数据，其中执行引擎提供取值逻辑，执行器提供判断逻辑。

update的执行流程

假设查询语句：update user set age=30 where id =1
1：执行器首先根据id=1查找这一行数据，如果这一行在内存中，则返回给执行器，如果不存在，则让InnoDB以数据页为单位去查找磁盘文件。
2：获取结果，将age的值设置为30，事务准备提交时，执行器调用执行引擎的写方法。
3：执行引擎调用写方法时，先把更新的数据写进redolog日志中，此时redolog日志状态为prepare状态、
4：执行器生成binlog日志（先write进page cache，再fysn进磁盘中）。
5：事务提交时，将redolog日志的状态从prepare改为commit状态，更新结束。

redolog日志

目的：当服务器瘫痪时，为了恢复内存中的脏页数据，而redolog中记录的是未同步进磁盘数据文件中的脏页数据，具备crash-safe能力，记录数据页做了什么操作，固定大小，是固定写。
crash-sale：1：所有提交成功的事务会一直存在，保证恢复数据。2：所有未提交的食物会自动回滚，丢了就丢了。

  • 脏页： 当内存中的数据页和磁盘文件中的数据页不一致时。
  • 干净页：当内存中的数据页和磁盘文件中的数据页一致时。
  • redolog具备WAL技术，先写日志，再写磁盘，目的是为了减少磁盘IO的消耗次数。
  • 触发Flush机制，会STW（暂停用户线程的DML操作），此机制是为了把内存中的脏页同步进磁盘文件中，保证干净页。
1：当系统空闲时，会触发flush，将内存中的脏页数据同步进数据磁盘文件中，同时使用checkPoint指针再redolog日志上擦去脏页数据
  	2：当数据库正常关闭时，会触发flush，同步+checkpoint同上。
  	3：当redolog日志文件满时，会触发flush，同步+checkpoint同上。一般redolog分四个小块，每一块可以给1GB，
  	如果redolog太小，发挥不出来WAL技术，checkpoint像个时钟上的秒表，走一段距离表示同步(擦完)完多少数据。
  	4：当系统内存满时，会触发MySQL的淘汰机制，更新最近未使用的数据页，若数据页为脏页，则同步进磁盘数据文件中，
  	若数据页为干净页，则直接释放。同步+checkpoint同上。

binlog日志

目的：主从备份数据，归档作用，保证MYSQL高可用，当服务器故障时，可提供恢复，binlog没有大小限制，是追加写操作。
有两种数据结构可供选择：statement+row，statement是保存执行的SQL语句，row是记录两条行的内容，即更新前和更新后。

  • binlog写入机制
事务提交中，先把日志写入到binlog cache中，事务提交时，把binlog cache写到binlog日志中。
  一个事务所有的操作都要同时写进一个binlog cache中，每个线程独有一个binlog cache。
  此缓存的效用由binlog cache size控制，若超过了设置值，则暂存临时文件中，带事务提交后，
  把binlog cache中的数据通过write同步进binlog日志中(此时还在page cache中)，同时清楚binlog cache空间，
  然后通过fysn同步进磁盘文件中。
  write和fync操作都是参数设置
  1： fysnc_binlog=0时，标识只write，不fync
  2： fysnc_binlog=1时，即write，也fync
  3： fysnc_binlog=N时，N就是有几个事务提交，当达到了N时，统一fsync。

redolog和binlog是相互配合的两个日志，基于两阶段提交的场景：先redolog->再binlog->再redolog
如果redolog里面的事务是完整的，则说明有commit标志，则说明binlog也写入成功，则直接提交事务。
如果redo里面只存在prepare状态，则判断对应的binlog日志是否完整，如果完整，则直接修改prepare->commit，并提交任务，如果不完整，则直接抛弃事务，不会造成数据不一致问题。
注意：redolog和binlog是通过唯一事务Id来进行关联。

面试题

redolog和binlog有什么区别？

1：redolog是InnoDB独有的日志模块 ，而binlog是所有执行引擎都有的日志模块
  2：redolog是固定大小，是固定写，要设置隔离的内存大小，binLog是追加写，写完再接着写。
  3： redolog的目的是为了减少磁盘Io的消耗次数，若redolog中数据存在，说明未被checkPoint擦进数据磁盘文件中，能保证数据库故障时，
  恢复内存中的脏页数据，而binlog的目的是为了恢复原始数据，无法挥发内存中脏页的数据。

更新操作的两阶段提交有什么作用?

两阶段提交能保证数据的一致性。
  反证法1：假设先提交redolog，再提交binlog
  redolog提交成功后，数据库故障了，导致binlog未写入，此时数据库恢复，redolog具备crash-safe能力，能恢复内存中的脏页数据，
  但Binlog此时并未记录这条记录，恢复数据时，会覆盖点最新的数据，造成数据不一致性问题

 反证法2：假设先提交binlog，再提交redolog
 binlog提交成功后，数据库故障了，导致redolog未写入，即没有prepare状态，也没有commit状态，mySQL会认为事务提交失败了，会回滚对应的数据
 但binlog中记录了最新的记录，恢复数据时，还是会造成数据不一致性问题。

MySQL如何直到binlog是完整的

1：MySQL提供checkSum参数来判断binlog是否完整
 2：针对于statement格式的binlog，结尾若存在commit标识，则说明是完整的。
 3：针对于row格式的binlog，会存在一个XID event。