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

2次阅读

共计 7468 个字符，预计需要花费 19 分钟才能阅读完成。

苍茫大地一剑尽挽破, 何处热闹笙歌落。难道这普天之下尽没我包容之处？为何这面试官总要与我争风绝对，这不是难为人吗？

每次经验痛领悟，总会悠然南山下，单独愁楚。记录心中一片大海，唯有单独致力，方能实现行业之豪杰。

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

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

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

 面试官: 看你简历形容精通 Mysql 优化办法，那你先说说你对 Mysql 的事务的理解吧。

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

 吒吒辉： 好呢，数据库的事务是指一组 sql 语句拜访各种数据项的数据库逻辑处理单元，在这组 SQL 操作中，要么全副执行胜利，要么全副执行失败。

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

 吒吒辉： 个别申请进入 MySQL，都是依据不同申请类型先在服务层外部做数据处理，而后在由后盾线程把数据刷到磁盘中。从而防止频繁的读写磁盘。

答复结束后，本人笑笑的模一下头发，打理一下本人的发型，整顿了一下脖子上得领结。

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

 面试官： 刚你提到了 MySQL 外部的工作，那你晓得事务的个性吗？数据是如何刷盘？服务层数据如何批改？这样得工作模式有什么益处？怎么防止频繁写入? 读申请和写申请在服务层工作是一样的吗？

我心田一钝，这老秃驴，不要命啦，逮到这么问我。看来本人得正经点，感觉空气中弥漫着杀气腾腾的气味。

难堪的冲他笑了一笑。立马说到

 吒吒辉： 在 Mysql 中事务的四大个性次要蕴含：
原子性（Atomicity）
一致性（Consistent）
隔离性（Isalotion）
持久性(Durable)，简称为 ACID。由它们独特来保证数据的一致性。

 面试官： 嗯，说说你对这四大特的了解？

 吒吒辉：

原子性：故名思议原子是最小的元素单位，所以一个事务是一个不可拆分的最小工作单元。整个事务操作要么全副执行胜利，要么全副失败回滚。

因执行胜利和失败都是状态的变动，如果只有执行成果只有一半，数据就会不残缺，从而违反了原子的特点。

一致性：指数据库从一个一致性的状态转换到另外一个一致性的状态。要保障事务前后的状态要统一
隔离性：一个事务所做的批改在最终提交以前，对其余事务是不可见的。
持久性：一旦事务提交，则其所做的批改就会永恒保留到磁盘上。是执行后果始终失效。

 面试官： 那数据库底层是如何实现这四大个性的？

登时, 我望向他那张有点清淡的大脸，可他的确气定神闲的望着我，面相是如许的随和。此刻心田一直策马崩腾，第一个问题就给我整了这么多，前面可咋办？心田感觉悬了

 吒吒辉：

原子性：由 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。

 面试官： 嗯，持续

此时，心田发毛。答复到这个水平，还面不改色，就一个持续打发我了？你可真是深造。

 吒吒辉：

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

InnoDB 的 MVCC，是通过在每行记录的前面保留两个暗藏的列来实现的。这两个列，一个保留了行的创立工夫，一个保留了行的过期工夫（或删除工夫），当然存储的并不是理论的工夫值，而是零碎版本号。

– 摘自《高性能 Mysql》这本书对 MVCC 的定义

 面试官： 那不同的 SQL 申请，MySQL 是如何实现得？

 吒吒辉：

select 申请依据零碎以后的事务版本号去和数据列中版本号做比照，先过滤存在过期工夫的数据列。而后取出满足小于或者等于以后版本号的记录。

insert innodb 为每插入一行，就保留以后零碎版本号为行版本
delete innodb 为删除每一行，就保留以后零碎版本号作为行删除标识。
update innodb 为 insert 插入一行新记录，保留以后零碎版本号作为行版本号，同时保留以后零碎版本号到原来的行作为行删除标识。

 面试官： 那这个机制有什么益处？

 吒吒辉：
它次要实现思维是通过数据多版本来做到 读写拆散。每个申请给到不同版数据来执行。从而实现不加锁读进而做到读写并行。
MVCC 机制实现还须要依赖于 undo log 与 read view

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

read view: 用来判断以后版本数据的可读性

 面试官： 那你给我画个图看看

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

** 面试官：没明确吗？就是用白板笔画出原理图解

 吒吒辉： 我急忙应和到。额额额，好

 吒吒辉： 就这样，每个申请批改时就会对应一个版本。

 面试官： 那这 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 再过滤符合条件的的行并开释锁。往后只有符合条件的行才持有锁。

 吒吒辉： 这样就升高了并发度，并且性能开销也会很大。

 吒吒辉： 而最初的一致性，实际上是通过原子性、持久性、隔离性来实现的

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

 面试官： 那请持续数据是如何刷盘？这样得工作模式是怎么防止频繁读写得?

 吒吒辉： 说到数据刷盘，首先得谈下 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 个性拿到以后操作数据最新版本，从而并发执行多个版本。

 面试官： 嗯嗯

霎时感觉被榨干了，怎么都这样出招，幸好以前多少有点筹备。突然感觉本人身后开始滑落着汗滴。只见面试官看了看简历，脸上表情祥和，感觉在打量着什么。。。。

 面试官： 小伙，这明天就面到这里，我看你如同有点热呀，先回去等告诉吧

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

 吒吒辉： 这样啊，那好吧。。。。

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

文章继续更新，可微信搜寻「莲花童子哪吒」第一工夫浏览，还有在线技术答疑群聊

正文完

mysql

发表至： mysql

2020-07-09

0

关于mysql:一文读懂-MySQL-Explain-执行计划

关于mysql:Mysql-隔离级别详解

关于mysql:数据库误操作数据恢复

关于mysql:你不知道的MySQL以及MariaDB初体验

aspnet-core-系列15-自定义Identity

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

前言

<span style=”color:#773098;font-weight:bold;”> 事务简介和原理

MySQL 内核工作原理

数据

日志

站内搜索