Нужен ли мне индекс для составного первичного ключа, где каждый столбец является внешним ключом

ProgramBox

Нужен ли мне индекс для составного первичного ключа, где каждый столбец является внешним ключом

Post author:admin
Запись опубликована:21 марта, 2023
Post category:Вопросы по программированию

#postgresql

Вопрос:

Когда я создаю таблицу user:

 pk
id | int

И друг таблицы:

 pk, fk  | pk, fk
user_id | friend_id

Насколько я понимаю, composite 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_defau<

1. Да, для запроса, который ищет пользователей, у которых есть x в качестве друга, такой индекс был бы полезен. Однако то, что существуют ссылки на внешние ключи, в значительной степени не имеет значения для этого.

2. @Bergi я не буду использовать такой запрос. Но почему это не имеет значения, если каждый раз, когда я вставляю в таблицу друзей, postgres должен быть уверен, что с внешними ключами все в порядке, и если для проверки user_id он может использовать индекс первичного ключа, для friend_id это не сработает?

3. Внешние ключи указывают на user.id столбец, если я правильно понял? (Было бы полезно, если бы вы могли опубликовать определения таблиц как SQL). Это будет использовать индекс первичного ключа id в user таблице для проверки правильности вставленных user_id значений и friend_id значений. Индексы в friend таблице не используются для проверки целостности внешнего ключа. (Однако они становятся актуальными при удалении или обновлении id of a user ).

4. @Bergi опубликовал sql. Да, есть каскад для каждого столбца. Пожалуйста, ответьте, чтобы я мог его принять. Думаю, теперь я понимаю. Вставки используют пользовательский индекс, но если я изменю идентификатор пользователя, мне понадобится индекс в таблице друзей.

5. @Bergi rarelly, хм, ты прав, тогда мне действительно не нужен индекс для друзей.

Ответ №1:

Насколько я понимаю, composite pk создает уникальный индекс user_id friend_id intenally

ДА.

и этот индекс используется для внешнего ключа user_id.

Не совсем, нет.

Здесь есть два случая:

INSERT или UPDATE в user_friend таблице. Конечно, ему нужно будет проверить первичный ключ таблицы (и user_friend_pkey для этого он будет использовать индекс). Для ограничений внешнего ключа потребуется только проверить user_id friend_id , существуют ли значения и в user таблице, и для них будет использоваться индекс, созданный user первичным ключом.
редактор UPDATE DELETE на user столе. Он должен будет проверить, было ли в таблице указано старое значение user_friend , и действовать в соответствии со стратегией CASCADE or RESTRICT . Чтобы найти эту строку user_friend , он будет использовать индексы вкл user_id и вкл friend_id , если они существуют.

Но нужно ли мне создавать индекс для friend_id, потому что индекс в составной таблице друзей pk не будет работать для friend_id

Да, поиск по friend_id происходит медленно без дополнительного индекса. Вам это нужно только в том случае, если вы хотите найти пользователей, у которых есть x в качестве друга, или когда обновления / удаления user не должны быть медленными.

1. Просто чтобы убедиться, что если я «выберу * из user_friend где user_id = что-то» или для идентификатора ОБНОВЛЕНИЯ или УДАЛЕНИЯ пользователя в таблице user, мне не нужно создавать индекс user_id в таблице user_friend, потому что он будет использовать индекс pk в таблице user_friend.

2. select * from friend_user where user_id=smth будет нормально работать с user_friend_pkey индексом, да. update "user" set id=smth / delete "user" where id=smth также будет использовать этот индекс для обновления user_friend строк, user_id ссылки на которые обновлены / удалены user.id . Однако для обновления user_friend строк, friend_id ссылки на которые обновлены / удалены user.id , это не сработает — для этого вам нужен дополнительный индекс friend_id .