innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit 是 MySQL 中 InnoDB 存储引擎的一个关键系统变量，它控制着事务提交时重做日志（redo log）的写入磁盘策略。该参数有三个可选值：0、1 和 2，每个值对应不同的日志刷新行为和性能/安全性权衡。以下是这三个设置的详细区别：

1. 设置为 0

• 日志刷新行为：
  • 事务提交时，重做日志不会被立即写入磁盘，而是将日志缓存在内存中。
  • 随后，InnoDB 会依赖后台的日志刷新线程（Master Thread）每秒将日志缓冲区（log buffer）的内容写入磁盘一次。
• 性能影响：
  • 由于减少了磁盘 I/O 操作，这种设置通常能提供较高的数据库性能。
• 数据安全性：
  • 在系统崩溃或突然断电的情况下，可能会丢失最近一秒内的事务数据，因为这些事务的日志还没有被写入磁盘。

2. 设置为 1

• 日志刷新行为：
  • 每次事务提交时，重做日志都会被立即写入磁盘，并进行同步（fsync）操作，确保日志数据被物理地写入磁盘。
• 性能影响：
  • 这种设置提供了最高的数据持久性，但会对性能产生一定的影响，因为每次事务提交都需要等待磁盘 I/O 操作完成。
• 数据安全性：
  • 在系统崩溃或突然断电后，数据库能够恢复到最近一次成功提交的事务状态，只会丢失崩溃前最后一秒内的事务数据（如果此时事务正在提交过程中）。

3. 设置为 2

• 日志刷新行为：
  • 每次事务提交时，重做日志会被写入到操作系统的文件系统缓存（page cache）中，但并不会立即等待这些日志被刷新到磁盘上。
  • 随后，InnoDB 会依赖操作系统的后台刷新机制或定期刷新策略（如每秒一次的刷新）来将缓存中的日志写入磁盘。
• 性能影响：
  • 与设置为 0 类似，由于减少了每次事务提交时的磁盘写入等待时间，这种设置也提供了较高的性能。
  • 但与设置为 0 相比，它在一定程度上提高了数据的安全性，因为日志至少被写入了操作系统的缓存。
• 数据安全性：
  • 在系统崩溃或突然断电的情况下，如果操作系统的缓存还没有被刷新到磁盘，那么最近一秒内的事务数据可能会丢失。
  • 然而，与设置为 0 相比，由于日志至少存在于操作系统的缓存中，因此在某些情况下（如仅数据库进程崩溃而操作系统正常）数据可能更安全。

总结

设置值	日志刷新行为	性能影响	数据安全性
0	事务提交时不立即写入磁盘，每秒由后台线程写入	较高	较低，可能丢失最近一秒的事务数据
1	事务提交时立即写入磁盘并同步	较低（但符合 ACID 持久性要求）	最高，只会丢失崩溃前最后一秒内的事务数据
2	事务提交时写入操作系统缓存，不等待磁盘写入	较高（略低于设置为 0）	中等，系统崩溃时可能丢失最近一秒的事务数据，但比设置为 0 更安全

在选择 innodb_flush_log_at_trx_commit 的值时，需要根据业务对数据安全性和性能的具体需求进行权衡。如果业务对数据安全性有严格要求，应选择设置为 1；如果业务对性能有较高要求，且可以接受在系统崩溃时丢失少量数据的风险，可以考虑设置为 0 或 2。然而，需要注意的是，将 innodb_flush_log_at_trx_commit 设置为 0 或 2 时，应确保有足够的措施来防止数据丢失，如使用 UPS（不间断电源）来保护系统免受突然断电的影响。