還在用變量去實現(xiàn)多維度分組排序嗎？你 out 了！

目錄
一、什么是窗口函數(shù)
二、用窗口函數(shù)實現(xiàn)分組內(nèi)排序
三、基于窗口函數(shù)的高效分頁批處理方案

一、什么是窗口函數(shù)
窗口函數(shù)（Window Function）又叫開窗函數(shù)，是一種常見的 OLAP 函數(shù)，與聚合函數(shù)不同，窗口函數(shù)可以按多個維度分別做排序，簡化了復(fù)雜分析場景的 SQL 邏輯。常見的單機數(shù)據(jù)庫一般都支持窗口函數(shù)，TiDB v3.0，MySQL 8.0 版本也開始支持窗口函數(shù)功能。

二、用窗口函數(shù)實現(xiàn)分組內(nèi)排序
分組并對組內(nèi)排序是使用窗口函數(shù)的常見場景。
首先我們制作一張學(xué)生成績表，包含學(xué)生姓名，學(xué)號，科目，以及科目成績字段，并寫入一些數(shù)據(jù)：

mysql> select * from class_score;
+--------------+-----------+-------------------------+-----------+
| stuname      | stuno     | course                  | courscore |
+--------------+-----------+-------------------------+-----------+
| SpongeBob    | 201903001 | LinearAlgebra           |      60.5 |
| SpongeBob    | 201903001 | AdvancedMathematics     |      55.0 |
| SpongeBob    | 201903001 | Physics                 |      65.0 |
| SpongeBob    | 201903001 | ProbabilityTheory       |      87.0 |
| SpongeBob    | 201903001 | PrincipleofStatistics   |      90.0 |
| SpongeBob    | 201903001 | OperatingSystem         |      95.0 |
| SpongeBob    | 201903001 | FundamentalsofCompiling |      43.0 |
| SpongeBob    | 201903001 | DiscreteMathematics     |      72.0 |
| SpongeBob    | 201903001 | PrinciplesofDatabase    |      88.0 |
| SpongeBob    | 201903001 | English                 |      79.0 |
| SpongeBob    | 201903001 | OpBasketball            |      92.0 |
| SpongeBob    | 201903001 | OpTennis                |      94.0 |
| PatrickStar  | 201903011 | LinearAlgebra           |       6.5 |
| PatrickStar  | 201903011 | AdvancedMathematics     |       5.0 |
| PatrickStar  | 201903011 | Physics                 |       6.0 |
| PatrickStar  | 201903011 | ProbabilityTheory       |      12.0 |
| PatrickStar  | 201903011 | PrincipleofStatistics   |      20.0 |
| PatrickStar  | 201903011 | OperatingSystem         |      36.0 |
| PatrickStar  | 201903011 | FundamentalsofCompiling |       2.0 |
| PatrickStar  | 201903011 | DiscreteMathematics     |      14.0 |
| PatrickStar  | 201903011 | PrinciplesofDatabase    |       9.0 |
| PatrickStar  | 201903011 | English                 |      60.0 |
| PatrickStar  | 201903011 | OpTableTennis           |      12.0 |
| PatrickStar  | 201903011 | OpPiano                 |      99.0 |
| MonkeyDLuffy | 201803015 | LinearAlgebra           |      92.5 |
| MonkeyDLuffy | 201803015 | AdvancedMathematics     |      95.5 |
| MonkeyDLuffy | 201803015 | Physics                 |      63.5 |
| MonkeyDLuffy | 201803015 | ProbabilityTheory       |      76.0 |
| MonkeyDLuffy | 201803015 | PrincipleofStatistics   |      69.0 |
| MonkeyDLuffy | 201803015 | OperatingSystem         |      90.5 |
| MonkeyDLuffy | 201803015 | FundamentalsofCompiling |      88.0 |
| MonkeyDLuffy | 201803015 | DiscreteMathematics     |      89.0 |
| MonkeyDLuffy | 201803015 | PrinciplesofDatabase    |      60.5 |
| MonkeyDLuffy | 201803015 | English                 |      43.0 |
| MonkeyDLuffy | 201803015 | OpSwimming              |      67.0 |
| MonkeyDLuffy | 201803015 | OpFencing               |      76.0 |
+--------------+-----------+-------------------------+-----------+
36 rows in set (0.01 sec)

