sql - 如何提高多对多 SQL 查询的性能？

Question

我在 Books 和 Genres 之间建立了多对多关系。例如，“霍比特人”这本书的类型可能是“ child ”、“小说”和“奇幻”。

这是架构:

CREATE TABLE "genre" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(50) NOT NULL
)
;
CREATE TABLE "book_genres" (
    "book_id" integer NOT NULL REFERENCES "book" ("id"),
    "genre_id" integer NOT NULL REFERENCES "genre" ("id"),
    CONSTRAINT book_genres_pkey PRIMARY KEY (book_id, genre_id)
)
;
CREATE TABLE "book" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(255) NOT NULL,
    "price" real NOT NULL
)
;

和索引:

CREATE INDEX "book_genres_36c249d7" ON "book_genres" ("book_id");
CREATE INDEX "book_genres_33e6008b" ON "book_genres" ("genre_id");
CREATE INDEX "book_5a5255da" ON "book" ("price");

行数:

• 类型:30
• 图书类型:800,000
• 图书:200,000

我正在尝试在 SQL 中编写一个查询，该查询返回按价格订购的特定流派的所有书籍，不重复。

这是我执行此操作的查询:

SELECT name, price 
FROM book 
WHERE book.id 
IN 
    (SELECT book_id 
    FROM book_genres
    WHERE genre_id = 1
    OR genre_id = 2)
ORDER BY price LIMIT 10

我的问题是性能。此查询最多可能需要 2000 毫秒才能执行。我怎样才能提高性能？

我可以完全控制数据库 (Postgres 9.3)，因此可以添加 View 、索引或非规范化。我也在使用 Django，因此可以使用 Python/Django 执行多个查询在内存中执行操作。

Answer 1

SELECT b.name, b.price
FROM book b
WHERE EXISTS (
    SELECT *
    FROM book_genres bg
    WHERE bg.book_id = b.id 
    AND bg.genre_id IN( 1 , 2)
    )
ORDER BY b.price 
LIMIT 10
        ;

按价格+LIMIT 排序可能会成为性能 killer :检查查询计划。

PLUS:用“反向”索引替换单列索引:使 book_id 成为 books.id 的 FK并且(也许)省略代理键 ID

---

CREATE TABLE book_genres
        ( book_id integer NOT NULL REFERENCES book (id)
        , genre_id integer NOT NULL REFERENCES genre (id)
        , PRIMARY KEY (book_id, genre_id)
        ) ;
CREATE INDEX ON book_genres  (genre_id,book_id);