深入浅出MySQL – 架构与执行

Posted on by hackdl

Photo by Merilin Kirsika Tedder from Pexels

一、MySQL的逻辑架构

MySQL作为一个流行的开源关系型数据库管理系统，它可以运行在多种平台上，支持多种存储引擎，提供了灵活的数据操作和管理功能。MySQL的逻辑架构可以分为三层：连接层、服务层和引擎层，下方是网上流传度很广的一张架构图。

需要注意的是, 上图描述的是MySQL5.7及以前的逻辑架构，MySQL8.0中正式移除了查询缓存组件, 因为从收集的数据来看查询缓存的命中率很低，即使是在MySQL5.7中查询缓存这个选项也是默认关闭的，所以本篇文章就不对缓存这款内容做解析了。具体可以查看官方的一篇博客：

MySQL ：： MySQL 8.0：停用对查询缓存的支持

事实上，如果不去关注其内部的细节，《高性能MySQL》一书中的这张简图也足够让我们对其逻辑架构有一个直观的认知：

1、连接层详解

当客户端发送连接请求时，MySQL服务器会在连接层接收请求，分配一个线程来处理该连接，随后进行身份验证。具体的功能如下：

  • 客户端连接的建立与处理：当客户端发起连接请求时，MySQL会创建一个专用的线程（以操作系统级别的线程实现）来为该客户端服务。这些服务线程使用线程池里的长连接服务多个用户请求，减少了线程切换的开销。

  • 安全认证：安全认证是连接层的另一项重要任务。当客户端连接到MySQL服务器时，服务器首先需要验证客户端的身份。MySQL使用基于用户名、主机和密码的认证方式。在连接时，客户端需要提供有效的用户名、主机名和密码，服务器会根据在”mysql.user”表中的数据进行验证，若通过，则建立连接。

  • 连接资源管理：MySQL支持可配置的最大连接数。当到达最大连接数时，新的连接请求会被拒绝。符合条件的客户端可以设置连接超时时间、客户端闲置关闭时间等参数。同时，可以通过”mysql.user”表配置特定用户对于数据库的操作权限。

  • 线程管理：MySQL会自动创建和管理连接线程，其中包括以线程数作为上限的线程池。线程池的目的是复用连接线程，避免了线程切换和创建的开销。此外，MySQL使用异步I/O机制和协程，尽可能提高了并发和吞吐量。

2、服务层详解

服务层是MySQL中的核心组件，负责提供各种数据库操作所需的基本功能，如SQL语法处理、事务管理、锁管理等。

SQL语法处理

服务层负责从客户端接收来自连接层的SQL查询请求，并进行初始分析、解析和预处理。

  1. 查询缓存（MySQL8.0 中不存在）：MySQL会将查询语句和其结果缓存在内存中。当收到一个相同的查询请求时，先检查缓存中是否有匹配的结果。如果有匹配结果，则直接返回，并跳过剩余的处理步骤。如果没有匹配，将继续执行下一个步骤。
  2. SQL解析器：服务层的SQL解析器主要进行语法解析。解析器会根据MySQL词法分析器和语法分析器的解析规则，将查询语句解析成一个字符串表示的树状结构，用于存储语法单位（词素）及它们之间的关系。
  3. SQL预处理：在构建完成解析树后，预处理模块对解析树进行优化和处理。这包括检查权限、完整性约束、函数调用和数据类型等。在预处理阶段，还可能对查询进行改写，例如将”UNION”操作转换为”JOIN”操作，或者将子查询转换为连接操作。

事务管理

MySQL的服务层负责事务管理，确保在执行一系列操作时，满足原子性、一致性、隔离性和持久性这四个特性。事务管理涉及的主要功能包括：

  1. 事务隔离级别：MySQL支持四个事务隔离级别：读未提交、读已提交、可重复读和串行化。这些隔离级别分别定义了事务间数据访问的隔离程度，用于防止脏读、不可重复读和幻读。
  2. 锁管理：在事务过程中，可能需要对数据加锁，以确保数据的一致性。MySQL支持的锁类型包括共享锁、排它锁、意向锁、行锁、表锁等。
  3. Undo日志：服务层通过Undo日志实现了事务回滚操作，当事务执行中途出现异常或用户发出回滚请求时，可以通过Undo日志回滚数据到事务开始前的状态。
  4. Redo日志：为了保证事务的持久性，在事务执行过程中，修改的数据首先写入到Redo日志中，再更新到磁盘文件上。在系统恢复过程中，可以通过Redo日志进行数据恢复。

