《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。

过程如下图所示：

图中浅色为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讲》的学习笔记，只是一个提纲。

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

强烈推荐！

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