單位有一個很複雜的統計SQL,關聯了5張表
並且排序跨越了兩個表，
並且一個升序，一個降序...

這個SQL先查到一個大分類下所有的小分類，然後查詢小分類下所有的歌曲，然後使用歌曲連線到排行榜的資料，
根據優先順序和排行榜的點選率排序，最後取若干條資料。

優先順序升序點選率降序。
表設計嚴格遵守三正規化,沒有一個冗餘欄位

這個SQL的功能是提供大分類下按照點選率排行的歌曲列表，所以使用率很高，雖然有快取，但是快取失效，平均查詢時間需要10s。

由於這個SQL對於點選率的實時性要求不高，所以採用類似Oracle物化檢視，定期全量重新整理的方式最佳化。
新建一個表，MV_Test
每天先truncate這個表，然後使用5個表的關聯查詢初始化這個表，
然後程式只使用MV_Test這個表。

這個最佳化的方式很常見。
但是遇到兩個問題
1.排序欄位一個升序，一個降序。
因為MySQL沒有實現降序的索引,所以在初始化MV的時候，需要整理資料。
假設原來的優先順序為priority欄位，則在插入MV的時候，需要插入（0-priority）
這樣優先順序和點選率就都可以使用降序了。

2.初始化MV的資料需要30s
先truncate，然後初始化資料。問題是初始化資料需要30s。
在這30s內，使用者訪問不到任何的資料。
業務人員也要求最好每個小時重新整理一次。
難道每小時都要給使用者看30s的白板嗎?

後來採用的方案
初始化的資料寫入mv_test_pre表(新建一個"準備"表)
初始化完成之後，rename表。這個rename的過程不到1毫秒。

    RENAME TABLE mv_test TO tmp_mv_test,
    mv_test_pre TO mv_test,
    tmp_mv_test TO mv_test_pre;

參考:

MySQL預先計算

相關文章