[知识点总结] MySQL中InnoDB的一些知识

整理自《MySQL王者晋级之路》如有侵权，立刻删除

存储引擎

存储引擎名称	特点	应用场景
InnoDB	支持事务，行锁，支持MVCC多版本并发控制，并发性高	应用于联机事务处理
MyISAM	不支持事务，不支持行锁，MySQL 8.0后被废弃	应用于联机分析处理，不建议使用
Memory	数据存放于内存，支持Hash和B树索引，安全性不高但是读取速度快	应用于对安全性要求不高的场景
TokuDB	支持事务，支持压缩，高速写入，在线DDL，不产生索引碎片，归Percona公司	应用于海量数据存储场景
MariaDB columnstore	列式存储引擎，高压缩性	数据仓库，联机分析处理
Blackhole	不存储数据，写入只会写入binlog	做binlog转储或测试

区别	InnoDB	MyISAM
锁的细粒度	支持行锁	支持表锁
事务	支持事务	不支持事务
并发性能	高并发	低并发
存储结构	数据与索引存于.ibd，缓存在内存	数据存于.myd，索引存于.myi，只缓存索引
select count(*)	需要全局扫描，统计行数	读取保存好的行数即可

InnoDB的构成

内存结构，线程，磁盘文件

存储结构

主要的逻辑存储单元为：表空间，段，区，页

tablespace → segment → extent(64个page, 1MB) → page

表空间：InnoDB引擎中存放所有数据的地方，分为系统表（共享表），独立表空间

• 系统表：以ibdata1命名，存储所有数据的信息和回滚段(undo)的信息
  • 优点：便于管理
  • 缺点：无法在线回收，需要回收时，需要备份所有数据，删除原表，再导回到与原表结构相同的新表中
• 独立表：设置参数innodb_file_per_table=1，默认使用
  • 文件存储对应表的B+树数据、索引和插入缓存
  • 优点：便于表空间的转移，回收也很方便
  • 缺点：文件包含.frm和.ibd，单表增长过快可能有性能问题

段：表空间由段组成，通常有数据段，回滚段，索引段，每个段由N个区和32个散页组成，空间扩展以区为单位。创建一个索引就会有两个段产生（叶子段，非叶子段）

区：大小固定为1MB，物理上的连续的一段空间

页：最小的物理存储单位，默认大小16KB，一个区由64页组成

• 页的大小目前是可调的
• 一般数据只使用15/16的空间，剩余空间用于更新数据
• 页最少有两行数据 —— 虚拟最小行和虚拟最大行，用于保证B+树节点的双向性质

行：InnoDB存储是按行存储，存储方式Antelope羚羊，Barracuda梭鱼

• 存储格式：compact紧凑的，dynamic动态的，redundant冗余的，compressed压缩的
• 使用准则：行溢出问题。
  • compact：溢出的列只存放768个前缀字节
  • dynamic：数据存放于off-page中，数据页只存放前20字节的指针
  • redundant消耗大量存储空间，compressed涉及转换，有无用的cpu消耗

内存结构

SGA（系统缓存区）和PGA（程序缓存区）

• SGA: innodb_buffer_pool, Query_cache等
• PGA: sort_buffer_size, join_buffer_size, read_buffer_size

Buffer和链表结构

数据存放在page中，对应到内存就是一个个buffer: free buffer, dirty buffer, clean buffer

• free buffer 未被使用过的buffer，基本不存在
• dirty buffer 被使用过，但是还没有被刷新到磁盘，与磁盘上的数据不一致
• clean buffer 被使用过，已经刷新到磁盘，与磁盘上的数据一致

buffer用链表来管理

• free list 将free buffer连接，需要的时候会调用，不够用是会从lru list和flush list上进行释放
• lru list 将clean buffer用最近最少使用的链表串联
• flush list 将dirty buffer串联，方便线程将脏数据刷入磁盘，内部也有lru规则

线程

InnoDB是多线程模型，包括主线程，后台循环线程，刷新线程，暂停线程。

主线程的工作流程

• 每1s

1. 将日志缓存刷新到磁盘中，即使该事务还未被提交
2. 刷新脏页到磁盘中
3. 执行合并并插入缓存的操作
4. 产生checkpoint
5. 清理无用的cache
6. 如果没有用户活动的话，切换到后台循环线程

• 每10s

1. 前三项和1s内容一致
2. 删除无用的undo页（回滚）
3. 产生checkpoint

内存刷新机制

基本原则：日志先行策略，无论事务提交与否，先记录日志

• redo log 记录事务操作变化的日志
  • 顺序写，循环写机制
  • 刷新到磁盘的条件 innodb_flush_log_at_trx_commit
    • 0：每隔1s，thread将redo log 缓存中的数据，刷入redo log，同时刷盘。但是事务提交不会触发这一机制（不太安全）
    • 1：事务提交时，会触发缓存写入log并刷盘（最安全）
    • 2：事务提交时，会触发缓存写入log，但不刷盘
• binlog 二进制文件，用于备份恢复和主从复制
  • 刷新到磁盘的条件 binlog_sync
    • 0：事务提交，由filesystem来决定何时同步，或者cache满了自动同步
    • n：每次进行n次事务提交后，来一次fsync的磁盘同步指令，写入磁盘

一般将innodb_flush_log_at_trx_commit = 1; binlog_sync = 1；确保安全性

	redo log	binlog
记录内容	物理日志，记录所有InnoDB表数据的变化	逻辑日志，记录所有数据的改变信息
记录时间	记录事务发起后的DML和DDL SQL语句	记录commit后的DML和DDL SQL语句
使用方式	循环写，顺序写	写满后会创建新的binlog
作用	异常宕机或介质故障后恢复	主从复制搭建

主从数据：两阶段提交

1、准备阶段：SQL写入redo log buffer，做事务准备的标记，之后将buffer内容写入redo log

2、提交阶段：事务产生的binlog写入文件，刷入磁盘

只要binlog写入完成，那么主从就都会正常完成事务

三大特性

• 插入缓存
  • 将普通索引的DML从随机IO变成顺序IO：
    • 判断DML是否在缓存池中，在就直接插入，不在先放到change buffer中，然后将change buffer和普通索引合并，一次执行多个插入操作
• 两次写
  • 解决页损坏时，redo log失效产生的问题
    • 脏页在被写入文件前，先写入double write buffer，然后分两次，每次1MB写入”双写“空间，再从双写空间写入文件
    • 如果设备支持原子写，那么两次写自动失效
• 自适应哈希索引
  • 自动建立哈希索引来优化查询，分区进行