作者：dcguo

使用 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:

如果从辅助索引取，根据一些过滤条件得到 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

with as

group by/with rollup

group by

主要是用来做数据聚合 需要选择字段作为聚合维度后，然后通过聚合函数得到汇总值的过程。 count，sum，avg，... max/min，std，variance，... rank，first/last_value，row_number，...

demo:

优化:

1. 分组是一个相对耗时的操作，我们可以先通过 where 缩小数据的范围之后，再分组；

2. 也可以将分组拆分，如果是大表多维度分组，可以使用 with as 语法先计算一部分得到临时表然后再利用临时表进行计算，sql 也可以简化 。

with rollup

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

name sum(score) dc 100 xc 100 NULL 200

tag:

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

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

union/union all/intersect/except

用法基本类似，只举例部分 union 并集 intersect 交集 except 差集

limit

limit 分页查询使用

1. 使用

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 好

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

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

• join/inner join 内连接 略

• 不等值连接

• left join/right join 外连接

• • 左外连接

    略

  • 右外连接

    略

• full join 全连接

• • full join

    略

• left semi join 左半连接

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

• 隐式连接

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

having

1. 使用聚合函数进行计算

2. 使用 having 子句筛选分组

where/on

join 时候 where/on 不可以混用 inner join 中 where 可以代替 on 但是 on 不能代替 where on 是 using on 的简便写法

explain(mysql)字段名 含义 id 查询或者关联查询得顺序如果没有子查询且只有一个查询，则为一个常数 1，表示第一步如果有子查询则子查询为 1，父查询为 2id 相同查询顺序从上到下，否则 id 越大，优先级越高 select_type 显示查询种类是简单还是复杂 select 查询中不包含子查询或者 union 查询中若包含任何复杂的子查询，最外层查询则被标记为 PRIMARY union 查询中第二个或者后面的 select 子查询中第一个 select union 的结果 查询中第二个或者后面的 select，取决于外面的查询 子查询中的第一个 select，取决于外面的查询 派生表的 select, from 子句的子查询 一个子查询的结果不能被缓存，必须重新评估外连接的第一行 table 显示这一行的数据是关于哪张表的 type 访问类型，all, index, rane, ref, eq_red, const, system, null 性能从差到好 全表遍历 索引树遍历 检索给定范围的行，使用索引选择行 表示表的连接匹配条件，即哪些列或者常量被用于查找索引列上的值 类似于 ref，只是使用的索引是主键或者唯一索引 查询优化为了常量，比如主键再 where 列表里面，system 是 const 特例，表只有一行则是 system 优化分解语句后，执行时甚至不需要访问表或者所以 extra 解决查询的详细信息 表示 mysql 会对结果使用外部排序，不是按照索引从表内读行，无法利用索引 表示覆盖索引得到结果，避免回表 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回，表示对表的全部请求都是索引的部分 表示需要临时表来存储结果集，常见于排序和分组查询 获取链接条件时候没使用索引，并且需要连接缓冲区存储中间结果 强调了 where 语句会导致没有符合条件的行 意味着仅通过使用索引，优化器可能从聚合函数结果中返回一行keykey_lenrefrowspossible_keys key 列显示 mysql 实际决定使用的键key_len 表示索引中使用的字节数，可以计算查询使用的索引的长度，越短越好ref 表示连接匹配条件，那些列或者常量被用于查找索引列上的值rows 表示 mysql 根据表统计信息以及索引选用情况，估算查询需要读取的行数possible_keys 表示可以使用哪个索引查到记录，查询涉及的字段若存在索引则会被列出，但不一定使用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

关于删数据 ？

在删除内部表的时候，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))

字符串

统计函数

判断语句

列转行

• concat_ws

• collect_list/collect_set

• group_concat

• lateral view explode

udf 函数

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

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

窗口函数

ps: 以前一直不知道为啥叫窗口函数，总联想到滑动窗口，但是其实不是这样 partition by 的结果是分组的结果，每个组都是不同的范围，窗口的意思就是范围 它可以做什么？ 有些需求可以使用窗口函数完成，如下： 排名问题，根据科目进行分组 top n 问题，找出每个科目前三的同学进行奖励 增量表问题 某字段某段时间移动平均 某种行为前百分之多少的数据 此时可以引入窗口函数 该函数也叫 olap 函数（online anallytical processing，联机分析处理），可以对数据库数据进行实时分析处理 它的执行顺序 where 之后

demo

就下面图 2 这个查询不用窗口该咋写？各种子查询想想都难受，so 使用窗口。 我们做到了，在一个单个 sql 中计算列的综合，以及每一列占总数得比例，以及当前列，这就是窗口函数得一个突破。

基本语法

