对大量数据选择 Count(*)

Question

我想对报告执行此操作，但我的表中有 20,000,000 条记录，这会导致我的应用程序超时。

SELECT
        T.transactionStatusID,
        TS.shortName AS TransactionStatusDefShortName,
        count(*) AS qtyTransactions
    FROM
        Transactions T 

    INNER JOIN TransactionTypesCurrencies TTC
                ON  T.id_Ent = TTC.id_Ent
                    AND T.trnTypeCurrencyID = TTC.trnTypeCurrencyID
            INNER JOIN TransactionStatusDef TS
                ON  T.id_Ent = TS.ent_Ent
                AND T.transactionStatusID = TS.ID
WHERE
    T.id_Ent = @id_Ent
GROUP BY
        T.transactionStatusID,
        TS.shortName

据我所知 COUNT(*) 会导致全表扫描，这使我的查询花费太多时间，我使用 MS SQL 2005，有什么帮助吗？

编辑：

项目负责人告诉我，查询只是一天，它可以提供帮助吗？

Answer 1

据我所知，COUNT(*) 会导致全表扫描，这会使我的查询花费太多时间，我正在使用 MS SQL 2005，有什么帮助吗？

COUNT(*) 可以使用任何能够给出答案的来源，其中包括索引。

在您的情况下，我将使用 trnTypeCurrencyID 在 (id_ent, transactionStatusID) 上创建一个覆盖索引：

CREATE INDEX ON Transactions (id_ent, transactionStatusID) INCLUDE (trnTypeCurrencyID)

并稍微重写查询：

SELECT  transactionStatusID, qtyTransactions, TS.shortName
FROM    (
        SELECT  T.transactionStatusID,
                COUNT(*) AS qtyTransactions
        FROM    Transactions T
        JOIN    TransactionTypesCurrencies TTC
        ON      TTC.id_Ent = T.id_Ent
                AND TTC.trnTypeCurrencyID = T.trnTypeCurrencyID
        WHERE   T.id_Ent = @id_Ent
        GROUP BY
                T.transactionStatusID
        ) TD
JOIN    TransactionStatusDef TS
ON      TS.ent_Ent = @id_Ent
        AND TS.ID = TD.transactionStatusID

索引将在 上进行过滤id_ent 并在 transactionStatusID 上并行化。由于您已经覆盖了 trnTypeCurrencyID，引擎将不必查找表中的值，它已经存在于索引中。

GROUP BY 子句还仅包含索引中的列，因此它的并行性更好。

更新：

通过添加WITH (ONLINE = ON)，您可以在创建索引期间使表保持可运行状态：

CREATE INDEX ON Transactions (id_ent, transactionStatusID) INCLUDE (trnTypeCurrencyID) WITH (ONLINE = ON)

Answer 2

如果您查看查询的执行计划，就会突出显示表现不佳的部分。它会告诉您是否正在执行表扫描、索引扫描或索引查找。
所以这是开始寻找的最佳位置。

您现在有索引吗？ JOIN 和 WHERE 子句中涉及的字段是主要候选者 - 如果您没有索引，这将是一个主要因素。

Answer 3

你尝试过

COUNT(1)

吗？

另外，是否需要加入TransactionTypesCurrency，您似乎没有使用其中的任何内容？

Answer 4

事务表上的聚集索引是什么？还存在哪些其他索引？您可以尝试此查询来消除一个连接：

SELECT
    T.TransactionStatusID,
    TS.ShortName,
    qtyTransactions = COUNT(*)
FROM
    dbo.Transactions AS T
INNER JOIN
    dbo.TransactionStatusDef AS TS
    ON T.id_Ent = TS.ent_Ent
    AND T.transactionStatusID = TS.ID
WHERE EXISTS
(
    SELECT 1
        FROM do.TransactionTypeCurrencies AS TTC
        WHERE TTC.id_Ent = T.id_Ent
        AND TTC.trnTypeCurrencyID = T.trnTypeCurrencyID
)
AND T.id_Ent = @id_Ent
GROUP BY
    T.transactionStatusID,
    TS.shortName;

您还可以尝试在发出查询之前使用快照隔离，例如，

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

要执行此操作，您必须：

ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON;

一般来说，尽管您希望确保索引正确。如果您无法为此查询的表应用适当的索引，因为这会损害其他查询，那么您可以考虑使用索引视图来为您维护此计数（以插入/更新性能为代价），或者如果您需要表分区在企业版上，或者偶尔在后台运行此数据的汇总，以便您的应用程序不必等待它（假设实际计数有点过时是可以的）。