jnjy,何平平怎么死的,周来强
由于最近需要做一些sql query性能提升的研究,因此研究了一下sql语句的解决过程。在园子里看了下,大家写了很多相关的文章,大家的侧重点各有不同。本文是我在看了各种资料后手机总结的,会详细的,一步一步的讲述一个sql语句的各个关键字的解析过程,欢迎大家互相学习。
sql语句的解析顺序简单的说一个sql语句是按照如下的顺序解析的:
1. from from后面的表标识了这条语句要查询的数据源。和一些子句如,(1-j1)笛卡尔积,(1-j2)on过滤,(1-j3)添加外部列,所要应用的对象。from过程之后会生成一个虚拟表vt1。首先创建一个customers表,插入如下数据:
customerid | city |
fissa | madrid |
frndo | madrid |
krlos | madrid |
mrphs | zion |
创建一个orders表,插入如下数据:
orderid | customerid |
1 | frndo |
2 | frndo |
3 | krlos |
4 | krlos |
5 | krlos |
6 | mrphs |
7 | null |
假如我们想要查询来自madrid的,订单数小于3的客户,并把他们的订单数显示出来,结果按照订单数从小到大进行排序。
customerid | numorders |
fissa | 0 |
frndo | 2 |
下面我们会详细的讲述sql是怎样计算出这个结果的:
from子句from子句标识了需要查询的表,如果指定了表操作,会从左到右的处理,每一个基于一个或者两个表的表操作都会返回一个输出表。左边表的输出结果会作为下一个表操作的输入结果。例如,交表相关的操作有 (1-j1)笛卡尔积,(1-j2)on过滤器,(1-j3)添加外部列。from句子生成虚拟表vt1。
step 1-j1:执行笛卡尔积(cross join)笛卡尔积会把左右两个表每一行所有可能的组合都列出来生成表vt1-j1,如果左表有m列,右表有n列,那么笛卡尔积之后生成的vt1-j1表将会有m×n列。
step 1-j1这个步骤等价于执行:
select * from customers c cross join orders o
执行结果为:(共有4×7列)
c.customerid | c.city | o.orderid | o.customerid |
fissa | madrid | 1 | frndo |
fissa | madrid | 2 | frndo |
fissa | madrid | 3 | krlos |
fissa | madrid | 4 | krlos |
fissa | madrid | 5 | krlos |
fissa | madrid | 6 | mrphs |
fissa | madrid | 7 | null |
frndo | madrid | 1 | frndo |
frndo | madrid | 2 | frndo |
frndo | madrid | 3 | krlos |
frndo | madrid | 4 | krlos |
frndo | madrid | 5 | krlos |
frndo | madrid | 6 | mrphs |
frndo | madrid | 7 | null |
krlos | madrid | 1 | frndo |
krlos | madrid | 2 | frndo |
krlos | madrid | 3 | krlos |
krlos | madrid | 4 | krlos |
krlos | madrid | 5 | krlos |
krlos | madrid | 6 | mrphs |
krlos | madrid | 7 | null |
mrphs | zion | 1 | frndo |
mrphs | zion | 2 | frndo |
mrphs | zion | 3 | krlos |
mrphs | zion | 4 | krlos |
mrphs | zion | 5 | krlos |
mrphs | zion | 6 | mrphs |
mrphs | zion | 7 | null |
on过滤条件是sql的三个过滤条件(on,where,having)中最先执行的,on过滤条件应用于前一步生成的虚拟表(vt1-j1),满足on过滤条件的行会被加入到虚拟表vt1-j2中。在应用了on 过滤之后,生成的vt1-j2表如下所示:
c.customerid | c.city | o.orderid | o.customerid |
frndo | madrid | 1 | frndo |
frndo | madrid | 2 | frndo |
krlos | madrid | 3 | krlos |
krlos | madrid | 4 | krlos |
krlos | madrid | 5 | krlos |
mrphs | zion | 6 | mrphs |
这个步骤只会出现在使用了外连接的情况。对于外连接(left,right, or full),你可以标记一个或者两个表作为保留表。作为保留表意味着你希望这个表里面的所有列都被返回,即使它里面的数据不满足on子句的过滤条件。left outer join 把左边的表标记为保留表,rightouter join把右边的表作为保留表,full outer join把两个表都标记为保留表.step 1-j3为根据vt1-j2中的虚拟表,添加了保留表中不满足on 条件的列,在未保留表中没有对应的列,因此标记为null。这个过程生成了虚拟表vt1-j3。
c.customerid | c.city | o.orderid | o.customerid |
fissa | madrid | null | null |
frndo | madrid | 1 | frndo |
frndo | madrid | 2 | frndo |
krlos | madrid | 3 | krlos |
krlos | madrid | 4 | krlos |
krlos | madrid | 5 | krlos |
mrphs | zion | 6 | mrphs |
如果from子句中有多个表操作运算,sql会按照从左到右的顺序处理,左边生成的临时表结果作为右边表的输入表。
step 2 where 子句where过滤被应用到前一步生成的临时表中,根据where过滤条件生成临时表vt2。
注意:由于数据现在还没有被分组,因此现在你不能使用聚合运算-例如:你不能使用这样的句子 where orderdate = max(orderdate)。另外你也不能使用select子句中创建的变量别名,因为现在还没有处理select子句-例如你不能写这样的句子:select year(orderdate) as orderyear . . . where orderyear > 2008.
应用这个过滤
where c.city = 'madrid'
这时生成的临时表vt2的内容如下:
c.customerid | c.city | o.orderid | o.customerid |
fissa | madrid | null | null |
frndo | madrid | 1 | frndo |
frndo | madrid | 2 | frndo |
krlos | madrid | 3 | krlos |
krlos | madrid | 4 | krlos |
krlos | madrid | 5 | krlos |
在这个例子中,你需要在on子句中使用on c.customerid = o.customerid过滤,没有订单的客户在1-j2这一步中被过滤掉,但是在1-j3这一步中作为外部列又被加回来。但是,由于你只想返回来自madrid的客户,因此你需要在where子句中过滤城市(where c.city = ‘madrid'),如果你放在on过滤中,不属于madrid的客户在添加外部列中会被添加回来。
关于on 和 where 的区别需要在这里说明一下,on 和where 的主要区别在于 on 实在添加外部列之前进行过滤,where 是在之后。on过滤掉的列会在1-j3中添加回来。如果你不需要添加外部列,那么这两个过滤是相同的。
step 3 group by子句这个子句会把前一步中生成的临时表中的数据进行分组,每一行都会分到并且只分到一个组里,生成虚拟表vt3。vt3表中包含了vt2表中所有的数据,和分组标识符。
这是生成的临时表vt3的内容如下:
groups c.customerid |
c.customerid | c.city | o.orderid | o.customerid |
fissa | fissa | madrid | null | null |
frndo | frndo | madrid | 1 | frndo |
frndo | madrid | 2 | frndo | |
krlos | madrid | 3 | krlos | |
krlos | krlos | madrid | 4 | krlos |
krlos | madrid | 5 | krlos |
sql最终返回的结果中,每一个分组必须只能返回一行(除非被过滤掉),因此当一个sql语句中使用了group by时,在group by后面处理的子句,如select,having子句等,只能使用出现在group by后面的列,对于没有出现group by后面的列必须使用聚合函数(如 max ,min,count,avg等),保证每一个group只返回一行。
step 4 having子句having子句用来过滤前一步生成的临时表,并且只作用于分组后的数据,满足having条件的group被添加到虚拟表vt4中。
当应用了这个过滤:
groups c.customerid |
c.customerid | c.city | o.orderid | o.customerid |
fissa | fissa | madrid | null | null |
frndo | frndo | madrid | 1 | frndo |
frndo | madrid | 2 | frndo |
需要注意的一点是,这里面使用的是count(o.orderid),而不是count(*),由于这个查询中添加了外部列,count方法会忽略null的列,导致出现了你不想要的结果。
step 5 select 子句尽管出现在sql语句的最前面,select在第五步的时候才被处理,select子句返回的表会最终返回给调用者。这个子句包含三个子阶段:(5-1)计算表达式,(5-2) 处理distinct,(5-3)应用top过滤。
step 5-1 计算表达式
select子句中的表达式可以返回或者操作前一步表中返回的基本列。如果这个sql语句是一个聚合查询,在step 3之后,你只能使用group by中的列,对不属于group集合中的列必须使用聚合运算。不属于from表中基本列的必须为其起一个别名,如year(orderdate) as orderyear。
注意:在select子句中创建的别名,不能在之前的step中使用,即使在select子句中也不能。原因是sql的很多操作是同时操作(all at once operation),至于什么是all-at-once operation这里就不再介绍了。因此,select子句中创建的别名只能在后面的子句中使用,如order by。例如:select year(orderdate) as orderyear . . . order by orderyear。
在这个例子中:
c.customerid | numorders |
fifssa | 0 |
frndo | 2 |
step 5-2:应用distinct子句
如果sql语句中使用了distinct,sql会把重复列去掉,生成虚拟表vt5-2。
step 5-3:应用top选项
top选项是t-sql提供的一个功能,用来表示显示多少行。基于order by子句定义的顺序,指定个数的列会被查询出来。这个过程生成虚拟表vt5-3。
正如上文提到的,这一步依赖于order by定义的顺序来决定哪些列应该显示在前面。如果你没有指定结果的order by顺序,也没有使用with ties子句 ,每一次的返回结果可能会不一致。
在我们的例子中,step 5-3被省略了,因为我们没有使用top关键字。
step 6:order by子句前一步返回的虚拟表在这一步被排序,根据order by子句指定的顺序,返回游标vc6。order by子句也是唯一一个可以使用select子句创建的别名的地方。
注意:这一步和之前不同的地方在于,这一步返回的结果是一个游标,而不是表。sql是基于集合理论的,一个集合没有对他的行定义顺序,它只是一个成员的逻辑集合,因此成员的顺序并不重要。带有order by子句的sql返回一个按照特定序列组织每一行的对象。ansi 把这样的一个对象叫游标。理解这一点对你了解sql很重要。
上面的步骤如图所示:本书中主要内容是参照 inside microsoft sql server 2008:t-sql query,中的内容,大家如果想深入了解sql查询相关的知识,可以找这本书看看,我这有英文原版的pdf,需要的可以找我要。
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论