MySQL 快速检查 hash 是否存在

Question

我正在尝试创建一个 MySQL 函数，它将 n 和 m 作为输入并生成随机 n 的唯一组合m 来自查询结果的 id。

每次调用该函数将返回一个组合，并且该组合必须与之前的所有组合不同。

在生成期间它必须检查另一个表:如果组合已经存在，继续循环直到每个组合保持唯一。以破折号分隔的 id 返回组合，或者如果没有唯一组合的空间则返回 false。

所以我得到了 100 个这样的随机元素:

SELECT
    `Item`.`id`
FROM
    `Item`
LEFT JOIN `ItemKeyword` ON `Item`.`id` = `ItemKeyword`.`ItemID`
WHERE
    (`Item`.`user_id` = '2')
AND(`ItemKeyword`.`keywordID` = 7130)
AND(`Item`.`type` = 1)
ORDER BY RAND()
LIMIT 100

过去的组合存储为 - 的 itemID 串联的 md5。

所以我需要通过 - 连接这个查询的结果并创建它的 md5。然后将另一个查询发送到名为 Combination 的第二个表中，并检查 hash 列是否存在。并继续这个循环，直到我得到 n 个结果。

我不知道如何正确快速地实现这一目标。有什么建议吗？

更新:

整个 SQL 转储在这里:https://gist.github.com/anonymous/e5eb3bf1a10f9d762cc20a8146acf866

Answer 1

如果您通过 md5 测试唯一性，则需要在获取 md5 之前对列表进行排序。这可以用 SELECT MD5('1-2'), MD5('2-1');

来演示

去掉LEFT，好像没什么用。之后，优化器可以选择以 ItemKeyword 而不是 Item 开始。 (在不知道数据分布的情况下，我不能说这是否有帮助。)

(如果您为每个表提供 SHOW CREATE TABLE 会很有帮助。如果没有，我会假设您使用的是 InnoDB 并且有 PRIMARY KEY(id)和 PRIMARY KEY(keywordID)。)

需要“复合”索引:

Item: INDEX(user_id, type, id)
ItemKeyword: INDEX(ItemID, keywordID)

ItemKeyword 闻起来像一个多对多映射表。 大多数这样的表都可以改进，从扔掉 id 开始。参见 7 tips on many:many .

我有点迷失在你的二次加工中。

My tips on RAND可能有帮助也可能没有帮助。

模式批判

• 一个PRIMARY KEY是一个UNIQUE KEY是一个INDEX；消除冗余索引。
• INT(4) -- (4) 没有任何意义； INT 总是 32 位(4 字节)，范围很大。请参阅 SMALLINT UNSIGNED(2 个字节，0..64K 范围)。
• MD5 应该声明为 CHAR(32) CHARACTER SET ascii，而不是 255，也不是 utf8。 (latin1 可以。)
• 表组合(id + hash)好像没用。相反，只需将 Item 表中的 KEY md5 (md5) USING BTREE, 更改为 UNIQUE(md5)。
• 您已使用 SET NAMES utf8mb4; 开始使用 utf8mb4，但表(及其列)仍然是 utf8。表情符号和中文需要utf8mb4；大多数其他文本没有。

解决这些问题后，原始问题可能 得到解决(以及进行一些清理)。如果现在，请添加一些进一步的说明。

缩小

1. 获取 m 个唯一 ID 的排序列表。 (下一步我需要“排序”，并且由于您正在寻找“组合”，因此似乎不需要“排列”。)

SELECT GROUP_CONCAT(id) AS list
    FROM (
        SELECT id FROM tbl
            ORDER BY RAND()
            LIMIT $m
         ) AS x;

2. 检查唯一性。通过获取 MD5(list)(从上面)并检查“使用过的”md5 表来执行此操作。注意:除非您要求在一小部分 ID 中进行大量组合，否则不太可能出现重复(尽管并非不可能)。

3. 交付列表。但是，它是一串用逗号分隔的 ID。拆分它最好在应用程序代码中完成，而不是 MySQL 函数。

4.您将如何处理该列表？这可能很重要，因为将步骤 4 折叠到步骤 3 中可能很方便。

底线:我只会在 SQL 中执行第 1 步和第 2 步的一部分；我会在应用程序代码中构建一个“函数”来完成剩下的工作。