MySQL学习4之备份策略、缓冲机制、日志刷新策略、EXPLAIN命令

一、备份策略

1.1、完全备份

• 介绍: 完全备份是指备份所有数据和日志文件，通常在低峰时段执行。
• 优点: 一旦恢复，可以立即使用所有数据。
• 缺点: 备份文件较大，占用较多存储空间。

1.2、增量备份

• 介绍: 增量备份是指备份自上次完全备份或增量备份以来发生更改的数据。
• 优点: 备份文件较小，节省存储空间。
• 缺点: 恢复时需要多次恢复操作，即先恢复最后一次完全备份，再依次恢复所有增量备份。

1.3、差异备份

• 介绍: 差异备份是指备份自上次完全备份以来发生更改的数据。
• 优点: 比增量备份快，恢复时只需要一次完全备份和一次差异备份即可。
• 缺点: 每次差异备份都会逐渐增大，直到下一次完全备份。

1.4、二进制日志备份

• 介绍: 二进制日志备份是指备份 MySQL 的二进制日志文件，可以用来恢复数据或复制数据。
• 优点: 可以精确到某一时刻的恢复，适合点恢复。
• 缺点: 需要与完全备份结合使用才能完整恢复。

二、缓冲机制

MySQL 的缓冲机制是为了提高查询性能和减少磁盘 I/O 而设计的。这些机制主要集中在 MySQL 的存储引擎层，尤其是 InnoDB 存储引擎，因为它是最常用的事务处理存储引擎。

2.1、查询缓存 (Query Cache)

查询缓存是 MySQL 中用于缓存 SELECT 查询结果的机制。当相同的查询再次执行时，MySQL 可以直接从缓存中获取结果，而不需要重新执行查询。

• query_cache_size: 设置查询缓存的大小。
• query_cache_type: 控制查询缓存的开启或关闭。

2.2、InnoDB 缓冲池 (Buffer Pool)

InnoDB 缓冲池是 InnoDB 存储引擎中最重要的内存结构之一，它用于缓存表数据和索引数据，以减少磁盘 I/O 操作，从而显著提高查询性能。

• innodb_buffer_pool_size: 设置缓冲池的大小，通常是服务器可用内存的 70%-80%。
• innodb_buffer_pool_instances: 分割缓冲池为多个实例，以利用多个 CPU 核心。

2.3、表缓存 (Table Cache)

表缓存 (Table Cache) 是 MySQL 中用于提高性能的一种机制，它缓存了打开的表的句柄和元数据信息，使得在后续的查询中可以更快地访问这些表，通过减少表的打开次数，可以减少磁盘 I/O 操作和文件系统的开销，这对于频繁访问相同表的查询尤其有用。

当 MySQL 需要访问一个表时，它会首先检查表缓存中是否存在该表的句柄。如果存在，则直接使用缓存中的句柄；如果不存在，则打开表并将句柄放入缓存中。

表缓存的大小可以通过 MySQL 的配置文件 (my.cnf 或 my.ini) 中的 table_open_cache 参数来控制。以下是相关的配置选项：table_open_cache

• 设置表缓存的大小，即可以同时缓存的表句柄的数量。
• 默认值通常较小，但在高性能环境中，可能需要增加这个值以容纳更多的表句柄。

三、日志刷新策略

3.1、重做日志 (Redo Logs)

重做日志记录了对数据库所做的更改，这些更改可以用来在系统崩溃后恢复数据。InnoDB 使用重做日志来确保事务的持久性。

重做日志刷新策略：innodb_flush_log_at_trx_commit:

• 0: 表示每秒刷新一次日志文件，即使在事务提交后也不立即刷新，这可以提高性能但降低了安全性。
• 1: 表示每次事务提交时立即刷新日志文件，这是默认设置，提供了较高的安全性。
• 2: 表示事务提交时不立即刷新日志文件，而是在操作系统缓存中累积，然后每秒由操作系统刷新一次，这在性能和安全性之间提供了一个折中。

3.2、二进制日志 (Binary Logs)

二进制日志记录了所有更改数据库结构和数据的 SQL 语句，主要用于复制和恢复。

二进制日志刷新策略：sync_binlog

• 0: 从不强制刷新二进制日志文件，这可能导致数据丢失。
• 1: 每次写入二进制日志文件后立即刷新，这可以提高数据的安全性，但可能会降低性能。

3.3、错误日志 (Error Logs)

错误日志记录了 MySQL 运行期间发生的错误和警告信息。

错误日志刷新策略：

• 错误日志通常会在错误或警告发生时立即写入，以确保记录相关信息。
• 错误日志的刷新策略主要受操作系统和文件系统的影响，通常不需要特别配置。

3.4、慢查询日志 (Slow Query Logs)

慢查询日志记录了执行时间超过指定阈值的 SQL 查询。

慢查询日志刷新策略：

• 慢查询日志通常在查询执行完成后立即写入，以确保记录所有慢查询。
• 通过配置 slow_query_log 和 long_query_time 参数来控制哪些查询会被记录。

3.5、撤销日志 (Undo Logs)

Undo log 是 InnoDB 存储引擎中用于记录事务撤销信息的日志。Undo log 主要用于支持事务的回滚操作以及多版本并发控制 (MVCC)。

Undo log 存储在 InnoDB 的表空间中，而不是单独的日志文件中。每个事务开始时都会创建 undo log 条目，Undo log 是随着事务的执行动态创建和销毁的。一旦事务提交或回滚，与之相关的 undo log 条目就会被标记为不再需要。

刷新策略:

• 由于 Undo log 存储在 InnoDB 的缓冲池中，因此它不是实时刷新到磁盘上的。
• 当 undo log 条目不再需要时，它们会被标记为可释放，并在适当的时机从缓冲池中清除。

3.6、审计日志 (Audit Logs)

审计日志记录了 MySQL 中的用户活动，包括登录尝试、查询执行等，用于安全审计和合规性目的。MySQL 通过插件的方式提供审计日志功能。

审计日志刷新策略:

审计日志本身没有专门的刷新策略，因为审计日志通常是立即写入的，以确保记录所有安全相关的活动。但是，审计日志的写入行为受到以下配置的影响：

# 审计日志配置
audit_log_policy = ALL
audit_log_file = /var/log/mysql/audit.log
audit_log_rotate_threshold = 100M

四、什么是EXPLAIN命令？

EXPLAIN 命令是 MySQL 中一个非常有用的工具，用于分析和优化 SQL 查询。通过使用 EXPLAIN获得有关查询执行计划的详细信息，从而帮助用户理解 MySQL 如何执行查询以及如何优化查询性能。

通过使用 EXPLAIN 并根据输出进行优化，可以显著提高查询性能。

• 使用索引:

  • 如果 possible_keys 列显示了多个可能的索引，但 key 列显示了没有使用索引，则考虑添加或优化索引。

• 减少全表扫描:

  • 如果 type 列显示为 ALL，则表示 MySQL 正在扫描整个表以找到匹配的行。考虑使用索引以减少扫描范围。

• 优化查询:

  • 如果 Extra 列显示 Using filesort 或 Using temporary，则表示 MySQL 需要额外的操作来排序结果或使用临时表。考虑优化查询或添加合适的索引来减少这类操作。

• 使用覆盖索引:

  • 如果查询只需要索引中的列，则可以创建覆盖索引以避免访问表。