MySQL 8架构

MySQL 客户端/服务器协议

这个客户端/服务器协议使MySQL通信简单快捷，但是也有一些限制。

首先，它没有流量控制，一旦一方发送了消息，另一方必须在响应之前获取整个消息。当服务器响应时，客户端必须接收整个结果集。这就是为什么 LIMIT 子句如此重要的原因。

大多数库连接MySQL的默认行为是取得数据结果，并将其缓冲到内存中。这很重要，因为在所有的行都被获取之前，MySQL不会释放查询所需的锁和其他资源。查询将处于“发送数据”状态。当客户端库一次性获取结果时，它减少了服务器需要完成和清理查询的工作量，因此服务器可以尽快完成查询并清理资源。

大多数客户端库允许您将结果集视为从服务器获取，尽管实际上您只是从库的内存缓冲区获取它。

这在大多数情况下都可以很好地工作，但对于可能需要长时间获取并使用大量内存的巨大结果集来说，这不是一个好主意。如果您指示库不要缓冲结果，则可以使用较少的内存并更快地开始处理结果。缺点是，当您的应用程序与库交互时，服务器上的锁和其他资源将保持打开状态。

析器和预处理器

首先，MySQL的解析器将查询分解成标记，并从中构建“解析树”。解析器使用MySQL的SQL语法来解释和验证查询，例如验证未终止的引号字符串，检查表和列是否存在等。

接下来，预处理器会检查权限。通常，这很快，除非您的服务器有大量权限。

查询优化器

现在解析树已经有效，优化器可以将其转换为查询执行计划。一个查询可以以多种不同的方式执行并产生相同的结果。优化器的工作是找到最佳选项。

MySQL使用基于代价的优化器，这意味着它试图预测各种执行计划的代价并选择最便宜的选项。

它基于统计数据来估算：每个表或索引的页面数、索引的基数（不同值的数量）、行和键的长度以及键分布。优化器不包括任何类型的缓存效果在其估算中-它假设每个读取都将导致磁盘I/O操作。

优化器可能并不总是选择最佳的计划，原因有很多：

● 统计数据可能是错误的。服务器依赖存储引擎提供统计数据，它们的准确性可能从非常正确到极其不准确。例如，由于其MVCC架构，InnoDB存储引擎无法维护关于表中行数的准确统计信息。

● 成本度量标准并不完全等同于运行查询的真实成本。它可能比MySQL的近似值更昂贵或更便宜。例如，当MySQL不知道哪些页面在内存中，哪些页面在磁盘上时，它实际上不知道查询将导致多少I/O。

● 您可能希望获得最快的执行时间，但MySQL并不真正尝试使查询快速，而是尝试将其成本最小化。

● 优化器并不总能估计每个可能的执行计划，因此可能会错过最佳计划。

MySQL的查询优化器可以应用静态优化，例如独立于值的优化，例如WHERE子句中的常量值。它们可以执行一次，并且在重新执行查询时始终有效。

相比之下，动态优化基于上下文，可以取决于许多因素，例如WHERE子句中的值或索引中的行数。每次执行查询时都必须重新评估它们。您可以将它们视为“运行时优化”。

MySQL知道如何自行执行许多优化，例如重新排序连接、转换连接、应用代数等价规则、COUNT()、MIN()和MAX()优化、子查询优化等等。

当然，优化器再聪明，有时候也不能得到最佳结果。有时候，您可能会了解关于数据的某些信息，而优化器不知道。

如果您知道优化器没有给出良好的结果，并且您知道原因，可以帮助它。

查询执行引擎

解析和优化阶段输出查询执行计划，MySQL的查询执行引擎使用它来处理查询。

与优化阶段相比，执行阶段通常不是很复杂：MySQL只需按照查询执行计划中给出的指令进行操作。计划中的许多操作调用由存储引擎接口实现的方法，也称为处理程序API。

向客户端返回结果

执行查询的最后一步是向客户端回复。即使不返回结果集的查询也会向客户端连接回复有关查询的信息，例如它影响了多少行。

如果查询是可缓存的，MySQL也会在此阶段将结果放入查询缓存中。

存储引擎层

它们负责存储和检索MySQL中存储的所有数据。它们是以插件的形式实现的，这使得相对容易实现不同的数据处理方式。

主要的存储引擎是InnoDB，是完全事务性的，并具有非常好的支持高并发工作负载的能力，这也是本指南将要考虑的唯一一种存储引擎。

另一个存储引擎示例是NDBCluster，它也是事务性的，并作为MySQL NDB Cluster的一部分使用。

