芝麻の杂谈

mysql知识整理

28 min

其实关于mysql部分，小林coding讲得很不错，但是我觉得不够系统化，为了拆分多章，作者把很多内容打散了书写的。

为了整理让整个知识连贯，本文决定从底层到上层开始梳理mysql的实现。

数据存储

作为数据库，数据存下来当然是最重要的。那么如何存是一个很有意思的话题。

mysql数据存放：

mysql对于每一张表有一个单独的文件夹，文件夹内分别有三个文件：
db.opt：存放表的默认字符集和字符校验规则
t_order.frm：存放表的结构信息 t_order.ibd：存放着表的数据
所以我们只需要关注t_order这个文件如何存放我们的数据即可

段

这个文件会被分为许多段，比如：索引段、数据段、回滚段等。

每个段的数据保存在一起，这样做也是为了符合计算机的局部性原理。

区

区这个概念是相比于其他存储概念来说显得不那么重要，为什么这么说呢？

一个区=64个连续页。为什么有了页还要有区呢？页是磁盘操作（分配/读取/写入）的最小单位，但是页又太小了，每次只给一个索引结构分配一个页，会导致索引连续的数据实际上可能相隔很远，所以此时，就可以直接给这个索引空间分配一个区。这就是区存在的意义。

页

页是核心概念。B+数的一个节点就是一个页，无论是索引，还是数据。

页由数据行组成，数据行是数据库里的一条记录，当然除此之外还有些冗余信息。

页相当于一个行的list，为了快速检索到一个页中的一行，页引入了槽的概念。一个页有k个槽，可以通过二分法定位出数据在哪个槽里。

一个页的第一槽只会有一行，就是初始行。每个槽的最后一条记录的len位置会记录槽里有多少条数据。

这样，在槽的帮助下，页能够快速二分定位到数据。

行

数据行，也就是一条记录。 行的构成 行的构成非常简单，但是有趣的是，行的头节点并不在最开头。

头节点的左边，分别是倒序的null list和变长字段长度list

头节点的右边，有6字节的row_id隐藏字段，当然，如果头节点具有unique_key的话就不需要这个隐藏字段。

6字节的trx_id，记录的是创建这条记录的事务id，7字节的roll_pointer，记录的这个记录修改前的记录，这两个字段用于MVCC，很疑惑，没关系，两分钟后就开始讲这个部分。

之后的部分就全部都是一条记录的各个字段的值。

所以mysql中为null的字段，会在null list对应的位记为1

MVCC

MVCC，全称为Multi-Version Concurrency Control，多版本并发控制。

是数据库中实现事务隔离性的重要手段，不同事物的读操作不会被另一个事务的写操作阻塞的关键技术。

Read View

当一个事务开始的时候，会为这个事务建立一个快照，但是很显然，这个快照不可能去拷贝数据库的数据，所以只能记录少量数据获得数据库的快照。

我们还知道的是，数据库的更新时候，旧版本会留有一条记录，也就是undo log，而每条记录都有一个指针指向它的前一条记录，也就是undo log。

如果，我们能够在记录住当前事务的id，那么就可以控制当前事务能够看到的记录。

而记录这些信息的，就是Read View。

它一共记录四个信息：

  1. creator_trx_id：当前事务id：
  2. m_ids：当前仍活跃的事务id
  3. min_trx_id：当前活跃最小事务id
  4. max_trx_id：当前活跃最大事务id

这样，在利用MVCC机制进行快照读的时候，读取一条记录的时候就用creator_trx_id和记录中的trx_id进行比较。

有五种情况：

  1. creator_trx_id=trx_id，那当然可见
  2. trx_id < min_trx_id，证明是当前事务开启之前就已经完成修改的，那当然可见
  3. trx_id > max_trx_id，证明是当前事务开启后才创建的，不可见，需要通过roll_pointr找到undo log，然后在对undo log的数据进行同样的操作
  4. trx_id in m_ids，证明是活跃事务修改的，不可见，需要通过roll_pointr找到undo log，然后在对undo log的数据进行同样的操作
  5. trx_id <= max_trx_id and trx_id not in m_ids，证明事务开始前，数据的修改已经提交，当然可见

在可重复读的情况下：事务开启才创建一条Read View

在读已提交的情况下，每次读取数据时候生成一条新的Read View

锁

是不是很疑惑，为什么写了这么多还不到索引，不着急的，索引是更加上层的东西。在有索引前，我们得先有数据。在数据读取和写入的时候，需要考虑冲突这件事情。

锁的类型

这里按锁的粒度来进行第一步划分：

全局锁：锁的是整个数据库，包括数据库的每个一张表

fulsh tables with read lock
一般只在数据库做全局备份的时候使用。

表级锁，表级锁锁的是整张表，一般来说表锁包括：表锁、元数据锁、意向锁、Auto_Inc锁。我们一项一项分析。

