MySQL(二)：逻辑查询

最新推荐文章于 2022-08-21 10:15:39 发布

GDUT_Ember

最新推荐文章于 2022-08-21 10:15:39 发布

阅读量721

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/GDUT_Trim/article/details/115523496

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

逻辑查询处理

SQL语言与其他编程语言最大的不同之处，在于语句的执行顺序，一般的编程语言执行代码语句都是按顺序执行，但SQL并不一定是按顺序执行的，比如，在SQL语言中，第一个被处理的子句总是FROM子句，下面来看一条完整的SQL的执行顺序

SELECT DISTINCT<select_list>  //对要找的列进行去重
FROM <left_table> //找哪张表
<join_type>JOIN<right_table> //联结类型
	ON<join_condition> //根据什么条件进行联结
WHERE<where_condition> //where子句过滤行数据
GROUP BY <group_by_list> //根据列进行聚合分组，可以理解成将表根据列的值来分成几块，每一块都是独立
						//分成的每一块只会显示一行，所以对于每一块要找的数据都要使用聚合函数。
WITH{CUBE|ROLLUP} //进行CUBE或ROLLUP操作
HAVING<having_condition> //根据条件过滤块，分组后会形成块，可以进行过滤
ORDER BY<order_by_list> //根据列来进行排序
LIMIT<limit_number> //只显示前几行数据

首先，看一下语句的代码顺序

SELECT -> DISTINCT -> FROM -> JOIN ON -> WHERE ->GROUP BY -> WITH -> HAVING -> ORDER BY -> LIMIT

这里只是要注意的是从FROM后面的关键字排序，首先是要生成表，所以JOIN ON要在前面，然后使用WHERE来过滤行数据，才可以使用GROUP BY来进行聚合，才可以用HAVING来过滤块数据，最后使用ORDER BY来对此时过滤完数据新生成的表进行排序，最后用LIMIT对新生成的表进行筛选数据。

现在，看一下代码执行的先后顺序

FROM：对FROM子句中的左表<left_table>和右表<right_table>执行笛卡儿积（笛卡尔积其实相当于嵌套for相乘），产生虚拟表VT1。
ON：对虚拟表VT1进行应用ON筛选，只有符合ON条件，即<join_condition>的行才被插入虚拟表VT2中。
JOIN 如果指定了OUTER JOIN（即LEFT JOIN、RIGHT JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中（注意这里保留的表，并不是VT1，而是左表或者右表），产生虚拟表VT3，如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表进行重复的操作。
WHERE：对VT3应用WHERE过滤条件，只有符合<where_condition>的记录才被插入新的虚拟表VT4中
GROUP BY：根据GROUP BY 子句中的列，对VT4中的记录进行分组操作，产生VT5
CUBE|ROLLUP：对表VT5进行CUBE或ROLLUP操作，产生VT6
HAVING：对虚拟表VT6应用HAVING过滤器，专门过滤块数据的，也就是GROUP BY分组后生成的数组，只有符合<having_condition>条件的记录才被插入新的虚拟表VT7中
SELECT：经过上面的各种匹配过滤，数据已经完整了，现在是执行SELECT操作，选择指定的列，将列插入到虚拟表VT8中。
DISTINCT：对VT8表中的数据进行去重操作，并且产生新的虚拟表VT9
ORDER BY：将虚拟表VT9中的记录按照<order_by_list>进行排序操作，生成虚拟表VT10
LIMIT：从VT10中取出指定行的记录，产生虚拟表VT11，并返回给查询用户。

总的执行顺序：

FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING ->SELECT -> DISTINCT->ORDER BY -> LIMIT

FROM -> ON -> JOIN 生成要查询表的所有数据，包括联结

WHERE -> GROUP BY -> HAVING -> 行过滤，分组，块过滤

SELECT -> DISTINCT -> ORDER BY -> LIMIT 此时再进行精确筛选

1、执行笛卡尔积

第一步需要做的是对FROM子句前后的两张表进行笛卡尔积操作，也称做交叉连接(Cross join)，生成虚拟表VT1，比如FROM子句先遇到的第一个表a，加入里面有n条数据，然后后面遇到的另一个表b，里面有m条数据，那么虚拟表VT1中的数据就是n*m条，虚拟表VT1的列名是由源表定义的，也就是由a,b的列名来定义。

2、应用ON过滤器

SELECT查询一共会有3个过滤过程，分别是ON、WHERE、HAVING，ON是最先执行的过滤过程。

比如

ON c.customer_id = o.customer_id //过滤条件为c表的customer_id等于o表的customer_id

Mysql数据库跟其他编程语言还有其他不同，就是其他编程语言的逻辑表达式的值一般只有两个值，就是TRUE和FALSE，但是在关系型数据库中起逻辑表达式作用的并非只有两种，还有一种被称为三值逻辑的表达式，这是因为在数据库中对NULL值得比较与大多数的编程语言不同，在C语言中，NULL == NULL，返回的是1，即相等，而在关系型数据库中，NULL的比较则完全不是这么回事，例如：
在这里插入图片描述
可以看到第一个NULL值的比较，返回的是NULL而不是0，第二个比较返回的也是NULL而不是1，所以，对于NULL，应该将它视为UNKNOWN，即表示未知的，这是因为在某些情况下，NULL返回值可能代表1，即NULL等于NULL，而有时NULL返回值可能代表0。

