sql优化核心思想 pdf_数据库运维:百亿级数据处理优化?

百亿级数据处理优化

最近在做大数据处理时,遇到两个大表 join 导致数据处理太慢(甚至算不出来)的问题。我们的数仓基于阿里的 ODPS,它与 Hive 类似,所以这篇文章也适用于使用 Hive 优化。处理优化问题,一般是先指定一些常用的优化参数,但是当设置参数仍然不奏效的时候,我们就要结合具体的业务,在 SQL 上做优化了。为了不增加大家的阅读负担,我会简化这篇文章的业务描述。

问题

这是一个离线数据处理的问题。在这个业务中有两张表,表结构及说明如下:

user_article_tb 表:
7c982987e2a79e30464e9f10673b9142.png

字段解释:

uid: 用户标识,itemid:文章id,dur: 阅读文章时长,如果大于 0 代表阅读了文章,等于 0 代表没有点击文章

dt:天分区,每天 55 亿条记录

user_profile_tb 表:
59434430a6ed3c560f9837cfffb7795a.png
字段解释: uid:用户标识,gender:性别,F 代表女,M 代表男,age:年龄,city:城市 dt:天分区字段,这是一张总表,每天存储全量用户画像属性,最新数据十亿级别

需求是这样的:计算 7 天中,女性用户在每篇文章上的 ctr (最终会按照降序进行截断)。直接写 SQL 很容易,如下:

select   itemid  , count(if(dur > 0, 1, null)) / count(1) ctrfrom  (   select uid, itemid, dur   from user_article_tb   where dt>='20190701' and dt<='20190707'  ) data_tb  join  (    select *    from user_profile_tb    where dt='20190707' --最新的日期     and gender='F'  ) profile_tb  on     data_tb.uid = profile_tb.uidgroup by   itemidorder by ctr desclimit 50000;

那么问题来了:

  • 对于 user_article_tb 来说,7天的数据量将近 400 亿条记录,还需要 join 一张十亿级别的画像表。这个数据量基本上就跑不出来了
  • 像这种探索性质的需求,经常会变化。假设需求变成计算男性或者计算一二线城市用户的呢?可能又需要重跑整个数据,既要付出时间成本又要付出高昂的资源成本

解决

我们一一解决上面提到的两个问题。先考虑第一个,既然 join 的两张表太大了,我们能不能尝试把表变小呢。答案是肯定的,对于画像表来说显然是没办法缩小了,但是对于 user_artitle_tb 是可以的。我们可以按照表的分区字段 dt 用每天的数据分别 join 画像表,将结果再按天存储在一张临时表里面。这样每天就是十亿级别的数据 join,基本可以解决问题。但是每天的数据仍有多余的 join,比如:某天的数据中 uid = 00001 的用户,一天看了 1000 篇文章,那这个用户就需要多 join 999 次。在我们的业务中一个用户一天看文章的数量 > 10 是很普遍的,因此多余 join 的情况还是比较严重的。

针对上面提到的多余 join 的情况,最彻底的解决方法就是把 user_article_tb 表变成 uid 粒度的,跟画像表一样。我们将 7 天的数据转换成 uid 粒度的 SQL 如下:

insert overwrite table user_article_uid_tb as select uid, wm_concat(':', concat_ws(',', itemid, dur)) item_infosfrom   (    select *    from user_article_tb    where dt >= '20190701' and dt <= '20190707'   ) tmpgroup by uid

从上面 SQL 可以看到,我们首先将 7 天的数据按照 uid 做 group by 操作,构造 item_infos。因为我们的是计算 ctr,所以我们可以按照 uid 粒度对表做转换,并且 item_infos 字段包含什么是要根据业务需求做选择。每天不到 1 亿 uid,7天汇总的 uid 不到 10 亿,两张 uid 粒度的表进行 join 就会快很多。

至此,多余 join 的问题得到了解决, 再来看看第二个问题。这个问题其实就是我们维度建模理论中所说的宽表,为了避免统计不同维度时频繁 join 维表,我们可以在上游数据将常用的维度提前关联起来,形成一张大宽表。下游数据可以直接用从而减少 join。以我们的问题为例,SQL 如下:

create table user_profile_article_uid_tb asselect  data_tb.uid , item_infos , gender , age , city  -- 其他维度字段from  (    select uid, item_infos   from user_article_uid_tb   ) data_tb  join  (   select uid, gender, age, city    from user_profile_tb    where dt='20190707' --最新的日期  ) profile_tb  on     data_tb.uid = profile_tb.uid;

这样,上面提到的两个问题就都解决了。最终我们的需求:女性用户每篇文章的 ctr 计算如下:

select  itemid , count(if(dur > 0, 1, null)) / count(1) ctrfrom   (    select       split(item_info, ',')[0] itemid     , split(item_info, ',')[1] dur    from user_profile_article_uid_tb     lateral view explode(split(item_infos, ':')) item_tb as item_info  ) tmpgroup itemidorder by ctr desclimit 50000

参数优化

mapreduce.map.memory.mbmapreduce.reduce.memory.mbmapred.reduce.tasks

这些参数设置是比较通用的选项, 当这些选项不能够达到最优的效果时,需要从业务上进行优化。

小结

这篇文章主要介绍了在 ODPS 或 Hive 上,百亿级数据规模的 join 优化。核心思想就是减少 join 的数据量,同时优化没有放之四海而皆准的方法,一定是结合业务进行的。

以上仅代表作者观点;

作者:渡码

原文:https://www.cnblogs.com/duma/p/11186279.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是sql性能问题。本书是作者十年磨一剑的成果之一,深入分析与解剖oracle sql优化与调优技术,主要内容包括: 第一篇“执行计划”详细介绍各种执行计划的含义与操作,为后面的深入分析打下基础。重点讲解执行计划在sql语句执行的生命周期中所处的位置和作用,sql引擎如何生成执行计划以及如何获取sql语句的执行计划,如何从各种数据源显示和查看已经生成的执行计划。 第二篇“sql优化技术”深入分析oracle的sql优化技术,包括逻辑优化技术和物理优化技术。用大量示例详尽分析oracle 中现有的各种查询转换技术,先分析oracle如何收集、统计系统和对象的数据,然后推导各种代价估算公式,给出各种情形下的代价计算演示。 第三篇“sql调优技术”深入剖析oracle提供的各项调优技术。先对语句实际运行的性能统计数据进行了深度分析,介绍各项统计数据是由什么操作导致的以及如何统计。然后介绍如何对sql语句进行优化以获得稳定、高效的性能。最后,依据对sql优化及调优技术的分析,介绍如何快速优化sql的思路。 《oracle 高性能sql引擎剖析:sql优化与调优机制详解》内容丰富且深入,破解了oracle技术的很多秘密,适合oracle数据库管理员、应用开发人员参考。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值