本站承诺永不接任何虚假欺骗、联盟广告、弹窗广告、病毒广告、诱导充值等影响用户体验的广告,广告屏蔽插件会影响本站部分功能,还请不要屏蔽本站广告,感谢支持!

当前位置:首页 / 正文

2021-04-12 | 编程技术 | 3029 次阅读 | 等你评论 | 4 次点赞 | 繁体

图片alt

对数据库中的记录依据某个字段进行排序是一种常见需求,虽然简单的 Order by 可以胜任,但如果想要输出具体的排名却难以直接实现。如果再考虑重复排名或者分类排名,那么情况就更为复杂。

本文介绍 4 种分类排名方式:子查询、自连接、自定义变量以及 MySQL8.0 窗口函数。

需求介绍

考虑 MySQL 中的一个经典应用:给定一个学生考试成绩表,要实现对学生按课程依成绩高低进行排序。为了简单起见,仅给定成绩表,而不考虑可能关联的学生信息表、课程信息表和教师信息表等,且成绩表中仅创建 3 个关键字段:

cid:课程 id,int 型,共 5 门课程
sid:学生 id,int 型,共 8872 名学生
score:成绩,int 型,共 22366 条成绩信息,分布于 10-100 之间

图片alt

为了逐步分析,初始状态不添加主键,也不建立任何索引。

子查询

实现这一需求的最直接想法是通过子查询,对每个分数进行统计:统计表中有多少分数比其更高,那么该分数的排名就是更高分数计数 +1。如果要区分课程排名,那么统计表时只需增加一个限制课程 id 相等的约束条件即可。

SELECT a.*, ( SELECT COUNT(score)+1 FROM scores WHERE cid = a.cid AND score > a.score ) AS 'rank' FROM scores a ORDER BY 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。

图片alt

优化查询的第一想法当然是添加索引:虽然外层查询未用到任何 where 约束条件,但子查询中用到了 cid 和 score 两个字段判断,于是考虑添加索引:

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

增加索引后,查询耗时 96s,虽然有提升,但仍难堪重任。解释查询计划,发现虽然速度仍然很慢,但两个索引确实都得到了应用:

图片alt

既然独立索引无法明显提升效率,考虑子查询中 where 条件不是独立字段的常值约束,而是依赖于外层循环取值的联合约束,那么再考虑添加一个联合索引:

CREATE INDEX idcs ON scores(cid, score);

查询速度确实是更快了,实际用时 24s。解释查询计划,发现既用到了独立索引,也用到了联合索引。但不得不说,24s 的响应时间对于要求 0.5s 解决战斗的即时任务来说,仍然是不够的。

图片alt

只能另辟蹊径。

自连接

一般来说,对于速度较慢的子查询任务,换做连接查询 (join) 可以得到明显提升。

具体到分课程排名这一具体需求,我们考虑对 scores 表进行自连接,其中连接条件为课程相等且 a 表 score 值小于 b 表 score 值,从而通过统计满足连接条件的记录数即可得到排名信息:

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

需注意的是:连接方式要选用 left join,以便将 a 表中的所有分数信息都显示出来;若是用 join,则最高分因为不存在满足连接的记录而被漏掉。至于连接条件中 score 值和 count() 的关系类似于子查询中的情况。

应用自连接,在不创建任何索引的情况下查询速度与子查询情况差不多,耗时 73s;在添加有效索引后,查询时间 27s,效率有所提升,但与查询方案效率相当。

图片alt

图片alt

显然,应用自连接替代子查询的方案并没有显著提升查询效率,即使是在添加了有效索引的基础上。

进一步分析数据表发现,实际上速度慢并不能否认索引在改善查询效率方面的能力,而仅仅是因为添加索引的字段取值较少的原因:cid 字段仅有 5 个取值——当字段取值个数较少时,添加索引很难见效。

例如,如果换一个需求,改为按学生区分各门课程的成绩排名(sid 取值数量很大),则应用索引即可有效改善查询效率。按学生查询成绩排名 SQL 语句:

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

对于如上查询,在未添加索引时,查询时间 34s;添加有效索引后耗时仅为 0.184s,添加索引的提升效果非常明显。

虽然这一论断捍卫了索引的地位作用,但如果我们的需求就是按课程进行排名呢?显然,无论是子查询还是自连接方案,都难以满足我们的实时查询需求。

只得再觅他法。

自定义变量

实际上,上述两种方案之所以速度较慢,是因为都作用在两个表上查询,如果再考虑外层的 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。这三种需求应用自定义变量进行排序都可以轻松搞定(具体变量含义和思路后续给出):

连续排名:

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

同分同名,不跳级:

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

同分同名,跳级:

SELECT 
    sid, cid, @curRank := IF(score=@preScore, @curRank, @totalRank) AS 'rank',
    @preScore := score,
    @totalRank := @totalRank+1
FROM 
    scores, (SELECT @curRank:=1, @totalRank:=1, @preScore:=NULL) tmp
ORDER BY 
    score

以上 SQL 语句是在不进行任何分类条件下的排名:通过自定义变量(MySQL 定义变量用 @ 作为引导符,并用 := 表示赋值)记录前一个排名、前一个分数值、当前的总排名,分别实现三种需求。

那么,若要实现分类排名呢,比如说区分各课程进行排名?那么只需再增加一个自定义变量,用于记录前一个课程 cid 即可:
若当前分类信息与前一课程 cid 相同,则继续当前的排名处理(根据具体需求选择三种排名中的一种);
若当前分类与前一课程 cid 不同,则排名信息初始化,从 1 重新开始。

