MySQL学习笔记二——InnoDB存储引擎

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL学习笔记二——InnoDB存储引擎，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含16423字，纯文字阅读大概需要24分钟。

内容图文

一、innodb简介

• 遵循ACID、支持事务
• 支持MVCC、一致性读
• 支持行锁
• 按照主键聚簇的索引组织表
• 支持外键
• 自动故障恢复
• 死锁自动检测
• 拥有自己独立的缓冲池(对应innodb_buffer_pool_size，类似sga_target)
• 拥有change buffering，减少磁盘I/O
• 5.5之后默认是innodb

二、Innodb实例

实例就是线程跟内存的结合

内存分为SGA跟PGA

2.1 SGA(系统全局区)

• innodb_buffer_pool：缓存InnoDB表的数据、索引、插入缓存、数据字典等数据
• innodb_log_buffer：事务在内存中的缓冲，redo log buffer的大小
• innodb_additional_mem_pool_size：保存数据字典信息和其他内部数据结构的内存池的大小

2.1.1 Buffer状态及其链表结构

buffer分为以下3种

• free buffer：从未被使用的内存
• clean buffer：内存中的buffer跟磁盘中的page数据一致
• dirty buffer：内存中的buffer跟磁盘中的page数据不一致

内存中的buffer是通过链表来管理的，共有3条链表

• free list：把 free buffer 都串联起来。每次把page调用到内存中时，会先判断 free buffer 的使用情况，如果不够用了，就会从 lru list 和 flush list 链表中释放 free buffer。
• lru list：把 clean/dirty buffer 都串联起来。并且按照最近最少使用的buffer排序
• flush list：当页第一次被修改的时候，就将该页的 page number 放入 flush List(只要修改过，就放入，不管该页被修改过多少次) 。把按照最近最少使用的 buffer 串联起来，方便刷新到磁盘。推进checkpoint lsn，加快实例奔溃恢复。

2.1.2 LRU List的管理

LRU list 分为 young sublist 和 old sublist ，数据从磁盘读入时，会将该缓存块插入到 LRU list 的 3/8 的位置，由参数 innodb_old_blocks_pct 控制，5/8 是热点块，3/8 是冷块。引入这个参数的作用是，当一个块从磁盘中调用到内存，如果把他放到顶端，那么这个块只被访问一次就不需要了，那么就会挤掉其他的热点块。如果放在尾端，那么如果这个块被经常访问，突然就被挤掉了。

虽然引入了 innodb_old_blocks_pct 优化了传统的 LRU 的弊端，但是还是会有一个问题，假如一个全表扫描，例如 mysqldump，那么会把内存中的很多热点块都冲刷掉。为了解决这个问题，就引入了 innodb_old_blocks_time(ms)，至少要在 old sublist 停留超过 1s 后，如果该块继续被访问，则会从 old sublist 转移到 young sublist，young sublist是活跃的热点数据。较少被访问的块逐渐向尾部移动，优先从尾部淘汰。

当 LRU list 小于 innodb_lru_scan_depth(默认1024) 个 clean page 用时，会从 LRU list 尾部扫描 1024 个块，如果这 1024 个块中没有 dirty buffer，那么一个页也没有刷新。如果有 1 个，则刷新 1 个。当 buffer pool 中脏页的比例达到 innodb_max_dirty_pages_pct 时，会将 innodb_io_capactity 个脏块刷新到磁盘。当脏块小于 innodb_max_dirty_pages_pct 时，如果 innodb_adaptive_flushing 设置为 true，InnoDB 根据函数 buf_flush_get_desired_flush_rate返回的redo速度确定刷新的脏块数

--计算 InnoDB 的命中率
1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100

2.1.3 查看buffer pool的状态

show engine innodb status\G 配合pager more
mysql> show engine innodb status\G

---BUFFER POOL 0
Buffer pool size 16383 -- 该Buffer Pool中有多少个页
Free buffers 16357 -- 该Buffer Pool中有多少个空白页（Free List），线上可能看到为0
Database pages 41 -- 该Buffer Pool中使用了多少页（LRU List）
Old database pages 0 -- old pages（见3.4）
Modified db pages 0 -- 脏页
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s -- young表示old-->new的状态
Pages read 41, created 0, written 20
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 41, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

