SELECT 搜索模型

2024-03-12 16:01:44

SELECT 搜索模型

MySQL的语句一共分为11步，如下图所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。**其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。**如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。

SELECT 搜索模型

下面我们来具体分析一下查询处理的每一个阶段

  1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1

  2. ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。

  3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。

  4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。

  5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
    CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.

  6. HAVING： 对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。

  7. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。

  8. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.

  9. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.

  10. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

SQL语句执行顺序

查询语句中select from where group by having order by的执行顺序

1.查询中用到的关键词主要包含六个，并且他们的顺序依次为
select–from–where–group by–having–order by

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序
与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行
from–where–group by–having–select–order by,
from:需要从哪个数据表检索数据
where:过滤表中数据的条件
group by:如何将上面过滤出的数据分组
having:对上面已经分组的数据进行过滤的条件
select:查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据

2.from后面的表关联，是自右向左解析的
而where条件的解析顺序是自下而上的。

也就是说，在写SQL文的时候，尽量把数据量大的表放在最右边来进行关联，
而把能筛选出大量数据的条件放在where语句的最下面。

3.连接查询的执行顺序

SQL语句中join连表时on和where后都可以跟条件，那么对查询结果集，执行顺序，效率是如何呢？
通过查询资料发现：
区别：
on是对中间结果进行筛选，where是对最终结果筛选。
执行顺序：
先进行on的过滤, 而后才进行join。
效率：
如果是inner join, 放on和放where产生的结果一样, 但没说哪个效率速度更高? 如果有outer join (left or right), 就有区别了, 因为on生效在先, 已经提前过滤了一部分数据, 而where生效在后.

SQL Select语句完整的执行顺序【从DBMS使用者角度】：
　　1、from子句组装来自不同数据源的数据；
　　2、where子句基于指定的条件对记录行进行筛选；
　　3、group by子句将数据划分为多个分组；
　　4、使用聚集函数进行计算；
　　5、使用having子句筛选分组；
　　6、计算所有的表达式；
　　7、使用order by对结果集进行排序。

SQL Select语句的执行步骤【从DBMS实现者角度，这个对我们用户意义不大】：
　　1）语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。
　　2） 语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。
　　3）视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。
　　4）表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达式。
　　5）选择优化器，不同的优化器一般产生不同的“执行计划”
　　6）选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。
　　7）选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪个表做为源数据表。
　　8）选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。
　　9）运行“执行计划”。

了解下T-SQL对查询逻辑处理。
T-SQL逻辑查询的各个阶段
(5)SELECT DISTINCT TOP(<top_specification>) <select_list>

(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>

(2)WHERE <where_predicate>

(3)GROUP BY <group_by_specification>

(4)HAVING <having_predicate>

(6)ORDER BY <order_by_list>

T-SQL在查询各个阶级分别干了什么
（1）FROM 阶段

FROM阶段标识出查询的来源表，并处理表运算符。在涉及到联接运算的查询中（各种join），主要有以下几个步骤：

a.求笛卡尔积。不论是什么类型的联接运算，首先都是执行交叉连接（cross join），求笛卡儿积，生成虚拟表VT1-J1。

  b.ON筛选器。这个阶段对上个步骤生成的VT1-J1进行筛选，根据ON子句中出现的谓词进行筛选，让谓词取值为true的行通过了考验，插入到VT1-J2。

  c.添加外部行。如果指定了outer join，还需要将VT1-J2中没有找到匹配的行，作为外部行添加到VT1-J2中，生成VT1-J3。

经过以上步骤，FROM阶段就完成了。概括地讲，FROM阶段就是进行预处理的，根据提供的运算符对语句中提到的各个表进行处理（除了join，还有apply，pivot，unpivot）

（2）WHERE阶段

 WHERE阶段是根据<where_predicate>中条件对VT1中的行进行筛选，让条件成立的行才会插入到VT2中。

（3）GROUP BY阶段

  GROUP阶段按照指定的列名列表，将VT2中的行进行分组，生成VT3。最后每个分组只有一行。

（4）HAVING阶段

  该阶段根据HAVING子句中出现的谓词对VT3的分组进行筛选，并将符合条件的组插入到VT4中。

（5）SELECT阶段

这个阶段是投影的过程，处理SELECT子句提到的元素，产生VT5。这个步骤一般按下列顺序进行

    a.计算SELECT列表中的表达式，生成VT5-1。

    b.若有DISTINCT，则删除VT5-1中的重复行，生成VT5-2

    c.若有TOP，则根据ORDER BY子句定义的逻辑顺序，从VT5-2中选择签名指定数量或者百分比的行，生成VT5-3

（6）ORDER BY阶段

 根据ORDER BY子句中指定的列明列表，对VT5-3中的行，进行排序，生成游标VC6.

One example for the inner join and where condition

select coulumns from table_01 inner join table_02 on 01.col1=02.col2 where 01 = ‘A’ and 02=‘B’ with ur ;

from the db2 plan it will Optimized Statement like below which remove the join on condition and using where replace it :

select coulumns from table_01 , table_02 where 01 = ‘A’ and 02=‘B’ and 01.col1=02.col2 with ur ;

上面优化后的sql 未必是最佳的 ，有可能程序index 会用不到的 。

================华丽分割线 ==============

详细说明
from 子句–执行顺序为从后往前、从右到左
表名(最后面的那个表名为驱动表，执行顺序为从后往前, 所以数据量较少的表尽量放后）

oracle 的解析器按照从右到左的顺序处理，FROM 子句中的表名，FROM 子句中写在最后的表(基础表 driving table)将被最先处理，即最后的表为驱动表，在FROM 子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3 个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指被其他表所引用的表

多表连接时，使用表的别名并把别名前缀于每个Column上。可以减少解析的时间并减少那些由Column 歧义引起的语法错误.

where子句–执行顺序为自下而上、从右到左
ORACLE 采用自下而上从右到左的顺序解析Where 子句,根据这个原理,表之间的连接必须写在其他Where 条件之前, 可以过滤掉最大数量记录的条件必须写在Where 子句的末尾。

group by–执行顺序从左往右分组
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。即在GROUP BY前使用WHERE来过虑，而尽量避免GROUP BY后再HAVING过滤。

having 子句----很耗资源，尽量少用
避免使用HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作.

如果能通过Where 子句在GROUP BY前限制记录的数目,那就能减少这方面的开销.
(非oracle 中)on、where、having 这三个都可以加条件的子句中，on 是最先执行，where 次之，having 最后，因为on 是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，

where 也应该比having 快点的，因为它过滤数据后才进行sum，在两个表联接时才用on 的，所以在一个表的时候，就剩下where 跟having比较了。

在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where 可以使用rushmore 技术，而having 就不能，在速度上后者要慢。
如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，where 的作用时间是在计算之前就完成的，而having 就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。

在多表联接查询时，on 比where 更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where 进行过滤，然后再计算，计算完后再由having 进行过滤。

由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。

select子句–少用*号，尽量取字段名称。
ORACLE 在解析的过程中, 会将依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 使用列名意味着将减少消耗时间。

sql 语句用大写的；因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行

order by子句–执行顺序为从左到右排序,很耗资源

https://blog.csdn.net/wqc19920906/article/details/79411854

上一篇：SQL中GROUP BY语句与HAVING语句的使用


下一篇：关键字group by 、 Having的 用法