以相对复杂的“同分同名、跳级”为例,此时 SQL 语句为:

SELECT  sid, cid, 
    @totalRank := IF(cid=@preCid, @totalRank+1, 1),
    @curRank := IF(cid=@preCid, IF(score=@preScore, @curRank, @totalRank), 1) AS 'rank',
    @preScore := score,
    @preCid := cid
FROM 
    scores, (SELECT @curRank:=0, @totalRank:=0, @preScore:=NULL, @preCid:=NULL) tmp
ORDER BY 
    cid, score DESC

对各变量含义解释如下:

@totalRank 用于记录当前分类中的总排名,初始化为 0
@curRank 用于记录当前分类中的当前排名,初始化为 0
@preScore 用于记录上一个分数情况,初始化为 NULL
@preCid 用于记录上一个课程 cid,初始化为 NULL

执行流程及条件判断为:

若当前 cid 与前一 cid 相同,表示是同一个分类,排名在之前排名基础增加,具体来说:

总排名每次 +1
若当前分数与前一分数相同,则当前排名不变;否则跳级到总排名
若当前 cid 与前一 cid 不同,表示开始新的课程排名,总排名和当前排名均初始化为 1

基于以上 SQL 语句,执行相同的任务,耗时仅需 0.09s,其效率相当于子查询最快速度 24s 的 266 倍,相当于自连接最快速度 27s 的 300 倍,其查询效率可见一斑。

另外,由于上述 SQL 语句不存在 where 约束条件,所以与是否建立索引无关。

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 语句为:

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

查询耗时 0.066s,比自定义变量实现的排名速度略高一点。同时,该排名方式也与索引无关。

将 RANK() 替换成另外两个窗口函数,可实现其他相应需求。

具体的窗口函数用法查看文章:https://blog.wangtwothree.com/database/147.html

总结

本文以对给定成绩表进行分课程排名为例,讲述了 4 种实现方案:

  • 子查询方案,通过嵌套 count() 函数实现,效率较低,创建有效索引可提升一定效率,仅支持"同分同名、跳级"排名需求
  • 自连接方案,与子查询类似,通过自连接和 count() 函数实现,效率较低,依赖于索引,也仅支持"同分同名、跳级"排名需求
  • 自定义变量方案,通过定义变量实现计数,效率很高,不依赖索引,且可以实现各种排名需求,任意版本通用
  • MySQL8.0 窗口函数,相当于对自定义变量方案的封装,效率最高,不依赖于索引,但 8.0 以前版本无法使用

实际上,在得到排名需求后,可进一步通过简单子查询实现查询分类 Top K 的任务需求。

via:
一文解决所有 MySQL 分类排名问题
https://mp.weixin.qq.com/s/1E3x6dRcav5oK50TISTLCw

标签: mysql

猜你喜欢
如何定位Mysql中CPU占用高的查询语句
今天 mysql 服务器突然 CPU 告警,记录一下问题查找的过程第一步查看具体是哪个线程占用CPU最高1、在 Linux 中使用 top 命令找到 mysql 进程 PID2、指定进程 PID...
mysql8利用CTE特性实现递归查询
递归查询分为父子查询和子父查询。父子查询: 根据父 id 查询下面所有子节点数据;子父查询: 根据子 id 查询上面所有父节点数据;下边就利用 mysql8 新增语法实现递归查询,表结构及数...
mysql数据库删除重复的数据只保留一条
问题引入假设一个场景,一张用户表,包含 3 个字段:id,identity_id,name。现在身份证号 identity_id 和姓名 name 有很多重复的数据,需要删除多余数据只保留一条有...
Mysql 窗口函数学习
窗口函数是数据库查询中的一个经典场景,在解决某些特定问题时甚至是必须的。个人认为,在单纯的数据库查询语句层面【即不考虑 DML、SQL 调优、索引等进阶】,窗口函数可看作是考察求职者 SQL 功...
三种方式修改 MySQL 数据库名
在 Innodb 数据库引擎下修改数据库名的方式与 MyISAM 引擎下修改数据库的方式完全不一样,如果是 MyISAM 可以直接去数据库目录中 mv 就可以,Innodb 如果用同样的方法修改...
Navicat Premium Mac 12.022 破解(20200131亲测可用!!!)
背景受新型冠状病毒影响,公司要求在家远程办公,故准备强行搞个 Navicat Premium Mac 12 破解版本。历经了种种种种种种磨难与艰辛与火海,终于破解成功了。因为要经常使用 MySQ...
PHP 自动爬毒汤日历搭建毒鸡汤一言 API 接口
什么是毒汤日历?毒汤日历是一本有毒的日历,每天用毒鸡汤来唤醒你。 你甚至不用打开日历,打开 App 的推送,每天会定时送上一杯毒鸡汤。 自己也能制作毒鸡汤?那太好了,毒性够强,如果让别人扎到心你就厉害
Typecho纯代码生成sitemap站点地图
想要实现 Typecho 纯代码生成 sitemap 站点地图只需要 2 步就够了。 1、在博客主题目录新建 sitemap.php 页面,放入以下代码: ```
薅京东羊毛必备抓取Cookies教程
本文只介绍如何利用安卓手机浏览器获取京东 cookie 教程,具体为什么要获取 cookie 以及如何薅羊毛请查看:[闲置服务器薅京东的羊毛—青龙面板部署与京东签到](https://blog.wan
(首次提交评论需审核通过才会显示,请勿重复提交)