Mysql复习笔记: 基础概念(待补充)

一. 基础概念

在这里插入图片描述

通用概念:

网络连接必须得分配给一个线程去进行处理，由一个线程来监听请求以及读取请求数据，比如从网络连接中读取和解析出来一条我们的系统发送过去的SQL语句
在数据库中，哪怕执行一条SQL语句，其实也可以是一个独立的事务，只有当你提交事务之后，SQL语句才算执行结束。

SQL 解析器

所谓的SQL解析，就是按照既定的SQL语法，对我们按照SQL语法规则编写的SQL语句进行解析，然后理解这个SQL语句要干什么事情

查询优化器

针对编写的SQL语句生成查询路径树，然后从里面选择一条最优的查询路径出来。按照一个什么样的步骤和顺序，去执行哪些操作去完成SQL操作

存储引擎了

存储引擎其实就是执行SQL语句的，他会按照一定的步骤去查询内存缓存数据，更新磁盘数据，查询磁盘数据等等…
常用存储引擎: InnoDB、MyISAM、Memory等等

执行器

执行器就会去根据我们的优化器生成的一套执行计划，然后不停的调用存储引擎的各种接口去完成SQL语句的执行计划

InnoDB的重要内存结构：缓冲池

作用: 缓存很多的数据，以便于以后在查询的时候，内存缓冲池里有数据，就可以不用去查磁盘了

示例:
InnoDB存储引擎要执行更新语句的时候，比如对“id=10”这一行数据，他其实会先将“id=10”这一行数据看看是否在缓冲池里，如果不在的话，那么会直接从磁盘里加载到缓冲池里来，而且接着还会对这行记录加独占锁。

undo日志文件


在MySQL中，"undo 文件"通常是指存储在InnoDB存储引擎中的撤销日志文件。Undo日志是InnoDB事务性存储引擎的一个核心特性，它用于在事务处理中维持ACID属性（原子性、一致性、隔离性、持久性）的一致性和数据完整性。简单来说，Undo日志记录了事务开始之前的数据状态，以便在事务失败或被撤销时可以将数据回滚到原始状态。Undo日志的作用
数据恢复：在事务失败或被撤销时，可以利用Undo日志将数据恢复到事务开始之前的状态。
MVCC（多版本并发控制）：Undo日志支持MVCC，允许在不同事务中查看数据的旧版本，从而提高并发访问性能。
保证事务的ACID属性：通过撤销未完成的事务更改，Undo日志帮助维持数据库的一致性和完整性。
Undo日志的存储
在MySQL中，Undo日志的存储方式有两种：内部Undo日志空间：在MySQL 5.7及之前版本，默认Undo日志是存储在系统表空间中的。
独立的Undo表空间文件：从MySQL 8.0开始，支持将Undo日志存储在独立的Undo表空间文件中，这些文件通常位于数据目录下，并以.ibd扩展名结尾。这样做的好处包括改善I/O性能、便于管理等。
配置Undo表空间
在MySQL 8.0及更高版本中，可以配置Undo表空间的数量和位置，一些相关的系统变量如下：innodb_undo_tablespaces：用于设置独立Undo表空间的数量。
innodb_undo_directory：指定存放Undo表空间文件的目录。
MySQL通过优化Undo日志的存储和管理，提高了数据库的性能和可靠性。当需要处理大量事务时，合理配置Undo日志对于维持数据库的高效运作非常关键。

redo日志

