MySQL-进阶篇 ( InnoDB 引擎 )

逻辑存储结构

• 表空间 ( ibd 文件 )，一个 mysql 实例可以对应多个表空间，用于存储记录、索引等数据
  • Linux 里的 mysql 数据文件存放：cd /var/lib/mysql 切换后 ll 显示内容
• 段，分为数据段 ( Leaf node segment 叶子节点 )、索引段 ( Non-leaf node segment 非叶子节点 )、回滚段 ( Rollback segment )， InnoDB 是索引组织表，数据段就是 B+ 树的叶子节点，索引段即为 B+ 树的非叶子节点。段用来管理多个 Extent ( 区 )。
• 区，表空间的单元结构，每个区的大小为 1M。默认情况下，InnoDB 存储引擎页大小为 16K，即一个区中一共有 64 个连续的页。
• 页，是 InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
• 行，InnoDB 存储引擎数据是按行进行存放的
  • Trx_id：每次对某条记录进行改动时，都会把对应的事务 id 赋值给 trx_id 隐藏列
    • 最后一次操作事务的 id
  • Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到 undo 日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

架构

• MySQL5.5 版本开始，默认使用 InnoDB 存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是 InnoDB 架构图，左侧为内存结构，右侧为磁盘结构

  

左侧内存结构部分：

• Buffer Pool：缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据 ( 若缓冲池没有数据，则从磁盘加载并缓存 )，然后再以一定频率刷新到磁盘，从而减少磁盘 IO，加快处理速度
  • 若是没有缓冲区， 增删改查时，每一次数据库都要操作磁盘空间，就会存在大量的磁盘 IO，非常耗费性能。有了缓冲区，每次就先操作缓冲池中的数据
  • 缓冲池以 Page 页为单位 ( 上图中深浅不一的方形块就是页 )，底层采用链表数据结构管理 Page。根据状态，将 Page 分为三种类型：
    • free page：空闲 page，未被使用
    • clean page：被使用 page，数据没有被修改过
    • dirty page：脏页，被使用 page，数据被修改过，其中数据与磁盘的数据产生了不一致 ( 缓冲区数据还没更新到磁盘中 )
• Change Buffer：更改缓冲区 ( 主要是针对于非唯一的二级索引页 )，在执行 DML 语句时，如果相关数据 Page 没有在 Buffer Pool 中，不会直接操作磁盘，而会将数据变更操作放在更改缓冲区 Change Buffer 中，在未来数据被读取时，再将数据合并恢复到 Buffer Pool 中，再将合并后的数据刷新到磁盘中
  • 8.0 之后引入的
  • 存在意义：与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘 ( 每次操作还都是随机的磁盘 IO )，会造成大量的磁盘 IO。有了 Change Buffer 之后，我们可以在缓冲池中进行合并处理 ( 不用每次都操作磁盘了 )，减少磁盘 IO
• Adaptive Hash lndex：自适应 hash 索引，用于优化对 Buffer Pool 数据的查询。InnoDB 存储引擎会监控对表上各索引页的查询，如果观察到 hash 索引可以提升速度，则自动建立 hash 索引，称之为自适应 hash 索引。
  • 自适应哈希索引，无需人工干预，是系统根据情况自动完成。
  • 参数：adaptive_hash_index
  • ( InnoDB 默认不支持哈希索引，哈希索引最大的优势就是快 ( 无哈希冲突下 )，但是不适合范围查询，只适合等值匹配 )
• Log Buffer：日志缓冲区，用来保存要写入到磁盘中的 log 日志数据 ( redo log、undo log )，默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O
  • 设置参数：
    innodb_log_buffer_size —— 查看缓冲区大小
    innodb_flush_log _at _trx_commit —— 控制日志刷新到磁盘时机：
    • 1：日志在每次事务提交时写入并刷新到磁盘
    • 0：每秒将日志写入并刷新到磁盘一次
    • 2：日志在每次事务提交后写入，并每秒刷新到磁盘一次

右侧磁盘结构部分：

