我的分享

Appearance

mysql配置参数 ​

sync_binlog ​

sync_binlog 是 MySQL 中的一个系统变量，用于控制 MySQL 服务器将二进制日志（binary log）写入磁盘的同步方式。二进制日志是 MySQL 用来记录所有修改数据库数据的语句（如 INSERT、UPDATE、DELETE 等）和可能更改数据或可能产生数据变化的 DDL 语句（如 CREATE TABLE、ALTER TABLE 等）的日志文件。这些日志对于数据恢复、复制（replication）以及审计等场景非常重要。

sync_binlog 的值决定了 MySQL 在将二进制日志的内容写入其内部缓冲区后，多久将其同步到磁盘上。这个设置对于数据库的可靠性和性能有重要影响。

  • sync_binlog=0：默认情况下（在某些 MySQL 版本和配置中可能不是默认的），MySQL 不会将二进制日志同步到磁盘。相反，它依赖于操作系统的调度机制来定期将缓冲区的内容刷新到磁盘。这种方式可以提高性能，因为减少了磁盘 I/O 操作，但在系统崩溃或电源故障时可能会丢失尚未写入磁盘的二进制日志数据，从而导致数据丢失。
  • sync_binlog=1：这是最安全的设置。每当事务提交时，MySQL 都会将二进制日志同步到磁盘。这保证了即使在系统崩溃或电源故障的情况下，也不会丢失已提交事务的二进制日志数据。然而，这种设置可能会降低性能，因为频繁的磁盘 I/O 操作会增加系统的负担。
  • sync_binlog=N（N 是大于 1 的整数）：这是一个折中的设置。MySQL 会在收集了 N 个二进制日志事务的提交后，将二进制日志同步到磁盘。这种设置既提供了一定程度的数据安全性（因为不会每次事务都进行磁盘 I/O），又减少了频繁的磁盘写操作对性能的影响。然而，N 的值需要根据具体的应用场景和硬件性能来权衡。

在配置 sync_binlog 时，需要仔细考虑数据库的可靠性和性能需求。对于对数据安全性要求极高的应用场景，建议将 sync_binlog 设置为 1。而对于性能要求更高、可以容忍一定程度数据丢失风险的应用场景，则可以考虑将 sync_binlog 设置为 0 或一个较大的 N 值。不过，需要注意的是，将 sync_binlog 设置为 0 可能会带来较高的数据丢失风险，因此通常不建议在生产环境中这样做。

innodb_flush_log_at_trx_commit ​

innodb_flush_log_at_trx_commit 是 MySQL 中 InnoDB 存储引擎的一个重要参数，它控制着事务提交时重做日志（redo log）的刷新行为。该参数有三个可选值：0、1、2，其中0和2在行为上有着显著的区别，这些区别主要体现在数据的安全性和系统性能上。

数据安全性

  • 0：当 innodb_flush_log_at_trx_commit 设置为 0 时，事务提交时重做日志不会被立即写入磁盘，而是将日志缓存在内存中。这些日志的写入和同步操作由 InnoDB 的主线程（master thread）每秒执行一次。这意味着，如果系统在这一秒内崩溃，那么这一秒内提交的所有事务的重做日志都可能丢失，从而导致数据的不一致或丢失。
  • 1：在每次事务提交时，重做日志都会被物理写入磁盘，并确保日志的同步（fsync）操作完成。提供了最高的数据持久性，即使在系统崩溃后，也只会丢失1秒内的数据。
  • 2：当设置为 2 时，事务提交时重做日志会被写入到文件系统的缓存中，但并不会立即同步到磁盘。InnoDB 依赖于操作系统的缓存机制来最终将日志写入磁盘。这通常意味着每秒（或更长时间，取决于操作系统的具体行为）会有一次从文件系统缓存到磁盘的同步操作。因此，如果系统崩溃或断电，可能会丢失最近一秒内的事务数据，但相比设置为 0，其数据丢失的风险要小一些。

系统性能

  • 0：由于减少了磁盘 I/O 操作，将 innodb_flush_log_at_trx_commit 设置为 0 可以显著提高数据库的性能。这对于那些对性能有极高要求，且可以容忍一定数据丢失风险的应用场景非常有用。
  • 1：对性能有较大影响，因为每次事务提交都需要等待磁盘I/O操作完成。
  • 2：虽然设置为 2 也会减少每次事务提交时的磁盘 I/O 操作，因为日志只是被写入到文件系统的缓存中，而不是直接写入磁盘，但相比设置为 0，它在数据安全性上提供了一定的保障。因此，它在性能和安全性之间提供了一个折中的选择。

注意事项

  • 在选择innodb_flush_log_at_trx_commit的值时，需要根据应用的具体需求来权衡数据的安全性和性能。
  • 如果对数据的实时性和持久性要求非常高，应该选择值为1，以保证数据的完整性和一致性。
  • 如果应用对数据丢失的容忍度较高，并且对性能有较高要求，可以考虑选择值为0或2，以提高数据库的写入性能。
  • 在生产环境中，建议谨慎调整该参数的值，并在调整前进行充分的测试和评估。

查看和设置

  • 查看当前值：可以使用SQL语句SELECT @@innodb_flush_log_at_trx_commit;来查看当前的设置值。
  • 设置新值：可以使用SQL语句SET GLOBAL innodb_flush_log_at_trx_commit = [新值];来设置新的值，但请注意，这个设置是全局的，并且会影响所有新的连接，但不会改变已经存在的连接的设置。对于已经存在的连接，需要断开并重新连接才能应用新的设置。

query_cache_type ​

通过参数query_cache_type启用或者禁用query cache。

在sql性能优化过程中，禁用当前session query cache特性以调试sql优化后的性能

sql
set session query_cache_type=0;