mysql存储引擎详解

一 MySQL组织架构

MySQL数据库系统采用模块化设计，每个模块各司其职，保证了系统的高效运行和可扩展性。通过了解MySQL的组织架构，我们可以更好地理解其工作原理，并能够进行更有效的优化和调试。结合上图进行详细整理如下：

1. 连接层

连接层负责处理客户端的连接请求，包括认证和授权等操作。主要功能包括：

连接管理：管理客户端的连接和断开。通过管理连接，MySQL可以高效地处理并发连接，提供良好的扩展性。
身份验证：验证用户身份，确保只有合法用户可以访问数据库。身份验证通过用户名、密码和权限进行。
连接池：复用连接，减少创建和销毁连接的开销，提高性能。连接池的使用可以显著提高系统的并发处理能力。

支持的连接方式包括：

本地连接：例如Unix Socket和Named Pipe，适用于同一台服务器上的客户端。
网络连接：通过TCP/IP协议实现远程连接，适用于分布式环境。
多种API接口支持：MySQL支持多种编程语言的API接口，包括Native C API, JDBC, ODBC, .NET, PHP, Python, Perl, Ruby, VB等，方便开发者进行应用开发。

2. SQL层

SQL层是MySQL的核心部分，负责处理SQL语句，包括查询解析、优化和执行。主要模块有：

SQL接口：接收用户提交的SQL命令，并返回查询结果。支持多种SQL操作，包括数据操作语言（DML）、数据定义语言（DDL）、存储过程、视图、触发器等。
解析器（Parser）：将SQL语句解析成数据结构，同时进行语法检查和权限验证。解析器将SQL语句转换成内部表示形式，供后续模块处理。
优化器（Optimizer）：根据统计信息和访问路径生成最优的查询计划，提高查询效率。优化器选择最佳的执行路径，减少查询的资源消耗。
缓存与缓冲区（Caches & Buffers）：缓存查询结果和频繁访问的数据，减少磁盘I/O，提高性能。MySQL使用多种缓存和缓冲技术，如查询缓存、InnoDB缓冲池等，来提升查询性能。

3. 存储引擎层

存储引擎层是MySQL最具特色的部分，它将数据存储和管理的功能抽象成插件，可以根据具体需求选择不同的存储引擎。主要包括：

存储引擎定义：存储引擎是数据存储和提取的核心组件，负责表的物理存储结构和数据操作。MySQL允许在同一实例中使用不同的存储引擎，提供了极大的灵活性。
常见存储引擎：

MyISAM：不支持事务和外键，适用于读密集型应用。MyISAM存储引擎的特点是简单高效，适合进行大量读操作的应用场景。
InnoDB：支持事务、外键和行级锁，适用于高并发、事务性应用。InnoDB是默认的存储引擎，提供了强大的事务处理能力和数据一致性保证。
Memory：将数据存储在内存中，适用于临时数据或需要高速访问的数据。Memory存储引擎适合进行快速查询和计算操作，但不适合存储长期数据。
Archive：用于存储大量历史数据，支持高效的插入和压缩。Archive存储引擎适合保存归档数据，提供了较高的压缩比和存储效率。
Federated：允许访问远程MySQL数据库中的表。Federated存储引擎可以将多个MySQL实例中的表联合起来，进行分布式查询。
其他：例如NDB Cluster、Falcon等，根据需求选择。每种存储引擎都有其独特的优点和应用场景，可以根据具体需求进行选择。

4. MySQL查询过程

MySQL的查询过程由多个步骤组成，通过以下几个阶段来处理客户端发送的查询请求。参考上图，详细描述MySQL查询的每个步骤：

客户端发送查询请求

客户端通过通信协议（如TCP/IP）向MySQL服务器发送SQL查询请求。

查询缓存

MySQL首先检查查询缓存。如果查询缓存中存在相同的查询结果，则直接返回缓存中的结果，跳过后续步骤。缓存结果可以显著提高重复查询的性能。

解析器（Parser）

如果查询缓存未命中，MySQL将SQL查询交给解析器进行语法分析和预处理。
语法解析：解析器检查SQL语句的语法是否正确，并将其转换成解析树（Parse Tree）。
预处理器：预处理器进一步检查解析树，确保引用的表和列存在，验证用户权限等操作。

查询优化器（Query Optimizer）

