满满干货!深入剖析MySQL中可能会忽视的小细节,原来还能这么玩

189 阅读13分钟

今日分享开始啦,请大家多多指教~

mysql基础

对于后端开发来说,打交道最多的应该是数据库了,因为你总得把东西存起来。

或是mongodb或者redis又或是mysql。然后你发现一个问题,就是他们都有日志系统,那么这些日志用来干什么的呢?

举两个例子,回滚和同步。

回滚,这里的回滚是比如说一条语句增加了1,然后再减一吗?这里的回滚操作并不是这样的。

比如说我要更新一条语句,update test set a=1 where b=2,这样的语句,如果这条语句需要回滚,那么操作就应该是在执行前,先查询这条数据进行保存,如果执行完毕需要回滚,那么就直接把原来那条语句写回去。

又比如说,你的数据库要还原到一个小时前,那么你可以把2个小时前的备份拿出来,然后运行前两个小时到前一个小时的日志文件,那么这个时候就相当于回到了一个小时前了。

同步,比如说主从同步了,这样老生常谈的了,一般通过事务日志来同步。

总之,有了日志,那么可以帮我们实现很多功能的了。

那么mysql 在innodb 引擎下,有两个日志非常重要,那就是redo log(重做日志)和 binlog(归档日志)日志。

如果没有这两个日志,应该没啥人敢用mysql的了,因为这两个日志是用了保证mysql的数据完整性的,如果一个数据库连完整性都不能保证,那么是非常危险的。

redo log

首先看下redo log,这个是什么呢? 这个是innodb存储引擎的日志。

说一个它的功能哈,前文提及到存储引擎就相当于我们操作系统的文件系统。

那么问题来了,我们的文件系统是有缓存的,比方说我们写入一个文件,当我们调用函数的时候,不会直接写入,而是写入缓存去,而后又文件系统自己判断啥时候应该写入进去。

判断什么时候应该写入进去:

其中有一个标准就是这次要写入缓存的时候,判断缓存是否能够装得下,如果装不下,那么先写入文件,清除缓存,然后再写入缓存。

第二个判断标准就是根据时间某一段时间后进行写入。

同样存储引擎也要为这一段事情操心啊。如果我们更新一条语句,存储引擎就直接给我们操作正在存储数据的地方,那效率可想而知。所以说,存储引擎就想到一个方法,把更新记录记录到redo log 中,等redo log 快写满,然后就操作到磁盘,或等空闲时间更新进去。

写完redo log 之后,就会告诉执行器,执行完毕了。这个时候我们的应用程序得到更新成功的回调。

如果单纯只写入redo log是不行的,因为存储过程不仅要写,还要读啊,如果写完redo log 通知我们的应用程序更新成功,这个时候还没写入到数据文件,那么我们的应用程序去读的时候就读到了旧的数据。

那么这个时候,存储引擎是这么干的。 反正你给我查询的时候要先查询内存,内存中没有才去查询数据文件。那么存储引擎,先更新到内存中,然后更新到redo log。这样对于存储引擎外部来说,是更新了的,毕竟对于外部来说存储引擎是一个整体。

这就是MySQL里经常说到的WAL技术,WAL的全称是Write-Ahead Logging。

那么这个redo log 是一个什么样的机制呢?难道就一直记录到一个文件中,然后当要写入数据文件的时候,全部写入,然后删除?如果这么干效率自然是低了。redo log的机制是这样的。

redo log 是由四个文件组成,每个文件大小为1G左右,这个都是可以设置的。

有两个参数,分别为checkpoint 和 write pos。

write pos 是当前记录的位置。

checkpoint 是当前写入到数据文件的位置。

比如说:

image.png

一开始的时候write pos 写得了第二个文件的问题,如果为位置1000。

这个时候还没有去正在写入数据文件,那么这个时候checkpoint 位置就是0。要往数据文件中写入数据的部分就是checkpoint 到 write pos这一段区间,也就是0-1000位置。

