InnoDB简介

• innodb 引擎 基本上就是一个小型的 oracle
• 基于B+树的聚集索引组织表,主键采用聚集索引高效存取
• 实现基于索引的行锁
• 完整支持ACID
• 支持外键、全文索引
• 具备四种事务隔离级别
• 快速创建、删除二级索引
• 支持多种文件格式，数据压缩
• 支持crash recovery(故障恢复)
• 高性能、高并发、高效的buffer pool 管理

InnoDB组成（三个）

• caching
• transaction system
• sorange

内存级别

主要是 innodb buffer pool：
每个表都会 尽可能把 更多的数据放在内存的buffer 里面

• page cache
  数据的缓存， 对普通索引的一个更新（如果有修改的数据 ，需要通过刷新线程，首先刷新到数据文件里，其次再刷新到doublewrite buffer 里面，还有一些可能要刷到 对应的change buffer里面
• adaptive hash indexes（自适应哈希索引，对数据缓存的再一次的哈希的索引）
• buffer pool 里面有两个列表：
• • 一个是 LRU的列表（热点数据 都放在 LRU里面 ）
• • 一个是flush 的列表（脏 数据 列表，事务产生了脏数据 放在 flush 列表中 然后 刷新）

事务系统

有一个 log buffer，这里面的 log 指的是 redo log 是一个自己独立的 buffer
还包含 redo log ，至少两个，因为是循环使用的，log0 用完以后 就用log1，然后再用 log 2，等等 ，然后 一直的不断循环使用。

存储系统

• 系统表空间
• 独立表空间
• undo tablespace ，mysql 5.6+才有
• temporary tablespace（临时表空间） ，mysql 5.7+ 才有
• general tablespace（通用表空间），mysql 5.7+ 才有

系统表空间

（ibdata1、ibdata2、ibdata3）

系统表空间是必须要有的，也就是说 ibdata1 文件是必须要存在的，这个文件要是不存在 那么 innodb 直接就挂了。

系统表空间是由 innodb_data_file_path 这个选项定义的，这里面可以定义系统表空间的绝对路径，也可以定义相对路径，可以设置自动化的初始化的大小。初始化 默认是12 M，我们建议是 默认 1G 或者是 500M 左右。也可以设置最大值，比如设置 innodb的系统表空间，最大只能 1G，超过 1G以后 ，那这里面的事务 可能就会受到影响，可能影响到事务并发，但是一般不会设置他的 最大值，只设置他的 初始 大小，连自动扩展策略都不用，默认是他的一次自动扩展的大小是 8M。

系统表空间里面存储的数据 主要有：

• data dictionary（数据字典）
• double write buffer（一块在内存里，一块在 磁盘里，这两块是并行存在的）
• insert buffer /change buffer（以前叫 insert buffer，现在叫 change buffer，同样他也是基于内存 和磁盘两个都有的）
• rollback segments（回滚段）
• UNDO space
• foreign key constraint system tables
• user data ，if innodb_file_per_table=0

主要的是 回滚段和 是undo

这两个如果放在系统表空间里面，有可能在高并发的时候，有可能会把系统表空间，也就是 ibdata1 文件全部撑爆，比如默认初始化可能是 1G，但是跑一段时间以后可能就变成 2G，再过一段时间可能就变成 3G，所以，独立出来就没事了。（5.6 开始允许 把undo 表空间独立出来）

ibdata 1 文件里面怎么存的？

FSP_HRD：文件头
IBUF_BITMAP：change buffer 里面的
INODE：INODE的一些信息
INDEX：change buffer 的根节点

独立表空间

innodb_file_per_table =1 这个选项启用以后，可以使用独立表空间，这个选项 从5.5 开始就已经是 默认启用。

每个表都会有自己的 独立的XX.ibd 文件，但是 rollback segments，doublewrite buffer 等仍存储在系统表空间文件里面。

数据表空间里面主要存储的就是 聚集索引 和其他的 普通索引数据 ，所以说 innodb他的数据表空间里面其实存储的都是索引，要么是聚集索引，要么是普通索引

general tablespace（通用表空间 ）

5.7 开始支持的 功能，类似于 oracle 里面的 表空间的做法，可以把多个表放在一个公共的通用表空间里面，然后我们还可以定义多个 通用表空间，分别 放在不同的磁盘上。

通用表空间的好处：

主要是 为了可以减少 metadata (元数据/中继数据) ,这方面的存储的开销，以及 如果我们删除一个大表，但是我们使用 的是通用表空间的话，那么我们删起来更快一些 ，因为他只需要删除metadata 里面的数据 ，所以更快一些。

通用表空间 VS 系统表空间

两者是比较类似的，已分配占用的表空间没有办法回收还给操作系统的，只有需要重建 才可以。

temporary tablespace（临时表空间）

5.7 新增的，以前 临时表空间是和myisam 一起存储的，或者是memory，表空间文件叫 ibtmp1，默认是 12M。

