数据仓库开发 SQL 使用技巧总结

作者：dcguo

使用 sql 做数仓开发有一段时间了，现做一下梳理复盘，主要内容包括 sql 语法、特性、函数、优化、特殊业务表实现等。

mysql 数据结构

常用 innodb 存储为 B+ 树

特点

• 多路平衡树，m 个子树中间节点就包含 m 个元素，一个中间节点是一个 page(磁盘页) 默认 16 kb；
• 子节点保存了全部得元素，父节点得元素是子节点的最大或者最小元素，而且依然是有序得；
• 节点元素有序，叶子节点双向有序，便于排序和范围查询。

优势

• 平衡查找树，logn 级别 crud；
• 单一节点比二叉树元素更多，查询 io 次数更少；
• 所有查询都要查询到叶子节点性能稳定；
• 所有节点形成逻辑有序链表，便于排序查询范围查询。

索引

优化索引是提升性能主要手段，主要用到的索引基本为以下三种：

1. 聚簇索引

一般表会用自增主键做聚集索引，没有的话 mysql 会默认创建，但是一旦确定之后这个主建得更改代价就会很高，所以建表时候要考虑自增主建不能频繁 update

2. 非聚簇索引

根据实际情况自行添加得索引都是辅助索引，就是一个为了寻找主键索引得二级索引，就是先找到主键索引再通过主键索引找数据。

辅助索引可能没有我们需要的 select 列，这就引出 innodb 非聚集索引独有得耗时操作 回表，sql 重要得优化操作 索引覆盖。

覆盖索引: 

• 指从辅助索引就可以得到查询结果，不需要 “回” 到聚集索引中查询；

• 辅助索引可能是等值查询，范围查询或者全索引扫描。

回表:

• 对二级查询中查询到的每个主键，都需要回到聚集索引中在查询数据行。

比如开发人员最喜爱得 select * ... 就经常会回表

回表理解：select * 导致业务覆盖不到索引，那么优化器决策后很可能就不走辅助索引了，因为辅助索引上拿到的 key 太多了，随机回表开销太大，还不如走聚集索引，经常出现再 范围查询, join 操作 上，但是现在磁盘都是 ssd, 不怕随机读，所以我们又可以用 force index() 操作强制优化器走辅助索引。

demo: 

-- 辅助索引
select * from t where a > '1999-08-01';

如果从辅助索引取，根据一些过滤条件得到 50w 行，此时我还需要回表 50w 次，50w*3 层 = 150w 次 i/o，而且因为回表时主键是无序的，所以是随机 io。

如果我不走辅助索引，直接顺序扫描这 150w 行的数据，需要 (50w*3 层) / (16KB/100B) 约 1w 次 i/o，而且是顺序 io 。

优化方案

mrr 将二级索引上查询出来的主键排序之后在回表，explain 得 extra 有一列 using mrr。

3. 复合索引

其实还是一个 b+ 树，每个节点是几个字段值 concat 起来的元组，比如复合索引 (a, b) 的 b+ 树上，对 (a) 列是有序的，对 (a, b)组合列也是有序的，但是对 (b) 列却不一定是有序的，对其叶子节点上带的 pk 列也是无序的。

聚簇索引/非聚簇索引

sql 语法

case when

--demo1 case 搜索函数 case when xxx then xxx else end
select
 id,
 case
 when score < 60
 then 'low'
 when score < 90
 then 'middle'
    else 'high'
    end as rank
from
 test

--demo2 简单 case 函数 case field when xxx then xxx else end
select
 id
 case score
 when 0
 then 'bad'
 when 100
 then 'good'
 else 'middle'
    end

with as

-- 定义临时表
with tmp as (select * from test)
-- 使用临时表
select * from tmp

-- 定义临时表
with tmp as (
    select id from test where score > 60
)
-- 使用临时表
select distinct id from tmp;

group by/with rollup

group by

主要是用来做数据聚合

需要选择字段作为聚合维度后，然后通过聚合函数得到汇总值的过程。

• count，sum，avg，...
• max/min，std，variance，...
• rank，first/last_value，row_number，...

demo:

select
 score,
 count(distinct id)
from
 test
group by
 score

优化: 

1. 分组是一个相对耗时的操作，我们可以先通过 where 缩小数据的范围之后，再分组；
2. 也可以将分组拆分，如果是大表多维度分组，可以使用 with as 语法先计算一部分得到临时表然后再利用临时表进行计算，sql 也可以简化 。