优化器并不在意一个特定表使用哪个存储引擎，但存储引擎确实会影响服务器优化查询的方式。优化器会向存储引擎询问一些操作的成本和能力，以及表数据的统计信息。

存储引擎本身可能也很复杂。对于InnoDB，它包括用于缓存数据和索引的缓冲池、重做和撤销日志、其他缓冲区以及表空间文件。如果查询返回行，这些行将从存储引擎通过SQL层发送回应用程序。

但在解析查询之前，服务器会先查询查询缓存，其中只存储SELECT语句及其结果集。如果有人发出的查询与缓存中已有的查询完全相同，则服务器根本不需要解析、优化或执行查询，它只需返回存储的结果集即可。

在查询调优中，优化器和执行步骤，包括存储引擎，是最重要的步骤。本指南中的大部分信息都与这三个部分直接或间接相关。

InnoDB引擎是一个通用的存储引擎，平衡了高可靠性和高性能，它被设计用于处理许多短暂的事务。除非有强烈的理由使用其他引擎，否则应该为表使用InnoDB。如果你想研究存储引擎，深入学习InnoDB以尽可能多地了解它，而不是平均学习所有存储引擎，也是值得的。

InnoDB表是建立在聚集索引上的，聚集索引是InnoDB用于组织数据的术语。该名称来自于索引值聚集在一起的事实。在InnoDB中，一切都是索引。行数据位于B树索引的叶子页面中。

主键用于聚集索引。如果你没有明确指定主键，InnoDB将寻找一个不允许NULL值的唯一索引。如果也不存在这样的索引，则InnoDB将添加一个隐藏的6字节整数列，使用全局（适用于所有InnoDB表）自增值来生成一个唯一值。

因此，它提供了非常快的主键查找。但是，次要索引（不是主键的索引）包含主键列，因此如果你的主键很大，其他索引也将很大。如果在表上有许多索引，应该力求使主键小。

InnoDB存储引擎

InnoDB特性

MySQL 5.5.8后InnoDB是默认的存储引擎。

InnoDB使用B+Tree的结构进行存储。

InnoDB采用“表空间”保存文件，一般使用独立表空间:tablename.ibd。

InnoDB支持事务处理。

MySQL InnoDB事务隔离级别

事务隔离级别主要是为了解决脏读，不可重复读，幻读这三个问题，所以先说明以下这三个分别是什么。

脏读

脏读是指事务读取到其他事务没有提交的数据，主要发生在读取了其他事务未提交的数据上。

不可重复读

不可重复读是指在同一次事务中前后查询不一致的情况，主要发生在并发更新的情况。

幻读

幻读是一次事务中前后数据发生变化，发生了预期之外的问题，主要发生在删除和新增上，体现在数据量发生了变化。

事务隔离级别

事务隔离级别相关命令

# 查询事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

# 修改事务隔离级别为RC
set global TRANSACTION ISOLATION level read COMMITTED;
set session TRANSACTION ISOLATION level read COMMITTED;
# 查询修改结果
select @@global.transaction_isolation,@@transaction_isolation;

4种事务隔离级别

隔离级

脏读可能性

不可重复度可能性

幻读可能性

加锁读

READ UNCOMMITTED

是

是

是

否

READ COMMITTED

否

是

是

否

REPEATABLE READ

否

否

是（innodb除外）

否

SERIALIZABLE

否

否

否

是

常用的隔离级别也就是RC(读已提交)和RR(可重复读)。

MySQL Buffer Pool原理

官方文档地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html

缓冲池是主内存中的一个区域，用于 InnoDB缓存访问的表和索引数据。缓冲池允许直接从内存访问常用数据，从而加快处理速度。在专用服务器上，通常最多有 80% 的物理内存分配给缓冲池。

为了提高大容量读取操作的效率，缓冲池被划分为可容纳多行的页面。为了提高缓存管理的效率，缓冲池被实现为页面的链接列表；使用最近最少使用 (LRU) 算法的变体将很少使用的数据从缓存中淘汰。

知道如何利用缓冲池将经常访问的数据保存在内存中是 MySQL 调优的一个重要方面。

缓冲池 LRU 算法

缓冲池使用 LRU 算法的变体作为列表进行管理。当需要空间将新页面添加到缓冲池时，最近最少使用的页面将被逐出，并将新页面添加到列表中间。此中点插入策略将列表视为两个子列表：

头部是最近访问的 新（ “年轻” ）页面的子列表

末尾是最近访问较少的旧页面子列表

默认情况下，操作如下：

● 缓冲池的 3/8 专用于旧子列表

● 列表的中点是新子列表尾部与旧子列表头部的边界

