内容
- 一条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讲》
网友评论