MySQL最佳实践

一、MySQL查询执行过程

1.MySQL分层结构

• MySQL8.0没有查询缓存的功能了,如果频繁修改缓存,将会损耗性能
• 查询流程就按照分层结构就可以清楚,只要了解各个组件的各自功能就行
• 分析器主要分析语法和词法是否正确
• 优化器主要优化SQL语句

二、MySQL更新执行过程

• 更新主要涉及两个重要的日志,redolog(重做日志)和binlog(归档日志)

1.redolog

• 如果没有日志记录的话,每一条更新语句将会写进磁盘,再从磁盘找到对应的数据进行更新,将会非常损耗性能
• InnonDB引擎会先更新内存,再写redolog日志,等系统空闲的时候,再将redolog日志写入磁盘.即使数据库宕机,恢复后也能将未写入磁盘的redolog写入磁盘,保证数据更新这就是InnoDB提供的crash-safe能力
• redolog是固定大小的文件,如果日志文件不够写,将会先擦除一部分再写

2.binlog

• binlog是server层提供的归档日志,会记录下更新的逻辑,比如将1变为2

3.两种日志区别

• redolog是InnoDB提供的,支持crash-safe能力;binlog是server层提供的
• redolog是循环写的,日志文件会用完;binlog是追加写的

4.更新过程

示例:给ID=2的C字段加1

• 查找ID=2的数据,如果内存有直接返回,没有则从磁盘查找,再返回给内存
• 将ID=2的C字段+1,将数据更新到内存
• 将记录写入redolog,此时redolog处于prepare状态
• 告知执行器可以提交事务,写binlog日志
• redolog提交事务,更新完成

(1)两阶段提交

为什么会两阶段提交redolog和binlog呢,原因就是为了误删数据后,恢复数据能与历史数据达成一致.我们可以进行反证

• 假设先提交redolog,当发生了一次crash-safe,恢复后redolog记录的日志可以更新数据,但是binlog没有记录.之后使用binlog恢复删除的数据,会造成该条数据不一致
• 假设先提交binlog后crash,但是redolog并没有记录该条数据的更新,所以机器恢复后redolog不会更新该条数据,但是使用binlog恢复删除的数据则会更新该条数据,也会造成数据不一致