MySQL各部分的执行顺序

前几天参加了一个公司的面试,到了后面面试官出了一个SQL相关的题目:

给定访问id,用户id,页面id,访问时间,求出最近一个月对id为100的页面有访问记录且访问该页面次数大于10次的用户id

数据的形式类似于以下这样(表名为views):

view_id user_id page_id view_time
1 abc 100 2021-01-15
2 def 101 2021-02-01
... ... ... ...

当时为了稳妥起见,我的第一反应是使用窗口函数,

select distinct(user_id) from
(select *, count(*) over (partition by page_id, user_id) as view_cnt,
last_value(view_time, 0) over (partition by page_id, user_id) as recent_time
from views)
where view_cnt > 10 and datediff(curdate(), recent_time) < 31 and page_id = 100 

然后面试官问:“还有没有什么简便的方法么?”
很明显他的意思是要用传统的groupby来完成这个查询,确实我之前的查询又是用窗口函数又是加了distinct确实是复杂一些。
于是我用group by再写了一遍。

select (user_id) from
(select *, count(*) as view_cnt,
max(view_time) as recent_time
from views
group by page_id, user_id)
where view_cnt > 10 and datediff(curdate(), recent_time) < 31 and page_id = 100

看完我的查询之后,面试官又问了一句:“可以不需要使用嵌套查询吗?”
当时我的回答是”应该不行,如果不使用嵌套而直接在group by后面加having的话sql会报错,就和where如果使用别名查询就会报错一样“
后面面试完想了一下,发现自己当时回答得不好,不是正确的但也不完全错,不是正确的原因是按照sql的规则having后面是可以拿聚合函数做判断的,但是不完全错的原因是如果having用的是像我之前设置的别名来判断的话,确实是会出错的。(虽然mysql在5.6之后基于sql的规则对group by进行拓展,支持这种写法。但在其它sql上面用别名having还是不行的)
我们可以从SQL运行时各部分的执行顺序来进行分析,当我们选择执行一个SQL语句的时候,它会按照以下的顺序来进行操作,

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. DISTINCT
  7. ORDER BY
  8. LIMIT

这个执行顺序的设计是很巧妙的,我说一下我自己对于上述顺序的理解,
1. FROM
顾名思义,当执行查询语句的时候,首先需要知道的是它需要哪些表,正如我们去一个地方需要知道它的具体位置一样。如果需要多个表的话在这一部分也需要按照一定的顺序进行表的join操作。
2. WHERE
当确定我们需要读取哪一张表(或者多张表)的数据之后,我们就需要进行where的filter操作,根据filter尽量减少读取的数据数量。

与此同时,由于where早于select执行,那么它是不知道select语句里面定义的别名的,所以在这里使用别名会导致SQL报错。

那么问题来了,为什么where的优先级要比group by,having, select之类的要高呢?
第一个原因是可以减少不必要的查询量,加快执行语句的速度,类似于Apache Spark在对查询语句进行逻辑优化时需要用到的谓词下推类似的道理。举个栗子,比如我们可能需要userid从100到300的用户对于某一个页面的浏览次数,那么如果先执行group by再执行where的话,userid小于100的用户的数据也会被汇总进去,但实际上这些部分的数据是完全不需要的,计算它们完全是浪费系统资源(而且group by操作本身就是很耗资源的操作)
3. GROUP BY
在完成where操作的过滤之后,如果语句中有group by的话则会对过滤后的数据进行聚合操作,聚合操作是多对一的转换,因此在聚合操作过后,除了用于group by的字段之外,其它字段的原始数据将会丢失,只能得到它们相应的聚合结果(比如sum(), avg()这样)
在完成聚合操作之后,参与group by的字段以及其它字段对应的聚合值已经处于已知状态,后续的操作可以直接使用它们。
4. HAVING
HAVING操作主要做的是对group by之后的分组结果进行过滤,可以根据参与group by的字段进行过滤,也可以根据其它字段的聚合值进行过滤。(因为聚合值在这里已经算是已知数据)因此这里是可以拿聚合函数做判断的,比如最开始的那个查询的例子,可以直接写成以下的形式,

select user_id 
from views
where page_id = 100
group by page_id, user_id
having count(*) > 10 and datediff(curdate(), max(view_time)) < 31 and page_id = 100

这里需要注意的是,虽然聚合函数可以用于做having里面的判断条件,但是它们在select里面设置的别名是不可以的,因为此时还没进行select阶段,SQL并不知道它们的别名是什么样子。
(MySQL5.6及以上版本对group by进行了拓展,这些版本可以在having的时候使用别名。如果不希望使用的话,可以将MySQL的sql_mode调为ONLY_FULL_GROUP_BY 参考链接 )

HAVING并不是一定要和group by成对出现的,它也可以单独存在,在没有group by的时候,此时默认只有一个组,但是需要注意的是这时having里面参与过滤的字段需要在select里面存在,不然having会不知道这是分组里面的内容而导致报错。
5. SELECT
选取结果集中相对应的字段,在select中为字段设置的别名在此阶段及之后的操作中生效。
6. DISTINCT
去重操作,放在select之后有个原因是去重操作是要根据select里面所选字段来进行的。

需要注意的是distinct去重是只去重所有和已选字段完全重复的数据,举个栗子,select distinct name, city只会去重姓名和城市完全重复的数据,而如果只有姓名相同,而城市不同,那么不会发生去重操作

7. ORDER BY
对得到的结果按照特定字段顺序进行排列,这里可以使用别名
8. LIMIT
设置显示结果集中的几条数据

通过分析MySQL中各部分的执行顺序,我们就不难理解为什么where不能有别名,而having可以用聚合函数来判断的原因,而且借此机会重新温习一遍SQL各部分对应的功能,加深理解,可以说是一举两得。

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 204,053评论 6 478
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,527评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 150,779评论 0 337
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,685评论 1 276
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,699评论 5 366
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,609评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,989评论 3 396
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,654评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,890评论 1 298
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,634评论 2 321
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,716评论 1 330
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,394评论 4 319
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,976评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,950评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,191评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 44,849评论 2 349
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,458评论 2 342

推荐阅读更多精彩内容