with rollup

rollup 是 group by 子句的扩展，rollup 选项允许包含表示小计的额外行，通常称为超级聚合行，以及总计行。

-- demo
-- 下图结果第三行就是超级聚合行
select
    name, sum(score)
from
    students
group by rollup(name)

tag:

如何区分超级聚合行得 NULL 和 普通 NULL？

使用 grouping 函数可以识别超级聚合形成的 NULL, 避免和普通的 NULL 混淆。

union/union all/intersect/except

用法基本类似，只举例部分

• union 并集
• intersect 交集
• except 差集

-- union 去重, union all 不去重
select column_name(s) from table_name1
union
select column_name(s) from table_name2

limit

limit 分页查询使用

1. 使用

   select
    id
   from
    test
   where
    id > 1000000 limit 20;
   

in/not in/exists/not exists/between

in/not in:  作用不用多说

exists/not exists：强调的是是否返回结果集

exists 用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值 true 或 false；exist 指定一个子查询，检测行的存在。

in/exists 对比 in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询；如果查询语句使用了 not in 那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引；所以无论那个表大，用 not exists 都比 not in 要快。

between: 如果表达式大于或等于 >= low 值且小于或等于 <= ) high 值，则 between 运算符返回 true

in/between 对比 连续数据使用 between 比用 in 好

-- in/notin
-- 略

-- exists/not exists(略)
-- 子查询是相关的, 对于 scores 表中的每一行，子查询检查 class 表中是否有对应的行。 如果有匹配行，则子查询返回一个使外部查询包含 scores 表中的当前行的子查询。 如果没有相应的行，则子查询不返回导致外部查询不包括结果集中 scores 表中的当前行的行
select
    id, score
from
    scores
where
    exists(
        select
            1
        from
            class
        where
            class.id = scores.id
    );

-- between/not between(略)
select
    id, score
from
 scores
where
    score between 2500 and 3000

join(inner join)/left join/right join/full join/隐式连接/cross join/left semi join/不等值连接

• 笛卡尔积 连接条件，如果该条件恒成立（比如 1=1 )，该连接就是笛卡尔连接。所以，笛卡尔连接输出的记录条数等于被连接表的各记录条数的乘积，若需要进行笛卡尔积连接，需使用专门的关键词 cross join。

  select
   a.id, b.id
  from
   scores as a
  cross join
   class as b
  on (1=1)
  

• join/inner join 内连接 略

• 不等值连接

  select
   a.a1, b.b1
  from
   a
  inner join
   b
  on
   a.c1<>b.c1
  order by
   a.a1
  

• left join/right join 外连接

• 左外连接

  略

• 右外连接

  略

• full join 全连接

• full join

  略

• left semi join 左半连接

  只显示左表中的记录。可通过在 left semi join， where ...in 和 where exists 中嵌套子查询来实现。左半连接与左外连接的区别是，左半连接将返回左表中符合 join 条件的记录，而左外连接将返回左表所有的记录，匹配不上 join 条件的记录将返回 null 值。

  select
   student_info.name, student_info.courseId
  from
   student_info
  left semi join
   course_info
  on
   student_info.courseId = course_info.courseId
  

• 隐式连接

  与内连接功能相同，返回两表中满足 where 条件的结果集，但不用 join 显示指定连接条件

  select
   student_info.name, course_info.courseName
  from
   student_info,course_info
  where
   student_info.courseId = course_info.courseId;
  

having

1. 使用聚合函数进行计算
2. 使用 having 子句筛选分组

where/on

join 时候 where/on 不可以混用

inner join 中 where 可以代替 on 但是 on 不能代替 where

on 是 using on 的简便写法

explain(mysql)

hive/spark/mysql 8.0

之前也没有接触过大数据相关知识，简单了解这几种 sql 其实大差不差，主要区别就是 hive/spark 操作的数据可以很大很大，单机存不下，所以数据文件位于分布式文件系统 HDFS。

hive: sql 解析引擎，将 sql 转译成 map/reduce job 然后再 hadoop 执行，相当于 hadoop 的客户端工具。

hive 的表其实就是 hdfs 的目录，按照表名分开文件夹，就是分区表，分区值就是子文件夹，可以直接再 map/reduce job 里面使用。

hive 数据存储格式

textfile, sequencefile, avro, rcfile, orcfile, parquet

