SQL中带有where子句和group by的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数量的。我认为这应该很快。我的表中有2000个用户id和600万行数据。然而，实际执行计划显示从索引扫描中获取了600万行。如果我使用一个带有USERDATETIME/USERID的索引，则计划更改为使用索引查找，但仍然处理了600万行。

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

- Mike

---

你使用的是哪个数据库管理系统（DBM）？ - user330315

“WHERE USERDATETIME < '2015-10-11'”有多少行？ - Lamak

什么类型的索引？ - devlin carnate

Answer 1

如果您正在使用SQL Server，通常情况下该产品不会进行此优化（除非表按该值进行分区的有限情况where the table is partitioned by that value）。

但是，您可以使用从这里学到的技术手动执行此操作。

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

如果您有另一张包含用户ID的表格，那么使用它可以更容易地得到一个高效的计划。

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 

- Martin Smith

---

@Lamak - 据我所知，这个名字是由Paul White创造的。 - Martin Smith

CROSS APPLY的效果很好。由于一些用户没有任何数据，我们仍然希望在列表中看到他们，所以我不得不将其更改为OUTER APPLY。将日期索引更改为升序或降序并没有起到帮助作用。删除where子句（在大多数情况下也没有过滤太多）也没有起到帮助作用。 - Mike

@Mike - 如果这些用户不在那个表中，即使在原始的GROUP BY查询中，它们也不会出现。 - Martin Smith

Answer 2

WHERE子句是使用索引限制查询的关键因素。

在标准SQL Server查询中，索引用于快速选择记录（该索引允许），以及限制返回的记录（该索引不允许）。那么，为什么这个索引不允许快速限制呢？

当查询优化器考虑基于WHERE子句的优化时，它会寻找一个以WHERE子句中的项开头的索引，或者一个可以有效地识别允许（或不允许）出现在结果集中的记录的索引。

对于这个索引，服务器首先可以找到涉及的不同用户ID。然后，它希望根据WHERE子句限制要考虑的行。但是，为了做到这一点，优化器可能会估计，在定位用户ID之后，它将不得不进行等效于完整索引或表扫描。

可能的替代策略是扫描索引，同时识别用户ID和日期。这就是优化器选择的方法。

解决这个问题的一个可能的方法是使用不同的索引 - 其中一个是按日期排序，然后按用户ID排序 - 除了正在使用的索引之外。这将限制被扫描的记录数，以识别userID的最大值，因此会更快一些。

请注意，如果您不需要WHERE子句，那么索引将会快速。但是，WHERE子句要求优化器考虑使用情况，其中WHERE子句将选择的项目限制为最后一行。

此外，日期字段按降序排列的索引也可能更有效率。

- Laughing Vergil

---

此外，您可能希望更新涉及的列的统计信息。如果平均每个用户ID有50个日期，则优化器会做出不同的选择，而如果平均每个ID只有两个或三个日期，则情况就会不同。 - Laughing Vergil