一個清理和查詢都要兼顧的簡單方案

最近和開發應用的同學在討論一個需求，目前他們碰到了一些效能問題，想讓我來看看是否能夠從資料庫的角度有一些解決方案。
假設表為消費記錄，簡稱service_details,這是一個普通表，目前這個表資料量很大，需要定期去刪除一些過期的資料，至於過期的標準先暫時按照兩個星期來算。可見這是一個需求變化極快的場景。
每天的新增資料情況為幾百萬，那麼兩個星期的資料量大概就在5千萬至一億左右。
為什麼現在考慮要改進這個表呢，有一個主要的原因是他們碰到了效能瓶頸，根據他們的業務需求，他們需要每分鐘都定時去查詢這個表，然後和其它的幾個表做一個關聯，然後把查詢的結果資料顯示在前端頁面作為一個實時的展現，是的，是每分鐘都會執行。如果資料量達到了一定的程度，查詢就會慢，舊資料也越來越多，如果去刪除這部分的資料，目前是根據時間條件去刪除，使用的是delete方式，而且目前在時間欄位上也存在索引，按照這種情況似乎索引讓問題也沒有什麼改善，前段時間他們做一個實時的查詢顯示，結果剛好有一箇舊資料刪除的任務，結果刪除的操作持續時間比較長，結果其它的查詢都會掛起，新增資料的操作也被阻塞。結果一個查詢沒做出來，第二分鐘的查詢已經照例開始執行了，然後第三分鐘，最後系統就被拖的很慢。當然最後還是找我們臨時解決，kill了那些session得以臨時釋放，而對於他們來說，這種刪除操作還是比較昂貴的，每次嘗試刪除都有些提心吊膽。
所以大體瞭解了他們的需求，其實主要在一下幾個點上，
首先這個表是一個普通表，表中的資料變化較快，需要刪除舊資料。
目前的查詢邏輯是1分鐘觸發一次，當然聽起來在邏輯上似乎還是有改進的地方，不過可以看出執行頻率還是相對比較固定而且頻繁，一旦出現了某個查詢緩慢或者阻塞，就會馬上出現系統緩慢的情況。
目前的資料清理工作比較痛苦，想刪除資料，但是感覺刪除的操作持續時間較長，不刪除舊資料較多，也影響查詢。
其實我說到這裡，大家應該也猜出來了，我最後推薦他們使用分割槽表。
推薦理由如下：
首先對於主要的痛點是刪除操作，那麼最快的刪除肯定不是delete,而是truncate，但是不能truncate全表啊，我們可以truncate分割槽
然後根據刪除的策略，是保留2個星期的資料，查詢的時候是根據時間欄位來查詢，所以完全可以考慮採用分割槽欄位作為分割槽鍵值，然後通過truncate partition的方式清理分割槽，然後查詢時直接根據時間欄位可以直接訪問指定的分割槽，而不會直接訪問全表的資料，掃描100個分割槽和掃描1個分割槽，其實基數完全不同。

當然還有一個主要的問題就是分割槽的維護，開發的同事覺得這種方式聽起來不錯，不過對於新增分割槽還是存在顧慮，比如在這個專案持續了很長時間，可能只有我們知道這個邏輯，一旦他們撤出專案了，或者沒有關注忘記了，分割槽沒有繼續增加維護到時候就比較難處理了，其實對於這點來說，DBA是需要付出一些額外的工作的。當然對於解決這個問題而言都是值得的。

假設保留了6天的資料，比如到第9天的時候，就會是右邊的藍色部分
當然也不是隻是說說，網際網路公司的實踐能力就是強，今天提完，明天就馬上付諸實踐，從現在的情況來看，查詢語句是隻會掃描某一個分割槽，不過還是遇到了一些小小的問題，那就是關於統計資訊。
這個怎麼理解呢。比如分割槽p_20160105是存放2016年1月6號的資料，但是在1月5號肯定是沒有任何資料的，直到1月6號的時候才會生成大量的資料，所以按照Oracle預設的Job去收集統計資訊，收集統計資訊之後，1月6號的分割槽統計資訊還是0，在執行sql的時候還是可能出現一些問題。這個時候有一個持續的改進方法，一個就是保證統計資訊儘可能新，準確，另一個思路就是保證執行計劃的執行路徑是最優最穩定的。這樣的話就有幾個額外的附件任務，一個就是可以考慮對於統計資訊進行分割槽級的收集，當然對於某一個特定分割槽也是幾秒鐘即可搞定，而且也不會影響線上查詢，另外一個思路就是穩定執行計劃，可以考慮使用sql profile來做。
上面的方式也都已經付諸實踐，還有一個方法，就是根據每天的資料生成量，直接設定一個固定的資料統計資訊，比如每天500萬的資料，就設定這個分割槽有500萬的資料，也讓優化器吃一顆定心丸。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1971212/，如需轉載，請註明出處，否則將追究法律責任。