解析树被传递给查询优化器，优化器生成查询执行计划。优化器根据表的统计信息、索引情况和查询条件，选择最优的执行路径。
新解析树：优化后的解析树用于生成执行计划。
执行计划：优化器生成的执行计划描述了查询的具体执行步骤和顺序。

查询执行引擎（Query Execution Engine）

查询执行引擎根据执行计划执行查询。查询执行引擎负责与存储引擎交互，读取和写入数据。
执行引擎通过API接口调用具体的存储引擎（如InnoDB、MyISAM），执行具体的数据操作。

返回结果

查询执行引擎执行完毕后，将结果返回给SQL接口。
如果查询结果需要缓存，则缓存结果，以便下次相同查询时可以直接返回缓存结果，提高性能。
最后，SQL接口将结果返回给客户端，完成整个查询过程。

二、查看存储引擎信息

MySQL支持多种存储引擎，了解和查看当前数据库支持的存储引擎信息是管理数据库的重要步骤。以下是查看MySQL存储引擎信息的方法：

1. 查看MySQL支持的存储引擎

使用SHOW ENGINES命令可以查看MySQL当前支持的所有存储引擎及其状态：

SHOW ENGINES;

执行上述命令后，会显示一个表格，其中包含以下列：

Engine：存储引擎的名称。
Support：是否支持该存储引擎（YES、NO、DEFAULT）。
Comment：对该存储引擎的简短描述。
Transactions：该存储引擎是否支持事务（YES、NO）。
XA：该存储引擎是否支持XA事务（分布式事务）。
Savepoints：该存储引擎是否支持保存点（Savepoints）。

示例如下：

mysql> SHOW ENGINES;
+------------+---------+-----------------------------------+--------------+------+------------+
| Engine     | Support | Comment                           | Transactions | XA   | Savepoints |
+------------+---------+-----------------------------------+--------------+------+------------+
| InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES  | YES  | YES        |
| MyISAM     | YES     | Default engine with support for high-performance indexing | NO   | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO   | NO   | NO         |
| CSV        | YES     | CSV storage engine                | NO           | NO   | NO         |
| ARCHIVE    | YES     | Archive storage engine            | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine    | NO           | NO   | NO         |
| EXAMPLE    | NO      | Example storage engine            | NO           | NO   | NO         |
| NDB        | NO      | Clustered, fault-tolerant, memory-based tables | NO    | NO   | NO         |
| PERFORMANCE_SCHEMA | YES | Performance Schema            | NO           | NO   | NO         |
+------------+---------+-----------------------------------+--------------+------+------------+

2. 查看当前数据库使用的存储引擎

可以使用SHOW TABLE STATUS命令查看当前数据库中各个表使用的存储引擎：

SHOW TABLE STATUS;

示例如下：

mysql> SHOW TABLE STATUS\G;
*************************** 1. row ***************************
           Name: employee
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1000
 Avg_row_length: 83
    Data_length: 81920
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1001
    Create_time: 2023-06-01 10:00:00
    Update_time: 2023-06-20 11:00:00
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 2. row ***************************
           Name: department
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 50
 Avg_row_length: 40
    Data_length: 2048
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2023-06-01 10:05:00
    Update_time: 2023-06-20 11:05:00
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment:

通过SHOW TABLE STATUS命令，可以获得每个表的详细信息，包括表的存储引擎、行格式、行数、数据长度、索引长度、创建时间、更新时间等。

3. 使用`INFORMATION_SCHEMA`查看存储引擎信息

INFORMATION_SCHEMA是MySQL提供的一个数据库，其中包含了关于数据库对象的元数据。可以查询INFORMATION_SCHEMA.ENGINES表来查看存储引擎的信息：

SELECT * FROM INFORMATION_SCHEMA.ENGINES;

示例如下：

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;
+------------+---------+-----------------------------------+--------------+------+------------+
| ENGINE     | SUPPORT | COMMENT                           | TRANSACTIONS | XA   | SAVEPOINTS |
+------------+---------+-----------------------------------+--------------+------+------------+
| InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES  | YES  | YES        |
| MyISAM     | YES     | Default engine with support for high-performance indexing | NO   | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO   | NO   | NO         |
| CSV        | YES     | CSV storage engine                | NO           | NO   | NO         |
| ARCHIVE    | YES     | Archive storage engine            | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine    | NO           | NO   | NO         |
| EXAMPLE    | NO      | Example storage engine            | NO           | NO   | NO         |
| NDB        | NO      | Clustered, fault-tolerant, memory-based tables | NO    | NO   | NO         |
| PERFORMANCE_SCHEMA | YES | Performance Schema            | NO           | NO   | NO         |
+------------+---------+-----------------------------------+--------------+------+------------+

