innodb的内存结构

buffer pool

使用innotop工具查看。

• 数据缓存
  InnoDB数据页面
• 索引缓存
  索引数据
• 缓冲数据
  脏页（在内存中修改尚未刷新(写入)到磁盘的数据）change buffer
• 内部结构
  如自适应哈希索引，锁（5.5之前）等。

MySQL InnoDB存储引擎大观 - 简书

MySQL Buffer Management

buffer pool中的链表【三种链表】

• free链表：空闲的
  • 
  • 链表通过“控制块”访问缓冲页
• lru链表（改进lru）：最近访问的页（其中有已经修改的页，也有没有修改的页）
  • 
  • 先清除的是tail
  • 新插入放在lru_old处。
  • 数据页被访问，在链表时间长于1秒（innodb_old_blocks_time）则移动到head，短于一秒原地不动。
  • innodb_old_blocks_pct：默认37%控制冷热比例
  • innodb_old_blocks_time：默认1s之后再放入热区域（连续读两次算1次，如果一个页中有多行数据，将被连续读多次）

• flush链表：脏的

  • flush包含在lru链表（有已经修改的页，也有没有修改的页，flush只引用被修改的页）
  • flush链表保存指针
  • 作用：page cleaner thread定时刷新赃页

    buffer pool配置参数

    InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置
    成可用物理内存的 60%~80%。【默认128MB】

• innodb_buffer_pool_size：大小

• innodb_buffer_pool_instances：buffer pool实例数量【多线程并发】
  • pool_size小于1G，默认instance=1；
• innodb_buffer_pool_chunk_size：【默认128M】【buffer pool动态调整大小】buffer pool以innodb_buffer_pool_chunk_size为单位进行动态增大和缩小。调整前后innodb_buffer_pool_size应一直保持是innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。（程序会自动调节）

MySQL · 特性分析 · innodb buffer pool相关特性

buffer_pool预热【buffer pool dump】

• innodb_buffer_pool_dump_pct：[5.7]预热dump百分比

ib_buffer_pool保存内容：，预热时加载这些页。

1. change buffer（insert buffer）

change buffer5.5之前叫insert buffer
数据页不在内存中，则将更新操作缓存在changebuffer中。
定期purge数据到磁盘。（Page Cleaner Thread）
唯一索引需要判断唯一性，所以用不了change buffer。所以，单纯从速度上讲，普通索引可能插入效率更高。

change buffer记录到redo log，避免数据丢失。
参数：

• innodb_change_buffer_max_size：表示change buffer在buffer pool中的最大占比，默认25%，最大50%
• innodb_change_buffering：表示索引列merge对象，all表示对IDU索引列都起作用，都进行merge，如果只想对insert索引列进行merge，就把all改为inserts。

buffer pool的命中率

show engine innodb status

• Buffer pool hit rate

2. Adaptive Hash Index自适应hash【优化主键搜索】【建议关闭】

• 查看：show engine innodb status
  • 
• value是对应到记录而不是页，自适应hash判断热点页，将页中的记录创建hash索引。
• 优化主键搜索，通过hash搜索比b树搜索快。
• MySQL5.7 默认开启。
• 不推荐的原因：索引的是记录，维护代价大。

3. double write buffer【InnoDB page 默认16k】

InnoDB默认的Page大小是16KB(innodb_page_size)，是大于文件系统能保证原子的4KB大小（磁盘io的单位是4k，磁盘存储的单位512B）

• 最小文件是4k
  • 只有1个字节的文件实际占用多少磁盘空间？ - 知乎
  • NTFS 文件系统是如何处理小文件使占用空间为 0？ - 知乎

为什么innodb page设置为16k不设置为4kb？

可以配置参数，但4k效率低。16k容纳数据多。

有redo log为什么还要double write buffer？

如果写一个Page写一半，redo log无法判断此页是否写成功了。 redo记录的是修改，而不记录完整数据（redo恢复的前提是这个页是干净的）

redo log写磁盘时是否需要double write buffer？

一个redo log block是512字节
应该不需要，最后redo log 修改为commit才算事务提交成功。如果redo log不全，修改commit失败，等于事务提交失败回滚。
**

InnoDB关键特性之double write - GeaoZhang - 博客园
刷写赃页步骤：

1. 拷贝到double write buffer中
2. 写共享表空间ibdata文件中【顺序写】
3. 写数据文件ibd文件【随机写】

如果出现Page写一半的情况，从ibdata中恢复数据。【如果事务一半宕机，从redolog恢复】

log buffer【redolog】

MySQL配置

InnoDB数据页结构【页】【 默认16kb】

一个页中有许多行数据。

页的类型

页内部——页目录、槽、分组、记录

一个数据页中的记录：

• 页目录（page directory）为了快速从一个页中找出数据
• 页目录中存放的是“槽”

• 每个槽放每个分组的最大值的地址

  页内搜索过程

• 主键搜索：页内搜索记录使用二分法定位到槽，再遍历槽中记录。

• 非主键：遍历

  innodb ROW_FORMAT行格式【行】

  

• Compact、 Redundant、 Dynamic和Compressed四种行格式

• Barracuda，Antelope两种文件格式
• msyql 5.7.9 及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是DYNAMIC。

查看行格式

设置修改行格式

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

Compact行格式

• 真实数据中有3列隐藏列。（row_id,transaction_id,roll_pointer）

  1. 变长字段长度列表

  记录varchar这样的变长字段长度，逆序。
  
  另外如果是char(M)类型使用utf8编码也是变长的，也要存储到变长字段长度列表。【单位是字节？】
  

