MySQL 基础技术（一） —— MySQL 是如何查询的？

古城微笑少年丶 2023-09-29 09:50 4阅读 0赞

## 一、引子 ##

在日常工作中，我们执行了一个简单的查询语句：

select * from <table_name> where id = 647;

> 那么，在 `MySQL` 内部会如何工作呢？

## 二、架构 ##

首先，`MySQL` 架构可以分为 **Server层** 和 **Engine层**两部分。

### Server（服务）层： ###

涵盖了 `MySQL` 上层核心服务功能。  
包括 “连接器”、“查询缓存”、“分析器”、“优化器”、“执行器”等等。  
以及内置功能函数（数学、时间计算等等），触发器、视图等等跨存储引擎的功能都在 Server 层实现。

### Engine（引擎）层： ###

主要负责数据的存储和提取等底层服务。  
其实现是插件式的，MySQL 支持 `InnoDB`、`MyISAM`、`Memory` 等多个存储引擎。  
目前，最常用的是 InnoDB 存储引擎。  
而从 MySQL 5.5.5 版本开始，`InnoDB` 成为了默认存储引擎。

可以使用 `show engines;` 命令来查看各个引擎。

show engines;

> 几个常用底层存储引擎的简单区别：  
> InnoDB：支持事务，支持行锁，支持外键。  
> MyISAM：不支持事务，只支持表锁，不支持外键。  
> Memory：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 MySQL 重新启动时丢失。

--------------------

## 三、架构图解 ##

![fee9e86b6de0fd1143b91fa51ee1f3c5.png][]

接下来我们依次解释下各个组件：

### 1.连接器： ###

顾名思义，连接器负责跟客户端建立连接、获取权限、维持和管理链接的。

常用命令如下：

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

在完成 TCP 握手后，连接器会基于用户名和密码来验证身份。  
验证通过会查询出当前用户的权限。  
（这代表：即使修改了用户权限，也只会影响下次连接时的权限。之前连接过的权限依然是旧的权限）

执行 `show processlist;` 可以查询当前的连接状态。

show processlist;

如果客户端长时间没有操作，连接器会自动断开。这个时间是由 wait\_timeout 控制的，默认是8小时。

### 2.查询缓存 ###

建立完连接之后，会优先查询有没有缓存。  
在打开查询缓存的前提下，  
每次执行过的 `select` 语句会以 `Key` \- `Value` 的形式保存在内存中。  
如果缓存命中，就会直接返回结果给客户端。

> 但是查询缓存往往弊大于利，得看场景使用。

查询缓存的失效非常频繁，只要表有更新，那么表上所有的查询缓存都会被清空。  
大大降低了查询缓存的命中率，还牺牲了MySQL的部分性能。  
除非是一种更新频率较低的静态表，可以打开查询缓存。（可以用SQL\_CACHE显示制定。 如：select SQL\_CACHE \* from <table\_name>）  
更新频率比较高的表建议不要使用查询缓存。（query\_cache\_type 设置成 DEMAND）

> 注意：  
> MySQL 8.0 开始，已经废弃查询缓存功能。  
> 因为缓存老是会被 update/insert/delete 操作清除掉，缓存了还没来得及用就又没了。

### 3.分析器 ###

#### 词法分析 ####

首先，会进行词法分析。  
将一个完整的SQL语句，拆分成语句类型（select? insert? update? ...）、表名、列名等等。

#### 语法分析 ####

其次，会进行语法分析。  
判断 SQL 语句是否符合 MySQL 语法。  
如果错误，会报出下面的错误：  
`ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...`  
这时，我们只要修正 use near 后面的语句即可。

### 4.优化器 ###

上一步，分析器通过“词法分析”、“语法分析”解析出这条 SQL 具体要做的事。  
优化器，会基于这条 SQL 来分析具体要使用哪个索引方案。

具体可以使用 `explain <sql>;` 命令来分析 MySQL 的索引选择。  
这个命令在优化 MySQL 查询效率时会经常用到。

例如：

explain select * from <table_name> where id = 647;

会分析出具体使用哪个索引方案，是否符合我们预期。  
如果不符合我们的预期，可以使用 force index 命令来强制使用某个索引。（绝大部分场景下，MySQL选择的都是最优的）

### 5.执行器 ###

分析器知道了 SQL 要做什么，  
优化器明确了 SQL 需要用哪个索引方案。  
执行器才会真正的基于索引方案，开始执行查询语句。

执行之前，会判断一下当前连接的用户有无该表的权限。  
如果有权限，就会根据表的 Engine 选择来调用对应的引擎接口。

举例：  
user\_info 表的存储引擎是 `InnoDB`。

select * from user_info where name = "647";

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

1.  调用 innoDB 引擎接口获取表的第一行，判断 `name` 是否等于 `647`。如果不是，跳过。如果是，将结果保存。
2.  调用 innoDB 引擎接口获取表的下一行，重复相同逻辑，一直到表的最后一行。
3.  将所有满足条件的结果集返回给客户端。

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

1.  调用 innoDB 引擎接口获取索引树（B+树），基于索引树快速查到 `name` 等于 `647` 的所有主键id。
2.  将所有满足条件的组件 id，回主表查详细信息。（这个操作称为“回表”）
3.  将所有满足条件的结果集返回给客户端。

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg_size_19_color_FFFFFF_t_70_g_se_x_16][]  
链接：https://www.jianshu.com/p/06cc23874950

[fee9e86b6de0fd1143b91fa51ee1f3c5.png]: https://img-blog.csdnimg.cn/img_convert/fee9e86b6de0fd1143b91fa51ee1f3c5.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg_size_19_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/2920e584fc684dcba3544668c480a763.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAamF2YeaZtOWkqei_h-WQjg==,size_19,color_FFFFFF,t_70,g_se,x_16