mysql - 排除子查询返回需要互斥的结果的记录

Question

标题不够解释......

我每小时运行一次查询来准备 MEMORY 表，然后将其用于下一小时的高强度流量。现在查询如下:

INSERT INTO tmp_table
            SELECT DISTINCT SQL_NO_CACHE
            B.*, G.node
            FROM books B
            RIGHT JOIN book_genres G on G.asin=B.asin
            WHERE EXISTS
            (
              SELECT 1 FROM genres K WHERE K.node=G.node
            )
            AND...[nothing special here]

所以 books 表只保存带有 asin 主键的图书数据。 book_genres 包含 asin 和 node 字段，并包含在网站各个部分使用的大量节点。然而，tmp_table 只需要包含流派表中的节点子集，因此是子查询。希望这已经足够了，无需发布完整的架构。

我们现在想让某些类型相互排斥。这意味着在构建 tmp_table 之后执行此操作:

# Delete records that should have been excluded
DELETE T FROM tmp_table T INNER JOIN 
(
    SELECT N.* FROM tmp_table N INNER JOIN
    (
        SELECT DISTINCT ASIN FROM tmp_table 
        INNER JOIN genres ON genres.node=tmp_table.node
        WHERE isFiction=1
    ) F 
    ON F.asin=N.asin 
    INNER JOIN genres ON  genres.node=N.node
    WHERE genres.isNonFiction=1 
) D 
USING (asin, node)
WHERE D.asin=T.asin AND D.node=T.node

因此，如果 asin 属于 isFiction=1 的流派，则所有 nonFiction=1 的流派都会被删除。

但这感觉很丑:使用查询添加数据，然后再次删除它。有什么方法可以将其合并到一个查询中以避免双重传递。或者我只是为自己工作，考虑到(经过一些调整和编辑)上述内容确实可以很快地工作。

任何想法将不胜感激，谢谢。

添加示例:

books table:
asin        title       price     etc...
B111111111  Book1       $0.99     ....
B222222222  Book2       $0.99     ....
B333333333  Book2       $0.99     ....

book_genres table:
asin        node
B111111111  1111
B111111111  2222
B111111111  3333
B111111111  5555
B222222222  1111
B222222222  3333
B222222222  4444
B333333333  1111
B333333333  2222

genres table:
node    name         isFiction    isNonFiction
1111    Bestsellers  0            0
2222    Romance      1            0
3333    Biographies  0            1
4444    History      0            1

因此，运行 INSERT INTO 后，tmp_table 将如下所示:

asin       title       node 
B11111111  Book1       1111
B11111111  Book1       2222
B11111111  Book1       3333
B22222222  Book2       1111
B22222222  Book2       3333
B22222222  Book2       4444
B33333333  Book3       1111
B33333333  Book3       2222

Book1 没有节点 5555 的记录，因为它不在类型表中，而且我们在 tmp_table 中不需要它。其他一切几乎都是数据的非规范化，因为 WHERE 标识了数据库中数百万本书中的数千本书的一小部分，这样处理起来要快得多。

下一步确​​保如果一本书有 isFiction 节点，则删除该书的所有 isNonFiction 节点。

运行DELETE后，tmp_table中的最终结果是:

asin       title       node 
B11111111  Book1       1111
B11111111  Book1       2222
B22222222  Book2       1111
B22222222  Book2       3333
B22222222  Book2       4444
B33333333  Book3       1111
B33333333  Book3       2222

唯一的区别是 Book1 的节点 3333 已被删除，因为 Book1 位于 isFiction=1 的 2222 节点中，而节点 3333 的 isNonFiction=1。 Book2 没有变化，因为它不包含 isFiction 节点。同样，Book3 也没有改变，因为它不包含 isNonFiction 节点。

在这个阶段，它正在使用这个实现，尽管运行时间已经翻倍，从大约 20 多秒增加到大约 40 秒。这并不奇怪，因为 DELETE 有点复杂。这可能是一个足够好的解决方案，但如果其他人有一个想法可以使整个事情变得更简单或更快，我会很高兴。

标记...

Answer 1

而且很丑。它工作得很好，直到数据库上有任何其他重大负载，然后一切都变得非常缓慢。这主要是由于服务器的 IO 限制，但更简单的方法是将 isfiction 和 isNonFiction 放入 MEMORY 表中，然后 DELETE 语句可以如下所示:

    DELETE tmp_table FROM tmp_table
         INNER JOIN
         (
            SELECT ASIN, MAX( isFiction ) AS isFiction, MAX( isNonFiction ) AS isNonFiction
            FROM tmp_table
            GROUP BY ASIN
            HAVING isFiction =1
            AND isNonFiction =1
         ) D
         WHERE D.ASIN=tmp_table.ASIN AND tmp_table.isNonFiction=1

在测试中，整个过程从大约 90 秒缩短到 10 秒。