MySQL学习（一）| 基础架构

已经许久没有更新过博客了，曾经的热情似乎早已不再。在经历过一些人和事之后，觉得还是有必要重启这个博客，回归打算就从MySQL开始吧，经好友推荐了解到了《MySQL45讲》，之前的数据库基础实在是有点薄弱，打算趁此机会正式学习一下这个数据库。

MySQL基础架构

如图所示，是MySQL的基础架构，大体可以分为server层和存储引擎层两部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现。存储引擎层负责数据的存储和提取。其架构模式是插件式的，最常用的存储引擎是InnoDB，也是MySQL的默认存储引擎。当然，我们也可以在建表时使用 engine=MyISAM 来更改存储引擎。

值得一提是，MySQL8.0取消了查询缓存这个模块，原因自然是查询缓存存在弊端和局限性。同时由于现代缓存redis发展的成熟，将缓存置于客户端也有更多的好处。这些内容有机会再另开一篇博客稍作解释吧。

连接器

第一步，在连接到数据库之后，首先通过的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令如下：

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

在经过 TCP握手 之后，连接器就会开始认证你的身份。

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

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

如果连接断开之后（如果客户端长期处于空闲，MySQL也会将连接中断，由参数 wait_timeout 控制），客户端再次发送请求，就会发生错误，此时需要进行重连。这里区分了 长连接 和 短连接 长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程较为复杂，实际使用中要减少建立连接的动作，因此尽量使用长连接。

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

如何解决这种问题？

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

查询缓存

因为新版的MySQL已经舍弃了这一部分，所以不作过多介绍。只需要知道MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以键值对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。

但是查询缓存的失效十分频繁，只要有针对表的更新，那么关于这个表的查询缓存就会被清空。

分析器

分析器首先会进行“词法分析”，识别出里面的字符串分别是什么，代表什么。之后进行“语法分析”，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

如果查询的字段在表中不存在，那么在分析器阶段就会报错

优化器

经过分析器，MySQL就知道要做什么了，在执行之前还需要经过优化器处理。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的join：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

此时优化器要决定先取出t1表的数据还是t2表的数据再进行关联。虽然是一样的逻辑，但可能有不一样的执行效率。

执行器

到了执行器之后就开始正式执行语句。开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误。对于如下的这个语句，若ID字段没有索引，那么执行流程如下

select * from T where ID = 10;

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

参考

MySQL45讲——01 基础架构：一条SQL查询语句是如何执行的？