在数据库中查找“第二”的各种方法以及性能比较

如何在一份成绩单中寻找到排名第二的学生，这是很常见的问题，本文中就给出几种解法并分析他们的性能。

假设数据如下：

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语句中进行排序，所以初始数据是什么样的分布是无所谓的。

从上面的分析中我们看出，方法一的性能远不如方法二和方法三，方法二比方法三稍微好一点，但是并不明显。