mysql - 索引 SET 字段

Question

我有两个实体 A 和 B。它们具有多对多关系。实体 A 最多可以关联 100 个 B 实体。实体 B 最多可以关联 10000 个 A 实体。我需要快速的方法来选择例如 30 个 A 实体，这些实体与指定的 B 实体有关系，并按不同的属性进行过滤和排序。

这里是我如何看待理想的解决方案:我将我知道的关于 A 实体的所有信息，包括它们与 B 实体的关系放入单行(带有 SET 字段的特殊表)，然后添加所有必要的索引。问题是在按 SET 字段查询时不能使用索引。我应该怎么办？如果有帮助，我可以用不同的东西替换数据库。

更新:对不起。看来我忘了提一个重要的细节。我需要同时找到与 id = 1 的 B 条目和 id = 2 的 B 条目有关系的那些 A 条目。因此，如果使用联接，我将得到类似于以下内容的内容:

SELECT a.id, count(*) as cnt FROM a INNER JOIN ab ON a.id = ab.a_id WHERE ab.b_id IN(1,2) GROUP BY a.id ORDER BY NULL having cnt = 2

这给我带来了非常糟糕的表现

Answer 1

你为什么不这样做:

SELECT  *
FROM    a
WHERE   a.id IN
        (
        SELECT  ab.a
        FROM    b
        JOIN    ab
        ON      ab.b = b.id
        WHERE   b.id IN (1, 2, 3, 4)
        )

并在 ab (b, a) 上创建一个 PRIMARY KEY?

更新:

使用这个:

SELECT  *
FROM    a
WHERE   (
        SELECT  COUNT(*)
        FROM    ab
        WHERE   ab.a = a.id
                AND ab.b IN (1, 2, 3, 4)
        ) = 4
ORDER BY
        ...
LIMIT 30

或者这个:

SELECT  a.*
FROM    (
        SELECT  a
        FROM    ab
        WHERE   ab.b IN (1, 2, 3, 4)
        GROUP BY
                a
        HAVING  COUNT(*) = 4
        ) q
JOIN    a
ON      a.id = q.id
ORDER BY
        ...
LIMIT 30

您需要在 ab (b, a) 上有一个 PRIMARY KEY(按此顺序)才能快速运行。

哪种查询更有效取决于您的数据分布。