数据库学习笔记：MySQL基础架构

前言

数据库都不陌生，后端必接触，没用过也听过，是不可或缺的重要技术手段。同时大多公司也用的是MySQL。

正文

问题一~九

MySQL的基本架构示意图

问题二：何为连接器

我们要进行查询，第一步就是先去链接数据库，那这个时候就是连接器跟我们对接。

它负责跟客户端建立链接、获取权限、维持和管理连接。

链接的时候会经过TCP握手，然后身份验证，然后我们输入用户名密码就好了。

验证ok后，我们就连上了这个MySQL服务了，但是这个时候我们处于空闲状态。

问题三：如何查看空闲连接列表

命令：show processlist，下图就是我在自己的数据库表执行命令的结果，其中的Command列显示为Sleep的这一行，就表示现在系统里面有一个空闲连接。

这里需要注意的是，我们数据库的客户端太久没响应，连接器就会自动断开了，这个时间参数是wait_timeout控制住的，默认时长为8小时。

断开后重连的时候会报错，如果你想再继续操作，你就需要重连了。

这个有个我看过的书本的案例：

一个在政府里的朋友说，他们的系统很奇怪，每天早上都得重启一下应用程序，否则就提示连接数据库失败，他们都不知道该怎么办。

按照这个错误提示，应该就是连接时间过长了，断开了连接。

数据库默认的超时时间是8小时，而他们平时六点下班，下班之后系统就没有人用了，等到第二天早上九点甚至十点才上班，这中间的时间已经超过10个小时了，数据库的连接肯定就会断开了。

是的，就是超出了超时时间，然后写代码的人也没注意到这个细节，所以才会出现这个问题。

把超时时间改得长一点，问题就解决了。

这种参数其实我们平时不一定能接触到，但是真的遇到问题的时候，知道每个参数的大概用法，不至于让你变成无头苍蝇。

问题四：比重新建立链接更优雅的方式

使用长连接。

但是这里有个缺点，使用长连接之后，内存会飙得很快，我们知道MySQL在执行过程中临时使用的内存是管理在连接对象里面的。

只有在链接断开的时候才能得到释放，那如果一直使用长连接，那就会导致OOM（Out Of Memory），会导致MySQL重启（如果是java，那么在JVM里面就会导致频繁的Full GC。）

问题五：如何解决长连接导致OOM和MySQL重启

我一般会定期断开长连接，使用一段时间后，或者程序里面判断执行过一个占用内存比较大的查询后就断开连接，需要的时候重连就好了。

问题六：如何更优雅解决长连接导致OOM和MySQL重启

执行比较大的一个查询后，执行mysql_reset_connection可以重新初始化连接资源。相比上一个好点，不需要重连，但是会初始化连接的状态。

问题七：何为MySQL的查询缓存

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。

大家是不是好奇同一条语句在MySQL执行两次，第一次和后面的时间是不一样的，后者明显快一些，这就是因为缓存的存在。

他跟Redis一样，只要是你之前执行过的语句，都会在内存里面用key-value形式存储着。

查询的时候就会拿着语句先去缓存中查询，如果能够命中就返回缓存的value，如果不命中就执行后面的阶段。

但是我还是不喜欢用缓存，因为缓存弊大于利。

问题八：为何缓存弊大于利

缓存的失效很容易，只要对表有任何的更新，这个表的所有查询缓存就会全部被清空，就会出现缓存还没使用，就直接被清空了，或者积累了很多缓存准备用来着，但是一个更新打回原形。

这就导致查询的命中率低的可怕，只有那种只查询不更新的表适用缓存，但是这样的表往往很少存在，一般都是什么配置表之类的。

问题九：查询不想用缓存如何操作，或者是用缓存又怎么操作

可以显示调用，把 query_cache_type 设置成为DEMAND，这样SQL默认不适用缓存，想用缓存就用SQL_CACHE。

有个小技巧就是：去库里看看sql执行时间，但是可能是有缓存的，一般我们就在sql前面使用SQL_NO_CACHE就可以知道真正的查询时间了。

 select SQL_NO_CACHE * from B

缓存在MySQL8.0之后就取消了。

问题十~十二

问题十：缓存查询后做啥

在缓存没有命中的情况下，就开始执行语句了，你写的语句有没有语法错误，这是接下来MySQL比较关心的点。

会先做词法分析，你的语句有这么多单词、空格，MySQL就需要识别每个字符串所代表的是什么，是关键字，还是表名，还是列名等等。

然后就开始语法分析，根据词法分析的结果，语法分析会判断你sql的对错，错了会提醒你的，并且会提示你哪里错了。

分析没错之后就进入下一步，优化器。

问题十一：主要是优化什么

优化就比较简单了，因为我们建立表可能会建立很多索引，优化有一步就是要确认使用哪个索引，比如使用你的主键索引，联合索引还是什么索引更好。

还有就是对执行顺序进行优化，条件那么多，先查哪个表，还是先关联，会出现很多方案，最后由优化器决定选用哪种方案。

最后就是执行了，执行就交给执行器去做。

问题十二：执行器如何工作

第一步可能就是权限的判断，其实这里我不确定的一个点就是，我接触的公司很多都是自研的线上查询系统，我们是不能用Navicat直连线上库，只能去网页操作，那表的权限是在MySQL层做的，还是系统做的，我猜应该是系统层做的，MySQL可能默认就全开放了，只是我们不知道ip。

执行的时候，就一行一行的去判断是否满足条件，有索引的执行起来可能就好点，一行行的判断就像是接口都提前在引擎定义好了，所以他比较快。

数据库的慢日志有个 rows_examined 字段，扫描多少行可以看到，还有 explain 也可以看到执行计划，我们扫描了多少行。