时间戳

首页 > 解决方案 > 单个子查询很快。添加 1 个子查询很快。但是，将超过 1 个子查询一起添加会使 MySQL 崩溃。怎么修？

mysql - 单个子查询很快。添加 1 个子查询很快。但是，将超过 1 个子查询一起添加会使 MySQL 崩溃。怎么修？

问题描述

我有以下查询来选择过去 4 周内每周至少发布一次的用户：

SELECT DISTINCT(user_id) FROM `posts` WHERE

    user_id IN (SELECT DISTINCT(user_id) FROM `posts` WHERE post_date > ((UNIX_TIMESTAMP()- (604800*0) ) - 604800) AND post_date <= (UNIX_TIMESTAMP()- (604800*0))) AND
    user_id IN (SELECT DISTINCT(user_id) FROM `posts` WHERE post_date > ((UNIX_TIMESTAMP()- (604800*1) ) - 604800) AND post_date <= (UNIX_TIMESTAMP()- (604800*1))) AND
    user_id IN (SELECT DISTINCT(user_id) FROM `posts` WHERE post_date > ((UNIX_TIMESTAMP()- (604800*2) ) - 604800) AND post_date <= (UNIX_TIMESTAMP()- (604800*2))) AND
    user_id IN (SELECT DISTINCT(user_id) FROM `posts` WHERE post_date > ((UNIX_TIMESTAMP()- (604800*3) ) - 604800) AND post_date <= (UNIX_TIMESTAMP()- (604800*3)))

如果我只使用这 4 个子查询中的任何一个来运行查询，它会很快。

如果我单独运行这 4 个子查询中的任何一个，它们都很快。

但是，当我将这些子查询中的一个以上添加在一起时，MySQL 就会挂起。

我该如何解决这个问题，以便考虑到各个部分的速度很快？

（我应该补充一点，在这种特殊情况下，我不能再添加任何索引，所以解决方案不应该涉及索引）。

标签： mysqlcrashsubquerypivot-tablequery-optimization

解决方案

试试看EXPLAIN <query>执行计划，每个子查询可以读取很多行数据。

但我建议为此特定目的重新编写查询，它需要GROUP BY用户和COUNT DISTINCT周为 4，读取表一次就足够了。

SELECT user_id
FROM `posts` 
WHERE post_date > ((UNIX_TIMESTAMP()- (604800*3) ) - 604800)
GROUP BY user_id
HAVING COUNT(DISTINCT 
    (CASE WHEN post_date > ((UNIX_TIMESTAMP()- (604800*0) ) - 604800) AND post_date <= (UNIX_TIMESTAMP()- (604800*0)) THEN 1
        WHEN post_date > ((UNIX_TIMESTAMP()- (604800*1) ) - 604800) AND post_date <= (UNIX_TIMESTAMP()- (604800*1)) THEN 2
        WHEN post_date > ((UNIX_TIMESTAMP()- (604800*2) ) - 604800) AND post_date <= (UNIX_TIMESTAMP()- (604800*2)) THEN 3
        ELSE 4 END)) = 4

关于查询

  • WHERE post_date > ((UNIX_TIMESTAMP()- (604800*3) ) - 604800)仅过滤适用数据
  • CASE WHEN post_date > ((UNIX_TIMESTAMP ...将其post_date转换为第 1、2、3 或 4 周

现在如果有如下数据

+---------+------+
| user_id | week |
+---------+------+
|       1 |    1 |
|       1 |    2 |
|       1 |    3 |
|       1 |    3 |
|       2 |    1 |
|       2 |    2 |
|       2 |    3 |
|       2 |    4 |
+---------+------+

每个有四行user_id，但用户 1 出现在第 1、2、3 周，COUNT DISTINCT week为 3，用户 2 将计为 4

推荐阅读