大数据计算,如何优化SQL?

简介: 很多大数据计算都是用SQL实现的,跑得慢时就要去优化SQL,但常常碰到让人干瞪眼的情况。

>📢📢📢📣📣📣

>哈喽!大家好,我是【IT邦德】,江湖人称jeames007,10年DBA工作经验

>一位上进心十足的【大数据领域博主】!😜😜😜

>中国DBA联盟(ACDU)成员,目前从事DBA及程序编程

>擅长主流数据Oracle、MySQL、PG 运维开发,备份恢复,安装迁移,性能优化、故障应急处理等。

>✨  如果有对【数据库】感兴趣的【小可爱】,欢迎关注【IT邦德】💞💞💞

>❤️❤️❤️感谢各位大可爱小可爱!❤️❤️❤️



# 前言

<font color=#999AAA >

很多大数据计算都是用SQL实现的,跑得慢时就要去优化SQL,但常常碰到让人干瞪眼的情况。

<hr style=" border:solid; width:100px; height:1px;" color=#000000 size=1">




很多大数据计算都是用SQL实现的,跑得慢时就要去优化SQL,但常常碰到让人干瞪眼的情况。


比如,存储过程中有三条大概形如这样的语句执行得很慢:


```sql

select a,b,sum(x) from T group by a,b where …;  

select c,d,max(y) from T group by c,d where …;  

select a,c,avg(y),min(z) from T group by a,c where …;

```


这里的T是个有数亿行的巨大表,要分别按三种方式分组,分组的结果集都不大。


分组运算要遍历数据表,这三句SQL就要把这个大表遍历三次,对数亿行数据遍历一次的时间就不短,何况三遍。


这种分组运算中,相对于遍历硬盘的时间,CPU计算时间几乎可以忽略。如果可以在一次遍历中把多种分组汇总都计算出来,虽然CPU计算量并没有变少,但能大幅减少硬盘读取数据量,就能成倍提速了。


如果SQL支持类似这样的语法:


```sql

from T --数据来自T表

select a,b,sum(x) group by a,b where …   --遍历中的第一种分组    

select c,d,max(y) group by c,d where …   --遍历中的第二种分组    

select a,c,avg(y),min(z) group by a,c where …; --遍历中的第三种分组

```


能一次返回多个结果集,那就可以大幅提高性能了。


可惜, SQL没有这种语法,写不出这样的语句,只能用个变通的办法,就是用group a,b,c,d的写法先算出更细致的分组结果集,但要先存成一个临时表,才能进一步用SQL计算出目标结果。SQL大致如下:


```sql

create table T\_temp as select a,b,c,d,  

    sum(case when … then x else 0 end) sumx,  

    max(case when … then y else null end) maxy,  

    sum(case when … then y else 0 end) sumy,  

    count(case when … then 1 else null end) county,  

    min(case when … then z else null end) minz

    group by a,b,c,d;

select a,b,sum(sumx) from T\_temp group by a,b where …;  

select c,d,max(maxy) from T\_temp group by c,d where …;  

select a,c,sum(sumy)/sum(county),min(minz) from T\_temp group by a,c where …;

```


这样只要遍历一次了,但要把不同的WHERE条件转到前面的case when里,代码复杂很多,也会加大计算量。而且,计算临时表时分组字段的个数变得很多,结果集就有可能很大,最后还对这个临时表做多次遍历,计算性能也快不了。大结果集分组计算还要硬盘缓存,本身性能也很差。


还可以用存储过程的数据库游标把数据一条一条fetch出来计算,但这要全自己实现一遍WHERE和GROUP的动作了,写起来太繁琐不说,数据库游标遍历数据的性能只会更差!


只能干瞪眼!




TopN运算同样会遇到这种无奈。举个例子,用Oracle的SQL写top5大致是这样的:


```sql

select \* from (select x from T order by x desc) where rownum<=5

```


表T有10亿条数据,从SQL语句来看,是将全部数据大排序后取出前5名,剩下的排序结果就没用了!大排序成本很高,数据量很大内存装不下,会出现多次硬盘数据倒换,计算性能会非常差!


避免大排序并不难,在内存中保持一个5条记录的小集合,遍历数据时,将已经计算过的数据前5名保存在这个小集合中,取到的新数据如果比当前的第5名大,则插入进去并丢掉现在的第5名,如果比当前的第5名要小,则不做动作。这样做,只要对10亿条数据遍历一次即可,而且内存占用很小,运算性能会大幅提升。


