《MySQL实战45讲》学习小结(基础篇)

最近学习丁奇老师的《MySQL实战45讲》,明白了以前一知半解的概念,对MySQL的运行机制有了比较系统的理解。

课程内容组织得非常好,留言里面也有很多精彩的见解,学到了很多东西,过程很享受。

在此先感谢下丁奇老师。

 

这里把学到的内容组织一下,把知识点串成线,织成网。

写下来的只是个提纲,以及一些要点。

接下来还要学以致用,在实际工作中带着这些知识去练习。

 

把学到的内容归纳为三部分:

一、基础概念

二、运维管理

三、合理使用MySQL

 

 

第一篇:基础概念

 

1. 事务

第3讲,8讲,20讲

 

事务是关系数据库(RDB)区别于非关系数据库的关键特性。

关键在于理解并发时可能出现的问题,以及避免这些问题出现的机制。

 

从问题入手:

- 脏读:读到其他事务未提交的修改

- 不可重复度:同一行,两次读到的数据不同

- 幻读:两次执行同一个sql,得到的结果集不同

理解“问题”的关键:两次读,是限定在一个事务的上下文内的。

很简单,可我之前就是没领会到,汗 ~ ~ ~

 

隔离级别(InnoDB):

- 读提交(RC),解决脏读问题

- 可重复读(RR),解决不可重复读问题,以及大部分幻读问题

- 串行化,同时只能有一个事务运行,完全解决幻读问题

隔离级别越高,解决的问题越多,并行能力越低。

InnoDB的缺省隔离级别为:可重复度。

 

 

2. 锁

第6讲,7讲,19讲,20讲,21讲,30讲,39讲,40讲

 

锁是数据库用以实现事务隔离的工具。

按照被锁定的对象,有:全局锁、表锁、MDL、行锁、gap lock、自增锁等。

按并行能力,可分共享锁(读锁)和互斥锁(写锁)

 

两阶段锁:锁在需要时加上,在事务结束时释放。

 

行锁、gap lock:这两个锁要重点学习。它们都在索引上执行。被访问到的记录才需要加锁。如果查询的是非主键索引,除了在非主键索引上的 next-key lock,还要在主键索引上对符合条件的记录加行锁。

 

MDL(meta-data lock)

为避免表结构被其他线程修改,自动加锁。

5.6版本实现online DDL,通过 MDL降级使得DDL期间仍可读写该表,提升并行能力。

原理:DDL语句先申请DML写锁,拿到MDL写锁之后,创建临时表,把源表数据拷过去,变更临时表数据结构,然后再用临时表替换源表,并把期间对源表的变动应用到新的表。在拷贝数据、变更临时表数据结构期间,MDL写锁降级为读锁,其他事务可以对原表进行读写。最后临时表和源表切换的时候,再从MDL读锁升为写锁。

 

死锁检测

死锁检测,时间复杂度为O(n^2)

高并发的情况下,如果出现大量锁等待,会因为死锁检测,消耗大量的CPU,导致性能急剧下降。

这种情况下,要控制对数据库的并发操作数量,以提升性能。

 

 

3. Multi-Version Concurrency Control(MVCC)

第3讲,8讲

 

多版本并发控制,是在不加锁的情况下实现事务隔离的一种手段。

对数据的每个修改,都会记录一个undo log。

每个事务会记录其启动瞬间,执行中的事务的trx_id列表。每行数据的每个版本(undo log),也会记录导致该变化的trx_id。trx_id,在InnoDB引擎层,在事务启动是获取,严格递增。但由于事务提交的次序和启动的次序不一致,所以在数据版本上,后一个版本的trx_id可能比前一个版本的trx_id小。

事务从一行数据的最新版本开始,逐个对比数据版本的trx_id和上述trx_id列表,决定当前版本是否可见,否则执行对应的undo log,直到找到第一个可见版本。

这个过程可称为一致性读。事务对应的数据版本,可称为一致性视图。

需要注意:

如果存在对同一个数据频繁更新的长事务,并发执行的其他事务,如果要读取同一个数据,为获取一致性视图,需要执行大量的undo操作,会导致性能急剧下降。

 

 

4. 日志系统

第2讲,15讲,23讲,24讲,25讲,26讲,27讲,28讲,31讲

 

主要有3类日志:binlog、redo log、undo log

 

binlog:

记录一个事务的所有修改。数据异常恢复、主备数据同步,都依赖binlog。

binlog有statement和row两种记录方式。statement方式记录事务中所执行的语句。row方式记录对数据的最终影响,细化到每一行,也即一个语句在执行时影响了多少行记录,就会在binlog中记录多少条语句,并写明把每个字段设置为什么值。

MySQL使用binlog来完成主备数据同步。考虑到主备服务器的差异、执行同一条sql命令的时间不同等等因素,在binlog中会额外加入一些语句,用以设置当前时间等环境相关的信息。

