HenryKang's Docs

Innodb基础知识

20分钟阅读

Overview

了解 MySQL 的逻辑结构、SQL 语句的执行过程等，重点了解 Innodb 在应用方面的一些原理和特性。其他还有一些索引、事务、日志等杂项的主要目的是先列个大纲，后面另起篇幅记录。

参考资料：《MySQL 实战 45 讲》、《MySQL 技术内幕》……

MySQL 的逻辑结构

如图是 MySQL 服务器的逻辑结构，主要分为两层，Server 层和引擎层，两层分离，引擎以插件的形式向上提供服务。还有一点要注意的是引擎基于表而不是数据库。

下面列出每一部分的功能作用和要注意的小点：

  • 连接器：负责与客户端建立连接、获取权限、维持和管理连接。

    1. 用户名密码验证成功后，连接器会在权限表中查找该用户的权限作为此次连接时的权限，即便权限被更改，只要这次连接不断，权限就不改变。
    2. 使用 show processlist 命令可以查看目前所有的连接；
    3. 长连接长时间（默认 8 小时）空闲，连接器就会自动断开该链接；
    4. 长连接的缺点：每个连接中都保存了一些运行过程中使用的信息，久而久之占用内存越来越大。可以定期断开长连接，mysql5.7 之后可以通过 mysql_reset_connection 重置连接（不需要断开重连，只是恢复到刚刚连接的状态）。

  • 查询缓存：以键值对的形式缓存近期执行的 SQL 语句和结果集。一个 SQL 到来时，会先在缓存中查找，若命中则可以将缓存的结果集直接返回。

    1. 发生增删改操作，一张表的缓存就得失效，所以缓存比较适合很长时间才会更新静态表；
    2. MySQL8.0 将查询缓存这个模块直接去除掉了，大概是因为现在都会在业务层实现这个查询缓存，让数据库来实现查询缓存浪费了数据库服务器的性能。

  • 分析器：包括 词法分析 和 语法分析 两部分。词法分析负责分析这条语句中的关键词都是什么，如表名、列名都存不存在，是否合法；语法分析主要负责判断 sql 语句是否符合语法。

  • 优化器：先 判断权限，根据运行时的一些信息，选择 MySQL 认为的最优的执行计划，如使用那个索引，多表连接的顺序等。

  • 执行器：同样先判断该链接有没有相关权限，若有，则按照执行计划调用相关引擎的接口去执行查询。

    1. 为什么优化器和执行器都要判断权限？我想是因为执行计划可以缓存，当使用到缓存的执行计划时，就没有经过前面的优化器判断权限，所以在执行之前判断权限是有必要的；
    2. 查询缓存命中，在结果返回之前也会判断权限。

InnoDB

InnoDB vs MyISAM

  1. InnoDB 支持事务和外键，MyISAM 不支持。
  2. InnoDB 使用聚集索引，主键索引的叶节点保存数据，辅助索引的叶节点保存主键信息，查询可能需要回表；MyISAM 使用非聚集索引，主键索引和辅助索引没有区别，叶节点保存的都是指向数据的指针，不用回表。
  3. MyISAM 维护了表中的记录条数，所以 select count(*) 返回很快；InnoDB 没有保存记录条数，由于 MVCC，不同事务看到的记录数可能不一样。
  4. InnoDB 支持的锁粒度到行锁，MyISAM 只支持到表锁，所以 MyISAM 的并发度没有 InnoDB 高。
  5. 日志方面 InnoDB 有自己的 redo log，而 bin log 是 server 层提供的，不属于引擎层。
  6. MyISAM 支持全文索引，InnoDB 在 MySQL5.7 后也支持全文索引。

InnoDB 逻辑存储结构

如上图所示，InnoDB 的逻辑存储结构包括：表空间、段、区、页、行。类似操作系统内存管理中的段页式存储管理，InnoDB 引擎将数据存放在表空间中，表空间中根据功能不同又有不同分段，如数据段、索引段、回滚段等，每一段中又进行分区，分区的目的是合理组织数据页，数据页中放的是真实的一行行数据。

  • 表空间

需要注意的是 MySQL5.6 之前 innodb_file_per_table 参数默认为 off，即使用 innodb 引擎创建的表，其数据、索引、日志信息、事务信息等都会存储在 ibdata1 这个共享表空间中。表结构还是存储在后缀为 .frm 的文件中。

将该参数设置为 on，则会将数据和索引、插入缓冲位图（Insert Buffer Bitmap）分离出来存储在后缀为 .ibd 的文件中，表的其他信息还存在共享表空间中。

建议启用该参数，即将数据和索引分离出来存储。否则则可能会发生这样的现象：删了表中很大一部分数据，发现表空间大小没有多大变化，其实是因为删除后 MySQL 并不会去回收共享表空间中的碎片，只是标明了一下哪些页被删除，方便以后复用，因为磁盘整理是很耗时的。而如果将数据和索引分离出来，删除表时也会删除 .ibd 文件，回收空间。

  • 段

