无法聚合包含相关子查询的查询的结果而不会超时

问：

我下面的初始查询在 9 秒左右运行得相当快，并返回大约 250K 条记录。我想按 ID 计数month_year对结果进行分组，但是，任何按日期对结果进行分组或将查询包装为派生表并再次查询的尝试都会导致大约 6 分钟后超时。我认为相关子查询可能正在重新运行。我无法在 mysql 5.7 中使用 CTE，也无法使用初始结果创建临时表。

初始查询 - 运行良好

select
        valid_sellers.uid
        ,(select min(si.created) as 'temp_name' 
            from sell_item si where si.uid = valid_sellers.uid and si.status in ("For Sale", "Sold")  
            having temp_name >= "2023-01-01" and temp_name <= "2023-12-31") as 'first_sales_activity'
from 
    (
        select
            u.uid
        from
            user u
        where
            u.uid in (
                    select
                        t1.uid
                     from
                        (select distinct si.uid from sell_item si) t1)
            and
            u.since < "2023-04-24"
            and
            u.can_sell = "Y"
            and
            u.last_alive_time >= "2023-01-01"
    ) valid_sellers

尝试聚合导致超时

select
        count(valid_sellers.uid)
        ,DATE_FORMAT((select min(si.created) as 'temp_name' 
            from sell_item si where si.uid = valid_sellers.uid and si.status in ("For Sale", "Sold")  
            having temp_name >= "2023-01-01" and temp_name <= "2023-12-31"), '%Y-%m') as 'year_month'
from 
    (
        select
            u.uid
        from
            user u
        where
            u.uid in (
                    select
                        t1.uid
                     from
                        (select distinct si.uid from sell_item si) t1)
            and
            u.since < "2023-04-24"
            and
            u.can_sell = "Y"
            and
            u.last_alive_time >= "2023-01-01"
    ) valid_sellers

group by
    2

第二次尝试聚合导致超时

select
    count(first_listings.uid)
    ,DATE_FORMAT(first_listings.first_sales_activity, '%Y-%m') as 'first_listing'
    
from (
        select
                valid_sellers.uid
                ,(select min(si.created) as 'temp_name' 
                    from sell_item si where si.uid = valid_sellers.uid and si.status in ("For Sale", "Sold")  
                    having temp_name >= "2023-01-01" and temp_name <= "2023-12-31") as 'first_sales_activity'
        from 
            (
                select
                    u.uid
                from
                    user u
                where
                    u.uid in (
                            select
                                t1.uid
                             from
                                (select distinct si.uid from sell_item si) t1)
                    and
                    u.since < "2023-04-24"
                    and
                    u.can_sell = "Y"
                    and
                    u.last_alive_time >= "2023-01-01"
            ) valid_sellers
    
    ) first_listings 

group by
    2

mysql correlated-subquery 派生表

WITH d AS (
    SELECT uid, MIN(created) AS first_sales_activity
    FROM sell_item
    WHERE status IN ("For Sale", "Sold") AND created BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY uid
)

SELECT u.uid, DATE_FORMAT(d.first_sales_activity, '%Y-%m') AS first_sales_activity
FROM users AS u
JOIN d ON u.uid = d.uid
WHERE u.since < '2023-04-014' AND u.can_sell = 'Y' AND u.last_alive_time >= "2023-01-01"

上一个：如果有重做日志和撤消日志，为什么MySQL需要双写缓冲区？

下一个：Laravel Eloquent 和 SQL：对具有最大日期的行进行分组和选择

无法聚合包含相关子查询的查询的结果而不会超时 - mysql 5.7

Unable to Aggregate Results From Query Containing Correlated SubQuery without Timeout - mysql 5.7

评论