快捷導(dǎo)航

MySQL巧用sum、case和when優(yōu)化統(tǒng)計(jì)查詢

更新時(shí)間：2021年03月17日 16:59:16 作者：飛鍋鍋

這篇文章主要給大家介紹了關(guān)于MySQL巧用sum、case和when優(yōu)化統(tǒng)計(jì)查詢的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

最近在公司做項(xiàng)目，涉及到開發(fā)統(tǒng)計(jì)報(bào)表相關(guān)的任務(wù)，由于數(shù)據(jù)量相對較多，之前寫的查詢語句查詢五十萬條數(shù)據(jù)大概需要十秒左右的樣子，后來經(jīng)過老大的指點(diǎn)利用sum,case...when...重寫SQL性能一下子提高到一秒鐘就解決了。這里為了簡潔明了的闡述問題和解決的方法，我簡化一下需求模型。

現(xiàn)在數(shù)據(jù)庫有一張訂單表（經(jīng)過簡化的中間表），表結(jié)構(gòu)如下：

CREATE TABLE `statistic_order` (
 `oid` bigint(20) NOT NULL,
 `o_source` varchar(25) DEFAULT NULL COMMENT '來源編號',
 `o_actno` varchar(30) DEFAULT NULL COMMENT '活動(dòng)編號',
 `o_actname` varchar(100) DEFAULT NULL COMMENT '參與活動(dòng)名稱',
 `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 '訂單狀態(tài)',
 `syctime_day` varchar(15) DEFAULT NULL COMMENT '按天格式化日期',
 PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

項(xiàng)目需求是這樣的:

統(tǒng)計(jì)某段時(shí)間范圍內(nèi)每天的來源編號數(shù)量，其中來源編號對應(yīng)數(shù)據(jù)表中的o_source字段，字段值可能為CDE,SDE,PDE,CSE,SSE。

來源分類隨時(shí)間流動(dòng)

一開始寫了這樣一段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萬條數(shù)據(jù)執(zhí)行這句SQL，在workbench下等待了將近十分鐘，最后報(bào)了一個(gè)連接中斷，通過explain解釋器可以看到SQL的執(zhí)行計(jì)劃如下：

每一個(gè)查詢都進(jìn)行了全表掃描，五個(gè)子查詢DEPENDENT SUBQUERY說明依賴于外部查詢，這種查詢機(jī)制是先進(jìn)行外部查詢，查詢出group by后的日期結(jié)果，然后子查詢分別查詢對應(yīng)的日期中CDE，SDE等的數(shù)量，其效率可想而知。

在o_source和syctime_day上加上索引之后，效率提高了很多，大概五秒鐘就查詢出了結(jié)果：

查看執(zhí)行計(jì)劃發(fā)現(xiàn)掃描的行數(shù)減少了很多，不再進(jìn)行全表掃描了：

這當(dāng)然還不夠快，如果當(dāng)數(shù)據(jù)量達(dá)到百萬級別的話，查詢速度肯定是不能容忍的。一直在想有沒有一種辦法，能否直接遍歷一次就查詢出所有的結(jié)果，類似于遍歷java中的list集合，遇到某個(gè)條件就計(jì)數(shù)一次，這樣進(jìn)行一次全表掃描就可以查詢出結(jié)果集，結(jié)果索引，效率應(yīng)該會很高。在老大的指引下，利用sum聚合函數(shù)，加上case...when...then...這種“陌生”的用法，有效的解決了這個(gè)問題。
具體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;

關(guān)于MySQL中case...when...then的用法就不做過多的解釋了，這條SQL很容易理解，先對一條一條記錄進(jìn)行遍歷，group by對日期進(jìn)行了分類，sum聚合函數(shù)對某個(gè)日期的值進(jìn)行求和，重點(diǎn)就在于case...when...then對sum的求和巧妙的加入了條件，當(dāng)o_source = 'CDE'的時(shí)候，計(jì)數(shù)為1，否則為0；當(dāng)o_source='SDE'的時(shí)候......

這條語句的執(zhí)行只花了一秒多，對于五十多萬的數(shù)據(jù)進(jìn)行這樣一個(gè)維度的統(tǒng)計(jì)還是比較理想的。

通過執(zhí)行計(jì)劃發(fā)現(xiàn)，雖然掃描的行數(shù)變多了，但是只進(jìn)行了一次全表掃描，而且是SIMPLE簡單查詢，所以執(zhí)行效率自然就高了：

針對這個(gè)問題，如果大家有更好的方案或思路，歡迎留言

總結(jié)

到此這篇關(guān)于MySQL巧用sum、case和when優(yōu)化統(tǒng)計(jì)查詢的文章就介紹到這了,更多相關(guān)MySQL優(yōu)化統(tǒng)計(jì)查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: