首页  思维导图  详情

MySQL InnoDB架构

2021-11-04 17:58:58   1  举报





AI智能生成

MySQL InnoDB架构根据官网资料整理出来一套完整的资料，请参考

MySQL

作者其他创作

大纲/内容

InnoDB In-Memory Structures

Buffer Pool

简介

缓冲池是主内存中的一个区域，用于在 InnoDB访问时缓存表和索引数据。缓冲池允许直接从内存访问经常使用的数据，从而加快处理速度。在专用服务器上，多达 80% 的物理内存通常分配给缓冲池。

为了提高大量读取操作的效率，缓冲池被划分为可能包含多行的页面。为了缓存管理的效率，缓冲池被实现为页面的链表；很少使用的数据使用最近最少使用 (LRU) 算法的变体从缓存中老化。

Buffer Pool LRU 算法

缓冲池使用 LRU 算法的变体作为列表进行管理。当需要空间向缓冲池添加新页面时，最近最少使用的页面会被逐出，并将新页面添加到.

列表中间在此中点插入策略将列表视为两个子列表：

在头部，最近访问的新（“年轻”）页面的子列表

在尾部，最近访问过的旧页面的子列表

该算法将经常使用的页面保留在新的子列表中。旧的子列表包含不太常用的页面；这些页面是驱逐的候选页面。

默认情况下，算法操作如下：

缓冲池的 3/8 专用于旧子列表。

列表的中点是新子列表尾部与旧子列表头部相交的边界。

当InnoDB将页面读入缓冲池时，它最初将它插入到中点（旧子列表的头部）。可以读取页面，因为它是用户启动的操作（例如 SQL 查询）所必需的，或者是由自动执行的预读操作的一部分 InnoDB。

访问旧子列表中的页面使其 “年轻”，将其移动到新子列表的头部。如果页面是因为用户启动的操作需要它而被读取，则第一次访问会立即移动，并且页面会变年轻。如果页面是由于预读操作而读取的，则第一次访问不会立即移动，并且在页面被逐出之前可能根本不需要移动。

随着数据库的运行，缓冲池中未被访问的页面会通过向列表尾部移动来“老化”。新旧子列表中的页面随着其他页面的更新而老化。旧子列表中的页面也会随着页面插入中点而老化。最终，一个未使用的页面到达旧子列表的尾部并被驱逐。

Buffer Pool 配置

将缓冲池的大小设置为尽可能大的值，从而为服务器上的其他进程留出足够的内存来运行而不会产生过多的分页。缓冲池越大，就越InnoDB像内存数据库，从磁盘读取数据一次，然后在后续读取期间从内存访问数据。

配置 InnoDB 缓冲池大小

配置 InnoDB 缓冲池块大小：innodb_buffer_pool_chunk_size

缓冲池大小必须始终等于或倍数 innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances

当增加缓冲池的大小时，调整大小操作：

添加页面chunks（块大小由定义 innodb_buffer_pool_chunk_size）

转换哈希表、列表和指针以使用内存中的新地址

将新页面添加到空闲列表

当这些操作正在进行时，其他线程被阻止访问缓冲池。

当减小缓冲池的大小时，调整大小操作：

对缓冲池进行碎片整理并撤回（释放）页面

删除页面chunks（块大小由定义 innodb_buffer_pool_chunk_size）

转换哈希表、列表和指针以使用内存中的新地址

在具有足够内存的 64 位系统上，您可以将缓冲池拆分为多个部分，以最大程度地减少并发操作之间对内存结构的争用。

配置多个缓冲池实例

使用innodb_buffer_pool_instances 配置选项配置多个缓冲池实例

作用：

当多个线程访问buffer pool时，单个缓冲池实例就会限制访问性能，因此，使用多个缓冲池实例可以减少线程之间的争用。

用Hash函数将存储在随机分配给其中一个缓冲池中或从缓冲池中读取

每个缓冲池实例管理自己的空闲列表、刷新列表、LRU 和所有其他连接到缓冲池的数据结构，并由自己的缓冲池互斥锁保护。

Free List

Buffer Pool初始化的时候每个数据页都是空闲的，随着后续对数据库的增删查改等操作，空闲的页就会被填充或者没有价值的页会被释放。此时Buffer Pool不知道那些数据页是空闲，所以需要Free列表进行管理，需要空闲页只需要从Free列表查找即可。