• parquet

  不支持 update 操作(数据写成后不可修改), 不支持 acid 等 

  业务中建设数仓时，数仓数据都是由业务数据库拉取而来，数仓本身不进行什么更新操作，仅仅只有新增这种操作，所以使用 parquet。

内部表/外部表

hive 默认创建的是内部表

外部表没有办法直接 truncate table

创建外部表的 sql

CREATE EXTERNAL TABLE IF NOT EXISTS test (
    `quota`          STRING COMMENT '',
    `package`          INT    COMMENT '',
    `all_sys`         INT    COMMENT ''
) COMMENT 'test'
PARTITIONED BY (timeline STRING COMMENT '时间分区')
STORED AS PARQUET

关于删数据 ？

在删除内部表的时候，hive 将会把属于表的元数据和数据全部删掉；而删除外部表的时候，hive 仅仅删除外部表的元数据，数据是不会删除的，也就是说，外部表的数据其实不是 hive 自己管理的。

如何选择创建内部或者外部表 ？

但是作为一个经验，如果所有处理都需要由 hive 完成，应该创建表，否则使用外部表，基于此，我们使用数仓都是基于 hive 完成，所以应该创建内部表。

内置函数

函数主要有取值函数和变换函数等

• round，abs，ceil

• sin，cos，sqrt

时间

• unix 时间戳转字符串

• from_unixtime(unix_timestamp(), 'yyyy-MM-dd')
• from_unixtime(unix_timestamp(), '%Y-%m-%d')

• 截取部分日期，其他部分默认为 01

• select trunc('2009-02-12', 'year')

• 字符串转 uninx 时间戳

• to_unix_timestamp('2022-04-27', 'yyyy-MM-dd')

随机数

• 获取 i ≤ r ≤ j 这个范围的随机整数 r
• select floor(i + (rand() * (j-i+1))

字符串

-- 函数用于将多行数据聚合为单行，从而提供与特定值关联的数据列表，它将以逗号来分割列表
-- 结果
-- 1 python,c
-- 2 r,java
-- 3 swift
select id,wm_concat(',',name) as language from tmp_test group by id;

-- 字符串截取
-- CDE
select substring("ABCDE", 3)

-- json 解析
select get_json_object({"score":3}, '$.score')

统计函数

-- coalesce(expression_1, expression_2, ...,expression_n) 依次参考各参数表达式，遇到非 null 值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值
select coalesce(null, null, '200')

判断语句

-- 条件判断，满足第一个表达式返回 1, 否则返回 0
select if(1>0, 1, 0)

列转行

• concat_ws

-- 结果: a#b#c
select concat_ws('#', 'a', 'b', 'c', null)

• collect_list/collect_set

-- 特性: 它们都是将分组中的某列转为一个数组返回，不同的是 collect_list 不去重而 collect_set 去重
-- collect_set 去重, collect_list 不去重
-- 还可以利用 collect 来突破 group by 的限制, hive 中在 group by 查询的时候要求出现在 select 后面的列都必须是出现在 group by 后面的，即 select 列必须是作为分组依据的列
select username, collect_list(video_name)[0] from t_visit_video group by username;

• group_concat

-- 结果
-- +----------+--------+
-- | lastname |  name  |
-- +----------+--------+
-- |     a    |  aa,ab |
-- |     b    |  ba,bb |
-- +----------+--------+
select last_name, group_concat(name) as name from test where lastname in ('a', 'b')

• lateral view explode

-- 数据准备
-- id score
-- 1  100,96,98,100,96,85
-- 2  98,97,100,85,99,100

-- 计算
-- 找到具备 100 的 id
select
 *
from
 socre
 lateral view explode(spilt(scores, ',')) score AS each_score
where
 each_score in ("100")

udf 函数

其实就是一个简单的函数，执行过程就是在 hive 转换成 mapreduce 程序后，执行 java 方法，类似于像 mapreduce 执行过程中加入一个插件，方便扩展。udf 只能实现一进一出的操作，如果需要实现多进一出，则需要实现 udaf。hive 可以允许用户编写自己定义的函数 udf，来在查询中使用。

使用 udf 函数进行业务内复杂结构字段提取

-- parse_components 就是业务种自定义的 udf 函数，用来解析一个复杂得动态字段，此字段根据不同的模板可能出现得字段枚举超过百种
select
    test...,
    parse_components(doc.components, '100', '101').test as template_field
from
 test
order by
    test

窗口函数

ps: 以前一直不知道为啥叫窗口函数，总联想到滑动窗口，但是其实不是这样

partition by 的结果是分组的结果，每个组都是不同的范围，窗口的意思就是范围

它可以做什么？

有些需求可以使用窗口函数完成，如下：

• 排名问题，根据科目进行分组
• top n 问题，找出每个科目前三的同学进行奖励
• 增量表问题
• 某字段某段时间移动平均
• 某种行为前百分之多少的数据

此时可以引入窗口函数

该函数也叫 olap 函数（online anallytical processing，联机分析处理），可以对数据库数据进行实时分析处理

它的执行顺序 where 之后

demo

就下面图 2 这个查询不用窗口该咋写？各种子查询想想都难受，so 使用窗口。

我们做到了，在一个单个 sql 中计算列的综合，以及每一列占总数得比例，以及当前列，这就是窗口函数得一个突破。

基本语法

-- partition by 用于给结果集分组，另外如果不指定那么会默认把整个结果集作为分组
-- partition by 需要分组的列名
-- order by 需要排序的列名
-- rows between 参与计算的行起始位置 and 参与计算的行终止位置
-- over括号中的那些如果不需要可以省略
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名> rows between <起始位置> and <终止位置>)
-- rows between 指定计算起始行和结束行的方法
-- rows between 3 preceding and current row 包括本行和前三行
-- rows between current row and 3 following 包括本行和后三行
-- rows between unbounded preceding and current row 包括本行和该分组内之前所有的行
-- rows between current row and unbounded following 包括本行和该分组内之后所有行
-- rows between 5 preceding and 1 following 包括前五行和后一行

