mysql - 如何在没有 id/auto_increment 列的情况下在 MySQL 中检索多个随机行？

Question

我的目标

我正在尝试检索多个随机行，这些行仅包含唯一的 userid 但 type 列是随机的 - type 只能是0 或 1。有问题的表在任何给定时间都将包含少于 1,000 行。

我的 table

CREATE TABLE tbl_message_queue (
  userid bigint(20) NOT NULL,
  messageid varchar(20) NOT NULL,
  `type` int(1) NOT NULL,
  PRIMARY KEY (userid,messageid,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

示例数据

userid  |   messageid   |   type
---------------------------------------------------
4353453 |   518423942   |   0
4353453 |   518423942   |   1
2342934 |   748475435   |   0
2342934 |   748475435   |   1
7657529 |   821516543   |   0
7657529 |   821516543   |   1
0823546 |   932843285   |   0
0823546 |   932843285   |   1

要排除什么

使用 ORDER BY RAND() 是不可行的，因为应用程序在任何给定时刻至少执行 18,000 多个此类查询并导致高负载。使用 SELECT DISTINCT 或 GROUP BY (显然)更有效，并且总是会选择唯一的 userid 但 type 总是等于 0，负载可接受。

常用方法是创建一个 id 列，但我仅正在寻找替代方法。组主键不能更改，因为它需要并深入集成到我们的应用程序中，但是可以更改每列的结构。

谢谢。

Answer 1

我对您的问题的理解是，对于每个 userid，您有两个 条目，但只想随机提取一个。

为此，您应该为每个唯一的 userid 生成一个介于 0 和 1 之间的随机值，然后使用起始列表加入此列表:

SELECT a.* FROM tbl_message_queue AS a
    JOIN ( SELECT userid, FLOOR(2*RAND()) AS type 
           FROM tbl_message_queue GROUP BY userid ) AS b
    ON ( a.userid = b.userid AND a.type = b.type );

但如果 ORDER BY RAND() 对您不起作用，也许我们应该妥协。

在上面的序列中，任何两个用户 ID 都不相关——也就是说，用户 A 的类型为 0 并不能告诉您用户 B 会出现什么。

根据用例，可以通过两个查询获得不太随机(但“明显随机”)的序列:

SELECT @X := FLOOR(2*RAND()), @Y := POW(2,FLOOR(2+14*RAND()))-1;

SELECT * FROM tbl_message_queue WHERE (((userid % @Y) & 1) XOR type XOR @X);

通过这种方式，您可以获得看似随机提取的内容。真正发生的是用户 ID 是相关，并且您只有几十种不同的提取可能。但是只使用简单的运算符，没有 JOIN，这个查询非常快。