SQL优化实战

李铁刚
原创 2452       2019-04-23  


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


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

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


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

在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语句,而非仅仅是从自然语义出发的“查询”。


恒生技术之眼原创文章,未经授权禁止转载。详情见转载须知

联系我们

恒 生 技 术 之 眼