MySQL架构设计

MySQL架构设计

查询语句：

select * from users;

返回结果为：

问题：

一条SQL查询语句是如何执行的？

Server层

MySQL架构可以分为server层和Engine层两部分：

连接器（connector）

1. 连接管理

连接器的首要任务是管理客户端的连接。客户端与MySQL服务器之间的通信是通过连接来实现的。每次客户端向MySQL发起请求时，首先要建立一个连接。这个连接类似于一条通道，客户端通过这条通道向服务器发送SQL查询，服务器则通过这条通道返回结果。

1.1 连接类型

MySQL支持两种主要的连接类型：

• 短连接（Short-lived Connection）：这种连接在执行完一次请求后立即关闭。每次查询都需要重新建立连接，适合一些轻量级的操作或分布式系统中不频繁的交互。
• 长连接（Persistent Connection）：连接在建立后会保持一段时间，不会在每次请求后立即关闭。长连接在高频率请求的场景中效率更高，因为省去了频繁建立和关闭连接的开销。

1.2 连接数限制

MySQL服务器通常会限制同时打开的连接数。如果连接数超出配置的上限，新的连接请求会被拒绝。MySQL通过参数max_connections来设置最大连接数，管理员可以根据系统资源和应用需求调整该参数。

2. 权限验证

在客户端成功建立连接后，连接器的下一个任务是对用户进行权限验证。

2.1 用户身份验证

• 当用户尝试连接MySQL时，必须提供用户名和密码。连接器会验证这些凭据是否正确。MySQL通过存储在mysql.user表中的用户信息进行比对。
• 如果用户名或密码错误，连接器会拒绝连接请求，并向客户端返回错误信息。

2.2 权限检查

• 除了身份验证，连接器还会检查用户是否有权限执行所请求的操作。这包括对数据库、表、视图、存储过程等对象的访问权限。权限验证在连接建立时进行，也可能在后续操作中再次检查（例如执行一个需要更高权限的操作时）。
• MySQL的权限系统是非常细粒度的，可以为不同用户、不同的数据库对象授予不同级别的访问权限（如SELECT、INSERT、UPDATE等）。

3. 连接维护

在连接建立后，连接器负责维持该连接，直到它被关闭。

3.1 连接状态

• 在连接的生命周期内，连接器会管理连接的状态。MySQL提供了多种状态标志，用于描述连接的当前状态（例如Sleep表示空闲状态，Query表示正在执行查询）。
• 连接可以处于活动状态或空闲状态。对于长连接，如果在一段时间内没有活动请求，连接可能会进入空闲状态。管理员可以通过配置参数（如wait_timeout）来控制空闲连接的超时时间。

3.2 连接关闭

• 连接可以由客户端主动关闭，或者在客户端未明确关闭连接的情况下，由服务器根据配置的超时时间被动关闭。关闭连接时，连接器会释放与该连接相关的所有资源。

4. 连接池（Connection Pool）

为了提高性能和资源利用率，许多应用程序会使用连接池技术。

4.1 工作原理

• 连接池是一个缓存池，里面保存了多个连接实例。当一个请求到来时，应用程序不必重新建立一个新的连接，而是从连接池中获取一个空闲连接来使用。操作完成后，连接被返回到池中以供后续使用。
• 连接池减少了频繁建立和关闭连接的开销，提高了系统的响应速度，特别是在高并发环境中。

4.2 MySQL与连接池

• MySQL自身并没有内置的连接池管理功能，但大多数应用框架或中间件（如Java的JDBC）都提供了连接池的支持。通过合理配置连接池，可以显著提升应用程序的性能。

5. 潜在问题

尽管连接器功能强大，但也可能遇到一些常见问题：

• 连接过多：如果连接数超过了MySQL服务器的处理能力，可能导致系统资源耗尽，性能下降。
• 长连接资源占用：长连接虽然减少了连接开销，但如果使用不当，可能导致资源长期占用，例如内存泄漏等问题。
• 连接断开：网络不稳定可能导致连接中断，客户端需要具备相应的错误处理机制来应对这种情况。

思考：

（1）一个客户端只会和MySQL服务器建立一个连接吗？

（2）只能有一个客户端和MySQL服务器建立连接吗？

答：

（1）一个客户端可以和MySQL服务器进行多个连接的建立。

（2）MySQL服务器支持并发处理。

连接器负责和客户端建立连接、获取权限、维持和管理连接。

连接命令：

mysql -h$ip -p$port -u$user -p

在完成TCP握手之后，连接器就会基于我们给的账号密码进行身份验证，去mysql.user表中进行比对。

• 验证不通过："Access denied for user"错误
• 验证通过：连接器会到权限表里面查出拥有的权限，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限

show processlist   -- 查看连接状态

查询缓存

MySQL8 往后的版本都舍弃了这个功能。

通过了连接管理后，MySQL已经拿到了SQL语句，然后根据SQL语句去查询缓存。

缓存命中：当一个SQL查询语句执行时，MySQL首先会检查查询缓存中是否有该语句的结果。如果有，并且缓存内容依然有效，MySQL会直接返回缓存中的结果，而无需重新执行查询。这种情况下，查询速度会非常快，因为省去了执行SQL语句所需的所有步骤（如解析、优化和执行）。

