Hello，我是海星。

学习 MySQL 第一步，不是去学 select 、update，而是先要对他的整体架构设计有个大概的了解，先高屋建瓴，然后逐一攻破。

和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

下边是 MySQL 官网中 8.0 版本的一个图，我们展开看一下，对 MySQL 整体架构和可插拔的存储引擎先有个总体回顾。

要使用 MySQL，第一步肯定要与他进行连接。

最上层就是一些客户端和连接服务，包含本地 socket 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的通信。主要完成一些类似于建立连接、授权认证、及相关的安全方案。

# -h 指定 MySQL 服务得 IP 地址，如果是连接本地的 MySQL服务，可以不用这个参数；
# -u 指定用户名，管理员角色名为 root；
# -p 指定密码，如果命令行中不填写密码（为了密码安全，建议不要在命令行写密码），就需要在交互对话里面输入密码
mysql -h$ip -u$user -p

输入密码后，就成功建立了连接，我们可以用 show processlist 查看当前所有数据库连接的 session 状态

连接状态，一般是休眠（sleep），查询（query），连接（connect），如果一条 SQL 语句是query状态，而且time时间很长，说明存在问题

其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

当然，MySQL 对连接数量也是有限制的，最大连接数由 max_connections 参数控制

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

第二层架构完成了大部分的核心功能， 包括查询解析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等

第一步的连接建立后，我们就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

对一个表的更新，就会把该表上的所有查询缓存清空，所以更新比较频繁的表，查询缓存的命中率就极低，所以不建议使用，官方已经在 8.0 版本移除该功能了。

之前版本的 MySQL 也提供“按需使用”的方式。我们可以将参数 query_cache_type 设置成 DEMAND，这样对默认的 SQL 语句就都不使用查询缓存。

Note

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。

解析器会做如下两件事情。

• 第一件事情，词法分析。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

• 第二件事情，语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果我们输入的 SQL 语句语法不对，就会在解析器这个阶段报错。比如，我下面这条查询语句，把 from 写成了 form，这时 MySQL 解析器就会给报错。

mysql> select * form user;
ERROR 1064 (42000): 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 'form user' at line 1

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。比如重写查询、决定表的读取顺序，选择合适的索引等

比如你执行下面这样的语句，这个语句是执行两个表的join：

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。

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

• MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。
• 开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误
• 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

select * from T where ID=10;

比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

• 至此，这个整个语句就执行完成了。一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

用于接收客户端发送的各种 SQL 命令，返回用户需要查询的结果，比如 DML、DDL、存储过程、视图、触发器这些

存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。

不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互。

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

1. MySQL 客户端通过协议与 MySQL 服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析（MySQL 8.0 已取消了缓存）
2. 有一系列预处理，比如检查语句是否写正确了，然后是查询优化（比如是否使用索引扫描，如果是一个不可能的条件，则提前终止），生成查询计划，然后查询引擎启动，开始执行查询，从底层存储引擎调用 API 获取数据，最后返回给客户端。怎么存数据、怎么取数据，都与存储引擎有关。
3. 然后，MySQL 默认使用的 BTREE 索引，并且一个大方向是，无论怎么折腾 sql，至少在目前来说，MySQL 最多只用到表中的一个索引。

• 《高性能 MySQL》
• 《MySQL 实战 45 讲》