業(yè)務(wù)需求 1：計算出每科成績的前兩名的姓名、學(xué)號和成績
這是一個難以用聚合函數(shù)實現(xiàn)的需求，由于長期不支持窗口函數(shù)，MySQL 社區(qū)普遍推薦使用用戶變量的方式來實現(xiàn)，具體實現(xiàn)方式如下：

mysql> SET @z := NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @ROW_NUM := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select course, stuname, stuno, courscore from (select course, @ROW_NUM := IF(course = @z, @ROW_NUM + 1, 1) as ROW_NUM, @z := course AS z, stuname, courscore, stuno FROM (select * from class_score order by course, courscore desc) t1) t2 where t2.ROW_NUM<=2;
+-------------------------+--------------+-----------+-----------+
| course                  | stuname      | stuno     | courscore |
+-------------------------+--------------+-----------+-----------+
| AdvancedMathematics     | MonkeyDLuffy | 201803015 |      95.5 |
| AdvancedMathematics     | SpongeBob    | 201903001 |      55.0 |
| DiscreteMathematics     | MonkeyDLuffy | 201803015 |      89.0 |
| DiscreteMathematics     | SpongeBob    | 201903001 |      72.0 |
| English                 | SpongeBob    | 201903001 |      79.0 |
| English                 | PatrickStar  | 201903011 |      60.0 |
| FundamentalsofCompiling | MonkeyDLuffy | 201803015 |      88.0 |
| FundamentalsofCompiling | SpongeBob    | 201903001 |      43.0 |
| LinearAlgebra           | MonkeyDLuffy | 201803015 |      92.5 |
| LinearAlgebra           | SpongeBob    | 201903001 |      60.5 |
| OpBasketball            | SpongeBob    | 201903001 |      92.0 |
| OpFencing               | MonkeyDLuffy | 201803015 |      76.0 |
| OpPiano                 | PatrickStar  | 201903011 |      99.0 |
| OpSwimming              | MonkeyDLuffy | 201803015 |      67.0 |
| OpTableTennis           | PatrickStar  | 201903011 |      12.0 |
| OpTennis                | SpongeBob    | 201903001 |      94.0 |
| OperatingSystem         | SpongeBob    | 201903001 |      95.0 |
| OperatingSystem         | MonkeyDLuffy | 201803015 |      90.5 |
| Physics                 | SpongeBob    | 201903001 |      65.0 |
| Physics                 | MonkeyDLuffy | 201803015 |      63.5 |
| PrincipleofStatistics   | SpongeBob    | 201903001 |      90.0 |
| PrincipleofStatistics   | MonkeyDLuffy | 201803015 |      69.0 |
| PrinciplesofDatabase    | SpongeBob    | 201903001 |      88.0 |
| PrinciplesofDatabase    | MonkeyDLuffy | 201803015 |      60.5 |
| ProbabilityTheory       | SpongeBob    | 201903001 |      87.0 |
| ProbabilityTheory       | MonkeyDLuffy | 201803015 |      76.0 |
+-------------------------+--------------+-----------+-----------+
26 rows in set (0.01 sec)

通過定義兩個用戶變量，一個用于切換到下一組，另一個用來發(fā)放行號，以此來通過嵌套循環(huán)的方式來實現(xiàn)為每組單獨發(fā)放行號。缺點是不能處理相同分數(shù)名次并列的情況，并且嵌套太多，邏輯比較復(fù)雜，每次計算都要為變量重新賦值。

來看一下窗口函數(shù)的實現(xiàn)方式，僅需要一條 SQL，一個子查詢就可以得出各科成績的前兩名，注意這里使用的 rank() 函數(shù)可以識別相同分數(shù)名次并列的情況，也就是說假如一科出現(xiàn)了兩人并列第一，使用下面的 SQL 可以公平的把并列第一的情況展現(xiàn)出來，這是用戶變量難以實現(xiàn)的。

