时间戳

首页 > 解决方案 > 我是否需要复合主键的索引，其中每列都是外键

postgresql - 我是否需要复合主键的索引，其中每列都是外键

问题描述

当我创建表用户时：

pk
id | int

和表友：

pk, fk  | pk, fk
user_id | friend_id

据我了解，复合 pk 在内部创建唯一索引 user_id+friend_id，该索引用于 user_id 外键。但是我是否需要为friend_id创建索引，因为复合pk朋友表上的索引不适用于friend_id（它是复合pk中的第二个）？

SQL：

CREATE TABLE public."user"
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
)



CREATE TABLE public.user_friend
(
    user_id integer NOT NULL,
    friend_id integer NOT NULL,
    CONSTRAINT user_friend_pkey PRIMARY KEY (user_id, friend_id),
    CONSTRAINT user_friend_friend_id_fkey FOREIGN KEY (friend_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT user_friend_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)


CREATE INDEX fki_user_friend_friend_id_fkey
    ON public.user_friend USING btree
    (friend_id ASC NULLS LAST)
    TABLESPACE pg_default;

标签： postgresql

解决方案

据我了解，复合 pk 会在内部创建唯一索引 user_id+friend_id

是的。

该索引用于 user_id 外键。

不完全是，不。

这里有两种情况：

  • 一个INSERT或UPDATE在user_friend桌子上。当然，它必须检查表的主键（并将user_friend_pkey为此使用索引）。对于外键约束，这将只需要检查表中是否存在user_id和friend_id值user，并将使用由user的主键创建的索引。
  • 一个UPDATE或DELETE在user桌子上。它将必须检查user_friend表中是否引用了旧值，并根据CASCADEorRESTRICT策略执行操作。要在 中找到这一行user_friend，它将使用 onuser_id和 on的索引（friend_id如果存在）。

但是我需要为friend_id创建索引吗，因为复合pk朋友表上的索引不适用于friend_id

friend_id是的，如果没有额外的索引，查找速度很慢。仅当您想查找以 x 为朋友的用户时，或者当更新/删除user速度不慢时，您才需要此选项。

推荐阅读