在MySQL中，Redo日志是InnoDB存储引擎的一个重要组成部分，其主要目的是保证数据库的持久性和恢复能力。Redo日志主要用于记录那些已经提交到数据库但可能还没有被写入到磁盘中的物理数据页的修改操作。在数据库发生故障时，Redo日志可以用于重做这些操作，以确保数据的一致性和完整性。### Redo日志的作用1. **数据恢复**：在系统发生崩溃或宕机时，Redo日志可以用于恢复未来得及写入磁盘的数据，确保事务的持久性。
2. **提高性能**：通过将短期内的多次数据修改先记录在Redo日志中，然后再统一写入磁盘，可以减少磁盘I/O操作，从而提高数据库性能。### Redo日志的工作机制1. **写前日志**（Write-Ahead Logging, WAL）：在数据实际被写入磁盘之前，所有的修改操作都会先被记录到Redo日志中。这就是WAL的策略，确保了在发生故障时可以通过Redo日志重做（redo）这些操作，恢复到故障发生前的状态。
2. **日志缓冲**（Log Buffer）：修改操作首先被写入到内存中的日志缓冲区。之后，这些修改会按照一定的策略（如定时、事务提交时、日志缓冲区满时）被刷新到磁盘上的Redo日志文件中。
3. **日志刷新**：刷新操作确保了即使在突发的系统崩溃情况下，内存中的事务修改也能够通过Redo日志被恢复。### Redo日志的配置在MySQL中，可以通过一些配置项来管理Redo日志的行为和性能，包括：
- `innodb_log_file_size`：设置Redo日志文件的大小。
- `innodb_log_buffer_size`：设置日志缓冲区的大小。
- `innodb_flush_log_at_trx_commit`：设置事务提交时Redo日志的刷新行为。合理配置Redo日志对于优化InnoDB的性能和确保数据安全至关重要。通过调整Redo日志的大小和刷新策略，可以在系统资源使用和事务安全性之间找到适当的平衡点。

redo日志写入磁盘配置 : innodb_flush_log_at_trx_commit

在MySQL中，要查看innodb_flush_log_at_trx_commit这个系统变量的当前设置，你可以使用以下SQL命令：

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

这条命令会返回innodb_flush_log_at_trx_commit的当前值。innodb_flush_log_at_trx_commit是一个重要的系统变量，用来定义InnoDB存储引擎在事务提交时如何刷新（写入并同步）事务日志到磁盘上。它可以有以下几个值：

当innodb_flush_log_at_trx_commit=1时，InnoDB将在每次事务提交时将log buffer的数据更新到文件系统os buffer中，并调用文件系统的flush操作将数据缓存更新至磁盘中。此种方式下，数据库完全遵守ACID特性，安全性较高。
当innodb_flush_log_at_trx_commit=2时，InnoDB将在每次事务提交时将log buffer中的数据更新到文件系统缓存中，每秒钟将文件系统缓存中的数据更新到磁盘一次，该操作由操作系统调度。因为DDL变更或其他InnoDB内部原因会导致更新磁盘的操作独立于innodb_flush_log_at_trx_commit参数设置，不能完全保证每秒更新磁盘一次，没有被更新到磁盘中的事务可能会因宕机而丢失。
当innodb_flush_log_at_trx_commit=0时，InnoDB会每秒钟将log buffer中的数据更新到磁盘中。因为DDL变更或其他InnoDB内部原因会导致更新磁盘的操作独立于innodb_flush_log_at_trx_commit参数设置，并不能完全保证每秒将数据更新到磁盘一次。因此，在实例崩溃恢复场景中，可能会出现丢失1秒钟的事务。

选择哪种方式取决于你对数据安全性和性能的权衡。对于需要高数据一致性的应用，推荐使用1；而对于追求最高性能，可以接受极端情况下少量数据丢失的场景，可以选择0或2。

通过了解和设置innodb_flush_log_at_trx_commit的值，你可以根据系统的需要来优化MySQL数据库的性能和可靠性。

参考: https://help.aliyun.com/zh/rds/apsaradb-rds-for-mysql/innodb-flush-log-at-trx-commit

binlog

binlog不是InnoDB存储引擎特有的日志文件，是属于mysql server自己的日志文件

提交事务的时候，同时会写入binlog

当我们把binlog写入磁盘文件之后，接着就会完成最终的事务提交，此时会把本次更新对应的binlog文件名称和这次更新的binlog日志在文件里的位置，都写入到redo log日志文件里去，同时在redo log日志文件里写入一个commit标记。

Binary Log（简称binlog）是MySQL数据库中的一个重要组成部分，它记录了所有对数据库修改的操作，包括表的结构变化（DDL语句如CREATE、ALTER、DROP等）以及数据的变更（DML语句如INSERT、UPDATE、DELETE等），但不记录SELECT和SHOW这类只读操作。这些记录是以“事件”的形式存在，按照它们发生的顺序进行记录。

binlog的主要用途

数据复制：binlog是MySQL数据库复制的基础。在主从复制架构中，主服务器的binlog会被从服务器读取并重放，以此来同步主服务器上的数据更改，实现数据的一致性。
数据恢复：利用binlog可以进行点时间恢复（Point-in-Time Recovery, PITR）。如果数据库发生意外，可以通过回滚到特定时间点前的状态来恢复数据，前提是你有备份和相应时间段的binlog。
审计：binlog也可用于审计和分析，通过查看binlog内容，可以知道在特定时间段内对数据库做了哪些修改。

