简单的SQL查询需要20分钟才能运行吗？

Question

我有一个查询，它根据一个总数输出一个百分比列表，我唯一弄不清楚的部分是一种有效的方法来过滤“usid”等于另一个表上的值。

查询没有失败，但需要很长时间才能完成。

    SELECT badge, count(usid)*100 / (SELECT COUNT(DISTINCT usid) from Table1)
    FROM Table1
    WHERE usid IN(
        SELECT usid
        FROM Table2
        WHERE msid = 1
        )
    GROUP BY badge

输出结果如下所示

    -----------------------------
    badge        count
    -----------------------------
    1            65.1
    2            45.4
    3            22.7
    4            12.12

它正在计算的usid，我试图将其设置为usid，其中msid = 1，即使这个方法有效，也要花费太长的时间。有什么好主意吗？

Answer 1

您应该能够使用显式连接表示法而不是IN子句：

SELECT a.badge, COUNT(a.usid)*100 / (SELECT COUNT(DISTINCT usid) from Table1)
  FROM Table1 AS a
  JOIN (SELECT DISTINCT usid FROM Table2 WHERE msid = 1) AS b ON a.usid = b.usid
 GROUP BY a.badge

但是，我不相信这会解决性能问题。半途而废的优化器将意识到选择列表中的子选择是恒定的，但是您应该通过查看查询计划来验证优化器是半途而废(或更好)。

在这种情况下，我不相信COUNT(a.usid)做任何与COUNT(*)不同的事情。只有当a.usid可以包含空值时，它才会产生不同的答案。另见 — which is better?

Answer 2

这不是一个简单的查询。根据您正在使用的数据库，in可能效率很低，并且每个输出行都在计算count(distinct)。请尝试将查询重写为：

SELECT badge, count(usid)*100 / x.cnt
FROM Table1 t1 cross join
     (SELECT COUNT(DISTINCT usid) as cnt from Table1) x
WHERE exists (select 1
              from table2 t2
              where t2.usid = t1.usid and t2.msid = 1
             )
GROUP BY t1.badge, x.cnt;

不管您使用的是哪个数据库，这个查询可能会更快。

顺便说一句，你正在计算count(usid)，然后除以count(distinct usid)，这是可疑的。我希望两者兼而有之，或者两者都不是count(distinct)。

Answer 3

关于加速sql的一般经验规则：

1. 只返回所需的最小字段
2. 使用分页-所以您提供一个偏移量和限制，并获得一页的数据。
3. 或者，上限在某个合理的截止点返回数据。(您将只看到搜索的前500个结果，然后用户需要改进搜索参数)否则，有人可以运行一个开放的查询，并给系统带来极大的负担。
4. 避免IN语句
5. 避免嵌套查询
6. 在联接/搜索字段中添加索引(按查询中列出的顺序排列)
7. 如果可能的话，使用数字而不是字符串
8. 如果不需要，请避免联接(您也可以对数据库进行去denormalize )
9. 如果可能的话，预先计算信息(如和)并将这些信息存储在另一个表或字段中。可以对相关数据的插入/更新事件进行更新。