Mysql一条查询语句执行过程
select * from user where id = 1;
这样一条查询语句是如何去获取结果的呢？我想通过的Mysql的基础组件和执行流程来进行解剖和分析。
先看下图
Mysql主要分为两层，一个是server层，一个是存储引擎层。由图可知，多个存储引擎共用一个server层。
server层中又分为连接器、缓存、分析器、优化器、执行器等组件。我们接下来详细分析每个组件的作用，以及在使用中会碰到一些情况。
Mysql查询流程
我画了一个select查询语句执行的流程图，这里结合流程图来看mysql的执行过程。
Mysql基础模块
连接器
连接器是查询流程的第一步，进行用户身份的合法性验证。如果你连mysql的账户和密码都不知道，那mysql肯定不会把数据返回给你的。
那如何进行登录操作呢？
mysql -h$ip -P$port -u$user -p
当你输入了以上命令以后，连接器会判断申请的ip是否是合法人员，并判断用户的账户密码是否正确，进行验证。
如果用户名和密码验证失败，mysql则会返回以下报错信息
Access denied for user
验证通过以后，客户端与mysql就会建立连接，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。文本中这个图是 show processlist 的结果，其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。
作为一个开发者，相信在工作中都会碰到mysql连接建立以后，一直没有人使用，mysql就会关闭该连接，但是，下一次客户端进行访问时就会收到以下报错信息
Lost connection to MySQL server during query
针对于该场景，我们如何处理呢？
- 修改mysql的等待时间，使其超过8小时，通过参数wait_timeout设置
- 在客户端每8小时内，固定发送一个查询请求，在mysql默认时间进行检测时，判断该连接为存活连接
- 使用数据库连接池
首先说说第一种情况：
假设我们设置检测时间为24小时，这样当客户端某一个时刻建立了多个连接，却一直没有使用时，mysql也必须进行连接的维护，占用电脑的内存。由此看来，该方案会导致该关闭的连接并没有及时关闭，增大内存的压力。
再看第二种情况：
每隔一段时间从客户端发送一个请求，这种情况下，客户端会加入与业务毫无关系的一段代码，并且，还需要一个定时任务每隔一个固定时间进行执行。也增大了CPU的压力。从业务上也说不通。
最后一种情况：
数据库连接池，该方案是我们推荐使用的方案，具体原理如下图所示
- 数据库连接池初始化一定数量的连接，并维护在链表当中
- 每隔6小时，数据库连接池进行自动检测
- 如果存在6小时内使用过的连接，那就不做任何动作
- 如果6小时内存在连接未被使用，则关闭未被使用的连接，如果关闭过后，剩余的连接数量小于初始化连接数量，数据库连接池又会创建一定数量的连接进行维护
- mysql按照超时时间进行检测时，都是存活的连接
查询缓存
当连接建立完成以后，就走到了查询缓存了
以这里为例，走到查询缓存以后，会将select * from user where id = 1;整个select语句作为key放到缓存中进行查询。如果该key在查询时没有命中缓存，则会继续向下的步骤。如果，根据key命中了，会及时返回查询结果，这样的查询效率是不是很高呢？
但并不建议使用查询缓存，mysql默认的情况下，查询缓存也是关闭状态，甚至在mysql8.0版本，已经取消了查询缓存模块
在已经了解查询缓存的高效以后，我们来看看为什么不推荐使用查询缓存。
- mysql会将查询的数据从硬盘读取到内存中，进行缓存。但是，每当表出现更新或者结构发生改变时，辛辛苦苦构建的缓存就需要全部清除。
- 如果查询的语句发生了以下改变（大小写发生变化，多了一个空格）等情况，mysql认为该key是一个新的key，则查询不到想要的结果。
所以，基于以上两种情况下，并不推荐使用查询缓存。
分析器
当查询没有命中时，就要开始查询数据库了
mysql在拿到sql语句以后，需要进行解析，从sql语句中了解这一次要做什么，而分析器在过程中主要做了两件事，词法分析与语法分析
词法分析
将sql语句进行拆解，并取出其中的关键字和表名，列名。
比如说，select 关键字，分析以后知道这是一条查询语句
table1,分析以后知道是那一张表
语法解析
语法解析主要做了两件事情
- 解析语句，生成语法树
- 检查语句中的关键词，表，字段是否存在
如果语法解析没有通过，mysql会抛出异常
>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
根据返回的错误日志，near后面的为主要错误的内容。
优化器
在通过解析器以后，mysql就知道自己接下来要做什么了。在此之前，mysql还得选择最优的执行逻辑。比如说：
- join时连表的顺序如何才是最优
- 选择哪一个索引执行效率最高
执行器
解析器告诉了mysql要做什么、优化器告诉mysql怎么做最好、而真正的sql语句执行是执行器来运作。
在开始执行的时候，执行器会先判断你有没有这个表的权限，如果没有，就会返回没有权限的错误，如下图所示
mysql> select * from User where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'User'
还有一种情况是，查询时，会先查询缓存，如果缓存命中的情况下，还没有到执行器，也会调用权限查询接口precheck，进行权限验证。
如果用户拥有权限，就会继续向下执行。
mysql会去打开表，并判断表采用的是哪一种引擎，并采用相应的引擎接口去执行
以上述User表为例，假设ID字段没有索引（执行全表扫描），那执行流程如下（以InnoDB引擎为例）：
调用InnoDB引擎接口获取第一行，判断该行ID = 10？如果是，则将结果放入到结果集中，并返回。
如果不是，则继续向下执行，重复判断逻辑，直到找到ID=10的行，或者查询到记录的最后一行。
执行器会将上述过程中，满足条件的所有结果放入到结果集进行返回。
假设ID字段为主键索引的情况下，会打开索引文件进行查询，每次会加载一页的数据（16kb）进行比较，判断该页的数据里，有没有ID=10的索引，如果有，就找到该索引对应的叶子节点，取出其中的data进行返回。如果没有找到，继续向下查找，直到遍历整个索引文件。
