一个线上的排行榜SQL问题

AsiaYe

发布于 2019-11-06 17:04:25

4670

发布于 2019-11-06 17:04:25

文章被收录于专栏：DBA随笔

一个线上的排行榜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时，后续的优化也就得心应手了。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-04-11，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库