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

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

mysql 数据结构

常用 innodb 存储为 B+ 树

特点

• 多路平衡树，m 个子树中间节点就包含 m 个元素，一个中间节点是一个 page(磁盘页) 默认 16 kb；

• 子节点保存了全部得元素，父节点得元素是子节点的最大或者最小元素，而且依然是有序得；

• 节点元素有序，叶子节点双向有序，便于排序和范围查询。

优势

• 平衡查找树，logn 级别 crud；

• 单一节点比二叉树元素更多，查询 io 次数更少；

• 所有查询都要查询到叶子节点性能稳定；

• 所有节点形成逻辑有序链表，便于排序查询范围查询。

索引

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

1. 聚簇索引

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

1. 非聚簇索引

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

辅助索引可能没有我们需要的 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。

1. 复合索引

其实还是一个 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)

name	sum(score)
dc	100
xc	100
NULL	200

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 值。

  <