innodb io管理

Before writing pages to a data file, InnoDB first writes them to a contiguous tablespace area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer has completed does InnoDB write the pages to their proper positions in the data file. If there is an operating system, storage subsystem, or mysqld process crash in the middle of a page write (causing a torn page condition), InnoDB can later find a good copy of the page from the doublewrite buffer during recovery.

If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page. For a column chosen for off-page storage, InnoDB stores the first 768 bytes locally in the row, and the rest externally into overflow pages. Each such column has its own list of overflow pages. The 768-byte prefix is accompanied by a 20-byte value that stores the true length of the column and points into the overflow list where the rest of the value is stored.
不知道有没有行迁移的情况
InnoDB implements a checkpoint mechanism known as fuzzy checkpointing. InnoDB flushes modified database pages from the buffer pool in small batches. There is no need to flush the buffer pool in one single batch, which would disrupt processing of user SQL statements during the checkpointing process.

During crash recovery, InnoDB looks for a checkpoint label written to the log files. It knows that all modifications to the database before the label are present in the disk image of the database. Then InnoDB scans the log files forward from the checkpoint, applying the logged modifications to the database.

mysql的检查点也比oracle要简单很多
频繁的插入，删除会导致表碎片化，要处理碎片，使用
ALTER TABLE tbl_name ENGINE=INNODB
之前在生产环境操作，以为这样执行会报语法错误，然后就执行成功了，吓了一身冷汗，不知道这个语句究竟干了什么，只能祈祷苍天了，还好原来是反碎片化的操作，以后切记在生产环境上执行的语句自己一定要知道它究竟干了什么，否则不要轻易执行。
回收磁盘空间，可以使用truncate table来回收，只在innodb_file_per_table=on的条件下才有用。