MySQL临时不能看到一些新插入的行，但可以看到随后插入的其他行

Question

我有一个MySQL(Version5.7.14-8)表，它本质上像一个队列(它是一个遗留系统)，具有以下模式：

CREATE TABLE `myqueue` (
  `queue_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key identifying the queue event',
  `queue_insertiontime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'The time when this queue event was inserted',
  `tracking_key` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`queue_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12819839853 DEFAULT CHARSET=latin1 
/*!50100 PARTITION BY RANGE (queue_id)
(PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

在生产者端，我们只要一得到数据就将行插入到数据库中。在消费者端，我们以批方式消费，使用

SELECT * FROM myqueue ORDER BY queue_id ASC LIMIT 1

然后：

SELECT * FROM myqueue WHERE queue_id >= (last processed queue id) AND queue_id <= (max we got before) ORDER BY queue_id ASC LIMIT 1000

大多数情况下，该系统运行良好，但有时我们似乎“跳过”行，尽管在随后的查询中存在这些行。

例如，假设我们有一个接一个写入队列ids 1-5的行，如果我们查询非常接近写行的时间，有时我们只会看到行1、2和5。行3-4最终会出现，但是当我们第一次查询时它们就不在了。我们有默认的事务隔离级别。MySQL内部是否允许我们查询行5，而不是3-4，尽管这些行的in显然已经被保留了？当我们像这样查询时，如何确保在查询之前所有行都可用？

Answer 1

使用InnoDB构建队列是一个噩梦，特别是在涉及复制的情况下。

我的一个口号是“不要排队，只要‘做’。”在许多“工人”的设置中，让员工来完成任务实际上要比将他们排在队列中要好。尤其是当任务不需要很长时间才能运行时，情况尤其如此。有120亿件商品，听起来真的很“快”。

AUTO_INCREMENT可能是您遇到的问题的一部分。请注意以下事项：

• 当INSERT开始运行时，从表中取出一个'next‘id。它在整个事务是COMMITted之前是不可见的。
• 在复制中，ids可以是一个顺序，但是COMMITted可以是一个不同的顺序。这表明复制不能通过id“记住它停止的位置”。这可能足以解释您遇到的问题。

myqueue只有一个PARTITION？这似乎是一个性能问题，或者至少不是一个好处。

对于长期运行的任务，最好在持续时间内不要挂起myqueue。相反，使用更复杂的机制：

1. “签出”--单个SQL原子地标记队列项。这可以是一个UPDATE，它检查没有其他人签出它，并用worker_id和启动时间戳标记它。
2. “过程”-工人需要多长时间
3. “清除”--另一个原子SQL语句，可推定为一行DELETE。

但是，这需要一份“收割机”的工作来处理任何留下未完成工作的事故。

如果在队列中插入任务需要很长时间(例如，由于辅助表中有大量相关数据为INSERTed )，则可能需要这样的混乱顺序：

1. 将任务信息INSERT到其他表中。捕获一个task_id以便工作人员能够找到它。需要多长时间就用多久；不要担心auto_inc ids等等。
2. 作为一个单独的事务，执行原子INSERT of task_id到myqueue。希望这个表的Auto_inc queue_id是连续的。

如果你想提供更多的信息，我可能有一些进一步的改进。除了讨论应用程序外，请提供SHOW CREATE TABLE、表大小、分区原理等。