用于在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性能有不可避免的影响，因为每个时间戳都是免费索引的。

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

我知道我可以把这张表分成两张表，但我想把它放在一张表中。

Answer 1

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

分区更新:替代分区：

创建带有分区的表，并定义一个只有2个值1或0的字段。这将为field =1的记录创建一个分区，并为field = 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;

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

Answer 2

我有多个答案和对其他人的答案的评论。

首先，让我假设Foo的PRIMARY KEY是id INT UNSIGNED AUTO_INCREMENT (4字节)，表是Engine=InnoDB。

索引的额外列

额外列的索引将是每行额外列的宽度和PRIMARY KEY，外加一堆开销。对于您的processedOn，您谈论的是8个字节(2个整数)。使用一个简单的标志，5个字节。

分表

对于未处理的项目，此表将只有id。它需要额外的代码来填充它。它的规模将保持在某个“高水位线”。因此，如果有大量未处理的项目，它将增长，但不会缩小。(这是一个很少见的OPTIMIZE TABLE有用的例子。)InnoDB需要一个PRIMARY KEY，而id可以完美地工作。所以，只有一列，没有额外的索引。它比上面讨论的额外索引小得多。找到要做的事情：

$id = SELECT id FROM tbl LIMIT 1;   -- don't care which one
process it
DELETE FROM tbl where id = $id

2个分区，一个已处理，一个不是

不是的。将行从已处理更改为未处理时，必须从一个分区中删除该行并将其插入到另一个分区中。这是由您的UPDATE ... SET flag = 1在后台完成的。此外，这两个分区都有“高水位”问题--它们将增长，但不会缩小。并且分区的空间开销可能与其他解决方案一样多。

SELECT by PARTITION ...需要5.6。否则，您将需要一个INDEX，因此又回到了索引问题上。

连续扫描

这不会产生额外的磁盘空间。(这比您预期的要好，对吧？)而且它的效率也不是太低。下面是它的工作原理。下面是要放入cron作业中的一些伪代码。但不要让它成为一件棘手的事情。相反，让它一直运行。(我希望，原因会变得清晰。)

SELECT @a := 0;
Loop:
    # Get a clump
    SELECT @z := id FROM Foo WHERE id > @a ORDER BY id LIMIT 1000,1;
    if no results, Set @z to MAX(id)
    # Find something to work on in that clump:
    SELECT @id := id FROM Foo
        WHERE id > @a
          AND id <= @z
          AND not-processed
        LIMIT 1;
    if you found something, process it and set @z := @id
    SET @a := @z;
    if @a >= MAX(id), set @a := 0;   # to start over
    SLEEP 2 seconds   # or some amount that is a compromise
Go Loop

备注：

• It遍历表格，影响最小。
• It甚至可以处理id中的间隙。(如果没有间隙，它可以变得更简单。)(如果PK不是AUTO_INCREMENT，它几乎是sleep将成为一个‘好人’。

选择性索引

MariaDB的动态列和MySQL 5.7的JSON可以对事物进行索引，我认为它们是“选择性的”。一种状态是使列为空，另一种状态是在dynamic/json列中设置标志。这将需要一些研究来验证，并且可能需要升级。