mysql之数据

数据存储

数据库最终的数据都会存储在磁盘中。但是为了性能的考虑也会将数据放在在内存中进行操作和存储，内存中的数据也是必不可失的。
当在innodb中寻找一条数据时，首先会查内存，内存中有则直接修改或者(是查询的话)直接返回，内存没有则去磁盘捞取当前数据所在的页加载到内存中，然后进行返回或者修改。 至于修改后内存如何更新到磁盘中去就是所谓的刷脏页。（内存与磁盘数据不一致）
那么从上面一句话可以得到一条结论，innodb的数据行在内存中就是最新的，不在内存中磁盘中就是最新数据。

刷脏页的时机：

接下来说一下什么时候去刷脏页：
就是将内存与磁盘数据不一样的数据行写入磁盘。
假设几种可能：
1内存满了
内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

2redo log满了
redo log是在同一块地方进行循环的写，redo log记录的变动会被清除，在清除时可能redo log中有记录变更的数据还未刷入磁盘中，这时就得需要去判断这些变动的数据是否刷入磁盘，没有则进行刷脏页。

3系统空闲时，看到内存还有没刷入的脏页就去刷一下
4mysql正常关闭

上面的1，2种可能说的都是极限状态，在实际中如果处于当前状态则整个数据库性能则非常低，更新被堵或者查询被堵

需要解决的问题： innodb什么时候刷盘及刷盘速度？，innodb刷盘的IO参数如何设置？

innodb刷盘的IO参数设置：
innodb_io_capacity 这个参数用来告诉innodb的当前实例机器的磁盘能力。
建议设置为磁盘的iops：
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

innodb什么时候刷盘及刷盘速度：
参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。
参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。
当前脏页比例状态：F(M) （0—100）
InnoDB 会根据当前的脏页比例（假设为 M），(如果当前脏页比例超过上面的上限参数，M则为100)在经过一系列的复杂运算得到F(M)，双方成正比
当前redo log日志状态：F(N) （0—100）
redo log每次写入都会有一个序号，通过这个序号跟 checkpoint 对应的序号之间的差值，假设为N，在经过一系列的复杂运算得到F(N)，双方成正比

F(M)与F(N)对比，取两者较大的一个值记为R， 引擎就会按照innodb_io_capacity 参数 乘以R%来空值刷脏页的速度。
刷盘速度=innodb_io_capacity * R * 0.01

当开始刷内存中的脏页时，只会将数据刷入磁盘，不对redo log操作。
但是脏页对应的redo log可能时随机存放的，redo log在重放的时候会识别出来并跳过。 识别的方法就是LSN与数据页的对比。

合理设置innodb_io_capacity ，观察脏页比例 不要经常接近上限。
脏页比例查询：
Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

连坐机制：
当一个查询需要刷新一个脏页时，因为连坐机制可能会更慢，现象是 如果要刷的这个脏页旁边的数据页刚好也是脏页，那么会连带着把隔壁也会刷进去，并且这种连坐机制会蔓延，如果隔壁的隔壁仍然是脏页也会刷进去。

innodb中有参数来设置是否开启这个功能：
innodb_flush_neighbors 值为1时连坐，值为0时不连坐，只刷自己。

数据库表空间存放与回收

参数 innodb_file_per_table
这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

数据删除

某条记录的删除，只会标记当前位置可以复用，它会有先决条件要符合左邻右舍的范围之内。
例如：3，4，6 删除中间为4的数据，4记录可以复用，但是要在3-6的范围内。
整个数据页删除，标志整个页都可以复用了，可以随时应用到需要新数据页的地方。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

delete 命令作用是：所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。
相当于delete之后，之前数据存放的地盘数据清空了，但是地盘还是属于你。

插入数据

如果按照索引递增顺序依次插入，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

重建表

alter table A engine=InnoDB 命令来重建表
5.5之前 当前命令会阻塞所有操作DDL语句。
跟下面Online DDL 类似， 只不过临时文件tmp在server层。

5.6后引入了Online DDL
临时文件tmp在innodb层。
Online DDL 步骤：
1建立一个临时文件，扫描表 A 主键的所有数据页，
2扫描得到的数据生成B+树，存储到临时文件。
3生成临时文件过程中，原表之后新加的数据放入一个日志文件，临时文件生成后将日志文件写入。
4替换原表

崩溃恢复场景

场景描述：变更语句过程：改内存-(T1)-> 写redo log—(T2)–>写binlog–(T3)–>提交事务

T1时刻数据库崩溃：此时事务未提交，两个日志都未写入，恢复后当前事务回滚。
T2时刻数据库崩溃：redo log写完但是还未提交，binlog 还没写，恢复后当前事务回滚。
T3时刻数据库崩溃：
第一种情况：redo log写完，且有commint标识，则直接提交。
第二种情况：redo log写完，只有prepare，则判断对应事务的binlog是否存在完整，如果完整则提交事务，否则回滚。

如何判断binlog是否完整：
statement 格式的 binlog，最后会有 COMMIT；row 格式的 binlog，最后会有一个 XID event。
MySQL 5.6.2 版本以后引入binlog-checksum 参数。

redo log 和 binlog的关联关系：
两者日志都有一个数据字段：XID,当上面的T3时刻第二种场景时，就是通过这个XID去与binlog做对比。

redo log处于prepare与binlog是完整时则就可以恢复数据，为什么？-也就是T3-第二场景
因为此时Binlog已经写入，当前Binlog可能会被其他从库或者作为恢复数据使用。

为什么分两阶段提交
从上面可得知需要redo log和binlog双方完整性才可以恢复数据，那分开单独提交，恢复时则判断双方都有则可以恢复。 为什么要分成两阶段提交最后统一commit呢？
因事务的持久性，提交后则不可变更，如果单独一方提交，另一方失败，因持久性原则不可回滚。双方数据不统一。 两阶段提交保证给所有人一次机会。