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

>📢📢📢📣📣📣

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

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

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

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

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

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

# 前言

<font color=#999AAA >

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

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 …;



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

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 …;



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

sql

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



sql

select top(x,5) from T



sql

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



sql

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



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遍动作，每次关联后都需要保持中间结果供下一轮使用，计算过程复杂，数据也会被遍历多次，计算性能不好。

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

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="">



sql

create index index_T_1 on T(id)



- 一次遍历计算多种分组

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

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

|      | 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资料

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

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

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

SaaS 模式云数据仓库必修课

|
13天前
|
SQL 存储 关系型数据库
【MySQL】SQL 优化
【MySQL】SQL 优化
37 0
|
13天前
|
SQL 分布式计算 DataWorks
MaxCompute产品使用合集之大数据计算MaxCompute即使用相同的SQL语句在DataWorks和Tunnel上执行，结果却不同，如何解决
MaxCompute作为一款全面的大数据处理平台，广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践，可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集，涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
26 0
|
4天前
|
SQL 监控 关系型数据库
【PolarDB开源】PolarDB SQL优化实践：提升查询效率与资源利用
【5月更文挑战第24天】PolarDB是高性能的云原生数据库，强调SQL查询优化以提升性能。本文分享了其SQL优化策略，包括查询分析、索引优化、查询重写、批量操作和并行查询，以及性能监控与调优方法。通过这些措施，可以减少响应时间、提高并发处理能力和降低成本。文中还提供了相关示例代码，展示如何分析查询和创建索引，帮助用户实现更高效的数据库管理。
31 1
|
5天前
|

MaxCompute优化是一个多样而又重要的过程，优化过程需要能够深入理解ODPS的工作原理和内部机制，本文总结了以下几个日常优化案例，最终优化手段可能非常简单，但其中的分析过程较为重要，希望对大家有所启发。
21 0
|
8天前
|
SQL 分布式计算 Java

9 0
|
13天前
|
SQL 缓存 关系型数据库

17 2
|
13天前
|

【5月更文挑战第11天】本文探讨了利用大数据优化业务流程的策略与实践，包括明确业务目标、构建大数据平台、数据采集整合、分析挖掘及流程优化。通过实例展示了电商和制造企业如何利用大数据改进库存管理和生产流程，提高效率与客户满意度。随着大数据技术进步，其在业务流程优化中的应用将更加广泛和深入，企业需积极采纳以适应市场和客户需求。
20 1
|
13天前
|

45 1
|
13天前
|
SQL 存储 关系型数据库
【MySQL系列笔记】SQL优化
SQL优化是通过调整数据库查询、索引、表结构和配置参数等方式，提高SQL查询性能和效率的过程。它旨在减少查询执行时间、减少系统资源消耗，从而提升数据库系统整体性能。优化方法包括索引优化、查询重写、表分区、适当选择和调整数据库引擎等。
240 3
|
13天前
|
SQL 分布式计算 DataWorks
DataWorks操作报错合集之在DataWorks中使用ODPS SQL时遇到"该文件对应引擎实例已失效，请重新选择可用的引擎实例"的错误提示”，是什么导致的
DataWorks是阿里云提供的一站式大数据开发与治理平台，支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中，可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
42 0