4. 查看使用某个存储引擎的表

通过INFORMATION_SCHEMA.TABLES查询你可以通过查询INFORMATION_SCHEMA.TABLES表来查看使用特定存储引擎的所有表。下面是一个示例查询，显示所有使用InnoDB存储引擎的表：

SELECT TABLE_NAME, TABLE_SCHEMA, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE = 'InnoDB';

如果想查看使用其他存储引擎的表，例如MyISAM，可以将ENGINE的值更改为'MyISAM'：

SELECT TABLE_NAME, TABLE_SCHEMA, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE = 'MyISAM';

上述查询将返回所有使用指定存储引擎的表的名称、所属数据库和存储引擎。

4.1. 示例

假设数据库中有以下几个表：

CREATE DATABASE example_db;
USE example_db;

CREATE TABLE innodb_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE myisam_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MyISAM;

CREATE TABLE memory_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MEMORY;

要查看所有使用InnoDB存储引擎的表：

SELECT TABLE_NAME, TABLE_SCHEMA, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE = 'InnoDB';

查询结果：

+-------------+--------------+--------+
| TABLE_NAME  | TABLE_SCHEMA | ENGINE |
+-------------+--------------+--------+
| innodb_table| example_db   | InnoDB |
+-------------+--------------+--------+

要查看所有使用MyISAM存储引擎的表：

SELECT TABLE_NAME, TABLE_SCHEMA, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE = 'MyISAM';

查询结果：

+--------------+--------------+--------+
| TABLE_NAME   | TABLE_SCHEMA | ENGINE |
+--------------+--------------+--------+
| myisam_table | example_db   | MyISAM |
+--------------+--------------+--------+

要查看所有使用MEMORY存储引擎的表：

SELECT TABLE_NAME, TABLE_SCHEMA, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE = 'MEMORY';

查询结果：

+--------------+--------------+--------+
| TABLE_NAME   | TABLE_SCHEMA | ENGINE |
+--------------+--------------+--------+
| memory_table | example_db   | MEMORY |
+--------------+--------------+--------+

三、修改存储引擎

在MySQL中，可以通过多种方式修改表的存储引擎，包括使用ALTER TABLE语句、配置文件修改、临时修改以及在创建表时指定存储引擎。以下是详细步骤和示例：

1. 使用 `ALTER TABLE` 语句修改存储引擎

要修改表的存储引擎，可以使用以下语法：

ALTER TABLE table_name ENGINE = new_storage_engine;

其中：

table_name 是要修改的表的名称。
new_storage_engine 是要更改为的新存储引擎名称（例如，InnoDB、MyISAM、MEMORY等）。

示例：

ALTER TABLE example_table ENGINE = InnoDB;

2. 通过配置文件修改存储引擎

可以通过修改MySQL的配置文件my.cnf（或my.ini）来更改默认的存储引擎：

打开MySQL配置文件：

sudo nano /etc/mysql/my.cnf

添加或修改default-storage-engine参数：

[mysqld]
default-storage-engine=InnoDB

保存文件并重启MySQL服务：

sudo systemctl restart mysql

或者：

sudo service mysql restart

3. 临时修改存储引擎

可以通过在当前会话中临时修改默认存储引擎：

SET default_storage_engine = InnoDB;

此设置仅对当前会话有效，当会话结束后，默认存储引擎将恢复为配置文件中的设置。

4. 在创建表时指定存储引擎

在创建表时，可以通过ENGINE参数直接指定表的存储引擎：

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

示例：

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

5. 批量修改存储引擎

如果需要批量修改某个数据库中所有表的存储引擎，可以使用脚本或存储过程。以下是一个示例脚本，将某个数据库中所有表的存储引擎修改为InnoDB：

SET @database_name = 'your_database_name';
SET @new_engine = 'InnoDB';

SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=', @new_engine, ';') AS alter_sql
FROM information_schema.tables
WHERE table_schema = @database_name
AND engine <> @new_engine;