-- over (order by x) 相当于 over(order by x rows between unbounded preceding and current now) 从前方无界到当前行

-- over () 相当于从前方无界到后方无界，整组内容

-- 另, partition 子句可省略省略就是不指定分组
-- 例:
select *, rank() over (order by scores desc) as ranking from students

哪些函数可以使用窗口函数

专用函数 rank/dens_rank/row_number

rank/dens_rank/row_number

这三个函数得区别是分组排序后得到的虚拟 rank 列不同

实际上此函数可以为查出来的每一行增加 rank 序号

• rank
• dens_rank
• row_number

注意

rank() 函数中得到的 rank 值可能是会出现重复值，如果要取 1 条，需要 sql 查到的数据不重复，rank = 1 不能保证仅取 1 条, 除非你使用得函数是 row_number()：

demo

大概场景就是，我们需要查询一张表，要按照某个字段 a 去排序另一个字段 b，并且每个 c 字段只取前 n 条数据

select a.id, a.a, a.b, a.c, a.d
  from (select t.id,
               t.a,
               t.b,
               t.c,
               t.d,
               rank() over(partition by t.a order by t.b desc) rk
          from test t) a
where rk < 4;

demo：寻找企业下第一个入住企业

--distinct_org_id
select
 *
from (
 select
  org.*,
  row_number() over (partition by org.id, org.name order by org.creat_time asc) rk
 from
  org_test as org
) as temp
where
 rk = 1

平均分组 ntile

它将有序分区的行分配到指定数量的大致相等的组或桶中

可用场景

• 求成绩再前百分之 20 的分数

demo:

-- 求成绩再前百分之 20 的分数
select
 score,
 ntile (5) over (order by score) buckets
from
 scores.ntile_demo
where
 buckets = 1;

错位 lag/lead

定义

• lag 提供对当前行之前的给定物理偏移的行的访问
• lead 提供对当前行之后的给定物理偏移量的行的访问

通过这两个函数可以在一次查询中取出同一字段的前 n 行的数据 lag 和后 n 行的数据 lead 作为独立的列, 更方便地进行进行数据过滤

可用场景

• 在比较同一个相邻的记录集内两条相邻记录

• 计算今日电表消耗(需计算今日电表度数和昨日差值)

demo:

-- 语法
-- lag(field, num, defaultvalue)
-- 函数可以在一次查询中取出当前行的同一字段 field 的前面第 num 行的数据，如果没有用 defaultvalue 代替
-- lead(field, num, defaultvalue)
-- 函数可以在一次查询中取出当前行的同一字段 field 的后面第 num 行的数据，如果没有用 defaultvalue 代替