对应事务的原子性,binlog在事务执行期间,在独立的空间内先缓存,待事务提交之时,一起追加到binlog文件尾,因此,一个事务的binlog在文件中必然是连续的。

 

redo log(InnoDB):

InnoDB引擎才支持redo log。redo log是物理日志,记录“在某个数据页上做了什么修改”,类似binlog的row格式。

redo log,随着脚本的执行,随时写入。对并行的事务,其redo log可以穿插写入,因此可以共用redo log cache。

redo log在事务已完成,且对应的内存页已同步到存储的情况下,就可以删除。因此其需要的空间有限,所以被设计为固定大小(可分多个文件),循环写的方式,提高空间利用率。

但要注意:如果有长事务导致redo log的存储空间被全部占满,整个数据库服务都会被阻塞。

 

从日志系统的角度,每个事务为2阶段提交:

1. 引擎层:数据修改更新到内存页(或change buffer),记录redo log,处于prepare状态;

2. server层:写binlog

3. server层提交事务,通知引擎层把redo log的状态设为commit。

在这个机制下,结合redo log和binlog,可恢复所有已提交的数据,保证主备数据一致。

这个机制称为WAL(Write-Ahead Logging),能恢复在内存中已执行完毕的事务,实现 crash safe。

过程如下图所示:

redo log 和 binlog

图中浅色为InnoDB引擎层,深色为server层。

redo log和binlog有一个共同的字段:XID,用于进行关联。

由于数据的持久化操作,由各部分自行控制,因此redo log的状态修改、binlog的持久化,虽然在原文稿中为一前一后,但实际运行的时机,可视为并行。

数据库崩溃后的恢复:

如redo log处于commit状态,有完整记录binlog,则事务已提交完成,数据已入盘,无需额外操作。

如redo log处于commit状态,但没有完整的binlog,则执行redo log,然后commit,补全binlog。

如redo log处于prepare状态,有完整的binlog,引擎层应执行redo log,恢复数据。

如redo log处于prepare状态,但没有完整的binlog,事务未提交,无需操作,效果等同于回滚。

 

undo log

上文MVCC中已提高,针对数据的每个操作,对应redo log,有一个undo log,用于实现不用锁的事务隔离。

MySQL会定期检查当前执行中的事务,找到最早开始执行的事务,以此来决定哪些undo log可以被删除。

长事务,又是长事务。长事务的情况下,undo log的数量会显著增加。

监控长事务,及早发现并予以消除(需要业务系统开发者配合),是系统运维中必做的动作。

 

 

5. 索引

第4讲,5讲,9讲,10讲,11讲,15讲,37讲,38讲

 

InnoDB引擎

B+树来存储索引和数据

 

主键索引为聚簇索引,所有数据和索引存储在一起,通过主键索引就能查到全部数据。

非主键索引,在定位到位置后,需要再查一次主键索引才能得到全部数据,该动作称为回表

非主键索引,可设计为包含了某个查询语句所需的所有字段的值,这种情况称为覆盖索引

前缀索引:对于字符串字段,可设置索引只保存最靠左的多少个字符,以缩小索引所需空间,提高存储和搜索效率。但这样可能会导致查找次数变多。需要根据场景,合理选择。

InnoDB还支持根据某个函数做索引。

 

Memory引擎(17讲,36讲,38讲)

Memory引擎的数据用数组来存储,按写入顺序存储的。

主键索引和普通索引一样,都只记录数据的位置。缺省为hash索引。

 

 

6. 数据存储

第4讲,9讲,13讲,36讲,38讲,41讲

 

B+树

平衡N叉树,数据都在叶子节点上。叶子节点保证左小右大。

N的数量,由数据块的大小和每行对应的索引数据大小共同决定(InnoDB数据页默认为16KB)。

符合磁盘按页读写的特性。

InnoDB中,主键索引为聚簇索引,叶子节点保存了全部数据。非主键索引,叶子阶段保存主键的值。

这种方式称为 Index Organizied Table。

要理解数据库,必须深入了解B+数。

 

Memory引擎

数据用数组来存储,按写入顺序存储的。每行占用的空间大小固定。如果一行被删除,后续新增的数据就可以使用这个空间。

主键索引和普通索引一样,都只记录数据的位置。缺省为hash索引。

这种方式称为 Heap Organizied Table。

 

 

7. 缓存

 

内存数据页 buffer pool(第33讲,35讲,12讲,19讲)

数据库对数据的读写,需要把数据先读取到内存中。根据磁盘的特性,一次读入一页。

InnoDB每个数据页大小默认为16KB。

SQL脚本要访问的数据,如果已经在内存中,可以大大提升性能。Buffer pool对查询的加速效果,用内存命中率来衡量。一般情况下,内存命中率在99%以上。

InnoDB的内存管理,使用LRU算法,用链表实现。

