vlambda博客
学习文章列表

MySQL事务熟练使用就够?和腾讯大佬的一席对话,原来考点都在这些方面!

「苍茫大地一剑尽挽破,何处繁华笙歌落」。难道这普天之下尽没我容纳之处?为何这面试官总要与我争风相对,这不是难为人吗?

每次经历痛领悟,总会悠然南山下,独自愁楚。记录心中一片大海,唯有独自努力,方能实现行业之俊杰。

前言

一个阳光明媚得上午,迎面走来了一位身着雪白雪白得的白色衬衣,灰白色相间的休闲短裤,还给我露出了彰显男儿本色的黑色腿毛。手拿银色金属质感得 MacBook Pro,外加一双小白鞋。看着那稀少的发量,在灯光的照耀下,甚至还有点反光。透过那从容不迫的脸色。

我内心不忍一颤,今天怕是要遇到人了。整整一个架构师的大叔呀。

果不其然,他手里拿着我的简历,快速的扫了一下,然后用眼角余光看了一下我,像是在掂量着什么。上来开口即问。

事务简介和原理

面试官:」 看你简历描述精通 Mysql 优化方法,那你先说说你对Mysql的事务的了解吧。

我吒吒辉内心独自平静了一些,这个不难,就是一个事务定义和执行得调用的方法。殊不知后面的内容都是对我得严刑拷打。

吒吒辉:」 好呢,「数据库的事务是指一组sql语句访问各种数据项的数据库逻辑处理单元」,在这组 SQL 操作中,要么全部执行成功,要么全部执行失败。

举个经典的例子就是转账,事务 A  要进行转账,那么,转出的账号要扣钱,转入的账号要加钱,这两操作要么同时执行成功,要么都全部执行失败。为得就是确保数据的一致性。

吒吒辉:」 一般请求进入MySQL,都是根据不同请求类型先在服务层内部做数据处理,然后在由后台线程把数据刷到磁盘中。从而避免频繁的读写磁盘。

回答完毕后,自己笑笑的模一下头发,打理一下自己的发型,整理了一下脖子上得领结。

面试官:吆喝,这小子还有点料啊,敢在太岁头上动土,你还要反了天呀你。还在这欺负我头上的没头发。个小崽子。

面试官:」 刚你提到了 MySQL 内部的工作,那你知道事务的特性吗?数据是如何刷盘?服务层数据如何修改?这样得工作模式有什么好处?怎么避免频繁写入? 读请求和写请求在服务层工作是一样的吗?MySQL事务熟练使用就够?和腾讯大佬的一席对话,原来考点都在这些方面!

我内心一钝,这老秃驴,不要命啦,逮到这么问我。看来自己得正经点,感觉空气中弥漫着杀气腾腾的气息。

尴尬的冲他笑了一笑。立马说到

吒吒辉:」 在Mysql中事务的四大特性主要包含:
「原子性(Atomicity)」
「一致性(Consistent)」
「隔离性(Isalotion)」
「持久性(Durable)」,简称为 ACID。由它们共同来保证数据的一致性。

面试官:」 嗯,说说你对这四大特的理解?

吒吒辉:

  • 原子性: 故名思议原子是最小的元素单位,所以一个事务是一个不可拆分的最小工作单元。整个事务操作要么全部执行成功,要么全部失败回滚。

因执行成功和失败都是状态的变化,如果只有执行效果只有一半,数据就会不完整,从而违背了原子的特点。

  • 一致性:指数据库从一个一致性的状态转换到另外一个一致性的状态。要保证事务前后的状态要一致

  • 隔离性:一个事务所做的修改在最终提交以前,对其他事务是不可见的。

  • 持久性:一旦事务提交,则其所做的修改就会永久保存到磁盘上。是执行结果一直生效。

MySQL事务熟练使用就够?和腾讯大佬的一席对话,原来考点都在这些方面!

面试官:」   那数据库底层是如何实现这四大特性的?

顿时,我望向他那张有点油腻的大脸,可他确实气定神闲的望着我,面相是多么的随和。此刻内心不断策马崩腾,第一个问题就给我整了这么多,后面可咋办?内心感觉悬了

吒吒辉:

  • 「原子性:由 undo log 保证,也称回滚日志,主要用于记录数据被修改前的信息」

undo log 主要记录数据的逻辑变化。所以需要将之前的操作都记录下来,然后在发生错误或执行事务回滚时才能保证回滚到之前的操作。

面试官:」 那这个请求过程是怎么实现的?

比如当写请求为 insert 操作进入到数据库服务层时,会先在缓冲池下面的写缓冲中进行数据的写入,然后先把写请求的物理语言写入到 read log中,并记录一条删除当前语句的逻辑语句到 undo log中。最后由 MySQL 后台线程定期刷新数据到磁盘里面。

