SQL入门：在MySQL中使用SQL语句实现收藏排行功能

你的名字 2022-10-29 01:47 29阅读 0赞

### 文章目录 ###

*  导读
 *  问题描述
 *  环境声明
 *  数据准备
 *  业务上手
 *   *  熟悉数据
     *  一头莽上去
     *  试查询
 *  正片开始
 *   *  收藏数统计
     *  收藏量排名

# 导读 #

这次有个同学问我`SQL`的问题，我发现这个问题虽然简单，但是涉及了很多很细节的知识点，所以就感觉相当有意思，于是记录一下。

在这里，为了**方便**，数据字段设计得相当简单；同时，为了保证**数据安全**，所有的数据全部都相当随意。还请各位读者不要介意。

# 问题描述 #

为了生成最多人走的路，你的`GIS`系统需要记录每条路的收藏情况，并通过分析**收藏排行榜**对该用户的下次导航或者另一位用户的导航进行最佳路径推荐。

# 环境声明 #

*  系统： Ubuntu 20.10 \\text\{Ubuntu\}20.10 Ubuntu20.10
 *   CPU \\text\{CPU\} CPU： i7-7500u   2.7 GHz \\text\{i7-7500u\}\\ \\ 2.7\\text\{GHz\} i7-7500u  2.7GHz
 *  内存： 20 G 20\\text\{G\} 20G
 *  `MySQL`：8.0.23，运行在`Docker`中

# 数据准备 #

为了简单，这里之设计两张表：包含街道编号和街道信息的**街道表**、包含街道编号和用户编号的**用户表**。

我们用存储过程进行生成：

-- 事先准备
    DROP PROCEDURE IF EXISTS insert_street_batches;
    DELETE FROM street WHERE id >= 0;
    DELETE FROM star WHERE street >= 0;
    -- 存储过程
    CREATE PROCEDURE insert_street_batches()
    BEGIN
    DECLARE i, j, seed BIGINT DEFAULT 0;
    SET i = 0;
    SET seed = 0;
    START TRANSACTION;
    -- 正片开始
    WHILE i < 30000 DO
      -- 生成3w个街道数据
      INSERT INTO street (id, info) VALUES (i, concat('000', i));
      SET i = i + 1;
      -- 随机生成每个街道的收藏人数
      SET seed = FLOOR(RAND()*5000);
      SET j = 0;
      -- 生成收藏数据
      WHILE j <= seed DO
        INSERT INTO star (street, `user`) VALUES (i, j);
        SET j = j + 1;
      END WHILE;
    END WHILE;
    -- 提交修改
    COMMIT;
    END;
    -- 调用
    CALL insert_street_batches();

经过了半个小时后，终于， 30 , 000 30,000 30,000条街道数据、 75 , 054 , 700 75,054,700 75,054,700条收藏记录生成好了。这么久还是因为笔记本太拉胯了

# 业务上手 #

## 熟悉数据 ##

我们不妨先研究一下表中含有哪些属性：

*虽然我很熟悉，但还是走个流程，假装我是后来被招进来做维护的*。

SELECT * FROM street LIMIT 10;

于是输出了这些：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center]

嗷，是`id`和`info`。

然后再看看另一张表：

SELECT * FROM star LIMIT 10;

然后输出了这些：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 1]

看起来还不错。

## 一头莽上去 ##

如果你是个`SQL`初学者，那么一定会使用的就是`WHERE`直接联立两张表，就像这样：

SELECT street.id, street.info, star.`user`
    FROM street, star
    WHERE street.id = star.street;

*注：用一对单引号是单纯为了和MySQL的关键字区分开*

那么，猜一猜一共用了多少时间呢？

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 2]

答案是：将近 30 30 30秒，只处理了 75 , 054 , 700 75,054,700 75,054,700条数据。这还是我的内存相当大的情况，要是内存只有 8 8 8到 16 16 16，那可能需要更久。

由于我使用的是命令行，所以输出花了很多时间。但这部分时间是不算在查询中的。

## 试查询 ##

想都不用想，收藏表绝对是街道表的好几倍长，因为用户和街道是多对多关系，如果有 m m m条街道、 n n n个用户，那么收藏表的数据量高达 m ∗ n m\*n m∗n条，相当恐怖。所以，我们先试着从街道表开始入手：

SELECT street.id, street.info, star.`user` FROM street
    LEFT JOIN star
    ON street.id = star.street;

一些数据库老油子可能对这个比较熟悉，但是很可惜，效率基本就是误差级的变化：

![在这里插入图片描述][20210322130925646.png_pic_center]

也就提升了 2 2 2秒而已，完全不行。不过确实得承认，这 2 2 2秒的优化还是说明减少了相当多的数据量的。

那么，我们试试向右合并？

SELECT street.id, street.info, star.`user`
    FROM street RIGHT JOIN star
    ON street.id = star.street;

然后是惊人的发现：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 3]

不仅严重超时，还出现了大量的`NULL`。这是为什么？

首先，`MySQL`会把优先把主表加载入内存，然后再将待合并表的数据全部合并到另一个表。左合并就是右表所有数据一一根据`ON`条件合并到左表，右合并就恰恰相反。

