MySQL InnoDB架构
2021-11-04 17:58:58 1 举报AI智能生成
登录查看完整内容
MySQL InnoDB架构根据官网资料整理出来一套完整的资料，请参考
作者其他创作
大纲/内容
缓冲池是主内存中的一个区域，用于在 InnoDB访问时缓存表和索引数据。缓冲池允许直接从内存访问经常使用的数据，从而加快处理速度。在专用服务器上，多达 80% 的物理内存通常分配给缓冲池。
为了提高大量读取操作的效率，缓冲池被划分为可能包含多行的页面。为了缓存管理的效率，缓冲池被实现为页面的链表；很少使用的数据使用最近最少使用 (LRU) 算法的变体从缓存中老化。
简介
缓冲池使用 LRU 算法的变体作为列表进行管理。当需要空间向缓冲池添加新页面时，最近最少使用的页面会被逐出，并将新页面添加到.
在头部，最近访问的新（“年轻”）页面 的子列表
在尾部，最近访问过的旧页面的子列表
列表中间在此中点插入策略将列表视为两个子列表：
该算法将经常使用的页面保留在新的子列表中。旧的子列表包含不太常用的页面；这些页面是驱逐的候选页面。
缓冲池的 3/8 专用于旧子列表。
列表的中点是新子列表尾部与旧子列表头部相交的边界。
当InnoDB将页面读入缓冲池时，它最初将它插入到中点（旧子列表的头部）。可以读取页面，因为它是用户启动的操作（例如 SQL 查询）所必需的，或者是由 自动执行的预读操作的一部分 InnoDB。
访问旧子列表中的页面使其 “年轻”，将其移动到新子列表的头部。如果页面是因为用户启动的操作需要它而被读取，则第一次访问会立即移动，并且页面会变年轻。如果页面是由于预读操作而读取的，则第一次访问不会立即移动，并且在页面被逐出之前可能根本不需要移动。
随着数据库的运行，缓冲池中未被访问的页面会通过向列表尾部移动来“老化”。新旧子列表中的页面随着其他页面的更新而老化。旧子列表中的页面也会随着页面插入中点而老化。最终，一个未使用的页面到达旧子列表的尾部并被驱逐。
默认情况下，算法操作如下：
Buffer Pool LRU 算法
配置 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）
当减小缓冲池的大小时，调整大小操作：
配置 InnoDB 缓冲池大小
将缓冲池的大小设置为尽可能大的值，从而为服务器上的其他进程留出足够的内存来运行而不会产生过多的分页。缓冲池越大，就越InnoDB像内存数据库，从磁盘读取数据一次，然后在后续读取期间从内存访问数据。
使用innodb_buffer_pool_instances 配置选项配置多个缓冲池实例
当多个线程访问buffer pool时，单个缓冲池实例就会限制访问性能，因此，使用多个缓冲池实例可以减少线程之间的争用。
作用：
用Hash函数将存储在随机分配给其中一个缓冲池中或从缓冲池中读取
Buffer Pool初始化的时候每个数据页都是空闲的，随着后续对数据库的增删查改等操作，空闲的页就会被填充或者没有价值的页会被释放。此时Buffer Pool不知道那些数据页是空闲，所以需要Free列表进行管理，需要空闲页只需要从Free列表查找即可。
Free List是双向链表，链表的节点存储是空闲数据页的描述信息块。当需要从磁盘加载数据页到内存时会先从Free列表中找到空闲页，把数据页的表空间号和数据页号写入描述信息块，加载数据页写入空闲页后，该空闲页的描述信息块会从Free列表中移除。
Free List
Flush列表和Free列表一样都是双向链表，只是Flush列表存放着脏页。在Buffer Pool里被修改的数据页称为脏页，需要Flush列进行管理。当需要将脏页刷到磁盘时从Flush列表查找。脏页被刷新到磁盘后描述信息块会从Flush列表移除变成空闲页，添加到Free列表中。
Flush List
LRU列表是用来管理从磁盘读取的数据页，在讲LRU列表之前我们先理解LRU算法（Latest Recent Used）。内存区域的数据页就是通过该算法来管理，通常频繁使用的数据页放在LRU列表头部，最少使用的页放在尾部，当内存区域不能存放新读取页时就会淘汰尾端的数据页。
LRU List
每个缓冲池实例管理自己的空闲列表、刷新列表、LRU 和所有其他连接到缓冲池的数据结构，并由自己的缓冲池互斥锁保护。
为了获得最佳效率，使得每个缓冲池实例是至少为1GB。
配置多个缓冲池实例
在具有足够内存的 64 位系统上，您可以将缓冲池拆分为多个部分，以最大程度地减少并发操作之间对内存结构的争用。
配置参数 innodb_old_blocks_pct控制LRU 列表中“旧”块的百分比。
默认值 innodb_old_blocks_time是 1000。
增加此值会使越来越多的块可能会从缓冲池中更快地老化。
防止缓冲池被预读搅动的优化可以避免由于表或索引扫描引起的类似问题。
innodb_old_blocks_time 指定第一次访问页面后的时间窗口（以毫秒为单位），在此期间它可以被访问而不会被移到 LRU 列表的前面（最近使用的末尾）。
使缓冲池扫描抗性
可以将经常访问的数据保留在内存中，而不管操作的活动突然激增，这些操作会将大量不常访问的数据带入缓冲池。
根据缓冲池中按顺序访问的页面来预测可能很快需要哪些页面
线性预读
根据缓冲池中已有的页面来预测何时可能很快需要页面
随机预读
InnoDB使用两种预读算法来提高 I/O 性能：
将配置变量设置 innodb_random_read_ahead为 ON。
配置 InnoDB 缓冲池预取（预读）
可以控制如何以及何时执行预读请求以异步地将页面预取到缓冲池中，以预期很快就会需要这些页面。
InnoDB在后台执行某些任务，包括从缓冲池中刷新脏页。脏页是那些已被修改但尚未写入磁盘上数据文件的页。
该 变量的默认值为 4。
缓冲池刷新由页面清理线程执行。页面清理线程的数量由innodb_page_cleaners变量控制
默认的低水位标记为 0，这将禁用此早期刷新行为。
当脏页的百分比达到innodb_max_dirty_pages_pct_lwm 变量定义的低水位线值时，将启动缓冲池刷新 。
该 变量的默认值为 75。
innodb_max_dirty_pages_pct_lwm 阈值 的目的 是控制缓冲池中脏页的百分比，并防止脏页数量达到innodb_max_dirty_pages_pct 变量定义的阈值，
配置时 innodb_max_dirty_pages_pct_lwm，该值应始终小于该 innodb_max_dirty_pages_pct 值。
配置缓冲池刷新
可以控制何时发生后台刷新以及是否根据工作负载动态调整刷新速率。
为了减少重新启动服务器后的预热时间，InnoDB在服务器关闭时为每个缓冲池保存最近使用的页面的百分比，并在服务器启动时恢复这些页面。存储的最近使用页面的百分比由innodb_buffer_pool_dump_pct 配置选项定义 。
保存和恢复缓冲池状态
可以配置如何InnoDB保留当前缓冲池状态以避免服务器重新启动后的长时间预热。
Buffer Pool 配置
Buffer Pool
Change Buffer是一种特殊的数据结构，当二级索引页不在缓冲池中时，它会缓存对二级索引页的 更改 。可能由INSERT、 UPDATE或 DELETE操作 (DML)导致的缓冲更改 稍后在其他读取操作将页面加载到缓冲池时合并。
默认值：缓冲区插入、删除标记操作和清除。
all
不要缓冲任何操作。
none
缓冲区插入操作。
inserts
缓冲区删除标记操作。
deletes
缓冲插入和删除标记操作。
changes
在后台发生的缓冲区物理删除操作。
purges
Change Buffer 由innodb_change_buffering变量控制，配置的值：
默认设置为 25。最大设置为 50。
变量允许将更改缓冲区的最大大小配置为缓冲池总大小的百分比。
innodb_change_buffer_max_size
配置更改缓冲区大小
Change Buffer
概要
Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升。
InnoDB会监控对表上各索引页的查询，如果观察该数据被访问的频次符合规则，那么就建立哈希索引来加快数据访问的速度，这个哈希索引称之为\
当它注意到某些索引值被使用的非常频繁时，会在内存中基于B-Tree所有之上再创建一个哈希索引
innodb_adaptive_hash_index 变量控制
仅使用于 =这种逻辑的搜索条件，因为自适应哈希索引是用key value的形式对数据进行存储的。
自适应哈希索引无法对order by进行优化
不支持模糊查询
特点
（1）Hash 索引仅仅能满足\"=\
（2）Hash 索引无法被用来避免数据的排序操作。
（3）Hash 索引不能利用部分索引键查询。
（4）Hash 索引在任何时候都不能避免表扫描。
（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
Hash 索引限制和弊端
Adaptive Hash Index
日志缓冲区是保存要写入磁盘上日志文件的数据的内存区域。
日志缓冲区的内容会定期刷新到磁盘。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。
完全符合 ACID 需要默认设置 1。日志在每次事务提交时写入并刷新到磁盘。
设置为 0 时，提交事务时并不将log buffer写入磁盘，而是等待主线程每秒的刷新。
设置为 2 时，事务提交时将事务日志写入redo log file，但仅写入文件系统的缓存，不进行fsync操作。在这个设置下，当MySQL数据库发生宕机而操作系统不发生宕机，并不会导致事务的丢失。
innodb_flush_log_at_trx_commit 变量控制日志缓冲区的内容如何写入和刷新到磁盘。
允许你集日志冲洗频率 N秒（其中 N是1 ... 2700，为1的默认值）
任何意外的mysqld进程退出都可以擦除长达N数秒的事务
innodb_flush_log_at_timeout 变量控制日志刷新频率。
日志缓冲区大小由innodb_log_buffer_size变量定义 。默认大小为 16MB。
该参数决定着mysql事务日志文件（ib_logfile0）的大小；
innodb_log_file_size
innodb_file_per_table：该参数设置为on时，每张表都建一个ibd文件，否则合用ibdata1
innodb_log_files_in_group：该参数控制日志文件数。默认值为2。mysql 事务日志文件是循环覆写的。
Log Buffer
InnoDB In-Memory Structures
InnoDB默认情况下，表是在 file-per-table 表空间中创建的。
InnoDB表 的行格式决定了其行在磁盘上的物理存储方式。
InnoDB支持四种行格式，每种格式具有不同的存储特性
支持行格式包括 REDUNDANT，COMPACT， DYNAMIC，和COMPRESSED。DYNAMIC行格式是默认的。
行格式
MySQL 将表的数据字典信息存储在数据库目录中的 .frm 文件中。
.frm 文件
创建 InnoDB 表
原因可能包括空间管理、I/O 优化或将表放置在具有特定性能或容量特征的存储设备上。
CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';
在数据目录之外创建表
在外部创建表
在非生产 MySQL 服务器实例上运行报告以避免在生产服务器上放置额外负载。
将数据复制到新的副本服务器。
从备份的表空间文件恢复表。
这是一种比导入转储文件更快的数据移动方式，后者需要重新插入数据和重建索引。
将数据移动到具有更适合您的存储要求的存储介质的服务器。例如，可以将繁忙的表移至 SSD 设备，或将大表移至高容量 HDD 设备。
导入表的原因
导入 InnoDB 表
移动或复制 InnoDB 表
将表从 MyISAM 转换为 InnoDB
“简单的插入”
“ INSERT-like ” 语句
“批量插入”
“混合模式插入”
innodb_autoinc_lock_mode 变量 有三种可能的设置 。设置为 0、1 或 2，分别表示 “传统”、“连续”或 “交错”锁定模式。
InnoDB AUTO_INCREMENT 锁定模式
在复制中使用自动增量
“丢失”自动递增值和序列间隙
为AUTO_INCREMENT列 指定 NULL 或 0
为AUTO_INCREMENT列 分配负值
如果该AUTO_INCREMENT值大于指定整数类型的最大整数
“批量插入”的 自动增量值中的差距
由“混合模式插入”分配的自动递增值
InnoDB AUTO_INCREMENT 锁模式使用含义
InnoDB AUTO_INCREMENT 计数器初始化
InnoDB 中的 AUTO_INCREMENT 处理
Tables
聚集索引和二级索引
InnoDB 索引的物理结构
排序索引构建
InnoDB 全文索引
Indexes
系统表空间(共享表空间)是InnoDB数据字典、双写缓冲区、更改缓冲区和撤消日志的存储区域 。如果表是在系统表空间中创建的，而不是在每个表文件或通用表空间中创建，则它还可能包含表和索引数据。系统表空间（在操作系统上体现就是ibdata文件）
增加系统表空间大小的最简单方法是将其配置为自动扩展
innodb_data_file_path=ibdata1:1G:autoextend
增加系统表空间的大小
调整系统表空间的大小
The System Tablespace
独立表空间（file-per-table tablespaces）默认是开启的（也就是innodb_file_per_table参数不设置时，它默认等于1）
表名.frm # 表的表结构文件(里面存放的是表的创建语句)
独立表空间
File-Per-Table 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]
通用表空间可以在数据目录中或在其外部创建。为避免与隐式创建的每表文件表空间冲突，不支持在数据目录下的子目录中创建常规表空间。在数据目录之外创建常规表空间时，该目录必须在创建表空间之前存在。
创建通用表空间
General Tablespaces
Redo Log TablesSpaces
撤消表空间包含撤消日志，这是包含有关如何撤消事务对聚集索引记录的最新更改的信息的记录集合。
指定单独存放 undo 表空间的目录，默认为.（即 datadir），可以设置相对路径或者绝对路径
innodb_undo_directory
指定单独存放的 undo 表空间个数，例如如果设置为 3，则 undo 表空间为 undo001、undo002、undo003
innodb_undo_tablespaces
undo 表空间文件超过此值即标记为可收缩，默认 1G
innodb_max_undo_log_size
指定回滚段的个数（早期版本该参数名字是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_undo_logs
innodb_max_undo_log_size，undo 表空间文件超过此值即标记为可收缩，默认 1G，可在线修改；
配置撤销表空间：
Undo Tablespaces
命名ibtmp1文件，初始化12M，且默认无上限。
innodb_temp_data_file_path = ibtmp1:12M:autoextend
临时表空间不像普通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长时间运行影响业务。
建议
The Temporary Tablespace
Tablespaces
InnoDB数据字典由包含元数据的用于跟踪对象的如表，索引，和表中的列的内部系统表。数据字典元数据与存储在InnoDB表元数据文件（.frm文件）中的信息有一定程度的重叠
我们创建的表的元信息是放到系统表中的
系统表是什么? 跟自己创建的表有何不同?
data dictionary (简称DD)中的数据结构是完全按照多态、接口/实现的形式来组织的，接口通过纯虚类来实现（比如表示一个表的 Table），其实现类（Table_impl）为接口类的名字加 _impl 后缀。
InnoDB Data Dictionary
A. 如果写临时页时宕机了，物理页还是完全未写之前的状态，可以用重做日志恢复
Doublewrite Buffer
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: 日志文件路径，默认在数据文件路径下。
参数配置：
【0】事务提交时并不写，而是等待主线程每秒刷写一次。
【1】默认值，表示执行事务commit时同步写到磁盘，提供最大的安全性，也是最慢的方式。
【2】异步写磁盘，先写到系统缓存，交给系统写到磁盘。
参数innodb_flush_log_at_trx_commit影响重做日志的刷写动作
redo:ib_logfileN文件
Redo Log
undo log 叫做回滚日志，用于记录数据被修改前的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时 回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。
undo log有两个作用：提供回滚和多个行版本控制(MVCC)。
undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。
rollback segment称为回滚段，每个回滚段中有1024个undo log segment。
undo log默认存放在共享表空间中。
如果开启了 innodb_file_per_table ，将放在每个表的.ibd文件中。
innodb_undo_directory .
innodb_undo_logs 128
innodb_undo_tablespaces 0
undo的相关变量
undo:share tablespace或.ibd
Undo Logs
InnoDB On-Disk Structures
InnoDB Architecture
0 条评论
回复 删除
下一页