生成的输出将是多条ALTER TABLE语句，可以将其复制并执行以批量修改存储引擎。

6. 注意事项

数据备份：在修改存储引擎之前，务必备份数据，以防止意外的数据丢失或损坏。
兼容性：不同的存储引擎支持的功能和特性有所不同（如事务、外键等）。在修改存储引擎之前，确保目标存储引擎支持当前表的所有功能。
性能影响：不同存储引擎的性能特性不同，修改存储引擎可能会影响表的读写性能。

通过以上步骤和注意事项，可以安全地修改MySQL表的存储引擎。

四、不同存储引擎创建数据

不同存储引擎（InnoDB、MyISAM、Memory、Blackhole）如何创建数据，以及各自特点

五、不同存储引擎对比

Mysql不同存储引擎（InnoDB、MyISAM、Memory、Blackhole）的区别

六、InnoDB 逻辑架构

InnoDB 存储引擎是 MySQL 中常用的事务型存储引擎，具有高可靠性和高性能。其逻辑架构可以通过内存结构、操作系统缓存以及硬盘结构来详细理解。

一、内存架构

1. Buffer Pool
Buffer Pool 是 InnoDB 用于缓存数据和索引的主要内存区域。它主要包含以下几个部分：

数据页和索引页：InnoDB 将磁盘上的数据页和索引页缓存到 Buffer Pool 中，以提高读取和写入的性能。
Adaptive Hash Index (AHI)：自适应哈希索引是 InnoDB 在内存中动态创建的，用于加速索引查找。
Change Buffer：用于缓存对二级索引的修改操作（写操作），这些修改将在合适的时机被合并到磁盘上，以提高写入性能。

2. Log Buffer
Log Buffer 是一个内存区域，用于缓存事务日志（Redo Log）的记录。在事务提交时，日志先写入 Log Buffer，然后周期性地刷新到磁盘上的 Redo Log 文件中。

二、操作系统缓存

操作系统缓存是指操作系统用于缓存磁盘 I/O 操作的数据区域。InnoDB 利用操作系统缓存来进一步提升 IO 性能。

1. Write 操作

当 InnoDB 需要将数据从内存写入磁盘时，首先通过操作系统的 write() 系统调用将数据写入操作系统缓存。这一操作在大多数情况下是快速的，因为它只是将数据写入内存，而不是直接写入磁盘。

2. fsync 操作

操作系统缓存的数据并不会立即写入磁盘，这样做是为了提高写入性能。然而，为了确保数据的一致性和持久性，InnoDB 需要定期将缓存中的数据同步到磁盘。这一操作通过 fsync() 系统调用实现。
fsync() 调用会将操作系统缓存中的数据强制写入磁盘，确保数据在系统崩溃时不会丢失。InnoDB 在事务提交时会调用 fsync() 以保证事务日志（Redo Log）的持久性。

3. O_DIRECT 模式

InnoDB 可以配置使用 O_DIRECT 模式，这种模式下，数据在写入磁盘时会绕过操作系统缓存，直接写入磁盘。这可以避免双重缓存带来的性能开销，提高 I/O 性能。
使用 O_DIRECT 需要在 MySQL 配置文件中设置 innodb_flush_method 参数为 O_DIRECT。

三、硬盘架构

1. On-Disk Structures

硬盘上的结构是 InnoDB 存储的核心，主要包括以下几个部分：

System Tablespace (ibdata1)：系统表空间，用于存储 InnoDB 的数据字典、双写缓冲（Doublewrite Buffer）、更改缓冲（Change Buffer）等系统信息。
File-Per-Table Tablespaces：当参数 innodb_file_per_table 设置为 ON 时，每个表的数据和索引存储在独立的表空间文件中（.ibd 文件）。
General Tablespaces：可以跨多个表的通用表空间文件。
Undo Tablespaces：用于存储撤销日志（Undo Log），以支持事务的回滚和 MVCC（多版本并发控制）。系统表空间中包含系统撤销表空间（undo_002），也可以有用户定义的撤销表空间（如 undo_004）。
Temporary Tablespaces：临时表空间，用于存储临时表的数据。

2. Redo Log

Redo Log 是 InnoDB 用于恢复事务的日志文件。主要包括两个文件（ib_logfile1 和 ib_logfile2），记录了对数据库的所有修改，以便在系统崩溃后进行恢复。

