likes
comments
collection
share

MySQL系列（1）— 基础架构

作者站长头像
站长
· 阅读数 10

概述

MySQL 系列将深入的学习研究 MySQL 的一些底层原理和设计，便于更好的理解和使用MySQL，本系列将主要从以下几个方面深入学习：MySQL体系结构、Innodb存储引擎、索引与SQL优化、锁、事务、高可用与性能优化、日志等。一些基础的表设计、增删改查SQL、数据库范式等不会涉及。

这篇文章我们先从整体上来了解下MySQL的架构设计，对MySQL有个整体的认识。

本地使用的MySQL版本：5.7.29

本系列文章主要参考的一些资料：

  • 《MySQL技术内幕 InnoDB存储引擎 第2版》
  • 《MySQL是怎样运行的：从根儿上理解 MySQL》
  • 《极客时间 — MySQL实战45讲》
  • 《MySql 5.7 中文文档》

MySQL 体系结构

逻辑架构

首先来看看官方给出的 MySQL体系结构图：

MySQL系列（1）— 基础架构

可以知道 MySQL 由以下几部分组成：

  • 连接池组件
  • 管理服务和工具组件
  • SQL 接口组件
  • 查询分析器组件
  • 优化器组件
  • 缓冲组件
  • 插件式存储引擎
  • 物理文件

从大的层次上可以分为四层：

  • 第一层 连接层：主要负责连接处理、身份验证、安全性等，一般 C/S 架构都会有这一层。

  • 第二层 核心服务层：主要有查询缓存、分析器、优化器、执行器等，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

  • 第三层 存储引擎层：这一层是底层数据存取操作实现部分，由多种存储引擎共同组成。服务器通过API与存储引擎通信，API规避了不同存储引擎的差异，不同存储引擎也不会互相通信。

  • 第四层 数据存储层：文件系统存储数据，存放数据文件、日志文件等，完成与存储引擎的交互。

客户端连接

我们的应用要去访问 MySQL数据库，必须要依赖一个数据库驱动，这个MySQL驱动，它会在底层跟数据库建立网络连接，有网络连接，才能去发送请求给数据库服务器，才能基于这个连接去执行各种各样的SQL语句。

对于Java、PHP、Perl、.NET、Python、Ruby等各种常见的编程语言，MySQL都会提供对应语言的MySQL驱动，让各种语言编写的系统通过MySQL驱动去访问数据库。

比如在Java中，需要在 pom.xml 中引入 mysql-connector-java 的驱动依赖：

<dependency>
    <artifactId>mysql-connector-java</artifactId>
    <groupId>mysql</groupId>
    <version>5.1.47</version>
</dependency>

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

下面我们以一条SQL查询语句的执行过程来看看这些组件分别有什么作用。

SQL查询执行路径

看下面的SQL查询执行路径图，总的来说一条SQL查询语句将经过如下过程得到查询结果：

    1. 客户端发送一条SQL查询给服务器。
    1. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
    1. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
    1. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
    1. 将结果返回给客户端。

MySQL系列（1）— 基础架构

客户端连接池

首先要知道，我们部署在容器中的应用程序是有多个工作线程来处理用户请求的，多个线程都会去获取一个数据库连接来访问数据库。不能每次都创建一个新的数据库连接，用完然后销毁，这样效率非常低下，因此客户端需要一个数据库连接池，每次从连接池里拿一个连接来处理SQL请求，用完之后又放回连接池中，避免频繁创建销毁数据库连接。

常见的数据库连接池有 DBCP、C3P0、Druid 等。

MySQL连接器

客户端请求连接数据库时，连接器就会负责跟客户端建立连接、获取权限、维持和管理连接。MySQL服务器端也会有一个连接池，因为一般都会有多个系统与MySQL建立很多个连接，MySQL通过这个连接池去维护与客户端的数据库连接。

除此之外，连接器还会根据请求的账号和密码，进行安全认证，库表权限认证。如果用户名或密码不对，就会收到一个"Access denied for user"的错误。如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这也意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

连接完成后，如果没有后续的动作，这个连接就处于空闲状态。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

对于一个 MySQL连接，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。可以使用 SHOW FULL PROCESSLIST 命令查看当前连接的状态（Command列）。在一个查询的生命周期中，状态会变化很多次。

MySQL官方手册中给出了如下状态：

  • Sleep：处于空闲状态，线程正在等待客户端发送新的请求。
  • Query：线程正在执行查询或者正在将结果发送给客户端。
  • Locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。
  • Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
  • Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做 GROUP BY 操作，要么是文件排序操作，或者是 UNION 操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。
  • Sorting result：线程正在对结果集进行排序。
  • Sending data：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

MySQL系列（1）— 基础架构

客户端/服务器通信

MySQL客户端和服务器之间的通信协议是半双工的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。

这种协议让 MySQL通信简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，没法进行流量控制，一旦一端开始发送消息，另一端要接收完整个消息才能响应它。