于是在这里，`street` 表左合并到`star`表的时候，`street`就是主表，`star`表所有数据会根据当前情况全部合并到`street`并生成新表。所以在右合并的时候，`star`成为了主表，而且是 30 , 000 30,000 30,000条数据逐步匹配到 75 , 054 , 700 75,054,700 75,054,700条数据中。显而易见，两者间差别还是相当大的，有将近 8 8 8秒的差距。

这也是我们一直说的**小表驱动大表**真实原因所在。

最后，我们不妨限制一下查询数量，也就是`LIMIT`：

SELECT street.id, street.info, star.`user`
    FROM street LEFT JOIN star
    ON street.id = star.street
    LIMIT 10;

结果相当惊人：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 4]

**几乎没有时间损耗**。相比整表输出，**分页输出**还是相当有用的。

# 正片开始 #

好了，到这里对于数据集的探索基本上就结束了，我们接下来就是正式的工作了。

首先，既然是要推荐，那就是所有的街道所对应的收藏情况。刚刚我们的探索中得出的**最佳结论**是`LEFT JOIN`配合`LIMIT`缩短相当一部分时间。

## 收藏数统计 ##

那一切就好说了。我们把所有的收藏情况按`street`的`id`计个数，再排个序就好了。由于**排序会遍历整个数据库**，所以需不需要全部输出也就没有意义了，于是这里就直接分页：

SELECT street.id, street.info, COUNT(star.`user`) AS stars
    FROM street LEFT JOIN star
    ON street.id = star.street
    GROUP BY street.id
    ORDER BY stars DESC
    LIMIT 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 5]

看起来还是用了相当久的时间。不过数据我们全都拿到了！

## 收藏量排名 ##

那么我们再来排个名吧。这里排名有意思的地方就在于：同样的收藏数应当有并列的排名。来试试：

SELECT
    star_list.street_id,
    star_list.street_info,
    star_list.stars,
    (
      SELECT
      COUNT(DISTINCT star_rate.stars)
      FROM (
        SELECT
        street.id AS street_id,
        street.info AS street_info,
        COUNT(star.`user`) AS stars
        FROM street LEFT JOIN star
        ON street.id = star.street
        GROUP BY street_id
      ) star_rate
      WHERE star_rate.stars > star_list.stars
    ) + 1 AS `rank`
    FROM
    (
      SELECT
      street.id AS street_id,
      street.info AS street_info,
      COUNT(star.`user`) AS stars
      FROM street LEFT JOIN star
      ON street.id = star.street
      GROUP BY street_id
    ) star_list
    ORDER BY stars DESC
    LIMIT 10;

于是我们筛选出了 10 10 10个：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 6]

但是时间相当长，甚至直接提升了两倍。因为有两次联立表的查找。如果使用视图将联立查找的结果保存起来，也并没有提高速度，毕竟视图没有任何提速功能。但是视图的存在让`SQL`语句更简洁却是真的。

所以就直接创建一个视图吧，让`SQL`稍微简单点：

CREATE VIEW star_list AS
    SELECT
    street.id AS street_id,
    street.info AS street_info,
    COUNT(star.`user`) AS stars
    FROM street LEFT JOIN star
    ON street.id = star.street
    GROUP BY street_id

在这里如果不需要前 10 10 10个，而是前 10 10 10名，那就是将`LIMIT`换成`WHERE`就好了（在这里也直接把大坨大坨的联立查找换成视图）：

SELECT
    rank_list.street_id,
    rank_list.street_info,
    rank_list.stars,
    rank_list.`rank`
    FROM
    (
      SELECT
      star_list.street_id,
      star_list.street_info,
      star_list.stars,
      (
        SELECT
        COUNT(DISTINCT star_rate.stars)
        FROM star_list AS star_rate
        WHERE star_rate.stars > star_list.stars
      ) + 1 AS `rank`
      FROM star_list
    ) rank_list
    WHERE rank_list.`rank` <= 10
    ORDER BY stars DESC;

但是这样的话查询的话时间就相当长了：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 7]

超出了数倍的时间。基本上可以确定就是嵌套查询的锅了。但是没有嵌套是**不允许**使用`rank`作为`WHERE`筛选对象的，所以没有办法了。

到这里，基本上就是尾声了。不过，这还只是数据库本身最基础的算力，不涉及临时表查询、索引优化等等内容，只有`MySQL`最低限度的默认主键索引。所以，到这一步就只能算入门。往后的十倍数量级优化、内核改写都是相当高级的，等待以后再继续探索了。

是不是优点能理解了呢？

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center]: /images/20221024/393579b66b354da68aca37637b35a3e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221024/e0987a0796e64cf9a003437f0f80c710.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221024/8e25f480b36b479c85a771c048fc2bdd.png
[20210322130925646.png_pic_center]: /images/20221024/d59979c5975e44bab1e31755f03549fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221024/6aa8fed25d5448c797205b3171c8a2ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221024/1bbae7b408224e3da904b8b8f00dc6b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221024/359c8c23c8d84fe983e50c3c120624be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221024/89debde58b0948dbbe5ca05a8b5d28ae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29yZGluYXJ5X2Jyb255_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221024/60dfa04f6d6344fbae8e8d110cc2976f.png