select * from information_schema.INNODB_BUFFER_POOL_STATS\G
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 16383 -- 该Buffer Pool中有多少个页
FREE_BUFFERS: 16357 -- 该Buffer Pool中有多少个空白页（Free List），线上可能看到为0
DATABASE_PAGES: 41 -- 该Buffer Pool中使用了多少页（LRU List）
OLD_DATABASE_PAGES: 0 -- old pages
MODIFIED_DATABASE_PAGES: 0 -- 脏页
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 0
PAGES_NOT_MADE_YOUNG: 0
PAGES_MADE_YOUNG_RATE: 0
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 41
NUMBER_PAGES_CREATED: 0
NUMBER_PAGES_WRITTEN: 20
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 1041
HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 0
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 0
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0

select * from information_schema.INNODB_BUFFER_PAGE_LRU limit 1\G
*************************** 1. row ***************************
POOL_ID: 0
LRU_POSITION: 0
SPACE: 0 -- space id 表空间号
PAGE_NUMBER: 7 -- 对应的页号
PAGE_TYPE: SYSTEM
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: NO
NEWEST_MODIFICATION: 4005630175 -- 该页最近一次（最新）被修改的LSN值
OLDEST_MODIFICATION: 0 -- 该页在Buffer Pool中第一次被修改的LSN值，FLushList是根据该值进行排序的
-- 该值越小，表示该页应该最先被刷新
ACCESS_TIME: 729305074
TABLE_NAME: NULL
INDEX_NAME: NULL
NUMBER_RECORDS: 0
DATA_SIZE: 0
COMPRESSED_SIZE: 0
COMPRESSED: NO
IO_FIX: IO_NONE
IS_OLD: NO
FREE_PAGE_CLOCK: 0

2.1.4 启动预热

5.6之后

innodb_buffer_pool_dump_at_shutdown：在停机时 dump 出 buffer pool 中的(space,page)

innodb_buffer_pool_dump_now ：表示 dump 出当前的 buffer pool

innodb_buffer_pool_dump_pct ：dump 的比例，改比例是每个 instance 的比例

innodb_buffer_pool_filename ：dump 出的文件名字

innodb_buffer_pool_load_at_startup ：启动的时候加载 dump 的文件

innodb_buffer_pool_load_now ：加载 dump 文件到当前的 buffer pool

2.2 PGA(程序缓冲区)

• sort_buffer_size：用于排序的内存区域
• join_buffer_size：表连接使用，用于BKA
• read_buffer_size：表顺序扫描的内存区，只能用于MYISAM表
• read_rnd_buffer_size：MySQL随机读内存区，用作mrr

2.3 Innodb 线程

MySQL是单进程多线程的模式，分别有

• master thread
• read/write thread
• redo log thread
• change buffer thread
• purge thread

2.3.1 master thread

• 产生 checkpoint
• 合并 change buffer，如果前1s 的 IO 小于 innodb_io_capacity 的 5%，则执行此操作
• 如果当前没有用户活动，切换到backgroup loop
• 刷新 redo log buffer到磁盘，不管有没有 commit
• 判断脏页的比例 buf_get_modified_ratio_pct 是否大于 innodb_max_dirty_pages_pct，如果超过了，则刷新 innodb_io_capacity 个脏页。如果开启了 adaptive flush ，即使 buf_get_modified_ratio_pct 没有超过 innodb_max_dirty_pages_pct，也会执行刷新脏页。

每10s操作：

• 产生 checkpoint
• 清理无用 undo pages，最多20个 undo 页
• 合并 5% innodb_io_capacity 个 change buffer
• 如果前10秒的 IO 小于 innodb_io_capacity ，则刷新 innodb_io_capacity 个脏页
• 如果脏页的比例 buf_get_modifed_ratio_pct 大于70%，则刷新 innodb_io_capacity 个脏页，否则刷新 10% innodb_io_capacity 个脏页

后台循环

• 产生 checkpoint
• 合并 innodb_io_capacity 个 change buffer
• 删除无用的 undo pages
• 跳回到主循环

2.3.2 read/write thread

数据库的读写进程，默认是4个，由以下参数控制

show variables like '%io_threads';

| Variable_name | Value |
+-------------------------+-------+
| innodb_read_io_threads | 4 |
| innodb_write_io_threads | 4 |
+-------------------------+-------+

2.3.3 redo log thread

每秒 刷新 redo log buffer到磁盘，不管有没有 commit

2.3.4 page cleaner thread

