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

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

01 需求介紹

考慮MySQL中的一個經典應用：給定一個學生考試成績表，要實現對學生按課程依成績高低進行排序。為了簡單起見，僅給定成績表，而不考慮可能關聯的學生信息表、課程信息表和教師信息表等，且成績表中僅創建3個關鍵字段：

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

•  sid：學生id，int型，共8872名學生

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

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

02 子查詢

實現這一需求的最直接想法是通過子查詢，對每個分數進行統計：統計表中有多少分數比其更高，那么該分數的排名就是更高分數計數+1。如果要區分課程排名，那么統計表時只需增加一個限制課程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，表示統計的是比該成績更高的計數+1，例如對于90、80、80、70……這樣的分數得到排名結果是1，2，2，4……；如果選用score >= a.score和COUNT()作為排名條件，那么得到結果是1，3，3，4……

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

未添加索引時的子查詢執行計劃

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

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

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

添加獨立索引后的子查詢執行計劃

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

CREATE INDEX idcs ON scores(cid, score);

查詢速度確實是更快了，實際用時24s。解釋查詢計劃，發現既用到了獨立索引，也用到了聯合索引。但不得不說，24s的響應時間對于要求0.5s解決戰斗的即時任務來說，仍然是不夠的。

添加聯合索引后的子查詢執行計劃

只能另辟蹊徑。

03 自連接

一般來說，對于速度較慢的子查詢任務，換做連接查詢(join)可以得到明顯提升。

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

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表中的所有分數信息都顯示出來；若是用join，則最高分因為不存在滿足連接的記錄而被漏掉。至于連接條件中score值和count()的關系類似于子查詢中的情況。

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

未添加索引時的自連接執行計劃

添加有效索引后的自連接執行計劃

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

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

例如，如果換一個需求，改為按學生區分各門課程的成績排名（sid取值數量很大），則應用索引即可有效改善查詢效率。按學生查詢成績排名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，添加索引的提升效果非常明顯。

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

只得再覓他法。

04 自定義變量

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

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

•  連續排名： 

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定義變量用@作為引導符，并用:=表示賦值）記錄前一個排名、前一個分數值、當前的總排名，分別實現三種需求。

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

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

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

以相對復雜的“同分同名、跳級”為例，此時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用于記錄當前分類中的總排名，初始化為0

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

•  @preScore用于記錄上一個分數情況，初始化為NULL

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

執行流程及條件判斷為：

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

•   總排名每次+1

•   若當前分數與前一分數相同，則當前排名不變；否則跳級到總排名

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

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

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

05 MySQL8.0窗口函數

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

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

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

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

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

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

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

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

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

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

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

將RANK()替換成另外兩個窗口函數，可實現其他相應需求。

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