sql - 如何优化此 SQL 选择查询？

Question

我能够完成此查询，但需要 25 秒。那太长了!如何优化此查询？

SELECT COUNT(DISTINCT u1.User_ID ) AS total
FROM UserClicks u1
INNER JOIN (SELECT DISTINCT User_ID 
              FROM UserClicks 
             WHERE (Date BETWEEN DATE_SUB(:startDate, INTERVAL 1 MONTH) AND :startDate)) u2
            ON u1.User_ID = u2.User_ID
WHERE (u1.Date BETWEEN :startDate AND :endDate)

这是在 MySQL 数据库上使用

Answer 1

SELECT  COUNT(*) AS total
FROM    (
        SELECT  DISTINCT User_ID 
        FROM    UserClicks 
        WHERE   Date BETWEEN DATE_SUB(:startDate, INTERVAL 1 MONTH) AND :startDate
        ) u1
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    UserClicks u2
        WHERE   u2.User_ID = u1.User_ID
                AND u2.Date BETWEEN :startDate AND :endDate
        )

在 (User_ID, Date) 上创建复合索引:

CREATE INDEX ix_userclicks_user_date ON UserClicks (User_ID, Date)

如果您的用户很少但点击次数很多，并且有一个表 Users，您可以使用 Users 表而不是 DISTINCT:

SELECT  COUNT(*)
FROM    Users u
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    UserClicks uc1
        WHERE   uc1.UserId = u.Id
                AND uc1.Date BETWEEN DATE_SUB(:startDate, INTERVAL 1 MONTH) AND :startDate
        )
        AND EXISTS
        (
        SELECT  NULL
        FROM    UserClicks uc2
        WHERE   uc2.UserId = u.Id
                AND u2.Date BETWEEN :startDate AND :endDate
        )