mysql基础——sql 执行流程

内容

• 一条sql查询语句如何执行
• 一条sql更新语句如何执行

一 1条sql查询语句如何执行

1.1 执行流程
例如：select * from t where id=1 这样一条简单语句mysql是如何执行的呢？引用《丁奇45讲》里第一讲的mysql 基本架构示意图，简单说明一下：

image.png

• 当Mysql客户端连接到server时，首先接处到的是连接器，连接和tcp完成三次握手后，会对这个连接进行权限验证，验证密码 用户名是否正确;
• 连接完成后，会查询缓存，如果命中缓存，则直接返回；
  • mysql8.0以前会对sql和结果做一个缓存，key是sql语句，value是查询结果，8以后则去掉了缓存版本，因为缓存弊大于利，只要有一个更新语句更新了表，则整个表相关的查询语句缓存都会失效
• 如果没有命中缓存，则进入分析器模块；分析器会分析sql语句语法、语法是否正确；
• 接下来进入优化器，优化器会生成具体的执行计划，相应的索引选择等；
• 最后执行器根据优化器生成的执行计划，调用存储引擎存储接口获取数据；

1.2 其他知识点

• 建立连接后，本条连接的拥有的权限已经确定，之后即使管理员再对该连接的用户进行权限修改，但是不会影响到该连接拥有的权限，之后建立的连接才生效；
• 连接建立后，如果client长时间不进行操作，在等待wait_timeout设置的参数后，server会断开连接；
• mysql临时使用的内存是存储在连接对象里，所以长时间使用长连接后会出现mysql占用内存涨的特别快现象，这种情况可以：
  • 定期断开长连接
  • mysql5.7或是以后板块，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

二 一条更新语句如何执行

 update t set c = c+1 where id=2

• 更新语句还是会像查询语句一样，先找到id=2这条记录，找到id=2这条记录后，后续的执行流程大体如下：

  
  image.png
• 执行器拿到id=2这条数据后，修改值，将新行数据更新到内存；
• 同时将修改写入到redo 日志，redo日志是先写入到redo log buffer里，redo log buffer是一块内存，然后将redo 日志置于prepare状态[redo log最终也是存储在磁盘上，但是是顺序写入，速度很快]；
• 写入binlog；
• binlog写入成功后，提交整个事务，然后redo 日志状态置于commit，完成这个更新；
  补充：如果把 innodb_flush_log_at_trx_commit 设置成 1，那么 redo log 在 prepare 阶段就要持久化一次到磁盘
  疑问：如果写入binlog成功后，但是在修改事务为commit状态时，发生crash，mysql崩溃恢复后如何处理呢？详细记录在: Mysql基础——事务

2.3 change buffer
在实际过程中，mysql利用change buffer对要更新行的数据页不在内存这种情况做了优化，针对数据页不在内存这种情况，如果更新操作总是需要去随机IO磁盘拿取数据，效果会差一些，所以在buffer pool内存中开辟了一段空间change buffer,当有更新、delete、 insert语句时会把这个操作先写入change buffer, 之后在进行merge操作时，再把change buffer记录的操作应用到原数据页，这里有几个问题：
1 merge 操作发生时机是？

• mysql下次查询这个数据页的数据时，会把该数据页载入内存，这个时候会判断change buffer里是否有对该数据页的操作，如果有会进行merge;
• 后台线程定期merge;
• 数据库正常关闭时会merge

2 什么情况会利用change buffer 优化更新效率？

• insert操作时，如果语句中有普通索引，则对普通索引的插入操作会利用change buffer，主键索引则不会，因为数据库对唯一索引的操作总是要把数据页读入内存，进行是否重复的判断，既然已经读入内存了，则不需要change buffer再来优化了；
• 更新、删除操作，针对普通索引时

3 利用了change buffer后，如果保证数据一致性？

• change buffer本身的操作也要进行持久化，也要写入redo 日志，即：redo log里会记录对数据页的物理修改，还会记录对change buffer的物理修改日志；
• change buffer内容会默认持久化到系统表空间ibdata1里，在更新操作时，如果数据页不在内存，会先写change buffer，然后把这个写change buffer的操作写入redo log,这个时候事务才算完成，所以这种机制，是不会因为数据库异常、断电等情况导致数据丢失的

2.4 其他知识点

• innodb通过binlog和redo 共同合作，保证了数据库宕机后同样能保证之前数据不会丢，这个能力称为 crash-safe
• 写入redo和写入binlog是两个不同的模块，并不是原子的，所以要保证这两个操作原子，就在内部使用XA事务，通过两阶段提交，保证写redo和binlog原子；
  • 如果一个事务binlog写入成功，但是redo失败：这种情况会出现主从不一致，redo失败了，重启数据库后会回滚这个事务，但是binlog已经写入成功了，同步到其他slave了；
  • 如果写入redo成功，但是binlog失败了，那么同样主从不一致；
• redo是innodb的日志，它是一个日志组，循环写入，记录的是数据页的物理修改

引用自《丁奇45讲》，更多详细内容可以看《丁奇45讲》