Как внешние ключи связаны с индексами в БД?

Внешние ключи и индексы в БД

Внешние ключи (Foreign Keys) и индексы — это разные механизмы оптимизации, но они тесно связаны на уровне производительности и надёжности данных.

1. Что такое внешний ключ?

Внешний ключ (FK) — это ограничение целостности, которое гарантирует, что значение в колонке одной таблицы соответствует значению в колонке другой таблицы.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Попытка вставить пост с несуществующим user_id будет отклонена
INSERT INTO posts (title, user_id) VALUES ('My Post', 999);  -- ERROR

2. Индексы для внешних ключей

Хотя внешний ключ НЕ создаёт индекс автоматически, индекс на колонке FK критичен для производительности.

Без индекса на FK (медленно):

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Удаление пользователя требует полного сканирования posts
DELETE FROM users WHERE id = 5;
-- PostgreSQL сканирует ВСЕ строки в posts, чтобы проверить FK

С индексом на FK (быстро):

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Индекс на колонке FK
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Теперь удаление пользователя использует индекс для быстрого поиска связанных постов
DELETE FROM users WHERE id = 5;  -- Быстро!

3. Операции, требующие индекса на FK

Проверка ссылочной целостности

-- При вставке в posts нужно проверить, существует ли user_id
INSERT INTO posts (title, user_id) VALUES ('Post', 5);
-- Без индекса: O(n) сканирование таблицы users
-- С индексом: O(log n) поиск

Каскадное удаление

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

-- При удалении пользователя нужно найти и удалить все его посты
DELETE FROM users WHERE id = 5;
-- Без индекса на posts.user_id: полное сканирование posts
-- С индексом: быстрый поиск связанных строк

Обновление FK

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

-- При обновлении user_id нужно проверить, существует ли новое значение
UPDATE posts SET user_id = 10 WHERE id = 1;
-- Без индекса на users.id: медленно
-- С индексом на users.id (PK всегда индексирован): быстро

4. Пример: Blog с категориями

-- Без правильных индексов
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    category_id INTEGER NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL,
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

-- Проблемы производительности при:
-- - Удалении категории (нужно найти все посты)
-- - Запросе "все комментарии для поста"
-- - Обновлении post_id в комментариях

-- ПРАВИЛЬНО: добавляем индексы на все FK
CREATE INDEX idx_posts_category_id ON posts(category_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);

5. Составные внешние ключи

CREATE TABLE invoice_items (
    invoice_id INTEGER NOT NULL,
    line_number INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER,
    PRIMARY KEY (invoice_id, line_number),
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
);

-- Индекс должен начинаться с invoice_id
CREATE INDEX idx_invoice_items_invoice_id ON invoice_items(invoice_id);

6. Типы индексов для FK

-- B-tree индекс (по умолчанию, оптимален для FK)
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Hash индекс (только для равенства)
CREATE INDEX idx_posts_user_id_hash ON posts USING HASH (user_id);

-- Partial индекс (для активных постов)
CREATE INDEX idx_posts_user_id_active ON posts(user_id) 
WHERE is_active = true;

7. Python + SQLAlchemy

from sqlalchemy import (
    Column, Integer, String, ForeignKey, Index, create_engine
)
from sqlalchemy.orm import DeclarativeBase, relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String(200))
    user_id = Column(
        Integer,
        ForeignKey("users.id"),
        nullable=False
    )
    # Индекс на FK (явно для читаемости)
    __table_args__ = (
        Index("idx_posts_user_id", "user_id"),
    )
    
    # Relationship для удобства
    user = relationship("User", backref="posts")

# Миграция (Goose SQL)
# migrations/0001_create_tables.sql

8. Откат на миграции

-- migrations/0001_create_users.sql
-- +goose Up
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- +goose Down
DROP TABLE users;

-- migrations/0002_create_posts.sql
-- +goose Up
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

-- +goose Down
DROP TABLE posts;

9. Когда индекс на FK критичен?

10. Performance: с индексом vs без

-- Таблица с 1М постов и 100к пользователей

-- БЕЗ индекса на posts.user_id
-- DELETE FROM users WHERE id = 5; -- ~2 секунды (полное сканирование)

-- С индексом на posts.user_id
-- DELETE FROM users WHERE id = 5; -- ~50 миллисекунд (индекс)

-- Разница в 40 раз!

Best Practices

• ✅ ВСЕГДА создавай индекс на каждом FK
• ✅ Используй B-tree индексы (по умолчанию)
• ✅ Для составных FK индекс начинается с FK колонки
• ✅ Добавляй индекс в миграции сразу после FK
• ✅ Используй EXPLAIN ANALYZE для проверки
• ❌ Не полагайся на то, что БД создаст индекс автоматически
• ❌ Не забывай про индексы при оптимизации медленных DELETE