mysql> select course, stuname, stuno, courscore from (select *, rank() over(partition by course order by course, courscore desc) as RANK_ from class_score) t where t.RANK_<=2;
+-------------------------+--------------+-----------+-----------+
| course                  | stuname      | stuno     | courscore |
+-------------------------+--------------+-----------+-----------+
| AdvancedMathematics     | MonkeyDLuffy | 201803015 |      95.5 |
| AdvancedMathematics     | SpongeBob    | 201903001 |      55.0 |
| DiscreteMathematics     | MonkeyDLuffy | 201803015 |      89.0 |
| DiscreteMathematics     | SpongeBob    | 201903001 |      72.0 |
| English                 | SpongeBob    | 201903001 |      79.0 |
| English                 | PatrickStar  | 201903011 |      60.0 |
| FundamentalsofCompiling | MonkeyDLuffy | 201803015 |      88.0 |
| FundamentalsofCompiling | SpongeBob    | 201903001 |      43.0 |
| LinearAlgebra           | MonkeyDLuffy | 201803015 |      92.5 |
| LinearAlgebra           | SpongeBob    | 201903001 |      60.5 |
| OpBasketball            | SpongeBob    | 201903001 |      92.0 |
| OpFencing               | MonkeyDLuffy | 201803015 |      76.0 |
| OpPiano                 | PatrickStar  | 201903011 |      99.0 |
| OpSwimming              | MonkeyDLuffy | 201803015 |      67.0 |
| OpTableTennis           | PatrickStar  | 201903011 |      12.0 |
| OpTennis                | SpongeBob    | 201903001 |      94.0 |
| OperatingSystem         | SpongeBob    | 201903001 |      95.0 |
| OperatingSystem         | MonkeyDLuffy | 201803015 |      90.5 |
| Physics                 | SpongeBob    | 201903001 |      65.0 |
| Physics                 | MonkeyDLuffy | 201803015 |      63.5 |
| PrincipleofStatistics   | SpongeBob    | 201903001 |      90.0 |
| PrincipleofStatistics   | MonkeyDLuffy | 201803015 |      69.0 |
| PrinciplesofDatabase    | SpongeBob    | 201903001 |      88.0 |
| PrinciplesofDatabase    | MonkeyDLuffy | 201803015 |      60.5 |
| ProbabilityTheory       | SpongeBob    | 201903001 |      87.0 |
| ProbabilityTheory       | MonkeyDLuffy | 201803015 |      76.0 |
+-------------------------+--------------+-----------+-----------+
26 rows in set (0.01 sec)

業(yè)務(wù)需求 2：計算出每科成績第一名與第二名之間的分差
TiDB 提供 lead() 與 lag() 函數(shù)來獲取組內(nèi)數(shù)據(jù)排序后的下一行或上一行的列值，此處正是使用了 lead() 函數(shù)來獲取下一行的列值，通過子查詢的方式即可計算出第一名與第二名之間的分差：

mysql> select course, courscore, courscore - lead_ as delta from (select *, lead(courscore,1) over(partition by course order by course, courscore desc) as lead_, rank() over(partition by course order by course, courscore desc) as RANK_ from class_score) t where t.RANK_=1;
+-------------------------+-----------+-------+
| course                  | courscore | delta |
+-------------------------+-----------+-------+
| AdvancedMathematics     |      95.5 |  40.5 |
| DiscreteMathematics     |      89.0 |  17.0 |
| English                 |      79.0 |  19.0 |
| FundamentalsofCompiling |      88.0 |  45.0 |
| LinearAlgebra           |      92.5 |  32.0 |
| OpBasketball            |      92.0 |  NULL |
| OpFencing               |      76.0 |  NULL |
| OpPiano                 |      99.0 |  NULL |
| OpSwimming              |      67.0 |  NULL |
| OpTableTennis           |      12.0 |  NULL |
| OpTennis                |      94.0 |  NULL |
| OperatingSystem         |      95.0 |   4.5 |
| Physics                 |      65.0 |   1.5 |
| PrincipleofStatistics   |      90.0 |  21.0 |
| PrinciplesofDatabase    |      88.0 |  27.5 |
| ProbabilityTheory       |      87.0 |  11.0 |
+-------------------------+-----------+-------+
16 rows in set (0.00 sec)

三、基于窗口函數(shù)的高效分頁批處理方案
窗口函數(shù)作為數(shù)據(jù)庫的高級分析功能，它的應(yīng)用場景不僅限于分組內(nèi)排序，我們還可以利用窗口函數(shù)做很多有意思的事情，比如本案例用窗口函數(shù)來大幅優(yōu)化跑批中的分頁處理效率。
我們用 sysbench 創(chuàng)建一張表并加載一些數(shù)據(jù)，用這張表來模擬批量處理邏輯。
首先初始化一張表 sbtest1，其表結(jié)構(gòu)如下，其中 id 字段為整型主鍵：

mysql> desc sbtest1;
+-------+-----------+------+------+---------+----------------+
| Field | Type      | Null | Key  | Default | Extra          |
+-------+-----------+------+------+---------+----------------+
| id    | int(11)   | NO   | PRI  | NULL    | auto_increment |
| k     | int(11)   | NO   | MUL  | 0       |                |
| c     | char(120) | NO   |      |         |                |
| pad   | char(60)  | NO   |      |         |                |
+-------+-----------+------+------+---------+----------------+
4 rows in set (0.00 sec)