Free List是双向链表，链表的节点存储是空闲数据页的描述信息块。当需要从磁盘加载数据页到内存时会先从Free列表中找到空闲页，把数据页的表空间号和数据页号写入描述信息块，加载数据页写入空闲页后，该空闲页的描述信息块会从Free列表中移除。

Flush List

Flush列表和Free列表一样都是双向链表，只是Flush列表存放着脏页。在Buffer Pool里被修改的数据页称为脏页，需要Flush列进行管理。当需要将脏页刷到磁盘时从Flush列表查找。脏页被刷新到磁盘后描述信息块会从Flush列表移除变成空闲页，添加到Free列表中。

LRU List

LRU列表是用来管理从磁盘读取的数据页，在讲LRU列表之前我们先理解LRU算法（Latest Recent Used）。内存区域的数据页就是通过该算法来管理，通常频繁使用的数据页放在LRU列表头部，最少使用的页放在尾部，当内存区域不能存放新读取页时就会淘汰尾端的数据页。

为了获得最佳效率，使得每个缓冲池实例是至少为1GB。

可以将经常访问的数据保留在内存中，而不管操作的活动突然激增，这些操作会将大量不常访问的数据带入缓冲池。

使缓冲池扫描抗性

配置参数 innodb_old_blocks_pct控制LRU 列表中“旧”块的百分比。

默认值 innodb_old_blocks_pct是 37，对应于3/8原固定比率,则new为5/8

innodb_old_blocks_time 指定第一次访问页面后的时间窗口（以毫秒为单位），在此期间它可以被访问而不会被移到 LRU 列表的前面（最近使用的末尾）。

默认值 innodb_old_blocks_time是 1000。

增加此值会使越来越多的块可能会从缓冲池中更快地老化。

防止缓冲池被预读搅动的优化可以避免由于表或索引扫描引起的类似问题。

可以控制如何以及何时执行预读请求以异步地将页面预取到缓冲池中，以预期很快就会需要这些页面。

配置 InnoDB 缓冲池预取（预读）

InnoDB使用两种预读算法来提高 I/O 性能：

线性预读

根据缓冲池中按顺序访问的页面来预测可能很快需要哪些页面

随机预读

根据缓冲池中已有的页面来预测何时可能很快需要页面

将配置变量设置 innodb_random_read_ahead为 ON。

可以控制何时发生后台刷新以及是否根据工作负载动态调整刷新速率。

配置缓冲池刷新

InnoDB在后台执行某些任务，包括从缓冲池中刷新脏页。脏页是那些已被修改但尚未写入磁盘上数据文件的页。

缓冲池刷新由页面清理线程执行。页面清理线程的数量由innodb_page_cleaners变量控制

该变量的默认值为 4。

当脏页的百分比达到innodb_max_dirty_pages_pct_lwm 变量定义的低水位线值时，将启动缓冲池刷新。

默认的低水位标记为 0，这将禁用此早期刷新行为。

innodb_max_dirty_pages_pct_lwm 阈值的目的是控制缓冲池中脏页的百分比，并防止脏页数量达到innodb_max_dirty_pages_pct 变量定义的阈值，

该变量的默认值为 75。

配置时 innodb_max_dirty_pages_pct_lwm，该值应始终小于该 innodb_max_dirty_pages_pct 值。

可以配置如何InnoDB保留当前缓冲池状态以避免服务器重新启动后的长时间预热。

保存和恢复缓冲池状态

为了减少重新启动服务器后的预热时间，InnoDB在服务器关闭时为每个缓冲池保存最近使用的页面的百分比，并在服务器启动时恢复这些页面。存储的最近使用页面的百分比由innodb_buffer_pool_dump_pct 配置选项定义。

Change Buffer

Change Buffer是一种特殊的数据结构，当二级索引页不在缓冲池中时，它会缓存对二级索引页的更改。可能由INSERT、 UPDATE或 DELETE操作 (DML)导致的缓冲更改稍后在其他读取操作将页面加载到缓冲池时合并。

Change Buffer 由innodb_change_buffering变量控制，配置的值：

all

默认值：缓冲区插入、删除标记操作和清除。

none

不要缓冲任何操作。

inserts

缓冲区插入操作。

deletes

缓冲区删除标记操作。

changes

缓冲插入和删除标记操作。

purges

在后台发生的缓冲区物理删除操作。

配置更改缓冲区大小

