今天我们一起来聊聊MySQL的基础架构。我们经常说，看一个事千万不要直接陷入到细节里，而应该先鸟瞰全貌，这样能够帮助自己从高纬度理解问题。同样，对于MySQL的学习也是这样。
平时我们使用数据库，看到的通常都是一个整体。比如，有一张最简单的表，表中含有一个ID字段，在执行以下查询时：
mysql> select * from T where ID=10；
我们看到的只是输入一条语句，返回一个结果，却不知道这条语句在MySQL内部的执行过程。
因此我们来将MySQL进行拆解，看看内部构成，在了解这个内部构成的过程中，我们也会对MySQL有一个更加深入的理解。在后续碰到MySQL的一些异常或者问题时，就能够直接发现本质，更加快速的定位并解决问题。
下图是MySQL基本架构示意图，这张图明确表示了SQL语句在MySQL的各个功能模块中的执行过程。
整体来说，MySQL可以分为Server层和存储引擎层两部分。
Server层包含连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和读取。基于插件式架构模式，支持InnoDB、MyISAM、Memory等多个存储引擎。
现在最常用的存储引擎是InnoDB，从MySQL5.5.5版本开始成为默认存储引擎。
也就是说，你执行create table建表时，如果不指定引擎类型，默认使用的就是InnoDB。不过，你也可以通过指定存储引擎的类型来选择别的引擎，比如在create table语句中使用：engine=memory，来指定使用内存引擎创建表。
不同的存储引擎的表数据存取方式不同，支持的功能也不同。后续我们可以单独讨论引擎的选择。
而我们从上图中也不难看出，不同的存储引擎使用的是同一个Server层，也就是从连接器到执行器的那一部分。因此我们来好好了解一下Server层各个组件的用途和部分实现
操作数据库，我们总是需要先连接到这个数据库上，这个时候等待你的连接的就是连接器。连接器负责和客户端建立连接、获取权限、维持和管理连接。
mysql -h[ip] -P[port] -u[user] -p
输入完命令后，我们就可以在交互对话框里输入密码。其实密码也可以直接在-p参数后写入，但这样会直接导致密码被明文展示在终端中，如果连接的是生产服务器，强烈建议不要这么做。
连接命令中的mysql指令是客户端提供的工具，用来跟服务端建立一条连接。在完成经典的TCP握手后，连接器就会开始认证你的身份，此时用的就是刚刚输入的用户名和密码。
-
如果用户名或密码不正确，客户端就会收到一个“Access denied for user”的错误，然后客户端程序结束执行
-
如果用户认证通过，连接器则会到权限表里查询出该用户所拥有的权限，之后所有操作，这个连接里面的权限判断逻辑，都会依赖此时从权限表中读取到的信息。
这就意味着，一个用户成功建立连接后，这个期间管理员账户对该用户的权限做了修改，也不会影响到已经存在的该用户连接的权限。修改完成后，该用户只有重新建立连接才会对新的连接刷新权限设置。
当连接完成后，如果没有后续的动作，这个连接就会处于空闲状态，我们可以使用：show processlist命令查看。
下图是使用show processlist的结果（图源网络），其中Command列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。
如果客户端过长时间没有动作（默认为8小时，由配置项：wait_timeout控制），连接器就会自动将其断开。
当连接断开后，客户端再基于该连接发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。此时如果要继续，就需要重连。
数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是每次执行完很少的几次查询就会断开连接，下次再查询就会重新建立一个。
建立连接的过程通常是比较耗时且复杂的，因此实际使用中建议尽量减少建立连接的动作，也就是尽量使用长连接。
但是全部使用长连接后，可能会出现MySQL占用内存特别高，这是由于MySQL在执行过程中临时使用的内存是管理在连接对象里的。这些资源会随着连接断开的时候释放。
所以如果长连接累积下来，可能导致内存占用太大，被系统强行kill（OOM），从MySQL具体表现上来看，就是MySQL异常重启。
-
定期断开长连接。使用一段时间后，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
-
MySQL5.7或更新版本，可以在每次执行一个较大的操作后，通过执行：mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
这是一个很容易被忽视的一个部分，甚至大部分开发人员或者数据库使用者都不知道MySQL的查询可能会操作到缓存的可能，因此总是“悲观”的认为数据库的性能非常的差，甚至将一些完全可以拆解成一两个单表查询的SQL整合成复杂的连接查询以尝试减少查询次数，得不偿失。
实际上，当我们连接建立完成后，我们就可以立即执行select语句了，执行逻辑就会首先来到第二步：查询缓存。
MySQL拿到一个查询请求后，会先到查询缓存中检查，之前是否执行过这条语句。之前执行过的语句以及其结果可能会以Key-Value的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。
如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。
如果查询语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入到查询缓存中。因此，如果查询缓存命中，MySQL不需要执行后面复杂的操作，会直接返回结果，这个效率会很高。
但是，不管是什么场景，也不论应用系统和数据库，引入缓存往往会带来一系列的问题，因此需要视情况而定。而MySQL引入缓存带来的最大问题就是缓存的失效非常频繁，只要对该表有更新，这个表上面的查询缓存就会被全部清空（一致性问题）。
因此你之前的查询结果被缓存后，还未使用过，就被一个更新清空掉了。因此对于更新压力大的数据库来说，查询缓存命中率会非常低。除非你的业务本身就是读多写少，或者本身就是一些静态表，很长时间才会更新一次（比如系统配置表），那么这张表上的查询才会使用到查询缓存。
而MySQL也提供了“按需使用”的方式，你可以将参数：query_cache_type设置为DEMAND，这样对于默认的SQL语句都不会使用查询缓存。
而对于确定要使用查询缓存的语句，可以使用SQL_CACHE显式指定，如下语句：
mysql> select SQL_CACHE * from [表名] where...;
需要注意的是，MySQL在8.0版本直接将查询缓存的整块功能移除了，也就是8.0开始彻底无法使用该功能了。
如果查询没有命中缓存，就要开始真正的执行语句了。首先，MySQL需要知道你具体要执行的操作，因此需要对SQL语句进行解析。
分析器会首先做“词法分析”。SQL是一个由多个字符和空格组成的字符串，MySQL需要识别出这些字符串代表什么。
比如，MySQL从select这个关键字识别出来，这是一个查询语句，也会将from 后的字符串当成表名，列名同理。
做完这些识别后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断SQL语句是否满足MySQL语法。
如果SQL语法不符合规则，就会返回：“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”后的内容。
分析器后，MySQL就知道具体要执行的操作了，在开始执行之前，为了提升SQL性能，会先经过优化器的处理。
优化器是表里面存在多个索引时，决定使用哪个索引；或者在一个语句有多表关联（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通过分析器知道具体要执行的操作，也通过优化器得知了具体如何做。然后就进入执行器，开始执行语句。
开始执行的时候，会先判断是否具备该表查询的权限，如果没有，则会返回无权限相关错误（在工程实现上，如果命中查询缓存，则会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用precheck验证权限）。
mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'icefrog'@'localhost' for table 'T'
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
比如我们这个例子中的表T中，ID字段没有索引，那么执行器的流程是以下表现：
-
调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
-
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
-
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
对于有索引的表，执行的逻辑也差不多。第一次调用的是“去满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。
我们可以在数据库的慢查询日志中看到一个rows_examined字段，该字段标识语句执行过程中扫描了多少行，这个值就是在执行器每次调用引擎获取数据行时累加的结果。
但有些场景，执行器调用一次，在引擎内部却扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。
如果觉得本文给您提供了帮助，请点击转发并关注获取更多内容
