这些 SQL语句真是让我干瞪眼!
共 1754字,需浏览 4分钟
·
2022-01-10 00:02
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 是个有数亿行的巨大表,要分别按三种方式分组,分组的结果集都不大。
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 里,代码复杂很多,也会加大计算量。而且,计算临时表时分组字段的个数变得很多,结果集就有可能很大,最后还对这个临时表做多次遍历,计算性能也快不了。大结果集分组计算还要硬盘缓存,本身性能也很差。
TopN 运算同样会遇到这种无奈。举个例子,用 Oracle 的 SQL 写 top5 大致是这样的:
select * from (select x from T order by x desc) where rownum<=5
表 T 有 10 亿条数据,从 SQL 语句来看,是将全部数据大排序后取出前 5 名,剩下的排序结果就没用了!大排序成本很高,数据量很大内存装不下,会出现多次硬盘数据倒换,计算性能会非常差!
select * from
(select y,x,row_number() over (partition by y order by x desc) rn from T)
where rn<=5
这时候,数据库的优化引擎就晕了,不会再采用上面说的把 TopN 理解成聚合运算的办法。只能去做排序了,结果运算速度陡降!
select y,top(x,5) from T group by y
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 ...
订单表有几千万数据,城市、运货商、雇员、供应商等表数据量都不大。过滤条件字段可能会来自于这些表,而且是前端传参数到后台的,会动态变化。
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<to_date('2021-01-25', 'yyyy-MM-dd')
and …
在 T 表的几亿条历史数据中,快速找到某个帐户的几条到几千条明细,SQL 写出来并不复杂,难点是大并发时响应速度要达到秒级甚至更快。为了提高查询响应速度,一般都会对 T 表的 id 字段建索引:
create index index_T_1 on T(id)
在数据库中,用索引查找单个帐户的速度很快,但并发很多时就会明显变慢。原因还是上面提到的 SQL 无序理论基础,总数据量很大,无法全读入内存,而数据库不能保证同一帐户的数据在物理上是连续存放的。硬盘有最小读取单位,在读不连续数据时,会取出很多无关内容,查询就会变慢。高并发访问的每个查询都慢一点,总体性能就会很差了。在非常重视体验的当下,谁敢让用户等待十秒以上?!
A | B | |
1 | 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
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 | |
2 | //查询代码非常简单 |
除了这些简单例子,SPL 还能实现更多高性能算法,比如有序归并实现订单和明细之间的关联、预关联技术实现多维分析中的多层维表关联、位存储技术实现上千个标签统计、布尔集合技术实现多个枚举值过滤条件的查询提速、时序分组技术实现复杂的漏斗分析等等。