如何在一份成绩单中寻找到排名第二的学生,这是很常见的问题,本文中就给出几种解法并分析他们的性能。
假设数据如下:
id | score | name |
1 | 76 | 张三 |
2 | 100 | 李四 |
3 | 7 | 王五 |
4 | 85 | 秋柳 |
student_score
在表student_score中查找得分第二高的学生的方法:
1、select max(score) from student_score where score not in (select max(score) from student_score);
2、select max(score) from student_score where score < (select max(score) from student_score);
3、select score from (select score from student_score order by score desc limit 2) as temp order by score limit 1;
性能分析:
1、当score没有建索引时。
方法一:
方法二:
方法三:
首先,要清楚的一点是,当没有使用索引时,max()函数性能就会很低,就是简单的进行全表扫描。如下图所示:
我在有1743个数据集的环境上对三种方法进行测试,为了使结果更接近真相,我测试多次,测试结果如下(单位是:秒)。
方法一
|
方法二
|
方法三(1)
|
方法三(2)
|
select max(score) from student_score
|
---|---|---|---|---|
3.084 | 0.016 | 0.018 | 0.036 | 0.0097 |
3.717 | 0.019 | 0.012 | 0.037 | 0.014 |
2.969 | 0.026 | 0.023 | 0.060 | 0.021 |
3.664 | 0.015 | 0.279 | 0.046 | 0.013 |
2.918 | 0.018 | 0.014 | 0.041 | 0.027 |
在三种方法中,方法一方法二两个查询都进行了全表扫描;方法三子查询进行全表扫描,生成一个临时表,然后临时表进行全表扫描,但是方法三涉及到了文件排序。
我们看到方法二、方法三的的查询效率差不多,但是方法一的查询效率就很低了,大约只有方法二和方法三的1/200了。
为什么??
也许你看到select max(score) from student_score where score not in (select max(score) from student_score); 就会自然的认为,先是子语句执行,然后返回一个表里面含有(rows-1)行的数据,然后外层在调用select再扫描一遍表。这样的话就是扫描了大约 2*rows行数据。但是事实上,mysql在优化是完全不是你想的那样,他会把SQL语句按照他认为最优的执行方式给优化了,其实在方法一explain执行语句中我们可以看到“dependent subquery”,这表示这是一个依赖子查询,其实也就是它依赖于最外层的select语句。他的执行顺序是这样的:他首先对student_score进行全表扫描,然后根据返回的score逐个执行子查询,这样就是你看到“dependent subquery”的原因;这样的话他大概就得扫描rows*rows行的代码。
而在方法二中,where条件里的select会进行全表扫描,然后返回含有score的结果集,然后最外层的select语句扫描一遍子语句返回的结果集,就可以得到答案了。它总共扫描了大概2*row是行数据。
可能有人又有疑问了,这么说方法一运行时间大约是方法二运行时间的rows/2,为什么这里差别会这么大??
因为运行时间不光是扫描表的时间还有SQL优化、查找缓冲、读取表等时间因素影响,还有数据库内部可能还进行了某种优化等
为了验证以上的说法把数据扩了6.5倍,数据量为11333行数据。
方法一
|
方法二
|
---|---|
109 | 0.066 |
93 | 0.075 |
0.909 | |
0.043 | |
0.073 | |
0.060 |
因为方法一此时执行就太耗时了,我就运行了二次。由上面分析可知当数据扩大6.5倍时,方法一运行时间应该扩大6.5*6.5,方法二运行时间应该扩大6.5倍。上图运行结果验证了上面的分析。
方法三我们采取了两组数据,方法三(1)是本身内部就是有序的,方法三(2)内部是随机的,方法三(1)和方法二运行时间差不多,方法三(2)要运行时间大概是方法三(1)的2-3倍。刚开始我很困惑,为什么方法三会和方法二差不多呢,最快的排序算法都应该是O(nlgn),当我运行select score from student_score order by score ;时(他的运行时间远大于方法三)我才明白,MYSQL内部应该做了某种优化,根本就没有全部数据进行排序,只是找到了最大的二个数而已。
2、当score使用索引时
方法一:
方法二:
方法三:
当使用索引时,max()函数不需要全表,可以直接在B+树上读取出来
方法一
|
方法二
|
方法三(1)
|
方法三(2)
|
---|---|---|---|
0.024 | 0.0075 | 0.011 | 0.0099 |
0.027 | 0.015 | 0.0079 | 0.013 |
0.020 | 0.011 | 0.014 | 0.010 |
0.029 | 0.047 | 0.0096 | 0.0099 |
从explain 中看出,方法一仍要一次全表扫描,在没有索引时我们已经解释很清楚了,在每次子查询是此时不需要全表扫描可以直接拿到max()。在方法二中,两次都是直接读取max()。在方法三种因为索引本身就是有序的,可以直接读取2个最大值,然后在外层select语句中进行排序,所以初始数据是什么样的分布是无所谓的。
从上面的分析中我们看出,方法一的性能远不如方法二和方法三,方法二比方法三稍微好一点,但是并不明显。