|
MySQL確實(shí)坑爹。分組后的結(jié)果是無法排序的。 現(xiàn)在有這樣的一種需求。假設(shè)表結(jié)構(gòu)為 CREATE TABLE `WCC_PM_Promotion_Product` ( `PPPID` int(12) NOT NULL AUTO_INCREMENT, `BRID` int(8) DEFAULT NULL , `CTID` int(12) DEFAULT NULL , `Price` decimal(16,2) DEFAULT NULL, `PKID` int(11) DEFAULT NULL, PRIMARY KEY (`PPPID`), KEY `index_brid` (`BRID`), KEY `index_ctid` (`CTID`), KEY `IDX_PKID` (`PKID`) USING BTREE, ) ENGINE=InnoDB AUTO_INCREMENT=1831273 DEFAULT CHARSET=utf8; 該表的數(shù)據(jù)量為70W+條。 現(xiàn)在有這樣的某PKID的記錄（PKID為變量，存在并發(fā)），需要取出在該條PKID和CTID為1時的BRID和PRICE。同時需要將BRID相同的條目中，最小的PRICE行取出來。 為了完成這樣的需求，很快就寫出了這樣的語句： SELECT WCC_PM_Promotion_Product.* FROM WCC_PM_Promotion_Product WHERE (WCC_PM_Promotion_Product.PKID='1342848') AND (WCC_PM_Promotion_Product.CTID=1) GROUP BY WCC_PM_Promotion_Product.BRID ORDER BY WCC_PM_Promotion_Product.Price desc; 這樣的語句可以將取出的條目分組。但是在分組后執(zhí)行ORDER BY 語句時并沒有對分組后的結(jié)果進(jìn)行排序。取出的不是我們想要的結(jié)果：  BRID為3的記錄中，并沒有取到最小值。也就是說GROUP BY語句并沒有對結(jié)果集進(jìn)行排序。 下面是沒有進(jìn)行分組及排序的結(jié)果集：  可以看出BRID取到的并不是最小PRICE值。 從MYSQL的手冊中可以知道，MYSQL的GROUP BY并不支持排序。那么怎么實(shí)現(xiàn)這一功能呢。 可以使用子查詢來實(shí)現(xiàn)，于是上面的SQL語句被改寫為: SELECT t.* FROM (SELECT * FROM WCC_PM_Promotion_Product WHERE (WCC_PM_Promotion_Product.PKID='1342848') AND (WCC_PM_Promotion_Product.CTID=1) ORDER BY WCC_PM_Promotion_Product.Price) as t GROUP BY t.BRID; 執(zhí)行結(jié)果為：  查詢執(zhí)行了0.02s?？瓷先ッ菜撇诲e，性能OK同時還完成了需求。 但是一個只輸出3行的查詢，它的子查詢也只返回一個24行的結(jié)果集。為什么還會需要0.02s的時間？ 于是想到了查詢可能存在性能問題。先看一下子查詢的執(zhí)行計劃：  發(fā)現(xiàn)執(zhí)行計劃中只掃描了107行，但是使用索引的同時還出現(xiàn)了Using filesort（索引外部排序）。出現(xiàn)這樣的情況的可能原因是ORDER BY 中的排序列沒有索引。 于是， 處理1.在PRICE列上建立單獨(dú)的索引。執(zhí)行上述執(zhí)行計劃，發(fā)現(xiàn)結(jié)果同上面的結(jié)果完全一致。也就是說子查詢并沒有使用IDX_PRICE這個索引。甚至沒有將其作為備選鍵。 處理2.建立聯(lián)合索引IDX_PRICE_PKID。執(zhí)行計劃顯示子查詢同樣沒有使用該索引。FORCE INDEX(IDX_PRICE_PKID)語句強(qiáng)制使用該索引會引發(fā)更糟糕的性能，如下圖，70W行數(shù)據(jù)被掃描了68W行。  這一結(jié)果想到聯(lián)合索引的使用還和索引中列的順序有關(guān)。 于是， 處理3.更換了聯(lián)合索引中的列的位置，IDX_PKID_PRICE。繼續(xù)強(qiáng)制使用該索引，執(zhí)行后發(fā)現(xiàn)掃描行數(shù)與單獨(dú)使用IDX_PKID索引一致，但是沒有了使用外部索引排序的問題（Using filesort）。如下圖：  下面我們執(zhí)行以下原查詢語句在加入該索引后的執(zhí)行計劃。如下圖：  子查詢的結(jié)果正常了。但是外查詢中出現(xiàn)了Using temporary和Using filesort。同時出現(xiàn)兩大性能瓶頸。特別是使用臨時表，當(dāng)數(shù)據(jù)量很大的時候會加大CPU的負(fù)載，形成大量的鎖表，導(dǎo)致無法插入修改及查詢數(shù)據(jù)。然而我們的需求導(dǎo)致無法完全解決使用臨時表的問題，因此應(yīng)該盡量的減少內(nèi)查詢的消耗。讓其盡量的少掃描表中的行。 分析可知，只有24行的子查詢卻掃描了107行也不是很正常。所以，在索引IDX_PKID_PRICE的基礎(chǔ)上，再加入WHERE 語句中出現(xiàn)的CTID，將原本應(yīng)該使用的CTID上的索引起作用。  終于，子查詢的掃描行被控制在了結(jié)果集內(nèi)。這樣原查詢即便使用了臨時表也不會形成太大了性能開銷。如圖，原本需要0.02s的查詢被限制在了0.00s內(nèi)。雖然沒有看profile，但是相信不會有問題的（心態(tài)就是這么好~哈哈哈~）。  結(jié)論： SQL語句的調(diào)優(yōu)，首先要分析任務(wù)計劃。在出現(xiàn)USING FILESORT和USING TEMPORARY時，應(yīng)考慮一下是不是ORDER BY語句中的字段沒有出現(xiàn)在SELECT從句，從而導(dǎo)致了優(yōu)化器使用了外部排序。 當(dāng)查詢掃描的表行數(shù)太多時，應(yīng)考慮一下是否因?yàn)椴樵冏侄紊蠜]有索引。需要時要建立聯(lián)合索引。但是聯(lián)合索引在創(chuàng)建時要考慮使用索引的順序。 By Pottieva.Zhang 2013.11.13 |
|
|
