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

1. MySQL架构

• Server层（1-7均按照执行顺序排列）
  1. 连接器 （管理连接，权限验证）
  2. 查询缓存 （命中直接返回结果） 【MySQL8.0版本之后彻底去除了查询缓存这个功能】
  3. 分析器 （词法分析、语法分析）
  4. 优化器 （执行计划生成，索引选择）
  5. 执行器 （操作引擎，返回结果）
  6. 内置函数（日期、时间、数学、加密等）
  7. 跨存储引擎的功能（存储过程、触发器、视图等）
• 存储引擎层（插件式） （存储数据，提供读写接口）
  1. InnoDB 【从MySQL5.5.5版本开始成为了默认存储引擎】
  2. MyISAM
  3. Memory

存储引擎不会去解析SQL，但是InnoDB除外，InnoDB会解析外键。

2. 连接器

命令：mysql -h$ip -P$port -u$user -p

命令中的 mysql 是客户端工具，用来跟服务端建立连接，在完成 TCP 握手之后，连接器就使用输入的用户名与密码开始认证登录人的身份。

• 如果用户名或密码不对，客户端就会收到一个 “Access denied for user” 的错误，然后客户端结束执行
• **如果用户名密码认证通过，连接器就会到权限表查出登录人拥有的权限。之后，这个连接的权限判断，都依赖此时读到的权限。**这就意味着一个用户成功建立连接后，即使管理员对这个用户的权限做了修改，也不会影响到已经存在的连接。修改权限之后，只有在新建的连接里才会使用新的权限。

客户端连接完成之后，如果太长时间没动静（无操作），连接器就会自动将它断开。这个时间参数是由 wait_timeout 控制，默认8小时。命令：show variables like 'wait_timeout'
连接被断开之后，客户端再次发送请求，就会收到一个错误提醒："Lost connection to MySQL server during query。"此时，需要重连，然后再发送请求。

建立连接的过程是比较复杂的，在使用中应尽量减少连接的动作，尽量使用长连接。

3. 查询缓存

连接完成之后，就可以执行 SQL 语句了，如果是 select 语句，且MySQL版本 < 8.0，就会执行查询缓存。

MySQL服务端拿到一个查询请求后，会先到查询缓存查看之前是否执行过该条语句。之前执行过的SQL语句以及结果可能会以 key-value 对的形式，被直接缓存到内存中。key 是查询的语句，value 是查询的结果。如果查询缓存中有该条查询SQL，就会将查询缓存中该条SQL对应的查询结果直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

MySQL提供了按需使用查询缓存的方式。可以将 query_cache_type 参数设置为 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。对于要使用查询缓存的语句，可以使用 SQL_CACHE 显示指定。比如:

select SQL_CACHE * from table_a where id = 1;

4. 分析器

如果没有命中查询缓存，就开始真正执行查询语句。

分析器会先做“词法分析”，MySQL需要识别出SQL语句字符串分别是什么，代表什么。

比如执行 select name from table_a where id = 1; MySQL 从输入的 “select” 这个关键字识别出这是一个查询语句，“table_a” 识别成"表名 table_a"，字符串 “name” 识别成 “列 name”。

接下来是“语法分析”，根据词法分析的结果，语法分析器会根据语法规则，判断SQL语句是否满足MySQL语法。

5. 优化器

分析器处理完毕之后，在开始执行前，还需优化器处理。

优化器会解析查询，并创建内部数据结构(解析数)，然后对其进行各种优化，包括重写查询，选择合适的索引，以及在一个语句有多表关联(join) 的时候，决定各个表的连接顺序。用户可以通过关键字 hint(提示) 优化器，影响其决策过程。

优化器不关心表使用的什么存储引擎，但是存储引擎对优化查询是有影响的。优化器会请求存储引擎提供容量或者某个具体操作的开销信息，以及表的统计信息等。

6. 执行器

MySQL 通过分析器知道了SQL要做什么，通过优化器知道了应该怎么做，然后执行器开始执行语句。
开始执行的时候，需要先判断一下当前登录人对SQL语句涉及到表有没有执行查询或修改的权限。如果没有，就会返回无权限的错误。（如果命中了查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限）。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎定义的接口。

select id from table_a where id = 10;

无索引执行以上查询：

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

有索引执行以上查询：

• 第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是在存储引擎中定义好的。

在数据库慢查询日志中有一个 row_examined 字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用存储引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在存储引擎内部则扫描了多行，因此引擎扫描行数跟 row_examined 并不是完全相同的。

问题

1. 长连接、短连接 的含义

长连接：长连接指连接成功后，如果客户端持续有请求，则使用同一个连接。在连接保持期间，如果没有数据包发送，需要双方发链路检测包。
长连接：客户端连接 -- 创建 socket 认证连接 -- 维护连接 -- 数据传输 -- 维护连接 -- 数据传输..... -- 关闭连接
短连接：短连接是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
短连接：客户端连接 -- 创建 socket 认证连接 -- 维护连接 -- 数据传输 -- 关闭连接

2. 全部使用长连接，可能会导致MySQL内存占用涨得特别快的原因

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

3. 怎么解决全部使用长连接，可能会导致MySQL内存占用涨得特别快

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

4. 执行 mysql_reset_connection 会产生的影响

1、会回滚所有活动事务，并重置自动提交模式；
2、会释放所有的锁表；
3、所有的临时表会被关闭并清除；
4、Session 系统变量会被重新初始化为相应的全局系统变量的值；
5、用户自定义变量会丢失；
6、会释放 Prepared statements；
7、HANDLER 变量会被关闭；
8、LAST_INSERT_ID() 函数的值会被重置为 0；
9、通过 GET_LOCK() 函数获得的锁会被释放；

5. 查询缓存利大于弊的原因

查询缓存的失效非常频繁，如果对一个表有更新，那么涉及到这个表的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会很低。
当有大量的查询和大量的修改时，cache的机制可能会造成性能下降。因为每次修改会导致系统去做 cache 失效操作，这就会造成服务端的开销。
另外，系统 cache 的访问由一个单一的全局锁来控制，这时候大量的查询将被阻塞，直到锁释放。

6. 为什么对权限的检查不对优化器之前做

有些时候，SQL语句要操作的表不只是SQL字面上的那些，如果有个触发器，那就需要在执行器阶段（过程中）才能确定。优化器阶段前是无能为力的。

7. 在连接器里已经做过了权限验证，为什么在执行器里还要再做一次？

连接器“取”权限，执行器“用”权限。

8. 分析器指向查询缓存(→箭头)的原因

1、查询语句执行完毕会将SQL与操作结果集以key-value的形式存入缓存.
2、更新语句会失效query_cache