缓存存储：如果查询结果没有在缓存中命中，MySQL会正常执行SQL语句，并将执行结果存入查询缓存中，以便相同的查询在下次执行时可以直接使用缓存。

缓存失效：查询缓存并不是永远有效的。任何涉及到查询相关表的变更操作（如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE等）都会导致缓存中与该表相关的所有查询失效。这是因为一旦表的数据发生了变化，缓存中的查询结果可能不再准确，需要重新计算。

缓存的缺点

缓存失效频率高：查询缓存的一个主要问题是其失效机制。如果表中的数据被频繁更新，相关的缓存会不断失效，导致缓存的命中率很低。这不仅没有提高性能，反而可能会增加系统的负担，因为缓存管理本身也会消耗资源。

全局锁问题：在MySQL 5.7及之前的版本中，查询缓存使用了全局锁，这意味着任何一个更新操作都会导致缓存锁定，影响所有查询的并发执行。这在高并发环境下会导致严重的性能瓶颈。

内存碎片：查询缓存中的数据会导致内存碎片化。当查询缓存中的可用空间不足以存储新的查询结果时，MySQL需要执行内存整理操作，这个过程也会对性能产生负面影响。

举个栗子🌰

数据库更新：现在假设一个后台管理系统的用户（如管理员）在某个时间点通过一个SQL操作更新了“热销产品”列表中的一个商品信息（例如更改了商品价格或库存）。

全局锁生效：在MySQL 5.7及之前的版本中，当表中的任何数据发生变化（如UPDATE、INSERT、DELETE等操作），查询缓存中与该表相关的所有缓存都会失效。为了保证数据一致性，MySQL需要在更新操作执行时锁住整个查询缓存，确保在缓存失效和清理的过程中不会有新的查询请求试图访问缓存。

阻塞并发查询：如果此时有多个用户（如用户B、C、D等）正在同时访问“热销产品”页面，他们的查询会发现缓存已失效，需要重新执行查询。然而，由于全局锁的存在，这些查询请求必须等待缓存的清理和更新操作完成后才能继续。这种锁定会阻塞其他所有的查询操作，导致用户体验变差，页面响应时间变长。

分析器

缓存如果没有命中，就要开始真正执行语句了。

首先，MySQL需要知道要做什么，因此需要对SQL语句做解析。

词法分析（Lexical Analysis）

词法分析是分析器的第一步，也被称为扫描阶段。在这个阶段，SQL查询被拆解成一个个最小的语法单元，称为“词法单元”（Tokens）。词法单元可以是SQL关键字、标识符（如表名、列名）、运算符（如+、-）、文字常量（如字符串、数字）、括号等。

SELECT name FROM users WHERE id = 10;

词法分析器将其拆解为以下词法单元：

• SELECT
• name
• FROM
• users
• WHERE
• id
• =
• 10
• ;

语法分析（Syntax Analysis）

语法规则的定义

语法分析器使用上下文无关文法（Context-Free Grammar, CFG）来描述SQL语言的语法规则。这些规则定义了SQL语句如何由一系列词法单元构成，并规定了各个词法单元之间的合法组合方式。

一个简单的SQL SELECT语句的语法规则可能如下：

<query> ::= SELECT <select_list> FROM <table_list> [WHERE <condition>];
<select_list> ::= <column> [, <select_list>]
<table_list> ::= <table> [, <table_list>]
<condition> ::= <expression> [AND <condition>] | <expression> [OR <condition>]
<expression> ::= <column> <operator> <value>

这些规则定义了一个SQL查询的结构：SELECT关键字必须跟随一个列列表，然后是FROM关键字以及一个表列表，可选的WHERE子句用来定义查询条件。

构建解析树

如果说有某个单词拼错了，或者说SQL语句缺少FROM子句，都会引发语法错误

SELECT name WHERE id = 10;

优化器（optimizer）

通过了分析器，说明 SQL 字符串符合语法规范，现在 MySQL 服务器要执行 SQL 语句了。

MySQL 服务器要怎么执行呢？

那么就需要产出执行计划，交给 MySQL 服务器执行，所以来到了优化器阶段。

优化器不仅仅只是生成执行计划这么简单，这个过程它会帮你优化 SQL 语句

1. 生成执行计划

优化器会生成一个或多个执行计划，这些计划描述了如何访问数据以及如何执行查询的各个部分。执行计划包括以下内容：

• 访问路径选择：决定使用哪种方法来访问表中的数据。例如，优化器会选择是使用全表扫描、索引扫描、索引覆盖扫描，还是通过键查找来访问数据。
• 连接顺序选择：当查询涉及多个表的连接（JOIN）时，优化器会决定连接这些表的顺序以及连接方法（如嵌套循环连接、合并连接、哈希连接）。
• 子查询优化：对于包含子查询的SQL语句，优化器会决定是否将子查询重写为连接（JOIN），或者是否可以将其改写为更简单的查询形式。
• 选择合适的算法：例如，决定排序操作是通过内存中的排序算法还是通过磁盘上的临时表来进行。