-- 计算今日材料消耗(需计算今日材料损耗和昨日差值)
select name,
       data,
    --今日抄表值
       use as use_today,
    --前一天抄表值
       lag(use,1,0) over(partition BY name order by data desc) as use_yesday
from test

分组取最大最小 first_value/last_value

first_value() : 取分组内排序后，截止到当前行，第一个值

last_value():取分组内排序后，截止到当前行，最后一个值

简单地说你可以得到一列值中某字段上下其他行得字段值，和 lag/lead 函数有些近似

demo:

-- 数据准备
-- 计算每个月销售额，上一月销售额，下月销售额，相邻月销售额平均值
insert into dc_test_order values
(100, 1), (200, 2),
(300, 3), (400, 4),
(500, 5), (600, 6),
(700, 7), (800, 8),
(900, 9)

-- 计算
select month,
  first_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) prev_month,
  sum(tot_sales) monthly_sales,
  last_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) next_month,
  avg(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) rolling_avg
from dc_test_order
group by month
order by month;
-- 结果
|month|prev_month|monthly_sales|next_month|rolling_avg|
+-----+----------+-------------+----------+-----------+
|    1|     100.0|        100.0|     200.0|      150.0|
|    2|     100.0|        200.0|     300.0|      200.0|
|    3|     200.0|        300.0|     400.0|      300.0|
|    4|     300.0|        400.0|     500.0|      400.0|
|    5|     400.0|        500.0|     600.0|      500.0|
|    6|     500.0|        600.0|     700.0|      600.0|
|    7|     600.0|        700.0|     800.0|      700.0|
|    8|     700.0|        800.0|     900.0|      800.0|
|    9|     800.0|        900.0|     900.0|      850.0|
+-----+----------+-------------+----------+-----------+

分析函数 cume_dist，perent_rank

这两个函数使用较少，不支持 windows 字句

• cume_dist 小于等于当前值的行数/分组内总行数

• percent_rank 计算给定行的百分比排名

demo1:

-- 数据准备
-- 计算小于等于当前成绩的人数占总人数占比
insert into dc_test_scores values
(1, 100), (2, 200),
(3, 300), (4, 400),
(5, 500)

-- 计算
select
    id,
    score,
    cume_dist() over(order by score) as rn1
from
 test

-- 结果
|id|score|rn1|
+--+---------+
| 1|100.0|0.2|
| 2|200.0|0.4|
| 3|300.0|0.6|
| 4|400.0|0.8|
| 5|500.0|1.0|
+--+-----+---+

demo2: 

-- 数据准备
-- 给定行的百分比排名
insert into dc_test_scores values
(1, 100), (2, 200),
(3, 300), (4, 400),
(5, 500)

--计算
select
 id,
 score,
 percent_rank() over (partition by id order by score) as rn1
from
 test

--结果
|id|score|rn1 |
+--+----------+
| 1|100.0|0.0 |
| 2|200.0|0.25|
| 3|300.0|0.5 |
| 4|400.0|0.75|
| 5|500.0|1.0 |
+--+-----+----+

聚合函数 sum, avg, count, max, min

• 聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）
• 同时可以看出每一行数据，对整体统计数据的影响

场景

• 计算几天内平均数目，累计值，...

demo

• 计算三天内平均金额

  with test_tb (t, amount) as (
    values(1, 3),
          (2, 6),
          (3, 3),
          (4, 9)
  )
  select
   t, amount, avg(amount) over (order by t rows between 1 preceding and 1 following)
  from
   test_tb
  order by
   t
  

• 计算总和

  with test_tb (t, cnt) as (
    values(1, 1),
          (2, 2),
          (3, 3),
          (4, 4),
          (5, 5),
          (6, 9)
  )
  seletc
   t, cnt, sum(cnt) over (order by t rows between unbounded preceding and current row)
  from
   test_tb
  order by
   t
  

疑问

给 mysql 字段指定宽度

对证数类型指定宽度，比如 Int(11) 不会起到作用，int 使用 16 为存储空间，他的表示范围是确定的，int(1) 和 int(20) 没有区别

代码分表和分区表有什么区别？

两个维度，第一个是业务规则的变化对分表变化影响，分区表能否简单实现。第二个是数据量以及查询处理方面分区表能否满足

第一个很显然，如果一般的业务规则变化可能性不大，或者变化后，依然可以通过分区表简单处理就能实现，这一个维度是没什么问题