客户端用一个单独的数据包将查询传给服务器，如果查询太大，服务端会拒绝接收更多的数据并抛出相应错误（可通过max_allowed_packet参数控制请求大小）。一旦客户端发送了请求，它能做的事情就只是等待结果了。

相反的，一般服务器响应给客户端的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。所以必要的时候一定要在查询中加上 LIMIT 限制返回数量。

客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。

SQL接口

数据库连接池中的连接接收到了网络连接，比如客户端发送一条SQL查询语句，此时会有一个MySQL工作线程监听请求，从连接中读取请求数据，解析出客户端发送的SQL语句，然后再转交给SQL接口处理。

我们可以很容易理解一段SQL语句的意图，但数据库要执行各种增删改查SQL是非常复杂的，所以MySQL内部首先提供了一个SQL接口组件，它是一套执行SQL语句的接口，专门执行客户端发送给MySQL的各种增删改查的SQL语句。

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。

如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行，这个效率会很高。

但大多数情况下建议不要使用查询缓存，因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能缓存起来的结果还没使用，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

查询解析器和预处理器

接下来，SQL接口该怎么执行SQL呢？这个时候就需要解析器来拆解这个SQL，生成一棵对应的“解析树”，将其变成MySQL能理解的东西。

首先会进行词法分析，SQL语句是由多个字符串和空格组成的，MySQL 需要识别出里面的字符串分别是什么，代表什么。接着进行语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这SQL语句是否满足 MySQL 语法。

例如，它将验证是否使用错误的关键字，使用关键字的顺序是否正确，引号是否能前后正确匹配等。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒。

预处理器则根据一些MySQL规则进一步检查解析树是否合法，这里将检查数据表和数据列是否存在，解析名字和别名，看看它们是否有歧义。下一步预处理器会验证是否有表权限、字段权限。

查询优化器

一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

不过我们需要知道的是，查询优化器选择的最优执行计划可能并不是最好的，有多种原因会导致MySQL优化器选择错误的执行计划，例如下面的一些：

  • 统计信息不准确，MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。

  • 执行计划中的成本估算不等同于实际执行的成本，比如MySQL层面并不知道哪些页面在内存中、哪些在磁盘上，所以查询实际执行过程中到底需要多少次物理I/O是无法得知的。

  • MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。

  • MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。

  • 优化器有时候无法去估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。

MySQL的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划，下面列举了一部分MySQL能够处理的优化类型：

  • 重新定义关联表的顺序：数据表的关联并不总是按照在查询中指定的顺序进行，关联表的顺序是很重要的一个优化。

  • 将外连接转化成内连接：外连接可能会转化为内连接，然后调整表关联顺序。

  • 使用等价变换规则：MySQL可以使用一些等价变换来简化并规范表达式。

  • 优化COUNT()、MIN()和MAX()：MySQL会利用存储引擎或一些索引的特性来优化这类表达式。

  • 预估并转化为常数表达式：当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。

  • 覆盖索引扫描：当索引中的列包含所有查询中的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行。

  • 子查询优化：在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。

  • 提前终止查询：在发现已经满足查询需求的时候，就能够立刻终止查询。比如使用了LIMIT子句的时候。

  • 等值传播：如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上。

  • 列表IN()的比较：MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。

我们可以在一个查询SQL前添加 explain extended，然后在末尾添加 show warnings 就可以看到重构后的查询语句。

EXPLAIN EXTENDED 

select f from xxxx;
	
SHOW WARNINGS;

执行器与存储引擎

查询优化器已经选择好最优的查询路径了，接下来就需要交给底层的存储引擎去真正的执行。这时候就需要执行引擎根据执行计划，按照一定的顺序和步骤，不停的调用存储引擎的各种接口去完成SQL语句的执行计划，大致就是不停的更新或者提取—些数据出来。

开始执行的时候，要先判断一下对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

存储引擎其实就是执行SQL语句的，他会按照一定的步骤去查询内存缓存数据，更新磁盘数据，查询磁盘数据等等。MySQL支持多种存储引擎，比如常见的 InnoDB、MyISAM、Memory 等。

存储引擎

MySQL 数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎，存储引擎是底层物理结构的实现，负责数据的存储和提取。每个存储引擎都有各自的特点，可以根据具体的应用建立不同存储引擎表。

常用的存储引擎有 InnoDB、MyISAM、Memory 等，最常用的存储引擎是 InnoDB，它从 MySQL 5.5.8 版本开始成为默认的存储引擎。

建表

执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB。也可以在 create table 时使用 engine=xxx 来指定使用的引擎。

创建表时，不管是什么存储引擎，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。例如创建一个名为 MyTable 的表，MySQL会在 MyTable.frm 文件中保存该表的定义。

可以使用 show table status like <table> 命令显示表的相关信息，也可以查询 information_schema 中对应的表。