缓存管理

MySQL优化器使用缓存来提高查询速度，包括：

  1. 查询缓存：当相同的SQL查询被多次执行时，可以从查询缓存中直接获取结果，提高性能。由于MySQL 8.0中已移除了查询缓存功能，使用者需要自行实现相关功能，如使用Redis、Memcached等中间缓存系统。
  2. 表缓存：用于存储表的元数据，如表的结构定义。当查询需要表信息时，优先从表缓存中获取，避免磁盘操作。
  3. 线程缓存：用于复用服务器的连接线程。当一个连接关闭后，它的线程会被放回线程缓存池中，供新的连接使用。线程池意味着减少了创建和销毁线程的开销。
  4. 缓冲池：主要用于InnoDB存储引擎，缓冲池管理缓存的数据页，包括数据和索引。当需要访问这些页时，可以直接从缓冲池读取，提高访问速度。

3、引擎层详解

引擎层负责存储数据和执行SQL语句。MySQL支持多种存储引擎，每种引擎各有特点，根据实际需求进行选用。当然，只要没有非常明确的特殊需求就不需要更改存储引擎，因为InnoDB在大部分场景下都比其他引擎更加适用。引擎层通过标准API与服务层交互，实现数据的存储和查询。

  1. InnoDB：InnoDB是MySQL的默认存储引擎，提供了事务支持、行级锁定、外键约束等功能，主要用于高并发、高可靠性的OLTP场景。
  2. MyISAM：MyISAM通常用于只读数据表，适用于简单查询和全文索引。其不支持事务、行级锁等功能，适用于OLAP场景。
  3. Memory：Memory存储引擎支持哈希和B树索引，它将数据存储在内存中，易受到系统断电或宕机等影响，具有较高的写性能但不适用于大规模数据分布。
  4. 其他存储引擎：MySQL还支持如Archive、NDB Cluster等其他存储引擎，它们分别适用于存档表、分布式数据库等不同场景。

我们可以在SQL命令行中执行 show engines; 来查看当前支持的存储引擎：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

二、MySQL语句执行流程

SQL语句的执行流程可以简单分为以下几个步骤：

  1. 连接器：客户端连接到MySQL服务器，连接器负责验证客户端的身份和权限，如果通过验证，就建立一个连接，并从权限表中读取该用户的所有权限信息。
  2. 语法分析：在执行SQL语句之前，MySQL需要对SQL语句进行语法分析。语法分析器会检查SQL语句是否符合语法规则，并将其转换为一棵语法树。如果SQL语句不符合语法规则，MySQL将会返回一个错误消息。语义分析是在语法分析之后进行的。它会检查SQL语句是否符合语义规则，并将其转换为一棵语义树。语义分析器会检查表名、列名、数据类型等信息是否正确。如果SQL语句不符合语义规则，MySQL将会返回一个错误消息。
  3. 查询优化：在语义分析之后，MySQL会对SQL语句进行查询优化。查询优化器会分析查询语句，并生成一个最优的执行计划。执行计划是指MySQL执行SQL语句的具体步骤，包括使用哪些索引、如何连接表等。
  4. 执行SQL语句：在查询优化之后，MySQL会执行SQL语句。执行器会按照执行计划的步骤，逐步执行SQL语句。执行器会根据查询语句，从磁盘读取数据，并将其存储在内存中。然后，执行器会对数据进行排序、分组、聚合等操作，最终生成查询结果。

另外请注意，本篇文章依旧是在为后续写MySQL优化流程做知识上的铺垫，所以一些细节会简单介绍，但实际的SQL优化思想会等到后面的文章再详细介绍。

