做 SQL 性能优化真是让人干瞪眼
做 SQL 性能优化真是让人干瞪眼
xiaozhan_Python
一个有温度的 Python 订阅号！
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<date("2021-01-25") && …,index_id).fetch() | |
| 2 | //查询代码非常简单 |
除了这些简单例子，SPL 还能实现更多高性能算法，比如有序归并实现订单和明细之间的关联、预关联技术实现多维分析中的多层维表关联、位存储技术实现上千个标签统计、布尔集合技术实现多个枚举值过滤条件的查询提速、时序分组技术实现复杂的漏斗分析等等。
《慢得受不了的查询跑批》
识别二维码打开该 页面
重磅！开源SPL交流群成立了
简单好用的SPL开源啦！
为了给感兴趣的小伙伴们提供一个相互交流的平台，
特地开通了交流群（群完全免费，不广告不卖课）
需要进群的朋友，可长按扫描下方二维码
本文感兴趣的朋友，请转到阅读原文去收藏 ^_^