MySQL系列（1）— 基础架构

一些关键的信息如：

  • Name：表名。
  • Engine：表的存储引擎类型。
  • Row_format：行的格式。Dynamic 的行长度是可变的，一般包含可变长度的字段，如VARCHAR或BLOB。
  • Rows：表中的行数。对于InnoDB，该值是估计值，对于其他一些存储引擎，该值是精确的。
  • Avg_row_length：平均每行包含的字节数。
  • Data_length：表数据的大小(以字节为单位)。
  • Index_length：索引的大小（以字节为单位)。
  • Auto_increment：下一个AUTO_INCREMENT的值。
  • Collation：表的默认字符集和字符列排序规则。

InnoDB 存储引擎

InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特点是行锁设计、支持外键，并支持类似于Oracle 的非锁定读，即默认读取操作不会产生锁。

InnoDB存储引擎将数据放在一个逻辑的表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。InnoDB可以将每个表的数据和索引存放在单独的文件中。

对于表中数据的存储，InnoDB存储引擎采用了聚集（clustered）的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

InnoDB采用多版本并发控制（MVCC）来支持高并发，并且实现了SQL标准的四种隔离级别。默认级别是REPEATABLE READ（可重复读），同时，RR级别通过间隙锁（next-keylocking）的策略防止幻读（phantom）的出现。

除此之外，InnoDB储存引擎还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hashindex）、预读（read ahead）等高性能和高可用的功能。

MyISAM 存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，且有一个缺陷就是崩溃后无法安全恢复，主要面向一些OLAP数据库应用。此外，MyISAM存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，而不缓冲数据文件。

MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD 和 .MYI 为扩展名。MyISAM表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。从MySQL 5.0版本开始，MyISAM默认支持256TB的单表数据，这足够满足一般应用需求。

Memory 存储引擎

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用的。Memory表至少比MyISAM表要快一个数量级，因为所有的数据都保存在内存中，不需要进行磁盘I/O。如果数据库重启或发生崩溃，Memory表的结构还会保留，但数据会丢失。

Memory表支持Hash索引，因此查找操作非常快。Memroy表是表级锁，因此并发写入的性能较低。它不支持BLOB或TEXT类型的列，并且每行的长度是固定的，所以即使指定了VARCHAR列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费。

如果MySQL在执行查询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是Memory表。如果中间结果太大超出了Memory表的限制，或者含有BLOB或TEXT字段，则临时表会转换成MyISAM表。

要注意Memory表和临时表的区别，临时表是指使用 CREATE TEMPORARY TABLE 语句创建的表，它可以使用任何存储引擎，因此和Memory表不是一回事。临时表只在单个连接中可见，当连接断开时，临时表也将不复存在。

存储引擎对比

MySQL的存储引擎有很多种，不过一般我们选择InnoDB存储引擎即可，如果非要用到一些InnoDB不具备的特性的引擎，我们也可以用其它方案来代替，例如缓存Redis、消息中间件等。

可以使用 SHOW ENGINES 命令查看当前 MySQL 支持的存储引擎：

MySQL系列（1）— 基础架构

如下是常见存储引擎功能的一些对比：（参考官方文档）

MySQL系列（1）— 基础架构

系统文件存储层

系统文件存储层主要是负责将数据库的数据和日志存储在系统的文件中，同时完成与存储引擎的之间的交互，是文件的物理存储层。

主要的一些文件有：数据文件、日志文件、配置文件等。

数据文件

可以使用 SHOW VARIABLES LIKE '%datadir%'; 命令查看数据文件的目录，在数据文件目录下我们可以看到如下的一些文件。

  • db.opt：记录这个数据库默认使用的字符集和校验规则。
  • ib_logfile0、ib_logfile1：Redo log 日志文件。
  • .frm：存储表相关的元数据信息，包含表结构的定义信息等，每一张表都会有一个frm文件与之对应。

InnoDB数据文件：

  • .ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件
  • .ibdata：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件

MyISAM数据文件：

  • .MYD：主要用来存储表数据信息
  • .MYI：主要用来存储表数据文件中任务索引的数据树

日志文件

常用的日志包括：错误日志、二进制日志、查询日志、慢查询日志和事务redo日志、中继日志等等。

可以通过 SHOW VARIABLES LIKE '%\_log_%'; 查询当前MySQL日志使用情况。具体的一些日志文件会在后面的文章中详细介绍。

MySQL系列（1）— 基础架构

配置文件

用于存放MySQL所有的配置信息的文件，比如：my.cnf、my.ini 等。

可以使用 mysql --help|grep my.cnf 查看MySQL配置文件的位置：

MySQL系列（1）— 基础架构

可以看到MySQL是以 /etc/my.cnf -> /etc/mysql/my.cnf -> /usr/etc/my.cnf -> ~/.my.cnf 的顺序读取配置文件的，如果有相同的配置，会以读取到的最后一个配置文件中的参数为准。