01需求介绍考虑MySQL中的一个经典应用：给定一个学生考试成绩表，需要按课程和年级对学生进行排序。为了简单起见，只给出成绩表，不考虑可能关联的学生信息表、课程信息表、教师信息表等，只在成绩表中创建三个关键字段：cid：课程id,int类型,共5门课程sid:学生id,int类型,共8872个学生score:achievement,int类型,共22366条成绩信息,分布在10-100之间为渐进分析,初始状态不加a主键，也没有建立任何索引。02子查询实现这个需求最直接的思路就是通过子查询统计每一个分数：在统计表中有多少分数比它高，那么分数的排名就是高分count+1。如果要区分当然rankings，只需要添加一个约束条件，在统计表的时候限制courseids相等。1SELECT2a.*,(SELECTCOUNT(score)+1FROMscoresWHEREcid=a.cidANDscore>a.score)AS'rank'3FROM4scoresa5ORDERBY6a.cid,a.scoreDESC;需要注意的是score>a.scoreandCOUNT()+1，意思是比score高+1，比如90,80,80,70...等scores，排名结果为1，2,2,4...;如果选择score>=a.score和COUNT()作为排序条件，则结果为1,3,3,4...不添加任何索引，查询速度很慢，需要120s。不加索引时的子查询执行计划优化查询的第一个思路当然是加索引：虽然外层查询没有使用任何where约束，但是子查询使用cid和score这两个字段来判断，所以考虑加索引：1CREATEINDEXidcONscores(cid)；2CREATEINDEXidsONscores(score);添加索引后，查询耗时96s。虽然有进步，但还是很尴尬。解释一下查询计划，发现虽然速度还是很慢，但确实应用了两个索引：添加独立索引后的子查询执行计划由于独立索引不能显着提高效率，考虑子查询中的where条件为不是独立字段Constraints的常量值，而是依赖外循环值的联合约束，这时考虑加入联合索引：CREATEINDEXidcsONscores(cid,score);查询速度确实更快，实际用时24s。解释一下查询计划，发现同时使用了独立索引和联合索引。但是不得不说，24s的响应时间对于需要0.5s解决战斗的即时任务来说还是不够的。加入联合索引后的子查询执行计划只能另辟蹊径。03Self-join一般来说，对于慢速子查询任务，切换到连接查询（join）可以有明显的提升。具体到按课程排名的具体要求，我们考虑自加入scores表，加入条件是课程相等且表a的score值小于表b的score值，使得排名统计满足加入条件的记录条数可以得到信息：1SELECT2a.*,COUNT(b.score)+1AS'rank'3FROM4scoresaLEFTJOINscoresbON(a.cid=b.cidANDa.score
