MySQL玩转指南：探秘Server层组件及权限校验实践

本文为《MySQL归纳学习》专栏的第一篇文章，同时也是关于《MySQL查询》知识点的开篇文章。

本文将带你揭开MySQL Server层的神秘面纱，逐一剖析连接器、查询缓存、分析器、优化器、执行器等关键组件的功能和作用。同时，还将重点介绍这些组件在权限校验方面的精彩表现。

首先来看一下这张思维导图，对本文内容有个直观的认识。

接下来进入正文。

MySQL SQL语句执行流程示意图：

从中你可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等，未来我们接触索引失效的时候，就知道为什么不允许在where条件中对字段使用函数，），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

连接器

连接器负责建立和管理与客户端的连接，获取权限并管理连接。连接命令一般是这么写的：

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

为了安全起见，在交互对话中输入密码比直接在命令行中写密码更可靠。请避免在命令行中明文输入密码，尤其是在连接生产服务器时。

使用客户端工具（如mysql命令）连接到MySQL服务端后，在完成经典的 TCP 握手后，连接器会进行身份认证，使用你提供的用户名和密码进行验证。

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

对于已经建立的连接，即使管理员修改了该用户的权限，也不会影响已存在连接的权限。只有在新建的连接中才会使用新的权限设置。

连接完成后，如果没有其他操作，连接处于空闲状态。可以使用"show processlist"命令查看当前连接列表。当"Command"列显示为"Sleep"时，表示有一个空闲连接。

如果客户端长时间没有活动，连接器会自动断开连接。这个时间由"wait_timeout"参数控制，默认为8小时。当连接断开后，如果客户端再发送请求，会收到一个错误提示："Lost connection to MySQL server during query"。此时，需要重新连接并重新执行请求。

在数据库中，长连接指的是在连接成功后，如果客户端持续发送请求，将一直使用同一个连接。而短连接是指每次执行少量查询后就断开连接，下次查询时重新建立连接。建议采用长连接。

然而，如果全部使用长连接，可能会发现MySQL占用内存增长得很快。这是因为MySQL在执行过程中临时使用的内存是与连接对象相关联的。这些资源只有在连接断开时才会释放。因此，如果长连接积累过多，可能会导致内存占用过高，最终被系统强制终止（OOM），表现为MySQL异常重启。

怎么解决这个问题呢？你可以考虑以下两种方案。

• 建议定期断开长连接。使用一段时间后或在程序中执行占用大量内存的查询后，断开连接，并在需要查询时重新连接。（在实际开发中，我们一般引入连接池库，如HikariCP；以及根据项目需求和数据库服务器性能进行调优配置连接池的参数）
• 如果您使用的是MySQL 5.7或更新版本，可以在执行较大操作后使用"mysql_reset_connection"来重新初始化连接资源。这个过程无需重新连接或重新进行权限验证，但会将连接恢复到初始创建时的状态。

查询缓存

连接建立后，可以执行SELECT语句。如最开始的图片所示，虽然执行箭头同时指向查询缓存和分析器，但其实会优先执行查询缓存操作。

看之前是否执行过相同的语句。之前执行过的语句及其结果可能以键值对的形式直接缓存在内存中。键是查询语句，值是查询结果。如果查询能够直接在缓存中找到键，对应的结果将直接返回给客户端，就不会再走后续的流程。如果语句不在查询缓存中，将继续执行后续的查询过程，并将执行结果存入查询缓存中。

虽然查询缓存能够提高效率，直接返回结果，但在大多数情况下，我建议不要使用查询缓存。为什么呢？因为查询缓存往往会带来更多的弊端，弊大于利。

首先我们来看一下查询缓存的配置方式：

查看缓存是否开启：

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set, 1 warning (0.00 sec)

my.cnf加入以下配置，重启 MySQL 开启查询缓存

query_cache_type=1
query_cache_size=600000

MySQL执行以下命令也可以开启查询缓存

set global  query_cache_type=1;
set global  query_cache_size=600000;

如上，开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。查询条件包括查询语句、数据库、客户端协议版本等信息。然而，任何查询中的字符差异、用户自定义函数、存储函数、用户变量、临时表以及MySQL系统表等都会导致缓存不命中。此外，如果涉及的表（数据或结构）发生变化，与这些表相关的缓存数据也会失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，在启用查询缓存时需要谨慎，特别是对于写密集的应用程序。如果使用查询缓存，要合理控制缓存空间的大小，一般设置为几十MB比较适合。此外，还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存：

