MySQL InnoDB架构

一、InnoDB架构


InnoDB架构 Memory Buffer Pool 用途 缓存InnoDB表、索引以及其他辅助buffer 组成 Data/Index Page Cache Change Buffer 用途 当page不在BF中时,缓存非唯一索引pages的变更(DML),避免从磁盘读取非唯一索引pages所需的大量随机I/O\n当load到BF时,合并缓存中的非唯一索引pages 参数 innodb_change_buffering:优化非唯一辅助索引延迟写入,以便按顺序执行I/O操作 innodb_change_buffer_max_size:设置Change Buffer占用Buffer Pool的百分比 监控 SHOW ENGINE INNODB STATUS\G结果中INSERT BUFFER AND ADAPTIVE HASH INDEX部分 Adaptive Hash Index 用途 InnoDB存储引擎自动根据访问的频率和模式为索引中的热点数据建立哈希索引,提高检索效率 参数 innodb_adaptive_hash_index innodb_adaptive_hash_index_parts 管理 Buffer Pool LRU midpoint insertion strategy:InnoDB收回最近最少使用的页面,并将新页面添加到列表的中间(位置由innodb_old_blocks_pct控制) Buffer Pool Flush List dirty pages(被修改需要写回到磁盘的页) Redo Log Buffer 用途 缓存写入到redo log中的数据 参数 innodb_log_buffer_size:用于写入磁盘上的redo log的缓冲区的字节大小,通常8M-32M innodb_flush_log_at_trx_commit:1写磁盘、2写系统缓存(操作系统挂可能丢数据)、0写logbuffer(mysql挂可能丢数据) innodb_flush_log_at_timeout Doublewrite Buffer 用途 防止表空间中的页损坏后无法恢复 流程 Buffer Pool中的脏数据写入到数据文件前需先写Doublewrite Buffer 参数 innodb_doublewrite innodb_flush_method 监控 innodb_dblwr_pages_written/innodb_dblwr_writes = 64:1 后台线程 Master Thread(1个) 每秒工作内容 刷新dirty page到磁盘 执行change buffer merge 刷redo log buffer到磁盘 checkpoint 目的 定期确认redo log落盘,避免数据丢失,并提高crash recovery效率 什么时候触发 buffer pool脏数据太多,把脏页刷到磁盘,释放内存 redo log快用完了,把脏页刷新到磁盘 redo log切换时 分类 sharp checkpoint:将所有的脏页刷新到磁盘 fuzzy checkpoint:持续将脏页刷新到磁盘 检查dict table cache,判断有无要删除table cache对象 每10秒工作内容 刷新dirty page到磁盘 执行change buffer merge 刷redo log buffer到磁盘 undo purge checkpoint 实例关闭时 工作内容 刷redo log buffer到磁盘 change buffer merge checkpoint innodb_fast_shutdown 0,slow,full purge,change buffer merge 版本升级 主从切换 实例迁移 物理关机/重启 1,默认,fast,skip these operations 2,flushes logs,cold shut,like crashed 优化建议 避免dirty page堆积,适当调小innodb_max_dirty_pages_pct 避免undo堆积,适当调大innodb_purge_batch_size 及时checkpoint,调整innodb_flush_log_at_trx_commit 保持事务持续平稳提交 Flushing Thread(1个, Page Cleaner) 目的:将脏页刷新落地到磁盘 方式 LRU Flushing Adaptive Flushing 工作内容 将脏页拷到double write buffer 将double write buffer写dblwr文件并sync到磁盘 将dirty page写到.ibd数据文件并sync到磁盘 Purge Thread(1个) 目的:做GC(garbage collection) 工作内容 删除辅助索引中不存在的记录 删除已被打了delete-marked标记的记录 删除不再需要的undo log 参数 innodb_purge_threads innodb_purge_bacth_size IO Thread(10个) Read Thread(4个) Write Thread(4个) Insert/Change Buffer Thread(1个) 将非唯一辅助索引上的IUD操作从随机变成顺序I/O,提高I/O效率 Redo Log Thread(1个) Lock Monitor Thread(1个) Error Monitor Thread(1个) 图片 innodb-architecture InnoDB内部结构 逻辑结构 Tablespace 所有的数据都逻辑的存放在一个空间中,称为表空间。表空间由段(segment)、区(extent)、页(page) 组成。\n默认一个页16KB为数据库的最小管理单元,操作系统的最小管理单元为512k Segment 数据段 索引段 Extent 区是连续的页组成,每个区的大小始终为1MB,一个区中包含64连续的数据页 page size<=16KB,区大小是1MB;page size=32KB,区大小是2MB;page size=64KB,区大小是4MB 在表创建之初,先分配32个页,针对小表可以节省空间,使用完这32个页之后,才会按每次分配1个区, Page 页类型 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE表中的PAGE_TYPE innodb_page_size:默认16KB,仅在初始化实例时能指定页面大小 Row 最大行长度略小于数据库页面的一半 行溢出 COMPACT and REDUNDANT Row Formats DYNAMIC and COMPRESSED Row Formats Disk Tablespaces System Tablespace(ibdata1) 存储对象 Data Dict:InnoDB相关的元数据;8.0元数据存储在MySQL Data Dictionary Change Buffer:purge operation周期性将辅助索引的变更刷新到磁盘 Doublewrite Buffer:脏页在刷新时首先顺序写到Doublewrite buffer,再写回数据文件 Rollback Segments:指向Undo的索引,Undo用于存储旧版本的数据 参数 innodb_data_file_path File-Per-Table Tablespace(.ibd) 用途 用户表独立于System Tablespace,Buffer Pool中的脏数据定期flush到数据文件.ibd 参数 innodb_file_per_table 修改innodb_file_per_table只会影响之后创建的表,原先使用共享表空间的表,需通过alter table xxx engine=innodb转换 表空间管理 消除碎片 alter table xx engine=innodb; 尽量使用pt-osc操作 回收空间 独立:alter table xx engine=innodb;共享:导出、导入 尽量使用pt-osc操作 表空间传输 主要用于单表备份恢复 Undo Tablespaces 用途 Undo Log独立于System Tablespace,需在数据库初始化处理;主要存储事务过程产生的旧版本数据,Undo Log定期Purge 参数 innodb_undo_directory:undo文件存放目录,默认数据文件目录 innodb_undo_logs/innodb_rollback_segments:回滚段的个数(默认128个),存储undo slot指向undo space innodb_undo_tablespaces:undo表空间个数,每个文件默认10M Temporary Tablespace(ibtmp1) 用途 保存通用临时表和SQL执行过程中产生的内部临时表,不记录redo log 参数 innodb_temp_data_file_path General Tablespace 多个表放在同一个表空间中 可以定义多个通用表空间,并且分别放在不同的磁盘上 可以减少metadata的存储开销 Redo Log(ib_logfile*) 用途 crash recovery,逻辑物理日志;Redo Log Buffer定期flush到Redo Log 参数 innodb_log_group_home_dir:默认datadir,可以自行指定一个路径 innodb_log_file_size:单个redo log大小,通常1G-4G innodb_log_files_in_group:redo log个数,通常3-5个 监控 建议将ib_logfile*文件总大小设置满足一个小时redo空间 Undo Logs 存储于 Undo Tablespaces Temporary Tablespace 分类 insert_undo:insert操作,只用于回滚 update_undo:update/delete操作,用于一致性读+回滚

View Code

MySQL 8.0 InnoDB Architecture
MySQL InnoDB架构
Percona XtraDB Internals
MySQL InnoDB架构

相关文章