在mysql中实现消息队列表的最佳方法是什么 [英] What's the best way of implementing a messaging queue table in mysql

问题描述

这可能是我第十次实施这样的事情，而我从未对自己提出的解决方案感到百分百满意.

It's probably the tenth time I'm implementing something like this, and I've never been 100% happy about solutions I came up with.

之所以使用mysql表而不是适当的"消息系统是有吸引力的，主要是因为大多数应用程序已经将一些关系数据库用于其他内容(对于我一直在做的大多数事情来说，它通常都是mysql)很少有应用程序使用消息传递系统.另外-关系数据库具有非常强的ACID属性，而消息传递系统通常没有.

The reason using mysql table instead of a "proper" messaging system is attractive is primarily because most application already use some relational database for other stuff (which tends to be mysql for most of the stuff I've been doing), while very few applications use a messaging system. Also - relational databases have very strong ACID properties, while messaging systems often don't.

第一个想法是使用:

create table jobs(
  id auto_increment not null primary key,
  message text not null,
  process_id varbinary(255) null default null,
  key jobs_key(process_id) 
);

然后入队看起来像这样:

And then enqueue looks like this:

insert into jobs(message) values('blah blah');

出队看起来像这样:

begin;
select * from jobs where process_id is null order by id asc limit 1;
update jobs set process_id = ? where id = ?; -- whatever i just got
commit;
-- return (id, message) to application, cleanup after done

表和入队看起来不错，但出队还是有点困扰我.回滚的可能性有多大?还是被阻止?我应该使用什么键使其变成O(1)-ish?

Table and enqueue look nice, but dequeue kinda bothers me. How likely is it to rollback? Or to get blocked? What keys I should use to make it O(1)-ish?

或者我在做什么更好的解决方案?

Or is there any better solution that what I'm doing?

推荐答案

我已经构建了一些消息队列系统，但不确定您要指的是哪种类型的消息，但是对于出队(这是一个字吗?)我已经完成了与您所做的相同的事情.您的方法看起来简单，干净且可靠.并不是说我的工作是最好的，但是事实证明，它对于许多站点的大型监视非常有效. (错误记录，大规模电子邮件营销活动，社交网络通知)

I've built a few message queuing systems and I'm not certain what type of message you're referring to, but in the case of the dequeuing (is that a word?) I've done the same thing you've done. Your method looks simple, clean and solid. Not that my work is the best, but it's proven very effective for large-monitoring for many sites. (error logging, mass email marketing campaigns, social networking notices)

我的投票:不用担心！

这篇关于在mysql中实现消息队列表的最佳方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！