За что отвечает каждый из индексов БД

За что отвечает каждый из индексов БД

Индексы — это ключевая структура данных для оптимизации запросов. Рассмотрю все основные типы и их предназначение.

Основной принцип индексов

Индекс — это отсортированная структура данных, которая позволяет быстро найти строки без полного сканирования таблицы:

Без индекса:  O(N) — смотрим каждую строку
С индексом:   O(log N) — двоичный поиск или B-tree обход

1. PRIMARY KEY индекс

Отвечает за: уникальность и быстрый поиск по ID

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- автоматический уникальный индекс
    name VARCHAR(100)
);

-- PRIMARY KEY создаёт:
-- - Уникальный индекс (no duplicates)
-- - Кластеризованный индекс (физический порядок строк)
-- - Constraint (NOT NULL по умолчанию)

Использование в Python:

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)  # B-tree индекс
    name = Column(String(100))

# Быстрый поиск по первичному ключу
user = session.query(User).filter(User.id == 123).first()  # O(log N)

2. UNIQUE индекс

Отвечает за: уникальность + быстрый поиск по уникальному полю

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE  -- индекс + constraint
);

-- Эквивалент:
CREATE UNIQUE INDEX idx_users_email ON users(email);

Использование:

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True)  # Уникальный индекс

# Быстрый поиск по email И проверка уникальности
user = session.query(User).filter(User.email == 'john@test.com').first()

# Попытка добавить дубликат — ошибка
try:
    session.add(User(email='john@test.com'))  # email уже существует
    session.commit()
except IntegrityError:
    print("Email already exists")  # Unique constraint violation

3. Простой индекс (B-tree)

Отвечает за: быстрый поиск по одной колонке

CREATE INDEX idx_users_name ON users(name);

-- Используется для:
-- - WHERE name = 'John'         — точное совпадение
-- - WHERE name LIKE 'John%'     — префиксный поиск
-- - WHERE name > 'A'            — диапазоны
-- - ORDER BY name               — сортировка

Использование:

from sqlalchemy import Index

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), index=True)  # Простой B-tree индекс
    email = Column(String(255))

# Быстрые запросы:
users = session.query(User).filter(User.name == 'John').all()  # O(log N)
users = session.query(User).filter(User.name.like('John%')).all()  # O(log N + matches)

# Без индекса было бы O(N) — полное сканирование таблицы

4. Составной индекс (Composite Index)

Отвечает за: быстрый поиск по нескольким колонкам

CREATE INDEX idx_orders_user_status 
    ON orders(user_id, status);

-- Эффективен для:
-- - WHERE user_id = 1 AND status = 'completed'
-- - WHERE user_id = 1  (первая колонка)
-- НЕЭФФЕКТИВЕН для:
-- - WHERE status = 'completed' (вторая колонка без первой)

Использование:

from sqlalchemy import Index

class Order(Base):
    __tablename__ = "orders"
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    status = Column(String(20))  # 'pending', 'completed', 'cancelled'
    
    __table_args__ = (
        Index('idx_orders_user_status', 'user_id', 'status'),
    )

# Быстрый запрос (использует индекс)
orders = session.query(Order).filter(
    Order.user_id == 123,
    Order.status == 'completed'
).all()  # O(log N)

# Быстрый запрос (использует индекс, ищет по первой колонке)
orders = session.query(Order).filter(
    Order.user_id == 123
).all()  # O(log N)

# МЕДЛЕННЫЙ запрос (индекс не используется)
orders = session.query(Order).filter(
    Order.status == 'completed'
).all()  # O(N) — нужен отдельный индекс

5. HASH индекс

Отвечает за: очень быстрый поиск по точному совпадению

CREATE INDEX idx_users_email_hash USING HASH ON users(email);

-- Эффективен для:
-- - WHERE email = 'john@test.com'  — точное совпадение
-- НЕЭФФЕКТИВЕН для:
-- - WHERE email LIKE 'john%'       — префиксный поиск
-- - WHERE email > 'a'              — диапазоны
-- - ORDER BY email                 — сортировка

Использование (редко в PostgreSQL, чаще в MySQL):

# В PostgreSQL HASH индекс менее популярен (B-tree обычно лучше)
# В MySQL HASH индекс используется по умолчанию

# Оптимизация: HASH индекс для точного совпадения
class User(Base):
    __tablename__ = "users"
    email = Column(String(255))
    
    # PostgreSQL: B-tree с стандартным оператором =
    __table_args__ = (
        Index('idx_users_email', 'email'),
    )

6. FULL-TEXT индекс

Отвечает за: быстрый текстовый поиск

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

