MySQL上整个Queue？

[bingoohuang]

问题

思考

劣势：

1. Polling轮训MySQL的话显然对性能和负载都不好
2. Locking锁机制导致性能低下
3. Data Growth也会导致性能低下

优势：

1. 不需要其它基础设施，只需要有数据库就行了
2. 数据库持久化带来的基础保障

试验

CREATE TABLE `t_job_queue` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `status` INT NOT NULL DEFAULT '0',
  `params` VARCHAR(1024) NOT NULL DEFAULT '',
  `result` VARCHAR(1024) NOT NULL DEFAULT '',
  `update_id1` INT UNSIGNED NOT NULL DEFAULT '0',
  `update_id2` INT UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 注意，以下两条SQL必须在同一个会话中完成
UPDATE t_job_queue SET status=STATUS_PROCESSING, id=(@id:=id) WHERE status=0 ORDER BY id ASC LIMIT 1;
SELECT id, status, params FROM t_job_queue WHERE id=@id;

动手测试一番吧

参考

1. 5 SUBTLE WAYS YOU'RE USING MYSQL AS A QUEUE, AND WHY IT'LL BITE YOU
2. 上文在HackerNews上的讨论
3. A job queue in MySQL
4. 使用 MySQL 实现无锁任务队列(using MySQL as a job queue)
5. The Database As Queue Anti-Pattern
6. Durable MySQL-backed queue for Clojure projects
7. The best way to use a DB table as a job queue (a.k.a batch queue or message queue)
8. WHY MYSQL IS NOT A QUEUE
9. Redis Priority Queue offers a priority/timeline based queue for use with Redis
10. EBOOK – REDIS IN ACTION Delayed tasks
11. Fast circular buffer in MySQL，代码mysql-queue
12. Asynchronous Processing in Web Applications, Part 1: A Database Is Not a Queue
13. [译]如何优化Quartz调度器性能
14. I need a dedicated message queue... or do I?、Rolling your own message/job queue in MySQL (Part 1)、Rolling your own message/job queue in MySQL (Part 2: Message Queue Initial Spec)、Rolling your own message/job queue in MySQL (Part 3: Message Queue Implementation)
15. Later is a redis base delay queue
16. How to Implement a Queue in SQL
17. Creating a Job queue in Innodb