• 实例关闭以后，这个文件会删掉
• 实例启动后，这个文件也会被新创建，所以 临时表空间里面的文件，相当于实例一旦重新创建，就会全部消失，另外，临时表空间是不需要参与 crash recovery ，因此也不记录 redo log ，只记录 undo （需要回滚，但不需要 前滚，所以不需要记录 redo）
• 不支持 压缩
• 由 innodb_temp_data_file_path定义
• 通用临时表 create temporary table 和 SQL执行过程中产生的内部临时表 （using temporary table） 会共用 这个临时表空间

undo tablespace

5.6 开始支持，存储事务中的旧数据

• innodb_undo_logs 设置 undo 表空间个数（也就是我们使用多少个 undo 表空间）
• 系统表空间总是 需要1 个undo 表空间
• 临时表空间总是需要 32 个undo 表空间， 因此，undo 表空间总是必须大于 33个，并且循环轮流使用
• 5.7 可以被在线 truncate (删记录) ，也就是 undo 表空间用完以后，如果没有及时释放，那我们可以在线的手动的 truncate undo 表空间，避免暴涨。
• 当undo 超过 innodb_max_undo_log_size （undo 表空间最大的值）时， 会触发 truncate 工作，会把 所有的不需要的 undo 表空间都 truncate 掉
• purge （清除工作）执行 innodb_purge_rseg_truncate_frequency 次后，也会触发truncate 工作，默认是 128次（也就是当执行 128次 破解操作以后 ，也会触发undo 的 truncate 操作，建议使用独立的 undo 表空间，这样可以避免 ibdata1 文件暴涨）

建议使用独立 undo 表空间

独立表空间 VS 共享表空间

独立表空间

• 表空间更方便回收（比如一个表 使用独立表空间的，如果 把这个表删掉的话，他的表空间文件也会跟着删掉，立刻就能释放 这个表的存储空间）
• 透明表空间文件迁移（可以从一个实例迁移到 另一个实例）
• 如果我们使用地理表空间的话 会 需要更多的 file handler（文件描述符），尤其是 当table 数量很多，或者有很多表分区的时候，可能会是个 麻烦。

共享表空间

• 也就是把所有的文件 都放在 ibdata 1 文件里面，或者是 ibdata1 或者是 ibdata2 文件 这样的存储文件里面

共享表空间的优势：

• 当 删除大表或删除大量数据时候，因为他不会涉及到一些文件系统层面的 操作，他只需要一些更新innodb 里面的 matadata ，也就是 drop table / truncate table 这些操作很快
• 可以使用 裸设备
  据说 裸设备性能 会更好一些，但是实际上 innodb 基本上 没有人会使用裸设备，会有些不方便，因为 不支持表空间的迁移但是据说性能可能 有提升。

四种行格式

• redundant
• compact
• dynamic
• compressed

>select * from information_schema.innodb_sys_tables;
+----------+---------------------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME                            | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+---------------------------------+------+--------+-------+-------------+------------+---------------+------------+
|       14 | SYS_DATAFILES                   |    0 |      5 |     0 | Antelope    | Redundant  |             0 | System     |
|       11 | SYS_FOREIGN                     |    0 |      7 |     0 | Antelope    | Redundant  |             0 | System     |
|       12 | SYS_FOREIGN_COLS                |    0 |      7 |     0 | Antelope    | Redundant  |             0 | System     |
|       13 | SYS_TABLESPACES                 |    0 |      6 |     0 | Antelope    | Redundant  |             0 | System     |
|       15 | SYS_VIRTUAL                     |    0 |      6 |     0 | Antelope    | Redundant  |             0 | System     |
|       40 | master_test/t1                  |   33 |      5 |    26 | Barracuda   | Dynamic    |             0 | Single     |
|       34 | mysql/engine_cost               |   33 |      9 |    20 | Barracuda   | Dynamic    |             0 | Single     |
|       32 | mysql/gtid_executed             |   33 |      6 |    18 | Barracuda   | Dynamic    |             0 | Single     |

里面的 file_format 有 antelope （旧的 file_format ，支持redundant和 compact ）也有barracuda （支持 dynamic 和 compressed ，都是新增的 两种形式，同时也会兼容 redundant和 compact ）

虽然支持四种行格式，但是主要用 compact 和 dynamic 这两种行格式

5.6 默认的行格式为 compact
5.7 开始默认的行格式 是 dynamic

compact 是比较紧凑的行格式，相对来说更节省空间，dynamic 跟 compact 差不多，最大的区别就是最大对象的列存储方式不一样，如果是 compact ，如果遇到大对象的列，他需要做溢出存储的时候，会把前 768个字节 放在当前的page里，但是如果是dynamic，会之存储 20个字节的指针，把整个列的数据放在独立的一个page里面，

查看一下行格式的状态
select * from infomation_schema.innodb_sys_tables where name like '%table_name%';

dynamic VS compact

