MySQL存储引擎InnDB核心参数

大家好，我是anyux。本文介绍MySQL存储引擎InnDB核心参数。

innodb_flush_log_at_trx_commit参数解释

查看参数

 select @@innodb_flush_log_at_trx_commit;
复制

默认为1

innodb_flush_log_at_trx_commit控制的是redo向磁盘刷写的策略

在了解innodb_flush_log_at_trx_commit参数之前，需要了解MySQL服务的整体架构

MySQL具有专门的内存结构，下层有OS buffer，就是文件系统缓存，最后是disk磁盘

redo日志是在事务发生变化，在数据页发生变化时，在MySQL的redo buffer中生成redo信息，当commit提交时，就会读取innodb_flush_log_at_trx_commit参数值。首先它会刷写到OS buffer中，然后再写到磁盘文件中。

MySQL先把自身的内存日志写入到文件系统层缓存中，再从文件系统层写入到磁盘中。这是基本的逻辑

当innodb_flush_log_at_trx_commit=1时，处理过程是怎么样的呢

每次执行事务，提交commit时，会立即将MySQL内存的中的数据写入到文件系统缓存中，文件系统立即将缓存写入到磁盘中。直到确实将数据写入到磁盘中，commit命令才算执行成功。当值为1时，数据写入很安全，保证了数据入库的安全性

当innodb_flush_log_at_trx_commit=0时，处理过程是怎么样的呢

每秒钟将数据写入到磁盘中。每秒会发生很多事务，如果此时断电有可能丢失1秒内所有事务。因为它在1秒内未将数据写入到磁盘中

当innodb_flush_log_at_trx_commit=2时，处理过程是怎么样的呢

每次事务提交时，只保证写入到文件系统缓存中，然后每秒写入磁盘中

注意：最安全是参数值为1时，会最大限度的保证数据写入的完全性。对于数据安全性要求高的参数值设置为1.对于一些不重要的数据，参数值设置为0或2都可以，原因是可以减少大量的磁盘I/O。具体值可以根据公司业务调整

 The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
 默认设置为1是完全符合ACID要求的。每次事务提交时，日志都会写入并刷新到磁盘。
 
 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.
 设置为0时，每秒将日志写入并刷新到磁盘一次。未刷新日志的事务可能在崩溃中丢失。
 
 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.
 设置为2时，每次事务提交后都会写入日志，并每秒刷新一次到磁盘。未刷新日志的事务可能在崩溃中丢失。
复制

面对SBR和RBR如何选择呢？

SBR:原样记录sql语句，可读性强，对于范围操作，批量操作记录数据少，语义性强。但SBR存在明显的缺点，就是在对于now()这样的时间语句也会如实记录，对于像数据同步，数据恢复时，这些数据会出现问题。所以不建议使用SBR模式

RBR:会逐行记录变化的值，日志量较大，但是数据精确，在数据同步，数据恢复时不会出现错误。所以推荐使用

innodb_flush_method参数解释

它控制的是两部分，一个是redo buffer的刷写，另一个是buffer pool的刷写。redo buffer指缓冲redo log日志，buffer pool指的是缓冲数据和索引。控制的是刷写磁盘的时候是否经过文件系统缓存

对于Unix操作系统，有6个可选值，常用的只有三个分别是，fsync,O_DSYNC,O_DIRECT

查看参数

 select @@innodb_flush_method;
复制

注意，对于Unix操作系统来说，如果innodb_flush_method值为NULL,则默认使用fsync,对于Windows操作系统，如果值为NULL,则默认使用async_unbuffered

当innodb_flush_method=fsync时，处理过程是怎么样的呢

第一步，同时将redo buffer 和 buffer pool 写入到系统缓冲区中。第二步，同时将系统缓冲区的数据刷写到磁盘中。

当innodb_flush_method=O_DIRECT时，处理过程是怎么样的呢

第一步，将 buffer pool 写入到磁盘中。第二步，将redo buffer 写入到系统缓冲区后，再刷写到磁盘中。

当innodb_flush_method=O_DSYNC时，处理过程是怎么样的呢

第一步，将redo buffer写入到磁盘中。第二步，将 buffer pool 写入到系统缓冲区后，再刷写到磁盘中。

最高安全模式

innodb_flush_log_at_trx_commit=1

innodb_flush_method=O_DIRECT

最高性能模式

innodb_flush_log_at_trx_commit=0

innodb_flush_method=fsync

innodb_log_files_pages_pct脏页刷写策略

有哪些行为会触发脏页写入到磁盘

CSR：基于redo的前滚操作

redo写空间满了，将redo日志写入到磁盘