使用MySQL构建一个队列表

使用MySQL来实现队列表是一个取巧的做法，我们看到很多系统在高流量、高并发的情况下表现并不好。典型的模式是一个表包含多种类型的记录：未处理记录、已处理记录、正在处理记录等。一个或者多个消费者线程在表中查找未处理的记录，然后声称正在处理，当处理完成后，再将记录更新成已处理状态。一般的，例如邮件发送、多命令处理、评论修改等会使用类似模式。

通常有两个原因使得大家认为这样的处理方式并不适合。

第一，随着队列表越来越大和索引深度的增加，找到未处理记录的速度会随之变慢。你可以通过将队列表分成两部分来解决这个问题，就是将已处理记录归档或者放到历史表，这可以始终保证队列表很小。

第二，一般的处理过程分两步，先找到未处理记录然后加锁。找到记录会增加服务器的压力，而加锁操作则会让各个消费者进程增加竞争，因为这是一个串行化的操作。
找到未处理记录一般来说都没问题，如果有问题则可以通过使用消息的方式来通知各个消费者。具体的，可以使用一个带有注释的SLEEP()函数做超时处理，如下：

SELECT /* waiting on unsent_emails */ SLEEP(10000);

这让线程一直阻塞，直到两个条件之一满足：10000秒后超时，或者另一个线程使用KILL QUERY结束当前的SLEEP。因此，当再向队列表中新增一批数据后，可以通过SHOW PROCESSLIST，根据注释找到当前正在休眠的线程（我的试验结果是，上面的SELECT的注释并没有显示出来，可以加一个版本号注释：“SELECT /*!999999 waiting on unsent_emails */ SLEEP(10000);”，这样SHOW PROCESSLIST中就可以显示出注释），并将其KILL，然后消费者线程便随之出错后重连继续处理。你可以使用函数GET_LOCK()和RELEASE_LOCK()来实现通知（不建议用这种方式，锁的互斥还异常中断等锁的解除等不太好控制）。或者可以在数据库之外实现，例如使用一个消息服务，进程间通信之类的。

最后需要解决的问题是如何让消费者标记正在处理记录，而不至于让多个消费者重复处理一个记录。我们看到大家一般使用SELECT FOR UPDATE来实现。这通常是扩展性问题的根源，这会导致大量的事务阻塞并等待。
一般，我们要尽量避免使用SELECT FOR UPDATE。不光是队列情况，任何情况下都要尽量避免。总是有别的更好的办法来实现你的目的。在队列表中，可以直接使用UPDATE来更新记录，然后检查是否还有其他的记录需要处理。我们看看具体实现，我们先建立如下的表：

CREATE TABLE unsent_emails (
Id INT NOT NULL PRIMARY KEY AUTO_INCREMETN,
-- columns for the message, from, to subject, etc.
status ENUM(‘unsent’, ‘claimed’, ‘sent’),
owner INT UNSIGNED NOT NULL DEFAULT 0,
ts TIMESTAMP,
KEY (ownet, status, ts)
);

该表的列owner用来存储当前正在处理这个记录的连接线程的线程ID，即由函数CONNECTION_ID()返回的ID。如果当前记录没有被任何消费者处理，则该值为0。

我们还经常看到的一个办法是，如下面所示的一次处理10条记录：

BEGIN;
SELECT id FROM unsent_emails
WHERE owner=0 AND status = ‘unsent’
LIMIT 10 FOR UPDATE;
-- RESULT: 123, 456, 789
UPDATE unsent_emails
SET status = ‘claimed’, owner = CONNECTION_ID()
WHERE id IN(123, 456, 789);
COMMIT;

看到这里的SELECT查询可以用到索引的两个列，因此理论上查找的效率应该更快。问题是，在上面两个查询之间的“间隙时间”，这里的锁会让所有其他同样的查询全部都被阻塞。所有的这样的查询将使用相同的索引，扫描索引相同的部分，所以很可能会被阻塞。

如果改进成下面的写法，则会更高效：

SET AUTOCOMMIT = 1;
COMMIT;
UPDATE unsent_emails
SET status = ‘claimed’, owner = CONNECTION_ID()
WHERE owner = 0 AND status = ‘unsent’
LIMIT 10;
SELECT id FROM unsent_emails
WHERE owner = CONNECTION_ID() AND status = ‘claimed’;
-- result: 123, 456, 789

根本就无须使用SELECT查询去找到哪些记录还没有被处理。客户端的协议会告诉你更新了几条记录，所以可以知道这次需要处理多少条记录。

所有的SELECT FOR UPDATE都可以使用类似的方法改写。

最后还需要处理一种特殊情况：那些正在被进程处理，而进程本身却由于某种原因退出的情况。这种情况处理起来很简单，你只需要定期运行UPDATE语句将它都更新成原始状态就可以了，然后执行SHOW PROCESSLIST，获取当前正在工作的线程ID，并使用一些WHERE条件避免取到那些刚开始处理的进程。假设我们获取的线程ID有(10, 20, 30)，下面的更新语句会将处理时间超过10分钟的记录状态都更新成初始状态：

UPDATE unsent_emails
SET owner = 0, status = ‘unsent’
WHERE owner NOT IN (10, 20, 30) AND status = ‘claimed’
AND ts < CURRENT_TIMESTAMP – INTERVAL 10 MINUTE;

另外，注意看看是如何巧妙地设计索引让这个查询更加高效的。因为我们将范围条件放在WHERE条件的末尾，这个查询恰好能够用到索引的全部列。其他的查询也都能用上这个索引，这就避免了再新增一个额外的索引来满足其他的查询。

我们总结一下这个队列表实现中的一些基本原则：
尽量少做事，可能的话就不要做任何事情。除非不得已，否则不要使用轮询，因为这会增加负载，而且还会带来很多低产的工作。
尽可能快地完成需要做的事情。尽量使用UPDATE代替先SELECT FOR UPDATE再UPDATE的写法，因为事务提交的越快，持有的锁时间就越短，可以大大减少竞争和加速串行执行效率。
将已处理完成和未处理的数据分开，保证数据集足够小。
某些查询是无法优化的，考虑使用不同的查询或者不同的策略去实现相同的目的。通常对于SELECT FOR UPDATE就需要这样的处理。

有时，最好的办法就是将任务队列从数据库中迁移出来。Redis就是一个很好的容器，也可以使用memcached来实现。另一个选择是使用Q4M存储引擎，但我们没有在生产环境使用过这个存储引擎。RabbitMQ和Gearman也可以实现类似的功能。