带有 where 子句和 group by 的 SQL max() 函数不能有效地使用索引

Question

我有一个表 MYTABLE，大约有 25 列，其中两列是 USERID(整数) 和 USERDATETIME(日期时间)。

我在该表的这两列上有一个索引，USERID 是第一列，后面是 USERDATETIME。

我想获取每个 USERID 的最大 USERDATETIME。所以:

select USERID,MAX(USERDATETIME) 
from MYTABLE WHERE USERDATETIME < '2015-10-11'
GROUP BY USERID

我希望优化器能够找到每个唯一的USERID和最大USERDATETIME，其查找次数等于唯一USERID<的数量s。我希望这会相当快。我的 myTable 中有 2000 个用户 ID 和 600 万行。然而，实际计划显示索引扫描有 600 万行。如果我使用带有 USERDATETIME/USERID 的索引，计划将更改为使用索引查找，但仍然有 600 万行。

为什么 SQL 不使用索引来减少处理的行数？

Answer 1

如果您使用的是 SQL Server，则这不是产品通常执行的优化(有限情况除外 where the table is partitioned by that value )。

但是您可以使用 the technique from here 手动执行此操作

CREATE TABLE YourTable
  (
     USERID       INT,
     USERDATETIME DATETIME,
     OtherColumns CHAR(10)
  )

CREATE CLUSTERED INDEX IX
  ON YourTable(USERID ASC, USERDATETIME ASC);

<小时/>

WITH R
     AS (SELECT TOP 1 USERID,
                      USERDATETIME
         FROM   YourTable
         ORDER  BY USERID DESC,
                   USERDATETIME DESC
         UNION ALL
         SELECT SubQuery.USERID,
                SubQuery.USERDATETIME
         FROM   (SELECT T.USERID,
                        T.USERDATETIME,
                        rn = ROW_NUMBER()
                               OVER (
                                 ORDER BY T.USERID DESC, T.USERDATETIME DESC)
                 FROM   R
                        JOIN YourTable T
                          ON T.USERID < R.USERID) AS SubQuery
         WHERE  SubQuery.rn = 1)
SELECT *
FROM   R

如果您有另一个包含 UserIds 的表，则可以使用

更轻松地获得有效的计划

SELECT U.USERID,
       CA.USERDATETIME
FROM   Users U
       CROSS APPLY (SELECT TOP 1 USERDATETIME
                    FROM   YourTable Y
                    WHERE  Y.USERID = U.USERID
                    ORDER  BY USERDATETIME DESC) CA