下面我们来详细解释一下SQL语句的执行流程和细节。

1. 语法分析

语法分析是MySQL执行SQL语句的第一步。语法分析器会对SQL语句进行分析，检查其是否符合语法规则。如果SQL语句不符合语法规则，MySQL将会返回一个错误消息。详细的来说又可分为以下几步：

  • 词法分析：主要负责从SQL语句中提取关键字，比如：查询的表，字段名，查询条件等等。词法分析器会将SQL语句分割成一个个的词法单元（token），并为每个token赋予一个类型（type）和值（value）。
  • 语法规则：主要判断SQL语句是否合乎MySQL的语法。语法规则模块会使用yacc工具生成的语法分析器，根据MySQL的语法规则（grammar rule）来检查词法单元是否符合语法要求。
  • 语义分析：主要负责检查SQL语句的语义是否正确，比如：表名和字段名是否存在，数据类型是否匹配，函数是否合法等。语义分析器会根据数据字典（data dictionary）和目录（catalog）来验证SQL语句的有效性。

MySQL使用的语法分析器是Bison。它是一种自动生成解析器的工具，可以根据语法规则自动生成语法分析器。下面是一个示例SQL语句：

SELECT name, age FROM student WHERE id = 1;

在语法分析阶段，MySQL会进行以下操作：

  • 词法分析：将SQL语句分割成以下词法单元：
token type value
SELECT keyword select
name identifier name
, symbol ,
age identifier age
FROM keyword from
student identifier student
WHERE keyword where
id identifier id
= operator =
1 number 1
  • 语法规则：根据MySQL的语法规则，检查词法单元是否符合以下格式：
select_statement: SELECT select_expression_list FROM table_reference_list [WHERE where_condition]
  • 语义分析：根据数据字典和目录，检查以下内容：
    • 表student是否存在
    • 字段name, age, id是否属于表student
    • 字段id的数据类型是否与数字1匹配
    • 等等

如果以上步骤都没有出现错误，那么MySQL就会认为这条SQL语句在语法分析阶段是正确的，并继续进行后续的处理。否则，MySQL就会报错，并停止执行这条SQL语句。

2. 查询优化

查询优化是MySQL执行SQL语句的第三步。SQL语句在查询优化阶段会经历以下步骤：

  • 查询重写：MySQL会对SQL语句进行一些语法和逻辑上的变换，以便于后续的优化和执行。例如，将子查询转换为连接，将or条件转换为union，将in条件转换为exists等。
  • 查询分解：MySQL会将一条复杂的SQL语句分解为多个简单的子查询，每个子查询可以单独优化和执行。例如，将union查询分解为多个select查询，将关联子查询分解为独立的select查询等。
  • 预处理：MySQL会对SQL语句进行一些基本的检查和处理，例如检查语法错误，解析参数，分配内部资源等。
  • 优化器：MySQL会根据统计信息和成本模型，为SQL语句选择一个最佳的执行计划。执行计划包括了连接顺序，访问方法，索引选择，排序策略等。

举例说明，下面是一个示例SQL语句：

SELECT name, age FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 80);

则首先在查询重写时，MySQL会将这条SQL语句重写为：

SELECT name, age FROM student s JOIN score c ON s.id = c.student_id WHERE c.score > 80;

这样做的好处是：

  • 连接查询通常比子查询更快，因为MySQL优化器可以生成更佳的执行计划，可以预先装载数据，更高效地处理查询。
  • 子查询往往需要运行重复的查询，子查询生成的临时表上也没有索引，因此效率会更低。
  • 连接查询可以利用索引加速，比如在student表的id列和score表的student_id列上建立索引。

接下来在查询分解阶段，MySQL会将这条SQL语句分解为两个子查询：

SELECT name, age, id FROM student; 
SELECT student_id FROM score WHERE score > 80;

预处理时MySQL会对SQL语句进行一些基本的检查和处理，例如检查表名和字段名是否存在，解析参数等。

