四本书、一个专栏，揉成这篇MySQL（二）

四本书、一个专栏，揉成这篇MySQL（二） - yes的练级攻略的个人空间 - OSCHINA - 中文开源技术交流社区

你好，我是 yes。

这个MySQL系列的第二篇，再来个，开局一张图，文末会给高清图获取方式。

上一篇文章讲了体系结构、存储引擎、日志和索引，这篇文章继续往下讲：分区表和事务。

分区表的应用其实不太常见，与之有类似功能的就是我们常说的分表，两者的区别可以认为一个是 MySQL 帮我们做了分表的操作，而平日常说的分表是在中间层做了手动分表，听起来好像分区表更好啊？MySQL 直接帮我们做了分表，我们啥都不用管了。其实不然，实则还是有些许限制的。

分区表对外表现看起来是一张表，但在存储引擎看来就是多张物理表，在实际存储上也是划分到多个文件中的，只是在 Server 层做了封装，聚拢了一堆分区表使之对外看起来像一张表，所以对待分区表的 SQL 语句和不分区的 SQL 语句是一样的，不需要做任何修改，因为 MySQL 帮我们内部处理了。

看了下官网 8.0 版本的分区表一共支持类型四种的分区：

• RANGE 分区，按选择的列值根据给定的范围来划分行数据，分为不同分区

• LIST 分区，和 RANGE 分区类似，不同点在于 LIST 是离散值，RANGE 是连续的。

• HASH 分区，根据用户定义表达式计算选择的列值的返回的值来选择分区。

• KEY 分区，和 HASH 分区类似，不同点在于使用的是 MySQL 提供的哈希函数。

这四种分区有个条件：作为分区依据的数据必须是整型。

我举个 RANGE 分区的例子，如果现在有一张表，有个字段是 date(方便起见，平日不要用关键字)，存储的值都是2021-05-01、2021-06-01，此时如果要按这一列作为分区表的键，建表语句写成这样。

CREATE TABLE Yes (id INT, date DATE)
PARTITION BY RANGE (TO_DAYS(date)) (
       PARTITION p0 VALUES LESS THAN (TO_DAYS('2021-05-01')),
       PARTITION p1 VALUES LESS THAN (TO_DAYS('2021-06-01'))
)；

就比较麻烦，所以 MySQL 又提供了 COLUMNS 分区，在 RANGE 和 LIST 分区的时候，就可以不用通过一些函数来转了，直接上列的比较。

CREATE TABLE Yes (id INT, date DATE)
PARTITION BY RANGE COLUMNS (date) (
       PARTITION p0 VALUES LESS THAN ('2021-05-01'),
       PARTITION p1 VALUES LESS THAN ('2021-06-01')
)；

还有一个 Subpartitioning，也就是子分区需要提一提。可以对由 RANGE 或 LIST 分区的表进行子分区，子分区可以使用HASH或KEY分区，这也被称为复合分割。

就是分区之下再分区，对于一些比较热点的数据可以这样做，区分度更细一些，可以看下官网给的例子：

再来看看分区表一些限制吧，从《高性能MySQL》来看，主要几个限制有：

• 一个表最多只能有1024个分区。

• 主键和唯一索引列必须作为分区字段。

• 还有前面提到的整数的约束，不过有些可以用 COLUMNS 分区避免。

• 书中还提了外键，不过我相信现在也没人用外键了。

主要还有一个分区表打开行为，可以理解为 MySQL 需要统筹全局的，所以在第一次访问时，多个分区表都得打开汇总到 Server 层，并且因为 Server 层认为是同一张表，所以 MDL 锁是共用的，因此在做 DDL 的时候会同时锁多个分区的表，影响会比较大。

关于分区表还有个性能问题常被提到：

当不分区的时候，假设查询数据要用到一个二级索引，需要两次执行搜索树查询，也就是两次磁盘 IO，而分区表之后，一次访问多个分区表(没用上分区键)时，需要搜索多个分区表的二级索引，例如分了10个区，那就得 10*2=20 次磁盘IO。但手动分表其实也有这个问题，没用上分区键的时候，也是得多表扫描，也是多了很多磁盘IO。

所以很多限制说的是分区表，但是手动分表同样也有这些限制，不过 DDL 这个确实不太一样，可以一个表一个表的改，不用一次锁多个，并且没有分区表的那种打开行为。还有主键和唯一索引的处理，如何分配主键，如何保持全局唯一列的唯一性等，这都是手动分表需要考虑的。

之所以现在基本上都是手动分表，我个人觉得比较自由和方便吧，不论是利用业务代码分表，还是使用中间件，这些都已经很成熟了，并且手动分表一般还有分库，可以部署在不同的服务器上，这都是分区表无法实现的。