刷新 dirty buffer到磁盘

每1s操作：

• 判断脏页的比例 buf_get_modified_ratio_pct 是否大于 innodb_max_dirty_pages_pct，如果超过了，则刷新innodb_io_capacity 个脏页。如果开启了 adaptive flush ，即使 buf_get_modified_ratio_pct 没有超过 innodb_max_dirty_pages_pct，也会执行刷新脏页。

每10s操作：

• 如果前10秒的 IO 小于 innodb_io_capacity ，则刷新 innodb_io_capacity 个脏页
• 如果脏页的比例 buf_get_modifed_ratio_pct 大于70%，则刷新 innodb_io_capacity 个脏页，否则刷新 10% innodb_io_capacity 个脏页

5.7 之后可以通过参数 innodb_page_cleaners 控制 page cleaner thread 的个数

2.3.5 purge thread

每10s操作：

• 清理无用 undo pages，最多20个 undo 页

后台循环

• 删除无用的 undo pages

清理无用的undo buffer，可以通过参数 innodb_purge_thread 控制 purge thread 的个数

2.3.6 脏页的刷新条件

• redo log切换的时候，触发checkpoint，触发脏页的刷新
• innodb_max_dirty_pages_pct 参数控制 buffer pool 中 dirty page 所占的百分比，达到设置的值，就会出发脏页的刷新。建议25%~50%，默认是75%
• innodb_adaptive_flushing参数，通过 buf_flush_get_desired_flush_reate 函数判断 redo log的产生速度，来确定需要刷新相爷的合适数量，代替了 innodb_max_dirty_pages_pct 参数

2.4 CheckPoint

2.4.1 CheckPoint的作用

• 缩短数据库恢复时间
• 缓冲池不够用时，将脏页刷新到磁盘
• 重做日志不够用时，刷新脏页

2.4.2 LSN

show engine innodb status\G
---
LOG
---
Log sequence number 182867984924 --上次数据页的修改，还没有刷新到日志文件的 lsn号
Log flushed up to 182867984924 --上次成功操作，已经刷新到日志文件中的 lsn 号
Pages flushed up to 182862211669 --最后一个被刷新到磁盘的PAGE的最新LSN (对应 NETEST_MODIFICATION)，recover的时候从这个 LSN 开始，到 Log flushed up结束
Last checkpoint at 182862211669 --最后一个被刷新到磁盘的PAGE第一次被修改的LSN (OLDEST_MODIFICATION)

Log sequence number 和Log flushed up 这两个LSN可能会不同，运行过程中后者可能会小于前者，因为redo日志也是先在内存中更新，再刷到磁盘的。

Pages flushed up 与 Last checkpoint 其实都指向了最后一个刷新到磁盘的页，只是Pages flushed up 代表了页中的NEWEST_MODIFICATION ，而Last checkpoint 代表了页中的OLDEST_MODIFICATION 。

• FLUSH LIST 使用OLDEST_MODIFICATION 进行记录并排序，那在刷新脏页时， CheckPoint 的LSN 值就对应的是当前刷新到某个页的OLDEST_MODIFICATION ；
• 当某个页只被修改过一次，则Pages flushed up 与Last checkpoint 会相等，反之多次修改，则Pages flushed up 大于Last checkpoint ；
• 在恢复时，从CheckPoint 开始恢复，如果当前页的LSN大于CheckPoint 的 LSN ，则表示不需要恢复了；

新的LSN = 旧的LSN + 写入的日志文件大小，例如，日志文件大小为600MB，目前的LSN是1GB，现在要将512字节的更新记录写入redo log，则实际写入如下：

• 求出偏移量：由于LSN数值远大于日志文件大小，因此通过取余的方式，得到偏移量为400MB
• 写入日志：找到偏移400MB的位置，写入512字节日志内容，下一个事务的LSN就是1000000512

2.4.3 CheckPoint的分类

• Sharp CheckPoint
• 将所有的脏页刷新回磁盘
• 通常在数据库关闭的时候
• 刷新时系统hang住
• innodb_fast_shutdown={1|0}
• Fuzzy CheckPoint
• 将部分脏页刷新回磁盘
• 对系统影响较小
• innodb_io_capacity
• 最小限制为100
• 一次最多刷新脏页的能力，与 IOPS 相关
• SSD 可以设置在4000-8000
• SAS 最多设置在800多（IOPS在1000左右）