最后优化器会根据统计信息和成本模型，为SQL语句选择一个最佳的执行计划。

MySQL优化器是负责为SQL语句选择一个最佳的执行计划的模块。执行计划包括了连接顺序，访问方法，索引选择，排序策略等。MySQL优化器是基于成本的优化器（cost-based optimizer），也就是说它会根据统计信息和成本模型来估算不同执行计划的代价，并选择代价最小的那个。

MySQL优化器在选择执行计划时会考虑以下几个方面：

  • 表依赖关系：MySQL优化器会分析SQL语句中涉及到的表之间是否有依赖关系，比如外键约束，主键约束等。这些依赖关系会影响连接顺序和访问方法的选择。
  • 可用索引：MySQL优化器会分析SQL语句中参与条件过滤或排序的列是否有可用索引，并根据索引类型和覆盖度来选择合适的索引。
  • 预估行数：MySQL优化器会根据数据字典和目录中存储的统计信息来预估每个表或每个索引范围内的行数。这些行数会影响成本模型中的I/O代价和CPU代价。
  • 预估成本：MySQL优化器会根据预估行数和成本常数（cost constant）来预估每个执行计划的成本。成本常数是一些固定参数，比如随机读一页数据的代价，排序一行数据的代价等。MySQL优化器会选择成本最低的执行计划。

例如，上面能够重写后的SQL语句应该是：

SELECT name, age FROM student s JOIN score c ON s.id = c.student_id WHERE c.score > 80;

MySQL优化器在选择执行计划时会进行以下操作：

  • 分析表依赖关系：发现student表和score表之间没有依赖关系，因此可以任意调整连接顺序。
  • 分析可用索引：发现student表有一个主键索引idx_student_id（id），

这么一通分析之后到底有点纸上谈兵，接下来我们在MySQL 8.0 里执行命令，毕竟实践出真知。进入MySQL命令行，利用explain来查看执行计划：

mysql> SELECT name, age FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 80);
+----------+------+
| name     | age  |
+----------+------+
| zhangsan |   18 |
| wangwu   |   20 |
+----------+------+
2 rows in set (0.00 sec)

mysql> explain SELECT name, age FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 80);
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | student | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 |   100.00 | NULL                                                            |
|  1 | SIMPLE      | score   | NULL       | ALL  | student_id    | NULL | NULL    | NULL |    8 |    12.50 | Using where; FirstMatch(student); Using join buffer (hash join) |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

我们可以在explain的结果中看到，两行的ID值都为1，而我们自己写的SQL语句里有两个Select，说明实际并没有按照我们的原SQL来执行

再使用show warnings来查看实际执行的sql内容：

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                              |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `datasets`.`student`.`name` AS `name`,`datasets`.`student`.`age` AS `age` from `datasets`.`student` semi join (`datasets`.`score`) where ((`datasets`.`score`.`student_id` = `datasets`.`student`.`id`) and (`datasets`.`score`.`score` > 80)) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以发现，实际执行的时候用的是semi join，这是因为Semi Join返回的结果只包含主表（左表）中满足连接条件的行，而不包含从表（右表）的任何数据。它的主要目的是通过减少要比较的数据量来提高查询性能。通过使用Semi Join，可以避免将两个表的所有数据进行连接，并仅仅关注满足连接条件的部分数据。

但是对于开发人员来说，我们并不需要关注优化器内部的所有决策，因为涉及的因素太多了，所以我们从整体上来看知道大致的优化方向即可。

这里也给出上面示例的建表语句，方便有心的读者自行尝试：

CREATE TABLE student
(
    id   INT PRIMARY KEY auto_increment,
    name VARCHAR(20),
    age  INT
) charset = utf8mb4;

CREATE TABLE score
(
    student_id INT,
    course     VARCHAR(20),
    score      INT,
    FOREIGN KEY (student_id) REFERENCES student (id)
) charset = utf8mb4;
INSERT INTO student (id, name, age) VALUES (1, 'zhangsan', 18),(2, 'lisi', 19),(3, 'wangwu', 20),(4, 'zhaoliu', 21);
INSERT INTO score (student_id, course, score) VALUES (1, '数学', 85),(1, '语文', 90),(2, '数学', 75),(2, '语文', 80),(3, '数学', 95),(3, '语文', 100),(4, '数学', 65),(4, '语文', 70);
SELECT name, age FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 80);

