innodb存储引擎笔记（上）

MySQL发展路线图：

MySQL体系结构

InnoDB体系架构图

总体架构图：

存储结构：

表空间

所有的数据都需要存储在表空间中表空间分类 系统表空间（ibdata1）独立表空间（innodb_file_per_table）undo tablespace，MySQL5.6+temporary tablespace，MySQL5.7+general tablespace，MySQL5.7+（类似于oracle的）

问：数据库创建后，还能创建undo 表空间不？ 答：目前不能，undo表空间是在数据库初始化的时候创建的。

问：undo有默认的过期时间吗？ 答：undo没有默认的过期时间。等这个事务提交完以后，没有更多的事务需要这份undo log的时候，它就可以被清除。也就是MVCC里面没有事务再需要这份旧的undo log的时候，就可以purge掉；

系统表空间

总是必须的文件名ibdata1innodb_data_file_path定义路径、初始化大小、自动扩展策略主要存储对象 data dictionarydouble write bufferinsert buffer/change bufferrollback segmentsundo space（新版本可以独立出去）foreign key constraint system tablesuser data，if innodb_file_per_table=0

建议大小： innodb_data_file_path = ibdata1:1G:autoextend 除去占用空间最大的undo space之后，剩下的就很小了，不需要太大空间。

独立表空间

设置innodb_file_per_tables = 1每个table都有各自的xx.ibd文件rollback segments，double write buffer等仍然存放在共享表空间文件里主要存储聚集索引B+树以及其他普通索引数据

日志文件

undo logredo log

Innodb是聚集索引组织表

基于B+树数据以聚集索引逻辑顺序存储聚集索引有限选择显示定义的主键 其次选择第一个非null的唯一索引再次使用隐藏的rowid聚集索引叶子节点存储整行数据普通索引同时存储主键索引键值所有的索引都由两个segment组成 leaf page segmentnon-leaf page segment

表空间

独立表空间优势 表空间可更方便回收透明表空间文件迁移共享表空间优势 删除大表或删除大量数据时开销更小，drop table/truncate table可以使用裸设备，据称性能可能有提升。

表空间管理

消除碎片 alter table xx engine=innodb;或者optimize table xx；尽量用pt-osc来操作回收表空间 独立表空间：alter table xx engine = innodb;共享表空间：重新导入导出尽量用pt-osc来操作表空间文件迁移 目标服务器上：alter table xx discard tablespace;源服务器上：flush table xx for export，备份过去，修改权限目标服务器上：alter table xx import tablespace;表结构务必一致innodb_page_size也要一致

问：如何看innodb表的碎片？ 答：show table status like ‘tab_name’,看data_length+index_length 然后看idb文件，对比大小。 问：有表 查是400M 物理文件9G，原因是什么？ 答：各种大字段 varchar(4000)啊 text什么的。。。

表空间文件迁移步骤：（5.6以后）

1.在目标实例上创建一张和源实例表结构相同的表（表名可以不同）。 2.在目标实例上将表空间discard。 删除旧表空间文件。 alter table xx discard tablespace（执行完这个命令之后，ibd文件就没有了，只剩frm文件了） 3.在源实例上执行FLUSH TABLE xx FOR EXPORT。（执行完这个命令之后，表可以读取，但无法修改，metadata lock。目录下多了个.cfg文件） 将文件cp到目标实例上。并且修改文件权限。 4.在目标实例上执行import tablespace。源实例上unlock tables。 alter table xx import tablespace。

问：表空间迁移到目标实例后，有数据，但是数据行数、大小统计不到了。。。 答：anaylze table

通用表空间（general tablespace）（现在用的不多）

5.7+类似oracle的做法多个table放在同一个表空间中可以定义多个通用表空间，并且分别放在不同的磁盘上可以减少metadata的存储开销和系统表空间类似，已分配占有的表空间无法收缩归还给操作系统（全部需重建）

临时表空间，temporary tablespace

5.7+独立表空间文件ibtmp1，默认12MB实例关闭后，文件也会删除实例启动后，文件新创建无需参与crash recovery，因此也不记录redo log不支持压缩由innodb_temp_data_file_path定义

问：排序会用到临时表空间吗？ 答：不会，只有create temporary table xxx;这种才会用到。 通用临时表会用到。内部临时表不会用到。 select * from x order by y; 由于没有索引，产生的排序临时表，称为内部临时表，如果session内存排序空间不足，会放到internal_tmp_disk_storage_engine指定的问题，磁盘临时文件。 create temporary table xxx; 通用临时表

undo表空间，undo tablespace

5.6+独立的undo表空间存储事务中的旧数据innodb_undo_logs设置undo表空间个数系统表空间总是需要1个undo表空间临时表空间总是需要32个undo表空间（5.7+）因此，undo表空间总是必须大于33个，并且循环轮流使用可以被在线truncate（5.7+）当所有的undo加起来超过innodb_max_undo_log_size时，会触发truncate工作purge 执行innodb_purge_rseg_truncate_frequency次后，也会触发truncate工作（默认128次）建议使用独立undo表空间

