mysql执行sql必须先写入日志_MySQL实战(一)SQL执行过程

平时的工作过程中避免不了要使用MySQL或者相关工具，那这些SQL到底是如何执行的呢？

查询SQL是如何执行的

连接器

建议尽量使用长连接，可能会出现MySQL占用内存涨得特别快，最后导致被系统杀掉，从现象上看是MySQL重启了。

原因：MySQL临时使用的内存，管理在连接对象中，只有当连接断开才会释放

解决办法：定期断开长连接。使用一段时间或执行占用内存大的查询之后断开连接，后续查询需要重新连接

若使用MySQL 5.7或更新版本，可以在每次执行占用内存大的查询之后，通过执行mysql_reset_connection初始化连接，不需要重新连接。

更新SQL是如何执行的

大体执行与查询一致，但因为涉及到改变数据，所以会涉及到日志模块。MySQL通过使用WAL(Write-Ahead Logging)技术，先写日志，再写磁盘，减少IO,提升效率比

重要的日志模块

redo log

InnoDB专属，大小固定，循环写，write pos 指向当前写入的位置，check point 指向当前擦除的位置，当write pos 追上check point时，代表必须进行擦除。redo log可以保障即使数据库异常崩溃，之前提交的记录都不会丢失，这个能力称为crash-safe

binlog

又称为归档日志，归属于Server层，与redo log的不同之处如下

1.redo log 是InnoDB引擎特有的，binlog是MySQL的Server层实现的，所有的引擎都可以使用

2.redo log是物理日志，记录了“在某个数据页上面做了什么修改”，而bin log 是逻辑日志，记录的是这个语句的原始逻辑，有两种模式：statement记录sql，row模式记录前后的状态

3.redo log 是循环写，空间会用完。而binlog是追加写

示例：1

2create talbe student(ID int primary key ,age int)

update student set age = age +1 where ID = 2

1.执行器根据 ID = 2 从存储引擎中获取数据，存储引擎查找到数据之后返回

2.执行器拿到之后之后进行 age+1操作，调用存储引擎接口写入数据

3.引擎将新数据写入到内存中，同时将更新操作记录到 redo log 中，此时 redo log 处于 prepare 状态，然后告知执行器可以随时可以提交事务

4.执行器生成这个操作的binlog，并把binlog写入到磁盘中

5.执行器调用引擎的提交事务接口，引擎将对应的redo log 改为提交状态，更新完成

两阶段提交

两阶段提交是为了保持两份日志之间的逻辑一致，从而保证事务的完成行，可以对数据进行恢复。两阶段提交过程如下：

1：写redo log,处于prepare状态

2：写bin log

3：写redo log，处于commit状态

若1阶段发生崩溃，由于还未写入binlog，直接回滚；若处于2阶段，由于redo log和binlog都有对应操作记录，事务完成；若处于3阶段，redo log 处于commit状态且binlog完成，事务完成。

redolog用于保证crash-safe能力，innodb_flush_log_at_trx_commit参数建议设置为1，表示每次事务的redo log都直接持久化到磁盘，可以保证MySQL异常重启后数据不丢失。

syn_binlog参数也建议设置为1，表示每次事务的binlog都持久化到磁盘，保证MySQL异常重启后binlog不丢失

一般MySQL恢复数据使用备份数据加binlog进行，使用最近的一次备份数据，回放binlog到需要恢复的时间。