这种算法本质上是把TopN也看作与求和、计数一样的聚合运算了,只不过返回的是集合而不是单值。SQL要是能写成这样,就能避免大排序了:


```sql

select top(x,5) from T

```


然而非常遗憾,SQL没有显式的集合数据类型,聚合函数只能返回单值,写不出这种语句!




不过好在全集的TopN比较简单,虽然SQL写成那样,数据库却通常会在工程上做优化,采用上述方法而避免大排序。所以Oracle算那条SQL并不慢。


但是,如果TopN的情况复杂了,用到子查询中或者和JOIN混到一起的时候,优化引擎通常就不管用了。比如要在分组后计算每组的TopN,用SQL写出来都有点困难。Oracle的SQL写出来是这样:


```sql

select \* from (select y,x,row\_number() over (partition by y order by x desc) rn from T) where rn<=5

```


这时候,数据库的优化引擎就晕了,不会再采用上面说的把TopN理解成聚合运算的办法。只能去做排序了,结果运算速度陡降!


假如SQL的分组TopN能这样写:


```sql

select y,top(x,5) from T group by y

```


把top看成和sum一样的聚合函数,这不仅更易读,而且也很容易高速运算。


可惜,不行。


还是干瞪眼!




关联计算也是很常见的情况。以订单和多个表关联后做过滤计算为例,SQL大体是这个样子:


```sql

select o.oid,o.orderdate,o.amount

from orders o

   left join city ci on o.cityid = ci.cityid

   left join shipper sh on o.shid=sh.shid

   left join employee e on o.eid=e.eid

   left join supplier su on o.suid=su.suid

where ci.state='New York'

   and e.title='manager'

   and ...

```


订单表有几千万数据,城市、运货商、雇员、供应商等表数据量都不大。过滤条件字段可能会来自于这些表,而且是前端传参数到后台的,会动态变化。


SQL一般采用HASH JOIN算法实现这些关联,要计算 HASH 值并做比较。每次只能解析一个JOIN,有N个JOIN要执行N遍动作,每次关联后都需要保持中间结果供下一轮使用,计算过程复杂,数据也会被遍历多次,计算性能不好。




通常,这些关联的代码表都很小,可以先读入内存。如果将订单表中的各个关联字段预先做序号化处理,比如将雇员编号字段值转换为对应雇员表记录的序号。那么计算时,就可以用雇员编号字段值(也就是雇员表序号),直接取内存中雇员表对应位置的记录,性能比HASH JOIN快很多,而且只需将订单表遍历一次即可,速度提升会非常明显!


也就是能把SQL写成下面的样子:


```sql

select o.oid,o.orderdate,o.amount

from orders o

   left join city c on o.cid = c.# --订单表的城市编号通过序号#关联城市表

   left join shipper sh on o.shid=sh.# --订单表运货商号通过序号#关联运货商表

   left join employee e on o.eid=e.# --订单表的雇员编号通过序号#关联雇员表

   left join supplier su on o.suid=su.#--订单表供应商号通过序号#关联供应商表

where ci.state='New York'

   and e.title='manager'

   and ...

```


可惜的是,SQL 使用了无序集合概念,即使这些编号已经序号化了,数据库也无法利用这个特点,不能在对应的关联表这些无序集合上使用序号快速定位的机制,只能使用索引查找,而且数据库并不知道编号被序号化了,仍然会去计算 HASH 值和比对,性能还是很差!


有好办法也实施不了,只能再次干瞪眼!




还有高并发帐户查询,这个运算倒是很简单:


```sql

select id,amt,tdate,… from T

where id='10100'

   and tdate>= to\_date('2021-01-10','yyyy-MM-dd')

   and tdate<to_date('2021-01-25','yyyy-mm-dd')

   and="" …="" <p="">

```


在T表的几亿条历史数据中,快速找到某个帐户的几条到几千条明细,SQL写出来并不复杂,难点是大并发时响应速度要达到秒级甚至更快。为了提高查询响应速度,一般都会对 T 表的 id 字段建索引:


```sql

create index index_T_1 on T(id)

```