2. 成本评估

优化器基于“成本模型”来评估每个执行计划的成本。成本通常包括以下几个方面：

• I/O成本：读取数据所需的磁盘I/O操作的数量。
• CPU成本：处理数据所需的CPU时间，包括计算、比较、数据转换等操作。
• 内存使用：操作所需的内存量。

优化器会尝试选择总体成本最低的执行计划。这是因为在数据库查询中，I/O操作和CPU消耗通常是性能瓶颈，优化器旨在通过减少这些消耗来提高查询性能。

3. 查询重写

优化器可能会对查询进行重写，以生成一个等价但更高效的查询。常见的重写操作包括：

• 条件下推：将过滤条件尽可能早地应用到查询的各个阶段，以减少不必要的数据处理。
• 表达式简化：例如，将WHERE 1=1简化为不做任何操作，或者将常量表达式提前计算。
• 视图合并和子查询展开：将视图查询合并到主查询中，或者将子查询转换为连接（JOIN）操作。

4. 索引选择

优化器负责选择最合适的索引来加速查询。如果查询涉及多个索引，优化器会决定是否使用单一索引、多索引组合，或者完全不用索引。

5. 多表连接优化

在多表连接查询中，表的连接顺序对性能有很大影响。优化器会分析表的大小、索引的可用性、连接条件等，决定最优的连接顺序和连接方法。

例如：执行下面这样的语句

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

• 既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20
• 也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。

执行器（Actuator）

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执

行语句。

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'Hayaizo'@'localhost' for table
'T'

如果有权限，就会根据表的 Engine 选择来调用对应的引擎接口。

例如：

user_info 表的存储引擎是InnoDB。

select * from user_info where name = "Hayaizo";

• 如果 name 列没有声明任何索引，执行步骤如下：

1. 调用 innoDB 引擎接口获取表的第一行，判断 name 是否等于 Hayaizo 。如果不是，跳过。如果是，

将结果保存。

2. 调用 innoDB 引擎接口获取表的下一行，重复相同逻辑，一直到表的最后一行。
3. 将所有满足条件的结果集返回给客户端。

也就是暴力遍历，O(N)的时间复杂度。

• 如果 name 列有索引，执行步骤如下：

1. 调用innoDB引擎接口获取索引树，基于索引树快速查找到name等于Hayaizo的所有主键id
2. 将所有满足条件的主键id去主键索引树中查询信息，这个操作也叫做回表。
3. 将满足条件结果集全都返回给客户端

Engine层

什么是存储引擎？

引擎（Engine），我们都知道是机器发动机的核心所在，数据库存储引擎便是数据库的底层软件组织。

数据库使用数据存储引擎实现存储、处理和保护数据的核心服务

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得

特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySql的核心就是插件式存

储引擎。

MySQL支持哪些存储引擎？

SHOW ENGINES

可以发现，MySQL目前支持多种数据库存储引擎，默认引擎为InnoDB，且是唯一支持事务的存储引擎。

常见的存储引擎对比

InnoDB引擎

概述：InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。

优点：

• 支持外键。
• 支持事务，支持4个事务隔离级别，保证数据的完整性。
• 支持行级锁。
• InnoDB是为处理巨量数据量的最大性能设计。
  • 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如：.frm, .par 等都在MySQL8.0中不存在了，只有一个.ibd的文件，用于存放索引和数据（在InnoDB中索引即数据）。

缺点：

• 对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据。
• MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

---

MyISAM存储引擎

概述：MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

主要特性：

• MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
• 优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用。
• 表级锁定形式，数据在更新时锁定整个表。
• 数据文件的结构为：
  • 表名.frm 存储表结构
  • 表名.MYD 存储数据（MYData）
  • 表名.MYI 存储索引（MYIndex）

应用场景：只读应用或者以读为主的业务

---

MEMORY存储引擎

概述：MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

主要特性：

• 同时支持哈希（HASH）索引和B+树索引。
• Memory表至少比MyISAM表要快一个数量级。
• Memory表的大小是受限制的，表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按照需要进行扩大。
• 数据文件与索引文件分开存储，表的结构存储在.frm文件中，数据放在内存中
• 缺点：其数据易丢失，生命周期短。

从您提供的图片中提取的表格如下：

特点	MyISAM	BDB	Memory	InnoDB	Archive
存储限制	没有	没有	有	64TB	没有
事务安全	不支持	支持	不支持	支持	不支持
锁机制	表锁	页锁	表锁	行锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引	不支持	支持	支持	支持	不支持
全文索引	支持	不支持	不支持	支持	不支持
集群索引	不支持	不支持	不支持	支持	不支持
数据缓存	不支持	支持	支持	支持	不支持
索引缓存	支持	支持	支持	支持	不支持
数据可压缩	支持	支持	不支持	支持	支持
空间使用	低	低	N/A	高	非常低
内存使用	低	低	中等	高	低
批量插入的速度	高	高	高	低	非常高
支持外键	不支持	支持	不支持	支持	不支持