sql - 可能值分布不均匀的低基数指数
问题描述
我有一个用作队列的表:
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
为当前时间的记录 - 消费者选择
Pending
状态为 order by 的记录createdAt
。消费时,他们将记录标记为Processing
并将当前时间设置updatedAt
为当前时间(使用update/select
CTE) - 处理后,消费者将记录标记为
Processed
- 在处理过程中,消费者可能会崩溃,因此无法将记录标记为
Processed
- 当另一个消费者发现一条记录停留在
Processing
状态超过 x 分钟(即updatedAt < current_time - x
和status = Processing
)时,他们会拿起它们并处理(假设新消费者不会崩溃;)) - 该表有大约 100 万条记录,每天增长大约 20k
- 在任何给定时间都会有 < 100
Pending
和Processing
记录
我有 2 个问题
- 考虑到这种情况(尤其是最后两点),
(status, createdAt)
作为updatedAt
包含列的索引会是一个好的索引吗?
我试过了,它表明索引被命中并且执行时间非常快(亚秒级)。但是,我不太确定这个低基数索引(带有起始列status
)是一个好的索引,因为这样的索引通常被认为是 bad。我想知道它是否适用于我的情况,因为可能值的分布非常不均匀(<1% 的记录是Pending
,InProgress
这是我要查询的。没有运行查询来选择Processed
那些)。
- 我添加了包含的列 (
updatedAt
) 以支持此过滤器status = Processing and updatedAt < current_time - x
,但我不确定它是否有用。查询规划器关心包含的列还是只查看索引中的列 (status, createdAt
)?
如果您同时回答这两个问题,则可获得奖励积分;)
解决方案
索引的主要目的之一是减少从表中读取的行数。低基数索引意味着一列只接受少数几个值。因此,如果一个表有一千万行并且有十个值,那么每个值平均会有一百万行。
索引对于从一千万行表中获取一百万行没有帮助,因为每个(或几乎每个)数据页都会有一个匹配的行。目的是减少正在读取的数据页数。
因此,您对索引的使用是非常合理的,因为您只找到几行。您正在使用索引来查找未处理的行,而且这些行很少。
您的索引比必要的大得多,因为它包含有关已处理行的信息。您可能会考虑过滤索引。根据您的描述,我认为这将是:
create index idx_items_status_updated
on items(status, updated)
where status in (1, 2);
有时,在这些情况下,您希望在state
. 基本上,这允许将“已处理”项目组合在一起。并且,如果它们所在的数据页未被访问,则不需要加载这些数据页。
但是,在您的情况下,我猜测项目是按顺序添加的,因此只会处理最近的项目。较旧的数据页将充满已处理的项目,并且——因为它们从未被引用——甚至可能不会占用内存中的空间。
推荐阅读
- php - 重定向到 PWA 时出现 Google OAuth2 Invalid token format 错误
- c++ - 在 Ubuntu 上使用 C++ 和 cmake 使用 SDL2 + SDL2Image 加载 PNG
- angular - 使用 Fetch API 的响应似乎为空
- c# - EF Core 3.1 重复键异常唯一索引
- c# - C# 中性能不佳的 Linq 查询的优化
- python - 如何在不同时间填充sq数据库
- scala - 如何在 Gatling Scala 脚本运行时获取虚拟用户数、迭代数和虚拟用户总数?
- javascript - Express with FIREBASE 我得到 TypeError: Cannot read property 'trim' of undefined
- html - 如何生成二维码中的html表单数据?
- java - 返回客户异常对象时更改 responseStatusCode