3. 执行SQL语句

执行SQL语句是MySQL执行SQL语句的最后一步。简单来说，执行器会按照执行计划的步骤，逐步执行SQL语句。执行器会根据查询语句，从磁盘读取数据，并将其存储在内存中。然后，执行器会对数据进行排序、分组、聚合等操作，最终生成查询结果。

说的详细一点，一些重要的步骤如下：

  • 打开表：MySQL会根据执行计划中涉及到的表，打开相应的表文件，并为每个表分配一个表句柄（table handle）。
  • 锁定表：MySQL会根据SQL语句的类型（读或写）和事务的隔离级别，为涉及到的表加上相应的锁（共享锁或排他锁）。锁的作用是保证数据的一致性和并发性。
  • 读取数据：MySQL会根据执行计划中选择的访问方法（全表扫描或索引扫描），从存储引擎中读取数据。存储引擎是负责管理数据文件的模块，不同的存储引擎有不同的特性和优化。
  • 过滤数据：MySQL会根据执行计划中的过滤条件（where，group by，having，order by，limit等），对读取到的数据进行过滤和处理。过滤条件可以减少返回给客户端的数据量，提高查询效率。
  • 返回结果：MySQL会将过滤后的数据返回给客户端，并释放相关的资源（表句柄，锁等）。客户端可以接收到结果集，并进行后续的操作。

实际上，依旧可以通过MySQL命令行来了解其执行过程：

mysql> set profiling = 'ON';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT name, age FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 80);
+----------+------+
| name     | age  |
+----------+------+
| zhangsan |   18 |
| wangwu   |   20 |
+----------+------+
2 rows in set (0.00 sec)


mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000094 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000008 |
| checking permissions           | 0.000005 |
| checking permissions           | 0.000004 |
| Opening tables                 | 0.000088 |
| init                           | 0.000009 |
| System lock                    | 0.000009 |
| optimizing                     | 0.000012 |
| statistics                     | 0.000035 |
| preparing                      | 0.000076 |
| executing                      | 0.000065 |
| end                            | 0.000004 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000009 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000021 |
| cleaning up                    | 0.000010 |
+--------------------------------+----------+
18 rows in set, 1 warning (0.00 sec)

可以看到，命令执行结果非常详细的列出了所有步骤，本文只是挑选了一部分来展开说。

具体结合到例子来说明，假设有一条SQL语句如下：

SELECT name, age FROM student s JOIN score c ON s.id = c.student_id WHERE c.score > 80 ORDER BY s.age LIMIT 10;

在执行阶段，MySQL会进行以下操作：

  • 打开表：MySQL会打开student表和score表，并为它们分配两个表句柄s和c。
  • 锁定表：MySQL会根据SQL语句是读操作，并且假设事务的隔离级别是可重复读（repeatable read），为student表和score表加上共享锁（shared lock）。
  • 读取数据：MySQL会根据执行计划中选择的访问方法，从存储引擎中读取数据。假设执行计划是先扫描score表的索引idx_score（score），然后回表获取student_id列，再通过student_id列关联到student表，并获取name和age列。
  • 过滤数据：MySQL会根据执行计划中的过滤条件，对读取到的数据进行过滤和处理。具体步骤如下：
    • 根据where条件c.score > 80，筛选出符合条件的记录。
    • 根据order by条件s.age，对记录按照学生年龄进行排序。
    • 根据limit条件10，只取前10条记录作为结果集。
  • 返回结果：MySQL会将结果集返回给客户端，并释放相关的资源。

三、InnoDB存储结构

InnoDB是MySQL的默认存储引擎，它支持事务、行级锁、外键、MVCC等特性，提供了高性能和高可靠性的数据存储方案。InnoDB的底层结构主要由两部分组成：内存结构和磁盘结构。

