想成为一个有逼格的资深程序员,写每一行代码时都应该去思考,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 JOIN、RIGHT OUTER JOIN和FULL 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 子句时,需要注意以下两点:
-
由于数据还没有分组,因此现在还不能在 WHERE 过滤器中使用 where_condition=MIN(col) 这类对分组统计的过滤;
-
由于还没有进行列的选取操作,因此在 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 BY 的 LIMIT 子句,得到的结果同样是无序的,所以,很多时候,我们都会看到 LIMIT 子句会和 ORDER BY 子句一起使用。
MySQL 数据库的 LIMIT 支持如下形式的选择:
LIMIT n, m
表示从第 n 条记录开始选择 m 条记录。而很多开发人员喜欢使用该语句来解决分页问题。对于小数据,使用 LIMIT 子句没有任何问题,当数据量非常大的时候,使用 LIMIT n, m 是非常低效的。因为 LIMIT 的机制是每次都是从头开始扫描,如果需要从第60万行开始,读取3条数据,就需要先扫描定位到60万行,然后再进行读取,而扫描的过程是一个非常低效的过程。所以,对于大数据处理时,是非常有必要在应用层建立一定的缓存机制。
12 本文作者及团队介绍
杨君,主要从事系统服务端开发工作,负责设备相关业务模块,对系统高并发,性能优化有一定的沉淀,来自三翼鸟数字化技术平台-智能运营平台团队。
智能运营平台团队主要以用户行为数据为基础,利用推荐引擎为用户提供“千人千面”的个性化推荐服务,改善用户体验,持续提升核心业务指标。通过构建高效、智能的线上运营系统,全面整合数据资产,实现数据分析-人群圈选-用户触达-后效分析-策略优化的运营闭环,并提供可视化报表,一站式操作提升数字化运营效率。