用 sonata-project/admin-bundle 做的後台,上線2個月左右,出現了一個效能問題,把調試工具開啟發現在查詢列表的時候 有2條SQL語句執行特別慢
以上兩條SQL隨著項目的增大,執行時間越來越長。目前150W左右資料執行時間需要 18058.10 ms
我猜想是 knplabs/knp-paginator-bundle 在分頁和查詢總數的時候 總數帶上了關鍵字 "DISTINCT"
我把這兩條語句的關鍵字 DISTINCT 去掉以後,直接拿到資料庫執行只需要 5ms 。所以我去尋找 symfony2、sonata-project/admin-bundle、knplabs/knp-paginator-bundle 對於這個關鍵字的處理或者配置,但是持續尋找了快一個禮拜都沒有結果。
希望有知道這個問題的大神能夠協助我分析分析
回複內容:
用 sonata-project/admin-bundle 做的後台,上線2個月左右,出現了一個效能問題,把調試工具開啟發現在查詢列表的時候 有2條SQL語句執行特別慢
以上兩條SQL隨著項目的增大,執行時間越來越長。目前150W左右資料執行時間需要 18058.10 ms
我猜想是 knplabs/knp-paginator-bundle 在分頁和查詢總數的時候 總數帶上了關鍵字 "DISTINCT"
我把這兩條語句的關鍵字 DISTINCT 去掉以後,直接拿到資料庫執行只需要 5ms 。所以我去尋找 symfony2、sonata-project/admin-bundle、knplabs/knp-paginator-bundle 對於這個關鍵字的處理或者配置,但是持續尋找了快一個禮拜都沒有結果。
希望有知道這個問題的大神能夠協助我分析分析
tuser_recharge.user_id 看上去必是 user.id 的子集,你在tuser_recharge(1.5m)上做DISTINCT,再怎麼JOIN也沒什麼用,況且兩個語句並不從user表關聯取值,所以JOIN是多餘的。
但真正的問題還是在tuser_recharge的資料量,1.5m資料作DISTINCT,哪怕user_id有索引也不影響DISTINC的執行,mysql會遍曆整個索引,1.5m記錄,假設索引裡單記錄執行花費0.00001秒,光遍曆索引就需要大概0.000001x1500000=1.5秒,你畫出的第一條語句的執行結果就在這個數量級上。
這你可以直接跑
SELECT DISTINCT user_id FROM tuser_recharge
來驗證,速度會在一個數量級上。
第二條語句要慢很多,是因為除了遍曆整個1.5m的索引,還需要產生暫存資料表做SORT(因為ORDER BY),慢是可想而知的。
最佳化的思路,第一是看你是否有用WHERE的可能,即避免讓DISTINCT遍曆整個索引,而用WHER先縮小範圍。
SELECT DISTINCT user_idFROM tuser_rechargeWHERE col = xxx
如果業務不允許,那麼最好的辦法不是最佳化DINSTINCT,而是最佳化你的架構。通常操作思路是把前端代碼和慢SQL語句解耦,做一個MYSQL SLAVE,用一個背景程式定時執行慢語句,把結果存入某個地方,前端語句直接讀取這個結果,不經過mysql。這樣的好處是前端不會再有伸縮性問題,壞處是犧牲了一定的即時性。如果你控制後台語句每一分鐘執行一次,對一般業務也不至於產生什麼問題。通常使用者前端有一分鐘或者幾分鐘的延遲並不是什麼大問題。
這樣做你在架構上的收益是最大的,因為一個慢語句的成本不只是這個慢語句本身,還會BLOCK其他語句的執行,這是線上系統資料庫最應該避免的。