Table Schema 設計的重要性

想起之前遇過的一個專案, 對於 SQL 下法不同而有很嚴重的效能影響, 今天整理下來做個紀錄.

此專案需要紀錄某個產品在某年某月共有多少個數量與價值, 看描述我們可以得到以下的 Table Schema(命名為 table1), 欄位如下:
id, code, year, month, qty, value (除了 code 為 varchar 之外, 其餘為 decimal)

這個 Table Schema 看起來是很合理的, 但後來需要做統計報表的功能時. 就發現此設計的缺陷.

說明如下, 現在需要讓使用者挑選產品與年月起迄, 進而統計出總數量與總價值.
初步我們撰寫 SQL 如下(挑選條件: code為123, 從 94年2月到95年2月):

select sum(qty), sum(value) from table1 where code = '123'
   and year * 100 + month between 9402 and 9502

如此可求得正確答案.
現在問題來了, 此 table 的資料量目前為 11,411,862 筆.
由此 SQL 跑出來所需要的時間花了 2 分鐘 17.313 秒..

此 table 已經有加上 code, year, month 之 index.
但為何沒有成效?
原因在於, 我們將 year, month 做了運算, 所以 index 派不上用場.

由於此專案的 Table Schema 無法更動, 且很要求報表效能....
那我們該如何解決此問題呢? 就要透過下列的 SQL 來改進了..

select sum(qty), sum(value) from table1 where code = '123'
   and ((year = 94 and month between 2 and 12)
             or (year = 95 and month between 1 and 2))

得到的結果是相同的, 但此 SQL 所需花費的時間僅 1.31 秒.
與前一個 SQL 的效能差了百倍以上...

雖然我們解決了效能問題, 但可以看到的是, SQL 寫的就比較醜..
而這還不是最麻煩的案例, 若要統計3年的量, 則 SQL 就不能這樣寫了...
而若是統計同年的 SQL, 則該 SQL 又顯的笨重.
就程式面要組 SQL 的角度來看, 程式如下:

String sql = "sum(qty) w, sum(value) v " +
       "from table1 " +
       "where code = ? ";
if (y1 == y2) { //同年
   sql += "and year = ? and month between ? and ? ";
} else { //不同年
   sql += "and ((year > ? and year < ?) " +      //y1, y2 (1到12月)        
      "or (year = ? and month between ? and ?) " +   //y1  (m1 ~ 12)         
      "or (year = ? and month between ? and ?)) ";    //y2  (1 ~ m2) 
} 

可見這讓程式不好寫多了, 而且在開發階段, 資料量不大, 很容易被忽略效能的因素, 而寫出一剛開始的 SQL.

而我們若有此經驗, 我們該如何設計好 Table Schema 呢?
其實, 很簡單, 只要多加一個欄位, 如 year_month. 而此欄位存放 year * 100 + month 的值...
如此, 此欄位即可加上 index, SQL 寫法會變成如下:

select sum(qty), sum(value) from table1 where code = '123'
   and year_month between 9402 and 9502

如此, 效能與語法都可以獲得改善, 唯一的缺點就是多佔了點硬碟空間...
不過能用硬體解決的, 還是儘量透過硬體解決囉..

在這實際的案子中, 除了年月之外, 還有分批次的觀念(一個月分四批次, 但若批次欄位為 null, 則代表是該月份的加總值), 又加上不能動 Table Schema...
所以實際的 SQL 程式比上面所列的更加複雜化, 效能差異更是 5 分鐘與 1 秒多的差別...

所以, 要設計好 Table Schema 不是一件容易的事. 需要全盤考量及經驗的累積.