图片来源：https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

内存结构

InnoDB的内存结构主要包括以下几个部分：

  • 缓冲池（Buffer Pool）
  • 更改缓冲区（Change Buffer）
  • 自适应哈希索引（Adaptive Hash Index）
  • 日志缓冲区（ Log Buffer）

如果对这部分内容感兴趣可以看官方文档，这里只做一个简单的介绍。

缓冲池（Buffer Pool）

主要用于缓存表数据和索引数据，加快访问速度。缓冲池是InnoDB内存结构中最重要的部分，通常占用宿主机80%的内存。缓冲池被分成多个页，每页默认大小为16KB，每页可以存放多条记录。缓冲池中的页按照LRU（最近最少使用）算法进行淘汰，同时也被分成两个子链表：New Sublist和Old Sublist，分别存放访问频繁和不频繁的页。

  • New Sublist：占用缓冲池5/8的空间，存放最近被访问过的页。当一个新的页被读入缓冲池时，会被放在New Sublist的最前端。当一个已经存在于缓冲池的页被访问时，如果它在New Sublist中，则不会改变位置；如果它在Old Sublist中，则会被移动到New Sublist的最前端。
  • Old Sublist：占用缓冲池3/8的空间，存放较久未被访问过的页。当一个已经存在于缓冲池的页被访问时，如果它在Old Sublist中，则会被移动到New Sublist的最前端；如果它在New Sublist中，则不会改变位置。
  • 淘汰策略：当缓冲池已满时，需要淘汰一些页来腾出空间。淘汰策略是从Old Sublist的尾部开始扫描，找到第一个没有被修改过（clean）且没有被锁定（unlocked）的页，并将其淘汰出缓冲池。

写缓冲（Change Buffer）

主要用于缓存对非聚集索引的修改操作，减少磁盘I/O。写缓冲是缓冲池的一部分，当对非聚集索引进行插入、删除或更新时，不会立即修改磁盘上的索引页，而是先记录在写缓冲中。当缓冲池中的数据页被刷新到磁盘时，会将写缓冲中的修改操作合并到相应的索引页中。

  • 写缓冲类型：写缓冲有三种类型，分别用于记录不同类型的非聚集索引修改操作：
    • Insert Buffer：用于记录对非聚集索引进行插入操作时产生的新条目（entry）。
    • Delete Buffer：用于记录对非聚集索引进行删除操作时需要删除的条目（entry）。
    • Purge Buffer：用于记录对非聚集索引进行更新操作时需要删除和插入的条目（entry）。
  • 写缓冲结构：写缓冲是由多个段（segment）组成的，每个段有一个位图（bitmap），用于标记哪些页有写缓冲条目（entry）。每个段都有一个根页（root page），用于存储写缓冲条目（entry）。每个根页都有一个B+树结构，用于按照空间ID（space ID）和页号（page number）排序写缓冲条目（entry）。
  • 写缓冲合并：当缓冲池中的数据页被刷新到磁盘时，会触发写缓冲的合并操作。合并操作的步骤如下：

    • 根据数据页的空间ID（space ID）和页号（page number），在写缓冲中查找是否有对应的条目（entry）。
    • 如果有，就将写缓冲中的条目（entry）应用到磁盘上的索引页中，完成修改操作。
    • 如果没有，就直接刷新数据页到磁盘，不做任何修改操作。
    • 将写缓冲中已经合并的条目（entry）删除，释放空间。

日志缓冲（Log Buffer）

