SQL语句执行顺序及流程

avatar
@海尔优家智能科技(北京)有限公司

想成为一个有逼格的资深程序员,写每一行代码时都应该去思考,sql 也是,要想写出一条效率高的sql,就需要熟练掌握 sql 语句的执行顺序、流程、执行计划。

1 准备测试实验

创建 table1、table2,插入测试数据,如下:

CREATE TABLE table1 (     user_id VARCHAR(10) NOT NULL,     city VARCHAR(10) NOT NULL,     PRIMARY KEY(user_id) )ENGINE=INNODB DEFAULT CHARSET=UTF8;   CREATE TABLE table2 (     order_id INT NOT NULL AUTO_INCREMENT,     user_id VARCHAR(10),     PRIMARY KEY(order_id) )ENGINE=INNODB DEFAULT CHARSET=UTF8;  INSERT INTO table1(user_id,city) VALUES('lisi','xian'); INSERT INTO table1(user_id,city) VALUES('zhangsan','xian'); INSERT INTO table1(user_id,city) VALUES('brayden','xian'); INSERT INTO table1(user_id,city) VALUES('red','xian');  INSERT INTO table2(user_id) VALUES('lisi'); INSERT INTO table2(user_id) VALUES('lisi'); INSERT INTO table2(user_id) VALUES('zhangsan'); INSERT INTO table2(user_id) VALUES('zhangsan'); INSERT INTO table2(user_id) VALUES('zhangsan'); INSERT INTO table2(user_id) VALUES('brayden'); INSERT INTO table2(user_id) VALUES(NULL);

测试sql,如下:

SELECT a.user_id, COUNT(b.order_id) AS total_orders
 FROM table1 AS a
 LEFT JOIN table2 AS b
 ON a.user_id = b.user_id
 WHERE a.city = 'xian'
 GROUP BY a.user_id
 HAVING COUNT(b.order_id) < 2
 ORDER BY total_orders DESC; 

SQL逻辑查询语句执行顺序,标注为执行顺序,SQL语句的执行过程中,会产生一个虚拟表,用来保存SQL语句的执行结果。

(7)     SELECT 
(8)     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)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

2 执行FROM语句

以下内容是根据上面测试 sql 进行分析。

第一步,通过左表<left_table>右表<rifht_table>笛卡尔积产生T_1,如下:

SELECT * FROM table1 a CROSS JOIN table2 b 
 
 
user_id   city    order_id  user_id   
--------  ------  --------  ----------
brayden   xian           1  lisi      
lisi      xian           1  lisi      
red       xian           1  lisi      
zhangsan  xian           1  lisi      
brayden   xian           2  lisi      
lisi      xian           2  lisi      
red       xian           2  lisi      
zhangsan  xian           2  lisi      
brayden   xian           3  zhangsan  
lisi      xian           3  zhangsan  
red       xian           3  zhangsan  
zhangsan  xian           3  zhangsan  
brayden   xian           4  zhangsan  
lisi      xian           4  zhangsan  
red       xian           4  zhangsan  
zhangsan  xian           4  zhangsan  
brayden   xian           5  zhangsan  
lisi      xian           5  zhangsan  
red       xian           5  zhangsan  
zhangsan  xian           5  zhangsan  
brayden   xian           6  brayden   
lisi      xian           6  brayden   
red       xian           6  brayden   
zhangsan  xian           6  brayden   
brayden   xian           7  (NULL)    
lisi      xian           7  (NULL)    
red       xian           7  (NULL)    
zhangsan  xian           7  (NULL) 

3 执行ON

在虚拟表T_1基础上执行 ON 条件(ON a.user_id = b.user_id)进行过滤,得到虚拟表T-2,如下:

SELECT * FROM table1 a CROSS JOIN table2 b   ON a.user_id = b.user_id
 
user_id   city    order_id  user_id   
--------  ------  --------  ----------
brayden   xian           6  brayden   
lisi      xian           1  lisi      
lisi      xian           2  lisi      
zhangsan  xian           3  zhangsan  
zhangsan  xian           4  zhangsan  
zhangsan  xian           5  zhangsan 

4 添加外部行

如果连接类型为OUTER JOIN时才发生,如LEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOIN。在大多数的时候,我们都是会省略掉OUTER关键字的,OUTER表示的就是外部行,在T_2 的基础上进行操作,产生下面的虚拟表T_3,如下:

