MySQL基本架构和执行原理分析

本节主要介绍MySQL的基本架构，同时通过分析SQL语句的执行过程，贯穿梳理一下MySQL的各个组件的作用和工作机制。

结构层次

首先介绍一下MySQL的基本架构，MySQL的架构设计基于经典的C/S架构（客户端/服务端），服务端由mysqld来提供服务，其基本结构如下图所示：


可以看到服务端主要由连接层，SQL层以及存储引擎层三层组成，其中：

连接层

主要负责客户端与服务器端的连接管理，权限校验以及数据传输（SQL语句）等工作，一般情况下通过如下命令可以连接到MySQL服务器上：

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

在完成客户端与服务器的TCP连接后，连接层便开始校验用户名和密码，通过校验后，会去权限表中查询当前用户的权限情况，而本次连接的所有权限都将基于本次查询的内容进行权限控制。

权限管理

MySQL的系统库user库中存在4个控制权限的表，分别为user表，db表，tables_priv表和columns_priv表；
按照user，db，tables_priv，columns_priv的顺序进行验证。
1.先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；
2.如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推

权限级别

• 全局性的管理权限 ：作用于整个MySQL实例级别
• 数据库级别的权限 ：作用于某个指定的数据库上或者所有的数据库上
• 数据库对象级别的权限 ：作用于指定的数据库对象上（表、视图等）或者所有的数据库对象上

连接管理

在使用上述的命令登录完成后，可以看到每一个连接都会有一个connection id, 如本次查询的连接为3343，可以看到还有一个3342的连接在睡眠中，这是因为我在另一个Terminal上还有一个客户端访问到mysqld上

客户端如果太长时间没动静，连接器就会自动将它断开，一般默认值连接 8 小时；建立连接的过程通常是比较复杂的，所以在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。不过大量的长连接也会占用数据库大量的内存，因为在MySQL执行过程中临时使用的内存是保存在连接对象中的，所以过多的长连接会导致产生OOM，因此在使用过程中要注意判断长连接的数量；或者MySQL 5.7及以上版本通过mysql_reset_connection 来重新初始化连接资源，这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

SQL处理层

主要负责SQL语句处理，具体包括缓存查询，解析器对SQL语句进行语法词法分析，优化器进行SQL优化，执行器负责SQL执行；SQL层涵盖了MySQL大多数核心服务功能，包括跨存储引擎的功能都在这里实现（存储过程，触发器，视图等）

缓存

在MySQL 8.0之前，在进行SQL语句查询前，首先MySQL会去缓存中寻找该条语句是否已经缓存，如果缓存将直接返回该SQL语句的查询结果；如果缓存中没有，将进行后续的查询步骤，并将查询语句的结果放入缓存中。但是在大部分场景下，并不建议使用缓存查询，因为MySQL本身的缓存就非常鸡肋，只要表中有一个数据进行修改，整个缓存就会失效；在数据量较大的情况下对系统反而造成性能压力。MySQL可以通过置query_cache_type 为0(OFF)，1(ON)或者2(DEMOND)，分别表示完全不使用query cache，除显式要求不使用query cache之外的所有的select都使用query cache，只有显示要求才使用query cache三种级别。

set global query_cache_type = 2

对于需要走缓存的列，可以直接通过如下方式显示的进行设置：

select SQL_CACHE * from employee where employee_id = 10;

解析器

解析器首先会根据SQL语句的空格进行词法分析，确定每一个字符串对应的是什么，例如

select * from employee where employee_id = 10

这句SQL，词法分析器需要知道select对应的是查询语句的关键词，employee是数据库表名称，employee_id是表中的一个字段；然后在基于词法分析的结果，进行语法分析，判断该SQL语句是否有语法错误，如果有语法错误，进行报错提示。

优化器

优化器主要是针对当前查询进行索引优化，判断利用现有那种索引组合进行查询可以更高效，更好的减少扫码行数；或者是针对当前Join操作进行优化，提高查询的效率。

执行器

在完成优化后，将进行查询操作，在进行执行查询前首先校验当前用户是否是权限；如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口来进行查询

存储引擎

存储引擎负责数据的存和取，MySQL的存储引擎采用的是插件式的理念，分别支持InnoDB, MyISAM等多个存储引擎；从MySQL 5.5.5开始InnoDB成为默认的存储引擎。从开头图中可以看到存储引擎层是共用上面的连接层和SQL层的。

参考文献

[1]MySQL45讲
[2]SQL必知必会
[3]MySQL权限管理
[4]MySQL性能调优