Mysql学习笔记.zip

##### 流程图地址 ``` https://www.processon.com/diagraming/5ce61557e4b00f7e86dda40f ``` ##### innodb_flush_log_at_trx_commit 现在有一句更新语句，很简单： ``` update student set name = 'XXX' where id = 10 ``` 这里面的SQL是如何执行的呢？ 大概流程是以下这样的： ``` 1. 程序数据库连接池发送链接到mysql 2. mysql接口收到请求 3. sql解析器解sql 4. sql优化，寻找最优路径 5. 执行SQL ``` 在了解更新的过程之前，首先了解innodb里面有个缓冲池的概念 **buffer pool** 它是做什么的呢？  大概就是这么个流程 ``` 1.引擎要更新id为10的记录，会先看记录是否在缓存池里面存在，不在的话就会从磁盘加载到缓存池里面， 并且对这行记录上锁，目的是防止别人同时去进行更新 2.写入undo文件（撤销操作） 实际上事务的回滚，就是得益于这个操作，mysql会把老的值写到undo文件中。假设说id=10的数据之前的name是zhangsan. 那么mysql就会把zhangsan写到undo文件中。 3. 更新buffer pool 里面的缓存数据 这一步就是直接将name = xxx更新到bp中。注意的是：现在虽然bp内存中的内容已经被修改了，但是磁盘的值还是老的。 4.更新修改内容到redo log buffer，这是内存里面的缓冲区，用来存放redo日志的 进行完以上3步，将数据的修改操作写到redo log buffer 中 5. 提交事务将redo日志写到磁盘 当我们提交事务，会根据一定的策略把redo日志从redo log buffer 刷到磁盘文件中。 这里面涉及到一个关键的配置innodb_flush_log_at_trx_commit 当这个参数为0的时候，提交事务，是不会吧redo log buffer的数据刷新到磁盘的，此时就可能提交事务之后，mysql宕机了，这部分的数据会发生丢失。 当这个参数为1的时候，提交事务，数据一定会刷新到磁盘中，只要事务提交成功，修改的记录就一定在磁盘里面了。 当这个参数为2的时候，提交事务，是先会把数据刷到OS cache（系统内存）此时就可能提交事务之后，mysql宕机了，这部分的数据会发生丢失 ``` 官方解释来了： ``` 0（延迟写，实时刷）： log_buff -- 每隔1秒 > log_file -- 实时 > disk 1（实时写，实时刷）： log_buff -- 实时 > log_file -- 实时 > disk 2（实时写，延迟刷）： log_buff -- 实时 > log_file -- 每隔1秒 > disk 0：事务提交时，不将重做日志缓冲写入磁盘，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。因此如果 MySQL 发生宕机，那么就有可能丢失一部分事务。 1：事务提交时，会将重做日志缓冲写入磁盘，并且立即刷新（fsync()）。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。 2：事务提交时，会将重做日志缓冲写入磁盘，但是不会立即进行刷新操作，因此只是写到了操作系统的缓冲区。此时若操作系统发生宕机而没有即使的同步，也可能会丢失一部分数据。 ``` *** ##### 说下mysql的Binlog是什么？ 实际上我们之前说的redo log，他是一种偏向物理性质的重做日志，因为他里面记录的是类似这样的东西，“对哪个数据页中的什么记录，做了个什么修改”。 redo log本身是属于innoDb存储引擎的特有东西。 而Binlog比较偏向于归档日志，他是属于mysql server自己的日志文件。 还是刚刚那个例子，binlog叫做归档日志，他里面记录的是偏向于逻辑性的日志，类似于**对users表中的id=10的一行数据做了更新操作，更新以后的值是什么** ##### 提交事务的同时，会写入binlog 首先在提交事务的时候，会把redo log日志写到磁盘中 ##### binlog日志的刷盘策略分析 对于Binlog日志，其实也有不同的刷盘策略。他的默认值是0，此时你把binlog写入磁盘的时候，其实没有直接到磁盘文件，而是进入os cache内存缓存。 所以和之前分析的一样，如果机器宕机的情况，在OS CACHE里面的binlog实际上是丢失的。 ##### binlog和redo log真正完成提交 在binlog写完磁盘文件之后，会把本次的更新对应的binlog文件名称和这次更新的Binlog日志在文件中的位置，全部写到redo log文件中，并且会有一个commit的标记。 注意的是：完成了这一步**commit标记后，才算完成事务的提交** ##### commit标记的意义： 现在我们假设已经提交事务了，此时一次更新“update users set name='xxx' where id=10”，他已经把内存里的buffer pool中的缓存数据更新了，同时磁盘里有redo日志和binlog日志，都记录了把我们指定的“id=10”这行数据修改了“name='xxx'”。 此时我们会思考一个问题了，但是这个时候磁盘上的数据文件里的“id=10”这行数据的name字段还是等于zhangsan这个旧的值啊！ 所以MySQL有一个后台的IO线程，会在之后某个时间里，随机的把内存buffer pool中的修改后的脏数据给刷回到磁盘上的数据文件里去。 ##### mysql的Buffer页 1.Buffer Pool大小建议是2G ##### 磁盘上的数据页和buffer pool中的缓存页是如何对应的？ 实际上默认情况下，磁盘中存放的数据是16KB，也就是说一页数据包含了16KB的数据。 然后,对于Buffer Pool中存放的一个个数据页，我们通常把其称为缓存页，因为Buffer Pool是一个缓冲池，里面的数据都是从磁盘放到内存中去的。 Buffer Pool中除了缓存页以外，还有一些描述信息，描述信息的大小一般为缓存页的5%。 缓存信息中放的内容主要是： 这个数据页所属的表空间，数据页的编号，这个数据页在Buffer Pool中的地址，等一些杂七杂八的东西。 所以我们的Buffer Pool假设是128M的话，实际上大小可能会是130多M，多出去的内存用于存放描述信息。。