MySQL 基础架构

前言

本文通过分析单条查询 SQL 语句是如何执行的 ，从而分析MySQL 各个组成部分（Server 层 + Engine 层）的作用。此外，还需要补充不少关于 MySQL 的基础知识，从而确保之后索引、事务、日志的学习的基础。在了解 MySQL 的组成部分之前，先俯瞰整个 MySQL 的架构图，然后再详细了解每个部分的作用

Server 层

基本功能

• Server 层包括 连接器、查询缓存、分析器、优化器、执行器 等
• Server 层涵盖大多数核心服务功能：
  • 内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能（存储过程、触发器、视图等）
  • 事务和索引并不是所有引擎都支持，所以并不是在 Server 层实现而是在 Engine 层实现
  • 日志则是部分在 Engine 层实现（redo log、undo log），部分在 Server 层实现（binlog）
• Server 层具有的功能是所用引擎共享的，Engine 层的功能则是每个引擎特有的功能

连接器

连接器的功能

• 连接器主要负责建立客户端或者应用程序和数据库之间的连接，并对建立的连接进行管理和维护
  • 连接的建立通常依赖于 TCP/IP 协议（任何平台下都提供的方式）、Unix 域套接字、管道这三种方式
  • 应用程序和数据库建立连接通常会采用数据库连接池进行管理和维护
• 连接器还负责对建立连接的客户端进行权限验证，确保客户端访问的内容不会超出其权限范围内
  • MySQL 执行过程中总共存在三次权限验证：分别在连接器、分析器和执行器进行权限验证
    • 连接器：主要验证用户的身份，也就是其账号密码是否正确
    • 分析器：主要验证用户对数据库的访问权限，是粗粒度的验证过程（precheck）
    • 执行器：主要验证对表、字段、触发器等内容的验证，是更加细粒度的验证过程
  • 权限验证完毕后就会读取权限，权限在被读取之后就不会发生变化，无论该用户的权限是否被修改，只有重新启动后才会刷新权限

建立连接的详细过程

① 建立连接的命令：

如果不使用端口号和 IP 地址进行连接，那么就默认使用的 Unix 域套接字和数据库进行连接；相反，如果使用端口号和 IP 地址进行连接，那么就是使用的 TCP/IP 协议进行的连接

此外，强烈建议不要在输入命令的时候附带密码，容易导致密码泄露（譬如在 Linux 系统下，可以使用 history 命令查看所有执行过的命令）

• Unix 域套接字连接无法进行网络通信，要求数据库和客户端必须在同一台服务器上
• Unix 域套接字主要用于同一台主机上的进程间通信，而无法用于网络通信，它实际上是 Socket 机制的衍生，相对于传统的 Socket 套接字，在进程间通信的效率更高

-- 建立连接的命令
mysql -h$ip -P$port -u$root -p

② 建立连接的命令输入完成之后，连接器就开始工作：

首先，在使用远程连接的情况下，客户端和服务器端需要通过 TCP 三次握手建立连接

然后连接器开始验证输入的用户名和密码是否正确，对你的身份进行验证

• 如果输入的用户名和密码存在错误，那么就会抛出 Access denied for user，提示用户名或者密码错误
• 如果输入的用户名和密码正确，那就会读取权限，并将权限确定下来；直到下次连接的时候才会重新刷新权限

③ 连接建立完成之后，连接器就会对建立的连接进行维护和管理：

连接建立完成之后，如果没有后续的操作，那么连接器就会将其设置为空闲状态；如果太长的时间都没有任何动静，那么连接器就会自动断开这个连接；在连接断开之后，客户端必须重新建立和数据库之间的连接，否则会收到 Lost connection to MySQL server during query 错误

• 连接的状态可以通过 show processlist; 命令查看，可以看到当前数据库系统存在的连接数量及其状态

  

• 连接自动断开的阈值可以通过设置 wait_timeout 参数进行改变，默认阈值是 8 小时（show like variables wait_timeout 可以查看当前的阈值）

  

