From Index to Archive

如果今天我要在系統中，查詢存在Database中所有2008年的資料我會怎麼做？一個很直覺得做法:

select *
from hum_note
where YEAR(humnt_sdate) = 2008

我相信對大部份人來說，這是很直覺的，但在where條件中使用function，會令資料庫中，賴以增加效能的index失效。所謂index，是排序後的資料，經由對排序資料的查詢，以減少必要搜尋資料的數量，進而減少搜尋時間，使用者直接感受到的，是[系統變快了]！說到這邊，先以一個簡單的例子，來說明index在查詢時，得以增效的原理，假設有一10個成員的整數陣列：

[4，2，9，6，3，8，7，1，5，0]

若想在這個陣列中，篩選出 >= 5 的成員，需要進行多少次的比對處理？因為資料並未經過排序，因此10個成員，至少須比對10次，如果資料是經過大小排序，想篩選出 >= 5 的成員，又需要進行多少次的比對處理？

[0，1，2，3，4，5，6，7，8，9]

以人腦來說，大刀一切，可能有人回答: 一次
以電腦來說，接觸過演算法的人，會回答: log10 (以2為底的對數，10為總資料數，即所謂時間複雜度為logN)，若是上例未排序的資料，時間複雜度則為N，這意味著，複雜度為N，若資料量增加二倍，處理的時間會增加二倍(2* N)，複雜度為logN，若資料量增加二倍，處理的時間會增加2 * logN倍，有感受到index的威力嗎？ 有index的資料，不但處理時較快，在資料倍增時，處理時間的增量也比較小，我們再回到一開始，查詢系統中2008年所有差假的問題，

select　*
from　hum_note
where　YEAR(humnt_sdate) = 2008

這個查詢，資料庫引擎需要處理多少次，時間複雜度是多少？需要處理多少次，無法回答，要視hum_note中有多少資料而定，因為資料庫引擎沒有預知的能力，因此它要計算出每一筆資料的YEAR(humnt_sdate)後，再拿計算結果和2008進行數字比對，才能知道某筆資料，是否符合這個查詢條件，也就是說，
若hum_note中有10000資料，資料庫引擎需要處理10000次
若hum_note中有30000資料，資料庫引擎需要處理30000次
時間複雜度為N，因為資料庫引擎沒有預知的能力，在where條件中使用function，會令資料庫中，賴以增加效能的index失效我們可以試著把查詢做些修改，讓它可以利用到index的好處

select *
from hum_note
where humnt_sdate >= '2008-01-01' and
humnt_edate <= '2008-12-31'

這個query達到的效果和上個query相同，但時間複雜度只有logN (也許挑剔的人會說是2*logN，因為進行了兩次的比對)，在這邊要大力呼籲programmer的是，勿在SQL query where條件中，使用任何的function，因為這樣的用法會令資料庫中，賴以增加效能的index失效。 也許有人會問，是不是這樣的用法，我完全都不能用了？我的回答是，如果資料表中的資料是有限的，這個用法倒是無仿。 何謂資料有限的table？像是people_ldap（人員基本資料），department_ldap（部門組織資料），這類的table，如果今天單位內有100個部門，到了明年，單位內會有多少部門？也許105，所以增加了5%，如果今天單位內有1000人，到了明年，單位內的人數會是多少？了不起增加一倍，2000人。何謂資料無限的table？像是hum_wkrecord（每日出勤資料），CardLog（人員刷卡資料）之類的table，這類資料表，其內的資料量，應視為無限大，如果今天單位內有10000個人，hum_wkrecord內有100000筆出勤資料，一年後hum_wkrecord會有多少筆資料？ 100000 + 10000 * 365 = 3750000，年增率是3650000 / 100000 = 36.5倍，如果時間複雜度為N，今天出勤查詢需要花費1秒鐘，明年同一個查詢就要花37.5秒了，到了後年，使用者經驗大概會差到無法忍受，也許有人會說，100000筆資料，查詢只要0.001秒，3750000筆也只要花費0.0375秒，user根本感受不出來，是的，或許user感受不到，但效率卻差了37.5倍，如果時間複雜度為logN，37500000資料需要的時間，絕對比複雜度N少，又或許有人會說，不管我的複雜度是N還是logN，過了10年，系統還不是跟蝸牛一樣慢？沒錯，在硬體不升級，資料不斷澎漲的情況下，恐怕是如此情況，這時又帶出另一個議題Archive(資料封存)，定期將舊資料，封存至與線上使用分開的database，並針對這些封存資料，提供限制式的查詢，如此可解決資料爆炸造成的效能問題，這個我想是差勤系統在接下來要列入的部份，即使如此，但站在開發著的立場，還是希望系統在當下有限硬體，能夠有良好的效率。 這邊再提一個在差勤資料查詢上，我們經常的sql query用法，如果我想查詢系統中，和@__sdate ~ @__edate有時間重疊的差假單，我會怎麼做呢？以下是我們常用的方式:
select *
from hum_note
where greatest_datetime(humnt_sd，@__sdate) <=
least_datetime(humnt_ed，@__edate)

多麼簡潔明瞭的寫法啊，但存在一個嚴重效能問題: 我們在where條件中，使用了function call，hum_note是資料無限的table，有資料爆炸的問題，因為每筆資料的humnt_sd要做一次處理，humnt_ed又要一次處理，這個查詢式的時間複雜度是N，有沒有什麼方法，可以改寫這個查詢呢？ 如果把它改成:
select *
from hum_note
where ( humnt_sd >= @__sdate and humnt_sd <= @__edate ) or
( @__sdate >= humnt_sd and @__sdate <= humnt_ed )


以上達成相同的效果相同(效果有無相同，可以請各位驗證一下，若有不同，請不吝一起討論)，但複雜度只有logN。

[結論]
1. 盡量不要在SQL query where條件中，使用function，請優先思考非function的solution
2. Archive是勢在必行