MySQL笔记-基本架构

作者 |jaxer
来源 | WriteOnRead(ID：WriteOnRead)

说明

本文以及接下来的几篇 MySQL 笔记是本人在「极客时间」的专栏『MySQL实战45讲』和掘金小册「MySQL 是怎样运行的：从根儿上理解 MySQL」的学习笔记整理，并参考了一些其他材料加上个人的总结和思考。顺便推荐下这两个专栏。

基本架构

MySQL 基本架构示意图如下：

MySQL 大体可以分为两部分：Server 层和存储引擎层(功能跟日常开发中的 Service 层和与 DAO 层有点像，可以对比理解)。

Server 层

主要有连接器(Connector)、查询缓存(Cache)、分析器(Parser)、优化器(Optimizer)和执行器(Executor)等，包括了 MySQL 的大部分核心功能以及所有内置函数(日期、数学和加密函数等)，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。

存储引擎层

存储引擎(Storage Engine)层主要负责数据的存储和提取，它是直接和磁盘打交道的，以插件形式存在，例如 InnoDB、MyISAM、Memory 等多种存储引擎。

从 MySQL 5.5.5 开始，InnoDB 成为了默认的存储引擎。

Server 层

连接器

主要功能：跟客户端建立(TCP)连接、获取权限、维持和管理连接。

若用户认证通过，连接器会查询权限列表获取该用户的权限，之后该连接的权限判断都基于此(因此，一个用户建立连接后，即使被修改了权限也不会影响已存在连接的权限，只有重新建立连接后才生效)。

客户端建立连接示例(分别为失败和成功)：

# 连接失败（密码错误）
$ mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

# 连接成功
$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

进入 MySQL 后可以用如下命令查看连接状态：

# 查看连接状态
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  6 | root | localhost | NULL | Sleep   |   81 |          | NULL             |
|  7 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

在 Command 列中，Sleep 表示该连接是空闲的。

连接成功后是有超时时间的，若太长时间没有操作会断开连接，由参数 wait_timeout 控制，默认为 8 小时，

查询缓存

主要功能：缓存查询结果。

连接建立之后，就可以进行查询了。

在一个查询语句中，会先到缓存中查询之前是否查询过该语句，若存在则直接返回对应的结果;否则继续执行后面的流程。

PS: 此处理流程可以类比我们在项目中使用 Redis 等作为缓存的操作，即先查缓存，再查 DB。

也可以通过使用 SQL_CACHE 显式指定使用查询缓存(这里的 id 并非主键)，例如：

SELECT SQL_CACHE * FROM t1 WHERE id=10;

查询缓存的优缺点：

1. 优点：查询命中缓存时效率很高。

2. 缺点：缓存失效非常频繁，只要有对一个表的更新，该表所有的查询缓存都会被清空。

由于上述优缺点，可以发现缓存适用于静态表或更新较少的表，对于更新较频繁的表并不适用。值得一提的是，MySQL 8.0 版本已删除了查询缓存功能，可见该功能比较鸡肋。

分析器

主要功能：对 SQL 语句进行词法分析和语法分析。

1. 词法分析：分词操作，由于我们传递给 MySQL 的 SQL 语句实质上就是一个字符串，MySQL 需要将其拆分成一个个的分词(语法树)并进行识别，例如识别“SELECT”、“UPDATE”等关键字，将 t1 识别为一张表，将 id 识别为一列等。

2. 语法分析：拿到词法分析的结果，并根据语法规则判断 SQL 语句是否合法。若语法错误，则会收到如下错误提示：

You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near ...

优化器

主要功能：优化 SQL 语句。

经过了分析器之后，MySQL 已经知道了我们提交的 SQL 语句是干嘛的。但为了提高执行效率，它并非完全按照我们的 SQL 语句执行，而要进行一系列优化。例如，当表中有多个索引时决定使用哪个索引;多表关联(JOIN)查询时决定表连接的顺序等等。

PS: 有点类似于 JVM 执行 Java 代码时的操作。即，JVM 并非完全按照代码的先后顺序来执行的，它会调整一些代码的执行顺序以提高效率，只是保证最终结果与代码顺序执行的效果一致。

执行器

主要功能：执行 SQL 语句。

MySQL 知道了我们要做什么，并且进行了优化，接下来就要开始执行了。执行之前，会判断你对该表是否有查询的权限，若有权限则继续执行;否则会返回如下错误(这里以 SELECT 操作为例，其他类似)：

SELECT command denied to user 'user'@'localhost' for table 't1'

为什么到这一步才进行权限检查呢?

是因为有时候 SQL 语句要操作的表不只是 SQL 字面上的那些(例如触发器要在执行过程中才能确定)，因此权限检查在这里进行。

存储引擎

以上述 SELECT 语句为例，执行步骤如下：

1. 调用 InnoDB 引擎接口取 t1 表的第一行，判断 id 是否为 10，若不是则跳过;否则将这一条记录存在结果集中;

2. 调用存储引擎接口读取“下一行”，判断逻辑同步骤 1，直至读取到表的最后一行;

3. 执行器将上述遍历过程中所有满足条件的记录作为结果集返回给客户端。

MySQL 查看所有存储引擎：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

其中 Support 表示该引擎是否可用(DEFAULT 表示默认值);Comment 是描述信息;Transactions 表示是否支持事务;XA 表示是否支持分布式事务;Savepoints 表示是否支持回滚。

其中最为常用的就是 InnoDB 引擎，而且它也是默认的。其他常见的还有 MyISAM 和 Memory 引擎，这三种引擎简单对比如下：

 

 

小结

MySQL 的整体架构主要分为两部分：Server 层和存储引擎层。

Server 主要有连接器、查询缓存、分析器、优化器和执行器等，包括了 MySQL 的大部分核心功能以及所有内置函数，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。

存储引擎层：负责数据的存储和提取，以插件形式存在，例如 InnoDB (MySQL 5.5.5 以后默认的存储引擎)、MyISAM、Memory 等多种存储引擎。

PS: 发现一个现象，许多东西在学的时候觉得不难，但是如果自己不去做笔记的话就很容易忘记。因此就需要用自己的语言去表达出来，这样更能促进思考，也能进一步“内化”到自己的知识体系中。