将数据按照不同的类型，划分为数据段、索引段、回滚段等，段的目的是方便扩展，为区、页提供一个相对连续的空间，为程序的局部性原理服务，其中段的增长、页的分裂等由引擎本身维护。

  • 区

段中的区可能不连续，但是一个区中的数据页一定是连续的，每个区都是固定 1 MB，InnoDB 数据页默认 16K，即一个区中有 64 个连续的数据页，每次 IO 都会申请 4-5 个区换入内存（局部性）。

  • 页

默认 16K，可以通过 innodb_page_size 设置其大小为 4、8、16K，对一个库中的所有表都生效，并且不能再更该。

InnoDB 内存管理

这里说的内存管理主要是指 InnoDB 对 缓冲区的管理，包括从磁盘读取到内存中的索引页、数据页，及运行时产生的信息等。

数据页和索引页的缓存管理

InnoDB 缓冲区数据页的换入换出采用 LRU 算法，维护一个 LRU 列表，这个列表的最前端放最频繁使用的数据页，末端是最近最少使用的页，即要淘汰换出的目标页。

需要注意的是，每次换入新数据页的时候并不是直接插入列表的前端，而是放在 midpoint 位置，这个位置默认在 LRU 列表的 5/8 处，使用 innodb_old_blocks_pct 参数设置 old 区域的百分比（默认 37，即 3/8）。midpoint 前面称为 new 区域，后面称为 old 区域。

为什么这样设计呢？设想这样一个场景，有一个 冷门的查询 要扫描大量数据，这时就要换入许多新的数据页，而这些数据页并不是热门数据，如果直接将它们链入 LRU 列表的头部，以前的热门数据页很有可能被换出，会使缓存的命中率降低。

使用 innodb_old_blocks_time 参数设置新数据页，需要多少毫秒会被加入到 LRU 列表的前端。

那当 MySQL 刚启动时，LRU 列表是空的怎么办呢？其实内部还维护了一个 FREE 列表，新数据页过来先放入 FREE 列表，经过 blocksTime 后转入 LRU 列表。还有当 LRU 列表中的数据页脏了时后，会转入 FLUSH 列表，准备进行刷脏页。

redo log 的缓存管理

redo log buffer 不需要很大，因为后台线程会保证每秒将该缓冲区内容写入 redo logo 文件，同时当一个事务 commit 时、redo log buffer 剩余空间少于 1/2 时，都会写入 redo log 文件。

  • 这里提到了 redo log 就稍微小记一下 redo logo 的特点：

如果每次执行 DML 数据页变脏都要写回磁盘的话，那就有点奢侈了，而且刷盘的时候发生崩溃那内存中的数据就丢失了。所以 Innodb 采用 Write-Ahead Log（WAL）策略，先写 redo log 再落盘。

提高了性能同时也保证了一定的安全性，崩溃后可以根据 redo log 中的 Checkpoint 指示的位置进行恢复。Checkpoint 指示了是否已经刷盘的位置。

这里 redo log 还采用了 两阶段提交 确保和 binlog 的一致性。

其他缓存

像上图有的一些没提到的，如插入缓冲、锁信息、自适应 hash 索引、数据字典信息，后面多少会提到。

InnoDB 一些特性

插入缓冲（Insert Buffer)

插入缓冲目的是优化非唯一辅助索引的插入性能，减少随机写导致的磁盘 I/O，本质是将多个写入操作，延迟整理后再写入磁盘。

考虑使用自增主键索引，在插入数据时，总能快速定位到 B+ 树最右侧的叶子节点，顺序写入，磁盘 I/O 已是最优。而随机的主键索引，由于需要校验唯一性，就必须同步立即找到该键对应的位置进行判断，就得调入其他数据页导致磁盘 I/O，这时使用插入缓冲没有意义，这也是为什么唯一索引不适用插入缓冲。

当使用非唯一辅助索引时，有插入操作，若对应的数据页在内存中，则直接写入。若该数据页没有在内存中，则先将这个插入放到 Insert Buffer 中。每隔一段时间将 Insert Buffer 和该索引的数据页进行合并。

  • 何时 merge？
    1. 后台主线程会根据最近的 merge 情况动态判断要不要再次进行 merge。
    2. 当辅助索引数据页被调入缓存时，此时肯定是由于 select 造成的，所以调入相关数据页后进行 merge，再返回 select 的数据。
    3. 当一次在辅助索引数据页上的插入检测到 Insert Buffer Bitmap 指示该辅助索引的数据页上可用空间不足 1/32 时，会触发强制 merge。