sql_cache 表示查询使用缓存；sql_no_cache 表示当前查询不使用缓存；

select sql_no_cache count(*) from usr;

注意：执行查询语句的时候，会先查询缓存。不过，从 MySQL 5.7.20 开始，查询缓存已弃用，并在 MySQL 8.0中删除，因为这个功能不太实用。

分析器

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

1、词法分析。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

2、语法分析。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如下面这个语句 select 少打了开头的字母“s”。

mysql> elect * from t where ID=1;
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 'elect * from t where ID=1' at line 1

一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。

扩展： 如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。 该问题在分析阶段被发现。 分析器进行语法分析、词法分析，检查 sql 的语法顺序等得到解析树， 然后预处理器进一步检查解析树的合法。比如: 数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。

优化器

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

优化器的作用是在表中存在多个索引时，决定使用哪个索引；或者在语句中涉及多表关联（join）时，确定表的连接顺序。

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

mysql> 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 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

在执行之前，首先要判断是否具有对表T的查询权限。如果没有权限，将返回相应的错误信息。在具体实现上，如果查询命中了查询缓存，权限验证会在查询缓存返回结果时进行。此外，在优化器之前还会调用预检查（precheck）进行权限验证。

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果具有权限，执行器将打开表并继续执行。在打开表时，执行器会根据表的存储引擎定义，使用引擎提供的接口进行操作。

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

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

在MySQL中，执行带有索引的语句的逻辑与无索引的表类似。对于有索引的表，首先调用的是引擎中定义的"取满足条件的第一行"接口，然后通过循环调用"取满足条件的下一行"接口来获取数据行。这些接口是存储引擎提供的。

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

需要注意的是，在某些情况下，执行器调用一次引擎，引擎内部可能会扫描多行数据，因此引擎扫描的行数与"rows_examined"字段并不完全相同。

参考后文排序内容讲解时提到，rows_examined 是“server层调用引擎每取一行的时候”加1；当引擎内部自己调用时，读取行数据， rows_examined 并不加1。

除此之外还有一个例子可以说明：加索引的时候，也要扫描全表，但如果是 inplace DDL，你会看到扫描行数是0，也是因为这些扫描动作都是引擎内部自己调用的。

归纳总结

MySQL的框架有几个组件, 各是什么作用?

1. 连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
2. 查询缓存：查询请求先访问缓存(key 是查询的语句，value 是查询的结果)。命中直接返回。不推荐使用缓存，更新会把缓存清除(关闭缓存：参数 query_cache_type 设置成 DEMAND)。
3. 分析器：对 SQL 语句做解析，判断sql是否正确。
4. 优化器：决定使用哪个索引，多表关联（join）的时候，决定各个表的连接顺序。
5. 执行器：执行语句，先判断用户有无查询权限，使用表定义的存储引擎。

you have an error in your SQL syntax 这个保存是在词法分析里还是在语法分析里报错?

语法分析

扩展：字段不存在，则是在词法分析里判断的。

执行一条SQL查询语句通常会经历以下几个时机进行权限校验

1. 连接器阶段：

   1. 身份验证：在连接建立时，连接器会根据提供的用户名和密码进行身份验证，验证用户是否具有连接数据库的权限。

2. 分析器（Parser）权限验证：

   1. 表级权限验证：分析器在解析查询语句时需要验证用户是否具有对所涉及的表的查询、插入、更新、删除等操作的权限。
   2. 数据库级权限验证：分析器还需要验证用户对所使用的数据库的访问权限，包括创建、修改、删除数据库的权限。

3. 执行器（Executor）权限验证：

   1. 行级权限验证：执行器在执行查询操作时需要验证用户对每一行数据的访问权限，以确保用户只能访问其具有权限的数据行。
   2. 特殊权限验证：调用存储过程和执行存储过程中所涉及的操作的权限。

4. 查询缓存返回：

   1. 如果查询命中了查询缓存，权限验证会在查询缓存返回结果时进行。
   2. 因为查询缓存前只经历了连接器，只是校验了账号密码是否有连接权限，但是并没有验证用户是否拥有该语句中涉及到的对象查询权限。
   3. 此外，MySQL的权限管理是动态的，意味着在查询被缓存后，用户的权限可能会发生变化。如果在缓存返回结果时不进行权限验证，那么即使用户在执行查询之前没有权限，但在缓存返回结果时获得了相应的权限，仍然可以获取到结果。通过在缓存返回结果时进行权限验证，可以确保用户在获取结果时仍然需要具备相应的权限。