01 需求介紹

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

•  cid：課程id，int型，共5門課程
•  sid：學(xué)生id，int型，共8872名學(xué)生
•  score：成績，int型，共22366條成績信息，分布于10-100之間

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

02 子查詢

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

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

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

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

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

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

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

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

CREATE INDEX idcs ON scores(cid, score); 

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

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

只能另辟蹊徑。

03 自連接

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

具體到分課程排名這一具體需求，我們考慮對(duì)scores表進(jìn)行自連接，其中連接條件為課程相等且a表score值小于b表score值，從而通過統(tǒng)計(jì)滿足連接條件的記錄數(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，則最高分因?yàn)椴淮嬖跐M足連接的記錄而被漏掉。至于連接條件中score值和count()的關(guān)系類似于子查詢中的情況。

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

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

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

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

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

例如，如果換一個(gè)需求，改為按學(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) 

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

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

只得再覓他法。

04 自定義變量

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

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

•  連續(xù)排名： 

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

•  同分同名，不跳級(jí)： 

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 

•  同分同名，跳級(jí)：

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

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

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

以相對(duì)復(fù)雜的“同分同名、跳級(jí)”為例，此時(shí)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 

對(duì)各變量含義解釋如下：

•  @totalRank用于記錄當(dāng)前分類中的總排名，初始化為0
•  @curRank用于記錄當(dāng)前分類中的當(dāng)前排名，初始化為0
•  @preScore用于記錄上一個(gè)分?jǐn)?shù)情況，初始化為NULL
•  @preCid用于記錄上一個(gè)課程cid，初始化為NULL

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

•  若當(dāng)前cid與前一cid相同，表示是同一個(gè)分類，排名在之前排名基礎(chǔ)增加，具體來說：
  •   總排名每次+1
  •   若當(dāng)前分?jǐn)?shù)與前一分?jǐn)?shù)相同，則當(dāng)前排名不變；否則跳級(jí)到總排名
•  若當(dāng)前cid與前一cid不同，表示開始新的課程排名，總排名和當(dāng)前排名均初始化為1

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

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

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

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

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

•  連續(xù)排名：row_number()，排名即行號(hào)
•  同分同名，不跳級(jí)：dense_rank()，致密排名，類似1、2、2、3……這種，因?yàn)椴惶?jí)，所以比較"致密"
•  同分同名，跳級(jí)：rank()，普通排名，類似1、2、2、4……這種

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

•  order by：與常規(guī)SQL語句中order by一致，表示按照某一字段進(jìn)行排序，也區(qū)分ASC還是DESC
•  partion by：用作分類依據(jù)，缺省時(shí)表示不分類，對(duì)所有記錄排序；若指定某一字段，則表示在該字段間進(jìn)行獨(dú)立排序，跨字段重新開始

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

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

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

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

06 總結(jié)

本文以對(duì)給定成績表進(jìn)行分課程排名為例，講述了4種實(shí)現(xiàn)方案：

•  子查詢方案，通過嵌套count()函數(shù)實(shí)現(xiàn)，效率較低，創(chuàng)建有效索引可提升一定效率，僅支持"同分同名、跳級(jí)"排名需求
•  自連接方案，與子查詢類似，通過自連接和count()函數(shù)實(shí)現(xiàn)，效率較低，依賴于索引，也僅支持"同分同名、跳級(jí)"排名需求
•  自定義變量方案，通過定義變量實(shí)現(xiàn)計(jì)數(shù)，效率很高，不依賴索引，且可以實(shí)現(xiàn)各種排名需求，任意版本通用
•  MySQL8.0窗口函數(shù)，相當(dāng)于對(duì)自定義變量方案的封裝，效率最高，不依賴于索引，但8.0以前版本無法使用

實(shí)際上，在得到排名需求后，可進(jìn)一步通過簡單子查詢實(shí)現(xiàn)查詢分類Top K的任務(wù)需求。