结合查询过程理解

客户端请求

客户端发送 SQL 查询请求，通过连接层进入 MySQL 服务器。

查询缓存

查询首先检查查询缓存，如果命中缓存，则直接返回结果。

解析器

查询解析器对 SQL 语句进行语法解析，生成解析树。

预处理器

预处理器对解析树进行进一步处理，生成新解析树。

查询优化器

优化器生成执行计划，并选择最优的执行路径。

执行器

执行器按照执行计划从存储引擎读取数据，通过 API 调用访问存储引擎。

存储引擎

存储引擎在 Buffer Pool 中查找数据页，如果没有找到则从磁盘中读取。
数据页在内存中进行处理（包括可能的日志记录），最后返回结果给客户端。

七、InnoDB 存储引擎的执行过程

通过图示可以看出 InnoDB 存储引擎在执行写操作时的详细过程。下面将逐步解释这些过程：

执行器接收到 SQL 语句后，首先进行解析和优化，生成执行计划。执行计划决定要修改的数据页。

1. 加载数据到缓冲池：

当有一条 SQL 语句需要修改数据时，首先执行器将需要修改的数据页从磁盘加载到内存中的缓冲池（Buffer Pool）中。如果数据页已经在缓冲池中，则跳过这一步。

2. 记录 Undo 日志：

在对数据进行任何修改之前，为了支持事务的回滚，需要首先将数据修改前的旧值写入 Undo 日志文件。Undo 日志用于在事务回滚时恢复数据的旧值。

3. 更新内存数据：

在缓冲池中进行数据页的修改。此时，内存中的数据页已经被修改，但还没有被持久化到磁盘上。

4. 写入 Redo 日志：

将数据修改操作记录到 Redo 日志缓冲区中。Redo 日志记录了事务的每一个修改操作，用于在系统崩溃时进行数据恢复。Redo 日志包含了修改操作的详细信息，例如修改了哪个数据页、具体的修改内容等。

5. 刷新 Redo 日志到磁盘：

将 Redo 日志缓冲区的内容刷新到磁盘上的 Redo 日志文件中。确保即使在系统崩溃的情况下，也能通过 Redo 日志恢复到最新的状态。通常是通过 fsync 操作将缓冲区内容持久化到磁盘。

6. 准备提交事务：

在提交事务之前，首先将 Binlog 日志写入磁盘。Binlog 日志记录了所有的写操作，用于主从复制和数据恢复。此时，Binlog 日志中的内容还没有标记为已提交状态。

7. 提交事务：

确保 Redo 日志和 Binlog 日志都已成功写入磁盘后，在 Binlog 日志中写入一个 commit 标记，标记事务已经提交。并将事务标记为已提交状态，事务正式提交。

8. 刷新数据到磁盘：

事务提交后，InnoDB 会异步地将缓冲池中已修改的数据页刷新到磁盘上的数据文件中。这个过程可能会在稍后的时间进行，不会阻塞事务提交。这样做的目的是提高性能，通过减少频繁的磁盘 I/O 操作。

当然，以下是各组件的详细简介：

各组件简介

8.1. Buffer Pool（缓冲池）：

功能：Buffer Pool 是 InnoDB 存储引擎中用来缓存磁盘上的数据页的内存区域。
作用：通过将数据页缓存在内存中，可以减少磁盘 I/O 操作，提高数据访问速度。Buffer Pool 还用于缓存索引页，提高查询效率。
组成：包括数据页和索引页，以及一些辅助的数据结构如 Adaptive Hash Index 用于加速查询。

8.2. Undo Log（撤销日志）：

功能：Undo Log 记录了数据被修改之前的旧值。
作用：Undo Log 用于支持事务回滚和多版本并发控制（MVCC）。在事务回滚时，InnoDB 会使用 Undo Log 将数据恢复到修改之前的状态。同时，Undo Log 还支持快照读，使得读操作可以看到事务开始时的数据状态。
组成：Undo 日志文件记录了数据修改之前的旧值，可以在事务回滚或 MVCC 读取时使用。

8.3. Redo Log（重做日志）：