user_id   city    order_id  user_id   
--------  ------  --------  ----------
lisi      xian           1  lisi      
lisi      xian           2  lisi      
zhangsan  xian           3  zhangsan  
zhangsan  xian           4  zhangsan  
zhangsan  xian           5  zhangsan  
brayden   xian           6  brayden   
red       xian      (NULL)  (NULL)

5 执行where条件过滤

mysql 从左到右,oracle 从右到左。

在T_3的基础上执行条件 WHERE a.city = 'xian' 过滤输出结果集,得到虚拟表T_4,如下:

user_id   city    order_id  user_id   
--------  ------  --------  ----------
lisi      xian           1  lisi      
lisi      xian           2  lisi      
zhangsan  xian           3  zhangsan  
zhangsan  xian           4  zhangsan  
zhangsan  xian           5  zhangsan  
brayden   xian           6  brayden   
red       xian      (NULL)  (NULL)   

但是在使用 WHERE 子句时,需要注意以下两点:

  1. 由于数据还没有分组,因此现在还不能在 WHERE 过滤器中使用 where_condition=MIN(col) 这类对分组统计的过滤;

  2. 由于还没有进行列的选取操作,因此在 SELECT 中使用列的别名也是不被允许的,如:SELECT city as c FROM t WHERE c='shanghai'; 是不允许出现的。

6 执行GROUP BY分组

根据条件( GROUP BY a.user_id)在虚拟表T_4基础上进行分组,得到虚拟表T_5,如下:

user_id   city    order_id  user_id   
--------  ------  --------  ----------
brayden   xian           6  brayden   
lisi      xian           1  lisi      
red       xian      (NULL)  (NULL)    
zhangsan  xian           3  zhangsan 

7 执行HAVING过滤

HAVING 子句主要和 GROUP BY 子句配合使用,在虚拟表T_5基础上进行过滤,得到虚拟表T_6,如下:

user_id  city    order_id  user_id  
-------  ------  --------  ---------
brayden  xian           6  brayden  
red      xian      (NULL)  (NULL)  

8 SELECT列表

在T_6的基础上查询列表,产生虚拟表T_7,如下:

user_id  total_orders  
-------  --------------
brayden               1
red                   0

9 执行DISTINCT子句

如果在查询中指定了 DISTINCT 子句,则会创建一张内存临时表(如果内存放不下,就需要存放在硬盘了)。这张临时表的表结构和上一步产生的虚拟表VT7是一样的,不同的是对进行 DISTINCT 操作的列增加了一个唯一索引,以此来除重复数据。

10 执行ORDER BY子句

对虚拟表中的内容按照指定的列进行排序,然后返回一个新的虚拟表T_8,我们执行测试SQL语句中的 ORDER BY total_orders DESC

11 执行LIMIT子句

LIMIT 子句从上一步得到的T_8虚拟表中选出从指定位置开始的指定行数据。对于没有应用 ORDER BYLIMIT 子句,得到的结果同样是无序的,所以,很多时候,我们都会看到 LIMIT 子句会和 ORDER BY 子句一起使用。

MySQL 数据库的 LIMIT 支持如下形式的选择:

LIMIT n, m

表示从第 n 条记录开始选择 m 条记录。而很多开发人员喜欢使用该语句来解决分页问题。对于小数据,使用 LIMIT 子句没有任何问题,当数据量非常大的时候,使用 LIMIT n, m 是非常低效的。因为 LIMIT 的机制是每次都是从头开始扫描,如果需要从第60万行开始,读取3条数据,就需要先扫描定位到60万行,然后再进行读取,而扫描的过程是一个非常低效的过程。所以,对于大数据处理时,是非常有必要在应用层建立一定的缓存机制。

12 本文作者及团队介绍

杨君,主要从事系统服务端开发工作,负责设备相关业务模块,对系统高并发,性能优化有一定的沉淀,来自三翼鸟数字化技术平台-智能运营平台团队。

智能运营平台团队主要以用户行为数据为基础,利用推荐引擎为用户提供“千人千面”的个性化推荐服务,改善用户体验,持续提升核心业务指标。通过构建高效、智能的线上运营系统,全面整合数据资产,实现数据分析-人群圈选-用户触达-后效分析-策略优化的运营闭环,并提供可视化报表,一站式操作提升数字化运营效率。