• 空闲连接除了依赖 MySQL 连接器的管理，还可以使用数据库连接池作为中间层对其进行管理，从而避免空闲连接过多导致内存占用过大的情况

连接的类型

① MySQL 中存在两种连接类型，分为长连接和短连接，这两种连接类型和 HTTP 中的长连接和短连接并不相同：

• HTTP 中的长连接和短连接是可以进行配置的，可以明确的选择启用长连接还是短连接
• 但是 MySQL 中并不能明确的配置，而是根据你连接持续的时间认定的，是一个比较“感性”的分类
  • 如果仅查询几次之后就立刻断开连接，那么就认定这个连接是短连接
  • 如果长期持续使用这个连接进行查询，那么就认定这个是长连接

② 长连接和短连接如何选择呢？

• 短连接就意味着需要 频繁地建立连接，而建立连接的过程是非常复杂的，这样就会造成不必要的性能开销
• 虽然长连接避免了频繁建立连接带来的性能开销，但是 如果存在大量的长连接就会导致内存溢出（OOM），从而导致 MySQL 宕机重启

③ 针对长连接存在的问题通常有两种解决方案：

• 定期断开连接，或者在 执行一个占用大量内存的查询之后，断开相应的连接
  • MySQL 执行过程中会将大量的数据绑定在连接对象中，所以占用内存的查询，最终会导致连接的内存增大，容易造成内存溢出
• MySQL 5.7 之后可以采用 mysql_reset_connection 函数重新初始化数据库连接
  • 该函数执行之后不需要重新连接和权限验证，只会初始化连接的相关信息
  • 相关信息主要包括将事务重新设置为自动提交模式、释放表锁、关闭临时表等等

查询缓存

查询缓存在 MySQL 8.0 之后就完全删除，不再使用查询缓存这个功能

查询缓存的功能

• 查询缓存中将<SQL 语句，查询结果>以 key-value 的形式进行存储，从而加快 SQL 语句的执行速度

查询缓存的执行过程

首先，SQL 语句会被 同时传递给查询缓存和分析器，然后 MySQL 就开始在查询缓存中搜索是否存在对应的键值对

• 如果查询缓存中存在 SQL 对应的键值对，那么就立刻返回对应的结果并且暂停分析器中对 SQL 语句的分析
• 如果查询缓存中没有 SQL 对应的键值对，那么就需要继续执行分析器之后的过程，并且在查询到结果之后将其缓存再查询缓存中

如果查询缓存的命中率很高，那么 MySQL 的执行效率也会相应的变高，但是实际上查询缓存的命中率非常低，根本没有对 MySQL 的执行效率做出多大的提升

查询缓存存在的问题

• 查询的 SQL 语句必须和查询缓存中的 SQL 语句完全相同，包括传入的字段和限制条件，否则就查询不到

  -- 相似的 SQL 语句，但是并不是完全相同的
  select name, age from T where id = 1;
  select name, age from T where id = 2;

• 表中存储的内容发生了更新，那么该表对应的查询缓存就会被完全清空，这样可能导致刚完成缓存立刻被清空

  表中的内容更新导致缓存清空，主要是因为 SQL 语句对应的结果变化了，所以只能够清空缓存，否则缓存的结果就是错误的

基于以上两个原因，通常都不会使用 MySQL 的查询缓存，只有在表内容长期不更新的情况下（如系统配置表），才会开启查询缓存

查询缓存的设置

① 查询缓存是否可用

show variables like 'have_query_cache';

② 查询缓存是否开启

-- 查询当前缓存的状态
show variables like 'query_cache_type';
-- 设置当前缓存的状态
set session query_cache_type=value;

• off（0）：表示完全关闭查询缓存，任何表都不可以使用
• on（1）：表示开启查询缓存，所有表都默认使用
• demand（2）：表示开启查询缓存，但是所有表默认不使用，可以根据需要指定 SQL 语句使用缓存

-- 强制指定 SQL 语句使用查询缓存
select SQL_CACHE * from T where id=10；

③ 查询缓存配置的大小

缓存的默认大小是 1M