哪些函数可以使用窗口函数 专用函数 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 条数据

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

平均分组 ntile

它将有序分区的行分配到指定数量的大致相等的组或桶中 可用场景 求成绩再前百分之 20 的分数

demo:

错位 lag/lead

定义 lag 提供对当前行之前的给定物理偏移的行的访问 lead 提供对当前行之后的给定物理偏移量的行的访问 通过这两个函数可以在一次查询中取出同一字段的前 n 行的数据 lag 和后 n 行的数据 lead 作为独立的列, 更方便地进行进行数据过滤 可用场景 在比较同一个相邻的记录集内两条相邻记录 计算今日电表消耗(需计算今日电表度数和昨日差值)

demo:

分组取最大最小 first_value/last_value

first_value() : 取分组内排序后，截止到当前行，第一个值 last_value():取分组内排序后，截止到当前行，最后一个值 简单地说你可以得到一列值中某字段上下其他行得字段值，和 lag/lead 函数有些近似

demo:

分析函数 cume_dist，perent_rank

这两个函数使用较少，不支持 windows 字句 cume_dist 小于等于当前值的行数/分组内总行数 percent_rank 计算给定行的百分比排名

demo1:

demo2:

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

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

• 同时可以看出每一行数据，对整体统计数据的影响

场景

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

demo

• 计算三天内平均金额

• 计算总和

疑问 给 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

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

诊断 优化 禁止负向条件查询

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

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

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

拆分大的 insert/delete 子查询

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

分页优化

group by/order by 优化

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

join 优化

当表 a 和表 b 都用列 c 列来关联时候，如果优化器关联的顺序是 a b，那么只需要再 b 表 c 列添加索引即可； 具体原因可以参考优化器优化 sql 后得执行逻辑，反推就可以得到以上结果。

谓词下推

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

谓词下推案例

谓词下推失效案例

数仓项目思考

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

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

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

sqlspark sql其他场景 优化 列表优化

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

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

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

count 内增加条件

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

日/周/月 任务合并

使用一个 spark sql 定时任务同时写多个相同类型的 sql，减少任务量，也可以把同类型任务归类。 主要包含三部分 sql 即可： 日子任务 周子任务 月子任务 如果不是每周一，则 sql 跳过周任务，如果不是每月一号，则 sql 跳过月任务。

周任务跳过:

月任务跳过:

最终得到得任务可以同时跑日周月表，到达时间就会进行对应的表生成，任务数降低，相同模块表聚合度增强，更易维护，这些表我统一命名为 d_7d_1m 。累计表任务优化

运营往往会提出汇总表需求 类似如下表头： 可以看到该表是个汇总数据，是很常见的数仓需求。主要得难点字段如图为 累计数目，难点在于 需要将当天数目和之前数目合并。 目前数仓常用的方法是，任务每次执行出当天的累计数目，也就是当天的数据。 该方法存在的问题： 如果需要回溯历史数据，那么需要补充执行历史任务，一旦数目过多，对任务提交执行机和计算服务都是很大挑战。 比如最近遇到的如下需求： 将某累计表数据回溯至 2020 年 从当前时间回溯到 2020 年，超过 500 个任务实例，当前的服务器配置下，每次执行的实例数超过某个经验值就可能出现各种问题，提交任务阻塞，超时失败，cpu100%，oom 等等，直接就会引起各种告警，lead 们都会额外关注，对研发简直是一种消磨，重复的噩梦每天都惊心动魄。

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

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

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

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

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

结果

timeline day_cnt day_cnt_add 2020-10-10 10 10 2020-10-11 1 11 ... ... ...

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

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

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

• 使用递归创建一个连续无限时间戳表

• 和上面的增量表关联做左连接，如果数据行为空的时候，使用错位窗口 leg/lead 函数补齐

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

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

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

数仓建设时候除了一些需要每日/周/月展示的需求可以用一些定时离线任务也搞以外，还会有一些临时查询的需求，快速出数据，其中可能包含一些没有拉取到数仓得数据信息。 此时基本上会从数仓查询出部分数据，剩余数据部分会去 mysql join 的方式去连接。 比如 select ... from users where id in (...) 问题： 这个时候拉取到的 ... 字段可能和数仓导出的 id 列无法对齐

可以用如下方式拉取

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

引用

• TiDB 文档

      以上就是本篇文章【数据仓库开发 SQL 使用技巧总结】的全部内容了，欢迎阅览 ！ 文章地址：http://zleialh.xhstdz.com/news/5647.html 
       栏目首页      相关文章      动态      同类文章      热门文章      网站地图      返回首页 物流园资讯移动站 http://zleialh.xhstdz.com/mobile/ , 查看更多