我有一个用作队列的表:
create table items
(
itemId int, -- PK, identity
status int, -- Possible values: 0 = Pending, 1 = Processing, 2 = Processed
createdAt datetime2,
updatedAt datetime2,
content text
)
生产者将记录添加到队列中,消费者读取它们FIFO:
- 生产者添加
Pending
状态的记录,createdAt
为当前时间 - 消费者选择按
createdAt
排序的处于Pending
状态的记录。消费时,他们将记录标记为Processing
,并将updatedAt
设置为当前时间(使用update/select
CTE( - 处理后,消费者将记录标记为
Processed
- 在处理过程中,消费者可能会崩溃,因此无法将记录标记为
Processed
- 当另一个消费者发现一个记录停留在
Processing
状态超过x分钟(即updatedAt < current_time - x
和status = Processing
(时,他们会将其提取并处理(假设新消费者不会崩溃;( - 该表有大约100万条记录,每天增长约2万条
- 在任何给定时间,将存在<100条
Pending
和Processing
记录
我有两个问题
- 在这种情况下(尤其是最后2点(,以
updatedAt
为包含列的(status, createdAt)
索引是否是一个好的索引
我试过了,它表明索引被命中,执行时间非常快(秒(。然而,我不太确定这个低基数索引(起始列为status
(是否是一个好的索引,因为这样的索引通常被认为是坏的。我想知道它是否适用于我的情况,因为可能值的分布非常不均匀(<1%的记录是Pending
,InProgress
,这是我正在查询的。没有运行任何查询来选择Processed
(。
- 我添加了包含列(
updatedAt
(来支持此筛选器status = Processing and updatedAt < current_time - x
,但我不确定它是否有用。查询规划器关心包含的列还是只关注索引中的列(status, createdAt
(
如果您同时回答了这两个问题,将获得加分;(
索引的主要目的之一是减少从表中读取的行数。基数索引低意味着一列只接受少数值。因此,如果一个表有1000万行,并且有10个值,那么每个值平均会有100万行。
索引对于从一千万行的表中提取一百万行没有帮助,因为每个(或几乎每个(数据页都有一个匹配的行。其目的是减少正在读取的数据页的数量。
因此,您使用索引是非常合理的,因为您只找到了几行。您正在使用索引来查找未处理的行,其中很少有这样的行。
您的索引比需要的大得多,因为它包含有关已处理行的信息。您可以考虑使用筛选索引。根据你的描述,我认为这将是:
create index idx_items_status_updated
on items(status, updated)
where status in (1, 2);
有时,在这些情况下,您需要在state
上使用聚集索引。基本上,这允许将"已处理"的项目分组在一起。而且,如果他们所在的数据页没有被访问,那么这些数据页就不需要加载。
不过,在您的情况下,我猜项目是按顺序添加的,所以只处理最近的项目。较旧的数据页将填充已处理的项,并且由于它们从未被引用,甚至可能不会占用内存中的空间。