主页 / dba / 问题 / 52282
Accepted
DaveB
Asked: 2013-10-28 04:51:07 +0800 CST

带连接表的可空一对多 - 索引

  • 772

我有一个关于数据库设计的问题，我正在使用 Hibernate for ORM 使用 PostgreSQL，我继承的设计包括下表

用户

user_id (pk) user_name user_dob ...

会话

session_id (pk) session_date session_duration ...

用户会话

user_id (fk) session_id (fk)

您可以猜到一个用户可以有多个会话，使用连接表的原因（我相信）是因为可以在用户存在之前添加一个会话（并且它在用户注册后链接到用户），从而避免了可为空会话表中的字段...很好。

但我最近一直在考虑创建一些多列索引以加快某些查询的速度，并且意识到我不能将用户包含在该索引中，因为该列位于不同的表中。

由于我对数据库设计还很陌生，所以我想知道上面的设计是否正确？还是我实际上最好在会话表中使用可为空的 FK 以允许我索引用户关系？

database-design postgresql

1 个回答

  1. Best Answer

    如果一个会话在任何给定时间只能属于一个用户，则更合适的模型将是没有连接表 ( user_sessions)。column user_idbeing有什么问题NULL？

    如果你需要它NOT NULL并且你在创建时并不总是知道用户，只需插入一个用户“未知”（user_id 0在我的例子中）。也可以作为DEFAULT价值...

    CREATE TABLE sessions
  session_id   serial PRIMARY KEY
 ,user_id      int NOT NULL DEFAULT 0 REFERENCES users(user_id)
 ,session_date timestamp
 ...
)

    索引策略注意事项

    Postgres 可以以极快的速度组合多个索引。通常，在您的原始场景中，有一个嵌套循环：

    嵌套循环连接：在左侧关系中找到的每一行都会扫描一次右侧关系。此策略易于实施，但可能非常耗时。（但是，如果右关系可以用索引扫描来扫描，这可能是一个很好的策略。可以使用左关系当前行的值作为右索引扫描的键。）

    说，你有这些指数：

    sesssions(session_id)
user_sessions(session_id, user_id)

    两者都应自动包含在原始模型中的 pk 约束中。然而，第二个可能是任何一种方式(user_id, session_id)，(session_id, user_id)这很重要：
    复合索引是否也适用于第一个字段的查询？

    为了在查询中获得最佳性能，例如：

    SELECT u.user_name, s.session_date 
FROM   users         u
JOIN   user_sessions us USING (user_id)
JOIN   sessions      s  USING (session_id)
WHERE  u.user_name = 'foo'

    您需要索引（或 pk 约束）是

    user_sessions(user_id, session_id)

    或者有一个附加索引user_id作为第一列。

    • 0

相关问题