-- PostgreSQL full-text поиск
ALTER TABLE articles ADD COLUMN search_vector tsvector;

CREATE INDEX idx_articles_search 
    ON articles USING gin(search_vector);

UPDATE articles SET search_vector = 
    to_tsvector('russian', title || ' ' || content);

-- Быстрый поиск
SELECT * FROM articles 
WHERE search_vector @@ to_tsquery('russian', 'python');

Использование в Python:

from sqlalchemy import func, Index
from sqlalchemy.dialects.postgresql import TSVECTOR

class Article(Base):
    __tablename__ = "articles"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    content = Column(Text)
    search_vector = Column(TSVECTOR)
    
    __table_args__ = (
        Index('idx_articles_search', 'search_vector', postgresql_using='gin'),
    )

# Быстрый текстовый поиск
query = "python & development"
results = session.query(Article).filter(
    Article.search_vector.match(query, postgresql_operator='@@')
).all()

7. GiST и GIN индексы

Отвечает за: поиск по сложным типам данных

GiST (Generalized Search Tree):

• Геопространственный поиск (PostGIS)
• Поиск по диапазонам
• Текстовый поиск (менее эффективен чем GIN)

GIN (Generalized Inverted Index):

• Текстовый поиск (более быстрый)
• Поиск в массивах (ARRAY типы)
• Поиск в JSON полях

from sqlalchemy.dialects.postgresql import JSONB, ARRAY, Index

class Document(Base):
    __tablename__ = "documents"
    
    id = Column(Integer, primary_key=True)
    # JSON field с GIN индексом
    metadata = Column(JSONB)
    tags = Column(ARRAY(String))  # Массив тегов
    
    __table_args__ = (
        Index('idx_doc_metadata', 'metadata', postgresql_using='gin'),
        Index('idx_doc_tags', 'tags', postgresql_using='gin'),
    )

# Поиск по JSON
docs = session.query(Document).filter(
    Document.metadata['author'].astext == 'John'
).all()

# Поиск в массиве
docs = session.query(Document).filter(
    Document.tags.contains(['python', 'backend'])
).all()

8. PARTIAL индекс

Отвечает за: индексирование только части данных (экономия места)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255),
    is_active BOOLEAN
);

-- Индексируем только активных пользователей
CREATE INDEX idx_active_users_email 
    ON users(email) 
    WHERE is_active = true;

-- Быстрый поиск активного пользователя
SELECT * FROM users 
WHERE email = 'john@test.com' AND is_active = true;  -- Использует индекс

-- Медленный поиск неактивного
SELECT * FROM users 
WHERE email = 'john@test.com' AND is_active = false;  -- O(N)

Использование:

from sqlalchemy import Index

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    email = Column(String(255))
    is_active = Column(Boolean, default=True)
    
    # Partial индекс (только для активных)
    __table_args__ = (
        Index(
            'idx_active_users_email',
            'email',
            postgresql_where=User.is_active == True
        ),
    )

Таблица выбора индекса

Важные правила

✅ Когда добавлять индекс:

• Колонка часто используется в WHERE
• Запрос медленный (проверить EXPLAIN)
• Таблица большая (> 10K строк)

❌ Когда НЕ добавлять индекс:

• Частые INSERT/UPDATE (индекс замедляет)
• Малая таблица (< 1K строк)
• Колонка редко используется
• Индекс займёт больше места чем выгода

⚠️ Опасности:

• Слишком много индексов → медленные INSERT/UPDATE
• Неправильный составной индекс → не используется
• Индекс на скачивающимся столбце → неэффективен

Проверка эффективности индекса

from sqlalchemy import text

def check_index_usage(connection):
    # Посмотреть все индексы
    result = connection.execute(text("""
        SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
        FROM pg_stat_user_indexes
        ORDER BY idx_scan DESC;
    """)).fetchall()
    
    for row in result:
        print(f"{row[0]}: scans={row[1]}, reads={row[2]}")

# Неиспользуемые индексы
def find_unused_indexes(connection):
    result = connection.execute(text("""
        SELECT indexname, idx_scan
        FROM pg_stat_user_indexes
        WHERE idx_scan = 0;
    """)).fetchall()
    
    # Эти индексы можно удалить для оптимизации
    for idx, _ in result:
        print(f"DROP INDEX {idx};")

Вывод

Выбор индекса зависит от:

• Тип данных → какой индекс поддерживается
• Тип запроса → точный поиск vs диапазон vs текст
• Частота доступа → есть ли SELECT запросы
• Размер таблицы → на малых не нужны
• Место на диске → индекс занимает память

Правило: Начни с PRIMARY KEY, добавляй индексы только если EXPLAIN показывает полное сканирование.