Mysql 概念介绍 InnoDB 数据页结构小白教程（三）

最新推荐文章于 2024-07-25 11:20:42 发布

m0_73583876

最新推荐文章于 2024-07-25 11:20:42 发布

阅读量99

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/m0_73583876/article/details/128756345

版权

在这里插入图片描述

一个事务的 binlog 是有完整格式的：

WAL，中文全称是 Write-Ahead Logging，它的关键点就是日志先写内存，再写磁盘。MySQL 执行更新操作后，在真正把数据写入到磁盘前，先记录日志。

好处是不用每一次操作都实时把数据写盘，就算 crash 后也可以通过redo log 恢复，所以能够实现快速响应 SQL 语句。

binlog 日志有三种格式

Statement格式

每一条会修改数据的 SQL 都会记录在 binlog 中

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
缺点：由于记录的只是执行语句，为了这些语句能在备库上正确运行，还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果。

Row格式

不记录 SQL 语句上下文相关信息，仅保存哪条记录被修改。

优点：binlog 中可以不记录执行的 SQL 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或 function、或trigger的调用和触发无法被正确复制的问题。
缺点:可能会产生大量的日志内容。

Mixed格式

实际上就是 Statement 与 Row 的结合。一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式。

redo log buffer (内存中)是由首尾相连的四个文件组成的，它们分别是：ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。
如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
有了 redo log，当数据库发生宕机重启后，可通过 redo log将未落盘的数据（check point之后的数据）恢复，保证已经提交的事务记录不会丢失，这种能力称为crash-safe。

原因：从大到小可分为四种情况

解决：

考虑采用 force index 强行选择一个索引
考虑修改语句，引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。
第三种方法是，在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
如果确定是索引根本没必要，可以考虑删除索引。

一个数据页大致划分七个部分

只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后回复数据
在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。

DBA 的最核心的工作就是保证数据的完整性，先要做好预防，预防的话大概是通过这几个点：

权限控制与分配(数据库和服务器权限)
制作操作规范
定期给开发进行培训
搭建延迟备库
做好 SQL 审计，只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核
做好备份。备份的话又分为两个点 (1)如果数据量比较大，用物理备份 xtrabackup。定期对数据库进行全量备份，也可以做增量备份。(2)如果数据量较少，用 mysqldump 或者 mysqldumper。再利用 binlog 来恢复或者搭建主从的方式来恢复数据。定期备份binlog 文件也是很有必要的
如果发生了数据删除的操作，又可以从以下几个点来恢复:
DML 误操作语句造成数据不完整或者丢失。可以通过 flashback，美团的 myflash，也是一个不错的工具，本质都差不多
都是先解析 binlog event，然后在进行反转。把 delete 反转为insert，insert 反转为 delete，update前后 image 对调。
所以必须设置binlog_format=row 和 binlog_row_image=full，切记恢复数据的时候，应该先恢复到临时的实例，然后在恢复回主库上。
DDL语句误操作(truncate和drop)，由于DDL语句不管 binlog_format 是 row 还是 statement ，在 binlog 里都只记录语句，不记录 image 所以恢复起来相对要麻烦得多。
只能通过全量备份+应用 binlog 的方式来恢复数据。一旦数据量比较大，那么恢复时间就特别长
rm 删除：使用备份跨机房，或者最好是跨城市保存。

DELETE 语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
drop语句将表所占用的空间全释放掉。
在速度上，一般来说，drop> truncate > delete。
如果想删除部分数据用 delete，注意带上 where 子句，回滚段要足够大；
如果想删除表，当然用 drop；如果想保留表而将所有数据删除，如果和事务无关，用 truncate 即可；
如果和事务有关，或者想触发 trigger，还是用 delete；如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入/插入数据。