索引更新表太慢 [英] Update table with index is too slow

问题描述

我正在应用程序的实时系统上观看Profiler，我发现有一条更新指令需要我们定期运行(每秒)，这非常慢.每次大约花费400毫秒. 查询中包含此更新(这是最慢的部分)

I was watching the Profiler on a live system of our application and I saw that there was an update instruction that we run periodically (every second) that was quite slow. It took around 400ms every time. The query includes this update (which is the slow part)

UPDATE BufferTable
    SET LrbCount = LrbCount + 1,
    LrbUpdated = getdate()
WHERE LrbId = @LrbId

这是桌子

CREATE TABLE BufferTable(
    LrbId [bigint] IDENTITY(1,1) NOT NULL,
        ...
    LrbInserted [datetime] NOT NULL,
    LrbProcessed [bit] NOT NULL,
    LrbUpdated [datetime] NOT NULL,
    LrbCount [tinyint] NOT NULL,
)

该表具有2个索引(非唯一和非聚簇)，其字段按以下顺序排列:
* Index1 -(LrbProcessed，LrbCount)
* Index2 -(LrbInserted，LrbCount，LrbProcessed)

The table has 2 indexes (non unique and non clustered) with the fields by this order:
* Index1 - (LrbProcessed, LrbCount)
* Index2 - (LrbInserted, LrbCount, LrbProcessed)

当我查看此内容时，我认为问题可能出在 Index1 上，因为LrbCount发生了很大变化，并且改变了索引中数据的顺序.
但是在停用 index1 之后，我看到查询所花的时间与最初的时间相同. 然后，我重建了 index1 并停用了 index2 ，这一次查询非常快.

When I looked at this I thought that the problem would come from Index1 since LrbCount is changing a lot and it changes the order of the data in the index.
But after desactivating index1 I saw the query was taking the same time as initially. Then I rebuilt index1 and desactivated index2, this time the query was very fast.

在我看来， Index2 应该更新得更快，因为LrbInserted时间没有改变，因此数据的顺序也不应改变.

It seems to me that Index2 should be faster to update, the order of the data shouldn't change since the LrbInserted time is not changed.

有人可以解释为什么 index2 要比 index1 更新得多吗?

Can someone explain why index2 is much heavier to update then index1?

谢谢！

编辑

我只是意识到自己承担了错误的事情.
完整查询的另一部分是造成延迟的原因:

I just realized I was assuming the wrong thing.
The complete query has another part that is responsible for the delay:

DECLARE @LrbId as bigint
SELECT TOP 1 @LrbId = LrbId
FROM Buffertable
WHERE LrbProcessed = 0
    AND LrbCount < 5
ORDER BY LrbInserted

因此，很可能与Sql引擎的错误决定有关，要使用哪个索引.
对困惑感到抱歉.我想我们可以解决这个问题.

So, most probably it's related with a bad decision from the Sql engine on which index to use.
Sorry for the confusion. I guess we can close this question.

推荐答案

有人可以解释为什么index2要比index1重得多吗?

Can someone explain why index2 is much heavier to update then index1?

index2更长:密钥大小是10个字节(8 +1 + 1)，而不是2(1 +1)

index2 is much longer: key size is 10 bytes (8 + 1 + 1) rather than 2 (1 + 1)

可能它不适合缓存，需要进行页面查找才能找到记录.

Probably it does not fit into the cache and a page lookup is needed to locate the records.

你的桌子有多大?

您可能还希望启用I/O统计信息:

You may also want to enable I/O statistics:

SET STATISTICS IO ON

，运行查询几次，然后在输出中查看物理页读取的数量.

, run the query a couple of times and see the number of physical page reads in the output.

更新:

对于此查询:

SELECT TOP 1 @LrbId = LrbId
FROM   Buffertable
WHERE  LrbProcessed = 0
       AND LrbCount < 5
ORDER BY
       LrbInserted

要快速工作，请创建以下索引:

to work fast, create the following index:

CREATE INDEX ix_buffertable_p_c_i ON BufferTable (LrbProcessed, lrbCount, LrbInserted)

并重写查询:

WITH    cts (cnt) AS
        (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    cts
        WHERE   cnt < 5
        )
SELECT  TOP 1 bt.*
FROM    cts
CROSS APPLY
        (
        SELECT  TOP 1 bti.*
        FROM    BufferTable bti
        WHERE   LrbProcessed = 0
                AND LrbCount = cts.cnt
        ORDER BY
                LrbInserted
        ) bt
ORDER BY
        LrbInserted

这篇关于索引更新表太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！