主要用于缓存重做日志（Redo Log），保证事务的持久性。日志缓冲是一个循环使用的内存区域，默认大小为16MB，可以通过参数innodb_log_buffer_size来调整。当事务提交时，会将日志缓冲中的重做日志刷新到磁盘上的重做日志文件中。日志缓冲中的重做日志也会在以下情况下被刷新：日志缓冲已满、每秒钟一次、每个事务检查点一次。

  • 重做日志：重做日志是一种物理日志，记录了对数据页进行的物理修改操作。重做日志可以用于恢复事务在崩溃或异常情况下未完成的修改操作，保证数据的完整性和一致性。
  • 重做日志格式：重做日志由多个固定大小的日志块（log block）组成，每个日志块默认大小为512字节。每个日志块包含以下信息：

    • 日志块头（log block header）：占用12字节，包含了日志块编号（log block number），校验和（checksum），数据长度（data length）等信息。
    • 日志记录（log record）：占用不定长度，包含了对数据页进行的物理修改操作的详细信息。例如，修改了哪个数据页，修改了哪个偏移量，修改前后的值是什么等。
    • 日志块尾（log block tail）：占用4字节，包含了日志块编号（log block number）的副本。
  • 重做日志文件：重做日志文件是磁盘上存储重做日志的文件，通常有两个或多个，以实现循环写入和备份。重做日志文件可以通过参数innodb_log_files_in_group来指定数量，通过参数innodb_log_file_size来指定大小。重做日志文件中有一个特殊的位置叫做检查点（checkpoint），表示到这个位置之前的所有重做日志都已经被应用到数据文件中，可以被覆盖或删除。

自适应哈希索引（Adaptive Hash Index）

主要用于加速等值查询，提高查询效率。自适应哈希索引是InnoDB根据查询频率和模式自动建立的一种哈希索引，可以将某些B+树索引转换为哈希索引，从而减少树的搜索次数。自适应哈希索引是可选的，可以通过参数innodb_adaptive来开启或关闭。

  • 自适应哈希索引原理：当InnoDB发现某个B+树索引被频繁用于等值查询时，就会为该索引建立一个对应的哈希索引。哈希索引是一种基于键值对的索引，可以通过哈希函数快速定位到数据页的位置。哈希索引比B+树索引更简单，更高效，但只能用于等值查询，不能用于范围查询或排序查询。
  • 自适应哈希索引结构：自适应哈希索引是由多个分区（partition）组成的，每个分区有一个哈希表（hash table），用于存储键值对。每个键值对包含以下信息：

    • 键（key）：由B+树索引的键值和空间ID（space ID）组成。
    • 值（value）：由数据页的页号（page number）和记录的偏移量（offset）组成。
  • 自适应哈希索引使用：当InnoDB执行一个等值查询时，会先在自适应哈希索引中查找是否有匹配的键值对。如果有，就直接定位到数据页的位置，并获取记录。如果没有，就回退到B+树索引中进行搜索。

磁盘结构

表空间（Tablespace）

表空间是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。表空间可以分为以下五种类型¹²³⁴⁵：

  • 系统表空间（system tablespace）：系统表空间是InnoDB的默认表空间，通常存储在ibdata1文件中，也可以分成多个文件。系统表空间包含了InnoDB的数据字典，双写缓冲区，Change Buffer和undo log等重要信息。系统表空间是共享的，可以存储多个表的数据和索引。系统表空间的大小是不可缩小的，只能通过重建整个数据库来减小。
  • 临时表空间（temporary tablespace）：临时表空间用于存储临时表的数据和索引，例如在执行复杂查询时产生的中间结果。临时表空间通常存储在ibtmp1文件中，可以配置为自动扩展或固定大小。临时表空间不会持久化到磁盘，每次数据库重启后都会被清空。
  • 常规表空间（general tablespace）：常规表空间是一种共享表空间，可以存储多个表的数据和索引。常规表空间可以在任意位置创建多个文件，文件的扩展方式可以是自动扩展或预分配。常规表空间可以用于跨数据库存储数据，或者将不同类型或大小的表分开存储。
  • undo表空间（undo tablespace）：undo表空间包含undo log撤销记录的集合，用于事务回滚和一致性读。undo log记录了数据页修改前的值，用于恢复未提交或已回滚的事务所做的修改，或者提供给其他事务一个数据修改前的快照。undo表空间可以有多个文件组成，文件存储在innodb_undo_directory指定的目录下，以undofile开头命名。
  • 文件独占表空间（file-per-table tablespace）：文件每表表空间是一种独占表空间，含单个InnoDB表的数据和索引，并存储在文件系统中自己的数据文件中。文件每表表空间的文件名与表名相同，以.ibd为扩展名，位于数据库目录下。文件每表表空间可以实现单个表的压缩，加密，传输和优化等操作。

