mysql存储过程从基于数据库的队列中弹出

Question

我们有一个系统，它有一个基于数据库的队列，用于在线程中而不是实时地处理项目。目前在Mybatis中实现调用mysql中的这个存储过程:

DROP PROCEDURE IF EXISTS pop_invoice_queue;
DELIMITER ;;
CREATE PROCEDURE pop_invoice_queue(IN compId int(11), IN limitRet int(11)) BEGIN

   SELECT LAST_INSERT_ID(id) as value, InvoiceQueue.* FROM InvoiceQueue 
      WHERE companyid = compId 
      AND (lastPopDate is null OR lastPopDate < DATE_SUB(NOW(), INTERVAL 3 MINUTE)) LIMIT limitRet FOR UPDATE;
   UPDATE InvoiceQueue SET lastPopDate=NOW() WHERE id=LAST_INSERT_ID(); 

END;;

DELIMITER ;

问题是这会从队列中弹出 N 个项目，但只会更新从队列中弹出的最后一个项目的 lastPopDate 值。因此，如果我们使用 limitRet = 5 调用此存储过程，它将从队列中弹出五个项目并开始处理它们，但只有第五个项目将设置 lastPopDate，因此当下一个线程到来并从队列中弹出时，它将获取项目1-4 和第 6 项。

我们如何才能更新从数据库“弹出”的所有 N 条记录？

Answer 1

如果您愿意通过以下方式将 BIGINT 字段添加到表中:

ALTER TABLE InvoiceQueue
ADD uuid BIGINT NULL DEFAULT NULL,
INDEX ix_uuid (uuid);

然后你可以先做更新，选择更新的记录，通过:

CREATE PROCEDURE pop_invoice_queue(IN compId int(11), IN limitRet int(11))
BEGIN
   SET @uuid = UUID_SHORT();

   UPDATE InvoiceQueue
   SET    uuid = @uuid,
          lastPopDate = NOW()
   WHERE  companyid = compId
   AND    uuid IS NULL 
   AND    (lastPopDate IS NULL OR lastPopDate < NOW() - INTERVAL 3 MINUTE)
   ORDER BY
          id
   LIMIT  limitRet;

   SELECT * 
   FROM   InvoiceQueue 
   WHERE  uuid = @uuid
   FOR    UPDATE;
END;;

要使 UUID_SHORT() 函数返回唯一值，每台机器每秒调用它的次数不应超过 1600 万次。访问here了解更多详情。

为了性能，您可能希望将 lastPopDate 字段更改为 NOT NULL，因为 OR 子句将导致您的查询不使用一个索引，即使一个索引可用:

ALTER TABLE InvoiceQueue
MODIFY lastPopDate DATETIME NOT NULL DEFAULT '0000-00-00';

然后，如果您还没有索引，可以在 companyid/lastPopDate/uuid 字段上添加一个索引，如下所示:

ALTER TABLE InvoiceQueue
ADD INDEX ix_company_lastpop (companyid, lastPopDate, uuid);

然后您可以从 UPDATE 查询中删除 OR 子句:

   UPDATE InvoiceQueue
   SET    uuid = @uuid,
          lastPopDate = NOW()
   WHERE  companyid = compId 
   AND    lastPopDate < NOW() - INTERVAL 3 MINUTE
   ORDER BY
          id
   LIMIT  limitRet;

它将使用您刚刚创建的索引。