初始化時加載了 100 萬行數(shù)據(jù)，之后我們刪除掉其中一部分，通過這樣的方式使 id 值不再連續(xù)，弱化分頁時對于 id 值的依賴。當(dāng)前表中剩余數(shù)據(jù)有 90 萬行左右：

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|   899997 |
+----------+
1 row in set (0.65 sec)

表內(nèi)數(shù)據(jù)預(yù)覽：

mysql> select * from sbtest1 limit 6;
+--------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id     | k      | c                                                                                                                       | pad                                                         |
+--------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 170713 | 502585 | 68207710198-92682096687-30191949979-36606876762-68131108662-05227395575-42775011851-25226186240-86628605904-92905646658 | 92965159868-07234410731-39167064470-14286085716-15715308680 |
| 170715 | 594870 | 03482720054-50379763215-87903836122-97559417898-49419423256-08561919665-14395666373-04552411341-51225532045-80056729812 | 14534783486-12748024297-66217900494-07062661389-59419864770 |
| 170716 | 618106 | 17284178744-35252021030-57793972189-12648949390-90678614158-50453793363-79361198568-92739087625-90147799094-56275382145 | 96022213702-57054390589-17717245768-83668730988-26655128451 |
| 170717 | 498071 | 55266913813-66118089063-10841700714-78346894223-87037025257-46356741961-50684103191-23859048041-87607902200-58092836685 | 85952977843-18323978167-65380568194-90178704467-17391816925 |
| 170718 | 500843 | 81176419361-91278769025-45575469479-70005546210-57581523030-24528178176-84655463505-48851510236-43885747093-01732211221 | 56651630364-99235825673-25852643818-33561663285-01699695675 |
| 170719 | 499063 | 87982690236-17188898588-98406118277-04805507744-90184035670-09591916010-78045349706-89374841792-79952082330-08177876709 | 16885918921-25441055158-88415348869-22003000705-82198521530 |
+--------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
6 rows in set (0.01 sec)

常見的分頁更新 SQL 一般使用主鍵/唯一索引進行排序，以確保相鄰的兩頁之間沒有空隙或重疊，配合 MySQL limit 語法中非常好用的 offset 功能來按固定行數(shù)拆分頁面，拆分后的頁面被包裝在獨立的事務(wù)中，可以靈活的進行逐頁或批量對數(shù)據(jù)進行更新。

begin;
update sbtest1 set pad='new_value' where id in (select id from sbtest1 order by id limit 0,10000);
commit;
begin;
update sbtest1 set pad='new_value' where id in (select id from sbtest1 order by id limit 10000,10000);
commit;
begin;
update sbtest1 set pad='new_value' where id in (select id from sbtest1 order by id limit 20000,10000);
commit;

這種方案邏輯清晰，SQL 易于編寫，但它有著明顯的劣勢，由于需要對主鍵/唯一索引進行排序，越靠后的頁面需要參與排序的行數(shù)越多，TiKV 中掃描數(shù)據(jù)的壓力也越大，批量整體處理效率就越低，當(dāng)批量的整體數(shù)據(jù)量比較大時，很可能會占用過多計算資源，甚至觸發(fā)性能瓶頸，影響聯(lián)機業(yè)務(wù)。

下面案例是一種改進方案，通過靈活運用窗口函數(shù) row_number() 將數(shù)據(jù)按照主鍵排序后賦予行號，再通過聚合函數(shù)按照設(shè)置好的頁面大小對行號進行分組，以計算出每頁的最大值和最小值。

mysql> select min(t.id) as start_key, max(t.id) as end_key, count(*) as page_size from (select *, row_number() over(order by id) as row_num from sbtest1) t group by floor((t.row_num-1)/50000) order by start_key;
+-----------+---------+-----------+
| start_key | end_key | page_size |
+-----------+---------+-----------+
|         1 |   55556 |     50000 |
|     55557 |  111111 |     50000 |
|    111112 |  166667 |     50000 |
|    166668 |  222222 |     50000 |
|    222223 |  277778 |     50000 |
|    277779 |  333333 |     50000 |
|    333335 |  388889 |     50000 |
|    388890 |  444445 |     50000 |
|    444446 |  500000 |     50000 |
|    500001 |  555556 |     50000 |
|    555557 |  611111 |     50000 |
|    611112 |  666667 |     50000 |
|    666668 |  722223 |     50000 |
|    722225 |  777779 |     50000 |
|    777780 |  833335 |     50000 |
|    833336 |  888891 |     50000 |
|    888892 |  944447 |     50000 |
|    944448 | 1000000 |     49997 |
+-----------+---------+-----------+
18 rows in set (1.87 sec)

