深入探究MySQL内部实现原理（图）

初学者眼中的MySQL

对于我们很多初学者来说，MySQL就是这么简单：E,y但是 MySQL 如何在后台处理 SQL 查询？换句话说，工程师和数据科学家编写的SQL查询通常是简单的文本字符串内容，并发送到MySQL。那么MySQL如何解析这个字符串并知道要查找哪个数据表以及获取哪些记录呢？

连接池

正如我们当前正在查看此页面一样，Web浏览器 (chromechrome❀❀, ) 仍连接到 中网站连接；同样，我们的应用服务器必须通过网络连接到MySQL服务器，然后发送SQL请求文本内容。连接池通常用于管理网络连接。 。

连接池允许重用现有的网络连接，避免创建新连接时的初始化成本和断开连接时释放资源的成本。用户身份验证也可以合并到这一层中，以拒绝未经授权的数据库连接。

通常，每个连接都映射到一个线程。当处理 SQL 查询请求时，应用服务器线程从连接池中取出一个连接并发送给 MySQL 服务器发送请求，服务器中的线程接收以SQL字符串格式查询，执行以下步骤。

那么接下来的步骤是什么？

SQL 解析

MySQL 服务器必须了解请求试图执行的操作。它是否试图读取一些数据、更新数据或删除数据？

收到查询请求后，首先要解析SQL的内容。主要任务是将其本质上是文本格式的内容转换成MySQL内部二进制结构的组合，方便优化器程序进行优化操作。

查询优化器

在MySQL执行查询之前，它决定如何完成查询，即选择最佳的查询方法。

比如，你一家人出去旅游，大家都坐在车里准备出发，你突然发现自己忘记带20瓶水了。你很快就想起所有的瓶装水都在储藏室里，但你必须尽快把它们拿到你的车上，因为其他人都在等你；你开始想，你可以一次带着4瓶来回跑5次，或者你可以随身携带一个行李箱，把20瓶都放在箱子里，把它们带到车上，而不用来回。这就是优化器所做的，它分析满足请求的所有不同方法并选择最好的一种。

我们看一个简单的SQL查询：

SELECT name FROM employee_table WHERE employee_id = 1;

想象一下，employee_table1员工记录，至少有两个方法（或者正式的两个执行计划和术语）：

• 执行计划一：扫描name列中的所有名称，对于每个名称，检查其是否对应

  

  ❙❙❙❙，如果emp loyee_id = 1 然后返回名字；

• 执行计划二：使用主键索引查找出employee_id = 1记录，并返回记录名称内容。

方法2几乎总是会执行得更快，优化器将使用方法2，下一步是实际的执行计划。

执行引擎

执行引擎调用存储引擎API来执行查询优化器选择的执行计划。

存储引擎

很多软件系统可以分为计算层和存储层。计算层的效率通常很大程度上取决于数据在存储层中的组织方式。在本节中，我们将深入了解MySQL的存储引擎以及为加快存储引擎的读写速度而精心设计的诸多优化。

MySQL可以集成多种不同类型的存储引擎。每个存储引擎针对不同的使用场景都有自己的优缺点。也就是说，存储引擎可以看成一个接口，有各种底层实现，比如InnoDB、MyISAM、❀、❀、CSV 、存档、合并、黑洞❀。

InnoDB无疑是使用最多的存储引擎。这是根据版本 MySQL 5.5 的默认设置。

就像我们使用的笔记本电脑一样，InnoDB 将数据存储在内存和磁盘上。从高层的角度来看，当写入InnoDB时，数据总是先写入内存缓存空间，然后持久化到磁盘。

InnoDB将内存分为两部分：

• 缓冲池；
• 日志缓冲区。

缓冲池对于InnoDB非常重要。 MySQL在处理查询时通常非常快，因为数据实际上存储在内存中并对外提供服务（大多数情况下数据不是从磁盘读取的，这与很多人的想法完全相反），这个内存组件就是缓冲池。

Bufferpool

一般情况下，主机的 80% 会分配给缓冲池。更大的内存可以在内存中缓存更多的数据，从而可以使读取速度更快。快速地。

缓冲池除了简单地将数据放置在内存中之外，还针对内存中数据的组织进行了精心设计，以加快数据库的读写速度。让我们进一步了解这个详细设计。

页面

与图书馆组织图书的方式类似，使用 ID 来识别图书，并按类似的字母或数字顺序将它们组织在图书馆书架上。

InnoDB将缓冲池划分为许多数据页，还包含一个changebuffer（稍后解释）。所有数据页都以双向链表的形式链接，这意味着我们可以轻松地从当前页转到下一页，或者从当前页转到上一页。

那么页面上的数据是如何存储的呢？

用户记录

在一个数据页中包含以下内容：

• 指向上一个数据页的指针；
• 指向下一个数据页的指针；
• 用户记录；
• 其他属性信息。

指向上一个数据页和下一个数据页的指针只是一个指针。用户记录是存储所有row数据的位置。每行都有一个指向下一行的 。 next 形成单向链表的指针。

那么问题来了。

我们知道，关系数据库中的一行记录通常由一个主键字段和许多其他字段组成。这些记录通常按主键排序，因此在查找具有特定主键的记录时，可以使用二分查找等算法来快速检索数据。减少延迟；但如果每次向用户记录添加新数据时，InnoDB 都必须重新排列所有记录（行），这将是一个非常耗时的操作。

实际上，用户记录中的行是按插入顺序排列的，添加一条新记录只是添加到用户记录的末尾，所需的主键顺序是通过next指针实现的，对于每行 next 指针指向主键顺序之后的下一个逻辑行，而不是内存中的物理下一行。