● 当InnoDB读取页到缓冲池时，它会初始化地将它插入到中间点（即旧子列表的头部）。一页可以被读取是因为这是用户启动的操作（比如一个SQL查询）或者是InnoDB预读操作的一部分。

● 访问旧子列表中的页会使这个页变得年轻，该页会被移动到新子列表的头部。移动的触发时机取决于页被读取的原因（用户启动的操作或者InnoDB的预读操作）。如果页读取是由于用户启动的操作而引起的，第一次访问页后会立即将页移动到新子列表的头部。如果页读取是InnoDB预读操作引起的，第一次访问页后不会立即发生移动页的操作，或者直到该页被驱逐都不会发生。

● 随着数据库的运行，缓冲池中未被访问的页会通过向列表尾部移动来来 “老化”（即被驱逐的概率加大）。 新旧子列表的页会随着其他页的更新而被老化。旧子列表的页也会随着插到中间点的页而老化。最终，一个未使用的页面到达旧子列表的尾部并被驱逐。

默认情况下，通过查询触发读取到缓冲池的页会立即移动到新子列表，这意味着这些页会停留在缓冲池的时间更长。

一个mysqldump操作或者一个不带where条件的查询语句引起的全表扫描，会将大量的数据加载到缓冲池中并且驱逐相等量的旧数据，即使这些新加载进来的大量数据以后从未被使用。

类似地，由后台进程预读取加载到缓冲池中的页，即使只被访问过一次，这些页都会移动到新子列表的头部。这些场景都会将经常使用的页推向旧子列表，在那里他们会成为被驱逐的对象。

缓冲池配置

你可以缓冲池的各种方面来提高性能：

● 理想地，你可以根据实际情况将缓冲池的大小设置得尽可能大，从而留出足够多的内存给服务器上的其他进程运行，而不会出现过多的分页。缓冲池越大，InnoDB就越像一个内存数据库，从磁盘读取数据一次，后续的读取期间从内存访问数据。详情见配置InnoDB缓冲池大小。

● 在具有足够内存的64位系统上，您可以将缓冲池拆分成多个部分，以减少并发操作之间对内存结构的争用。详情见配置多个缓冲池实例。

● 你可以将经常访问的数据保留在内存中，而不管操作的活动突然激增，这些操作会将大量不常用的数据带入缓冲池。详情见使缓冲池扫描具有抵抗性

● 你可以控制如何以及何时执行预读取请求来将分页异步地预取到缓冲池中，以应对即将到来的需求。详情见配置InnoDB缓冲池预取（预读）.

● 你可以控制后台何时刷新以及是否根据工作负载动态调整刷新速率。详情见配置缓冲池刷新

● 你可以配置InnoDB如何保存当前缓冲池的状态来避免服务器重启后长时间的预热。详情见保存和回复缓冲池的状态

简单总结

缓冲池就像一个内存数据库，当用户访问数据后，它将数据复制到缓冲池的旧页的头部，当用户再次访问，它会将就页的数据移动到新页的头部，这里类似与热点数据的道理，也就是说越不热门的数据，越会慢慢的老去，最后离开缓冲池。

问题也同样存在，就是他是基于SQL来缓存的，那么如果你的项目中经常有全表扫描或大量数据的查询，那么它也会缓存进去，这样往往会顶掉热门数据的位置，打乱缓冲池中有效的数据结构。

知道了这个机制，可以在SQL优化过程中，利用上这个机制，减少大量数据查询，构建有效的缓冲池。

MySQL索引

索引是什么

索引就类似与字典的目录，起到快速查询定位速度的作用。

索引在查询中来讲就是为了防止全表扫描（Full Scan）。

MySQL常用的索引为B+Tree索引：适用于范围查找，Hash索引：适用于精准匹配。

索引的分类

从存储结构来划分（这里对应着不同的存储引擎）：

BTree索引（B-Tree，B+Tree），Hash索引，full-index全文索引，R-Tree索引

从应用层次划分：

普通索引，唯一索引，复合索引。

从数据的物理顺序和键值逻辑来划分：

聚集索引，非聚集索引。

InnoDB引擎B+Tree索引

在MySQL中InnoDB引擎的表都会默认有一个主键索引，实际在硬盘上，就是使用主键生成的B+Tree来存储数据的。

下面是一个主键ID的索引结构图，非叶子节点上只有主键ID，叶子节点上有的完整数据

如果我以某个字段创建索引，实际是创建非叶子节点是索引字段，叶子节点是对应的主键ID的这样一个B+Tree存储到硬盘上。

然后查询的时候，通过索引字段找到对应的数据ID，然后通过主键索引查询到数据。