將這個結(jié)果集作為批量處理的元信息，這樣在批量處理階段只需要使用 between...and... 來圈定好每個頁面的數(shù)據(jù)，多個頁面并發(fā)的進行批量更新即可，由于元信息的計算階段使用主鍵/唯一索引進行排序，并用 row_number() 函數(shù)賦予了唯一序號，因此也可以避免在兩個相鄰的頁面中出現(xiàn)空隙或重疊。
使用這種方案可以顯著避免由于頻繁，大量的排序造成的性能損耗，進而大幅提升批量處理的整體效率。

mysql> update sbtest1 set pad='new_value' where id between 1 and 55556;
Query OK, 50000 rows affected (3.51 sec)
Rows matched: 50000  Changed: 50000  Warnings: 0

mysql> update sbtest1 set pad='new_value' where id between 55557 and 111111;
Query OK, 50000 rows affected (2.14 sec)
Rows matched: 50000  Changed: 50000  Warnings: 0

mysql> update sbtest1 set pad='new_value' where id between 111112 and 166667;
Query OK, 50000 rows affected (2.21 sec)
Rows matched: 50000  Changed: 50000  Warnings: 0

四、復(fù)合主鍵分頁案例

  1. 制作元信息表
mysql> SELECT floor(( t1.row_num - 1 )/ 600000 )+1 rn, min(mvalue),max(mvalue),count(*) FROM (SELECT concat( '(''', customer_id, ''',''', customer_idno, ''')' ) AS mvalue, row_number() over ( ORDER BY customer_id, customer_idno ) AS row_num FROM findpt.customer) t1  GROUP BY floor(( t1.row_num - 1 )/ 600000 )  ORDER BY rn;
+----+--------------------------------------+--------------------------------------+----------+
| rn | min(mvalue)                          | max(mvalue)                          | count(*) |
+----+--------------------------------------+--------------------------------------+----------+
|  1 | ('10000000001','351421198512031871') | ('10000600000','541420198607276566') |   600000 |
|  2 | ('10000600001','410727197307043818') | ('10001200000','221518199305165132') |   600000 |
|  3 | ('10001200001','521527198406224414') | ('10001800000','320209197609305969') |   600000 |
|  4 | ('10001800001','220304197912193073') | ('10002400000','230504197308067651') |   600000 |
|  5 | ('10002400001','121711197208214015') | ('10003000000','430112199003258074') |   600000 |
|  6 | ('10003000001','330609198706142725') | ('10003600000','520519197407128506') |   600000 |
|  7 | ('10003600001','621108199508175476') | ('10004200000','631319197203254252') |   600000 |
|  8 | ('10004200001','350406198608214809') | ('10004800000','500827199406068657') |   600000 |
|  9 | ('10004800001','450311198612295355') | ('10005400000','430713199601229738') |   600000 |
| 10 | ('10005400001','640608199311094703') | ('10006000000','131222199007068025') |   600000 |
| 11 | ('10006000001','110724197808158121') | ('10006600000','410909199902088607') |   600000 |
| 12 | ('10006600001','371802199909286692') | ('10007200000','331616199104157617') |   600000 |
| 13 | ('10007200001','631618198707015770') | ('10007800000','311424198409271703') |   600000 |
| 14 | ('10007800001','450212199805062337') | ('10008400000','141520197703176129') |   600000 |
| 15 | ('10008400001','130920197811106553') | ('10009000000','640206197509055077') |   600000 |
| 16 | ('10009000001','151822197801136758') | ('10009600000','810620197505228665') |   600000 |
| 17 | ('10009600001','230109198906203721') | ('10010000000','340408198312036321') |   400000 |
+----+--------------------------------------+--------------------------------------+----------+
17 rows in set (26.42 sec)
  1. 操作分頁的案例
delete from customer where  (customer_id, customer_idno) >= ('10000000001','351421198512031871') and  (customer_id, customer_idno) <= ('10000600000','541420198607276566') order by customer_id,customer_idno;

另外可以使用隱藏字段 _tidb_rowid 做分頁使用。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容