对于在ON过滤条件下的NULL值比较，此时的比较结果为UNKNOWN时，会被视为FALSE来进行处理，所以不会出现NULL数据匹配NULL数据的情况。

但在下面两种情况下，Mysql会认为两个NULL比较是相等的

GROUP BY子句会把所有NULL值分到同一组
ORDER BY子句中会把所有NULL值排列在一起

CREATE TABLE t(
	a CHAR(5)
)ENGINE=INNODB;
INSERT INTO t(a) VALUES('a'),(NULL),('b'),('c'),(NULL);
SELECT * FROM t ORDER BY a;
//这也是一个小技巧，当想使用GROUP BY后，想统计各个组/块的数量，可以使用COUNT(1)
SELECT a,COUNT(1) FROM t GROUP BY a;

在这里插入图片描述
结果如上，很清楚可以看到order by会把值为null的排在一起，而GROUP BY会把NULL的规划在同一块上，也就证明了NULL = NULL。

所以Mysql判断逻辑会有三种表达式，TRUE、FALSE、UNKNOWN（UNKNOWN是根据情况为TRUE或者为FALSE）。

因此在产生虚拟表VT2时，会增加一个额外的列来表示ON过滤条件的返回值，返回值有TRUE、FALSE、UNKNOWN，会取出比较值为TRUE的记录，然后产生虚拟表VT2。

3、添加外部行

这一步只有在连接类型为OUTER JOIN时才会发生（因为只有发生外联结匹配的时候），比如LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN。在很多时候，我们可以将OUTER关键字省略掉，但OUTER其实代表的就是外部行，LEFT OUTER JOIN把左表记为保留表，RIGHT OUTER JOIN把右表记为保留表，而FULL OUTER JOIN就是把左右表都记为保留表。

添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表VT3。

4、应用WHERE过滤器

对上一步骤产生的虚拟表VT3进行WHERE条件过滤，只有符合<where_condition>的记录才会输出到虚拟表VT4中。

在当前应用WHERE过滤器时，有两种过滤是不被允许的。

由于数据还没有分组，因此现在还不能在WHERE过滤器中使用<where_condition=MIN(col)>，这类对统计的过滤，这也是因为顺序问题，聚合函数，是要进行聚合分组操作才会出现的，也就是GROUP BY，WHERE的执行顺序在GROUP BY之前，所以会报错Invalid use of group function。
由于没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的，比如SELECT city as c FROM t WHERE c = 'ShangHai’是不被允许出现的。这是因为取别名，是返回结果的时候才进行的，也就是起码要进行SELECT语句后，才进行SELECT语句后面的字段，也就是别名c最早能被识别，是处理SELECT后面列的时候，但此时执行WHERE并还没有执行SELECT，所以别名c是找不到的。

CREATE TABLE t4(
	id INT PRIMARY KEY AUTO_INCREMENT,
	city VARCHAR(255)
)ENGINE=INNODB,CHARSET=utf8
INSERT INTO t4(city) VALUES("ShangHai"),("BeiJing"),("HongKong");

SELECT city FROM t4 WHERE id = MIN(id);

在这里插入图片描述

CREATE TABLE t3(
	city VARCHAR(100)
)ENGINE=INNODB,CHARSET=utf8;
INSERT INTO t3(city) VALUES("ShangHai"),("BeiJing"),("HongKong");
//下面语句无法执行，因为c此时还没存在
SELECT city AS c FROM t3 WHERE c = "ShangHai";

在这里插入图片描述
但以下SQL就可以成功执行

SELECT city FROM t4 AS c WHERE c.`city` = 'ShangHai';

在这里插入图片描述
这是因为FROM是最先执行的，所以可以识别出别名c的

此外，在WHERE过滤器中进行的过滤和在ON过滤器中进行的过滤是有所不同的，对于OUTER JOIN中的过滤，在ON过滤器过滤完之后还会添加保留表中被ON条件过滤掉的记录，因为后面还会进行OUTER JOIN保留表的匹配，但WHERE条件中被过滤掉的记录则是永久的过滤，即使在INNER JOIN中两者也是没有差别的。

SELECT  * FROM t4 LEFT JOIN t3 ON t4.`city` = t3.`city`;

在这里插入图片描述
其实OUTER JOIN ON 其实是ON先处理数据，然后OUTER JOIN 补回保留表的数据。

5、分组(GROUP BY)

在本步骤中根据指定的列对上个虚拟表进行分组，最后得到虚拟表VT5。

GROUP BY要注意的点就是NULL = NULL（前面已经提过）

不过要注意的是，使用OUTER JOIN之后再使用GROUP BY，不太建议使用COUNT，比如COUNT(1)、COUNT(*)，因为会把保留表的行也会加进去，导致数据不准

比如，这是t3表
在这里插入图片描述
这是t4表

然后进行下列SQL

