一个线上的排行榜SQL问题
一个线上的排行榜SQL问题
今天上班的时候，要对一个数据库中的所有慢日志记录进行做一个统计，统计出数据库中所有慢日志用时最长的10条，这个需求乍一听比较简单，数据库中的满日志大概有5万多条吧，走个全表扫描也就不到半秒的时间。我第一反应是：
select * from tbl_name order by query_time limit 10 desc；
这不就直接找到了么？
但是问题还没有结束，因为慢日志数据库中统计的是所有的慢日志，可能有些重复的SQL，一直运行速度比较慢，所以就需要做一个去重的工作，大概如下：
+-----------------------+
|query_id | query_time|
+-----------------------+
|aaa | 9.897 |
|bbb | 8.376 |
|ccc | 7.685 |
|aaa | 12.09 |
...
|bbb | 6.555 |
+-----------------------+
问题就是要求这些数据里面最慢的10条。这不是直接按照query_time排序，再对query_id进行去重就可以了么？问题是distinct的方法不能对select后面的多个字段中的某一个进行去重，它只能对select的所有字段进行去重。如果我们使用distinct，结果是这个表里的数据还是跟之前的保持一致，没有实现去重的效果。
这个问题可以简化为如下模型，就是在一个5条记录的表里面，找到花费时间最长的两条。当然也就是说，这两条数据的query_id必须不一样。我们针对上面的模型分析，如果我们想要求出上面的两条花费时间最长的query_id，用肉眼可以看出来，aaa中最大的时间是12.09s，而bbb中最长的时间是8.376s，而ccc中只有一个时间，也就是7.685s，不难判断出，这两条记录是：
+-----------------------+
|query_id | query_time|
+-----------------------+
|aaa | 12.09 |
|bbb | 8.376 |
+-----------------------+
我们创建一个表对这个问题进行分析：
mysql> select * from slow order by query_time desc;
+----------+------------+
| query_id | query_time |
+----------+------------+
| aaa | 12.09 |
| aaa | 9.90 |
| ccc | 9.20 |
| bbb | 8.38 |
| ccc | 7.69 |
| bbb | 6.56 |
+----------+------------+
6 rows in set (0.00 sec)
需要注意的是，为了看着方便，我把上面的3位小树改成了2位小数，并且多添加了一条数据，这样就保证aaa、bbb、ccc都有两条对应的数据，也就保证了干扰项，然后把数据库里面的数据进行了排序。现在，我们的目的是找到query_time最长的两条SQL,不能重复，用肉眼不难看出，结果是：
+----------+------------+
| query_id | query_time |
+----------+------------+
| aaa | 12.09 |
| ccc | 9.20 |
+----------+------------+
那么，接下来，我们就是要写SQL，让它输出的结果跟我们预料的结果匹配。
这个数据库中一共有6条记录，我没两条都一样，自然而然我们能想到对它进行分组：
mysql> select * from slow group by query_id;
+----------+------------+
| query_id | query_time |
+----------+------------+
| aaa | 9.90 |
| bbb | 8.38 |
| ccc | 7.69 |
+----------+------------+
3 rows in set (0.00 sec)
分组后的结果显然不能满足我们的需求，因为aaa有一条查询时间为12.09s的记录不见了，ccc的9.20s的记录也没有了。group by操作仅仅是做了一个分组，我们需要求出来每个分组里面最大的值：
mysql> select query_id,max(query_time) from slow group by query_id ;
+----------+-----------------+
| query_id | max(query_time) |
+----------+-----------------+
| aaa | 12.09 |
| bbb | 8.38 |
| ccc | 9.20 |
+----------+-----------------+
3 rows in set (0.00 sec)
加上max(query_time)之后，看样子已经出来了，因为我们需要的两条记录都已经算出来了，现在我们需要对最终的结果进行排序，然后输出两条即可。下午的时候，我顺手就写了一句：
mysql> select query_id,max(query_time) from slow
group by query_id order by query_time desc limit 2;
+----------+-----------------+
| query_id | max(query_time) |
+----------+-----------------+
| aaa | 12.09 |
| bbb | 8.38 |
+----------+-----------------+
2 rows in set (0.00 sec)
发现辛苦找出来的记录又不见了！到这里的时候脑子比较懵，过了一会儿才发现问题，原来是order by的对象写错了，应该是max(query_time),写成了query_time,改成max(query_time),终于对了：
mysql> select query_id,max(query_time) from slow group by query_id order by max(query_time) desc limit 2;
+----------+-----------------+
| query_id | max(query_time) |
+----------+-----------------+
| aaa | 12.09 |
| ccc | 9.20 |
+----------+-----------------+
2 rows in set (0.00 sec)
除了这种写法，当然我们还可以通过链接的写法来实现：
1.首先找到每个query_id对应的时间最长的记录：
mysql> select query_id,max(query_time) from slow group by query_id ;
+----------+-----------------+
| query_id | max(query_time) |
+----------+-----------------+
| aaa | 12.09 |
| bbb | 8.38 |
| ccc | 9.20 |
+----------+-----------------+
3 rows in set (0.00 sec)
2.然后再用原来的表和这个结果表进行连接：
mysql> select * from slow
as a
join
(select query_id,max(query_time) from slow group by query_id)
as b
where a.query_id=b.query_id;
+----------+------------+----------+-----------------+
| query_id | query_time | query_id | max(query_time) |
+----------+------------+----------+-----------------+
| aaa | 9.90 | aaa | 12.09 |
| bbb | 8.38 | bbb | 8.38 |
| ccc | 7.69 | ccc | 9.20 |
| aaa | 12.09 | aaa | 12.09 |
| bbb | 6.56 | bbb | 8.38 |
| ccc | 9.20 | ccc | 9.20 |
+----------+------------+----------+-----------------+
6 rows in set (0.00 sec)
通过这一步找到了a表和b表query_id相同的记录。
3.然后再在a表中去匹配b表中的记录，如下：
mysql> select a.query_id,b.mq from slow
as a
join
(select query_id,max(query_time) as mq from slow group by query_id)
as b
where
a.query_id=b.query_id
and
a.query_time = b.mq;
+----------+-------+
| query_id | mq |
+----------+-------+
| bbb | 8.38 |
| aaa | 12.09 |
| ccc | 9.20 |
+----------+-------+
3 rows in set (0.00 sec)
4.加上order by语句和limit语句即可。
看着比较简单的一个操作，实际上还是有很多细节的部分，当然，这个sql还可以根据慢日志表的索引结构再进行优化。当我们能够通过sql选出慢日志的sql时，后续的优化也就得心应手了。