-- 查询缓存大小
show variables like 'query_cache_size';
-- 设置缓存大小
set global query_cache_size = value;

④ 缓存的最大查询结果

-- 超过阈值的查询结果也是不会存储在查询缓存中的
show variables like 'query_cache_limit';
-- 设置最大查询缓存
set global query_cache_limit = value;

⑤ 查询所有和缓存相关的变量

show varibles like '%query_cache%'

分析器

分析器的功能

• 分析器主要功能就是对 SQL 语句进行分析，识别客户端需要完成的功能是什么

分析的详细过程

-- 样例1
select * from t where id=1;
-- 样例2
elect * from t where id=2;

首先，分析器会对 SQL 语句进行 词法分析，也就是分析每个词语的含义是什么。对于上面这个 SQL 语句，就会依次提取每个单词，然后分析其含义，比如 select 就表示这是个查询语句，然后 t 就表示查询的表是哪一张；

然后分析器会再对 SQL 语句进行 语法分析，也就是分析组成 SQL 语句的词语是否符合 SQL 的语法规则

• 如果符合语法规则，那么就会直接交给优化器进行优化
• 如果不符合语法规则，那么你就会收到 You have an error in your SQL syntax 错误，并且要尤其关注 use near 之后的部分，它将会提示你的 SQL 语句出错的部分

优化器

优化器的功能

• 优化器在知道 SQL 语句的含义之后，就会针对需要完成的功能进行优化，从而选择最优的执行方案
  • 使用不同的索引会导致查询的速度不同，优化器就会决定选择哪种索引，从而使查询速度最快
  • 多张表相联的时候，优化器会决定多张表的连接顺序，从而是查询速度最快

优化器具体如何选择索引，则涉及到索引的知识，暂时不在这里展开。不过 选择的原则始终都是保证在读取表中的内容的时候，尽可能减少扫描的行数

执行器

执行器的功能

• 执行器会对用户进行权限验证，也就是验证用户对该表、字段等内容是否存在访问权限
• 执行器会打开需要使用的表，根据表定义中使用的存储引擎去调用相应接口，最后依靠存储引擎执行相应功能

执行的详细过程

首先，执行器经从分析器中得知需要使用的表结构，所以会验证当前的用户对将要访问的表结构是否具有合理的权限

• 如果该用户对将要访问的表没有权限，那么就会返回没有权限的错误
• 如果该用户拥有相应的权限，那么执行器就会开始执行这个 SQL 语句

在权限验证成功之后，执行器就会去调用表定义中使用到的存储引擎，然后去调用对应的存储引擎接口，最后交由存储引擎执行相应的功能，读取相应的数据

补充内容

① 数据库文件的数据是存储在磁盘中的，所以在查询数据的时候需要存储引擎先从磁盘中读取内容，然后加载到内存中，然后从内存中查询到相应的数据；下次再次查询的时候，就只需要从内存中查询就可以了，不需要再读取磁盘了

Engine 层

基本功能

• Engine 层主要包括的就是存储引擎，负责数据的存储和提取
• Engine 层的主要特点在于其 插件式的表存储引擎，可以根据不同表的需要选择不同的存储引擎

常用引擎

MySQL 中使用最为广泛的引擎就是 InnoDB 引擎，但是还存在其他许多应用在不同场景下的引擎。这些引擎以 MyISAM 为代表，有许多和 InnoDB 不同的特点，所以需要了解除 InnoDB 以外的引擎的特点，并将其和 InnoDB 进行对比，了解其区别

InnoDB

注：InnoDB 引擎由第三方开发，后被 Oracle 收购成为官方引擎，现在是应用最为广泛的 MySQL 开源引擎

① InnoDB 引擎特性：