表锁

用于对整张表加锁，可以是S锁，也可以是X锁
lock tables table_name read
lock tables table_name write
值得注意的是，如果在当前线程中加了表锁，在释放表锁前不得访问其他的锁。

意向锁

意向锁分为意向独占锁和意向共享锁，插入意向锁并不是意向锁。

意向锁存在的目的是为了快速确认能不能对整张表加锁。

举个例子：
在事务A中向table1加上了行锁S，而此时，事务B需要对整张表加锁，则事务B需要遍历全部的行确认行是否有锁吗？

不需要的，如果事务A向table加上了行锁S，那么同时也会向table1加上一把意向锁S。此时如果事务B想对表table1加表锁，会发现有一把意向锁S，此时锁冲突。

意向锁和意向锁并不冲突，意向锁只会和表锁冲突。

元数据锁

元数据锁锁的不是表的数据，锁的是表的结构，当修改表的结构的时候，mysql会自动给表加上元数据锁。修改表事务结束之后，也会自动释放元数据锁。

Auto_Inc锁

这个锁的主要作用是用来帮助自增主键能够按顺序自增，不会出现冲突。

当一个数据要插入的时候，获取Auto_Inc锁，当插入数据执行完成之后，该锁自动释放，不需要等到事务执行完毕。

同时后续mysql还引入了轻量Auto_Inc锁，只有在需要获取自增数据的时候才加锁，获取自增数据完成后立刻释放锁。

自增锁的应用在主从复制上会存在问题：
如果binlog的存储方式是row的话不会出现问题，毕竟是存储是行的变化。
但是如果存储方式是statement的方式，则会出现问题。在事务执行完成之后，才会写入binlog。

假设自增主键的初始值为0，A事务在time1插入数据1，B事务在time2插入数据2，A事务在time3插入数据3。

此时数据1、2、3在主库的id应该分别为：1，2，3

但是如果从库根据statement的binlog进行同样的插入操作执行，会发现：要先执行A事务，插入数据1、数据3，再执行B事务，插入数据2

此时数据1、2、3在主库的id应该分别为：1，3，2

会出现主从不一致的情况。

如下图所示：

自增主键在 STATEMENT 复制下的不一致示意
自增主键在 STATEMENT 复制下的不一致示意

行级锁

行级锁的类型也比较多，包括：记录锁、间隙锁、临键锁、插入意向锁，这些锁一般都是由引擎管理的。

  1. 记录锁，Record Lock，一般需要加锁可以这么加：
select * from table_name where id = 1 lock in share mode;

select * from table_name where id = 1 for update
  1. 间隙锁，Gap Lock 主要用于可重复读或者串行化的隔离级别。

还是上面的sql select * from table_name where id = 1 for update，如果此时id = 1是没有记录的，则会加上间隙锁和。间隙锁和间隙锁是不冲突的，只和插入意向锁冲突。

  1. 临键锁：Next-Key Lock，是记录锁和间隙锁的组合

  2. 插入意向锁：用来和间隙锁作为冲突的锁，在一个位置插入数据时，需要在这个间隙加插入意向锁，如果这个位置存在间隙锁，则会冲突。

值得一题的是：mysql加锁的时候，会先生成一个锁，然后让锁处于等待状态。锁处于等待状态的时候并不是成功获取到锁了。

加锁时机

一般来说，mysql对单条记录是不会加读锁的。因为MVCC机制的存在，读操作大多数时候是快照读，所以可以不阻塞写操作。

但是当然，开发者也可以主动给读操作加锁，但是需要考虑性能问题。

通常我们的加锁时机包括：更新、删除、添加

不同隔离级别下有哪些行级锁

  1. 读已提交：只有记录锁
  2. 可重复读：记录锁、临键锁、间隙锁。

为什么有MVCC还需要临键锁和间隙锁：主要是为了防止幻读操作。

加什么锁

  1. 如果是通过主键来进行操作范围查询，需要分为两种情况来看
    如果是等值查询，数据存在时会加记录锁，数据不存在时会加间隙锁
    如果是范围查询，一般是对查到的全部记录加上临键锁，但是会有一些情况导致临键锁退化，这部分不在博客里详细讨论。
  2. 如果是通过二级唯一索引来进行操作范围查询，则需要同时对索引记录，和索引记录指向的一级索引都加上锁，但是对一级索引只加记录锁，对于二级索引加的锁同上。具体来说： 如果是等值查询，数据存在时：二级索引加记录锁，一级索引加记录锁；数据不存在时；二级索引加间隙锁，一级索引不加锁。 如果是范围查询，二级索引加临键锁，一级索引检索到的记录加记录锁。
  3. 如果是通过非唯一索引进行操作范围查询，则情况复杂许多：

