大部分開發人員都熟悉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語句，而非僅僅是從自然語義出發的“查詢”。

每天都會分享乾貨，記得點個關注哦！！！