• InnoDB 引擎支持事务：常见的引擎中只有 InnoDB 是支持事务特性的
• InnoDB 引擎支持全局锁、表级锁、行锁
• InnoDB 引擎支持 MVCC（并发版本控制）
• InnoDB 引擎支持 redo log、binlog、undo log 三种日志
• InnoDB 引擎支持大部分形式的索引，并且采用索引组织表（聚集索引）的形式
  • 支持的 索引模型 有 B+树索引、哈希索引（自适应哈希）
  • 支持的 索引类型 有聚集索引（主键）、唯一索引、普通索引（二级索引）、全文索引、外键
    • 全文索引是在 5.6 之后的版本中才支持的，以前的旧版本是不支持全文索引的
    • 支持许多索引优化技术：联合索引、覆盖索引、前缀索引
  • 索引组织表意味着每张表都需要拥有唯一的主键，并且索引和数据是绑定在一起的
    • 如果没有设置主键，那么会检查表中是否存在唯一索引，如果存在那么就将唯一索引设置为主键
    • 如果唯一索引也没有，那么就会为每行生成一个 6B 的主键（ROWID）
• InnoDB 引擎支持自适应哈希、写缓冲、二次写等特性
  • 自适应哈希是对通过 B+ 树索引查询数据这种方式的进一步优化
  • 写缓冲又称为插入缓冲（change buffer / insert buffer）目的在于优化更新数据的性能
  • 二次写主要是防止数据落盘的时候 MySQL 宕机，从而出现数据丢失
• InnoDB 引擎默认将所有数据都存储在共享表空间中，也可以设置每张表独立存储
  • 共享表空间就是 ibdata1 这个文件
  • 每张表结构的定义文件是以 .frm 结尾的文件
  • 设置为独立存储的时候，每张表的数据存储在以 .idb 结尾的文件中

② InnoDB 引擎细节：

• MySQL 在 Windows 系统下是将 InnoDB 作为默认存储引擎的，其余所有系统都是将 MYISAM 作为默认引擎

MyISAM

① MyISAM 引擎特点

• MyISAM 引擎不支持事务，但是 OLAP（在线分析处理）操作比较快
• MyISAM 引擎只支持全局锁和表级锁
• MyISAM 引擎不支持 MVCC（并发版本控制）
• MyISAM 引擎仅支持 binlog，不支持 undo log、redo log
• MyISAM 引擎支持的索引类型和 InnoDB 几乎一致，但是不采用索引组织表的形式
  • 支持的索引模型仅有 B+ 树索引，不支持哈希索引
  • 支持的的索引类型和 InnoDB 基本没有区别，但是不支持外键
  • 不采用索引组织表意味着数据和索引是单独存放在不同的文件中，而不是绑定在一起的
• MyISAM 引擎可以采用 myisampack 压缩表中的数据，从而缩小数据库容量
  • 被压缩之后的表是只读的，不可以进行任何修改

② MyISAM 引擎细节

• MyISAM 引擎的缓存结构仅采用 LRU 算法存储索引的缓存，数据的缓存由操作系统来完成

Memory

此后，会专门详细介绍 Memory 引擎和 InnoDB 引擎的区别，这里仅简单概述

① Memory 引擎特点

• Memory 引擎依然不支持事务
• Memory 引擎也支持全局锁和表锁
• Memory 引擎将所有的数据全部存储在内存中，而不是存储在磁盘中
  • 数据全部存储在内存中的优点就是读取速度快，缺点就是宕机之后，数据难以恢复
  • 所以其主要的应用场景就是临时表的使用
• Memory 引擎支持大部分的索引
  • 支持的索引模型有 B+树索引、哈希索引：不同于其他引擎，Memory 默认使用哈希索引
• Memory 不支持 BLOB 和 TEXT 类型的字段，并且将 VARCHAR 类型当做 CHAR 类型处理

② Memory 引擎的细节

• 如果查询的中间结果集超过了 Memory 引擎预设的内存大小，那么 MySQL 就会启动 MyISAM 引擎
• 将中间结果集的内容全部存储在磁盘中，这样就会对中间结果集的查询速度产生影响

参考资料：

1. 《高性能 MySQL》：MySQL 架构和历史
2. 《MySQL 技术内幕：InnoDB 存储引擎》：MySQL 体系结构和存储引擎
3. 《MySQL 实战：45 讲》