现在有一个新问题。前面我们提到，我们不需要遍历所有记录来查找具有特定主键的目标数据，但如果所有行本质上都是一个单向链表，那么单向链表的特性决定了我们只有遍历整个链表才能找到特定的行。我们知道，将链表连接起来是一个时间复杂度为O(n)的操作。这是非常耗时的。那么InnoDB我该怎么做才能更快呢？还记得我们之前提到的其他领域吗？它们正是用于此目的。

下限和上限

两个属性分别代表数据页上最大和最小的存储记录集合，这意味着两者构成了一个min-max❀过滤器。通过O(1)时间复杂度算法检查这两个字段，InnoDB可以决定要查找的行是否存储在指定的数据页中。

假设我们的主键是数字类型，并且我们的数据页之一有 supremum = 1, infimum = 99 key = 101 行，那么显然在 O 之内(1)时候，InnoDB可以判断不在这个数据页中立即去另一个搜索。

如果该记录不在某个数据页上，InnoDB会跳过该页，但是如果该行记录在该数据页上，那么InnoDB还需要吗？整个链表呢？答案是否定的，其他领域的信息又会派上用场。

页面目录

顾名思义，它就像一个目录。

页目录存储指向行块的指针，假设我们在用户记录中的第n行上存储了行1，行的页目录和第行、第行的行和指针...，块大小为6，这个设计与跳转表结构非常相似。

我们现在可以想象，InnoDB首先检查数据页目录，然后快速确定要查找哪个数据块，然后使用指针跳转到该数据块对应的起始行单向链表，从该位置开始遍历。这样InnoDB避免了遍历整个列表，只需要遍历一个小得多的子列表来检索数据。

我们上面提到的区块正式称为Slot。一个页目录会有更多的槽，每个槽都有一个指向用户记录中一组数据的指针。

当将新数据添加到用户记录中时，Inod还将更新页面内容以制作两个奉献，以及Inodb❙❙Rhy。

Index

InnoDB 使用 B+ 树（对于数据，发音为 ❙tree❿）MySQL支持两种类型的索引：主键索引

和中学。一旦我们理解了数据页的概念，索引就变得显而易见了。

主键索引的叶子节点存储“页”

相反，辅助索引的叶子节点存储主键信息。当查询命中二级索引时，首先从二级索引中检索主键值。一旦我们知道了主键值，就可以使用主键索引来检索目标数据行。

为什么我们使用 B+ 树而不是 B- 树（B asn，而不是 pron B 树木减少）：

• B+树减少了必要的IO操作；
• 要求效率更稳定；
• 可以更好的支持范围查询。

---

所有这些优秀的设计，例如使用用户记录之间的单向链表、infimum-supremum和页面索引，主要是为了加快数据读取速度。那么当我们想要更新一行时会发生什么？现在我们来看看MySQL如何处理数据更新。

更新数据

如果我们在MySQL中输入一行记录，例如Id，从0开始，我们需要立即更新这行刚刚插入的数据也可以在要保存的缓冲池。

但是如果我们等待很长时间才更新这一行，那么id = 100的行记录可能不再位于缓冲池中。

由于内存有限，我们不能总是将嵌入的数据保存到内存中。如果是这样，内存很快就会被填满。清理策略，通常说的ttl(time-to-live)，用于清理内存空间，例如Redis通常用作缓存，Redis

ttl 表示密钥已被删除（仅标记为已删除并显示为不可读，然后实际上由后台进程批量删除）。对于MySQL，数据必须持久存在，删除意味着将数据保留在磁盘上并从内存中删除。

因此，当您尝试更新行记录时，如果该行记录不在缓冲池中，InnoDB 会将数据从磁盘加载到缓冲池中。这里的问题是 InnoDB 不能只加载行记录 id = 100，它必须加载包含该行的整个数据页。一旦整个数据页被加载到缓冲池中，就可以更新指定的数据行。

到目前为止一切看起来都很好，但这只是描述了主键索引的更新，那么辅助索引呢？

更改缓冲区

假设我们更新id = 100行的一些字段信息。如果其中一个字段建立了二级索引，则在更新字段信息时，必须同时更新两个字段。水平指数。但如果二级索引的数据页不在缓冲池中，InnoDB是否也会将该数据页加载到缓冲池中呢？

如果不直接使用二级索引，如果每次更新相关字段时都直接将数据加载到缓冲池中，会发生很多随机磁盘I/O操作，速度肯定会变慢InnoDB。

因此，Change Buffer旨在延迟二级索引的更新。

如果二级索引需要更新，并且其包含的数据页不在缓冲池中，则更新后的数据会暂时存储在更改缓冲区中。当数据页稍后加载到缓冲池中时（由于主键索引的更新），InnoDB将存储在更改缓冲区中的更改数据合并到缓冲池中的数据页中。

但是，这种变更缓冲区设计有一个缺点。如果Change Buffer已经积累了大量的临时变更数据，如果我们想把这些同时合并到Buffer Pool，可能需要几个小时。完成！ ！并且在合并过程中会产生大量的磁盘I/O操作，占用CPU周期，整体性能L。

所以这里还有一些权衡，我们前面提到当带有二级索引的页面加载到缓冲池中时会触发合并操作。在 InnoDB 设计中，为了避免可能需要几个小时的大型合并操作，合并操作还可以由其他事件触发，包括 事务 ❝ 和 重新启动服务器

等。存储引擎的功能不仅仅是内存数据库。自适应哈希索引可以通过 innodb_adaptive_hash_index 变量启用，或者在服务器启动时通过 --skip-innodb-adaptive-hash-index♼❀❀ 禁用。