在数据库中,用索引查找单个帐户的速度很快,但并发很多时就会明显变慢。原因还是上面提到的SQL无序理论基础,总数据量很大,无法全读入内存,而数据库不能保证同一帐户的数据在物理上是连续存放的。硬盘有最小读取单位,在读不连续数据时,会取出很多无关内容,查询就会变慢。高并发访问的每个查询都慢一点,总体性能就会很差了。在非常重视体验的当下,谁敢让用户等待十秒以上?!


容易想到的办法是,把几亿数据预先按照帐户排序,保证同一帐户的数据连续存储,查询时从硬盘上读出的数据块几乎都是目标值,性能就会得到大幅提升。


但是,采用SQL体系的关系数据库并没有这个意识,不会强制保证数据存储的物理次序!这个问题不是SQL语法造成的,但也和SQL的理论基础相关,在关系数据库中还是没法实现这些算法。


那咋办?只能干瞪眼吗?


不能再用SQL和关系数据库了,要使用别的计算引擎。


开源的集算器SPL基于创新的理论基础,支持更多的数据类型和运算,能够描述上述场景中的新算法。用简单便捷的SPL写代码,在短时间内能大幅提高计算性能!


上面这些问题用SPL写出来的代码样例如下:


- 一次遍历计算多种分组


|      | A                                          | B                                        |

| ---- | ------------------------------------------ | ---------------------------------------- |

