InnoDB存储引擎的核心参数

"双一"标准

innodb_flush_log_at_commit参数
innodb_flush_log_at_commit参数的作用是控制从 log buffer(如redo buffer、undo buffer) 往磁盘刷写数据的粗略。innodb_flush_log_at_commit参数有三个状态：

• 为0时：log buffer 中的数据是按照每秒写入到 OS（操作系统缓存）中，然后再每秒刷新一次数据到磁盘中。每次事务的提交不会有任何影响，也就是此状态下 log buffer 的刷新操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果mysqld进程崩溃，通常会导致最后 1s 的日志丢失。
• 为1时：在每次提交事务的时候，就必须完成 log buffer 中的数据写入到 OS 中，然后立刻又将数据刷新到从盘中。这也是默认设置。这是最安全的设置，但是由于每次事务都需要磁盘I/O，所以也最慢。
• 为2时：在每次提交事务的时候，log buffer 中的数据必须写入到 OS 中，然后再每秒刷新一次数据到磁盘中。这时如果mysqld进程崩溃，由于日志已经写入到 OS 中，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s 的日志丢失。
  官方文档解释：

-------
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. （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.
-------

上面说到的"最后1s"并不是绝对的，有的时候丢失更多的数据。有时候由于调度的问题，每秒刷写并不能保证100%执行。对于一些数据一致性和完整性要求不高的应用，配置2就足够了；如果为了最高性能，可以设置为0；有些应用，如支付服务，对数据一致性和完整性要求很高，所以即使最慢，也最好设置为1。

sync_binlog参数
sync_binlog的取值为N，即sync_binlog = N
sync_binlog是MySQL数据库的二进制日志（binary log）同步到磁盘的频率。
如果 autocommit 开启，每个语句都写一次 binary log，否则每次事务写一次。默认值是0，不主动同步，而是依赖操作系统本身不定期把文件内容 flush 到磁盘。设置为1最安全，在每个语句或事务后同步一次 binary log，即使在崩溃时也最多丢失一个语句或事务的日志，但因此也最慢。
大多数情况下，对数据的一致性并没有很严格的要求，所以并不会把 sync_binlog 配置成为1。为了追求高并发，提升性能，可以设置为100或直接用0，对于数据一致性和完整性要求较高的应用，还是比较推荐 sync_binlog = 1。

innodb_flush_method参数

由于InnoDB存储引擎对于数据文件和日志文件都有自己的内存缓冲，在真正写入磁盘时，完全可以不用 OS 的缓冲机制（避免双缓冲的冗余浪费）。所以InnoDB存储引擎提供了 innodb_flush_method 参数来控制对文件写入的方式。

首先来看一下Linux OS中的几种文件操作方式
O_DIRECT、O_SYNC、O_DSYNC、以及default模式（当然可选的远不止这四个，由于本文不涉及其它的，就忽略介绍了）

1. default模式：每次读写都会使用 OS 缓冲，写时不保证数据落入磁盘
2. O_DIRECT模式：每次读写文件时是直接向磁盘写入，绕过 OS 缓冲，但是返回的时候不保证数据和元数据信息已经完全刷新到磁盘
3. O_SYNC模式：仍然会使用 OS 的缓冲，只不过在每次写的时候强制将缓冲中的数据刷新到磁盘中（包括所有元数据信息）
4. O_DSYNC模式：仍然会使用 OS 的缓冲，只不过在每次写的时候强制将缓冲中的数据刷新到磁盘中（包括部分元数据信息）
   O_DSYNC相较于O_SYNC模式，可能会减少磁盘操作次数，因为元数据和文件数据可能在磁盘的不同区域，O_DSYNC可以减少这部分元数据的磁盘操作

然后我们来看看MySQL是怎么使用这些文件操作方式的，这里的前提是MySQL每次写文件的目的都是刷新到磁盘中，仅仅将数据写入 OS 缓冲是不足以保证崩溃后的数据一致性。MySQL通过 innodb_flush_method 参数来调节数据文件和日志文件读写的方式

在Linux OS下，innodb_flush_method参数的取值为：

• fsync（默认选项）：日志文件和数据文件使用default + fsync函数 操作，即读写都经过 OS 缓冲，OS 缓冲中的数据都通过Linux中一种专门用于刷入磁盘的系统调用 fsync函数，刷写进磁盘中。
• O_DSYNC：使用上述的O_SYNC方式打开日志文件，即每次写完日志文件，OS 会自动立即通过 fsync函数 将数据刷新到磁盘；使用default + fsync函数 操作数据文件。
• O_DIRECT：使用上述O_DIRECT方式操作数据文件，绕过 OS 缓冲，然后每次数据刷新到磁盘中这一步通过 fsync函数 完成的（由于O_DIRECT方式返回的时候不保证数据和元数据都刷新到磁盘中了，这时就需要调用 fsync函数 保证刷盘保证）；对于日志文件，此模式下使用default + fsync函数 操作。

InnoDB存储引擎常用的其它参数：

#存储引擎默认设置参数
db01 [(none)]>select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

#独立表空间设置参数
db01 [(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |    
+-------------------------+
1 row in set (0.00 sec)
//innodb_file_per_table=1 此参数默认设置为1，表明开启独立表空间；若设置为0，则表示关闭独立表空间

#共享表空间设置
db01 [(none)]>select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)
在MySQL的配置文件"/etc/my.cnf"中添加这两行参数：
innodb_data_file_path=ibdata1:512M:autoextend  //表示创建一个大小为512M的共享表空间，且512M空间使用完后自动扩容
innodb_autoextend_increment=64   //每一次自动扩容的空间大小为64M

若是想一次性配置两个共享表空间，可以如下设置：
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64

tip:切记！！！
配置共享表空间一定要在初始化MySQL之前就要配置好，配置好以后就不要再动了。

#redo log设置有关的参数
innodb_log_buffer_size = xxxx     指定redo buffer 的大小
innodb_log_file_size = xxxx       指定一个redo log文件的大小
innodb_log_files_in_group = xxx   指定有几个 redo log 文件，默认有两个 ib_logfile0 ib_logfile1

#MySQL中内存脏页刷写策略设置
innodb_max_dirty_pages_pct=75     指定脏页占据内存多少时，就要把数据写回磁盘了   设置为75，表明脏页占据内存的百分之75时就要把数据写回磁盘了
tip：还有哪些机制会触发脏页写回磁盘？
      1、触发CSR时
      2、redo log 满了时

参考文章：
https://my.oschina.net/u/3211934/blog/3163267
https://blog.csdn.net/staforn/article/details/80422758
https://blog.csdn.net/gua___gua/article/details/44916207