导读

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

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

问题描述

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

环境声明

• 系统：$\text{Ubuntu}20.10$
• $\text{CPU}$：$\text{i7-7500u}2.7\text{GHz}$
• 内存：$20\text{G}$
• 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$条街道数据、$75,054,700$条收藏记录生成好了。这么久还是因为笔记本太拉胯了

业务上手

熟悉数据

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

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

SELECT * FROM street LIMIT 10; 

于是输出了这些：

嗷，是id和info。

然后再看看另一张表：

SELECT * FROM star LIMIT 10; 

然后输出了这些：

看起来还不错。

一头莽上去

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

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

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

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

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

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

试查询

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

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

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

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

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

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

然后是惊人的发现：

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

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

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

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

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

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

结果相当惊人：

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

正片开始

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

首先，既然是要推荐，那就是所有的街道所对应的收藏情况。刚刚我们的探索中得出的最佳结论是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; 

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

收藏量排名

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

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$个：

但是时间相当长，甚至直接提升了两倍。因为有两次联立表的查找。如果使用视图将联立查找的结果保存起来，也并没有提高速度，毕竟视图没有任何提速功能。但是视图的存在让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$名，那就是将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; 

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

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

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

是不是优点能理解了呢？