最近碰到的一个需求：一个系统内有基于访问网页路径的权限设置，而每次操作后后台都会留下相应记录，现在要把这些记录取出来显示，按照出现的次数排序，即经常访问的路径排在前面，如果路径配置了权限那就同时显示权限名。 留下的操作记录只记录了路径，并没有记录对应路径的权限名称，需要在另外的表里查询。

虽然可以经过几次简单查询拼接，但是我决定尝试直接用一句 SQL 取出所有需要的数据，于是写出了一个比较复杂（可能算不上困难和复杂，但这是到目前为止我写过最复杂的 SQL 了……）的 SQL 语句。经过几次调整最终如下：

SELECT perm_name, path, perm_count FROM tb_permissions RIGHT JOIN (SELECT path, count() as perm_count FROM tb_operations WHERE created_time > ? AND created_time < ? GROUP BY path HAVING count() > ? ORDER BY count(*) DESC) operation ON convert(path USING utf8) collate utf8_unicode_ci LIKE perm_path ORDER BY perm_count DESC LIMIT ?;

(其中表名、字段名脱敏)

看起来可能有点难以理解，不过这其实是经过几次修改最终得到的结果，中间还有一次错误的写法。

perm_name 和 perm_path 记录在 tb_permissions 中，分别表示权限名和权限路径。而 tb_operations 表则记录着每一次请求、操作的信息，其中自然有网页路径 path。并非所有 path 都有对应的 perm_name 对应，所以采用外连接的方式整合两张表。

只统计数量再倒序排序并不难，GROUP BY 即可搞定，最初的查询语句如下：

SELECT path, count(*) FROM tb_operations
WHERE created_time > ? AND created_time < ?
GROUP BY path HAVING count(*) > ?
ORDER BY count(*) DESC LIMIT ?;

简单，明了。于是我不加思索的开始左连接。

SELECT path, tb_permissions.perm_name, count(*) FROM tb_operations
WHERE created_time > ? AND created_time < ?
LEFT JOIN tb_permissions
ON convert(path USING utf8) collate utf8_unicode_ci LIKE perm_path
GROUP BY path HAVING count(*) > ？
ORDER BY count(*) DESC LIMIT ?;

两个表的 path 和 perm_path 有部分对应关系，奈何编码不同，一个是 utf8_general_ci，一个是 utf8_unicode_ci，因此中间有一步转换，写完了，开始跑。

我盯着黑色的控制台，干等了两分钟以上……最终，居然还显示了正确的结果！

问题出在哪里？我试了一下。

mysql> select count(*) from tb_operations;
+———-+
| count(*) |
+———-+
| 828481 |
+———-+
1 row in set (0.19 sec)

mysql> select count(*) from tb_permissions;
+———-+
| count(*) |
+———-+
| 1785 |
+———-+
1 row in set (0.00 sec)

好吧……原因是，这种查询方式先去做了左连接，把 tb_operations 和 tb_permissions 拼起来，拼接了 80 多万条数据后，再进行统计，所以速度如此之慢。

怎么解决这样的错误？思路比较清晰，先统计完，再连接。具体思考细节不表，最终几经调整写出了上面那么一串。

SELECT perm_name, path, perm_count FROM tb_permissions
RIGHT JOIN (
    SELECT path, count(*) as perm_count FROM tb_operations
    WHERE created_time > ? AND created_time < ?
    GROUP BY path HAVING count(*) > ?
    ORDER BY count(*) DESC
) operation
ON convert(path USING utf8) collate utf8_unicode_ci LIKE perm_path
ORDER BY perm_count DESC LIMIT ?;

分一下段看起来还是比较容易理解的，内部构建的临时表应该会先被筛选，一共百余条数据。筛选完成之后右连接 tb_permissions。这样大概一秒左右就筛选完成了，算是……大幅度优化吧。

（并不，只是修正了 bug 而已……）