innodb是聚集索引组织表

基于B+树数据以聚集索引逻辑顺序存储聚集索引优先选择显示定义的主键 其次选择第一个非null的唯一索引再次使用隐藏的rowid聚集索引叶子节点存储整行数据普通索引同时存储主键索引键值所有的索引都由2个segment组成 leaf page segmentnon-leaf page segment

存储结构

tablespace -> segment -> extent(64个page，1M) -> page

page，页 最小IO单位，16kB，5.6版本起可以自定义page size每个page最少存储两行记录（因为是B+tree结构，是双向链表。因此必须存储至少两行记录，才能前后互相连接起来。）extent，区 空间管理单位每个extent总是1MB，由64个page组成如果page size是8kB的话，则由128个page组成segment，段 对象单位，例如rollback seg，undo seg，data seg，index seg等每个segment由N个extent以及32个零散page组成segment最小以extent为单位扩展tablespace，表空间 表存储对象每个tablespace都至少包含2个segment（叶子/非叶子 page file segment）

问：每个page至少两行记录，那如果是空表，怎么办？ 答：每个page都至少有两个虚拟记录，这两个虚拟记录指向的是虚拟最小记录和虚拟最大记录，确保了每个page至少存储两行记录。infimum， supermum

innodb主键

最好是自增属性，INT/BIGINT最佳数据类型长度小，性能更佳数据顺序写入也是顺序的，不会离散也更有利于将更多普通索引放到buffer page中主键尽量不要更新，否则更新主键时，辅助索引也要跟着更新。（想想为啥？辅助索引记录主键值）

row，行记录

row-format redundant,最早的行格式compat，5.0.3以后的默认行格式dynamic，将长字段完全off-page存储compressed，将data，index pages进行压缩，但buffer pool中的page则不压缩。压缩比约1/2，但tps能下降到原来的1/10，不建议使用。查看行格式：select * from I_S.innodb_sys_tables where name like ‘%tab_name%’;行溢出，overflow 行记录长度大约超过page一半时，一次将最长的列拆分到多个page存储，知道不在超过page的一般为止。溢出的列放在一个page中不够的话，还会继续放在新的page中compat格式下，溢出的列只存储前768个字节dynamic格式下，溢出的列只存储前20个字节（指针）select * 会同时读取这些溢出的列，因此代价很高出现filesort或temporary table时，一般都无法放在内存中，需要变成disk tmp table，IO代价更高。每row中至少存储几个基本信息 DB_ROW_ID，6byte,指向对应行记录，每次写新数据该ID自增，如果已有显示声明的主键，则不需要存储DB_ROW_IDDB_TRX_ID，6byte，每个事务的唯一标识符DB_ROLL_PTR，7bytes，指向undo的回滚指针用于实现MVCC

注意：现在默认的数据文件类型已经是Barracuda了，不是Antelope了，两种最大区别是对于超长列的存储。Antelope前768个字节存储在行里，Barracuda只存储20个字节的指针。（只针对单行长度超过page大小一半的时候，才会overflow page，并不是只要出现blob或者text就一定会overflow的，单行长度不超过page大小一半，即使行中有blob列，也不overflow page）

内存管理

innodb buffer pool一般设置物理内存的50%-70%（5.7可以在线调整）设置太大可能导致SWAP使用多个buffer pool instance降低并发内存争用（一般建议每个instance管理8-16G内存）page采用hash算法分配到多个instance中读写每个缓冲区池管理自己的数据 innodb_buffer_pool_instances每个instance管理自己的free list，flush list，LRU list及其他，并由各自的buffer pool mutex负责并发控制

可以在线修改buffer pool size

在启动时预装入buffer pool

innodb_buffer_pool_load_at_startupinnodb_buffer_pool_dump_at_shutdown 设置buffer pool刷新机制 innodb_flush_method = O_DIRECT（绕过操作系统缓存，直接写阵列卡）buffer pool管理 LRU机制两个列表，young ，old优先放在young队列超过innodb_old_blocks_time后移入old队列old队列默认占比innodb_old_blocks_pct = 37

提问：在主从复制中，一张表若是除了一个非唯一的二级索引外并无其他索引，为什么根据二级索引的索引列来更新，也需要校验整行记录的值，并且所有的值一致才能正常在slave上重放？ 不是根据二级索引，得到一个内部的隐藏rowid就可以进行更新了吗？

答：因为同一行的rowid在主从上不一定一致。 为什么ROW格式日志一定要用主键定位记录，如果用二级索引行不行？虽然没有主键那么精准，但至少可以避免全表扫描。 根据主键做的更新，不会校验行数据。