• System Tablespace：系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。( 在 MySQL5.x 版本中还包含 InnoDB 数据字典、undolog 等 )

  • 参数：innodb_data_file_path
  • 8.0 版本后简规划了

• File-Per-Table Tablespaces：每个表的文件表空间包含单个 InnoDB 表的数据和索引，并存储在文件系统上的单个数据文件中。

  • 参数 ( 默认开启 ) ：innodb_file_per_table
  • 开启：每张表都有一个独立的表空间
    • 存表结构和数据索引

• General Tablespaces：通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间

  -- 创建表空间
  CREATE TABLESPACE xxx ADD -- xxx 为指定的表空间名字
  DATAFILE 'file_name' -- 关联的表空间文件
  ENGINE = engine_name; -- 指定存储引擎
  
  CREATE TABLE xxx... TABLESPACE ts_name; -- 创新表关联到 ts_name 表空间  
  

• Undo Tablespaces：撤销表空间，MySQL 实例在初始化时会自动创建两个默认的 undo 表空间 ( 初始大小 16M )，用于存储 undo log 日志

• Temporary Tablespaces：InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据

• Doublewrite Buffer Files：双写缓冲区，innoDB 引擎将数据页从 Buffer Pool 刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据

  • ib_16384_0.dblwr
  • ib_16384_1.dblwr

• Redo Log：重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲 ( redo log buffer ) 以及重做日志文件 ( redo log )，前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用

  • 循环不会永久保存，每隔一段时间会清理之前没有用的。当事务提交后，对应的 Redo Log 意义就不大了，因为作用就是为了保证异常时进行数据的恢复，从而保证事务的持久性
  • 以循环方式写入重做日志文件，涉及两个文件：
    • ib_logfile0
    • ib_logfile1

后台线程

• 内存结构部分 —— 后台线程 ——> 磁盘结构部分

  • 作用就是：将 InnoDB 存储引擎缓冲池中的数据在合适的时机刷新到磁盘文件当中

• Master Thread
  核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo 页的回收

• IO Thread
  在 InnoDB 存储引擎中大量使用了 AIO ( 异步非阻塞 IO ) 来处理 IO 请求，这样可以极大地提高数据库的性能，而 lO Thread 主要负责这些 IO 请求的回调

  线程类型	默认个数	职责
  Read thread	4	负责读操作
  Write thread	4	负责写操作
  Log thread	1	负责将日志缓冲区刷新到磁盘
  lnsert buffer thread	1	负责将写缓冲区内容刷新到磁盘

  • show engine innodb status; 查看 innodb 引擎状态信息，在其中就可以看到各 AIO

• Purge Thread
  主要用于回收事务已经提交了的 undo log，在事务提交之后，undo log 可能不用了，就用它来回收

• Page Cleaner Thread
  协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞

事务管理

介绍回顾

• 事务
  • 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
• 特性 ( ACID )
  • 原子性 ( Atomicity )：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
  • 一致性 ( Consistency )：事务完成时，必须使所有的数据都保持一致状态
  • 隔离性 ( Isolation )：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性 ( Durability )：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

特性的保证

• redo log 和 undo log 两个日志保证事务的原子性、一致性和持久性

  • 其中 redo log 保证事务的持久性，undo log 保证事务的原子性
  • redo log 和 undo log 一起保证了事务的一致性

• 锁和 MVCC 保证事务的隔离性

redo log 日志

• 保证了事务的持久性

• redo log：重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

  • 该日志文件由两部分组成：重做日志缓冲 ( redo log buffer ) 以及重做日志文件 ( redo log file )，前者是在内存中，后者在磁盘中。
  • 当事务提交之后会把所有修改信息都存到该 redo log 日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用