• compact 格式，会存储 前768个字节，放在当前的page里面，多出来的放在 额外的overflow page里面
• dynamic 格式，会存储 20个字节 的指针，然后剩下的整个列全部放在 overflow page （独立的一个page）里面。

例如 有一个列 65KB，这个时候一个16KB的 page是存不下的，那么就需要 5 个page来存储，如果很巧的是一个列的长度 正好是16KB+1个字节（1B），也就是说一个page是存不下的，但是实际上，这个列是 8192 =8KB ，如果这个page，他的行 长度超过 8KB的话，正好是 8193B，这个时候 两个page存的，另一行是16385B=16KB，这个时候也需要两个page来存储

一个存16KB，另一个存1B，这种就很浪费，所以说我们再做表设计的时候，尽可能让他和innodb的page size 是对应的，如果涉及到这种大对象的话，最好是定长的，innodb的page是按照实际存储的字节的长度来区分的，不是按照我们设计的长度区分的

当一个行记录，他的长度大约超过一个page 的一半，默认一半是 8KB （5.6 后可以调整大小），就会依次将 最长的列拆分到多个page 存储，直到不再超过page的 一半为止（例如说，现在的表里面，有很多个 大对象列，最长的是A列：32KB，B列：25KB，然后是C列： 15KB，）
A列首先被分离出去，溢出存储的列（至少需要2个page存储），然后 B同理，也要分离出去（也至少需要2个page），C需要1个page， （由此可见，32KB也是占用2个page，20KB也是两个page，所以说B这列就存在浪费）
其中 A的两个列是单独存在不能共享的，同理B也是
溢出的存在的page 是不能被共享的，都是独享的，是单独的对象，会压缩的大对象列
大对象列 指的是 text这种，出现 filesort 排序 或者是 temporary table（产生临时表，像两个表做join）的话，这种列是无法放在内存里面处理的，MySQL会认为这个对象太大了，会浪费内存，直接不在内存进行处理，会直接要求他们转成 disk tmp table （基于磁盘的临时表），所以说I/O 代价会很高，效率很低，所以尽量少使用，尤其是要做 排序和表连接的时候 。

select 会同时读取这些溢出的列，所以会有很高的IO 代价，所以说建议尽量只读 必要的列，而不是每次都读 多有的列

innodb每个行至少都要存储以下几个基本的信息

• db_row_id，6 bytes，指向对应的行记录，每次写新数据该ID都会自增，如果已有显示声明的主键，则不需要存储db_row_id
• db_trx_id（当前行的最新的事务ID），6bytes，每个事务的唯一标识符。
• db_roll_ptr，7bytes，指向 undo log 的回滚指针（当前这行指向上一个版本的回滚段的一个指针）
• 用于实现 MVCC（traction id 和 rollback point 结合起来，主要是为了实现多版本）

表空间整理

消除碎片

• alter table table_name engine=innodb;（相当于对整个表都重新整理了一遍，可以消除碎片的）
• 或者 optimize table table_name
• 尽量使用pt-osc 操作

回收表空间

• 独立表空间：alter table table_name engine= innodb
• 共享包空间：重新导出，导入
• 尽量用pt-osc 来操作

表空间文件迁移

• 目标服务器上：alter table table_name discard tablespace
• 源服务器上：flush table table_name for export，备份过去
• 目标服务器上：alter table table_name import tablespace
• 表结构务必一致
• innodb_page_size 也要一致

InnoDB 索引

innodb 是基于 B+树的 聚集索引组织表

数据以 聚集索引 （clustered index ）逻辑顺序 存储，聚集索引选择的顺序

• 聚集索引优先选择显式 定义的主键
• 其次选择第一个非 NULL 的唯一索引
• 再次使用隐藏的 rowId

注意

• 聚集索引叶子节点 存储整行数据
• 而普通索引存的是主键索引键值
• 所有的索引都由 2 个 segment 组成
• • leaf page segment （叶子结点）
• • non-leaf page segment （非叶子结点）

InnoDB 主键索引

InnoDB 主键索引建议

• 最好是自增属性，int/bigint 最佳（对于innodb 而言，数据新写入的时候基本上等同于像堆表写入的效率一样比较高
• 没有业务用途的的，通常也不会去更新主键的，所以说只要不涉及到 列从很小变成很长这种更新的话，基本上性能不会有太大的影响
• 如果是数据表 每次都是变长的更新的话，变成更长的那种更新的话，那么他的PTS和 每次变短的更新相比，两种的性能差是很大的。
• 数据类型长度小，性能更佳
• 数据顺序写入也是顺序的，不会离散，最佳
• 这样也有利于更多普通索引放在buffer page中（因为普通索引他后面也要跟着主键 键值的那个列，所以说 如果主键的列比较短的话，那么放在buffer pool里面 索引的数量也会更多一些）
• 主键尽量不要更新，否则更新主键时，辅助索引也要跟着更新