一条sql是如何执行的

一条sql是如何执行的

对于一名程序猿来说，了解sql是如何执行的是必要的的知识，基本上已经成为了程序猿的日常操作，无论是搞大数据的、搞web开发的、搞人工智能的、搞运维的……。但是有些程序猿虽然会写sql语句，却写不出来高效的sql，究其原因是什么，就是对底层不是很了解，在现在这个时代，懂底层是多么重要。很多人会用可能只是一个基本用法，但是懂底层的是多么的可贵。所以，此篇文章希望你能看下去。

1.sql执行的底层原理

mysql的底层架构

此次我拿mysql进行举例，虽然我拿mysql当做例子，但是像其他的主流数据库和mysql也没差多少，无非就是一些细节性的差别。

首先mysql的是一种分层的架构，分为server层和存储层，server层对应的就是一些组件和日志，存储层就是对应的存储引擎，就是咱们听到过的像InnoDB、myISAM、Memory，存储引擎咱们下一篇文章再谈，但是这里要记住，从mysql 5.5开始，mysql使用的存储引擎默认就为InnoDB了。

然后要说的就是sql语句的执行顺序不同于以往我们所使用的高级语言，高级语言语句的执行顺序是从上到下顺序执行的（c语言中的goto除外），同时包含了一些选择、循环、判断等。但是sql语言的执行顺序是有自己的规则的，明确规范出来了一条sql语句哪段先执行，哪段后执行的。从而，了解sql的执行顺序将有助于我们进行基础的sql优化。sql执行顺序我会在下面说。

先来看下图，mysql的架构图。

组件介绍

客户端：我们平常所使用的可编程的那一层软件就是客户端，主要就是负责和程序猿打交道的，编写sql和查看结果。像基础的命令行、sqlyog、navicat等，都属于客户端。

连接器：连接器主要是作为鉴权所用，当我们用客户端向mysql进行登录的时候，首先会到达连接器，然后连接器会读取用户权限列表，将当前用户的权限保存起来，以后我们再次执行mysql命令的时候，就会从读取出来信息里做鉴权处理，如果此时用户不下线，管理员就算修改此用户的权限，也是无效的。

注意_：连接器里的鉴权处理只针对一些重要的权限，例如是否允许登录、是否允许创建视图、存储过程、存储函数等，并不会判断此用户对哪个表具有什么操作权限。对表的权限会在执行器执行时做判断。

查询缓存：当连接器做完鉴权处理，允许用户进行操作时，此时又要进行一次判断处理（此时不是在连接器里做的判断），判断此sql是否在查询缓存中存在，如果存在，就会从查询缓存中取出结果，然后将结果返回给客户端，此次查询完毕。如果在查询缓存中不存在此条sql，那么就会走下一步骤，链接到分析器。查询缓存的结构其实是传统的key-value结构，sql语句作为key，结果集作为value。
如果两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。另外，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。以某些系统函数举例，可能同样的函数的两次调用会产生不一样的结果，比如函数NOW，每次调用都会产生最新的当前时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的！

不过既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、 UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或 DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！

注意：查询缓存将在mysql8.0废弃掉。原因是在实际项目中，能真正用到查询缓存的地方并不多，查询缓存只适用于增删改较少的情况。当我们执行增删改的时候，mysql会将关于此表的查询缓存全部清理掉。这也是浪费性能的，在真正项目中，不可能只进行查询操作。甲骨文可能就是基于这个原因才把查询缓存在mysql8.0版本干掉的吧。

分析器：分析器通俗易懂的意思就是解析出来你的sql到底是干啥的，分析器执行sql共有两个步骤，进行词法分析和语法分析。词法分析就是提取出关键字，例如执行一条查询语句，词法分析就会将里面的关键字，例如select、from、where等提取出来，明确要干啥，明确sql语义，然后就会进行语法分析，检查sql语句是否存在语法错误，如果没有错误就会将此条sql作为key存储在查询缓存里，待一会将结果查询出来之后，会将结果存放在此key的value中。

优化器：优化器的功能其实更加的单一，看一下下面的这条sql语句。

select * from student where s_name = '张三' and age = 12;

其实这条语句是有两条执行方案的。

第一种方案就是先在student表中查询名字为张三的同学，然后在得到的结果中再查年龄为12的同学。

第二种方案就是先在student表中查询年龄为12的同学，然后在得到的结果中查询名字为张三的同学。

那么mysql选择哪一种去执行的，这就是靠优化器去解决了。优化器会根据自身的算法，从这两种中选择其认为是最优的方案去执行，但是，还有但是，它有的时候认为最优的可能并不是最优的（你以为你以为的就是你以为的吗？）。

执行器：当经历了前面的几个步骤之后，就来到了执行器，执行器在执行之前先检查此用户此对待查询的表是有有相应的权限，如果对此表存在相应的权限，则执行器执行sql语句，调用存储层提供好的接口，类似于调用操作系统的内核一样，然后将得到的结果返回给客户端。

示例

关于查询语句是如何执行的我在上面介绍组件的时候也说了，那么接下来就说一说修改语句（insert、delete、update）是如何执行的。

以update进行举例，例如有下面一条sql语句

update student set age = 13 where s_name = '张三';

则先采用查新的套路查询出名字为张三的记录信息，然后将此记录中的age改为13，通过存储引擎写回到硬盘中。当调用存储引擎进行修改的时候，就不得不提到存储引擎InnoDB的redolog了，它和binlog同是日志模块，但有人可能就会有疑问了，为什么要用两个日志模块，有一个难道不可以吗？真的不可以，先来说一下binlog和redolog的区别。binlog只是起到了归档的作用，例如插入一条记录，binlog就记录一条，而redolog是具有crash-safe 能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)。