还有一些分区表的优点，比如分区的数据好选择性删除之类的，手动分表也是一样的。我个人觉得关于分区表大致了解这么多就差不多了，主要学习精力还是投入到手动分表这个主流上。

事务的目的就是将一种一致的状态转换为另一种一致的状态，说的再直白一些就是一次操作所有的修改要么都修改成功，要么都不修改成功。

在很多场景下，事务至关重要，且都需要满足 ACID 四个特性：

• A：原子性，atomicity

• C：一致性，consistency

• I：隔离性，isolation

• D：持久性，durability

具体 ACID 的定义就不提了，老生常谈。上面这四个特性是事务实现的标准，但是严格实现事务性能上会打些折扣，所以很多数据库厂商实现都了提供残血版的事务，任君采撷，比如 Oracle 默认事务隔离级别是 RC ，不满足隔离性。不过 InnoDB 的默认事务隔离级别是 RR，满足 ACID，当然也有提供 RC 的。

先说下事务隔离级别，主要针对隔离性：

• 读未提交，READ UNCOMMITED，简称 RU，最宽松的限制，即一个事务的修改还未提交，另一个事务就能看到修改的结果，会产生脏读现象。

• 读已提交，READ COMMITED，简称 RC，即一个事务只能读到另一个事务已经提交的修改，所以在一个事务里面的多次查询可能会得到不同的结果，因为第一次查询的时候，另一个事务还未提交，所以看不到修改的结果，第二次查询的时候，另一个事务提交了，因此读到了修改后的结果，所以两次查询结果不一致，称之为：不可重复读。

• 可重复读，REPEATABLE READ，简称 RR，它比 RC 更严格，即一个事务开始的时候读不到，那之后也读不到，也就是一个事务内的多次读结果是一致的，但是有幻读情况，即第一次读拿到了四行数据，第二次读拿到了五行数据，因为有新插入的行，不过 InnoDB 利用 MVCC 解决了 大部分幻读的情况，利用 update当前读再 select 的幻读无法解决， 之前文章已经提到，这里不再赘述。

• 串行化，SERIALIZABLE，最严格的模式，即这个隔离级别的读写访问会把需要遍历到的记录上锁，这样另一个事务要访问对应的记录时候就被阻塞了，需要等待上一个事务提交之后，才能继续执行，所以叫串行。在 InnoDB 中，非自动提交时，会隐式地将所有普通的 SELECT 语句转换为 SELECT..LOCK IN SHARE MODE来满足这个隔离级别。

事务还有好几种分类，在《MySQL技术内幕》中，列了以下五种：

• 带有保存点的扁平事务

• 分布式事务

扁平事务是我们最常用的，被 begin 和 commit 包裹的属于同一层次的事务操作。带有保存点的扁平事务其实就是多了个 savepoint，可以理解为执行过程中的存档，当后面的操作失败可以回滚到保存点那个位置，而不是全部回滚。

而保存点的事务如果系统崩溃了，那存档就都没了，所以有个链事务，每一次的存档其实就是提交事务并且开始下一次事务，等于是将多个事务链接起来，当前面的事务提交之后会释放当前事务持有的锁，并开启下一个事务，并且回滚也只能回滚当前事务，前面提交的事务无法回滚，这就是和带有保存点的扁平事务的区别。

嵌套事务就很好理解，事务中套了子事务，用过 Spring 的应该对嵌套事务不会陌生。

再者是分布式事务，这个我之前专门写过文章了，可以看看

InnoDB 是如何实现事务的？

锁、redo log 、undo log、MVCC。

锁其实很好理解，为了防止数据的并发修改，满足事务的隔离性。

redo log

redo log，重做日志，可以用来满足事务的持久性，因为它提供崩溃恢复功能。这里有一个术语，叫 WAL，Write-Ahead Logging，就是日志先行。当 InnoDB 修改数据的时候，会先记到 redo log 上，然后修改内存页的数据，就返回 ok 了，不会修改磁盘上的数据文件，所以意外崩溃了没关系，redo log 里面都帮你记好了。

那不能直接记到数据上吗？为啥要先记在 redo log 上？

我们平常更新数据的时候，数据可能分布在磁盘上各个位置，如果每次修改操作要落盘，那么会有很多随机 IO 的查找与落盘(与之相关的还有个 change buffer ，之后文章再提)，所以把这个修改先记录到 redo log 中，这个 log 是按顺序追加写入的，顺序IO，落盘的效率高，所以是先写日志，之后再落盘数据，因此叫日志先行，WAL。

不过这里需要提一下，其实 redo log file 的写入不是完全顺序 IO 的，在 redo log 有 group 的概念，log 确实往后顺序追加写入的，但是需要维护一些元信息，而这些元信息在 group 中的第一个 redo log 头部，所以需要维护这些信息，因此不是完全顺序IO。

