做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 支持类似这样的语法: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 大致如下: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 大致是这样的: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 要是能写成这样:select top(x,5) from T 就能避免大排序了。
然而非常遗憾,SQL 没有显式的集合数据类型,聚合函数只能返回单值,写不出这种语句!
不过好在全集的 TopN 比较简单,虽然 SQL 写成那样,数据库却通常会在工程上做优化,采用上述方法而避免大排序。所以 Oracle 算那条 SQL 并不慢。
但是,如果 TopN 的情况复杂了,用到子查询中或者和 JOIN 混到一起的时候,优化引擎通常就不管用了。比如要在分组后计算每组的 TopN,用 SQL 写出来都有点困难。Oracle 的 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 能这样写:select y,top(x,5) from T group by y
把 top 看成和 sum 一样的聚合函数,这不仅更易读,而且也很容易高速运算。
可惜,不行。
还是干瞪眼!
关联计算也是很常见的情况。以订单和多个表关联后做过滤计算为例,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 写成下面的样子: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 值和比对,性能还是很差!
有好办法也实施不了,只能再次干瞪眼!
还有高并发帐户查询,这个运算倒是很简单:select id,amt,tdate,… from T where id="10100" and tdate>= to_date("2021-01-10", "yyyy-MM-dd") and tdate A1=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
2
>env(city,file("city.btx").import@b()),env(employee,file("employee.btx").import@b()),...
3
//系统初始化时,几个小表读入内存
查询
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
B
1
=T.icursor(;id==10100 && tdate>=date("2021-01-10") && tdate //查询代码非常简单
除了这些简单例子,SPL 还能实现更多高性能算法,比如有序归并实现订单和明细之间的关联、预关联技术实现多维分析中的多层维表关联、位存储技术实现上千个标签统计、布尔集合技术实现多个枚举值过滤条件的查询提速、时序分组技术实现复杂的漏斗分析等等。
正在为 SQL 性能优化头疼的小伙伴们,来和我们一起探讨吧:
《慢得受不了的查询跑批》
识别二维码打开该页面
重磅!开源SPL交流群成立了
简单好用的SPL开源啦!
为了给感兴趣的小伙伴们提供一个相互交流的平台,
特地开通了交流群(群完全免费,不广告不卖课)
需要进群的朋友,可长按扫描下方二维码
中国女排亚洲杯14人名单,5位老将压阵,4位新星值得重点关注文水清清北京时间8月21日,女排亚洲杯于今天打响,中国队首战对阵韩国女排,由于9月25日开始有世界杯比赛,所以,本届亚洲杯中国女排是由二队出战,而这份14人大名中,球员们的天赋和能
5300万,告别勇士!用尽手段也无缘顶薪,库里的牺牲没能点醒你NBA新赛季还没有开始,作为卫冕冠军,勇士下赛季面临的难题已经数不胜数了,除了需要面对全联盟的针对之外,勇士内部也是风起云涌,竞争激烈,维金斯普尔的续约问题被丢到下赛季结束,这就意
宏远新赛季最大变化!球队战术重心移至后场徐杰获重用场均砍16随着周鹏的离队,功勋外援威姆斯加盟山西男篮,过去一直处于联盟顶尖水平的广东宏远锋线实力出现大面积下滑已经是不争的事实,杜锋不可能再让锋线承担起球队战术重心,广东宏远的战术重心将首次
招募安东尼!篮网开始行动,杜兰特成关键人物,欧文也不用担心了有那么一批老将,35岁以上的年龄依然征战在联盟中,像勒布朗詹姆斯和克里斯保罗,依然还是一支球队必不可少的核心球员,不过像霍华德,伊戈达拉,他们就只能以角色球员的身份,为所处球队提供
有没有一首歌让你直击心扉,突然就泪流满面有没有一首歌让你直击心扉,突然就泪流满面有没有一首歌,让你直击心扉,突然就泪流满面!这首歌天堂一定很美!相信每个妈妈不在的人都会有这样的感受!图片来自网络天堂一定很美原唱翟煜衡作词
第一次抄书匆匆朱自清偶然黄粱一梦,梦回小学一节无聊冗长数学课堂。炎热的下午,教室外的知了有气无力地鸣叫着,和教室内数学老师波澜不惊的讲课声调相映成趣,凶猛的睡意陡然袭来,我很应景地飘然入梦梦中,我看到
反省自己今天,看到这样一段文字,颇有感触一次,季羡林先生外出参会讲,出门前想起还没有给君子兰浇水,于是嘱咐保姆给君子兰浇水。结果回来后,却发现君子兰死了。细问之下,才知道,保姆浇完水发现外
行孝道,好好去做,用心做孝,不是局限于某一个人,某个时间段不是局限于某个地方要行孝道。孝,不是刻意表现,也不是必须要让人人看到你的付出。孝,是责任,是本分,是义务,是自然要做的事情。孝,不应让别人时刻提醒
哈勃捕捉到银河形态的奇迹这张哈勃本周图片中的星系具有与哈勃熟悉的许多星系不同的形状。虽然它的数千颗明亮的恒星让人联想到一个螺旋星系,但它缺乏特有的蜿蜒结构。闪亮的深红色花朵也很突出,被尘埃云扭曲这些是强烈
就在明天!准备观赏夜空中最亮的星天文科普专家介绍,天空中最亮的小行星灶神星将于8月23日冲日。届时,感兴趣的公众可借助双筒望远镜或小型天文望远镜对其进行观测。此后20天内,仍可寻觅其踪迹。海报制作冯娟除了太阳行星
气候变化使1400个冰川消失了一半以上头号周刊日内瓦一项新的研究发现,自20世纪30年代初以来,瑞士1400个冰川的总体积已经下降了一半以上,研究人员表示,在人们越来越担心气候变化之际,冰的退缩正在加速。受人尊敬的联邦
IMAX8EV五星安全纯电动家里那辆小轿车已经无法满足我家的日常需求了,平常出行人比较多,坐不下,这次IMAXB终于解决了我的难题,也是一直想换一台大一点的一起出门的时候能够方便一些一直在考虑的是哪一款SUV
张天爱才是深藏不漏,纯白吊带连衣裙配长发,直接美出圈春夏交替季节,很多女孩不知道如何穿衣服。事实上,他们不需要太复杂。他们只需要一条吊带裙就能让你走出圈子,瞬间在人群中脱颖而出。搭配吊带裙既年轻又充满活力。如果女孩身材好,就更容易控
鹿晗中长发型翻车?圆脸像发面馒头,姨味越来越浓作为曾经的顶流男星,鹿晗从来没有被停止过吐槽娘。不过那时只是他的长相偏甜一些,不过他的行事风格都挺爷们的,尤其是在顶流时期官宣了和关晓彤的恋情,让粉丝吃惊的同时,也让人们对他刮目相
2000人民币兑换9000泰铢左右,在泰国能干什么?让当地姑娘告诉你如今的人们都喜欢出去旅游,尤其是出国旅游,既能放松自己,又可以出去看看世界。(此处已添加小程序,请到今日头条客户端查看)泰国是我国游客尤为青睐的一个国家,一是因为泰国距离我们国家很
在异世界迷宫开后宫罗克珊是新手指引NPC,lsp才看无修版在异世界迷宫开后宫这次是准时开工了,当然无修版也是在更新完之后的一小段时间内就放出来了,可以看得出来这次的无修版比之前的前3话要更多而且更厚实,而且效果还贼好看,自然在写这篇文章的
泰国方便面厂家集体向政府求涨价环球时报综合报道泰国几大方便面厂商希望政府能够允许他们在一周内提高价格,这将是泰国方便面14年来首次涨价。据英国卫报17日报道,今年的自然灾害导致泰国原材料能源和运输等成本价格急剧
龙珠联名鞋又要来了?!这双短笛Dunk有点帅啊如今衣食住行各领域都能成为球鞋的设计灵感,动漫可以说是最热门的主题之一。近日,一款短笛配色NikeDunkLow现身网络,引起了不少玩家关注。鞋身采用短笛皮肤的绿色衣服的紫色和斗篷
小清新美甲,本甲就能做的款式,喜欢收藏吧分享一篇清新款美甲合集,快抓住夏天的尾巴吧!喜欢的记得收藏起来!第一款肉桂豆沙色渐变腮红法式格纹魔镜粉,珍珠饰品。图片来源于网络第二款玉脂裸透色亮星渐变珍珠,皓石蝴蝶结饰品。图片来
somi全昭弥为品牌拍摄的一组照片somi为代言品牌barrel拍摄的宣传图,少女和大海清凉解暑。卷发小米好美,她本来就很突出的混血五官在卷发衬托下更加明艳漂亮了,充满少女的明媚感。somi身材也很好,修身短上衣和
舒爽温柔的初秋,衣橱里别缺少这几款外套,实用又很时髦换季时节最重要的事情无非是为自己安排起几件当季很受欢迎的单品,满足日常职场的穿搭需要。谈及初秋穿搭,重心自然要放在外套选择上,选对外套款式,不管是出街还是上班都能轻松赢。下面我们就
巴黎铁卫遭多队哄抢,巴萨将其列为首要目标,枪手却率先下手夏窗渐渐临近尾声,欧洲各队都想趁最后时刻得到自己心仪的球员。目前,巴黎圣日耳曼的后卫阿什拉夫哈基米成为多支欧洲豪门的引援目标,但是,巴黎管理层似乎不愿意轻易放走这位摩洛哥国脚。阿什