功能：Redo Log 记录了所有的数据修改操作。
作用：Redo Log 用于在系统崩溃后进行数据恢复。Redo Log 确保即使在系统崩溃后，也可以通过重做日志将数据恢复到最新的状态。写入 Redo Log 后，事务可以被认为已经提交。
组成：Redo Log 包括 Redo Log Buffer（内存中的重做日志缓冲区）和磁盘上的 Redo 日志文件（如 ib_logfile0, ib_logfile1）。

8.4. Binlog（二进制日志）：

功能：Binlog 记录了所有对数据库的写操作。
作用：Binlog 用于主从复制和数据恢复。通过 Binlog，可以将主库上的数据修改操作重放到从库上，从而实现主从数据一致。Binlog 也用于 Point-in-Time Recovery（PITR），即从备份恢复后，通过重放 Binlog 日志将数据库恢复到特定时间点。
组成：Binlog 包含所有事务的提交信息，记录了数据的逻辑修改操作。

8.5. 磁盘文件：

功能：存储最终的数据页。
作用：磁盘文件是持久化存储的地方，Buffer Pool 中的数据页定期刷新到磁盘文件，以确保数据的一致性和持久性。数据最终存储在磁盘文件中，以确保在系统重启后数据依然存在。
组成：包括 InnoDB 表空间文件（如 .ibd 文件），以及系统表空间文件（如 ibdata1）。

八、InnoDB的刷盘策略

InnoDB的刷盘策略主要是为了保证数据的一致性、持久性，并提高系统的性能。刷盘策略包括将内存中的数据页、Redo Log、Undo Log 刷新到磁盘的机制。以下是详细的介绍：

1. Buffer Pool 刷新策略

Buffer Pool 中缓存了数据页和索引页，InnoDB 会根据以下策略将这些页刷新到磁盘：

LRU（Least Recently Used）算法：InnoDB 使用 LRU 算法管理 Buffer Pool 中的数据页。当 Buffer Pool 空间不足时，会将最近最少使用的数据页移出 Buffer Pool，并将其刷新到磁盘。
脏页刷盘：当 Buffer Pool 中的页被修改后，这些页被标记为脏页。InnoDB 会根据一定的策略将脏页刷新到磁盘，以确保数据的一致性和持久性。
Checkpoints：InnoDB 使用检查点机制定期将 Buffer Pool 中的脏页刷新到磁盘，减少恢复时间。检查点可以分为两种：小检查点和大检查点。小检查点频繁发生，写入较少的数据；大检查点较少发生，写入大量的数据。
后台线程：InnoDB 有后台线程（如 Page Cleaner 线程）负责定期将脏页刷新到磁盘，这些线程会在系统负载较低时执行刷盘操作，以减少对前台操作的影响。

2. Redo Log 刷新策略

innodb_flush_log_at_trx_commit 参数控制 InnoDB 如何处理事务提交时的日志刷新策略，它的值可以是 0、1 或 2。下面详细说明这三个选项的行为：

2.1. `innodb_flush_log_at_trx_commit = 0`

行为：

事务提交时，InnoDB 仅将日志缓冲区中的日志写入到日志文件（ib_logfile*）中，但不调用 fsync 将日志文件刷新到磁盘。
每秒钟，InnoDB 会将日志缓冲区中的日志写入到日志文件中，并调用 fsync 将日志文件刷新到磁盘。
影响：

性能：这种配置可以提高性能，因为减少了磁盘 I/O 操作。
数据安全性：在 操作系统崩溃时或MySQL 服务崩溃时，可能会丢失最近一秒内提交的事务，因为这些事务的日志可能还没有被写入磁盘。

2.2. `innodb_flush_log_at_trx_commit = 1`

行为：
事务提交时，InnoDB 会将日志缓冲区中的日志写入到日志文件，并调用 fsync 将日志文件刷新到磁盘。
影响：

性能：每次事务提交都会导致一次磁盘 I/O 操作，性能相对较低。
数据安全性：这种配置可以提供最高的数据安全性，因为每次事务提交后的日志都会立即写入磁盘，即使系统崩溃也不会丢失已提交的事务。

2.3. `innodb_flush_log_at_trx_commit = 2`

行为：

事务提交时，InnoDB 会将日志缓冲区中的日志写入到日志文件，但不调用 fsync 将日志文件刷新到磁盘。
每秒钟，InnoDB 会调用 fsync 将日志文件刷新到磁盘。
影响：

