为什么加入CTE这么慢？

Question

我有一个这样的查询

WITH 
events AS (
    SELECT * 
    FROM Table1 ae
    WHERE '2022-04-21 06:25:22' >= ae.Stamp
    AND ae.Stamp > '2022-04-21 05:25:22'
),
users AS 
(
    SELECT User,COUNT(*) AS cnt 
    FROM events 
    GROUP BY User
    HAVING cnt > 60
)
SELECT *
FROM events e join users u on e.User = u.User

执行得非常慢。执行两个CTE大约需要13s，事件包含3009条记录，用户包含2条记录。但是，如下图所示，运行查询需要超过30分钟:-O

如果我将最后一行更改为显式比较用户表中的用户

FROM events e join users u on e.User in ('User1','User2')

查询在22秒内执行！

为什么连接这么慢？是否有更好的方法来实现查询？

谢谢

-更新

基于SOS的输入，我将查询更改为

WITH 
events AS (
    SELECT *,
    COUNT(*) OVER(PARTITION BY User) AS TotalUser 
    FROM Table1 ae
    WHERE '2022-04-21 06:25:22' >= ae.Stamp
    AND ae.Stamp > '2022-04-21 05:25:22'
)
SELECT *
FROM events e 
WHERE TotalUser > 60

现在执行时间为6s (在相同的数据上) :-)。

Answer 1

尝试创建此索引。

CREATE INDEX ts_user ON Table1 (Stamp, User)

它应该同时帮助日期范围筛选器和组。

如果您使用SSMS -微软的-这个技巧是为您准备的:在查询窗口中右键单击，然后选择Show实际执行计划，然后运行查询。执行计划显示有时建议创建一个新索引。

Answer 2

看看这是否更有效：

SELECT u.*, ae.cnt
  FROM (
    SELECT User, COUNT(*) AS cnt
        FROM Table1
        WHERE Stamp >  '2022-04-21 05:25:22'
          AND Stamp <= '2022-04-21 05:25:22' + INTERVAL 1 HOUR 
        GROUP BY User
        HAVING cnt > 60
     ) AS ae
  JOIN Users AS u  USING(User)

可能的索引：

Table1:  INDEX(Stamp, User)
Users:   PRIMARY KEY(User) or INDEX(User), but not both