Emesene & SQL Optimization

最近都是用 emesene 上 MSN,不過他的啟動速度實在是非常慢,啟動的時候硬碟燈狂閃,判斷應該是在讀取聯絡人的資料快取。先用 python profiler 跑過一次,結果果然是 Logger.py 裡面從 sqlite 撈資料的函式在慢。

大小約 17MB 的 db,每個 query 竟然都要一秒以上,難怪 emesene 啟動要這麼久了。就來幫 emesene 最佳化一下 sql 吧。

以底下這個 query 為例:

#a71d5d;">select #0086b3;">e.#0086b3;">stamp,
       #0086b3;">ue.#0086b3;">data
#a71d5d;">from event e,
      user_event ue,
      user u
#a71d5d;">where #0086b3;">e.#0086b3;">id      #a71d5d;">= #0086b3;">ue.#0086b3;">id_event #a71d5d;">and
      #0086b3;">u.#0086b3;">id      #a71d5d;">= #0086b3;">ue.#0086b3;">id_user #a71d5d;">and
      #0086b3;">e.#0086b3;">name    #a71d5d;">= "status-changed" #a71d5d;">and
      #0086b3;">u.#0086b3;">account #a71d5d;">= "%s"
#a71d5d;">order by #0086b3;">e.#0086b3;">stamp #a71d5d;">desc
#a71d5d;">limit #0086b3;">1

結果是

1221129396.22343|NLN
CPU Time: user 1.173822 sys 0.023997

用 explain query plan 看看

0|1|TABLE user_event AS ue
1|0|TABLE event AS e USING PRIMARY KEY
2|2|TABLE user AS u USING PRIMARY KEY

應該是先把 user_event 與 event, user 三個 table join 在一起,最後再檢查 e.name 與 u.account。

來看看三個 table 的大小

  • user_event: 180801 row
  • event: 192968 row
  • user: 196 row

這樣 join 起來是多大的 table 呀

先試試改寫 from clause 裡的順序

#a71d5d;">select #0086b3;">e.#0086b3;">stamp, #0086b3;">ue.#0086b3;">data 
#a71d5d;">from user u, event e, user_event ue
#a71d5d;">where #0086b3;">e.#0086b3;">id      #a71d5d;">= #0086b3;">ue.#0086b3;">id_event #a71d5d;">and
    #0086b3;">u.#0086b3;">id      #a71d5d;">= #0086b3;">ue.#0086b3;">id_user #a71d5d;">and
    #0086b3;">e.#0086b3;">name    #a71d5d;">= "status-changed" #a71d5d;">and
    #0086b3;">u.#0086b3;">account #a71d5d;">= "%s"
#a71d5d;">order by #0086b3;">e.#0086b3;">stamp #a71d5d;">desc
#a71d5d;">limit #0086b3;">1

結果是

    1221129396.22343|NLN
    CPU Time: user 0.485926 sys 0.008998

很好,時間少了一半,explain query plan 顯示

    0|2|TABLE user_event AS ue
    1|0|TABLE user AS u USING PRIMARY KEY
    2  |1|TABLE event AS e USING PRIMARY KEY

如果幫 user_event 建個 index 呢?

#a71d5d;">create index ueiduser on #795da3;">user_event(id_user);

再 query 一次

1221129396.22343|NLN
CPU Time: user 0.017997 sys 0.004999

快了非常多吧!explain query plan 顯示:

0|0|TABLE user AS u
1|1|TABLE user_event AS ue WITH INDEX ueiduser
2|2|TABLE event AS e USING PRIMARY KEY

這是因為 sqlite 會以 from clause 的第一個當作 outer loop,所以我把數量最小的 user 移到前面,再適當的建立 index,使得 query 的速度快了 100 倍…

參考

Patch 在此

Emesene svn trunk 已經收錄最新 patch 🙂


已發佈

分類:

,

作者:

標籤: