MySQL中怎么解決分類排名問題

本篇文章為大家展示了MySQL中怎么解決分類排名問題，內(nèi)容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

01 需求介紹

考慮MySQL中的一個經(jīng)典應(yīng)用：給定一個學(xué)生考試成績表，要實現(xiàn)對學(xué)生按課程依成績高低進行排序。為了簡單起見，僅給定成績表，而不考慮可能關(guān)聯(lián)的學(xué)生信息表、課程信息表和教師信息表等，且成績表中僅創(chuàng)建3個關(guān)鍵字段：

•  cid：課程id，int型，共5門課程

•  sid：學(xué)生id，int型，共8872名學(xué)生

•  score：成績，int型，共22366條成績信息，分布于10-100之間

為了逐步分析，初始狀態(tài)不添加主鍵，也不建立任何索引。

02 子查詢

實現(xiàn)這一需求的最直接想法是通過子查詢，對每個分?jǐn)?shù)進行統(tǒng)計：統(tǒng)計表中有多少分?jǐn)?shù)比其更高，那么該分?jǐn)?shù)的排名就是更高分?jǐn)?shù)計數(shù)+1。如果要區(qū)分課程排名，那么統(tǒng)計表時只需增加一個限制課程id相等的約束條件即可。

1SELECT  2    a.*, ( SELECT COUNT(score)+1 FROM scores WHERE cid = a.cid AND score > a.score ) AS 'rank'   3FROM  4    scores a   5ORDER BY  6    a.cid,  a.score DESC;

需注意的是，在子查詢約束條件中要求score > a.score以及COUNT()+1，表示統(tǒng)計的是比該成績更高的計數(shù)+1，例如對于90、80、80、70……這樣的分?jǐn)?shù)得到排名結(jié)果是1，2，2，4……；如果選用score >= a.score和COUNT()作為排名條件，那么得到結(jié)果是1，3，3，4……

在未添加任何索引的情況下，這個查詢速度是相當(dāng)慢的，耗時120s。

未添加索引時的子查詢執(zhí)行計劃

優(yōu)化查詢的第一想法當(dāng)然是添加索引：雖然外層查詢未用到任何where約束條件，但子查詢中用到了cid和score兩個字段判斷，于是考慮添加索引：

1CREATE INDEX idc ON scores(cid);  2CREATE INDEX ids ON scores(score);

增加索引后，查詢耗時96s，雖然有提升，但仍難堪重任。解釋查詢計劃，發(fā)現(xiàn)雖然速度仍然很慢，但兩個索引確實都得到了應(yīng)用：

添加獨立索引后的子查詢執(zhí)行計劃

既然獨立索引無法明顯提升效率，考慮子查詢中where條件不是獨立字段的常值約束，而是依賴于外層循環(huán)取值的聯(lián)合約束，那么再考慮添加一個聯(lián)合索引：

CREATE INDEX idcs ON scores(cid, score);

查詢速度確實是更快了，實際用時24s。解釋查詢計劃，發(fā)現(xiàn)既用到了獨立索引，也用到了聯(lián)合索引。但不得不說，24s的響應(yīng)時間對于要求0.5s解決戰(zhàn)斗的即時任務(wù)來說，仍然是不夠的。

添加聯(lián)合索引后的子查詢執(zhí)行計劃

只能另辟蹊徑。

03 自連接

一般來說，對于速度較慢的子查詢?nèi)蝿?wù)，換做連接查詢(join)可以得到明顯提升。

具體到分課程排名這一具體需求，我們考慮對scores表進行自連接，其中連接條件為課程相等且a表score值小于b表score值，從而通過統(tǒng)計滿足連接條件的記錄數(shù)即可得到排名信息：

1SELECT   2    a.*, COUNT(b.score)+1 AS 'rank'  3FROM   4    scores a LEFT JOIN scores b ON (a.cid = b.cid AND a.score < b.score)  5GROUP BY   6    a.cid, a.sid  7ORDER BY   8    a.cid, COUNT(b.score)

需注意的是：連接方式要選用left join，以便將a表中的所有分?jǐn)?shù)信息都顯示出來；若是用join，則最高分因為不存在滿足連接的記錄而被漏掉。至于連接條件中score值和count()的關(guān)系類似于子查詢中的情況。

應(yīng)用自連接，在不創(chuàng)建任何索引的情況下查詢速度與子查詢情況差不多，耗時73s；在添加有效索引后，查詢時間27s，效率有所提升，但與查詢方案效率相當(dāng)。

未添加索引時的自連接執(zhí)行計劃

添加有效索引后的自連接執(zhí)行計劃

顯然，應(yīng)用自連接替代子查詢的方案并沒有顯著提升查詢效率，即使是在添加了有效索引的基礎(chǔ)上。

進一步分析數(shù)據(jù)表發(fā)現(xiàn)，實際上速度慢并不能否認(rèn)索引在改善查詢效率方面的能力，而僅僅是因為添加索引的字段取值較少的原因：cid字段僅有5個取值——當(dāng)字段取值個數(shù)較少時，添加索引很難見效。

例如，如果換一個需求，改為按學(xué)生區(qū)分各門課程的成績排名（sid取值數(shù)量很大），則應(yīng)用索引即可有效改善查詢效率。按學(xué)生查詢成績排名SQL語句：

1SELECT   2    a.*, count(b.score)+1 AS 'rank'  3FROM   4    scores a LEFT JOIN scores b ON (a.sid = b.sid AND a.score < b.score)  5GROUP BY   6    a.sid, a.cid  7ORDER BY   8    a.sid, count(b.score)

對于如上查詢，在未添加索引時，查詢時間34s；添加有效索引后耗時僅為0.184s，添加索引的提升效果非常明顯。

雖然這一論斷捍衛(wèi)了索引的地位作用，但如果我們的需求就是按課程進行排名呢？顯然，無論是子查詢還是自連接方案，都難以滿足我們的實時查詢需求。

只得再覓他法。

04 自定義變量

實際上，上述兩種方案之所以速度較慢，是因為都作用在兩個表上查詢，如果再考慮外層的order by，那么執(zhí)行時間復(fù)雜度粗略估計在O(n3)量級。此時，我們考慮應(yīng)用自定義變量實現(xiàn)更低復(fù)雜度的查詢實現(xiàn)。

應(yīng)用自定義變量，我們不僅可以提高速度，而且還能實現(xiàn)"各種"排名：例如對于90、80、80、70、60這樣一組成績，可能有3種排名需求，一種是連續(xù)排名，同分時名次也繼續(xù)增加：1、2、3、4、5；第二種是同分同名，下一排名不跳級，即1、2、2、3、4；第三種是同分同名，下一排名跳級，即1、2、2、4、5。這三種需求應(yīng)用自定義變量進行排序都可以輕松搞定（具體變量含義和思路后續(xù)給出）：

•  連續(xù)排名： 

1SELECT   2    sid, cid, @curRank:=@curRank+1 AS 'rank'  3FROM   4    scores, (SELECT @curRank:=0) tmp  5ORDER BY   6    score DESC

•  同分同名，不跳級： 

1SELECT   2    sid, cid, @curRank:=IF(score=@preScore, @curRank, @curRank+1) AS 'rank',   3    @preScore:=score  4FROM   5    scores, (SELECT @curRank:=0, @preScore:=NULL) tmp  6ORDER BY  7    score

•  同分同名，跳級：

1SELECT 

2    sid, cid, @curRank := IF(score=@preScore, @curRank, @totalRank) AS 'rank',  3    @preScore := score,  4    @totalRank := @totalRank+1  5FROM   6    scores, (SELECT @curRank:=1, @totalRank:=1, @preScore:=NULL) tmp  7ORDER BY   8    score

以上SQL語句是在不進行任何分類條件下的排名：通過自定義變量（MySQL定義變量用@作為引導(dǎo)符，并用:=表示賦值）記錄前一個排名、前一個分?jǐn)?shù)值、當(dāng)前的總排名，分別實現(xiàn)三種需求。

