如何为连接表创建索引

Question

我刚接触索引并遇到了一个问题，Postgresql一直在说我的代码有错误。

我需要在bookings表上添加外键，以引用members表的主键吗？

如果我的SQL表结构如下，并且我有一个使用joindate的JOIN查询和WHERE子句，我该如何实现索引。

SELECT *
FROM bookings b
JOIN members m ON m.memid = b.memid WHERE joindate > '2012-08-01';

bookings                                   members
---------                                 ----------
PK: bookid                                 PK: memid
facid                                      surname
memid                                      firstname
starttime                                  address
slots                                      joindate
                                           recommendedby

我的尝试查询

CREATE JOIN INDEX join_memid_index FOR members JOIN bookings ON  bookings.memid = members.memid

运行上述查询时产生的错误如下：

ERROR:在“JOIN”附近有语法错误
第1行：CREATE JOIN INDEX join_memid_index FOR members JOIN bookings...
^
SQL状态：42601
字符：8

- Anh Ton

---

不需要索引来进行连接（它用于提高性能）。Postgresql一直在说我的代码有错误 提供完整且未更改的查询文本和完整未更改的错误消息。 - Akina

你实际使用的是哪个数据库管理系统？ - jarlh

如果你正在使用PostgreSQL，为什么要标记问题为[tag:mysql]和[tag:sql-server]？这些是不同的数据库产品。 - Damien_The_Unbeliever

你从哪里得到了'CREATE JOIN INDEX' - 在PostgreSQL文档中没有提到在索引创建中使用join。https://www.postgresql.org/docs/9.1/sql-createindex.html - P.Salmon

@Akina，完整的错误信息在我的帖子末尾。还有我尝试的查询。 - Anh Ton

Answer 1

索引是表内部的一种结构。它只能优化查询，而不能执行操作。

您必须单独为每个表创建索引：

CREATE INDEX idx_members_memid ON members (memid);
CREATE INDEX idx_bookings_memid ON bookings (memid);

如果服务器决定这样做可以提高性能，它将使用此索引进行更有效的连接过程。如果它不决定这样做，它将忽略索引的存在并使用表扫描。

顺便说一下，服务器可能会做出错误的决定...

也许包括joindate列的members复合索引比memid单个索引更有效：

CREATE INDEX idx_members_memid_joindate_1 ON members (memid, joindate);
CREATE INDEX idx_members_memid_joindate_1 ON members (joindate, memid);

只有执行计划调查才能确定服务器将使用上述3个索引中的哪一个members（或不使用任何一个）。而且它的决策可能会根据数据统计信息（行数，总数和匹配条件的数量）和参数值而改变。

- Akina