• 流程：客户端发起多个增删改请求去操作 MySQL 服务器，MySQL 服务器的 InnoDB 引擎中分为内存结构和磁盘结构，磁盘结构存放很多的 xxx.ibd 数据文件，内存结构中有很大的 Buffer Pool 缓冲池，

  • 缓冲池中缓冲了一个一个数据页信息，客户端执行语句 SQL 会先到缓冲池中查看有无更改数据

  • 如果缓冲池中没有的话就通过后台线程从磁盘中读取数据缓存在缓冲池中，使得客户端执行语句直接操作缓冲区数据

    • 此时缓冲区数据和磁盘数据不同，即脏读，所以要每隔一段时间将数据刷新到磁盘中
    • 若是事务已经提交，数据也都成功更改，但是在刷新到磁盘时出现了问题，就无法保证持久性了

  • redo log 出现之后，每次增删改的数据都会记录在内存结构中缓冲池外的 Redolog buffer 中，在其中记录数据页的数据变化，这样在提交事务时，会把 Redolog buffer 中数据页的变化直接刷新到磁盘结构中，然后持久化地保存在磁盘文件当中 ( 存于 ib_logfile0/1 )，这样脏读数据刷新到磁盘时出错的话就会通过 redo log ( 保存在磁盘文件中的 ) 进行恢复

    • 当顺利刷新到磁盘中的话，redo log 在磁盘中记录的数据变更也就不再需要了，所以每隔一段时间会清理 redo log 日志，即 0 与 1 是循环写的，并不会永久的保留下来

• 若是不用 redo log 的话，直接将变更的数据页刷新到磁盘中，这样的话会存在严重的性能问题 ( 刷新时会存在大量的随机磁盘 IO，性能很低 )。而用了 redo log 的话，log 日志文件都是追加的，是顺序磁盘 IO，性能高于随机磁盘 IO，这种机制就是 WAL ( Write-Ahead Logging ) 先写日志

  • 是提交事务出了问题后解决问题所用，保证数据能够顺利恢复，从而保证持久性

undo log 日志

• 保证事务的原子性
• undo log：回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和 MVCC ( 多版本并发控制 )
  • MVCC 后面细讲
• undo log 和 redo log 记录物理日志 ( 记录内容怎样的 ) 不一样，它是逻辑日志 ( 记录每次执行什么操作 )
  • 可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录 ( 即执行此语句前长什么样子 )
  • 当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容 ( 即变更之前的样子 ) 并进行回滚 ( 到之前的样子 )
• Undo log 销毁：undo log 在事务执行时产生，事务提交时，并不会立即删除 undo log，因为这些日志可能还用于 MVCC
• Undo log 存储：undo log 采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含 1024 个 undo log segment

MVCC

基本概念

• 当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode ( 共享锁 )，select ... for update、update、insert、delete ( 排他锁 ) 都是一种当前读
  • 例：客户端 1 开启事务查询整张表，客户端 2 开启事务更新查询到的表中的一条数据，由于默认的是隔离级别是 Repeatable Read ( 可重复读 )，所以即使客户端 2 提交了事务，客户端 1 执行查询语句还是查不到更新后的数据，这时查询不是当前读
  • 但若是在客户端 1 执行的查询语句后加上锁 ... lock in share mode; 就是当前读，读到的就是最新的、客户端 2 更改后的数据
• 快照读：简单的 select ( 不加锁 ) 就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。以下各个隔离级别：
  • Read Committed：每次 select，都生成一个快照读
  • Repeatable Read ( 默认 )：开启事务后第一个 select 语句才是快照读的地方 ( 后面非加锁的查询查的其实就是快照时的数据 )
  • Serializable：快照读会退化为当前读 ( 每次都加锁 )
• MVCC：
  • 全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为 MySQL 实现 MVCC 提供了一个非阻塞读功能。MVCC 的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log 日志、readView
  • MVCC + 锁：保证了事务的隔离性

实现原理

记录中的隐藏字段

• 即在创建一个表后，除了自行增加的表的字段，InnoDB 引擎还会自行额外增加两个字段：DB_TRX_ID 和 DB_ROLL_PTR、DB_ROW_ID

  隐藏字段	含义
  DB_TRX_ID	最近修改事务 ID，记录插入这条记录或最后一次修改该记录的事务 ID
  DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合 undo log，指向上一个版本
  DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段

  • 即：前两个隐藏字段都有，第三个字段看表有无主键来定
  • 可在表结构中查看到

undo log 日志