innodb_change_buffer_max_size

默认设置为 25。最大设置为 50。

变量允许将更改缓冲区的最大大小配置为缓冲池总大小的百分比。

Adaptive Hash Index

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升。

InnoDB会监控对表上各索引页的查询，如果观察该数据被访问的频次符合规则，那么就建立哈希索引来加快数据访问的速度，这个哈希索引称之为"Adaptive Hash Index,AHI",AHI是通过缓冲池的B+树页构建的，建立的速度很快，而且不对整颗树都建立哈希索引。(可以理解成热点的数据才会进入这个哈希表)

当它注意到某些索引值被使用的非常频繁时，会在内存中基于B-Tree所有之上再创建一个哈希索引

innodb_adaptive_hash_index 变量控制

特点

仅使用于 =这种逻辑的搜索条件，因为自适应哈希索引是用key value的形式对数据进行存储的。

自适应哈希索引无法对order by进行优化

不支持模糊查询

Hash 索引限制和弊端

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

（2）Hash 索引无法被用来避免数据的排序操作。

（3）Hash 索引不能利用部分索引键查询。

（4）Hash 索引在任何时候都不能避免表扫描。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

Log Buffer

日志缓冲区是保存要写入磁盘上日志文件的数据的内存区域。

日志缓冲区的内容会定期刷新到磁盘。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。

innodb_flush_log_at_trx_commit 变量控制日志缓冲区的内容如何写入和刷新到磁盘。

完全符合 ACID 需要默认设置 1。日志在每次事务提交时写入并刷新到磁盘。

设置为 0 时，提交事务时并不将log buffer写入磁盘，而是等待主线程每秒的刷新。

设置为 2 时，事务提交时将事务日志写入redo log file，但仅写入文件系统的缓存，不进行fsync操作。在这个设置下，当MySQL数据库发生宕机而操作系统不发生宕机，并不会导致事务的丢失。

innodb_flush_log_at_timeout 变量控制日志刷新频率。

允许你集日志冲洗频率 N秒（其中 N是1 ... 2700，为1的默认值）

任何意外的mysqld进程退出都可以擦除长达N数秒的事务

日志缓冲区大小由innodb_log_buffer_size变量定义。默认大小为 16MB。

innodb_log_file_size

该参数决定着mysql事务日志文件（ib_logfile0）的大小；

innodb_file_per_table：该参数设置为on时，每张表都建一个ibd文件，否则合用ibdata1

innodb_log_files_in_group：该参数控制日志文件数。默认值为2。mysql 事务日志文件是循环覆写的。

InnoDB On-Disk Structures

Tables

创建 InnoDB 表

InnoDB默认情况下，表是在 file-per-table 表空间中创建的。

行格式

InnoDB表的行格式决定了其行在磁盘上的物理存储方式。

InnoDB支持四种行格式，每种格式具有不同的存储特性

支持行格式包括 REDUNDANT，COMPACT， DYNAMIC，和COMPRESSED。DYNAMIC行格式是默认的。

.frm 文件

MySQL 将表的数据字典信息存储在数据库目录中的 .frm 文件中。

在外部创建表

在数据目录之外创建表

原因可能包括空间管理、I/O 优化或将表放置在具有特定性能或容量特征的存储设备上。

CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';

导入 InnoDB 表

导入表的原因

在非生产 MySQL 服务器实例上运行报告以避免在生产服务器上放置额外负载。

将数据复制到新的副本服务器。

从备份的表空间文件恢复表。

这是一种比导入转储文件更快的数据移动方式，后者需要重新插入数据和重建索引。

将数据移动到具有更适合您的存储要求的存储介质的服务器。例如，可以将繁忙的表移至 SSD 设备，或将大表移至高容量 HDD 设备。

移动或复制 InnoDB 表

将表从 MyISAM 转换为 InnoDB

InnoDB 中的 AUTO_INCREMENT 处理

InnoDB AUTO_INCREMENT 锁定模式

“简单的插入”

“ INSERT-like ” 语句

“批量插入”

“混合模式插入”

innodb_autoinc_lock_mode 变量有三种可能的设置。设置为 0、1 或 2，分别表示 “传统”、“连续”或 “交错”锁定模式。

InnoDB AUTO_INCREMENT 锁模式使用含义

在复制中使用自动增量

“丢失”自动递增值和序列间隙