• 例如char(2)，可以存aa，也可以存两个汉字，他们的数据长度是不同的。所以字段长度也要存到变长字段长度列表中。

• char(M)有最小长度（就是每个字符按一个字节算）至少占用M个字节，varchar没有最小长度。

Mysql 为什么默认定义varchar(255) 而不是varchar(256) - 简书

2. null值列表【null就不记录变长字段长度了，但是专门记录】

用位表示字段是否为空
高位补0

3. 记录头信息（包含指向下一条记录指针）

• delete_mark
• min_rec_mark：非叶子节点最小的目录项。
• n_owned
  • 
• heap_no：infimum和Supremum记录的heap_no分别为0,1
  • 记录顺序
• record_type：
  • 0：普通记录
  • 1：B+ 树非叶节点的 目录项记录
  • 2：lnfimum 记录
  • 3：Supremum记录
    • 

• next_record：按主键排序的下一条记录。

  • 为啥指向记录头信息和真实数据之间的位置？因为这个位置刚刚好，向左读取就是记录头信息，向右读取就是真实数据。【这也是之前“变长字段长度”逆序的原因】

    4. 真实数据前的 隐藏列

    默认增加了一些“隐藏列”
    

• DB_ROW_ID：行的唯一标识。（在没有自定义主键以及Unique键的情况下才会添加该列）

• DB_TRX_ID：和事务相关，事务id
• DB_ROLL_PTR：回滚指针

⼀个行中的所有列（不包括隐藏列和记录头信息） 占⽤的字节⻓度加起来不能超过65535个字节！

5. 行溢出

• page大小是16k（16384字节），一行最大是65535字节（刨去长度和null65532字节），所以要多个数据页。
• 一个page最少两行记录
• 单行记录大于page一半就行溢出，选出最长的列做行溢出。

• 在 MySQL InnoDB 中，系统默认单个索引长度最大为 767 bytes

Dynamic行格式（5.7默认）

Dynamic和Compressed行格式，5.7默认行格式就是Dynamic， 这俩行格式和Compact行格式挺像， 只不过在处理行溢出数据时(存大对象)有点儿不一样。

数据压缩

compressed页压缩

• 数据量增加，内存能放的有效数据变少，对性能有影响

  tpc压缩【透明页压缩】（5.7.17没有编译进去，8.0支持）

  
  alter table a compression=’zlib’;

• 只有页需要刷新到磁盘时才压缩

• 虽然多了压缩步骤，但是数据量小了。写性能有所提升。
• 对当天正在使用的流水表不要启用TPC功能
• 对历史流水表启用TPC

MySQL InnoDB透明页压缩的简单分析 - JciX ~

• lz4性能好（推荐）
• zlib压缩比高

独立表空间 & 共享表空间

MYISAM文件结构是：MYI、MYD、frm
下面是innodb的

• 数据段
  • 一个page是一个b+树的节点。
• 索引段
• 回滚段

MySQL实战

.ibd文件【innodb独立表空间】【5.6.6之后默认独立表空间】

ibdata1里保存了哪些东西，为什么会变得越来越大呢，ibdata1是InnoDB的共有表空间，默认情况下会把表空间存放在一个文件ibdata1中，会造成这个文件越来越大。发现问题所在之后，解决方法就是，使用独享表空间，将表空间分别单独存放。MySQL开启独享表空间的参数是Innodb_file_per_table，会为每个Innodb表创建一个.ibd的文件。

.frm文件

在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件
里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了【元数据使用Innodb存储，无frm文件】

查看frm文件（mysql utilities中）

ibdata文件【系统表空间system tablespace】

开启独享表空间后，并不是说就不需要ibdata1了，因为在ibdata1中还保存着下面这些数据。

[共享表空间]InnoDB数据和索引【5.6.6之前默认共享表空间】

[共享表空间]undo log【5.7之后独立undo_001】

• innodb_undo_tablespaces

  InnoDB表的元数据（information_schema.tables）

  
  

  change buffer

  ``sql show variables likeinnodb_file_per_table`; #独立表空间为on

独立表空间可以 optimize table ```

ib_logfile文件【log buffer redolog持久化】

redo log

ibtmp1：临时表表空间[5.7]

tmpdir：临时目录，保存有临时表的结构frm文件。
内容放在ibtmp1中。

两种表空间的优点缺点

推荐使用独立表空间。因为可以回收表空间。
MySQL 5.6.6 之后默认 innodb_file_per_table = on

delete 命令 删除整个表。所有数据页都标记为可复用。磁盘文件大小不变。
插入数据，也会产生空洞。

【重建表，收缩表空间】optimize table& analyze table

innodb按页为单位存储。
delete 命令是不能回收表空间的，磁盘占用大小不变，但“空洞”可以复用。

MYISAM重建

optimize table table.name 对MYISAM有效
针对innodb会

压缩myisampack

和optimize table 有啥区别？

Innodb重建

推荐使用 开源的gh-ost来做在线表重建。

ALTER TABLE mydb.mytable ENGINE=INNODB;

• 重建普通索引：

alter table T drop index k
alter table T add index(k)
重建主键索引：
重建主键索引会导致整个表重建？？
alter table T engine = InnoDB

• 5.6之后支持DDL，重建时可以

MySQL锁总结

ANALYZE TABLE mydb.mytable;

• 重建索引为了整理空间碎片。
• analyze table t 只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；