那么，若要實現(xiàn)分類排名呢，比如說區(qū)分各課程進行排名？那么只需再增加一個自定義變量，用于記錄前一個課程cid即可：

•  若當(dāng)前分類信息與前一課程cid相同，則繼續(xù)當(dāng)前的排名處理（根據(jù)具體需求選擇三種排名中的一種）；

•  若當(dāng)前分類與前一課程cid不同，則排名信息初始化，從1重新開始。

以相對復(fù)雜的“同分同名、跳級”為例，此時SQL語句為：

1SELECT  sid, cid,   2    @totalRank := IF(cid=@preCid, @totalRank+1, 1),  3    @curRank := IF(cid=@preCid, IF(score=@preScore, @curRank, @totalRank), 1) AS 'rank',  4    @preScore := score,  5    @preCid := cid  6FROM   7    scores, (SELECT @curRank:=0, @totalRank:=0, @preScore:=NULL, @preCid:=NULL) tmp  8ORDER BY   9    cid, score DESC  8    score

對各變量含義解釋如下：

•  @totalRank用于記錄當(dāng)前分類中的總排名，初始化為0

•  @curRank用于記錄當(dāng)前分類中的當(dāng)前排名，初始化為0

•  @preScore用于記錄上一個分?jǐn)?shù)情況，初始化為NULL

•  @preCid用于記錄上一個課程cid，初始化為NULL

執(zhí)行流程及條件判斷為：

•  若當(dāng)前cid與前一cid相同，表示是同一個分類，排名在之前排名基礎(chǔ)增加，具體來說：

•   總排名每次+1

•   若當(dāng)前分?jǐn)?shù)與前一分?jǐn)?shù)相同，則當(dāng)前排名不變；否則跳級到總排名

•  若當(dāng)前cid與前一cid不同，表示開始新的課程排名，總排名和當(dāng)前排名均初始化為1

基于以上SQL語句，執(zhí)行相同的任務(wù)，耗時僅需0.09s，其效率相當(dāng)于子查詢最快速度24s的266倍，相當(dāng)于自連接最快速度27s的300倍，其查詢效率可見一斑。

另外，由于上述SQL語句不存在where約束條件，所以與是否建立索引無關(guān)。

05 MySQL8.0窗口函數(shù)

MySQL8.0版本的一個重要更新就是增加了窗口函數(shù)，使得前面的分類排名需求變得異常簡單。

與前述類似，不同的排名需求有不同的窗口函數(shù)，而且三個函數(shù)的命名也非常形象直觀：

•  連續(xù)排名：row_number()，排名即行號

•  同分同名，不跳級：dense_rank()，致密排名，類似1、2、2、3……這種，因為不跳級，所以比較"致密"

•  同分同名，跳級：rank()，普通排名，類似1、2、2、4……這種

其中，每個窗口函數(shù)函數(shù)又必須與over()函數(shù)配套使用，over()函數(shù)中的參數(shù)主要包括partion by 和order by：

•  order by：與常規(guī)SQL語句中order by一致，表示按照某一字段進行排序，也區(qū)分ASC還是DESC

•  partion by：用作分類依據(jù)，缺省時表示不分類，對所有記錄排序；若指定某一字段，則表示在該字段間進行獨立排序，跨字段重新開始

仍以之前的分課程排名需求為例，其SQL語句為：

1SELECT   2    *, RANK() OVER(PARTITION BY cid ORDER BY score DESC) AS 'rank'  3FROM   4    scores;

查詢耗時0.066s，比自定義變量實現(xiàn)的排名速度略高一點。同時，該排名方式也與索引無關(guān)。

將RANK()替換成另外兩個窗口函數(shù)，可實現(xiàn)其他相應(yīng)需求。

上述內(nèi)容就是MySQL中怎么解決分類排名問題，你們學(xué)到知識或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識儲備，歡迎關(guān)注億速云行業(yè)資訊頻道。