SQL进阶之路

对于写SQL，本文认为只要理解了 SQL执行逻辑+语法逻辑 ，理论上任何查询都是可以做到的

写SQL就要了解SQL执行逻辑，写一条SQL的过程可以抽象为三个阶段：

　　分析需求 -> 思考执行逻辑 -> 根据语法写出SQL

在真实的情况中，不可能做到学完SQL执行逻辑后再写SQL，只写SQL不了解执行逻辑那么只能处理一些低级的问题，SQL boy往往需要不断迭代对于两者的理解，形成闭环。

SQL语法较为简单，本文不作介绍

因此本篇博文将分为两个部分：SQL执行逻辑与SQL调优逻辑

SQL执行逻辑

 

 可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层

 Server 层负责建立连接、分析和执行 SQL

存储引擎层负责数据的存储和提取

第一步：连接器

首先，通过连接器连接MySQL服务器，连接器负责跟客户端建立连接、获取权限、维持和管理连接

建立连接的过程通常是比较复杂的，所以要尽量减少建立连接的动作，尽量使用长连接。业务开发中通常使用数据库连接池（DBCP、Druid等）来管理连接；

长连接的问题？

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

如何解决长连接占用内存问题？

1.定期、定量断开长连接

2.断开连接

 

第二步：查询缓存

效率低下，MySQL8.0已经移除

 

第三步：解析器和预处理器

解析器

解析器通过 词法分析 和 语法分析 验证SQL是否合法

词法分析通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”

语法分析：解析器通过MySQL的语法规则判断SQL语句是否合法

SQL输入 -> 词法分析(Lexer) -> Token序列 -> 语法分析(Parser) -> 分析树(AST)

预处理器

预处理器处理的是解析器生成的“解析树”。它根据一些MySQL的规则进一步检查解析树是否合法。这里会检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义等

 

第四步：优化器

优化器负责将AST转化成执行计划。优化器的作用就是找到其中最优的执行计划

 

第五步：执行器

MySQL生成了查询对应的执行计划，执行器负责根据这个执行计划调用存储引擎的API接口来完成整个查询工作。

调用 InnoDB 引擎API接口依次取这个表的每一行。这些接口都是引擎中已经定义好的

有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环

 

第六步：返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。

如果开启了查询缓存，MySQL在这个阶段会将结果放到查询缓存中。

MySQL将结果集返回给客户端是一个增量、逐步返回的过程。例如关联表操作，当服务器处理完最后一个关联表，开始生成第一条结果时，MySQL服务器就可以向客户端逐步返回结果集了。

这样处理有两个好处：

服务端减少内存消耗：服务器端无需存储太多的结果，也就不会因为要返回太多结果而消耗过多的内存。
客户端及时得到响应：客户端可以第一时间获得返回的结果。

 

一条SQL语句到底是如何执行的？

1.客户端通过连接器与MySQL服务器建立连接、获取权限、维持和管理连接；
2.查询缓存，如果开启查询缓存，则先去缓存哈希表查找数据，如果命中缓存，则直接返回数据给客户端；如果没有命中缓存，则继续执行下面逻辑；
3.解析器通过 词法分析 和 语法分析 验证SQL是否合法，并生成相应”语法树“；并通过预处理器进一步检查”语法树“是否合法；
4.接着，优化器将AST转化成执行计划。执行计划决定了执行器会选择存储引擎的哪个方法去获取数据。MySQL使用基于成本的优化器，它会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
5.执行器负责根据这个执行计划调用存储引擎的API接口来完成整个查询工作。
6.MySQL将结果集增量、逐步返回给客户端，如果开启了查询缓存，MySQL在这个阶段会将结果放到查询缓存中。

 

 

SQL调优逻辑