mysql - 优化连接查询以从 A 获取数据，条件是 B 按 B 排序

Question

我有一个使用这些表设置的项目到项目相似度矩阵:

items (id, ...) (Primary key `id`)
similarities (item1_id, item2_id, similarity) (Index on `item1_id` and `item2_id`)

similarities 表包含具有相似性索引的 id 对，即:

item1_id  item2_id  similarity
1         2         0.3143
2         3         0.734

为了高效存储，省略了“反向对”，即只有一对 (1,2)，没有冗余对 (2,1)。这意味着项目的外键可以是 item1_id 或 item2_id。

现在我想找到与一堆其他项目相似的项目，按降序相似性排序。我正在使用这个查询:

SELECT    `Item`.*
FROM      `items` AS `Item`
LEFT JOIN `similarities` AS `Similarity`
       ON (`Item`.`id` = `Similarity`.`item1_id`
              AND `Similarity`.`item2_id` IN (1, 2, 3, ...))
          OR (`Item`.`id` = `Similarity`.`item2_id`
              AND `Similarity`.`item1_id` IN (1, 2, ,3, ...))
WHERE     `Similarity`.`item1_id` IN (1, 2, 3, ...)
          OR `Similarity`.`item2_id` IN (1, 2, 3, ...)
GROUP BY  `Item`.`id`
ORDER BY  `Similarity`.`similarity` desc

虽然它非常慢，大约 100,000 个项目和大约 30,000 个相似对需要 4-5 秒。看来 JOIN 的成本非常高。这是查询 EXPLAINed:

select_type  table       type         possible_keys      key                key_len  ref   rows    Extra
SIMPLE       Similarity  index_merge  item1_id,item2_id  item1_id,item2_id  110,110  NULL  31      Using sort_union(item1_id,...
SIMPLE       Item        ALL          PRIMARY            NULL               NULL     NULL  136600  Using where; Using join buffer

我该怎么做才能加快速度？最坏的情况我会在两个单独的查询中执行，但如果可能的话我更喜欢一个 JOIN 查询。

Answer 1

我实际上并没有尝试过这个，但也许它为您指明了正确的方向。这个想法是对(唯一)id 的 UNION 和来自 similarities 的相似性对进行临时结果，然后将项目与之连接。

SELECT Item.*, s.other_item_id, s.similarity
FROM items AS Item
JOIN
    (
    SELECT item1_id AS id, item2_id AS other_item_id, similarity FROM similarities
    UNION
    SELECT item2_id AS id, item1_id AS other_item_id, similarity FROM similarities
    ) AS s ON s.id = items.id
WHERE items.id IN (1, 2, 3, ...)
ORDER BY s.similarity DESC;

在您的原始查询中，您不需要在 JOIN 条件和 WHERE 子句中限制来自 similarities 的 ID。