Java架构—SQL优化实践丨查询速度提升300倍！-CSDN博客

大部分开发人员都熟悉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语句，而非仅仅是从自然语义出发的“查询”。

每天都会分享干货，记得点个关注哦！！！