但是这个写缓冲在5.5之前叫 插入缓冲」,只针对 insert 优化; 现在对 update 和 delete更新操作都有效。现在叫做:写缓冲

面试官:」 这个写缓冲有什么好处呢?

吒吒辉:
避免频繁磁盘I/O的写操作,如果没得这玩意,需要在磁盘里面读到数据,然后才能数据上面的修改。

但大多数场景下都是读多写少,如果在高并发的场景下,通过打包批次处理就能有效避免,在写操作处理时资源开销而影响到大量得读请求。

  • 「一致性:」 由 Redo log 日志保证,也叫重做日志,是用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log),前者是在内存中,后者在磁盘中。

    当事务提交之后会把所有修改信息都会存到该日志中。假设有个表叫做t1(id,username) 现在要插入数据(3,'吒吒辉')。

start transaction;
//数据写入 写缓冲中
insert into t1 (id,username) values (3,'吒吒辉')
// 生成 重做日志 id =3 , username = '吒吒辉'
commit;

面试官:」 那这 redo log 有什么作用?

为了提升性能,不会把每次的修改都实时同步到磁盘,而是会先存到Boffer Pool(缓冲池)里头,把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。

面试官:」 那如果缓冲池的数据还没来得急同步就宕机或断电了怎么办?  这样可是会导致丢部分已提交事务的修改信息!

吒吒辉:」 所以引入redo log来记录已成功提交事务的修改信息,并且会把redo log持久化到磁盘,系统重启之后在读取redo log恢复最新数据

面试官:」 可以举一个场景,怎么应用它们呢?

吒吒辉:」   假如某个时刻数据库崩溃,在崩溃之前有相关事务在执行,一半已经提交,一半还未提交。当数据库重启进行 「crash-recovery」 时,就会通过 Redo log将已经提交事务的更改先载入到内存,然后再写到数据文件,而还没有提交的就通过 Undo log 进行 roll back。

面试官:」 嗯,继续

MySQL事务熟练使用就够?和腾讯大佬的一席对话,原来考点都在这些方面!

此时,内心发毛。回答到这个程度,还面不改色,就一个继续打发我了?你可真是深造。

吒吒辉:

  • 隔离性 也叫多版本并发控制。

InnoDB的 MVCC ,是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列, 一个保存了行的创建时间,一个保存了行的过期时间(或删除时间), 当然存储的并不是实际的时间值,而是系统版本号。
--摘自《高性能Mysql》这本书对MVCC的定义

面试官:」  那不同的SQL请求,MySQL 是如何实现得?

吒吒辉:

  • select 请求根据系统当前的事务版本号去和数据列中版本号做对比,先过滤存在过期时间的数据列。然后取出满足小于或者等于当前版本号的记录。
  • insert  innodb为每插入一行,就保留当前系统版本号为行版本
  • delete  innodb为删除每一行,就保存当前系统版本号作为行删除标识。
  • update  innodb为insert 插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识。 MySQL事务熟练使用就够?和腾讯大佬的一席对话,原来考点都在这些方面!

面试官:」 那这个机制有什么好处?

吒吒辉:
它主要实现思想是通过数据多版本来做到「读写分离」。每个请求给到不同版数据来执行。从而实现不加锁读进而做到读写并行。
「MVCC机制实现还需要依赖于 undo log与read view」

undo log: undo log 中记录某行记录的多个版本的数据。

read view: 用来判断当前版本数据的可读性

面试官:」 那你给我画个图看看

什么鬼,这玩意还画图。我用那水汪汪的大眼睛,直勾勾的看着他。只见他看口说到。

** 面试官:没明白吗?就是用白板笔画出原理图解

吒吒辉:」  我急忙应和到。额额额,好

MySQL事务熟练使用就够?和腾讯大佬的一席对话,原来考点都在这些方面! 吒吒辉:」 就这样,每个请求修改时就会对应一个版本。

面试官:」 那这 MVCC 是如何保证多个版本数据得最终一致呢?

吒吒辉:」 因数据操作为多个版本,所以它支持读写并行。

在请求进行事务操作前,会先入到读、写队列里面,然后再分别执行读写请求。若第一个写请求执行事务但未提交,但这时它就会拿到最新版本。

如果此时读请求进入也会直接执行,只不过读取数据时得系统版本就为未执行写请求的版本号,根据它来进行数据的读取。

这样在事务未进行提交前,所有读操作读取到的数据是一样的,因为它受系统当前版本控制。读取的都是同一个版本数据。

面试官:」 MySQL是多线程, 那同时有多个线程修改同一行数据,这种情况怎么处理的?

吒吒辉:」  一般如果系统达到并发执行的情况,对用户请求得要求是很高的,每秒至少得几万请求,如有并发问题产生。

