今天真的是尴尬，主管老大丢了一份资料让我去面一个三年的Java后端，让我根据上面的面试答案酌情给分。
这次面的小哥他上家公司资金充足的时候，招了很多人，他进去后就只需要负责数据库方面的工作，开发工作接触的很少。
又刚好小面的部门在数据库方面人才不足，于是主管就把这个活丢小面头上了。(明明开会的时候主管自己跟领导说自己会好好把关的....)
结果我拿着这份面试题进去后，就问了一句“你知道一条查询SQL是怎么执行的吗？”
对方就说不出话来了，很快就草草了事了，出门还撞上了正打算去旁听的主管，主管还问我怎么还没去面试？
到现在这份资料还在我手里，忘了还回去。干脆就来写写这道让我印象深刻的面试题吧！
前言
人们常说看待一个事儿千万不要直接陷入细节里，你应该先鸟瞰其全貌，这样能够帮助你从高维度理解问题。同样，对于 MySQL 的学习也是这样。平时我们使用数据库，看到的通常都是一个整体。比如下面这个最简单的一个查询语句：
select * from T where ID=10；
我们看到的只是输入一条语句，返回一个结果，却不知道这条语句在 MySQL 内部的执行过程。
所以今天我想和你一起把 MySQL 拆解一下，看看里面都有哪些“零件”，希望借由这个拆解过程，让你对 MySQL 有更深入的理解。这样当我们碰到 MySQL 的一些异常或者问题时，就能够直戳本质，更为快速地定位并解决问题。
执行一条SQL会经过哪些组件？
下面是 MySQL 的基本架构示意图，从中你可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。
大体来说，MySQL的逻辑架构 可以分为 Server 层和存储引擎层两部分。
要问为什么mysql的逻辑架构会分为两部分呢？因为mysql是插件式的，有不同的存储引擎，所以在执行器和优化器（主要包括索引的优化），会根据表设置的存储引擎进行选择。
逻辑架构
Server 层包括连接器、查询缓存、分析器、优化器、执行器等。
而存储引擎层负责数据的存储和提取。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说，你执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB。不过，你也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table 语句中使用 engine=memory, 来指定使用内存引擎创建表。
连接器
使用数据库的第一步，我们会从客户端连接到MySQL数据库上，到时候等待我们的第一关就是这个连接器。连接器负责跟客户端建立连接，获取权限，维持和管理连接。一般的连接指令是这样写的：
mysql -h$ip -P$port -u$user -p
输完命令后就要开始输入密码，你也可以在-p后面直接加密码，但是这样会导致你的密码泄漏，不建议在生产服务器上直接加密码。
输完密码后，连接器就会开始认证你的身份：
- 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
- 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
这意味着当你建立连接成功后，即便你这个账户被管理员修改了权限，无法使用select关键字，也不会影响到你已经建立连接后的使用。只要重新建立连接，重新获取权限信息后，你才无法使用select关键字。
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。文本中这个图是 show processlist 的结果，其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。
那么问题来了，空闲连接是怎么形成的？空闲连接多了，对系统有什么影响？如何避免空闲连接过多的情况？
答：新建连接使用过后，一段时间不用就会变成空闲连接，因为空闲连接会占用内存，所以空闲连接多了，会极其耗费资源，所以记得给空闲连接设置过期时间即可。
注意：
- MySQL 在默认设置下，当一个连接的空闲时间超过8小时后，MySQL 就会断开该连接。
- 但是使用c3p0连接池时则会被以为该被断开的连接依旧有效，这种情况下，一旦向连接池发起请求连接的话，客户端在使用该失效连接的时候即抛出异常。
对于这一点，要么定时使用连接，使得不会被MySQL断开，要么修改MySQL配置/etc/mysql/my.cnf，让默认时间变长，24小时(606024=604800)即可
set interactive_timeout=604800; set wait_timeout=604800;
- 要么就在项目配置文件里修改c3p0的时间 cpool.maxIdleTime=25200
- 这个时间的设置还需要根据业务来看待！因为使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
解决方法：1、定期断开链接，使用时再发起长连接；2、mysql5.7以后可以通过设置mysql_reset_connection来定期重置链接。
查询缓存
连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。
MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
注意：大多数情况下，不建议使用查询缓存，因为查询缓存失效非常频繁，只适用于常年不被修改的数据上。对于查询语句来说，但凡有一个表的更新，这个表的所有查询缓存都会被清空。除非你的业务就是有一张静态表，很长时间才会更新一次。
好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND
而默认的 SQL 语句一般都不使用查询缓存，如果你想对某条SQL进行缓存，可以通过添加SQL_CACHE关键字来达到目的。
select SQL_CACHE * from T where ID=10；
需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。
具体请查看MySQL 8.0 去除查询缓存功能的文章： https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/
分析器
如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。
分析器先会做“词法分析”与“语义解析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。
过程：
- 识别传进来的字符串
- 根据传进来的字符串用语法规则进行判断
- 如果有错误，将会收到You have an error in your SQL syntax的报错
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 就知道你要做什么了，数据库就要针对你的需求想一个最优的解决方案，也就是执行计划，这个最优方案选择的操作，这个就是优化器要做的事情了。
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的 join：
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。
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。一条 SQL 语句可能有不同的执行逻辑（或者执行顺序），但是优化器就是选择最优的执行顺序。
执行器
MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。
开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，
select * from T where ID=10; ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，也会做权限验证。查询也会在优化器之前调用 precheck 验证权限。
其实在这里可能会有一个问题，比如
我创建了一个没有select权限的用户，执行select * from T where k=1，报错“select command denied”，并没有报错“unknown column”，是不是可以说明是在打开表之后才判断读取的列不存在？
答：这个是一个安全方面的考虑。你想想一个用户如果没有查看这个表的权限，你是会告诉他字段不对还是没权限？如果告诉他字段不对，其实给的信息太多了，因为没权限的意思还包含了：没权限知道字段是否存在。
小结
今天介绍了 MySQL 的逻辑架构，希望你对一个 SQL 语句完整执行流程的各个阶段有了一个初步的印象。
对于全文，我会使用一种更直观的概念来解释他们的具体作用：
连接器：门卫：想进请出示准入凭证（工牌、邀请证明一类）。我这边没有您的预约记录(查询缓存)。“你好，你是普通员工，只能进入办公大厅，不能到高管区域”此为权限查询。
分析器：“您需要在公司里面找一张头发是黑色的桌子？桌子没有头发啊！臣妾做不到”
优化器：“要我在A B两个办公室找张三和李四啊？那我应该先去B办公室找李四，然后请李四帮我去A办公室找张三，因为B办公室比较近且李四知道张三具体工位在哪”
执行器：“好了，找人的计划方案定了，开始行动吧，走你！糟糕，刚门卫大哥说了，我没有权限进B办公室”
大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:
- MySQL的框架有几个组件, 各是什么作用?
- Server层和存储引擎层各是什么作用?
- you have an error in your SQL syntax 这个保存是在词法分析里还是在语法分析里报错?
- 对于表的操作权限验证在哪里进行?
- 执行器的执行查询语句的流程是什么样的?