为AUTO_INCREMENT列指定 NULL 或 0

为AUTO_INCREMENT列分配负值

如果该AUTO_INCREMENT值大于指定整数类型的最大整数

“批量插入”的自动增量值中的差距

由“混合模式插入”分配的自动递增值

InnoDB AUTO_INCREMENT 计数器初始化

Indexes

聚集索引和二级索引

InnoDB 索引的物理结构

排序索引构建

InnoDB 全文索引

Tablespaces

The System Tablespace

系统表空间(共享表空间)是InnoDB数据字典、双写缓冲区、更改缓冲区和撤消日志的存储区域。如果表是在系统表空间中创建的，而不是在每个表文件或通用表空间中创建，则它还可能包含表和索引数据。系统表空间（在操作系统上体现就是ibdata文件）

调整系统表空间的大小

增加系统表空间的大小

增加系统表空间大小的最简单方法是将其配置为自动扩展

innodb_data_file_path=ibdata1:1G:autoextend

File-Per-Table Tablespaces

独立表空间（file-per-table tablespaces）默认是开启的（也就是innodb_file_per_table参数不设置时，它默认等于1）

独立表空间

表名.frm # 表的表结构文件(里面存放的是表的创建语句)

表名.ibd # 表的数据文件(当有数据往表中插入时,数据就保存之个文件中的)

General Tablespaces

General tablespace 是一种共享的 innodb 表空间，有点类似 ibdata1 。可以在一个表空间数据文件下存储多张表，即使这些表来自不同的 schame 。

常规表空间功能提供以下功能：

类似于系统表空间，常规表空间是共享表空间，可以存储多个表的数据。

常规表空间比每表文件表空间具有潜在的内存优势。服务器在表空间的生存期内将表空间元数据保留在内存中。与单独的每表文件表空间中的相同数量的表相比，较少的常规表空间中的多个表为表空间元数据消耗的内存更少。

常规表空间数据文件可以放置在相对于MySQL数据目录或独立于MySQL数据目录的目录中，该目录为您提供了许多数据文件和每表文件表空间的存储管理功能。与每表文件表空间一样，将数据文件放置在MySQL数据目录之外的功能使您可以分别管理关键表的性能，为特定表设置RAID或DRBD或将表绑定到特定磁盘。

常规表空间支持Antelope和Barracuda文件格式，因此支持所有表行格式和相关功能。支持两种文件格式，通用表空间不依赖 innodb_file_format或 innodb_file_per_table 设置，这些变量也不影响通用表空间。

该TABLESPACE选项可用于 CREATE TABLE在常规表空间，每表文件表空间或系统表空间中创建表。

该TABLESPACE选项可用于 ALTER TABLE在常规表空间，每表文件表空间和系统表空间之间移动表。以前，不可能将表从每个表文件表空间移动到系统表空间。

创建通用表空间

CREATE TABLESPACE tablespace_name ADD DATAFILE 'file_name' [FILE_BLOCK_SIZE = value] [ENGINE [=] engine_name]

通用表空间可以在数据目录中或在其外部创建。为避免与隐式创建的每表文件表空间冲突，不支持在数据目录下的子目录中创建常规表空间。在数据目录之外创建常规表空间时，该目录必须在创建表空间之前存在。

Redo Log TablesSpaces

Undo Tablespaces

撤消表空间包含撤消日志，这是包含有关如何撤消事务对聚集索引记录的最新更改的信息的记录集合。

配置撤销表空间：

innodb_undo_directory

指定单独存放 undo 表空间的目录，默认为.（即 datadir），可以设置相对路径或者绝对路径

innodb_undo_tablespaces

指定单独存放的 undo 表空间个数，例如如果设置为 3，则 undo 表空间为 undo001、undo002、undo003

innodb_max_undo_log_size

undo 表空间文件超过此值即标记为可收缩，默认 1G

innodb_undo_logs

指定回滚段的个数（早期版本该参数名字是innodb_rollback_segments），默认 128 个。每个回滚段可同时支持 1024 个在线事务。这些回滚段会平均分布到各个 undo 表空间中

innodb_undo_logs>=35（默认 128）。因为在 MySQL 5.7 中，第一个 undo log 永远在系统表空间中，另外 32 个 undo log 分配给了临时表空间，即 ibtmp1，至少还有 2 个undo log 才能保证 2 个 undo 表空间中每个里面至少有 1 个 undo log；

