【MySQL】MySQL的基础架构及查询语句的执行流程

1、MySQL的架构组成

总体来说，MySQL可以分为Server层和存储引擎层。

Server层包括连接器、查询缓存、分析器、优化器、执行器等部分，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图（指虚拟表视图）等。该部分涵盖了MySQL的大多数核心功能，以及所有的内置函数。

存储引擎层负责数据的存储和提取，采用插件式架构，支持InnoDB、MyISAM、Memory等多个储存引擎。自5.5版本以后，InnoDB是其默认储存引擎。

1.1、连接器

连接器的作用是负责跟客户端建立连接、获取权限、维持和管理连接等。连接命令一般写法：

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

在完成经典的 TCP 握手后，连接器开始认证身份，这个时候用的就是输入的用户名和密码。

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。（从权限表里边查询用户权限并保存在一个变量里边以供查询缓存，分析器，执行器在检查权限的时候使用。）

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

wait_timeout是非交互式连接的空闲超时，interactive_timeout是交互式连接的空闲超时。执行时间不计入空闲时间。

1.2、查询缓存

查询缓存可以将查询过的语句以Key-Value键值对的形式缓存起来，其中Key为查询语句、Value为返回结果。如果新的查询在缓存中存在，则直接返回结果。如果不存在，则进行后续步骤。

查询缓存的优势在于如果查询命中缓存，执行效率很高。缺陷在于查询缓存失效非常频繁，只要有一个对表的更新，这个表上都有的查询缓存都会失效。因此，查询缓存适用于更新频率非常低的表，例如配置表等。

MySQL提供了“按需使用”的方式，可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

mysql> select SQL_CACHE * from T where ID=10；

MySQL 8.0版本删除了查询缓存功能。

1.3、分析器

分析器的主要作用是对语句进行词法分析和语法分析，词法分析识别语句中的字符串各代表什么，语法分析判断语句是否满足MySQL语法。

1.4、优化器

优化器会对将要执行的任务进行方案优化，例如选择合适的索引等，优化器优化完成以后执行方案就确定了。

1.5、执行器

分析器的功能是分析要做什么，优化器的功能是确定怎么做，执行器的任务是做。

在开始执行之前，会先判断用户有没有访问表的权限（在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限），如果没有则返回权限错误，如果有则打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

2、SQL查询语句的工作流程

基于MySQL的结构组成，对于SQL查询语句而言，其工作流程大致为：

• 客户端通过连接器建立连接；
• 查询缓存(开启查询缓存的前提下)，命中则返回，未命中则进入下一步；
• 分析器分系语句的词法、语法，确定语句的目的（要做什么）；
• 优化器确定具体的执行方案；
• 执行器执行并返回结果。

3、问题汇总

3.1、长连接、短连接问题

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

但是全部使用长连接后，可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

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

• 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
• 如果用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
• 可以使用连接池的方式，将短连接变为长连接。

3.2、如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。这个错误是在哪个阶段报出来的呢？

分析器阶段，分析器词法分析阶段需要将语句中的字符串与表的字段等信息进行匹配，此时就会发现存在字符串在表字段中无匹配项。词法分析阶段是从information schema里面获得表的结构信息的。

3.3、为什么对权限的检查不在优化器之前做？

有些时候，SQL语句要操作的表不只是SQL字面上那些。比如如果有个触发器，得在执行器阶段（过程中）才能确定。优化器阶段前是无能为力的。

3.4、我创建了一个没有select权限的用户，执行select * from T where k=1，报错“select command denied”，并没有报错“unknown column”，是不是可以说明是在打开表之后才判断读取的列不存在？

这个是一个安全方面的考虑。一个用户如果没有查看这个表的权限，你是会告诉他字段不对还是没权限？如果告诉他字段不对，其实给的信息太多了，因为没权限的意思还包含了：没权限知道字段是否存在。

sql执行过程中可能会有触发器这种在运行时才能确定的过程，分析器工作结束后的precheck是不能对这种运行时涉及到的表进行权限校验的，所以需要在执行器阶段进行权限检查。另外正是因为有precheck这个步骤，才会在报错时报的是用户无权，而不是 k字段不存在。

参考资料：

极客时间专栏：《MySQL实战45讲》