本文介绍了SQL查询优化中的临时表使用技巧，包括with和createtemporarytable方法，强调了性能提升和代码可读性。作者还讨论了字段计算的最佳实践和大表小表在连接时的顺序选择，指出依赖于具体数据库架构的优化策略。
零、前言
在短短几年的数据分析师生涯中，个人的SQL代码能力经过了多轮的迭代，从简单查询简单聚合，到复杂嵌套查询及各种窗口函数，再到能写出兼顾性能和代码美观的数百行超长SQL，中间有一些心得，借此灵感系统性的写一篇关于SQL语法优化的文章。
==本文仅针对SQL查询语句展开，详写如何通过优化SQL查询语句来实现性能提升，以实现大数据量的查询，尽可能写得清晰，也能直接上手就用。==对于更底层的索引、事务等优化由于日常工作接触较少，理解上可能有所欠缺，后续有机会再展开。
==PS：==本文篇幅…不算长，主要集中在第一节临时表代码演示的部分，示例的SQL代码有一定的个人风格，部分换行的写法是便于日常快捷注释及维护，大家酌情食用。
一、临时表的使用
把临时表放第一个点来讲，是因为它已经能给解决大部分场景的问题，包括但不限于查询性能、代码的可读性及维护难度。
临时表通常有2种使用方法，使用方法如下：
with [表名] as (select……) select……;create temporary table [表名] as select ……; select……;（推荐使用的方法，下文详讲）
先看写法，后面再讲讲他们的差异
1.1 常见写法
简单看个示例，下面是一段“简单”的做库存数据校验的SQL语句，使用常见的嵌套写法：
select tb1.stock_date, tb1.stockorg_code, tb1.product_code, tb1.color_code, tb1.size_code, tb1.stock_qty stock_zipper_qty, tb2.stock_qty ods_stock_qty
from (select '2023-01-01' stock_date, tb3.stockorg_code, tb2.product_code, tb2.color_code, tb2.size_code, tb1.stock_qty
from tenant_edw.fct_stock_zipper tb1
left join tenant_edw.dim_sku tb2 on tb1.sku_sk = tb2.sku_sk and tb2.etl_end = '9999-12-31'
left join tenant_edw.dim_stockorg tb3 on tb1.stockorg_sk = tb3.stockorg_sk and tb3.etl_end = '9999-12-31'
where tb1.stock_date_begin <= '2023-01-02' and tb1.stock_date_end > '2023-01-02'
) tb1 --库存拉链表
left join (select stock_date, ckdm stockorg_code, spdm product_code, gg1dm color_code, gg2dm size_code, sl stock_qty
from tenant_ods.spkcb
where stock_date = '2023-01-01' and kwdm = '000'
) tb2 -- ods原始数据
on tb1.stockorg_code = tb2.stockorg_code and tb1.product_code = tb2.product_code and tb1.color_code = tb2.color_code and tb1.size_code = tb2.size_code
where tb1.stock_qty <> tb2.stock_qty -- 取库存对不上的数据
到这里，我相信认真去看这十几行代码的人都已经疯掉一半了，其实这里的重点不在于代码本身 [手动狗头]
1.2 with方法
那么下面将用第1种with的方法，结合我个人的换行风格对这段代码进行优化，这里暂时还是书写优化，未涉及到性能，如下：
with tmp_stock_zipper as(--库存拉链表
select '2023-01-01' stock_date,
tb3.stockorg_code,
tb2.product_code,
tb2.color_code,
tb2.size_code,
tb1.stock_qty
from tenant_edw.fct_stock_zipper tb1
left join tenant_edw.dim_sku tb2
on tb1.sku_sk = tb2.sku_sk
and tb2.etl_end = '9999-12-31'
left join tenant_edw.dim_stockorg tb3
on tb1.stockorg_sk = tb3.stockorg_sk
and tb3.etl_end = '9999-12-31'
where tb1.stock_date_begin <= '2023-01-02'
and tb1.stock_date_end > '2023-01-02'
)
, tmp_spkcb as(--ods原始数据
select stock_date,
ckdm stockorg_code,
spdm product_code,
gg1dm color_code,
gg2dm size_code,
sl stock_qty
from tenant_ods.spkcb
where stock_date = '2023-01-01'
and kwdm = '000'
)
select tb1.stock_date,
tb1.stockorg_code,
tb1.product_code,
tb1.color_code,
tb1.size_code,
tb1.stock_qty stock_zipper_qty,
tb2.stock_qty ods_stock_qty
from tmp_stock_zipper tb1
left join tmp_spkcb tb2
on tb1.stockorg_code = tb2.stockorg_code
and tb1.product_code = tb2.product_code
and tb1.color_code = tb2.color_code
and tb1.size_code = tb2.size_code
where tb1.stock_qty <> tb2.stock_qty --取库存对不上的数据
;
这段代码中，使用with方法创建了2个临时表，最后再进行一次查询，得到最终结果，十几行代码变成几十行，其实不仅结构更清晰，也大幅提高的可读性。
1.3 create temporary table方法
下面再展示方法2，可读性上跟with方法大差不差：
drop table if exists tmp_stock_zipper;
create temporary table tmp_stock_zipper as --库存拉链表
select '2023-01-01' stock_date,
tb3.stockorg_code,
tb2.product_code,
tb2.color_code,
tb2.size_code,
tb1.stock_qty
from tenant_edw.fct_stock_zipper tb1
left join tenant_edw.dim_sku tb2
on tb1.sku_sk = tb2.sku_sk
and tb2.etl_end = '9999-12-31'
left join tenant_edw.dim_stockorg tb3
on tb1.stockorg_sk = tb3.stockorg_sk
and tb3.etl_end = '9999-12-31'
where tb1.stock_date_begin <= '2023-01-02'
and tb1.stock_date_end > '2023-01-02'
;
drop table if exists tmp_spkcb;
create temporary table tmp_spkcb as --ods原始数据
select stock_date,
ckdm stockorg_code,
spdm product_code,
gg1dm color_code,
gg2dm size_code,
sl stock_qty
from tenant_ods.spkcb
where stock_date = '2023-01-01'
and kwdm = '000'
;
select tb1.stock_date,
tb1.stockorg_code,
tb1.product_code,
tb1.color_code,
tb1.size_code,
tb1.stock_qty stock_zipper_qty,
tb2.stock_qty ods_stock_qty
from tmp_stock_zipper tb1
left join tmp_spkcb tb2
on tb1.stockorg_code = tb2.stockorg_code
and tb1.product_code = tb2.product_code
and tb1.color_code = tb2.color_code
and tb1.size_code = tb2.size_code
where tb1.stock_qty <> tb2.stock_qty --取库存对不上的数据
;
1.4 小结
这两个方法本身不难，多写几次就能养成习惯，相较于常规嵌套查询，它们都有很明显的优点：
- 对于查询链路比较长、涉及到多个表多步骤查询的场景，可以很清晰地梳理和呈现查询逻辑；
- 使用临时表可以在大数据表中筛选出需要用的数据作为小表，再用小表进行连接，可大幅提升查询效率及性能；
- 同时也有前面提到的较好的可读性，也更易于维护。
回到这两个方法的差异，细心的朋友会发现，with方法中整段查询语句就一个;分号，在结尾，也就是它跟常规嵌套查询一样都是一次性查询结果；而create temporary table方法的查询中，有多个分号，每个临时表独立为一个语句，最后的总查询也是独立的语句。这中间的差异在于：
- 常规嵌套查询以及
with：这两种方法都是一次性将数据读取到缓存中，把所有需要的数据一次性处理，这2种方式当遇到大数据量时，很容易出现内存不足无法查询，甚至把服务器跑崩（别问我怎么知道的…）； create temporary table是分步将数据读取出来，在最终的查询语句中，用到哪个表再调用哪个表，一步步操作，后续不再使用的temp表可以及时drop table tmp_a释放内存，对于需要创建多个临时表的场景，这个方法能更好的降低资源的占用，常规嵌套以及with写法就没法做到。
基于以上，我个人会推荐尽量养成使用create temporary table方法的习惯。
注意关于create temporary table ……的写法，不同架构的数据库会有所差异，对于MySQL、Hive这两个架构是适用的，对于PostgreSQL和Greenplum写法会变成create temp table ……，而Impala和sparksql则不支持这种写法，其中sparksql需要结合Python来搭建临时表。
二、查询内容
咳咳，这节就一句话，应该大家都有共识：尽量不要用select *，只select必须的字段，减少列上的数据量。
三、字段的计算
这里指的是使用各种函数对字段进行计算，如：to_date(a.bill_date) > ‘2023-01-01’。所有需要使用函数或对字段进行算术运算的，尽可能都放在select子句中，不要放在where、on、group by中，对于需要计算的字段，宁可多创建一个临时表单独计算。
原因是在SQL查询中，如果在where、on、group by等子句中对字段进行计算，可能会导致数据库无法有效地使用索引，从而降低查询性能。因为当对字段进行计算时，数据库需要对每一行数据进行计算才能确定其是否满足条件，这个过程称为全表扫描，相比于利用索引进行的查找，全表扫描的效率要低得多。
当在查询中使用函数（如to_date）或者进行算术运算时，数据库通常不能利用索引，因为索引是在原始数据上构建的，而不是在计算结果上构建的。因此，在where或者on子句中进行计算，即使对应的字段上有索引，也可能无法被有效利用，从而导致查询性能下降。
当然，日常小数据量查询的场景中这个点也可以不那么关注，只是当需要将代码标准化到产品中、或应对大数据的场景时才需要特别的关注。
四、大表在前还是小表在前
这个问题困扰了我很久，来自于一道面试题，反复查询资料后得到了以下结论：在进行表连接时，关于大表还是小表在前的问题，其实取决于使用的具体数据库架构以及它的查询优化器。
例如，对于MySQL，查询优化器会自动决定连接的顺序，无论在查询语句中如何书写，都不会影响最终的执行计划。MySQL查询优化器会根据统计信息，尝试找出最优的执行计划，包括表的连接顺序、使用哪个索引等等，同时还收到不同版本的影响，各个版本可能都存在一定的差异。
而对于Hive，则更倾向于把小表放在前面。因为Hive的查询优化器并没有像MySQL那样的成熟优化功能，而且Hive更倾向于用于处理大数据，如果小表在前，Hive可以将小表数据广播到每个节点，然后每个节点只需要处理自己的大表数据，可以有效减少网络传输压力，提高查询效率。
总的来说，这个问题并没有一个固定的答案，要看你使用的具体数据库架构以及它的查询优化器如何处理。
五、写在最后
本文若有不严谨或错误的表述欢迎大神们评论提醒~
若本文对各位有所帮助，也请嫑吝啬您手中的赞和收藏~~ 非常感谢！
扫一扫
1713
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
