大部分开发人员都熟悉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语句，而非仅仅是从自然语义出发的“查询”。
每天都会分享干货，记得点个关注哦！！！
网友评论