binlog的格式

MySQL支持三种不同的binlog格式：

STATEMENT：基于SQL语句的复制。记录执行的每个SQL语句。这种方式可能在某些特定的操作下（如使用了非确定性函数的语句）不能准确复制数据到从服务器。
ROW：基于行的复制。记录被修改行的内容变化。这种格式记录的信息更为详细，准确性更高，但会产生更多的日志数据，占用更多的磁盘空间。
MIXED：混合模式，默认情况下，MySQL会使用STATEMENT格式记录日志，但在某些情况下（当STATEMENT格式可能导致数据不一致时），会自动切换到ROW格式。

配置binlog

在MySQL服务器的配置文件（通常是my.cnf或my.ini）中，可以通过设置log_bin选项来启用binlog。

例如：

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log

此外，还可以配置binlog的格式（binlog_format）、过期时间（expire_logs_days）等选项来满足不同的需要。

查看binlog内容

MySQL提供了mysqlbinlog工具来查看和处理binlog文件。你可以使用这个工具来解析binlog文件的内容，或将其转换为SQL语句。

mysqlbinlog /path/to/binlog-file

binlog如何过滤数据库

在MySQL中，Binary Log（binlog）记录了数据库更改操作的所有细节，对于实现数据复制、恢复以及审计等功能至关重要。尽管binlog记录了所有数据库的活动，但在实际应用中，我们有时候需要针对特定数据库（db）的活动进行过滤或者分析，这就需要我们能够区分不同数据库的binlog记录。

指定数据库的binlog记录

当你需要开启binlog功能并指定只针对特定的数据库进行记录时，可以通过在MySQL的配置文件（通常是my.cnf或my.ini）中设置binlog-do-db来实现。例如，如果你只想记录数据库mydb的更改操作，可以这样设置：

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = mydb

请注意，这种方法有其局限性：

如果事务中涉及多个数据库，但当前默认数据库是binlog-do-db中指定的，那么整个事务的更改都会被记录下来，即使事务中有些更改是针对未在binlog-do-db中指定的数据库。
使用binlog-do-db时，需要确保应用在执行更改前正确地选择了数据库（即执行了USE database_name语句）。
使用mysqlbinlog工具过滤数据库

如果你已经有了一个包含多个数据库活动的binlog文件，而现在需要筛选出特定数据库的活动，可以使用mysqlbinlog工具的数据库过滤功能。mysqlbinlog命令提供了--database（或-d）选项，允许你仅显示指定数据库的binlog事件。例如：

mysqlbinlog --database=mydb /path/to/mysql-bin.000001

这个命令将只显示日志文件/path/to/mysql-bin.000001中mydb数据库的事件。

注意事项
当你需要严格地按数据库过滤binlog时，应细致地规划数据库操作和事务管理，以确保binlog准确地反映了所需数据库的变更。
也可以使用binlog-ignore-db来指定哪些数据库的更改不应该被记录到binlog中，这是另一种通过排除法来间接指定记录的数据库的方法。

总的来说，虽然MySQL的binlog记录了所有数据库的变更操作，但通过合理配置和使用工具，我们可以实现对特定数据库更改操作的跟踪和分析。这在数据库管理、故障恢复和数据复制等方面提供了很大的灵活性和便利。

binlog 参数 sync_binlog 默认值

sync_binlog是MySQL Binlog日志的重要参数，用于控制Binlog的更新策略，通过对该参数的调优，可以提升数据库的性能和数据安全性：

MySQL 5.6及之前的版本中，sync_binlog的默认值是0
MySQL 5.7开始，sync_binlog的默认值被改变为1

当sync_binlog=1时，MySQL会在每次事务提交后，将Log Buffer中的数据更新到磁盘上，此时MySQL安全性较高，但是IO消耗也较高。

当sync_binlog=0时，MySQL会在每次事务提交后将binlog_cache中的数据更新至文件系统缓冲区，但不会进行持久化，而是依赖操作系统来调度数据刷入磁盘。

当sync_binlog=N时，MySQL会在每N组事务提交后将数据更新到磁盘中。通过这种方式，可以在一定程度上平衡MySQL的性能和数据的安全性。如果N设置得比较大，可以提高系统的性能，但会降低数据的安全性。