SQL Server 2005适当的索引来过滤30,000,000个寄存器

Question

我对事务表的存储过程有问题，用户有一个 Web 表单来通过多个值查找事务。

该过程花费的时间太长，我不知道如何设置正确的索引。

这是我的存储过程：

CREATE PROCEDURE dbo.cg_searchTransactions
(
    @id_Ent tinyint,
    @transactionTypeID int = NULL,
    @transactionID numeric(18,0) = NULL,
    @channelID int = NULL,
    @transactionDateFrom datetime = NULL,
    @transactionDateTo datetime = NULL,
    @transactionStatusID INT = NULL,
    @documentType INT = NULL,
    @documentNumber varchar(50) = NULL,
    @userName varchar(50) = NULL,
    @accountFromNumber varchar(20) = NULL,
    @accountToNumber varchar(20) = NULL,
    @amountFrom money = NULL,
    @amountTo money = NULL,
    @correlationID varchar(30) = NULL,
    @externalReference varchar(20) = NULL,
    @externalReference2 varchar(20) = NULL,
    @PageIndex INT = 1, 
    @PageSize INT = 20
)
AS
BEGIN
    SET NOCOUNT ON


        DECLARE @QUERY VARCHAR(MAX)
        SET @QUERY = '
            WITH Trans AS (
            SELECT
                ROW_NUMBER() OVER (ORDER BY transactionID DESC) AS Row,
                T.id_Ent,
                T.transactionID,
                T.trnTypeCurrencyID,
                T.transactionDate,
                T.transactionStatusID,
                T.documentType,
                T.documentNumber,
                T.childDocumentType,
                T.childDocumentNumber,
                T.userName,
                T.accountFromNumber,
                T.accountFromType,
                T.accountFromCurrency,
                T.accountDescriptionFrom,
                T.costCenterFrom,
                T.subtotalFrom,
                T.taxamountFrom,
                T.taxamountFrom2,
                T.amountFrom,
                T.accountToNumber,
                T.accountToType,
                T.accountToCurrency,
                T.accountDescriptionTo,
                T.costCenterTo,
                T.subtotalTo,
                T.taxamountTo,
                T.taxamountTo2,
                T.amountTo,
                T.exchangeCurrency,
                T.traderAuthNumber,
                T.benefContractNumber,
                T.contractNumber,
                T.merchantID,
                T.creditCardAuthorizationNumber,
                T.comment,
                T.companyServiceCommision,
                T.usercommission,
                T.companyServiceAuthorizationNumber,
                T.customerBranchId,
                T.correlationID,
                T.transactionStartTime,
                T.transactionEndTime,
                T.enlapsedTime,
                T.serverName,
                T.externalReference,
                T.externalReference2,
                T.externalTrxType,
                T.beneficiaryName,

                C.shortName AS ChannelsShortName,
                TT.shortName AS TransactionTypesShortName,
                TS.shortName AS TransactionStatusDefShortName,
                DT.shortName AS DocumentTypesShortName,
                CDT.shortName AS ChildDocumentTypesShortName,
                AFT.shortName AS AccountTypesShortNameFrom,
                ATT.shortName AS AccountTypesShortNameTo,
                CURF.shortName AS CurrenciesShortNameFrom,
                CURT.shortName AS CurrenciesShortNameTo
            FROM
                Transactions T (NOLOCK) 

                    INNER JOIN TransactionTypesCurrencies TTC
                        ON  T.id_Ent = TTC.id_Ent
                            AND T.trnTypeCurrencyID = TTC.trnTypeCurrencyID

                        INNER JOIN Channels C
                            ON  TTC.id_Ent = C.id_Ent
                                AND TTC.channelID = C.ID

                        INNER JOIN TransactionTypes TT
                            ON  TTC.id_Ent = TT.id_Ent
                                AND TTC.transactionTypeID = TT.transactionTypeID

                    INNER JOIN TransactionStatusDef TS
                        ON  T.id_Ent = TS.ent_Ent
                            AND T.transactionStatusID = TS.ID

                    INNER JOIN DocumentTypes DT
                        ON  T.id_Ent = DT.id_Ent
                            AND T.documentType = DT.ID

                    INNER JOIN DocumentTypes CDT
                        ON  T.id_Ent = CDT.id_Ent
                            AND T.childDocumentType = CDT.ID

                    INNER JOIN AccountTypes AFT
                        ON  T.id_Ent = AFT.id_Ent
                            AND T.accountFromType = AFT.ID

                    INNER JOIN AccountTypes ATT
                        ON  T.id_Ent = ATT.id_Ent
                            AND T.accountToType = ATT.ID

                    INNER JOIN Currencies CURF
                        ON  T.id_Ent = CURF.id_Ent
                            AND T.accountFromCurrency = CURF.ID

                    INNER JOIN Currencies CURT
                        ON  T.id_Ent = CURT.id_Ent
                            AND T.accountToCurrency = CURT.ID
            WHERE 
                T.id_Ent = ' + CONVERT(VARCHAR,@id_Ent)
                IF NOT @transactionDateFrom IS NULL
                    SET @QUERY = @QUERY + ' AND T.transactionDate >= ''' + CONVERT(VARCHAR,@transactionDateFrom,121) + ''''

                IF NOT @transactionDateTo IS NULL
                    SET @QUERY = @QUERY + ' AND T.transactionDate <= ''' + CONVERT(VARCHAR,@transactionDateTo,121) + ''''

                IF NOT @transactionStatusID IS NULL
                    SET @QUERY = @QUERY + ' AND T.transactionStatusID = ' + CONVERT(VARCHAR,@transactionStatusID)

                IF NOT @documentType IS NULL
                    SET @QUERY = @QUERY + ' AND T.documentType = ' + CONVERT(VARCHAR,@documentType)

                IF NOT @userName IS NULL
                    SET @QUERY = @QUERY + ' AND T.userName = ''' + @userName + ''''

                IF NOT @documentNumber IS NULL
                    SET @QUERY = @QUERY + ' AND T.documentNumber = ''' + @documentNumber + ''''

                IF NOT @accountFromNumber IS NULL
                    SET @QUERY = @QUERY + ' AND T.accountFromNumber = ''' + @accountFromNumber + ''''

                IF NOT @accountToNumber IS NULL
                    SET @QUERY = @QUERY + ' AND T.accountToNumber = ''' + @accountToNumber + ''''

                IF NOT @amountFrom IS NULL
                    SET @QUERY = @QUERY + ' AND T.amountTo >= ' + CONVERT(VARCHAR,@amountFrom)

                IF NOT @amountTo IS NULL
                    SET @QUERY = @QUERY + ' AND T.amountTo <= ' + CONVERT(VARCHAR,@amountTo)

                IF NOT @correlationID IS NULL
                    SET @QUERY = @QUERY + ' AND T.correlationID = ''' + @correlationID + ''''

                IF NOT @externalReference IS NULL
                    SET @QUERY = @QUERY + ' AND T.externalReference = ''' + @externalReference + ''''

                IF NOT @externalReference2 IS NULL
                    SET @QUERY = @QUERY + ' AND T.externalReference2 = ''' + @externalReference2 + ''''

                IF NOT @channelID IS NULL
                    SET @QUERY = @QUERY + ' AND C.ID = ' + CONVERT(VARCHAR,@channelID)

                IF NOT @transactionTypeID IS NULL
                    SET @QUERY = @QUERY + ' AND TT.transactionTypeID = ' + CONVERT(VARCHAR,@transactionTypeID)

            SET @QUERY = @QUERY + ')'
            SET @QUERY = @QUERY + 'SELECT * FROM Trans WHERE Row BETWEEN (' + CONVERT(VARCHAR,@PageIndex) + ' - 1) * ' + CONVERT(VARCHAR,@PageSize) + ' + 1 AND ' + CONVERT(VARCHAR,@PageIndex) + '*' + CONVERT(VARCHAR,@PageSize)

            SET @QUERY = @QUERY + 'OPTION (FAST 1)'

            EXEC(@QUERY)

END

Answer 1

您只需要在 WHERE 子句中使用的所有字段（即 transactionDate、transactionStatusID 等）创建单独的索引。如果您有 < code>id_ent 作为附加过滤器，将其作为前导列：

CREATE INDEX ix_transaction_transactionDate ON transaction (id_ent, transactionDate)
CREATE INDEX ix_transaction_transactionStatusID ON transaction (id_ent, transactionStatusID)
-- etc.

请注意，对于每个查询，仅使用一个索引，SQL Server 将尝试选择最合适的索引（最有选择性的）。

另请注意，在生产表上放置 NOLOCK 提示是一个非常糟糕的主意。您可以在单个查询中进行脏读。

如果 id_ent 是所有表中 PRIMARY KEY 的一部分，则最好将其替换为常量。此查询：

SELECT  *
FROM    Transactions t
JOIN    TransactionTypesCurrencies ttc
ON      ttc.trnTypeCurrencyID = t.trnTypeCurrencyID 
WHERE   t.id_ent = @id_ent
        AND ttc.id_ent = @id_ent

通常比此查询： 更好

SELECT  *
FROM    Transactions t
JOIN    TransactionTypesCurrencies ttc
ON      ttc.id_ent = t.id_ent
        AND ttc.trnTypeCurrencyID = t.trnTypeCurrencyID 
WHERE   t.id_ent = @id_ent

，因为可以完成早期过滤。

如果您只有一个 id_ent 值，这没有什么区别，但如果您添加另一个值，则需要付出代价。

更新：

如果您的重复查询根据多个条件进行筛选并且速度很慢，则可以考虑针对多个条件创建额外的复合索引。

请参阅我博客中的这篇文章，了解有关如何执行此操作的一些建议：

• 创建索引

Answer 2

您可以通过使用探查器记录工作负载来获取一些经验信息，然后使用针对该工作负载的索引调整向导来确定最能处理该工作负载的索引。

创建的索引越多，插入要做的工作就越多，因此为正在搜索的所有内容创建索引可能不是一个好主意。

Answer 3

我发现在这种情况下，创建临时表比创建公用表表达式更快。这还允许您返回寻呼的总数。

Answer 4

在创建索引之前我问自己的问题：

1. 这个表（或多个表）是只读的还是读写的？

ReadWrite - 每次对表进行更新/删除时，索引都会更新。“选择”可能很快，但插入、更新和删除很慢。 引用 MS“如果您有大量表上索引的数量，就会增加优化器为查询计划选择次优索引的机会。”

2. 您正在动态创建查询。我要做的是尝试在 QA（或特定时间的生产数据库）上运行跟踪，并查看用户尝试运行的内容。您可以将数据库从生产环境转储到沙箱/尝试诸如索引调整向导之类的工具（它可以告诉您需要哪些索引），SQL DMV 等来查找瓶颈所在。问题不仅仅出在这个 SP 上，还可能存在死锁、临时表/临时数据库的不当使用等。

3. 如果您有足够的信心认为该表是罪魁祸首，您还应该尝试对表进行水平分区< /p>

4. 当您执行任何查询时，请查看执行计划并查找表扫描 - 这通常意味着某些索引是缺失

5. 阅读，阅读和阅读。