第二个方面，首先分区表并不能加速访问，而且一旦没有命中带上分区字段的唯一类索引，将会全表扫描，当然代码分表也一样，没法定位数据所在表也是多表全部扫描。
而当数据量上来了之后，特别是涉及到分布式管理，分区表就显得力不从心，即使能满足业务分离数据，分布式的特点也使得分区表用不上

分区表这个特性，主要是用于做数据的划分管理，不能优化性能。所以综上所述，当对于一些是适用于数据管理的业务，而且量没有大到要扩展存储的情况下，譬如一些日志表，七天或者一个月后就删除那种，就可以用分区表做。涉及到较为复杂或者数据较大的业务，适合业务代码分表，而且更加灵活

count(distinct) 和 group by 和 partition by(窗口函数) 区别

count(distinct) 对比 group by

count(distinct) 吃内存，查询快；group by 空间复杂度小，在时间复杂度允许的情况下，可以发挥他的空间复杂度优势

group by 对比 partition by

group by 分组汇总后改变了表的行数，一行只有一个类别。而 partiition by 和 rank 函数不会减少原表中的行数

数据湖是什么

关于什么是云原生架构，众说纷纭。寻找到比较好的答案是以下三点特征：

存储和计算分离，计算能力和存储能力均可独立扩展；

多模态计算引擎支持，SQL、批处理、流式计算、机器学习等；

提供 serverless 态服务，确保足够的弹性以及支持按需付费。

大屏实时展示数据方案

对于这一类实时数据场景来说，一般做法都比较简单

数通过 fink 计算或者聚合之后将结果写入 myslq/es/hbase/druid/kudu 等，然后提供查询和分析，一般就是 kafka+flink 的架构

数据技术生态圈分类

• 存储层

• HDFS
• HIVE
• HBASE
• MYSQL
• TIDB
• Redis/KV

• ...

• 计算层

• HadoopMR
• Spark
• Storm
• Flink
• Kylin
• Druid

• ...

• 传输层

• Flume
• Sqoop
• Kafka
• ...

视图

相当于在统计逻辑和实际库表之间提供了一种折中的方案。完成这个功能，逻辑上是必须有这么几道工序的，但又不想在每一道工序里都落地一张实际的数据表，显得繁琐而臃肿，那么就用视图把这些中间的工序用视图的形式去实现和替代。

递归

以前写递归的 sql 记得是用的 find_in_set 函数，这边最近又看到一种使用 with as 语法的办法看起来也更简单，看情况选择即可

使用 mysql 递归 cte 完成。

demo 1

with RECURSIVE T as
(
select '1' as id,'10' as parent_id union all
select '2', '20' union all
select '3', '30' union all
select '20', '200' union all
select '10', '100' union all
select '100', '1000' union all
select '200', '2000' union all
select '1000', '10000'
)
, A as (
select id, parent_id from T where id = '1'
union all
select T.id,T.parent_id from T
inner join A on T.id=A.parent_id
)

select * from A;
-- 结果
-- id parent_id
-- 1 10
-- 10 100
-- 100 1000

demo 2: 递归一个连续时间表

--递归一个连续时间表
with t as
(
    select
        date_add( to_date('2022-04-14'), i) as timeline -- 基础日期，起始时间
    from
        (select 80 as days) t
    lateral view
    posexplode(split(repeat(',', days), ',')) pe as i, x
)

诊断

 -- 查看成本
show status like 'last_query_cost'

优化

禁止负向条件查询

禁止使用负向查询 not、!=、<>、!<、!>、not in、not like 等，会导致全表扫描。

这条规定想满足其实很难，有些业务必不可免需要用到，那么可以考虑如果数据量大的情况使用以下用法：

select oid from order where uid = 1 and status != 1;

这条 sql 只要 uid 有索引，就可以先走索引缩小数据范围，此时再接上一个负向查询也没什么性能影响了。

拆分大的 insert/delete

子查询

往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。

分页优化

-- demo1
select id, content from news order by title limit 50,5
--> 优化后
select news.id, news.content
from news inner join (
    select id from news order by title limit 50,5
) as tmp using(id)

-- demo2
select id from t limit 10000, 10
--> 优化后
select id from t where id > 10000 limit 10

group by/order by 优化

确保任何 group by 和 order by 的列只涉及到一个表中的列，这样 mysql 才可以用索引去优化。

