Java架构—SQL优化实践丨查询速度提升300倍!-sql文件查看器

大部分开发人员都熟悉SQL,无论用什么语言开发系统,只要用到了关系型数据库,都会涉及到SQL的使用。

在某些系统中,主要的程序逻辑都体现一个个存储过程里,例如数据中心产品,这时候,大家都认为该产品主要的开发语言是SQL,于是我们把SQL当作程序本身来看待。但是在更多的业务系统中,我们通常只需要进行普通的增删改查,SQL更多只是插入在Java或者XML文件里的一些查询语句,这个时候,开发人员只把SQL当作查询分析的工具,而不是程序来看待。

接下来为大家讲述一个工作中发生的关于SQL优化的真实故事。

这是一个用户行为分析的系统,其中有三张表(简化字段后),见下图。

Java架构—SQL优化实践丨查询速度提升300倍!

在daily_access表中,记录了当天的用户访问情况,一条记录就是一次访问请求;

在ip_range表中,存着IP地址的分段,从ip_start到ip_end之间的IP地址,属于同一个地区;

area表则记录着area_id和所对应的地区,由于同一个地区可能有很多个IP段,所以area表会有重复数据。

3个表的数据量情况:daily_access表的数据量约10万,area表和ip_range表约50万。这里的先决条件:ip_range表和area表是一对一关系,并且ip_start和ip_end必然互斥,不存在重叠区间。

现在的需求是,从三张表中统计出来自每个地区的访问者人数。

如果按照“查询”的思维来看,这个实现非常简单,不考虑未命中的话,daily_access表的ip_access字段必然落在ip_range的某个ip_start和ip_end之间,进行三个表连表查询即可,查询语句如下:

select COUNT (*), a.addr

from daily_access d, ip_range r, area a

where 1 =1

and d.ip_access between r.ip_start and r.ip_end

and r.area_id = a.area_id

group by a.addr;

这个SQL固然是正确的,它曾经在系统中使用过一段时间,但是效果欠佳,因为在前述数据量下, SQL一次的执行时间大约是15分钟。

或许你会觉得,对于一个后台分析系统来说,查询结果并不需要实时查看,输出到报表或者存入结果表备查都可以——确实如此——但是10万的访问量其实是一个非常小的数字,如果访问量有百万,千万呢,那么消耗的时间会成指数上升,甚至执行一晚上也出不了报表。

因此,查询语句进行了一定的优化:数据量少的表先过滤,再去关联数据量多的表:

select COUNT (*), a.addr

from ( select t1.ip_access , t2.addr

from (select d.ip_access ,

( select r.area_id

from ip_range r

where d.ip_access between r.ip_start and r.ip_end) as area_id ,

from daily_access d) t1,

area t2

where t1.area_id = t2.area_id ) d,

area a

where d.area_id = a.area_id

group by a.addr;

经过优化之后,由于首先处理了数据较多的表,筛选出较少的结果后再和另一个表关联,所以速度有所提升,执行一次大约是6分钟左右。虽然第二条方案比第一条效率提高了一倍以上,但是很显然,不管是哪一条,性能都很难被接受。

接下来,我们来看看实际生产系统中使用的查询语句是怎样的(同样简化了字段,以便看更清晰):

with vstat_details as ( select /*+ all_rows materialize */ distinct ip_access from daily_access ),

vstat_ip_range as (

select /*+ all_rows materialize */

v2.ip_start n_ip, v2.area_id

from (select v1.dataset, v1.ip_start,

last_value(v1.range_start ignore nulls ) over (order by v1.vc_ip_start,v1.dataset) range_start,

last_value(v1.range_end ignore nulls ) over (order by v1.vc_ip_start,v1.dataset) range_end,

last_value(v1.area_id ignore nulls ) over (order by v1.vc_ip_start,v1.dataset) area_id

from (select 1 dataset,

t1.ip_start,

t1.ip_start range_start,

t1.ip_end range_end,

t1.area_id

from ip_range t1

union all

select /*+ leading(d) use_hash(r) no_merge(d) full(r) */

2 dataset,

t2.n_ip ip_start,

null range_start,

null range_end,

null area_id

from daily_access t2) v1) v2

where v2.ip_start >= v2.range_start

and v2.ip_start <= v2.range_end

and v2.dataset = 2)

select /*+ all_rows leading(v,d) use_hash(d,a) no_merge(v) */

count (*) as n_pageviews,

a.addr

from vstat_ip_range v,

daily_access d,

area a

where v.n_ip = d.ip_access

and v.area_id = a.area_id

group by a.addr;

为什么一个简单的查询语句有那么长呢?

前面两段查询语句,开发人员在编写的时候,潜意识里把SQL当作一种查询和分析数据的手段和工具,而不是编程,而这段SQL,不仅仅从“查”这个视角来看问题,更是利用数据结构和算法来解决问题。这种出发点的不同,导致了编程思路的不同。

