mysql - 在表中有一对 friend ，如何创建一个抽象获取用户 friend 列表的 View ？

Question

我有一张谁和谁是 friend 的表，它有以下简单的内容:

user_id1 | user_id2 | <some other data>

具有非唯一索引:index_on_user1和 index_on_user2

两列包含相同类型的数据，友谊总是双向的。

1 | 2

= 用户 1是用户的 friend 2和用户 2是用户的 friend 1 .

我想要的是获得一个查询，该查询将始终如一地为我提供我提供的任何 user_id 的 friend 列表。

到目前为止，我一直在使用

SELECT if(`user_id1` = $user_id, `user_id2`, `user_id1`) as friend
FROM `Friends`
WHERE `user_id1` = $user_id OR `user_id2` = $user_id

但我有更多的查询，我想将此信息作为子查询获取，并将包含此查询的 View 作为子查询。所以我一直在寻找一种方法来抽象它，有一些简单的选择，总能得到我 friend 的 id，而没有那些 if 的麻烦。陈述。

我对 union 有了这个想法:

CREATE OR REPLACE VIEW get_friend AS 
SELECT 
    `user_id1` as subject,
    `user_id2` as friend,
    FROM `Friends` 
    USE INDEX (`index_on_user1`)
UNION
SELECT
    `user_id2` as subject,
    `user_id1` as friend,
    FROM `Friends` 
    USE INDEX (`index_on_user2`)

使用简单的 SELECT friend FROM get_friend WHERE subject = $user_id得到结果，它起作用了。

但是explain select...说它不使用索引，甚至没有提示。

如何让它使用这些索引？有可能吗？还有其他解决方案吗？

Answer 1

您需要在 UNION 语句的各个查询中有一个 WHERE 子句才能使用索引。本质上，您对 View 的查询就是这样做的

SELECT subject, friend FROM
(SELECT 
    `user_id1` as subject,
    `user_id2` as friend,
    FROM `Friends` 
    USE INDEX (`index_on_user1`)
UNION
SELECT
    `user_id2` as subject,
    `user_id1` as friend,
    FROM `Friends` 
    USE INDEX (`index_on_user2`)
) WHERE friend = <some number>

尝试对填充了适当 where 子句的 UNION 运行 EXPLAIN 查询，您应该会看到正在使用索引。