join 优化

当表 a 和表 b 都用列 c 列来关联时候，如果优化器关联的顺序是 a b，那么只需要再 b 表 c 列添加索引即可；

具体原因可以参考优化器优化 sql 后得执行逻辑，反推就可以得到以上结果。

谓词下推

谓词下推将查询语句中的过滤表达式计算尽可能下推到距离数据源最近的地方，以尽早完成数据的过滤，进而显著地减少数据传输或计算的开销。

谓词下推案例

-- 谓词下推到存储层
-- demo1
select * from t where a < 1;
-- demo2
select * from t where a < substring('123', 1, 1);

-- 谓词下推到 join 下方
-- demo3
select * from t join s on t.a = s.a where t.a < 1;
-- 在该查询中，将谓词 t.a < 1 下推到 join 前进行过滤，可以减少 join 时的计算开销
-- 这条 sql 执行的是内连接，且 on 条件是 t.a = s.a，可以由 t.a < 1 推导出谓词 s.a < 1，并将其下推至 join 运算前对 s 表进行过滤，可以进一步减少 join 时的计算开销
-- 得到
select * from t join s on t.a = s.a and t.a < 1 and s.a < 1

谓词下推失效案例

-- demo 1
-- 不对函数进行支持则无法下推
select * from t where substring('123', a, 1) = '1';
-- demo 2
-- 外连接中内表上的谓词不能下推
-- 该谓词没有被下推到 join 前进行计算，这是因为外连接在不满足 on 条件时会对内表填充 NULL，而在该查询中 s.a is null 用来对 join 后的结果进行过滤，如果将其下推到 join 前在内表上进行过滤，则下推前后不等价，因此不可进行下推
select * from t left join s on t.a = s.a where s.a is null;

数仓项目思考

• 数据指标多样，每次开发新表新的数据项时，要注意和旧的任务数据口径一致；

  比如旧的数据表运算得到了每月活跃用户数目，新的表需要每月各种使用频度的用户数目(低，中，高频)，那么他们势必要有一个 总数 = 低频 + 中频 + 高频 数目这样的关系，可以通过以前的运算总数逻辑再次分组计算，保证口径一致，结果一致，换句话说，即便算错，都要错的一模一样；

• 数仓也需要合适的索引。

sql\spark sql\其他场景 优化

列表优化

列表中涉及的业务信息表，用户信息表全部都是大表，列表性能很差，短期内想分表需要改的业务太多，急需提高整体合同列表的性能。

采用订阅 binlog 方案，订阅表部分字段[满足查询条件的字段]，将结果输入新表，极大减少数据量，业务重要接口使用新表查询。

继续优化方案: 业务数据数据存入 es。

count 内增加条件

数仓进行各种复杂指标查询，往往需要分组后对各种指标进行条件匹配在进行 count，常用以下两种方式：

-- 常用以下两种
-- 1
count(distinct
case when
    condition
then
 result1
else
    result2
end
)

-- 2
count(condition or null)

日/周/月 任务合并

使用一个 spark sql 定时任务同时写多个相同类型的 sql，减少任务量，也可以把同类型任务归类。

主要包含三部分 sql 即可：

• 日子任务
• 周子任务
• 月子任务

如果不是每周一，则 sql 跳过周任务，如果不是每月一号，则 sql 跳过月任务。

select if(date_format('${yyyy-mm-dd}' ,"u") between '1' and '5','平日','周末')

周任务跳过:

月任务跳过:

累计表任务优化

运营往往会提出汇总表需求

类似如下表头：

可以看到该表是个汇总数据，是很常见的数仓需求。主要得难点字段如图为 累计数目，难点在于 需要将当天数目和之前数目合并。

目前数仓常用的方法是，任务每次执行出当天的累计数目，也就是当天的数据。

该方法存在的问题：

如果需要回溯历史数据，那么需要补充执行历史任务，一旦数目过多，对任务提交执行机和计算服务都是很大挑战。

比如最近遇到的如下需求：

将某累计表数据回溯至 2020 年

从当前时间回溯到 2020 年，超过 500 个任务实例，当前的服务器配置下，每次执行的实例数超过某个经验值就可能出现各种问题，提交任务阻塞，超时失败，cpu100%，oom 等等，直接就会引起各种告警，lead 们都会额外关注，对研发简直是一种消磨，重复的噩梦每天都惊心动魄。

