SQL性能优化的僵局
#编程 #sql #database #bigdata

在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 
 select a,b,sum(x) group by a,b where … -- the first grouping in the traversal
select c,d,max(y) group by c,d where … -- the second grouping in the traversal
select a,c,avg(y),min(z) group by a,c where …; -- the third grouping in the traversal

它将能够在一个遍历中返回多个结果集,并且可以大大提高性能。

不幸的是,SQL没有此语法,也不能像这样编码。我们只能使用替代方法,即使用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 …;

以这种方式,我们只需要遍历一次,但是我们必须将不同的地方转移到以前的情况下,代码更为复杂,计算的量将增加。此外,当计算临时表时,分组字段的数量变大,结果集可能很大。临时表经过多次遍历,计算性能不好。大型结果集分组计算需要硬盘缓冲区,并且其性能也很差。

我们还可以使用存储过程的数据库光标来一一获取数据,但是我们必须自己实施地点和分组的操作。编码太麻烦了,数据库光标的性能只会更糟!

我们无能为力!

TOPN操作还将遇到这种无助的情况。例如,用Oracle SQL编写的TOP5很粗糙如下:

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

表t中有10亿个数据。从SQL语句可以看出,获得前五名的方法是对所有数据进行排序,然后获得前五个数据,其余的排序结果无用呢大分类的成本很高。数据量太大,无法加载到内存中。将有多个硬盘数据缓冲,计算性能将非常差!

不难避免进行大规模分类。在内存中保留一小部分5个记录。遍历数据时,将最高的5个计算数据保存在此小集合中。如果新数据大于当前第五数据,请插入并丢弃当前的第五个。如果它小于当前的第五,则不会采取任何措施。这样,我们只需要一次遍历10亿个数据,并且记忆职业非常小,并且计算性能将得到很大改进。

本质上,该算法将TOPN视为与sum和count相同的汇总操作,但返回一个集合而不是单个值。如果可以像这样写SQL:从t选择顶部(x,5),它将能够避免进行大量排序。

不幸的是,SQL没有明确的设置数据类型。聚合函数只能返回一个值,不能编写此类语句!

幸运的是,整个组的顶部相对简单。尽管SQL是这样写的,但数据库通常可以在实践中进行一些优化,并且采用上述方法以避免进行大量分类。结果,Oracle计算该SQL语句的速度并不慢。

但是,如果TOPN的情况很复杂,则优化引擎在子征服中使用或与JOIN混合时通常不起作用。例如,要在分组后计算每个组的顶部,在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语句可以如下写:

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使用哈希联接算法来实现这些关联。哈希值将被计算并比较。一次只能解决一个连接,如果有N连接,则必须执行相同的操作。每次加入后,都需要保留中间结果。计算过程很复杂,数据将被多次遍历,并且计算性能很差。

通常,这些关联的表很小,可以首先读取为内存。如果订单表中的每个关联字段已提前序列化,例如,将员工ID字段值转换为相应员工表记录的序列编号,当计算时,我们可以使用员工ID字段值(即,序列,员工表的数量)直接在内存中的员工表的相应位置获取记录。性能比哈希(Hash)加入要快得多,我们只需要一次穿越订单表,并且速度将大大提高!

也就是说,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使用无序集的概念。即使这些ID已序列化,数据库也无法利用此功能。它不能在这些相应相关表的无序集上使用快速序列数定位的机制。它只能使用索引搜索。此外,数据库不知道ID已被序列化,并且仍然可以计算哈希值并进行比较,并且性能仍然非常差!

尽管有好方法,但不能实施它们。而且我们仍然无能为力!

也有高度并发的帐户查询。此操作非常简单:

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的理论无序基础。数据总量很大,不能完全读取内存,并且数据库无法确保同一帐户的数据在物理上不断存储。硬盘具有最小的读数单元。在阅读不连续的数据时,将获取许多无关的内容,并且查询将很慢。如果每个在高并发下的查询都稍慢一些,那么总体性能将非常差。谁敢让用户在用户体验非常重要的时候等待十秒钟以上?!

一种简单的思考方法是根据帐户提前对数亿个数据进行排序,并确保连续存储同一帐户的数据。这样,查询期间的硬盘几乎所有数据块都是目标值,并且性能将得到极大的改进。

但是,使用SQL系统的关系数据库没有这种意识,也不会强制数据存储的物理顺序!此问题不是由SQL语法引起的,而是与SQL的理论基础有关。在关系数据库中实现这些算法仍然是不可能的。

现在,我们该怎么办?我们可以做任何事情吗?

我们不能再使用SQL和关系数据库。我们需要使用其他计算机。

基于创新的理论基础,开源ESPROC SPL支持更多的数据类型和操作,并可以在上述情况下描述新算法。用简单便捷的SPL代码可以在短时间内大大提高计算性能!

用SPL编写的上述任务的代码示例如下:

lâ计算一个遍历中的多个分组

    A   
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   
//Define the first filtering and grouping in the traversal
4   cursor  =A4.select(…).groups(c,d;max(y))
5   
// Define the second filtering and grouping in the traversal
6   cursor  =A6.select(…).groupx(a,c;avg(y),min(z))
7   
// Define the third filtering and grouping in the traversal
8   … //End of definition, start calculating all three types of filtering and grouping

lâ通过聚合方法计算top5

Top5的总集合(多线程并行计算)

    A
1   =file("T.ctx").open()
2   =A1.cursor@m(x).total(top(-5,x), top(5,x))
3   // top(-5,x) calculates top 5 with the largest x,top(5,x) calculates top 5 with the smallest x

每组的TOP5(多线程并行计算)

    A
1   =file("T.ctx").open()
2   =A1.cursor@m(x,y).groups(y;top(-5,x), top(5,x))


la加入¼

系统初始化

    A
2   >env(city,file("city.btx").import@b()),env(employee,file("employee.btx").import@b()),...
3   //During system initialization, read several small tables into memory

查询

    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   //First join the sequence number, and then write the filter conditions by referencing the associated table fields

lâ高并发帐户查询

数据预处理和有序存储

    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   //Sort the original data, save it as a new table, and create index for id field

帐户查询

    A   B
1   =T.icursor(;id==10100 && tdate>=date("2021-01-10") && tdate<date("2021-01-25") && …,index_id).fetch()
2   //The query code is very simple

除了这些简单的示例外,SPL还可以实施更多的高性能算法,例如在多维尺寸表中的订单和详细信息之间的关联,在多维分析中的订单和详细信息之间的关联,位存储技术,对于数千个标签的统计数据,布尔设定了技术来加快多个枚举值过滤条件的查询,定时分组技术用于复杂漏斗分析等等。

>

头痛以进行SQL性能优化的朋友,与我们讨论:
http://www.scudata.com/html/Unbearably-slow-query-and-batch-job.html

起源:https://blog.scudata.com/the-impasse-of-sql-performance-optimizing/

SPL源代码:https://github.com/SPLWare/esProc