前面说到崩溃恢复能力来自于 redo log，是因为在数据落盘之前会记录到 redo log中，所以恢复的时候可以根据 redo log 还原数据，不过这里还是要看配置参数innodb_flush_log_at_trx_commit的值。

redo log 是物理文件，内存里面有个 redo log buffer，log 先写入 buffer 中，再适时落盘，而 redo log buffer 没有打开 O_DIRECT ，所以还是需要跟操作系统的缓存交互，因此 redo log buffer 有写入操作系统缓存的 write 操作和刷盘的 fsync 操作。

可以看到，真正的落盘其实有两个动作，在 innodb_flush_log_at_trx_commit 有三个值可以配置：

• 0，当事务提交时修改只是存在 redo log buffer 中。

• 1(默认值)，当事务提交时会 redo log buffer 写入到操作系统缓存中，并刷到磁盘文件上。

• 2，当事务提交时都只是把 redo log 写到 page cache 中。

如果配置是 0 ，那当 MySQL 突然挂了，数据就丢了。当配置是 2，那 MySQL 挂了没事，如果是机器宕机了，那数据也没了，所以只有配置为 1 的时候，才能保证已经提交事务的数据一定被持久化了，但是刷盘效率肯定比不刷盘低，所以一些允许数据丢失的场景下，可以修改配置使得性能提升。

其实 InnoDB 还有个后台线程 Master Thread 在后面刷盘，即使这个事务还未提交，每秒都会把 log buffer 中的内容持久化到磁盘中，因此如果是大事务，那么提交事务时候速度也很快，因为之前的修改已经落盘啦，不会只等提交事务那一刻落盘。

这里还有一个组提交的概念，从上图可以看到 log buffer 先写入到 page cache(这是内存写，很快)，然后需要 fsync 到磁盘上，这个步骤慢，所以减少 fsync 的次数和增加每次 fsync 的数据量是提高性能的办法。

因此就有个组提交的概念，当有多个并发事务发生，且都已经写入 redo log buffer 中等待刷盘，此时第一个事务刷盘会带着把后面事务的数据一起刷盘，所以一次 fsync 调用，刷了多个事务的数据，后面的事务提交就直接返回了，这就是组提交，提升了 IOPS。

再说下 redo log file 这个物理文件，里面存放的是物理日志，物理的意思：简单地认为log存储的内容是哪个表空间的哪一页的哪个地方的值是什么，所以说是物理的，即使重放多次也是幂等的。

这个文件是循环写入的，比如上图中有4个文件，可以认为先写 file1，写满了之后写 file2，再file3、file4，如果file4 也满了，会再回头来写 file1。

有些同学可能就有疑问了，这样循环写入，那 file1 之前的内容不就被覆盖了嘛？是的，但是只有那些修改已经应用到真正的记录上的 redo log 才可以被覆盖，也就是对应的数据脏页已经刷盘了的数据。

这不难理解，因为 redo log 的作用就是为了崩溃恢复，那真正的数据已经落盘了就用不到对应的 redo log 了，所以这部分数据可以被覆盖，对应的有个叫 checkpoint_lsn 的值，理解为在这个 lsn 之前的 log 对应的数据都已经应用刷盘了，所以在这个之前的 log 都可以被覆盖，具体之后详细说 redo log 再谈，看了先看下图，理解下。

undo log

undo log 用于事务的回滚，简单地说就是保存数据的历史版本，好使得事务执行失败回滚的时候，可以恢复之前的样子，所以修改数据的时候会不仅会生成 redo log ，还会生成 undo log，与 redo log不同的是，undo log 是逻辑日志，看下图就理解了。

当一条记录被多次修改，并且事务都未提交的时候，undo log 的记录演变如下图所示：

所以说可以根据 undo log 逻辑回滚到之前的样子，就是反着来记录，insert 记录 delete 、delete 记录 insert。

从上图可以看到每个事务自己沉浸在自己的世界里，即使后面有事务修改了数据，但在它自己看来还是之前的值，这就是多版本并发控制，MVCC。

所以利用 undo log 实现 MVCC，满足了非锁定读的需求，提高了并发度，有关 MVCC 下篇单独写，因为这个概念比较重要，面试常问。

还有，undo log 也是需要持久化保护的，所以写 undo log 也会记录相应的 redo log。

这篇先这样了，下篇 MVCC ，然后 SQL 调优、锁，之后慢慢安排。

我的一对一解答服务持续开放，不走知识星球直接私聊我。

个人微信：yes_oba

思维导图的话，公众号后台回复 MySQL 即可获得。

推荐阅读：

• 《MySQL技术内幕 InnoDB存储引擎》

欢迎关注我的公众号【yes的练级攻略】，更多硬核文章等你来读。

---

我是yes，从一点点到亿点点，我们下篇见。