性能：这种配置性能较好，因为减少了 fsync 操作的频率。
数据安全性：在操作系统崩溃时，可能会丢失最近一秒内提交的事务，但在 MySQL 崩溃时，已经提交的事务不会丢失。

3. Undo Log 刷新策略

Undo Log 用于支持事务回滚和 MVCC。InnoDB 会根据以下策略刷新 Undo Log：

事务提交时：在事务提交时，InnoDB 会将 Undo Log 记录刷新到磁盘，确保回滚信息的持久性。
脏页刷盘时：在将 Buffer Pool 中的脏页刷新到磁盘之前，会先将相关的 Undo Log 刷新到磁盘，以确保在系统崩溃时可以正确回滚未提交的事务。

4. 双写缓冲区（Doublewrite Buffer）

双写缓冲区是 InnoDB 为了防止写入过程中的部分写入（partial writes）导致数据页损坏而引入的机制：

写入策略：当 InnoDB 刷新脏页到磁盘时，会先将这些页写入双写缓冲区（内存中的一块连续空间），然后将双写缓冲区中的页写入磁盘上的连续空间。确保数据页的完整性。
数据恢复：在系统崩溃恢复时，如果检测到数据页损坏，InnoDB 可以使用双写缓冲区中的数据页进行恢复，确保数据的一致性。

5. fsync和write操作

write 操作：将数据从内存写入到操作系统的缓存中，但数据还没有真正写入到磁盘。
fsync 操作：强制将操作系统缓存中的数据刷入到磁盘，确保数据的持久性。InnoDB 使用 fsync 来保证事务日志和数据页在刷盘时被真正写入磁盘。

在MySQL中，redo log刷盘策略和binlog刷盘策略的设置对于保证数据的一致性和性能优化至关重要。下面是详细的设置说明：

6. 配置Redo Log和Binlog刷盘策略

配置redo log和Binlog的刷盘策略

九、InnoDB的表空间详解

InnoDB存储引擎表空间

一 MySQL组织架构

1. 连接层

2. SQL层

3. 存储引擎层

4. MySQL查询过程

二、查看存储引擎信息

1. 查看MySQL支持的存储引擎

2. 查看当前数据库使用的存储引擎

3. 使用INFORMATION_SCHEMA查看存储引擎信息

4. 查看使用某个存储引擎的表

4.1. 示例

三、修改存储引擎

1. 使用 ALTER TABLE 语句修改存储引擎

2. 通过配置文件修改存储引擎

3. 临时修改存储引擎

4. 在创建表时指定存储引擎

5. 批量修改存储引擎

6. 注意事项

四、不同存储引擎创建数据

五、不同存储引擎对比

六、InnoDB 逻辑架构

一、内存架构

二、操作系统缓存

1. Write 操作

2. fsync 操作

3. O_DIRECT 模式

三、硬盘架构

1. On-Disk Structures

2. Redo Log

结合查询过程理解

七、InnoDB 存储引擎的执行过程

1. 加载数据到缓冲池：

2. 记录 Undo 日志：

3. 更新内存数据：

4. 写入 Redo 日志：

5. 刷新 Redo 日志到磁盘：

6. 准备提交事务：

7. 提交事务：

8. 刷新数据到磁盘：

各组件简介

8.1. Buffer Pool（缓冲池）：

8.2. Undo Log（撤销日志）：

8.3. Redo Log（重做日志）：

8.4. Binlog（二进制日志）：

8.5. 磁盘文件：

八、InnoDB的刷盘策略

1. Buffer Pool 刷新策略

2. Redo Log 刷新策略

2.1. innodb_flush_log_at_trx_commit = 0

2.2. innodb_flush_log_at_trx_commit = 1

2.3. innodb_flush_log_at_trx_commit = 2

3. Undo Log 刷新策略

4. 双写缓冲区（Doublewrite Buffer）

5. fsync和write操作

6. 配置Redo Log和Binlog刷盘策略

九、InnoDB的表空间详解

发送评论 编辑评论

3. 使用`INFORMATION_SCHEMA`查看存储引擎信息

1. 使用 `ALTER TABLE` 语句修改存储引擎

2.1. `innodb_flush_log_at_trx_commit = 0`

2.2. `innodb_flush_log_at_trx_commit = 1`

2.3. `innodb_flush_log_at_trx_commit = 2`

发送评论编辑评论