innodb_max_undo_log_size，undo 表空间文件超过此值即标记为可收缩，默认 1G，可在线修改；

The Temporary Tablespace

innodb_temp_data_file_path = ibtmp1:12M:autoextend

命名ibtmp1文件，初始化12M，且默认无上限。

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G, 临时表空间设置最大值，避免无限增大

说明

临时表空间不像普通InnoDB表空间那样，不支持裸设备（raw device）。

临时表空间使用动态的表空间ID，因此每次重启时都会变化（每次重启时，都会重新初始化临时表空间文件）。

当选项设置错误或其他原因（权限不足等原因）无法创建临时表空间时，mysqld实例也无法启动。

临时表空间中存储这非压缩的InnoDB临时表，如果是压缩的InnoDB临时表，则需要单独存储在各自的表空间文件中，文件存放在 tmpdir（/tmp）目录下。

临时表元数据存储在 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 视图中。

建议

设置 innodb_temp_data_file_path 选项，设定文件最大上限，超过上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）。

检查 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO，找到最大的临时表对应的线程，kill之即可释放，但 ibtmp1 文件则不能释放（除非重启）。

择机重启实例，释放 ibtmp1 文件，和 ibdata1 不同，ibtmp1 重启时会被重新初始化而 ibdata1 则不可以。

定期检查运行时长超过N秒（比如N=300）的SQL，考虑干掉，避免垃圾SQL长时间运行影响业务。

InnoDB Data Dictionary

InnoDB数据字典由包含元数据的用于跟踪对象的如表，索引，和表中的列的内部系统表。数据字典元数据与存储在InnoDB表元数据文件（.frm文件）中的信息有一定程度的重叠

Data Dictironary(DD, 数据字典)是有关数据库对象的合集, 例如表、视图、索引等, 可以看做是数据库的元信息。换句话说, 数据字典存储了有关表结构的信息, 每个表具有的列, 表的索引等。

系统表是什么? 跟自己创建的表有何不同?

系统表有很多, 常见的有mysql.schemata,mysql.tables, mysql.indexes

我们创建的表的元信息是放到系统表中的

在内存中, 这些元信息以对象的方式提供给外部使用, 比如说, 创建一个表, 内存中会创建这个表的数据字典对象, 系统表以及我们创建的表都会有自己的数据字典对象

可以认为系统表的元信息就存储在自己的数据字典对象中, 这些信息会被序列化到磁盘的mysql.ibd文件中

data dictionary (简称DD)中的数据结构是完全按照多态、接口/实现的形式来组织的，接口通过纯虚类来实现（比如表示一个表的 Table），其实现类（Table_impl）为接口类的名字加 _impl 后缀。

Doublewrite Buffer

Doubel write保证了页的可靠性，Redo log是记录对页(16K)的物理操作，若innodb将页写回表时写了一部分(如4K)出现宕机，则物理页将会损坏无法通过redolog恢复。所以在apply重做日志前，将缓冲池中的脏页通过memcpy到doublewrite buffer中，再将doublewrite buffer页分两次每次1MB刷入共享表空间的磁盘文件中（磁盘连续，开销较小），完成doublewrite buffer的页写入后再写入各个表空间的表中, 当写入页时发生系统崩溃，恢复过程中，innodb从共享表空间的doublewrite找到该页的副本，并将其恢复到表空间文件中，再apply重做日志。

A. 如果写临时页时宕机了，物理页还是完全未写之前的状态，可以用重做日志恢复

B. 如果写物理页时宕机了，则可以使用临时页来恢复物理页,每次写物理页时，先写到double write buffer中，然后从double write buffer写到double write上去。最后再从double write buffer写到物理页上去。

Redo Log

redo log叫做重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程将缓存池刷新到磁盘。

当在执行刷新时，宕机或者断电，可能会丢失部分数据。所以引入了redo log来记录已成功提交事务的修改信息，并且在事务提交时会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。

参数配置：

innodb_log_file_size : 每个redo log文件大小。innodb_log_files_in_group : 文件组中的文件数量，默认为2.

innodb_log_files_in_group : 文件组中的文件数量，默认为2.

innodb_log_group_home_dir: 日志文件路径，默认在数据文件路径下。

参数innodb_flush_log_at_trx_commit影响重做日志的刷写动作

【0】事务提交时并不写，而是等待主线程每秒刷写一次。