最初的方案，每天的数据需要一个任务实例，只能跑当天数据

-- 汇总数据表-日数据
insert overwrite table test.test partition(timeline='${yyyy-mm-dd-1d}')
select ...,
    count(distinct
           case when left(reg.create_time , 10) = '${yyyy-mm-dd-1d}'
           then reg.id
           else null
           end
           as create_cnt,
    count(distinct reg.id) as create_cnt_al
from
       ...

得到的数据大概是这样的：

补数据的话其实就是将这个任务实例里面的 yyyy-mm-dd-1d 传入不同的值得到每天的数据，一个任务补一年就要 365 个实例

使用窗口函数简单优化可以得到如下 sql(缺点是不存在天数的数据则不展示，增量时间不连续，预期可能希望是 0)

实现方式不唯一，这里仅展示一种，可以推敲更简便的方案省去一些查询

-- 思路如下
-- 1. 首先使用 timeline 分组, 使用窗口函数, 计算每个时间分组内的一个增量数目
-- 2. 获取每个时间分组的数目的最大值, 也就是该时间段产生的一个数目
-- 3. 使用窗口函数, 不指定分组(则默认就是整个表作为一个分组), 此时使用 sum 得到累加值
select
    timeline,
    day_cnt,
    sum(day_cnt) over (order by timeline) as day_cnt_add
from (
    select
        timeline,
        max(create_cnt) as day_cnt
    from (
        select
            timeline,
            count(id) over(partition by timeline order by timeline rows between unbounded preceding and current row) as create_cnt
        from
            default.test
    ) as group_test
    group by
        timeline
    order by
        timeline
) as group_test

结果

一次性得到了从最初时间到现在每天的汇总和累计值

ps: 可以看到上面的第一列缺少部分天数，那几天没有数据产生，其实也应该产出一条数据，这个再后面的第三个方案处理了，这里不重复。

但是，如果用了下面的方式太过于复杂，逻辑混乱，考虑到我们这边用的是窗口函数，我们可以用下面方案试试。

• 使用递归创建一个连续无限时间戳表
• 和上面的增量表关联做左连接，如果数据行为空的时候，使用错位窗口 leg/lead 函数补齐

不使用窗口函数的方案（复杂一点不推荐，使用老版本 mysql 不支持窗口时候可以用）

下面为流程和注册的累计表数据，但是还有个存在的问题就是累计表不一定是连续的 如果某天没有数据，则这一天累计数据为空，解决办法就是把下面多个累计表按照时间 full join，使用分组函数 max() sum() 等查询出每天的累计数据，不在此赘述。

with tmp2 as (
    select
    a.first_time,
    count(a.org_id) as num
    from
    (
        select
        test.org_id as org_id,
        min(left(create_date, 10)) as first_time
        from
        default.test as test
        where
        org_id != ''
        group by
        test.org_id
    ) as a
    group by a.first_time
)
select
 a.first_time,
 sum(b.num) as all_num
from
 tmp2 a,
 tmp2 b
where
 b.first_time <= a.first_time
group by a.first_time
order by a.first_time

--
with tmp as (
        select
        A.give_day,
        count(A.org_id) as num
        from(
                        select
                        a.*,
                        left(created_on, 10) as give_day
                        from
                        test_org as a
                ) A
        where
                A.give_day >= '2000-01-01'
        group by A.give_day
)
select
 a.give_day,
        -- a.num,
        sum(case when b.give_day = a.give_day then b.num else 0 end),
 sum(b.num) as all_num
from
 tmp a,
 tmp b
where
 b.give_day <= a.give_day
group by a.give_day
order by a.give_day

一张表需要多库数据如何临时导出

数仓建设时候除了一些需要每日/周/月展示的需求可以用一些定时离线任务也搞以外，还会有一些临时查询的需求，快速出数据，其中可能包含一些没有拉取到数仓得数据信息。

此时基本上会从数仓查询出部分数据，剩余数据部分会去 mysql join 的方式去连接。

比如 select ... from users where id in (...)

问题： 这个时候拉取到的 ... 字段可能和数仓导出的 id 列无法对齐

可以用如下方式拉取

select ... from users where id in (...) order by field(id, 'id1', 'id2', ...)

得到的两部分数据直接拼接起来就 ok。

引用

• mysql 递归 cte
• TiDB 文档
• 极客-sql 实例练习
• understanding-window-functions