2.4.4 刷新条件

• Master Thread Checkpoint
  • 从FLUSH_LIST 中刷新
• FLUSH_LRU_LIST Checkpoint
  • 从LRU_LIST 中刷新（即使不在脏页链表中）
    • 5.6以后需要保证在 LRU_LIST 尾部要有1024个空闲页（可替换的页），即刷新一部分数据，保证有1024个空闲页
  • innodb_lru_scan_depth – 每次进行 LRU_LIST 刷新的脏页的数量
    • 应用到每个Buffer Pool实例，总数即为该值乘以Buffer Pool的实例个数，如果超过 innodb_io_capacity 是不合理的
    • 建议该值不能超过 innodb_io_capacity / innodb_buffer_pool_instances
• Async/Sync Flush Checkpoint
  • 重做日志重用
• Dirty Page too much Checkpoint
  • innodb_max_dirty_pages_pct 参数控制

3.1 表空间

所有的数据都存储在表空间中，表空间分为

• 系统表空间
• 独立表空间
• undo表空间
• 临时表空间
• 通用表空间

3.1.1 系统表空间

• 以ibdata1命名
• 存储元数据信息
• 存储change buffer的信息
• 默认10M，在遇到高并发时，会遇到性能影响，建议初始为 1GB

show variables like 'innodb_data_file_path';

+-----------------------+-----------------------+

| Variable_name         | Value |

+-----------------------+-----------------------+

| innodb_data_file_path | ibdata1:1G:autoextend |

+-----------------------+-----------------------+

• Innodb_data_file_path 定义系统表空间的路径、初始化大小、自动扩展策略，默认扩展64M

show variables like 'innodb_autoextend_increment';

+-----------------------------+-------+

| Variable_name               | Value |

+-----------------------------+-------+

| innodb_autoextend_increment | 64    |

+-----------------------------+-------+

innodb_data_file_path = ibdata01.dbf:2018M:autoextend:max:100G

• ibdata01.dbf：文件名
• 2018M：文件大小
• autoextend：是否可扩展
• max:100G：最大可用空间

对应一个数据文件，如果有多个数据文件需要设置，在添加数据文件的时候，在后面添加分号”;”

• 数据文件保存路径通过系统变量 innodb_data_home_dir 设置

innodb_data_home_dir=/data/mysql/3306/data 

也可以通过下面的方法设置路径

innodb_data_file_path = /data/mysql/3306/data ibdata01.dbf:2018M:autoextend:max:100G

3.1.2 独立表空间

每个表拥有一个单独的.idb的数据文件，这个文件就是单独的表空间

为了避免索引的表和所有保存在系统表空间，导致I/O争用，建议启动 innodb_file_per_table

相比较系统表空间，多重表空间有下列优点

• 各表对象的数据独立存储在不同的文件，可以分散I/O、执行备份恢复
• 支持compressed row format压缩存储数据
• truncate/drop表时，可以释放空间到操作系统
• 空间自动扩展
• 分区表会产生独立的ibd文件
• 当需要删除表（drop table）时， 独立的表空间存储可以直接删除文件，而ibdata1 存储也只是把该部分表空间标记为可用，所以从速度上看很难说哪个更快；但是删除文件后， ibdata1 占用的空间不会释放；

独立表空间由 innodb_file_per_table 控制

• 1或ON为启用
• 0或OFF为禁用

show global variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

该参数是个动态参数，启动了这个参数之后，新创建表会在操作系统生成 [表名].idb文件。

对于已经存在系统表空间的表，可以使用 alter table tbl_name engine=innodb 重建表对象，然后就可以保存在独立表空间。

3.1.3 undo表空间

• 5.6之前，undo默认在系统表空间，
• 配置参数 innodb_undo_directory 独立UNDO表空间。
• 与UNDO相关的参数如下：

• innodb_undo_directory：指定UNDO日志的位置
• innodb_undo_tablespaces：指定UNDO表空间的数量
• innodb_undo_logs：指定回滚段的数量

• undo表空间必须在数据库创建之前指定

3.1.4 临时表空间

• 以 ibtmp1 命名
• 5.7之前，临时表默认在系统表空间
• 配置参数 innodb_temp_data_file_path 独立临时表空间
• 临时表相关信息存储在information_schema.innodb_temp_table_info 中

3.1.5 通用表空间