数据字典（Data Dictionary）

数据字典包含用于跟踪对象，如表，索引，和列等元数据的内部系统表²⁴。元数据实际上位于InnoDB系统表空间中。InnoDB使用数据字典来管理和访问数据库对象，并检查用户对对象的权限。数据字典在数据库启动时加载到内存中，并在数据库关闭时刷新到磁盘上。

双写缓冲区（Doublewrite Buffer）

双写缓冲区位于系统表空间中的存储区域，用于保证数据页在写入磁盘时不会损坏²⁴⁵。InnoDB在Buffer Pool中刷新页面时，会将数据页写入doublewrite缓冲区后才会写入磁盘。如果在写入OS Cache或者磁盘mysql进程奔溃后， InnoDB启动崩溃恢复能从doublewrite找到完整的副本用来恢复。

重做日志（Redo Log）

重做日志是基于磁盘的数据结构，在崩溃恢复期间用于纠正不完整事务写入的数据 。MySQL以循环方式写入重做日志文件，默认会产生ib_logfile0 和 ib_logfile1两个文件。InnoDB在提交事务之前刷新事务的redo log，InnoDB使用组提交（group commit）技术来提高性能。重做日志记录了数据页的物理修改，而不是逻辑修改，这样可以减少日志的大小和恢复的时间。重做日志可以通过innodb_log_file_size和innodb_log_files_in_group参数来调整大小和数量。

更改缓冲区（Change Buffer）

更改缓冲区是Buffer Pool中的一部分，用于缓存对辅助索引页的修改 。当InnoDB需要修改一个辅助索引页时，如果该页在Buffer Pool中，则直接修改；如果该页不在Buffer Pool中，则将修改记录在Change Buffer中，而不是从磁盘读取该页。这样可以减少磁盘I/O操作，提高性能。Change Buffer中的修改会在后台或者检查点时合并到辅助索引页中。Change Buffer的大小可以通过innodb_change_buffer_max_size参数来调整。

四、InnoDB磁盘空间管理结构

这部分简单介绍即可，参考官方文档：MySQL ：： MySQL 8.0 参考手册 ：： 15.11.2 文件空间管理

InnoDB的磁盘结构主要包括以下几个部分：

  • 表空间（Tablespace）：表空间是InnoDB存储数据和索引的逻辑单位，它由一个或多个文件组成。表空间可以分为系统表空间（System Tablespace），通用表空间（General Tablespace），文件表空间（File-Per-Table Tablespace）和临时表空间（Temporary Tablespace）。
  • 段（Segment）：段是表空间中分配和管理空间的单位，它由一个或多个连续或不连续的区（Extent）组成。段可以分为数据段（Data Segment），索引段（Index Segment），回滚段（Rollback Segment），撤销日志段（Undo Log Segment）和系统段（System Segment）。
  • 区（Extent）：区是段中分配空间的单位，它由一组连续的页（Page）组成。每个区的大小固定为1MB，包含64个页。
  • 页（Page）：页是InnoDB在磁盘和内存之间传输数据的基本单位，它由一个固定大小的块（Block）组成。每个页的大小默认为16KB，可以通过参数innodb_page_size来调整。页可以分为不同的类型，根据存储的内容而定，比如数据页（Data Page），索引页（Index Page），系统页（System Page），事务系统页（Transaction System Page），撤销日志页（Undo Log Page）等。
  • 行（Row）：行是InnoDB存储数据记录的最小单位，它由一个或多个列（Column）组成。每个行的大小不能超过半个页。行可以分为两种格式，根据存储方式而定，比如紧凑格式（Compact Format）和动态格式（Dynamic Format）。

参考资料:

  • MySQL :: MySQL 8.0 Reference Manual
  • 高性能MySQL 第四版

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: