mysql聚合統計數據查詢緩慢的優化方法

寫在前面

在我們日常操作數據庫的時候,比如訂單表、訪問記錄表、商品表的時候。

經常會處理計算數據列總和、數據行數等統計問題。

隨著業務發展,這些表會越來越大,如果處理不當,查詢統計的速度也會越來越慢,直到業務無法再容忍。

所以,我們需要先瞭解、思考這些場景知識點,在設計之初,便預留一些優化空間支撐業務發展。

sql聚合函數

在mysql等數據中,都會支持聚合函數,方便我們計算數據。

常見的有以下方法

取平均值 AVG()
求和 SUM()
最大值 MAX()
最小值 MIN()
行數 COUNT()

演示幾個簡單使用的sql語句:

查詢u_id為100的訂單總數

select count(id) from orders where u_id = 100;

查詢u_id為100的訂單消費總和

select sum(order_amount) from orders where u_id = 100;

查詢銷量最高的商品

select max(sell_num) from goods

統計7月份的訂單數量、金額總和

select count(id) as count, sum(order_amount) as total_amount 
from orders where order_date between 20190701 and 20190731 and is_pay = 1

如果此時,訂單表的總數是1億條。並且此條sql運行很慢,我們應該如何排查優化?

有的同學會說瞭:行數多,在日期字段上加 索引,這樣子篩選就很快瞭。

總數1億條,假設7月份的訂單有1000萬條,加瞭索引的時候,篩選速度自然會提升不少。但是此時我們的問題真的解決瞭嗎?

在這種聚合函數中,結果需要 遍歷每一條 數據來計算,比如我們統計訂單總和,就需要每一行都讀取訂單金額,然後加起來。

也就是說在這條統計sql中,需要先從1億數據中篩選1000萬條數據,然後再遍歷這些數據來計算。 此時就會非常慢瞭。

增加索引並不能解決聚合函數統計慢的問題

優化聚合統計的方案

提前預算

建立 統計數據表,以日期區分,如:20190801一天,銷售瞭多少訂單、金額等等數據。
當訂單產生(支付完成後 可統計數據)時,便在統計數據表中對應的日期增加金額、數量。

需要註意的是,如果有退款等場景會影響減少數據,記得也相應地做操作處理

當我們需要統計8月份的數據時候,則隻需要遍歷計算這一個月的三十來行數據。

定時落地

我們可以使用easyswoole、計劃任務等。來定時(比如每20分鐘一次)計算總和,然後更新到 統計數據表 中。

優點:做的處理比較少,也無需改動退款操作等api,隻需要依賴 原訂單 表的數據,定時統計、刷新統計數據。

需要註意的是,根據不同的訂單熱度,來設置不同的落地頻率,比如 一周內的數據變化幾率比較大,可能20分鐘落地。而一年前的數據則變化幾率很小,可以選擇某天同步一次,甚至確保不會變動時,則不再刷新。

總結

索引並不能解決統計聚合數據慢的sql語句問題

聚合函數謹慎用 最好不用,因為我們無法預算以後的數據量需要掃描多少行數據來計算

優化方案離不開統計表,都需要按一定的周期儲存運算好的統計數據

到此這篇關於mysql聚合統計數據查詢緩慢的文章就介紹到這瞭,更多相關mysql聚合統計數據查詢緩慢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!