接下来,我们来把上面这段SQL拆解开研究一下它的解题思路。

首先,从最内层入手,内层的子查询,对ip_range表的数据进行了预处理,添加了一个标记“1”:

select 1 dataset,

t1.ip_start,

t1.ip_start range_start,

t1.ip_end range_end,

t1.area_id

from ip_range t1

假设ip_range的数据如下(为了方便,我们把IP简化为简单整数表示):

id area_id ip_start ip_end

-------------------------------------------------------

1 1 15 20

2 2 22 25

3 3 30 35

4 4 36 40

那么标记完成后的数据结构将是如下

标记 area_id ip_start ip_end start2

----------------------------------------------------------------------

1 1 15 20 15

1 2 22 25 22

1 3 30 35 30

1 4 36 40 36

再接下来,要将访问记录表daily_access,也按照来访IP记录,整理成相同格式,并且添加标记“2”:

select 2 dataset,

t2.n_ip ip_start,

null range_start,

null range_end,

null area_id

from daily_access t2

我们假设有以下4条访问记录,那么整理后的临时数据结构如下:

标记 area_id ip_start ip_end start2

----------------------------------------------------------------------

2 null 16 null null

2 null 22 null null

2 null 24 null null

2 null 39 null null

如果把两个表合并(union all),并且按照ip_start和标记字段进行排序,就能得到下面这个数据结构:

标记 area_id ip_start ip_end start2

----------------------------------------------------------------------

1 1 15 20 15

2 null 16 null null

1 2 22 25 22

2 null 22 null null

2 null 24 null null

1 3 30 35 30

1 4 36 40 36

2 null 39 null null

其实我们要取的内容,就是标记为2的ip所对应的area_id,但此时还看不出来,所以接下来最关键的一步是,将所有的“null”用数据填满,填充的规则是,用它上面一条相邻的标记为1的数据的对应字段的值来填充,于是得到下图:

标记 area_id ip_start ip_end start2

----------------------------------------------------------------------

1 1 15 20 15

2 1 16 20 15

1 2 22 25 22

2 2 22 25 22

2 2 24 25 22

1 3 30 35 30

1 4 36 40 36

2 4 39 40 36

从上面这个临时表中剔除标记为“1”的数据后,就得到了我们需要的数据:

标记 area_id ip_start ip_end start2

----------------------------------------------------------------------

2 1 16 20 15

2 2 22 25 22

2 2 24 25 22

2 4 39 40 36

从中可以看到,需要统计的area_id已经一目了然,任何ip_start的值落在同一条数据中ip_end和start2之间的数据,其area_id都是我们要取得数据。整个过程没有做任何大数据量的连表查询,效率非常高。

将上述过程预构造成一个临时表,就是前述查询语句上半段所做的事:

with vstat_details as ( select /*+ all_rows materialize */ distinct ip_access from daily_access ),

vstat_ip_range as (

select /*+ all_rows materialize */

v2.ip_start n_ip, v2.area_id

from (select v1.dataset, v1.ip_start,

last_value(v1.range_start ignore nulls ) over (order by v1.vc_ip_start,v1.dataset) range_start,

last_value(v1.range_end ignore nulls ) over (order by v1.vc_ip_start,v1.dataset) range_end,

last_value(v1.area_id ignore nulls ) over (order by v1.vc_ip_start,v1.dataset) area_id

from (select 1 dataset,

t1.ip_start,

t1.ip_start range_start,

t1.ip_end range_end,

t1.area_id

from ip_range t1

union all

select /*+ leading(d) use_hash(r) no_merge(d) full(r) */

2 dataset,

t2.n_ip ip_start,

null range_start,

null range_end,

null area_id

from daily_access t2) v1) v2

where v2.ip_start >= v2.range_start

and v2.ip_start <= v2.range_end

and v2.dataset = 2)

而最后,只需要用这个临时表进行简单关联查询:

select /*+ all_rows leading(v,d) use_hash(d,a) no_merge(v) */

count (*) as n_pageviews,

a.addr

from vstat_ip_range v,

daily_access d,

area a

where v.n_ip = d.ip_access

and v.area_id = a.area_id

group by a.addr;

由于没有between 比较,数据量也被预先筛选处理,整个查询过程非常的快速,前述数据量下,查询大约耗时3秒,比最初的查询语句性能要高出300倍。

实际上,目前用户行为分析系统已经用大数据平台进行了重制,IP地址比较也可以用非关系型数据库来获得更高的性能,但这段旧系统中的查询语句,能带给我们的启发,仍然非常有意义,它用事实让我们重新认识到这样一个道理:SQL也是程序。

记住这一点,能帮助在我们今后的程序开发中,写出更符合“程序”思维的SQL语句,而非仅仅是从自然语义出发的“查询”。

每天都会分享干货,记得点个关注哦!!!

推荐阅读