SQL逻辑查询语句执行顺序
我的抱怨
我一个搞应用开发的,非要会数据库,这不是专门的数据库开发人员干的事么?话说,小公司也没有数据库开发人员这么个职位吧。好吧,对数据库最深的印象还停留在大学《数据库原理》这堂课上,什么第一范式,第二范式…,这些理论的东西,多多少少还是记得点,至于更深层次的,我不会。所以呢,撸起袖子,开始学习吧。
干程序员,最不怕的就是学习,如果你连学习都怕了,那还是早点退出这行吧。你说是吧。而我今天这篇文章,既不总结什么深奥的理论,也不总结多么高深的架构(我也不会)。就从最基本的SELECT语句开始吧。
最后,这篇文章是我读《MySQL技术内幕:SQL编程》而总结出来的,对于书中有的东西讲的比较“粗”,可能是我的水平没有达到人家作者要求的水平,导致阅读起来,不是很舒服,所以,这篇博文,将会非常细致的进行总结。只有你想不到,没有你做不到。
能看懂么?
先来一段伪代码,首先你能看懂么?
(8)SELECT (9)DISTINCT <select_list>
(1)FROM <left_table>
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE|ROLLUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_condition>
(11)LIMIT <limit_number>
1.FROM: 对FROM
子句中的左表<left_table>
和右表<right_table>
执行笛卡尔积(Cartesian Product),产生虚拟表VT1
2.ON: 对虚拟表VT1应用ON筛选,只有那些符合<join_condition>
的行才被插入虚拟表VT2中
3.JOIN:如果指定了OUTER JOIN(如LEFT OUTER JOIN、RIGHT OUTER JOIN),那么保留表中未匹配的行作为外部行添加到虚拟表VT2中,产生虚拟表VT3。如果FROM
子句包含两个以上表,则对上一个连接生成的结果表VT3和下一个表重复执行步骤1~步骤3,直到处理完所有的表为止
4.WHERE:对虚拟表VT3应用WHERE
过滤条件,只有符合<where_condition>
的记录才被插入虚拟表VT4中
5.GROUP BY:依据GROUP BY子句中的列,对VT4中的记录进行分组操作,产生VT5
6.CUBE|ROLLUP:对VT5进行CUBE
或者ROLLUP
操作,产生VT6
7.HAVING:对虚拟表VT6进行HAVING
过滤器,只有符合<having_condition>
的记录才被插入虚拟表VT7中
8.SELECT:第二次执行SELECT操作,选择指定的列,插入到虚拟表VT8中
9.DISTINCT:去除重复数据,产生虚拟表VT9
10.ORDER BY:将虚拟表VT9的记录按<order_by_condition>
进行排序操作,产生虚拟表VT10
11.LIMIT:取出指定行的记录,产生虚拟表VT11,并返回给查询用户
如果你知道每个关键字的意思,作用,如果你还用过的话,那再好不过了。但是,你知道这些语句,它们的执行顺序你清楚么?如果你非常清楚,你就没有必要再浪费时间继续阅读了;如果你不清楚,非常好,你应该庆幸你阅读到了这么好的一篇文章。
准备工作
首先声明下,一切测试操作都是在MySQL数据库上完成,关于MySQL数据库的一些简单操作,请阅读一下文章:
继续做以下的前期准备工作:
1.新建一个测试数据库TestDB
create database TestDB;
2.创建测试表table1和table2
CREATE TABLE table1
(
customer_id VARCHAR(10) NOT NULL,
city VARCHAR(10) NOT NULL,
PRIMARY KEY(customer_id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
CREATE TABLE table2
(
order_id INT NOT NULL auto_increment,
customer_id VARCHAR(10),
PRIMARY KEY(order_id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
3.插入测试数据
INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');
INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');
INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');
INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');
INSERT INTO table2(customer_id) VALUES('163');
INSERT INTO table2(customer_id) VALUES('163');
INSERT INTO table2(customer_id) VALUES('9you');
INSERT INTO table2(customer_id) VALUES('9you');
INSERT INTO table2(customer_id) VALUES('9you');
INSERT INTO table2(customer_id) VALUES('tx');
INSERT INTO table2(customer_id) VALUES(NULL);
准备工作做完以后,table1和table2看起来应该像下面这样:
mysql> select * from table1;
+-------------+----------+
| customer_id | city |
+-------------+----------+
| 163 | hangzhou |
| 9you | shanghai |
| baidu | hangzhou |
| tx | hangzhou |
+-------------+----------+
4 rows in set (0.00 sec)
mysql> select * from table2;
+----------+-------------+
| order_id | customer_id |
+----------+-------------+
| 1 | 163 |
| 2 | 163 |
| 3 | 9you |
| 4 | 9you |
| 5 | 9you |
| 6 | tx |
| 7 | NULL |
+----------+-------------+
7 rows in set (0.00 sec)
4.准备SQL逻辑查询测试语句
SELECT c.customer_id, COUNT(o.order_id) as total_orders
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE c.city = 'hangzhou'
GROUP BY c.customer_id
HAVING count(o.order_id) < 2
ORDER BY total_orders DESC;
使用上述SQL查询语句来获得来自杭州,并且订单数少于2的客户。
好吧,这些测试表和测试数据均来自《MySQL技术内幕:SQL编程》,这应该不算抄袭吧,借鉴借鉴啊。
万事俱备,只欠东风。接下来开始这篇文章最正式的部分吧。
SQL逻辑查询语句执行顺序
还记得上面给出的那一长串的SQL逻辑查询规则么?那么,到底哪个先执行,哪个后执行呢?现在,我先给出一个查询语句的执行顺序:
(8) SELECT
(9) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(7) HAVING <having_condition>
(10) ORDER BY <order_by_condition>
(11) LIMIT <limit_number>
上面在每条语句的前面都标明了执行顺序号,不要问我怎么知道这个顺序的。我也是读各种“武林秘籍”才得知的,如果你有功夫,去阅读一下MySQL的源码,也会得出这个结果的。
好了,上面我标出了各条查询规则的执行先后顺序,那么各条查询语句是如何执行的呢?这就是我今天这篇博文的重点内容。Go on…
执行FROM语句
在这些SQL语句的执行过程中,都会产生一个虚拟表,用来保存SQL语句的执行结果(这是重点),我现在就来跟踪这个虚拟表的变化,得到最终的查询结果的过程,来分析整个SQL逻辑查询的执行顺序和过程。
第一步,执行FROM
语句。我们首先需要知道最开始从哪个表开始的,这就是FROM告诉我们的。现在有了<left_table>
和<right_table>
两个表,我们到底从哪个表开始,还是从两个表进行某种联系以后再开始呢?它们之间如何产生联系呢?——笛卡尔积
关于什么是笛卡尔积,请自行Google补脑。经过FROM
语句对两个表执行笛卡尔积,会得到一个虚拟表,暂且叫VT1
(vitual table 1),内容如下:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 9you | shanghai | 1 | 163 |
| baidu | hangzhou | 1 | 163 |
| tx | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 2 | 163 |
| baidu | hangzhou | 2 | 163 |
| tx | hangzhou | 2 | 163 |
| 163 | hangzhou | 3 | 9you |
| 9you | shanghai | 3 | 9you |
| baidu | hangzhou | 3 | 9you |
| tx | hangzhou | 3 | 9you |
| 163 | hangzhou | 4 | 9you |
| 9you | shanghai | 4 | 9you |
| baidu | hangzhou | 4 | 9you |
| tx | hangzhou | 4 | 9you |
| 163 | hangzhou | 5 | 9you |
| 9you | shanghai | 5 | 9you |
| baidu | hangzhou | 5 | 9you |
| tx | hangzhou | 5 | 9you |
| 163 | hangzhou | 6 | tx |
| 9you | shanghai | 6 | tx |
| baidu | hangzhou | 6 | tx |
| tx | hangzhou | 6 | tx |
| 163 | hangzhou | 7 | NULL |
| 9you | shanghai | 7 | NULL |
| baidu | hangzhou | 7 | NULL |
| tx | hangzhou | 7 | NULL |
+-------------+----------+----------+-------------+
总共有28(table1的记录条数 * table2的记录条数)条记录。这就是VT1
的结果,接下来的操作就在VT1的基础上进行。
执行ON过滤
SELECT
查询一共有3个过滤过程,分别是ON
、WHERE
、HAVING
,ON
是最先执行的
执行完笛卡尔积以后,接着就进行ON a.customer_id = b.customer_id
条件过滤,根据ON中指定的条件,去掉那些不符合条件的数据,得到VT2
表,内容如下:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 3 | 9you |
| 9you | shanghai | 4 | 9you |
| 9you | shanghai | 5 | 9you |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
VT2
就是经过ON
条件筛选以后得到的有用数据,而接下来的操作将在VT2
的基础上继续进行。
添加外部行
这一步只有在连接类型为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
LEFT OUTER JOIN
把左表记为保留表,得到的结果为:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 3 | 9you |
| 9you | shanghai | 4 | 9you |
| 9you | shanghai | 5 | 9you |
| tx | hangzhou | 6 | tx |
| baidu | hangzhou | NULL | NULL |
+-------------+----------+----------+-------------+
顾客baidu
在VT2表中由于没有订单而被过滤,因此baidu
作为外部行被添加到虚拟表VT2中,并将非保留表中的数据赋值为NULL
RIGHT OUTER JOIN
把右表记为保留表,得到的结果为:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 3 | 9you |
| 9you | shanghai | 4 | 9you |
| 9you | shanghai | 5 | 9you |
| tx | hangzhou | 6 | tx |
| NULL | NULL | 7 | NULL |
+-------------+----------+----------+-------------+
FULL OUTER JOIN
把左右表都作为保留表,得到的结果为:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 3 | 9you |
| 9you | shanghai | 4 | 9you |
| 9you | shanghai | 5 | 9you |
| tx | hangzhou | 6 | tx |
| baidu | hangzhou | NULL | NULL |
| NULL | NULL | 7 | NULL |
+-------------+----------+----------+-------------+
添加外部行的工作就是在VT2
表的基础上添加保留表中被过滤条件过滤掉的数据,非保留表中的数据被赋予NULL
值,最后生成虚拟表VT3
。
由于我在准备的测试SQL
查询逻辑语句中使用的是LEFT JOIN
,过滤掉了以下这条数据:
| baidu | hangzhou | NULL | NULL |
现在就把这条数据添加到VT2
表中,得到的VT3
表如下:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 3 | 9you |
| 9you | shanghai | 4 | 9you |
| 9you | shanghai | 5 | 9you |
| tx | hangzhou | 6 | tx |
| baidu | hangzhou | NULL | NULL |
+-------------+----------+----------+-------------+
接下来的操作都会在该VT3
表上进行。
执行WHERE过滤
对添加外部行得到的VT3
进行WHERE
过滤,只有符合<where_condition>
的记录才会输出到虚拟表VT4
中。当我们执行WHERE a.city = 'hangzhou'
的时候,就会得到以下内容,并存在虚拟表VT4
中:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| tx | hangzhou | 6 | tx |
| baidu | hangzhou | NULL | NULL |
+-------------+----------+----------+-------------+
但是在使用WHERE
子句时,需要注意以下两点:
由于数据还没有分组,因此现在还不能在WHERE过滤器中使用
where_condition=MIN(col)
这类对分组统计的过滤;SELECT customer_id, COUNT(customer_id) FROM orders WHERE COUNT(customer_id) < 2; 错误提示:1111 - Invalid use of group function, Time: 0.000000s
由于还没有进行列的选取操作,因此在
SELECT
中使用列的别名也是不被允许的,如:SELECT city as c FROM t WHERE c='shanghai';
是不允许出现的。SELECT order_id AS o, customer_id AS c FROM orders WHERE c = '163'; 错误提示:1054 - Unknown column 'c' in 'where clause', Time: 0.000000s 因为在当前的步骤中还未进行SELECT选取列名的操作
ON过滤器与WHERE过滤器的区别
对于OUTER JOIN中的过滤,在ON过滤器过滤完之后还会添加保留表中ON条件过滤掉的记录,而WHERE条件中被过滤掉的记录则是永久的过滤
如SELECT * FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND c.city = 'hangzhou'
,返回结果为:
而使用WHERE
,如SELECT * FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE c.city = 'hangzhou'
可以发现,customer_id
为9you的记录被添加入前者的查询中。因为ON过滤条件虽然过滤掉了city不等于"hangzhou"
的记录,但是由于查询是OUTER JOIN
,因此会对保留表中被排除的记录进行再次的添加操作
执行GROUP BY分组
GROU BY
子句主要是对使用WHERE
子句得到的虚拟表进行分组操作。我们执行测试语句中的GROUP BY a.customer_id
,就会得到以下内容:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| baidu | hangzhou | NULL | NULL |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
得到的内容会存入虚拟表VT5
中,此时,我们就得到了一个VT5虚拟表,接下来的操作都会在该表上完成。
执行HAVING过滤
HAVING
子句主要和GROUP BY
子句配合使用,对分组得到的VT5
虚拟表进行条件过滤。当我执行测试语句中的HAVING count(b.order_id) < 2
时,将得到以下内容:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| baidu | hangzhou | NULL | NULL |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
这就是虚拟表VT6
。
注意这里不能使用COUNT(1)
或者COUNT(*)
,因为会把通过OUTET JOIN添加的行统计入内,而导致最终结果与预期结果不同。
在这个例子中,只能使用count(o.order_id)
才能得到预期的结果
如使用count(o.order_id)
SELECT c.customer_id, COUNT(o.order_id) as total_orders
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE c.city = 'hangzhou'
GROUP BY c.customer_id
HAVING count(o.order_id) < 2
ORDER BY total_orders DESC;
结果为:
而使用COUNT(*)
,如下:
SELECT c.customer_id, COUNT(*) as total_orders
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE c.city = 'hangzhou'
GROUP BY c.customer_id
HAVING count(*) < 2
ORDER BY total_orders DESC;
结果为:
注意:子查询不能用做分组的聚合函数,如HAVING COUNT(SELECT...)<2
是不合法的
SELECT列表
现在才会执行到SELECT
子句,不要以为SELECT
子句被写在第一行,就是第一个被执行的。
我们执行测试语句中的SELECT a.customer_id, COUNT(b.order_id) as total_orders
,从虚拟表VT6
中选择出我们需要的内容。我们将得到以下内容:
+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| baidu | 0 |
| tx | 1 |
+-------------+--------------+
不,还没有完,这只是虚拟表VT7
。
注意:列的别名不能再SELECT中的其他别名表达式中使用。如SELECT order_id AS o, o+1 AS n FROM orders;
,会提示1054 - Unknown column 'o' in 'field list', Time: 0.000000s
执行DISTINCT子句
如果在查询中指定了DISTINCT
子句,则会创建一张内存临时表(如果内存放不下,就需要存放在硬盘了)。这张临时表的表结构和上一步产生的虚拟表VT7
是一样的,不同的是对进行DISTINCT
操作的列增加了一个唯一索引,以此来除重复数据。
由于我的测试SQL语句中并没有使用DISTINCT
,所以,在该查询中,这一步不会生成一个虚拟表。
执行ORDER BY子句
对虚拟表中的内容按照指定的列进行排序,然后返回一个新的虚拟表,我们执行测试SQL语句中的ORDER BY total_orders DESC
,就会得到以下内容:
+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| tx | 1 |
| baidu | 0 |
+-------------+--------------+
可以看到这是对total_orders列进行降序排列的。上述结果会存储在VT8
中。
在ORDER BY
子句中,NULL
值被认为是相同的值,会将其排序在一起。在MySQL数据库中,NULL
值在升序过程中总是首先被选出,即NULL
值在ORDER BY
子句中被视为最小值
执行LIMIT子句
LIMIT
子句从上一步得到的VT8虚拟表中选出从指定位置开始的指定行数据。对于没有应用ORDER BY
的LIMIT
子句,得到的结果同样是无序的,所以,很多时候,我们都会看到LIMIT
子句会和ORDER BY
子句一起使用。
MySQL数据库的LIMIT
支持如下形式的选择:
LIMIT n, m
表示从第n条记录开始选择m条记录。而很多开发人员喜欢使用该语句来解决分页问题。对于小数据,使用LIMIT子句没有任何问题,当数据量非常大的时候,使用LIMIT n, m
是非常低效的。因为LIMIT
的机制是每次都是从头开始扫描,如果需要从第60万行开始,读取3条数据,就需要先扫描定位到60万行,然后再进行读取,而扫描的过程是一个非常低效的过程。所以,对于大数据处理时,是非常有必要在应用层建立一定的缓存机制(貌似现在的大数据处理,都有缓存哦)。各位,请期待我的缓存方面的文章哦。
总结
文章略长,但都是干货。仔细阅读完,肯定有收获的。好歹是总结完了,个人认为还是比书上的内容清晰一点,好懂一点。如果觉的文章不错,对你有帮助,你也可以打赏我。你也可以加果冻想的微信公众号,期待你与我交流。
2015年1月17日 于深圳。