mysql-innodb 4内存与硬盘

大纲：

1. 内存页管理
2. innobd脏页控制策略
3. 表数据如何存储
4. 回收表空间
5. log buffer

 

 innodb内存硬盘模型

 

一、内存页管理

 innodb用buffer pool管理的内存，innodb无论在内存还是磁盘上都是以页的方式存储数据，一页大小默认16k。

select查数据的时候先从磁盘读出数据所在页到内存，然后再从内存返回查询结果。

写数据的时候也是先在内存中找要修改的数据页如果没有，需要先从硬盘读到内存，再修改数据页，最后flush到硬盘。

buffer pool中的页有三种状态：1.空白页；2.读入内存后未被修改的干净页（内存硬盘数据一致）；3.读入内存后被被修改的脏页（内存硬盘数据不一致）。

内存页管理机制：数据库刚开始使用，全部未空白页，使用一段时间之后，内存将被干净页与脏页填满，这时需要淘汰一些。干净页直接淘汰脏页需要flush到磁盘后再淘汰。已使用的数据页由一个链表以改进的lru方式管理，链表结点数按5比3分成2个部分，头占5热数据，尾占3冷数据（通过innodb_old_blocks_pct参数默认是37，可在5-95范围调整百分比）。当一个数据页1s以上被访问2次则被放到整个链表头部（热数据头部），1s以内被访问一次或多次则被放入冷数据的头部。由于1s以内被访问多次有很有可能是一次查询的全表扫描，因此这种数据不被判定未热数据。

 

二、innobd脏页控制策略

除了上述中1.内存满了回刷脏页之外，innodb还有三种情况回刷脏页，2.redolog的write pos赶上checkpoint时；3.mysql正常关闭时；4.mysql空闲时。

innodb刷脏页的速度取决于磁盘IO能力，可以通过fio命令测试磁盘能力，并通过参数innodb_io_capacity告诉innodb当前磁盘的IO能力。

其中3和4没有性能影响，1和2会阻塞数据库读写，然后把脏页刷入硬盘，因此，在redolog或者内存没有满的时候，就要刷一部分脏页。刷脏页速度取决于innodb_io_capacityIO读写能力和nodb_max_dirty_pages_pct脏页比例上限（默认75%）。

在准备刷一个脏页的时候，如果这个数据页在磁盘中旁边的数据页刚好是脏页，则会一起刷掉。innodb_flush_neighbors=1/0来配置是否开启。机械硬盘顺序IO优化明显，固态硬盘优化不明显。

 

三、表数据如何存储

MySQL中每建一张表都会生成一个.frm文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。

innodb表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的:

1. 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起，存储的文件即为后缀ibdata1共享表空间。

2. 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。

默认ON，drop table命令可以直接删除这个表的idb文件。如果OFF，放在共享表空间，即使drop table表空间页无法回收。

innodb 段 区 页：一个表就是一个段，一个段由多个区构成，一个区由64个连续的页组成大小1m。最小的IO单元是页,最小的分配存储单元是区。

 

四、回收表空间

delete数据之后，这一行空间并没有回收，只是标记这一行可以复用，整个页数据删光，这一页被标记为可服用。不仅delete操作，insert操作同样会造成页分裂导致页上数据间产生"空洞"。

可以通过alter table t engine = InnoDB（也就是recreate）表来回收空出来的空间，大致流程：1.将原表复制一份；2复制过程中，记录原表操作到一个日志文件，3.在复制表上重放这些操作，4.将所有操作切换到备份表。

ps：pg库中也由类似的优化表空间的操作：vacuum full table_name，reindex table/index tablename/indexname（重建表上索引/某个索引）

这些操作直接使用都用导致锁表，生产还是找dba用专业插件/工具操作，减少锁表时间

 

五、log buffer

在事务提交之前将redo log一般不会写入磁盘，除非log buffer被写满，默认大小为16MB。

事务提交后写入磁盘时机通过innodb_flush_log_at_trx_commit控制：

1. 0-等待后台线程定时写入，会造成写入间隔间数据的丢失。
2. 1-直接写入磁盘，不会造成丢失。（推荐）
3. 2-写入操作系统page cahe操作系统的缓存，mysql异常重启不丢失，掉电则丢失。

ps：binlog通过sync_binlog设置为1保证每次事务提交直接写入磁盘，这样的双1配置保证事务提交后数据不丢失。