| 1    | =file("T.ctx").open().cursor(a,b,c,d,x,y,z |                                          |

| 2    | cursor A1                                  | =A2.select(…).groups(a,b;sum(x))         |

| 3    |                                            | //定义遍历中的第一种过滤、分组           |

| 4    | cursor                                     | =A4.select(…).groups(c,d;max(y))         |

| 5    |                                            | //定义遍历中的第二种过滤、分组           |

| 6    | cursor                                     | =A6.select(…).groupx(a,c;avg(y),min(z))  |

| 7    |                                            | //定义遍历中的第三种过滤、分组           |

| 8    | …                                          | //定义结束,开始计算三种方式的过滤、分组 |


- 用聚合的方式计算Top5


全集Top5(多线程并行计算)



|      | A                                                            |

| ---- | ------------------------------------------------------------ |

| 1    | =file("T.ctx").open()                                        |

| 2    | =A1.cursor@m(x).total(top(-5,x),top(5,x))                    |

| 3    | //top(-5,x) 计算出 x 最大的前 5 名,top(5,x) 是 x 最小的前 5 名。 |


分组Top5(多线程并行计算)



|      | A                                              |

| ---- | ---------------------------------------------- |

| 1    | =file("T.ctx").open()                          |

| 2    | =A1.cursor@m(x,y).groups(y;top(-5,x),top(5,x)) |


- 用序号做关联的SPL代码:


系统初始化


|      | A                                                            |

| ---- | ------------------------------------------------------------ |

| 1    | >env(city,file("city.btx").import@b()),env(employee,file("employee.btx").import@b()),... |

| 2    | //系统初始化时,几个小表读入内存                             |


查询


|      | A                                                            |

| ---- | ------------------------------------------------------------ |

| 1    | =file("orders.ctx").open().cursor(cid,eid,…).switch(cid,city:#;eid,employee:#;…) |

| 2    | =A1.select(cid.state=="New York" && eid.title=="manager"…)   |

| 3    | //先序号关联,再引用关联表字段写过滤条件                     |


- 高并发帐户查询的SPL代码:


数据预处理,有序存储


|      | A                                                     | B              |

| ---- | ----------------------------------------------------- | -------------- |

| 1    | =file("T-original.ctx").open().cursor(id,tdate,amt,…) |                |

| 2    | =A1.sortx(id)                                         | =file("T.ctx") |

| 3    | =B2.create@r(#id,tdate,amt,…).append@i(A2)            |                |

| 4    | =B2.open().index(index_id;id)                         |                |

| 5    | //将原数据排序后,另存为新表,并为帐号建立索引        |                |


帐户查询


|      | A                                                            |

| ---- | ------------------------------------------------------------ |

| 1    | =T.icursor(;id==10100 && tdate>=date("2021-01-10") && tdate<date("2021-01-25") && …,index_id).fetch() |

| 2    | //查询代码非常简单                                           |


除了这些简单例子,SPL还能实现更多高性能算法,比如有序归并实现订单和明细之间的关联、预关联技术实现多维分析中的多层维表关联、位存储技术实现上千个标签统计、布尔集合技术实现多个枚举值过滤条件的查询提速、时序分组技术实现复杂的漏斗分析等等。


正在为SQL性能优化头疼的小伙伴们,可以和我们一起探讨:


<a href="http://www.raqsoft.com.cn/wx/Query-run-batch-ad.html" rel="nofollow" target="_blank">http://www.raqsoft.com.cn/wx/Query-run-batch-ad.html</a>


## SPL资料


- [SPL官网](<http://c.raqsoft.com.cn/article/1595816810031>)  

- [SPL下载](<http://c.raqsoft.com.cn/article/1595816810031>)  

- [SPL源代码](<https://github.com/SPLWare/esProc>)  


![在这里插入图片描述](https://ucc.alicdn.com/images/user-upload-01/5148b617c04d4b4eac077a3578cc8f39.gif#pic_center)

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
目录
相关文章
|
13天前
|
SQL 存储 关系型数据库
【MySQL】SQL 优化
【MySQL】SQL 优化
37 0
|
13天前
|
SQL 分布式计算 DataWorks
MaxCompute产品使用合集之大数据计算MaxCompute即使用相同的SQL语句在DataWorks和Tunnel上执行,结果却不同,如何解决
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
4天前
|
SQL 监控 关系型数据库
【PolarDB开源】PolarDB SQL优化实践:提升查询效率与资源利用
【5月更文挑战第24天】PolarDB是高性能的云原生数据库,强调SQL查询优化以提升性能。本文分享了其SQL优化策略,包括查询分析、索引优化、查询重写、批量操作和并行查询,以及性能监控与调优方法。通过这些措施,可以减少响应时间、提高并发处理能力和降低成本。文中还提供了相关示例代码,展示如何分析查询和创建索引,帮助用户实现更高效的数据库管理。
31 1
|
5天前
|
存储 SQL JSON
一些MaxCompute日常优化案例分享
MaxCompute优化是一个多样而又重要的过程,优化过程需要能够深入理解ODPS的工作原理和内部机制,本文总结了以下几个日常优化案例,最终优化手段可能非常简单,但其中的分析过程较为重要,希望对大家有所启发。
21 0
|
8天前
|
SQL 分布式计算 Java
大数据软件基础(2)—— Java、SQL
大数据软件基础(2)—— Java、SQL
9 0
|
13天前
|
SQL 缓存 关系型数据库
一次sql改写优化子查询的案例
在生产环境中,一个MySQL RDS实例遭遇了高CPU使用率问题,原因是执行了一条复杂的UPDATE SQL语句,该语句涉及一个无法缓存的子查询(UNCACHEABLE SUBQUERY),导致子查询需要针对每一行数据重复执行,极大地影响了性能。SQL语句的目标是更新一行数据,但执行时间长达30秒。优化方法是将子查询转换为内连接形式,优化后的语句执行时间降低到毫秒级别,显著减少了CPU消耗。通过示例数据和执行计划对比,展示了优化前后的时间差异和执行效率的提升。
|
13天前
|
数据采集 供应链 安全
利用大数据优化业务流程:策略与实践
【5月更文挑战第11天】本文探讨了利用大数据优化业务流程的策略与实践,包括明确业务目标、构建大数据平台、数据采集整合、分析挖掘及流程优化。通过实例展示了电商和制造企业如何利用大数据改进库存管理和生产流程,提高效率与客户满意度。随着大数据技术进步,其在业务流程优化中的应用将更加广泛和深入,企业需积极采纳以适应市场和客户需求。
|
13天前
|
存储 SQL 关系型数据库
掌握高性能SQL的34个秘诀🚀多维度优化与全方位指南
掌握高性能SQL的34个秘诀🚀多维度优化与全方位指南
|
13天前
|
SQL 存储 关系型数据库
【MySQL系列笔记】SQL优化
SQL优化是通过调整数据库查询、索引、表结构和配置参数等方式,提高SQL查询性能和效率的过程。它旨在减少查询执行时间、减少系统资源消耗,从而提升数据库系统整体性能。优化方法包括索引优化、查询重写、表分区、适当选择和调整数据库引擎等。
240 3
|
13天前
|
SQL 分布式计算 DataWorks
DataWorks操作报错合集之在DataWorks中使用ODPS SQL时遇到"该文件对应引擎实例已失效,请重新选择可用的引擎实例"的错误提示”,是什么导致的
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
42 0

热门文章

最新文章