为了避免因为对很大的冷表的全表扫,导致热数据被挤出内存,InnoDB对LRU进行了优化,将内存分为young和old两个区域,需要新插入的数据页,先放到old区头部,如果这个数据页再次被访问时,存在时间大于1秒,再移到young区头部。

虽然LRU算法做了优化,但是大表的join,如果执行时间超过1秒,还是可能会影响buffer pool,导致正常访问的数据无法进入young区,并有可能将热数据挤出young区,这样会在语句执行结束后的一段时间内仍影响后续查询语句的性能。

 

change buffer(第9讲)

要修改的数据不在内存页中,在不影响数据一致性的前提下,InooDB 会将这些更新操作缓存在 change buffer 中。待数据页被读入内存后,再执行change buffer中与这个页有关的操作。需要说明,change buffer本身也是可以被持久化的数据(通过redo log),以防数据丢失。

对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来,假设一个业务的更新模式是写入之后马上会做查询,那么change buffer反而会降低性能。

 

binlog buffer(第23讲)

事务结束之前,binlog先写到buffer中,事务提交时再写到文件中,每个事务单独一块buffer。

 

redo log buffer(第23讲)

先写buffer,再写磁盘,多个事务共用buffer,多个事务的redo log可以穿插写入。

一个事务提交时,可将buffer中这个事务的最后一条log及之前的log,全部写入磁盘(组提交)

在这两个binlog和redo log的buffer之外,IO端本身也有buffer,由IO控制器决定何时持久化到磁盘上。

日志是实现数据可靠性的主要工具,因此,在大多数情况下,应让日志尽可能快地写入磁盘。

 

sort_buffer(第16讲,17讲)

根据sql脚本需要,在内存中对数据进行排序,如果数据量超出buffer size,需要用到临时文件做归并排序。

优先选择全字段排序。如果需要返回的数据字段多、长度长,考虑rowid排序,只把参与排序的字段和id载入sort_buffer,排序后逐行回表,返回结果。

 

join_buffer(第34讲,35讲)

适用BNL算法时,被驱动表没有索引,把驱动表的数据读入join_buffer,然后扫描被驱动表,满足join条件的,作为结果集的一部分返回。

驱动表读入join_buffer的数据,先按这个表的条件进行筛选,只存入join和结果集需要的字段。

join_buffer默认256K,如果放不下驱动表,那就把驱动表分段放入join_buffer,这样对被驱动表要做多次全表扫。为了提高性能,可以增大 join_buffer。

 

内部临时表(第37讲)

执行union语句、group by语句时,MySQL会自动创建内部临时表,存储中间数据,并进行计数、sum、去重等操作。

内存临时表默认大小为16M,如果超出这个大小,就会转成磁盘临时表,默认使用InnoDB引擎。

 

外部临时表(第35讲,36讲)

典型用途:用临时表优化join。

将被驱动表满足条件的数据放入临时表,并在临时表上创建join所需的索引,避免全表扫。

对于其他复杂的查询逻辑,也可以将中间查询过程放入临时表,利用临时表的索引提升效率。

外部临时表只对当前用户会话可见。当前会话结束时,临时表会自动关闭。这种临时表的命名与非临时表可以同名。当重名是,用表名访问的是临时表。

 

 

在基础概念这部分,最困扰我的问题是事务隔离级别的选择。

 

MySQL InnoDB的缺省隔离级别是可重复读(RR),而且这个可重复度不仅解决不可重复度的问题,而且通过gap lock解决幻读的问题。而Oracle的缺省隔离级别是读提交(RC)。

InnoDB为什么有这个设计确定,我的理解是要从其数据的恢复、备份出发。MySQL基于binlog来实现数据的恢复和备份,而且一开始只支持statemen格式。为了实现主库和备库的数据一致,必须解决幻读问题。这个是设计的出发点。

为了在满足数据主备一致的前提上,尽量提升并发能力,提升可用性,采用了undo log、一致性视图等方法。尽管如此,隔离级别从(RC)变为(RR)还有存在性能差异的。

在binlog支持row格式之后,我认为主库上选择读提交(RC)这个隔离级别也能实现主备数据严格一致了。那么,选择隔离级别的主要依据就是读提交(RC)这个隔离级别是否能够满足业务要求了。

而这一点,尚未找到判断的准则,需要在实践中积累。

 

附:丁奇老师在第20讲中的灵魂之问:

“在备份期间,备份线程用的是可重复读,而业务线程用的是读提交。同时存在两种事务隔离级别,会不会有问题?进一步地,这两个不同的隔离级别现象有什么不一样的,关于我们的业务,“用读提交就够了”这个结论是怎么得到的?”

 

本文内容为丁奇老师《MySQL实战45讲》的学习笔记,只是一个提纲。

这门课程的内容和组织方式,每一讲的思考题、大家的留言、老师的点评,都非常棒。

强烈推荐!

 

可识别下图的二维码购买学习,也算顺便打个赏 :-)

极客时间《MySQL实战45讲》

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

黄鹤

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值