等值查询： 当查询的记录「存在」时,由于不是唯一索引,所以可能存在索引值相同的记录,于是非唯一索引等值查询的过程是一个扫描的过程,直到扫描到第一个不符合条件的的二级索引记录就停止扫描,然后在扫描的过程中,对扫描到的二级索引记录加的是next-key锁,而对于第一个不符合条件的二级索引记录,该二级索引的next-key锁会退化成间隙锁。同时,在符合查询条件的记录的主键索引上加记录锁。
当查询的记录「不存在」时,扫描到第一条不符合条件的二级索引记录,该二级索引的next-key锁会退化成间隙锁。因为不存在满足查询条件的记录,所以不会会对主键索引加锁。 范围查询： 非唯一索引和主键索引的范围查询的加锁也有所不同,不同之处上在于非唯一索引范围查询,索引的next key lock不会有退化为间隙锁和记录锁的情况,也就是非唯一索索引进行范围查询时,对二级索引记录加锁都是加next-key锁。

这里几乎全部引用自小林coding。

但是需要注意的是，小林coding说查询会加锁，这里是错误的。因为MVCC机制的存在，普通的查询是不会加锁的，一般加锁的情况都是用于更新。

我换了一种表述方式，特别强调了这个部分：操作范围查询

Buffer Pool

这章是后来才决定加到这里，在梳理日志之前需要梳理一下Buffer Pool

之前讲有存储的最小单位是页，每次读取一条记录，如果记录不在Buffer Pool，需要将记录所在的这个页读取到缓存中，这些缓存页，就会被存储在Buffer Pool。

Buffer Pool缓存的内容

数据页、索引页、插入缓存页、undo页、自适应哈希索引、锁信息。

如何管理缓存页

  1. 有一片连续的控制块List，用于记录缓存的信息，每个控制块对应个缓存页，记录缓存页的表空间、页号、缓存页地址、链表节点
  2. 空闲缓存页：由Free链表管理，每个Node指向一个控制块
  3. 脏缓存页：由Flush链表管理，每个Node指向一个控制块
  4. 通过LRU链表管理已经被加载到缓存中的页

缓存淘汰机制

如果需要将一块磁盘页读取到缓存中，但是free链表又不存在空闲页，此时就要对LRU链表进行淘汰。

Mysql的LRU链表设计了young区域和old区域，用以应对预读失效和缓存污染的问题。

预读机制

根据程序的局部性原理，如果读取一个页，那么它的相邻页面被使用到的概率很高，所以也会读取这些页面加入缓存。但是实际上可能这些页根本不会被访问到，这就是预读失效问题。

为了解决预读失效，mysql将lru链表划分为young区域和old区域，young区域是头部，old区域在链表的末端。

如果一个缓存页被预读进来，会被默认放到old区域，直到他被真正使用才会放到young区域，当然，也可以提高进入young区域的门槛，这部分放在缓存污染里介绍。

缓存污染

如果一个sql遍历了数据库的整张表，但是只会便利一次，但是整张表的内容又十分大，缓存会进行频繁的淘汰。同时，热点数据可能被缓存淘汰。

此时为了应对这个问题，mysql提高了old区域进入young区域的门槛，当一个数据被访问两次的时候才会被加入young区域，同时，还要求两次访问的时间间隔大于一定的长度。只有同时满足，被访问，和在old区域停留一秒的条件才会被转移到young区域。

数据写入的流程

  1. 如果数据不在Buffer Pool中，会寻找一个空闲页（若没有，则根据淘汰策略进行替换（如果页面是脏数据，需要写回）），将数据内容从磁盘加载到Buffer Pool。这需要至少一次磁盘IO。
  2. 修改Buffer Pool中的数据为要写入的数据
  3. 将Buffer Pool标记为脏的数据页，等待合适的时机写回

当然，如果我们将这个流程放到事务中来看

  1. 开启事务
  2. 记录undo log
  3. 修改Buffer Pool中的内容并标记为脏数据页
  4. 记录redo log并确认redo log已经落盘
  5. 提交事务

数据写回的时机

mysql采用了一种叫做WAL的策略，也就是Write Ahead Log，先写日志，再写磁盘的策略。通过写入redo log来让mysql拥有掉电恢复的能力。

在下面几种情况，Buffer Pool才会将数据写入磁盘：

  1. 当redo log日志满了的情况下，会主动触发脏页刷新到磁盘
  2. Buffer Pool内存淘汰脏页的情况下
  3. mysql关闭时
  4. mysql认为空闲时

如下图所示（Buffer Pool 与 redo 写入流程示意）：

Buffer Pool 与 redo 写入流程示意
Buffer Pool 与 redo 写入流程示意

redo log也是需要落盘的，为什么选择落盘redo log而不是直接落盘数据呢？

