MySQL巧用sum、case和when優化統計查詢

Posted on by

最近在公司做項目，涉及到開發統計報表相關的任務，由於數據量相對較多，之前寫的查詢語句查詢五十萬條數據大概需要十秒左右的樣子，後來經過老大的指點利用sum,case…when…重寫SQL性能一下子提高到一秒鐘就解決瞭。這裡為瞭簡潔明瞭的闡述問題和解決的方法，我簡化一下需求模型。

現在數據庫有一張訂單表（經過簡化的中間表），表結構如下：

CREATE TABLE `statistic_order` (
 `oid` bigint(20) NOT NULL,
 `o_source` varchar(25) DEFAULT NULL COMMENT '來源編號',
 `o_actno` varchar(30) DEFAULT NULL COMMENT '活動編號',
 `o_actname` varchar(100) DEFAULT NULL COMMENT '參與活動名稱',
 `o_n_channel` int(2) DEFAULT NULL COMMENT '商城平臺',
 `o_clue` varchar(25) DEFAULT NULL COMMENT '線索分類',
 `o_star_level` varchar(25) DEFAULT NULL COMMENT '訂單星級',
 `o_saledep` varchar(30) DEFAULT NULL COMMENT '營銷部',
 `o_style` varchar(30) DEFAULT NULL COMMENT '車型',
 `o_status` int(2) DEFAULT NULL COMMENT '訂單狀態',
 `syctime_day` varchar(15) DEFAULT NULL COMMENT '按天格式化日期',
 PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

項目需求是這樣的:

統計某段時間范圍內每天的來源編號數量，其中來源編號對應數據表中的o_source字段，字段值可能為CDE,SDE,PDE,CSE,SSE。

來源分類隨時間流動

一開始寫瞭這樣一段SQL：

select S.syctime_day,
 (select count(*) from statistic_order SS where SS.syctime_day = S.syctime_day and SS.o_source = 'CDE') as 'CDE',
 (select count(*) from statistic_order SS where SS.syctime_day = S.syctime_day and SS.o_source = 'CDE') as 'SDE',
 (select count(*) from statistic_order SS where SS.syctime_day = S.syctime_day and SS.o_source = 'CDE') as 'PDE',
 (select count(*) from statistic_order SS where SS.syctime_day = S.syctime_day and SS.o_source = 'CDE') as 'CSE',
 (select count(*) from statistic_order SS where SS.syctime_day = S.syctime_day and SS.o_source = 'CDE') as 'SSE'
 from statistic_order S where S.syctime_day > '2016-05-01' and S.syctime_day < '2016-08-01' 
 GROUP BY S.syctime_day order by S.syctime_day asc;

這種寫法采用瞭子查詢的方式，在沒有加索引的情況下，55萬條數據執行這句SQL，在workbench下等待瞭將近十分鐘，最後報瞭一個連接中斷，通過explain解釋器可以看到SQL的執行計劃如下：

每一個查詢都進行瞭全表掃描，五個子查詢DEPENDENT SUBQUERY說明依賴於外部查詢，這種查詢機制是先進行外部查詢，查詢出group by後的日期結果，然後子查詢分別查詢對應的日期中CDE，SDE等的數量，其效率可想而知。

在o_source和syctime_day上加上索引之後，效率提高瞭很多，大概五秒鐘就查詢出瞭結果：

查看執行計劃發現掃描的行數減少瞭很多，不再進行全表掃描瞭：

這當然還不夠快，如果當數據量達到百萬級別的話，查詢速度肯定是不能容忍的。一直在想有沒有一種辦法，能否直接遍歷一次就查詢出所有的結果，類似於遍歷java中的list集合，遇到某個條件就計數一次，這樣進行一次全表掃描就可以查詢出結果集，結果索引，效率應該會很高。在老大的指引下，利用sum聚合函數，加上case…when…then…這種“陌生”的用法，有效的解決瞭這個問題。
具體SQL如下：

 select S.syctime_day,
 sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE',
 sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE',
 sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE',
 sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE',
 sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE'
 from statistic_order S where S.syctime_day > '2015-05-01' and S.syctime_day < '2016-08-01' 
 GROUP BY S.syctime_day order by S.syctime_day asc;

關於MySQL中case…when…then的用法就不做過多的解釋瞭，這條SQL很容易理解，先對一條一條記錄進行遍歷，group by對日期進行瞭分類，sum聚合函數對某個日期的值進行求和，重點就在於case…when…then對sum的求和巧妙的加入瞭條件，當o_source = ‘CDE’的時候，計數為1，否則為0；當o_source=’SDE’的時候……

這條語句的執行隻花瞭一秒多，對於五十多萬的數據進行這樣一個維度的統計還是比較理想的。

通過執行計劃發現，雖然掃描的行數變多瞭，但是隻進行瞭一次全表掃描，而且是SIMPLE簡單查詢，所以執行效率自然就高瞭：

針對這個問題，如果大傢有更好的方案或思路，歡迎留言

總結

到此這篇關於MySQL巧用sum、case和when優化統計查詢的文章就介紹到這瞭,更多相關MySQL優化統計查詢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：