mysql - 单表 SELF JOIN 备选方案/除外/相交

Question

我目前正在处理一个查询，该查询根据书的属性从表中搜索书籍。该表包含超过 5000 万行，结构如下:

-----------------------
| book_id | attr_id   |
-----------------------
| 2005207 | 35021     |
-----------------------
| 2005207 | 28106     |
-----------------------
| 2005207 | 27173     |
-----------------------
| 2005207 | 35109     |
-----------------------
| 2005207 | 34999     |
-----------------------
| 2005207 | 35107     |
-----------------------
| 2005207 | 35099     |
-----------------------
| 2005207 | 35105     |
-----------------------
| 2005207 | 28224     |
-----------------------
| ...     | .....     |    
-----------------------

属性栏代表属性，如装订、出版年份、流派等等。 主键是复合键attr_id, book_id

一个示例查询可以是“查找所有类型为漫画或科幻小说且没有精装书的书籍”。

SELECT sql_no_cache a.book_id
FROM
  (SELECT book_id
   FROM attribute_books ab
   WHERE ab.attr_id IN (38571,
                        38576)) a
LEFT JOIN
  (SELECT book_id
   FROM attribute_books ab
   WHERE ab.attr_id = 35003) b ON b.book_id = a.book_id
AND b.book_id IS NULL;

这类查询可以多次自连接，目前性能很差。除了 IN 语句的内连接和 NOT IN 语句的左连接，我还可以使用 intersect 命令，它在某些 SQL 风格中可用。

我目前有以下问题:

1. 对于类似查询，这是最有效的查询类型吗？如果没有，是否有任何加快速度的建议？
2. 我是否应该切换到完全不同类型的数据库/引擎，例如更高效(更快)的查询？

Answer 1

可能最有效的方法是存在和不存在:

select b.*
from books b
where not exists (select 1
                  from attribute_books ab
                  where ab.attr_id in (38571, 38576) and b.book_id = ab.book_id
                 ) and
      exists (select 1
              from attribute_books ab
              where ab.attr_id = 35003 and b.book_id = ab.book_id
             )

为此，您需要在 attribute_books(book_id, attr_id) 上建立索引。