• 回滚日志，在 insert、update、delete 的时候产生的便于数据回滚的日志

  • 当 insert 的时候，产生的 undo log 日志只在回滚时需要，在事务提交后，可被立即删除
  • 而 update、delete 的时候，产生的 undo log 日志不仅在回滚时需要，在快照读时也需要，不会立即被删除

• undo log 版本链

  • DB_TRX_ID 事务 id 是从 1 开始自增的

  • 如图，在起初记录的部分为 DB_TRX_ID 为 1、DB_ROLL_PTR 是 null 的记录并生于 undo log 部分中一个 ( 即为变更前的，且带有地址值 )，事务 2 执行语句修改后，就更换了记录部分，DB_TRX_ID 自增为了 2，DB_ROLL_PTR 记录的就是要回滚的地址 ( 就是 undo log 中要回滚的样子 )：

    

  • 最终为：

    

  • 所以 undo log 版本链指的就是：不同事务或相同事务对同一条记录进行修改，会导致该记录的 undolog 生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录 ( 就是上图的有箭头指向的链表，头部就在记录部分 )

readView

• ReadView ( 读视图 ) 是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务 ( 未提交的 ) id ( 即 select 快照读时产生的 )

  • ReadView 中包含了四个核心字段：

    字段	含义
    m_ids	当前活跃的事务 ID 集合 ( 没提交的 )
    min_trx_id	最小活跃事务 ID ( 当前活跃的事务 ID 集合的 )
    max_trx_id	预分配事务 ID，其值是当前最大事务的 ID + 1 ( 因为事务 ID 是自增的，即值为已分配了的事务 ID 中最大的值加一 )
    creator_trx_id	ReadView 创建者的事务 ID ( select 快照读所在的事务的 id )

• 版本链事务访问规则 ( 在其中出现的 trx_id 就是代表当前事务 ID —— 即上面演示图中各链表的 DB_TRX_ID 字段值 )

  • trx_id == creator_trx_id ? 可以访问该版本
    • 此条件成立，就说明数据是当前的这个事务更改的 ( 即刚刚更改的数据肯定可以读取出来 )
  • trx_id < min_trx_id ? 可以访问该版本
    • 此条件成立，就说明数据已经提交了
  • trx_id > max_trx_id ? 不可以访问该版本
    • 此条件成立，就说明该事务是在 ReadView 生成后才开启
  • min_trx_id <= trx_id <=max_trx_id ? 如果 trx_id 不在 m_ids 中是可以访问该版本的
    • 此条件成立，就说明数据已经提交了

• 不同的隔离级别，生成 ReadView 的时机不同：

  • READ COMMITTED：在事务中每一次执行快照读时生成 ReadView
  • REPEATABLE READ：仅在事务中第一次执行快照读时生成 ReadView，后续复用该 ReadView

• 案例演示：

  • RC 隔离级别下，在事务中每一次执行快照读时生成 ReadView

    

    • 此时看链表并参考规则可知，在左侧链表中 trx_id 为 4、3、2、1 依次比较时，与事务 5 的第一个 ReadView 符合的最近的为 0x00002，所以回滚时就是到 0x00002 数据这里

      

    • 事务 5 的第二个 ReadView 也是同理，第一个查到的符合的为 0x00003，所以回滚时返回的就是 0x00003 这个版本的数据

      

    • 总结来说，就是：返回与当前查询前最近的一次提交的版本

  • RR 隔离级别下，仅在事务中第一次执行快照读时生成 ReadView，后续复用该 ReadView

    

    • 套用规则与上面 RC 的一致

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 2d物理引擎学习 - 斜坡上下滑的物体
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• 2d物理引擎学习 - 基于约束的公式解决接触稳定性问题
• 如何处理Mysql死锁
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset

引擎 InnoDB MySQL引擎innodb mysql 文件 引擎innodb mysql 引擎innodb myisam mysql 架构 引擎innodb mysql 特点 引擎innodb mysql 引擎myisam innodb mysql 引擎 简介innodb mysql 事务 引擎innodb mysql 引擎innodb buffer引擎innodb change