Insert Buffer 只针对 insert 操作，Change Buffer 则针对 DML 操作，适用的对象同样都是非唯一的辅助索引。同样，在访问相关数据页时和后台定期进行 merge。

两次写 (Double Write)

两次写的目的是保证数据页写入的原子性，防止出现“部分写”的情况，确保数据的一致性、完整性。

系统宕机时，可能某个数据页只落盘了一半，导致数据页损坏，使用 redo log 也无法恢复，因为 redo log 记录的是数据页上的物理修改，只能修复不正确的数据，但无法修复损坏的数据。

来自MySQL技术内幕

两次写机制在内存和磁盘上 (共享表空间 ibdata1 中) 都开辟出了一块 2MB 大小的区域 (Double Write Buffer)，当有数据页需要落盘时：

  1. 先将这个完整的数据页复制到内存中的 Double Write Buffer；
  2. 再将内存 Double Write Buffer 中累积的数据页写入磁盘中的 Double Write 区域；
    • 这一步是顺序写，效率很高，保证了数据页的快照已经被落盘；
  3. 然后才将数据页，写回到磁盘上他们所在的离散的位置 (.ibd 文件)，属于随机写。

本质是先写备份、再写原文件，避免写原文件时损坏。崩溃恢复时，如果检测到原数据页损坏，那么就先从备份中恢复，修复数据页，然后再应用 redo log；如果写入备份时宕机，备份损坏，那么原文件一定是好的，直接应用 redo log 即可。

自适应哈希索引 (Adaptive Hash Index)

InnoDB 会监控对数据页的查询，根据查询频率来为热门数据页动态建立哈希索引，提升索引的效率。注意只会对缓存中的数据页进行哈希。这个我们开发人员无法干预。

异步 IO(Asynchronous IO，AIO)

同步 IO 每次请求 IO 操作都要阻塞，等待上一次同步 IO 请求完成。若想要连续请求多个数据页，那同步 IO 逐次请求效率就太低了，于是就有了 AIO。可以在发出一个 IO 请求后不必等待响应，立即发送下一个请求，优势是可以对 IO 进行 merge 操作，例如将对连续数据页的 AIO 请求合并为一个大的 IO 请求一次性提交。

索引

  • MySQL 中有 B+ 树索引、全文索引、哈希索引，但不同引擎支持的索引有所不同：
    • Memory 引擎不支持事务，锁粒度最小为表锁，支持 B+ 树索引、hash 索引、不支持全文索引。
    • MyISAM 和 InnoDB 都支持 B+ 树索引、全文索引，不支持 hash 索引。
      • 对于 B+ 树索引而言，MyISAM 的叶节点保存的是指针，而 InnoDB 的叶节点保存数据。
      • 可以通过附加一个字段存放一个散列值来实现伪哈希索引。
  • 需要知道：
    • 索引是怎么放的
    • 索引的查询过程
    • 各种不同查询条件下的查询过程是怎样的
    • 聚集索引、非聚集索引……
  • 索引的使用要考虑：
    • 主键索引的长度
    • 覆盖索引
    • 最左前缀匹配
    • 索引下推
    • 索引失效的情况

参考：MySQL索引

事务、并发控制、锁

  • 事务的 ACDI 特性
  • 几种问题与隔离级别
  • MySQL 的 MVCC，如何解决幻读问题的
  • 各种锁及加锁机制、锁的原理、两段锁协议

参考：MySQL事务与并发控制和锁

日志

  • 了解 binlog、redo logo、WAL 机制
  • 了解日志的作用
  • 了解错误日志、查询日志

参考：MySQL日志

一个事务提交并将数据落盘的完整流程

场景：执行 update 语句，例如 update user set age = 20 where id = 1，有主键索引 idx_id 和辅助索引 idx_age。

事务执行阶段：

  1. 事务开始，查找数据页，若不在内存 (buffer pool) 中则先调入，修改 buffer pool 中的该数据页，并将其标记为“脏页”；
  2. 记录 undo log 以便事务撤销时进行回滚操作；
    • 修改 undo log 本身也会产生 redo log 确保回滚操作的持久性；
  3. 记录 redo log 到缓冲区，并且更新辅助索引 (也会产生 redo log)；
  4. server 层记录 binlog 到缓冲区；

事务提交阶段：

  1. 将本次事务涉及的 redo log 从缓冲区写入磁盘，即第一阶段 prepare 提交；
  2. Server 层将 binlog 从缓冲区写入磁盘，并通知引擎层；
  3. 进行第二阶段提交，即将 commit 标志写入 redo log 文件；
    • 该阶段完成标志着事务已经持久化；

数据页落盘阶段：

  1. 使用 double write 将数据页写回磁盘；
  2. 更新 checkpoint，表示这部分数据已经安全持久化。

关系图谱

反向链接