//t4作为保留表
SELECT COUNT(1) FROM t4 LEFT JOIN t3 ON t4.`city` = t3.`city` GROUP BY t4.`city`;

在这里插入图片描述
结果显示产生4组，没组都有一条数据，这就证明了，t4保留表的QingHai在聚合时，独自分在一组

6、应用ROLLUP或CUBE

如果指定了ROLLUP选项，那么将创建一个额外的记录添加到虚拟表VT5的最后，并且生成虚拟表VT6。

对于CUBE选项，Mysql并没有实现，也就是并不支持CUBE操作，只是SQL中是有的。

7、应用HAVING过滤器

HAVING过滤器已经是最后一个条件过滤器了，之前已经经过了ON和HAVING的过滤器，HAVING过滤器是对分组条件进行过滤的筛选器，也就是对GROUP BY形成的块组进行过滤，后面的条件不一定是一个聚合，也可以是普通的条件匹配。

注意

子查询不可以用于做分组的聚合函数，比如HAVING COUNT(SELECT …) < 2是不符合的。

8、处理SELECT列表

虽然SELECT是查询中最先被指定的部分，即是顺序排在第一，但是直到现在步骤8才进行处理SELECT列表，这一步中，将SELECT中指定的列从上一步产生的虚拟表中选出。

记住！在SELECT语句中，列的别名是不可以在别处使用的，

9、应用DISTINCT语句

如果在查询表中指定了DISTINCT子句，则会创建一张内存临时表（如果内存中存放不下就放到磁盘上）。这张内存临时表的表结构和上一步产生的虚拟表是一样的，也就是经过SELECT出来的表，不同的是对进行DISTINCT操作的列增加了一个唯一索引，就是凭此来除去重复的数据。

另外，如果对于这个列已经使用了GROUP BY，那就不需要使用DISTINCT了，因为已经根据这个列进行分组了，所以肯定是唯一的，DISTINCT在这里是多余的，即使DISTINCT不是操作GROUP BY的列，也是不可以使用的，因为到了GROUP BY后，已经是对于块来进行的了，在SELECT形成的临时表，是不会进行去除行数据的，也就是要到ORDER BY和LIMIT才会对行再进行一次筛选。

10、应用ORDER BY子句

根据ORDER BY子句中指定的列对上一步输出的虚拟表再进行一次排序，也就是经过SELECT后（或许里面还有DISTINCT里要处理），得到的虚拟表，再进行行数据的排序，ORDER BY语句还支持指定SELECT列表中列的序列号（也就是可以用SELECT里面中的列名排序来代替ORDER BY里面的参数），比如下面的语句：

//一般的ORDER BY
SELECT order_id,customer_id FROM orders ORDER BY(order_id,customer_id)
//使用序号来代理的ORDER BY
SELECT order_id,customer_id FROM orders ORDER BY(1,2)

能这么用也是因为先执行SELECT，也就是明知道有哪些列了，所以可以用顺序来代替。

当然这种方式是不推荐的，因为可能会出现修改了SELECT中的列，但ORDER BY中的列表忘记修改，这样也是能正常运行的，但却是得到不想要的结果。

注意

前面已经提到过在ORDER BY中，NULL的值会被排在最前，也就代表NULL值在ORDER BY中是被认为最小的。

11、LIMIT子句

LIMIT子句其实就是从上一步骤的虚拟表中选出从指定位置开始的指定行数据，对于没有应用ORDER BY和LIMIT子句，结果同样可能是无序的，因此LIMIT子句常和ORDER BY子句一起使用。

LIMIT的使用方式为:

//从第n条记录开始选择m条记录
LIMIT n,m

但是使用LIMIT是比较抵消的，特别是在各种分页场景中，很多人都是用LIMIT来进行分页，但这对于数据规模不大的还行，但数据规模很大的时候，比如100W，需要找到80W后面的第五条数据，那么就先要扫描第80W条数据，因此对于数据量非常庞大的分页问题，在应用层建立一定的缓存机制是十分有必要的。

物理查询处理

下面介绍一下MySQL数据库层的两个组件，一个是Parser，另一个是Optimizer，Parser的工作就是分析SQL语句的，而Optimizer的工作就是对SQL语句进行优化，选择一条最优的路径来选取数据，但是必须保证物理查询处理的最终结果和逻辑查询处理是相等的。

如果表上建有索引，那么优化器就会判断SQL语句是否可以利用该索引来进行优化，如果没有可以利用的索引，可能整个SQL语句的执行代价是很巨大的，比如查询数据量很大的表。

GDUT_Ember

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL(二)：逻辑查询

逻辑查询处理SQL语言与其他编程语言最大的不同之处，在于语句的执行顺序，一般的编程语言执行代码语句都是按顺序执行，但SQL并不一定是按顺序执行的，比如，在SQL语言中，第一个被处理的子句总是FROM子句，下面来看一条完整的SQL的执行顺序SELECT DISTINCT<select_list> //对要找的列进行去重FROM <left_table> //找哪张表<join_type>JOIN<right_table> //联结类型 ON<j
复制链接

扫一扫