那么这个时候存储引擎感觉可以更新了,然后开始写入到正在的数据文件中,那么这个时候开始checkpoints 开始往右移动,假设更新800条。

那么就到了下面这个位置:

image.png

这个时候存储引擎感觉比较忙了,那么就更新800条后,继续接执行器的任务,那么write pos 往右继续移动。

那么这个时候就有一个问题出现,比如说文件大小不变,write pos 一直往右移动,这样会超出啊。

那么这个时候write pos 发现自己到了末尾,人家又从第一个开始写,覆盖写入。

如下:

image.png

绿色横线部分是要更新到数据文件的部分。

redo log 还有一个重要的作用,保证数据正在地写入到数据文件中。

比如说这个时候正在写入数据文件,然后数据库异常重启了,这里理解异常重启,简单点理解就是内存都没了。那么我们知道写入文件是有缓存的,如果写入到一半异常了,那么数据其实是丢了的。

有了redo log之后,只有数据文件flush了,那么这个时候checkpoint才开始偏移,否则就如果异常了内存没了,那么继续覆盖更新,因为checkpoint 没有变化,那么还是从原来那个异常前的位置开始同步。

那么问题来了,这时候就会想,数据文件是文件,redo log也是文件啊。如果写入的时候在缓存区,然后宕机这个时候也没了啊。

没错,的确有这个问题,这个时候为了数据安全,redo log直接不使用缓存区。

redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候,表示每次事务的redo log都直接持久化到磁盘。

binlog

binlog 是每个mysql都有的,而不是存储引擎的东西,属于mysql 的server 层的东西。

对比一下binlog 和 redo log。

这两种日志有以下三点不同。

redo log是InnoDB引擎特有的;binlog是MySQL的Server层实现的,所有引擎都可以使用。

redo log是物理日志,记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志,记录的是这个语句的原始逻辑,比如“给ID=2这一行的c字段加1 ”。

redo log是循环写的,空间固定会用完;binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。

一条更新语句在innodb引擎下的更新过程:

  1. 执行器先找引擎取ID=2这一行。ID是主键,引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。

  2. 执行器拿到引擎给的行数据,把这个值加上1,比如原来是N,现在就是N+1,得到新的一行数据,再调用引擎接口写入这行新数据。

  3. 引擎将这行新数据更新到内存中,同时将这个更新操作记录到redo log里面,此时redo log处于prepare状态。然后告知执行器执行完成了,随时可以提交事务。

  4. 执行器生成这个操作的binlog,并把binlog写入磁盘。

  5. 执行器调用引擎的提交事务接口,引擎把刚刚写入的redo log改成提交(commit)状态,更新完成。

这个时候有人可能会问,如果第3步骤先更新到内存,这个时候要是读取操作而之后redo log没有写入就宕机怎么办?因为是写入是有锁的,如果没有提交事务,这个时候有写锁。

这时候就发现一个细节,那就是redo log 一条记录有两个状态一个是prepare,一个是commit状态。

那么为什么要两个状态呢?

我们知道mysql 主从,其实是通过binlog 一条一条发送给从数据库,让从数据库执行binlog里面的操作。

假设没有这两个状态。

假如innodb 写入redo log之后呢,这个时候数据库突然宕机了,这个时候redo log 是有的记录的,这个时候binlog 没有记录。那么innodb 通过redo log 进行写入到数据文件后,binlog 依然没有这一条记录。那么从库就少了一条操作了。

这个时候主从永远不可能一致。

如果有了两个状态,数据库重启后,innodb存储引擎还是会通知binlog。这时候两个状态就保证了binlog里面的数据完整性。那么这个时候又会问了,假如上面第四步执行了,第五步没有执行怎么办?比如宕机了。

是啊,这个时候bin log 中有记录但是redo log没有记录。那么从库就少了一条操作记录了。