当执行器调用存储引擎进行修改操作时，会先将redolog置为prepare（预提交状态）状态，然后返回给执行器，说更新完成，此时执行器通知redolog可以提交了。在通知redolog期间，执行器会先通知binlog对某条记录进行了一次修改。此时redolog收到消息后进行提交，此次更新操作完成。

还是接着来说为什么只有一个日志是不可以的。例如当binlog突然挂掉了，此时redolog处于预提交状态，由于binlog挂掉了之后无法响应信息，所以redolog也就收不到让其提交的信息，所以redolog是不会对此次更新操作进行提交的，也就保证了server层和存储层数据的同步，反过来也是一样的，例如redolog挂掉了，那么由于redolog没有发出prepare信息，那么执行器也就不会通知binlog更新了一条操作。

那么如果存在极端的情况呢？

• redolog处于预提交状态，而binlog也处于已经更新的状态，然后服务器突然重启了。

那么这种情况就要依赖于mysql处理机制了，mysql会这么处理：**判断binlog是否是完整的，如果是完整的，则直接提交redolog。**这就解决了数据的一致性的问题了。

2.sql语句的执行顺序

上面说了sql语句的执行顺序和面向对象以及面向过程的语言语句的执行顺序是不一样的，那么接下来就说一说sql语言的执行顺序，主要就是针对select语句来说的，其它语句的执行原理和select差不多。

--查询组合字段
(6)select (6-2) distinct(6-3) top(<top_specification>)(6-1)<select_list>
--连表
(1)from (1-J)<left_table><join_type> join <right_table> （2）on <on_predicate>
        (1-A)<left_table><apply_type> apply <right_table_expression> as <alias>
        (1-P)<left_table> pivot (<pivot_specification>) as <alias>
        (1-U)<left_table> unpivot (<unpivot_specification>) as <alias>
--查询条件
(3)where <where_pridicate>
--分组
(4)group by <group_by_specification>
--分组条件
(5)having<having_predicate>
--排序
(7)order by<order_by_list>
(8)limit index size

每次我们执行查询的时候，都是从from后进行的，如果是单表查询就是选择了某一张表，如果是多表联查则是进行连表的操作，如果是三张表或者多张，那么就需要先进行两表连接，生成虚表，然后和第三张表进行连接，连表操作的顺序就是和编码的顺序有关系了。

其次就是如果存在on语句，执行on子句，在执行on子句后，可能中间形成了多张虚表，但最后传递的虚表只有最后一个。

第三顺序就是执行where条件，期间存在多个条件，对应的就是形成多个虚表，最后留下来的也只有一个虚表。

第四顺序就是执行分组，切记，在执行分组之后，在显示字段位置只能出现聚合函数，或者被分组的字段，其它条件是会报错的。

第五顺序就是执行分组过滤，即having，这是分组特有的条件过滤标识，过滤完成之后，同样也会生成虚表。

第六顺序就是进行显示字段的筛选了，例如mysql自动添加的distinct，还有就是我们自己指定的。

第七顺序就是执行排序了，按我们指定的列进行排序。默认为ASC(升序)。

第八顺序就是进行分页了，由于mysql里没有top（sql server）这个字段，就更没有top precent了，所以mysql里就引入了limit这个概念，从而实现分页功能。

一条sql语句就是这样被分解了。

执行顺序一定要掌握，这是你sql优化的基础。

接下来看一些拓展的知识：

连表分为内连接、外连接、交叉连接、自然连接、全连接等等等。

在我们进行外连接的时候，使用的on后接过滤条件，大家有没有想过为什么用on，而不是用where，其实在进行内连接或者交叉连接的时候，也可以使用on进行条件过滤，那么问题来了，where和on的区别是什么呢？还是拿左外连接举例吧。例如

select * from student stu left outer join score sc on stu.s_id = sc.s_id where s_name = '张三'

这就纳闷了，这怎么on后还有where啊，怎么不把on里的条件放到where中啊，在on后过滤掉的记录，到where中还能再加回来。外连接执行的步骤就是先将两个表进行交叉连接，然后再on中过滤掉笛卡尔积，最终将不满足条件的某一侧表的记录再加回来，然后去执行where中的条件，在where中过滤掉的记录将永久不可以操作！！！

所以mysql中，使用外连接必须要指定条件，不指定条件是会报错的，还有就是，如果你在外连接中不指定条件，那么你为什么不用交叉连接呢？？？

3.总结

1. mysql架构共分为server层和存储层，其中server层共包括连接器、查询缓存、分析器、优化器、执行器、binlog。存储层即为存储引擎，mysql5.5的开始默认存储引擎即为InnoDB（以往InnoDB只是当做mysql的一个插件进行使用）。
2. mysql执行顺序：from > on > where > group > having > 显示字段 > order > limit。
3. on 和 where 的区别：
   • on是过滤两个表连接后产生的虚表，不管on中条件是否为真，都返回某一侧的表（具体看采用哪种外连接）。即是可逆的。
   • where操作是不可逆的，如果where后的条件为false，则返回为空。也是对虚表进行过滤的。

4.参考资料

【1】https://www.cnblogs.com/knowledgesea/p/4177830.html

【2】https://www.cnblogs.com/yxnchinahlj/p/4096484.html

【3】https://blog.csdn.net/lanxingbudui/article/details/80903143

【4】https://blog.csdn.net/u014044812/article/details/51004754

【5】https://blog.csdn.net/qiushisoftware/article/details/80489128

【6】mysql技术内幕 Paul DuBois