mysql - 用于定位 MySQL 中未处理条目的高效索引

Question

我有一个包含数百万条目的 MySQL 表。

每个条目都必须在某个时刻由 cron 作业处理。

我需要能够使用索引快速找到未处理的条目。

到目前为止，我使用了以下方法:我添加了一个可为空的索引 processedOn 列，其中包含处理条目的时间戳:

CREATE TABLE Foo (
    ...
    processedOn INT(10) UNSIGNED NULL,
    KEY (processedOn)
);

然后使用以下方法检索未处理的条目:

SELECT * FROM Foo WHERE processedOn IS NULL LIMIT 1;

感谢 MySQL 的 IS NULL optimization ，查询速度非常快，只要未处理条目的数量很少(几乎总是如此)。

这种方法已经足够好了:它完成了工作，但同时我觉得索引被浪费了，因为它只用于WHERE processedOn IS NULL 查询，从不用于定位该字段的精确值或值范围。因此，这对存储空间和 INSERT 性能产生了不可避免的影响，因为每个时间戳都被毫无意义地编入索引。

有更好的方法吗？理想情况下，索引将只包含指向未处理行的指针，而不包含指向任何已处理行的指针。

我知道我可以将此表拆分为 2 个表，但我想将它保留在一个表中。

Answer 1

我想到的是创建一个 isProcessed 列，默认值 = 'N' 并在处理时设置为 'Y'(同时设置 processedOn 列)。然后在 isProcessed 字段上创建索引。当您查询时(使用 where 子句 WHERE isProcessed = 'N')，它会非常快速地响应。

更新:带分区的替代方案:

创建带分区的表并定义一个只有 2 个值 1 或 0 的字段。这将为字段 = 1 的记录创建一个分区，为字段 = 0 的记录创建另一个分区。

create table test (field1 int, field2 int DEFAULT 0)
PARTITION BY LIST(field2) (
    PARTITION p0 VALUES IN (0),
    PARTITION p1 VALUES IN (1)  
);

这样，如果您只想查询字段等于其中一个值的记录，只需执行以下操作:

select * from test partition (p0);

上面的查询将只显示 field2 = 0 的记录。而如果需要将所有记录一起查询，则正常查询表即可:

select * from test;

据我所知，这将帮助您满足您的需求。