day06-01-InnoDB核心参数说明

上节回顾

1.表空间

1.1 说明

独立表空间：5.6 开始的默认表空间，一个表一个ibd文件，存储数据行和索引。

共享表空间：5.5 默认是共享表空间，所有表的行和索引都存储到ibdata1

说明：从5.6 开始，不再使用共享表空间模式

5.6 版本 数据字典信息 + undo + tmp

5.7 版本 把tmp独立

8.0 版本 把undo独立

1.2 表空间迁移

1）创建和原表结构相同的表

2）新建表的ibd删除

alter table tb_name discard tablespace;

3）拷贝原表ibd文件到新文件

4） 导入ibd到新表

alter table tb_name import tablespace;

1.3 ibtmp1 保存临时表

当出现多表链接，或者出现排序/去重，就会使用ibtmp1临时表空间

1.4 undo 重做的日志存储位置

2.0 ACID

原子性，一致性，隔离性，持久性

3.redo （ib_logfile 重点记忆）

作用：

（1）记录：内存数据页变化日志

（2）提供：快速的事务的提交

（3）CSR：redo提供前滚的功能

4.undo（ibdata1 重点记忆）

undo 有段和slot槽位，但没有区和页（page）

slot槽位，用来保存事务信息

（1）记录：数据修改之前的状态

（2）提供：事务工作过程中的回滚操作（rollback）

（3）CSR：把未提交的事务进行回滚

5.隔离级别(重点记忆)

RU读未提交，会有脏读，幻读，不可重复读

RC（重点）读以提交，不会有脏读，会有幻读，不可重复读。（可以容忍和钱无关的业务）

RR（重点）默认模式，可重复读，防止不可重复读。有可能会出现幻读（前提是列上没有索引）。

（MVCC,undo快照+GAP（间隙锁）+nextlock（下键锁））

SR：可串行化，可以防止所有问题，有效防止死锁。但并发支持很不好

6.不可重复读(现象 非常重要)

隔离级别是RC：

client-A:开启事务对表 t1 进行数据更新并提交。

client-B:每次查询 t1 到的结果都是最新的，结果随着client-A更新而变。

这就是数据的不可重复读。

7.幻读（现象 非常重要）

隔离级别是RC：

client-A:

begin;
update t1 set name='aa' where id>2;

未提交。

client-B 同时在做insert 操作并commit提交。

client-A: commit并查询，发现id>2的条件中，client-B 新增数据并未修改。

这就是幻读现象。

---

8.InnoDB 核心参数的介绍

存储引擎默认设置：

show engines;
show variables like 'default_storage_engine';
select @@default_storage_engine;

default_storage_engine=innodb

独立表空间模式

show variables like 'innodb_file_per_table';
innodb_file_per_table=1

共享表空间文件个数和大小设置：

innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

缓冲区池，查询

select @@innodb_buffer_pool_size;
show engine innodb status\G
innodb_buffer_pool_size 
一般建议最多是物理内存的 75-80%

---

"双一"标准其中一个

（非常非常重要）事务提交时redo刷写磁盘的策略参数：默认1，分别0，1，2

innodb_flush_log_at_trx_commit=1

查看：

root@localhost 09:09:52->select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

root@localhost 09:10:06->

为1时：表示commit提交动作发生时，mysql先确保把redo buffer里的内容刷写到OS 的FS cache成功，再确保从 FS cache 刷写到 磁盘成功。必须都成功，否则提交则不成功。

为0时：不管是否commit提交，只要redo buffer里面有内容，就以秒为单位，刷写到FS cache，再每秒从FS cache刷写到磁盘。(宕机，掉电异常关机，会丢失1秒数据风险)

为2时：首先每次commit时，确保redo刷写到FS cache成功。再以每秒为单位，从FS cache刷到磁盘。(宕机，掉电，会丢失1秒数据风险) 。

追求数据安全设置1，追求性能设置0。

这是官方关于这个参数给到的解释：

The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

双“一”标准之二（非常非常重要）

sync_binlog =1

作用：每次事务提交，都立即刷写到binlog到磁盘。

---

innodb 刷写模式：（非常非常重要）

查看：

show variables like '%innodb_flush%';

Innodb_flush_method=(O_DIRECT,fsync)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method

作用：控制的是redo buffer(ib_logfile)，data buffer pool(ibdata1)刷写磁盘的时候是否经过文件系统缓存

默认：FSYNC 模式

这个模式下，redo buffer 和 data buffer pool区域内数据，都会以同样的方式步骤，进行写盘操作。（先写入FS cache，再从FS cache刷写到磁盘）

O_DIRECT：（推荐模式，安全度高）

这个模式下，redo buffer 内的数据会按照FSYNC模式的步骤进行刷写到磁盘。

data buffer pool内的数据会绕过FS cache，直接进行flush 写磁盘操作

O_DSYNC:

这个模式下，刷写策略与O_DIRECT完全相反。

redo buffer内的数据会绕过FS cache，直接进行flush 写磁盘操作。

data buffer pool内的数据会按照FSYNC模式的步骤进行刷写到磁盘。

这两个参数，使用建议：

#最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
#最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync

---

redo日志设置有关的

日志缓冲区的大小

innodb_log_buffer_size=16777216

日志文件大小（字节）

innodb_log_file_size=50331648

redo文件个数

innodb_log_files_in_group=3

---

脏页刷写策略

innodb_max_dirty_pages_pct=75

innodb的 data buffer pool内存脏页数据占据内存比例：75%

也就是说，在buffer pool中，当比例达到75%时，会触发CKPT。

写数据不是实时的！！！有触发条件

还有哪些机制会触发写盘？

时间机制，比如每10秒？ 每分钟？

CSR 会立即触发CKPT 写盘

redo log空间满的时候，也会触发CKPT写盘