乘风原创程序

  • SQL查询的底层运行原理分析
  • 2020/10/8 16:14:30
  • SQL查询的底层运行原理分析

    SQL 语言无处不在。SQL 已经不仅仅是技术人员的专属技能了,似乎人人都会写SQL,就如同人人都是产品经理一样。如果你是做后台开发的,那么CRUD就是家常便饭。如果你是做数仓开发的,那么写SQL可能占据了你的大部分工作时间。我们在理解 SELECT 语法的时候,还需要了解 SELECT 执行时的底层原理。只有这样,才能让我们对 SQL 有更深刻的认识。本文分享将逐步分解SQL的执行过程,剖析其底层运行原理,希望对你有所帮助。

    数据准备

    本文旨在说明SQL查询的执行过程,不会涉及太复杂的SQL操作,主要涉及两张表:citizencity,具体数据如下所示:

    CREATE?TABLE?citizen?(?
    ????name?CHAR?(?20?),?
    ????city_id?INT?(?10?)?
    );
    
    
    CREATE?TABLE?city?(
    ????city_id?INT?(?10?),?
    ????city_name?CHAR?(?20?)?
    );
    
    INSERT?INTO?city
    VALUES
    ?(?1,?"上海"?),
    ?(?2,?"北京"?),
    ?(?3,?"杭州"?);
    ?
    ?
    INSERT?INTO?citizen
    VALUES
    ("tom",3),
    ("jack",2),
    ("robin",1),
    ("jasper",3),
    ("kevin",1),
    ("rachel",2),
    ("trump",3),
    ("lilei",1),
    ("hanmeiei",1);
    

    查询执行顺序

    本文所涉及的查询语句如下,主要是citizen表与city表进行join,然后筛掉city_name != "上海"的数据,接着按照city_name进行分组,统计每个城市总人数大于2的城市,具体如下:

    查询语句

    SELECT?
    ????city.city_name?AS?"City",
    ????COUNT(*)?AS?"citizen_cnt"
    FROM?citizen
    ??JOIN?city?ON?citizen.city_id?=?city.city_id?
    WHERE?city.city_name?!=?'上海'
    GROUP?BY?city.city_name
    HAVING?COUNT(*)?>=?2
    ORDER?BY?city.city_name?ASC
    LIMIT?2
    

    执行步骤

    上面SQL查询语句的书写书序是:

    SELECT?...?FROM?...?WHERE?...?GROUP?BY?...?HAVING?...?ORDER?BY?...
    

    但是执行顺序并不是这样,具体的执行顺序如下步骤所示:

    • 1.获取数据 (From, Join)

    • 2.过滤数据 (Where)

    • 3.分组 (Group by)

    • 4.分组过滤 (Having)

    • 5.返回查询字段 (Select)

    • 6.排序与分页 (Order by & Limit / Offset)

    执行的底层原理

    其实上面所说的SQL执行顺序就是所谓的底层原理,当我们在执行SELECT语句时,每个步骤都会产生一张虚拟表(virtual table),在执行下一步骤时,会将该虚拟表作为输入。指的注意的是,这些过程是对用户透明的。

    你可以注意到,SELECT 是先从FROM 这一步开始执行的。在这个阶段,如果是多张表进行JOIN,还会经历下面的几个步骤:

    获取数据 (From, Join)

    • 首先会通过 CROSS JOIN 求笛卡尔积,相当于得到虚拟表 vt1-1;

    • 接着通过ON 条件进行筛选,虚拟表 vt1-1 作为输入,输出虚拟表 vt1-2;

    • 添加外部行。我们使用的是左连接、右链接或者全连接,就会涉及到外部行,也就是在虚拟表 vt1-2 的基础上增加外部行,得到虚拟表 vt1-3

    过滤数据 (Where)

    经过上面的步骤,我们得到了一张最终的虚拟表vt1,在此表之上作用where过滤,通过筛选条件过滤掉不满足条件的数据,从而得到虚拟表vt2。

    分组 (Group by)

    经过where过滤操作之后,得到vt2。接下来进行GROUP BY操作,得到中间的虚拟表vt3。

    分组过滤 (Having)

    在虚拟表vt3的基础之上,使用having过滤掉不满足条件的聚合数据,得到vt4。

    返回查询字段 (Select)

    当我们完成了条件筛选部分之后,就可以筛选表中提取的字段,也就是进入到 SELECT 和 DISTINCT 阶段。首先在 SELECT 阶段会提取目标字段,然后在 DISTINCT 阶段过滤掉重复的行,分别得到中间的虚拟表 vt5-1 和 vt5-2。

    排序与分页 (Order by & Limit / Offset)

    当我们提取了想要的字段数据之后,就可以按照指定的字段进行排序,也就是 ORDER BY 阶段,得到虚拟表 vt6。最后在 vt6 的基础上,取出指定行的记录,也就是 LIMIT 阶段,得到最终的结果,对应的是虚拟表 vt7

    详细执行步骤分析

    Step 1:获取数据 (From, Join)

    FROM?citizen
    JOIN?city?
    

    该过程的第一步是执行From子句中的语句,然后执行Join子句。这些操作的结果是得到两个表的笛卡尔积。

    namecity_idcity_idcity_name
    tom31上海
    tom32北京
    tom33杭州
    jack21上海
    jack22北京
    jack23杭州
    robin11上海
    robin12北京
    robin13杭州
    jasper31上海
    jasper32北京
    jasper33杭州
    kevin11上海
    kevin12北京
    kevin13杭州
    rachel21上海
    rachel22北京
    rachel23杭州
    trump31上海
    trump32北京
    trump33杭州
    lilei11上海
    lilei12北京
    lilei13杭州
    hanmeiei11上海
    hanmeiei12北京
    hanmeiei13杭州

    在FROM和JOIN执行结束之后,会按照JOIN的ON条件,筛选所需要的行

    ON?citizen.city_id?=?city.city_id
    
    namecity_idcity_idcity_name
    tom33杭州
    jack22北京
    robin11上海
    jasper33杭州
    kevin11上海
    rachel22北京
    trump33杭州
    lilei11上海
    hanmeiei11上海

    Step 2:过滤数据 (Where)

    获得满足条件的行后,将传递给Where子句。这将使用条件表达式评估每一行。如果行的计算结果不为true,则会将其从集合中删除。

    WHERE?city.city_name?!=?'上海'
    
    namecity_idcity_idcity_name
    tom33杭州
    jack22北京
    jasper33杭州
    rachel22北京
    trump33杭州

    Step 3:分组 (Group by)

    下一步是执行Group by子句,它将具有相同值的行分为一组。此后,将按组对所有Select表达式进行评估,而不是按行进行评估。

    GROUP?BY?city.city_name
    
    GROUP_CONCAT(citizen.name)city_idcity_name
    jack,rachel2北京
    tom,jasper,trump3杭州

    Step 4:分组过滤 (Having)

    对分组后的数据使用Having子句所包含的谓词进行过滤

    HAVING?COUNT(*)?>=?2
    

    Step 5:返回查询字段 (Select)

    在此步骤中,处理器将评估查询结果将要打印的内容,以及是否有一些函数要对数据运行,例如Distinct,Max,Sqrt,Date,Lower等等。本案例中,SELECT子句只会打印城市名称和其对应分组的count(*)值,并使用标识符“ City”作为city_name列的别名。

    SELECT?
    ????city.city_name?AS?"City",
    ?COUNT(*)?AS?"citizen_cnt"
    
    citycitizen_cnt
    北京2
    杭州3

    Step 6:排序与分页 (Order by & Limit / Offset)

    查询的最后处理步骤涉及结果集的排序与输出大小。在我们的示例中,按照字母顺序升序排列,并输出两条数据结果。

    ORDER?BY?city.city_name?ASC
    LIMIT?2
    
    citycitizen_cnt
    北京2
    杭州3

    总结

    本文主要剖析了SQL语句的执行顺序和底层原理,基本的SQL查询会分为六大步骤。本文结合具体事例,给出了每一步骤的详细结果,这样会对其执行的底层原理有更加深刻的认识。

    本文地址:https://blog.csdn.net/young_0609/article/details/108967311