鏈接:https://www.cnblogs.com/dijia478
一、問題背景
現網出現慢查詢,在500萬數量級的情況下,單表查詢速度在30多秒,需要對sql進行優化,sql如下:
我在測試環境構造了500萬條數據,模擬了這個慢查詢。
簡單來說,就是查詢一定條件下,都有哪些用戶的。很簡單的sql,可以看到,查詢耗時為37秒。
說一下app_account字段的分佈情況,隨機生成了5000個不同的隨機數,然後分佈到了這500萬條數據裡,平均來說,每個app_account都會有1000個是重複的值,種類共有5000個。
二、看執行計劃
可以看到,group by字段上我是加了索引的,也用到了。
三、優化
說實話,我是不知道該怎麼優化的,這玩意還能怎麼優化啊!先說下,下面的思路都是沒用的。
思路一:
後面應該加上 order by null;避免無用排序,但其實對結果耗時影響不大,還是很慢。
思路二:
where條件太複雜,沒索引,導致查詢慢,但我給where條件的所有字段加上了組合索引,也還是沒用
思路三:
既然group by慢,換distinct試試??(這裡就是本篇博客裡說的神奇的地方了)
臥槽???!!!這是什麼情況,瞬間這麼快了??!!!
雖然知道group by和distinct有很小的性能差距,但是真沒想到,差距居然這麼大!!!大發現啊!!
四、你以為這就結束了嗎
我是真的希望就這麼結束了,那這個問題就很簡單的解決了,順便還自以為是的發現了一個新知識。
但是!
這個bug轉給測試後,測試一測,居然還是30多秒!?這是什麼情況!!???
我當然是不信了,去測試電腦上執行sql,還真是30多秒。。。
我又回我的電腦上,連接同一個數據庫,一執行sql,0.8秒!?
什麼情況,同一個庫,同一個sql,怎麼在兩臺電腦執行的差距這麼大!
後來直接在服務器上執行:
醉了,居然還是30多秒。。。。
那看來就是我電腦的問題了。
後來我用多個同事的電腦實驗,最後得出的結論是:
是因為我用的SQLyog!
哎,現在發現了,只有用sqlyog執行這個“優化後”的sql會是0.8秒,在navcat和服務器上直接執行,都是30多秒。
那就是sqlyog的問題了,現在也不清楚sqlyog是不是做什麼優化了,這個慢查詢的問題還在解決中(我覺得問題可能是出在mysql自身的參數上吧)。
這裡只是記錄下這個坑,sqlyog執行sql速度,和服務器執行sql速度,在有的sql中差異巨大,並不可靠。
五、後續(還未解決)
感謝大家在評論裡出謀劃策,我來回復下問題進展:
1.所謂的sqlyog查詢快,命令行查詢慢的現象,已經找到原因了。是因為sqlyog會在查詢語句後默認加上limit 1000,所以導致很快。這個問題不再糾結。
2.我已經試驗過的方法(都沒有用):
①給app_account字段加索引。
②給sql語句後面加order by null。
③調整where條件裡字段的查詢順序,有索引的放前面。
④給所有where條件的字段加組合索引。
⑤用子查詢的方式,先查where條件裡的內容,再去重。
測試環境和現網環境數據還是有點不一樣的,我貼一張現網執行sql的圖(1分鐘。。。):
六、最終解決方案
感謝評論裡42樓的@言楓大佬!
經過你的提醒,我確實發現,explain執行計劃裡,索引好像並沒有用到我創建的idx_end_time。
然後果斷在現網試了下,強制指定使用idx_end_time索引,結果只要0.19秒!
至此問題解決,其實同事昨天也在懷疑,是不是這個表索引建的太多了,導致用的不對,原本用的是idx_org_id和idx_mvno_id。
現在強制指定idx_end_time就ok了!
最後再對比下改前後的執行計劃:
改之前(查詢要1分鐘左右):
改之後(查詢只要幾百毫秒):
另:想了解更多數據庫的知識與用法,歡迎關注墨天輪“數據庫專欄” (地址:https://www.modb.pro/db,點擊左下角的“閱讀原文”或者掃描下方二維碼可直達),此外,墨天輪開放了很多數據庫專欄,如 GaussDB、PolarDB、OceanBase、TDSQL、GoldenDB 等眾多數據庫專欄,歡迎關注學習!
閱讀更多 數據和雲智能 的文章