多个表放在同一个表空间中。减少metadata的开销

3.1.6 space_id

每个表空间都对应一个SpaceID ，而表空间又对应一个ibd文件，那么一个ibd文件也对应一个SpaceID

• 因为表空间 <-> idb文件， 表空间 <-> SpaceID ，所以ibd文件<-> SpaceID
• ibdata1 对应的SpaceID 为0
• 每创建一个表空间（ibd文件） ， SpaceID 自增长（全局）

3.2 段

段有多种，比如表段、索引段、回滚段。段由多个区和32个页组成。一个索引2个段，分别是叶子节点段和非叶子节点段；一个表4个段

3.3 区

每个扩展固定1MB，由64个16K的连续的页组成，页大小8K由128个组成

3.4 页

• 最小的 I/O 单位，所有表空间的页大小都相同。
• 默认16K，在初始化的时候可以通过innodb_page_size修改成4K、8K、16K。5.7开始可以调整为32K、64K。
• 预留 1/16用于更新，类似Oracle的pctfree

3.4.1 PageNumber

PageNumber：在一个表空间中，第几个16K的页（假设 innodb_page_size = 16K） 即为PageNumber，每个表空间中，都是从0开始递增，且仅仅是表空间内唯一

如何定位到页？

每次读取 Page 时，都是通过 SpaceID 和 PageNumber 进行读取；

3.5 行

3.5.1 heap_number

heap_number表示页中每个记录插入的顺序序号

• 假设插入的数据是a, b, d, e, g ；则对应的heap_number 为2，3，4，5，6
• 0 和1 被infimum 和supermum 所使用

• infimum 对应最小的heap_number
• supermum 对应最大的heap_number，随着数据的插入，该值会更新

• update对heap_number没有影响
• heap_number是物理的，存储在row的record_header 字段中

3.5.2 数据存储格式

Innodb存储引擎有两种文件格式

• Antelope，有两种记录行的格式
• compact：对于行溢出的列，行头部前768个字节保存相关信息。
• redundant：相比compact消耗更多的空间，不建议使用
• Barracuda，也有两种记录行的格式
• compressed：物理上的压缩，读到内存时需要转换，压缩比 1/2。生产不建议使用
• dynamic：对于行溢出的列，数据页只存前20b 的指针，数据都存放在溢出的页中

查看表的行格式

show table status like 'emp'\G

*************************** 1. row ***************************

Name: emp

Engine: InnoDB

Version: 10

Row_format: Compact

查看文件格式

show variables like 'innodb_file_format';

+--------------------+----------+

| Variable_name      | Value    |

+--------------------+----------+

| innodb_file_format | Antelope |

+--------------------+----------+

5.7之后默认使用 dynamic 行记录模式和 Barracuda 文件存储格式。

3.5.3 行溢出

如果一条记录大于page的一半，那么选择其他的page存储。类似Oracle的行迁移、行链接。

3.6 索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table），或者叫聚集索引（clustered index）

• 每张表都必须有一个主键
• 根据主键的值构造一棵B+树
• 这棵B+树的叶子节点（leaf page） 存放所有的记录（Row）
• 非叶子节点（Non-leaf page）存放的主键和指针（ 若干个{主键，指针}组成一个非页节点）

这里的指针其实就是PageNumber （这里不需要SpaceID ，因为SpaceID对应的是ibd文件，现在是在ibd文件内部查找数据）

3.6.1 主键

如果创建表的时候没有显示指定主键，则InnoDB会按照如下方式选择或创建主键：

1. 判断表中是否有非空的唯一索引，如果有，该列即为主键；如果存在多个非空唯一索引，以创建表时第一个定义的非空唯一索引为准，而不是（columns）定义的顺序
2. 如果上述条件都不符合，则InnoDB自动创建一个6字节大小的指针；

3.6.2 Page空间申请

• 叶子节点（leaf page） 由leaf page segment 进行申请空间
• 非叶子节点（Non-leaf page） 由Non-leaf page segment 进行申请空间

所以索引由两个段组成

• leaf page segment
• Non-leaf page segment

段（segment） 是由区（extent） 组成，申请空间就按照区（extent）进行申请（一般情况下一次申请4个区）

内容总结

以上是互联网集市为您收集整理的MySQL学习笔记二——InnoDB存储引擎全部内容，希望文章能够帮你解决MySQL学习笔记二——InnoDB存储引擎所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端