redo log日志的加入是追加写入的，是连续IO，磁盘执行较快，但是数据的写入并不是，随机IO小于数据 redo log只记录对数据页所做的物理操作（例如某个字节从A改成了B），而不是整个数据页的内容，因此redo log通常比实际的数据页要小得多。

日志

日志的类型

  1. undo log：主要用于事务的回滚和MVCC，是事务原子性和隔离型的重要保证。
  2. redo log：主要用于故障后的数据恢复，是事务持久性的重要保证。
  3. bin log：Sever层生成的日志，主要用于数据备份和主从复制。

undo log

在我们之前介绍数据库记录的存储时介绍过，一条记录的组成为：
【变长字段的长度】【null list】【记录头】【row_id】【trx_id】【roll_pointer】【字段内容】【字段内容】

如下图所示（InnoDB 记录结构示意）：

InnoDB 记录结构示意
InnoDB 记录结构示意

其中，roll_pointer就是指向的就是这条记录的undo log, undo log本质上也是一条结构相同的记录。

当在一个事务中执行某项修改时，如果需要取消修改，执行流程图下：

  1. 开始事务
  2. 执行修改操作（记录undo log）
  3. 取消提交
  4. 按照undo log回滚

redo log

为了保证数据的持久性，当一条记录需要更新时，在写完undo log，将数据的更新写入buffer pool之后，需要记录redo log，并且要在redo log落盘之后，才能完成事务的commit。

同时，我们写undo log的时候，也是写入buffer pool之中，没有落盘，redo log也需要记录这条undo log的修改，并且落盘。

当然，redo log也不是每产生一条log就落盘一次，也是会先写入redo log buffer，等待落盘时机

redo log落盘时机：

  1. mysql正常关闭时
  2. 当redo log buffer中记录的写入量大于redo log buffer内存空间的一半时
  3. InnoDB的后台线程每隔1秒,将redo log buffer落盘
  4. 每次事务提交时都将缓存在redo log buffer里的redo log直接落盘（由innodb_flush_log_at_trx_commit参数控制，为0时不主动落盘，为1时每次commit则落盘，为2时，则将buffer内容写入到page cache，但是不fsync，由操作系统选择何时落盘）

redo log文件写满怎么办

redo log是一个file，显然是不可能无限大的，而且存储那么多log的意义不大，一般来说只关注最近的log。 redo log是存在两个文件的，他们是循环写的流程，log1写满之后，往log2内写入内容，log2写满之后，再切回log1写。

同时，还存在一个已经写入到的指针checkpoint，当前写入的位置如果套圈了checkpoint，mysql就会阻塞并将脏页刷新到内存，然后擦除redo log中可擦除的内容。

bin log

上面的undo log和redo log都是InnoDB引擎生成的log，在mysql完成一条更新之后，server层还会生成一条bin log。

bin log对比redo log

  1. 创建对象不同： bin log由server层创建，redo log由引擎层创建
  2. 写入方式不同： redo log是循环写，会有覆盖，bin log是追加写，写满创建一个新的文件，不会覆盖。
  3. 用途不同： bin log用于数据同步、备份，redo log用于数据恢复

写log时机

  1. bin log是在数据写完undo log之后写入
  2. 写入一般是先写入bin log cache（Server层）
  3. 当事务提交的时候，会把bin log cache的内容写入到bin log文件中，但是同样的，也是需要等待系统调用fsync来真正落盘。可以由sync_binlog来控制时机，与AOF较为相似。

两阶段提交

这个部分其实很多时候是难以理解的，为什么要写两份log，为什么log还可能出现差异。

bin log和redo log可能出现的数据差异

  1. 首先我们要关注log的写入时机与落盘时机。 mysql写入数据时，是先写入数据到buffer pool，然后再写入redo log到redo log buffer，之后写入bin log到bin log cache。 待事务提交时，server层和引擎层分别按照落盘计划将redo log和bin log落盘。
  2. 如果redo log 落盘后突然断电，bin log未能落盘，会出现bin log缺失数据更新。导致从库无法同步主库更新，从库数据版本落后主库。
  3. 如果bin log 落盘后突然断电，redo log未能落盘，会出现redo log缺失数据更新。导致主库断电恢复后按照redo log恢复，未能更新数据，但是从库通过bin log更新数据，主库版本落后从库。

两阶段提交流程

mysql使用内部XA事务来协调提交。
事务提交触发后会进入下面两个流程：

  1. 准备阶段 将XA事务ID XID写入到redo log，同时将redo log对应的事务状态设置为prepare，之后将redo log落盘。
  2. 提交阶段 将XID写入bin log，然后将bin log落盘 接着调用引擎层接口，将redo log事务对应状态设置为commit，不需要落盘，只需要写入到page cache即可。

关键点： 事务没提交的时候，redo log可以落盘，但是bin log一定要事务commit才落盘。

mysql 八股笔记