这个时候主从永远不可能一致。同样,我们如果数据库退回到某个时间点,如果binlog 和 redolog不一致的话,同样适用binlog进行回滚一样的会遇到这个问题。

如果有了redo log 的prepare 状态,那么如果数据库重启的时候检测到宕机,这个时候redo log里面prepare 状态的数据就会和binlog里面的数据进行校验,进而进行恢复。

这种有两个状态的提交,叫做两阶段提交。他们起到的作用是如果宕机检测到异常,就会对比恢复。

同样binlog也是文件,同样存在缓存的问题,sync_binlog这个参数设置成1的时候,表示每次事务的binlog都持久化到磁盘。

mysql 事务

事务有四大特性:

1.原子性(atomicity)

一个事务必须被视为一个不可分割的最小单元。

2.一致性(consistency)

数据库总是从一个一致性的状态转换到另一个一致性的状态。很多人对事务的一致性和原子性可能会有偏差。要理解这个东西呢,首先要抛开mysql,或者我们常见的数据库sql server,mongodb。

单纯来理解数据库的事务。

假如有两个事务,事务a和事务b。

假设A和B的两个账号,a账户是500块,b账户是300块。里面有一个限制就是A账号不能大于600块。

事务a 的逻辑是给A增加100块。然后给B减少100块。

事务b 的逻辑是给B减少100块。然后给A增加100块。

现在事务a,开始执行,做好备份做回滚(500,300),然后给A增加了100块,A现在是600。

现在事务b,开始执行,做好备份做回滚(600,300),然后给B减少了100块,现在B是200。

现在事务a开始执行,给B减少100,B变成了100。也急速说b事务提交的是(600,100)

现在b开始执行,但是报错了,遇到了A不能高于600的限制,现在开始回滚,那么回滚为(600,300)。

那么这个时候就变成了(600,300)了。那么请问事务a和事务b 是否符合原子性?

首先分析事务a,现在能做的,全部执行了。那么是符合原子性的。

然后分析事务b,的确是回滚了,也是符合原子性的。

这有疑问吗?没有吧。

那么事务a和事务b是否符合一致性呢?

a是否符合一致性呢? 数据库总是从一个一致性的状态转换到另一个一致性的状态。一致性的要求是B账户减少100块,A账户多出100块了。这是一致性的要求。

数据库一开始是:(500,300),然后a提交的时候(600,100),这显然不符合。b事务其实是符合一致性的,一开始是(500,300),回滚也是(500,300),这没错。

那么是否一致性就一定要原子性呢?

image.png

这样其实也是可以一致性的。

那么数据库为什么不这么干呢?比如说 update test set a=a-1;

这个时候回滚的时候还得给你生成一个update test set a=a+1。这是简单的,如果是复杂的,这是要将数据库称为人工智能吗?数据库都会傻掉的。

要实现一致性,原子性的成本应该是最低的,但是单单原子性是不能实现一致性的。

3.隔离性

上面我们知道单单原子性是没有实现一致性的。那么隔离性就是在原子性的基础上增加一些,一些限制条件那么就可以实现一致性。

比如说,每个事务只能串行执行,这个时候也说符合的,这也是一种隔离级别。但是如果是串行,就不满足并发了,所以就有其他隔离级别了,或者说其他隔离方式。

4.持久性

一旦事务提交,则其所做的修改就会永久保存到数据库中。可能有人说这不是废话吗?事务做的修改不就是要保持带数据文件中,能够持久化吗?

这里面的持久性,表示得更多的是一种方案。持久化是有很多方式的,怎么确保你的持久化方案可行呢?比如说一个事务要修改4条语句分别在四张表,那么怎么确保这四条语句能够全部写入进去呢?会不会写到第二条的时候系统崩溃呢?

如果出现了上面的问题,该怎么处理?这就是持久性的重要性了。前三个都是强调语句执行,最后一个强调存储。

今日份分享已结束,请大家多多包涵和指点!