假如事务 A、B 都要执行 update 操作,事务A先 update 数据时,会先获取行锁,锁定数据,当事务 B 要进行 update 操作时,也会尝试获取该数据行的行锁,但此时已经被事务A占有,故事务B只能 wait。

吒吒辉:」  如事务A,是个很费时的大SQL,长时间没释放锁。那么事务 B 就会等待超时。

查询全局等待事务锁超时时间
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

面试官:」  这个是在 update 的where后的条件是在有索引的情况下吧?

吒吒辉:」 嗯,是的,因为 innodb 的行数只有索引才会触发,锁定的是行锁

面试官:」  那没有索引的条件下,就没办法快速定位到数据行呀?

吒吒辉:」  若是没有索引的条件下,就会退化为表锁。然后获取所有行后,Mysql 再过滤符合条件的的行并释放锁。往后只有符合条件的行才持有锁。

吒吒辉:」 这样就降低了并发度,并且性能开销也会很大。

吒吒辉:」  而最后的一致性,实际上是通过原子性、持久性、隔离性来实现的

此刻,感觉自己多少放松了一些,内心有点小得意,感觉达到了巅峰时刻。然后再拿着面前的矿泉水,饮了一口,顺便擦了下汗水。一切是那么得行云流水。然而面试官那脸还是一沉不变,像是在酝酿着什么,这不又开始了。

MySQL 内核工作原理

面试官:」 那请继续数据是如何刷盘?这样得工作模式是怎么避免频繁读写得?

吒吒辉:」  说到数据刷盘,首先得谈下 innodb 缓冲池。

buffer pool 主要由数据、索引、插入缓冲、自适应哈希索引、锁等组成。

不同请求会根据 buffer pool里面对应部分数据来执行相关的操作。buffer pool中的数据会根据配置参数定期同步到磁盘中;只是这里刷盘的数据主要由 「数据和日志」 组成。

数据

* 由 innodb_max_dirty_pages_pct 参数决定。在说到这个之前,得说说「脏页」。

脏页指存在于内存中的数据,但未同步到持久化存储里面,因为数据库的数据用页来读取,故此称之为脏页。

innodb_max_dirty_pages_pct 参数可动态调整,最小值为0, 最大值为99.99,默认值为 75。

根据缓冲池的大小和存储脏页的数量计算比例,如果满足了就调用后台线程把数据刷新到持久层当中里面。

这样做的好处就是合并其它数据页,从而提高写入的效率。

日志

由 innodb_flush_log_at_trx_commit 变量来控制日志缓冲刷新频繁程度。

  • 0:把日志缓冲写到日志文件,并且每秒钟刷新一次持久化存储,但是事务提交时不做任何事。

  • 1:将日志缓冲写到日志文件,并且每次事务提交都刷新到持久化存储。这是默认的(并且是最安全的)设置,该设置能保证不会丢失任何已经提交的事务,「除非磁盘或者操作系统是“伪”刷新,即写入到磁盘缓冲」。

  • 2:每次提交时把日志缓冲写到日志文件,但是并不刷新到持久化存储。InnoDB 每秒做一次刷新到持久化存储。一般也会选2,如果MySQL进程“挂了”,2 不会丢失任何事务。如果整个服务器“挂了”或者断电了,则还是可能会丢失 1s 的事务。

为什么上面把日志缓冲写到日志文件,还没到持久化存储呢?

因为在大部分操作系统中,把日志缓冲写到日志文件只是简单地把数据从 InnoDB  的内存缓冲转移到了操作系统的缓存,也是在内存里,并没有真的把数据写到了持久化存储。所以还需要进行数据磁盘同步的刷写。

吒吒辉:」 缓冲池是降低频繁得读写请求,这个得分情况来看:

  • 读请求:请求首先在缓冲池下面找缓存的数据,从而避免每次访问都进行磁盘IO。
  • 写请求:事务请求执行时,先根据日志缓冲配置的方式把数据记录到日志文件中,等数据的脏页达到一定比例或内存不够时,就会把数据落地磁盘上。
    从而把大量磁盘的随机I/O改写为顺序I/O。
    每次更新相关请求都基于 MVCC 特性拿到当前操作数据最新版本,从而并发执行多个版本。

面试官:」    嗯嗯

瞬间感觉被榨干了,怎么都这样出招,幸好以前多少有点准备。忽然感觉自己身后开始滑落着汗滴。只见面试官看了看简历,脸上表情祥和,感觉在端详着什么。。。。

面试官:」 小伙,这今天就面到这里,我看你好像有点热呀,先回去等通知吧

感情你老这才开胃呀!还能这样整。行不行你到是给句花呗。让我流这么多汗,我得喝多少水才补得回来,这不虚都被你搞虚。瞬间炸裂开来

吒吒辉:」  这样啊,那好吧。。。。

* 我命由我不由天,只为那不甘心的,如有帮助,关注分享额