选择1个随机行并进行复杂过滤

小编典典

选择1个随机行并进行复杂过滤

sql

我有2张桌子：

第一张桌子users：

+-------------------------+---------+------+-----+---------+-------+
| Field                   | Type    | Null | Key | Default | Extra |
+-------------------------+---------+------+-----+---------+-------+
| id                      | int(11) | NO   | PRI | NULL    |       |
| first_name              | text    | NO   |     | NULL    |       |
| age                     | int(11) | YES  |     | NULL    |       |
| settings                | text    | YES  |     | NULL    |       |
+-------------------------+---------+------+-----+---------+-------+

第二张表proposals：

+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| from_id | int(11) | NO   |     | NULL    |                |
| to_id   | int(11) | NO   |     | NULL    |                |
| status  | int(11) | NO   |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+

我需要1个随机从用户排它id不是to_id在proposals

我正在使用此sql（没有rand）：

SELECT DISTINCT *
FROM profiles
WHERE
    profiles.first_name IS NOT NULL
AND
NOT EXISTS (
    SELECT *
    FROM proposal
    WHERE
        proposal.to_id = profiles.id
)
LIMIT 0 , 1

表现还不错： 1 row in set (0.00 sec)

但是性能却很糟糕：1 row in set (1.78 sec)当我添加ORDER BY RAND()到最后时

我的洞很大，users.id不能使用类似的东西MAX(id)

我尝试设置random limit，例如：

...
LIMIT 1234 , 1;
Empty set (2.71 sec)

但是也要花很多时间:(

如何获得性能users.id不高的随机1个用户proposals.to_id？

我认为我首先需要profiles使用arand()进行筛选，然后对其进行过滤，但是我不知道该怎么做。

阅读 151

2021-04-14

共1个答案

小编典典

我有两个问题解决方案。

1）具有随机ID，来自

SELECT *
FROM profiles AS r1
JOIN
    (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM profiles)) AS id)
        AS r2
WHERE
    r1.id >= r2.id
    AND
    r1.first_name IS NOT NULL
AND
NOT EXISTS (
    SELECT *
    FROM proposal
    WHERE
        proposal.to_id = r1.id
)
LIMIT 0 , 1

2）与 ORDER BY RAND()

SELECT *
FROM
    (
        SELECT *
        FROM profiles
        WHERE
            profiles.first_name IS NOT NULL
        ORDER BY RAND()
    ) AS users
WHERE
    NOT EXISTS (
        SELECT *
        FROM proposal
        WHERE
            proposal.to_id = users.id
    )
LIMIT 0 , 1

第一个解决方案速度更快，但存在“漏洞id”的问题，当您从头到尾解决问题时id（用户可能比匹配的结果更早结束）

第二种解决方案速度较慢，但没有缺陷！

2021-04-14